Efficient SQL Server Administration: Mise En Place for IT

What is "Mise En Place"?

Mise En Place comes from the French for "putting in place".  It's a culinary term which refers to organizing and arranging ingredients required for a shift in a professional restaurant for preparing all menu items for a given shift.  The individual utensils are placed in locations that will intuitive to the chef, ingredients are located and prepared (diced, sliced, macerated) and ready for each dish.  A fluent practicioner of Mise En Place should be able to be blindfolded and still navigate their prep station.  They should barely need to move their feet to produce their fare for the evening rush.

How does this relate to SQL Server?  It relates to any disipline.  You know as you start your day roughly what you'll need to do to make it successful.  You'll know the tools you'll use, the raw ingredients of accomplishing the required tasks that aggregate to a day in the life of a SQL Professional.  There should be no reason for you to spend (waste I'd call it) valuable time in looking for frequently used diagnostic scripts, typing code you use tens (if not hundreds) of times per day, or even looking for those reference materials you often find yourself looking up because no one can remember the syntax or exceptions for every dusty/dark corner of Microsoft SQL Server.

I practice Mise En Place as an established SQL Server Pro and what it comes down to is organization, consistency, and tools.

Mise En Place and Organization

To harken back to the culinary roots of Mise En Place I'd state that this means you have your station organized:  your desk is set up properly (no stacks of half-read magazines, receipts you need to file and a stray XBox controller here and there.  Your desk is where the magic happens so keep it clean and laid out where you know things are.  I know my coffee is on the warmer to within reach of my left hand; my cell phone is on the charging pad under my iMac.  My PC is in the charging dock to my left with the roller ball mouse I've used for 15 years right there next to it.  Then there are my lights.  I recently picked up a set of Phillips HUE lights and a NEST thermostat as I start to dip my toes into the Internet of Things.  I have my lights on a timer in my office to come on at 7:30am and go off at 10:00am.  I've also have them timed to turn red and blink on a 52 minute / 17 minute cycle to practice an efficiency technique for doing sprints and taking breaks that research shows improves work efficiency but I'll save that for a different article on telecommuting.    

There, that covers the environment apect but you also need to take into consideration the digital environment.  When we talk about SQL Server that means SQL Server Management Studio (SSMS).

Though there are plenty of issues with the beloved SSMS (and I've written about them and will do so again quite soon) the nice thing is it's customizable.  Want Object Explorer on the left, no worries.  Do you want Template Explorer on the right but auto-hidden?  Piece of cake.  Do you want each new query to be based off a certain template, perhaps one that starts with BEGIN TRANSACTION with no matching COMMIT TRANSACTION to protect you from hitting that F5 key with your elbow when you reach for your coffee?  Thanks to Mladen Prajdic's SSMS Tools Pack that's an option as well.  (See the section below on Mise En Place and SQL Tools for more on that.)  The point here is that you take the time to set up SSMS precisely how you want it and then become comfortable with those layout decisions.  After a certain period of time you should almost be able to close your eyes and move the mouse and know you're hitting a specific area on the screen just like if you were to close your eyes and reach for that toasty cup of caffeine.  

EDITOR'S NOTE: WE HERE AT SQLMAG.COM DO NOT ADVOCATE RANDOMLY CLICKING ON ITEMS IN SQL SERVER MANAGEMENT STUDIO WITH YOUR EYES CLOSED.

Content is also important.  Here I'm specifically referring to the Registered Servers Window.  Please take the time to build a heirarchy of pre-defined connections here so you don't find yourself repeating effort to connect to a SQL Server instance for the sake of new queries or adding an instance to Object Explorer.  This is how I have set up my Registered Servers content ever since SSMS was released as part of the SQL Server 2005 roll-out:

Before you ask the question, yes I definitely have duplicate registrations between various groupings.  I set up these various groups because:

This last pooint is key for deploying upgraded administration scripts, checking on baselines I'm capturing (or preparing to capture) or a variety of other reasons.  This is also the main reason why I separate out the different versions.  The underlying system database schemas change from version-to-version (and in some cases between CUs and SPs within the same version.)  If I want to execute a script against the Dynamic Management Views (for example) that pulls information back from sys.dm_os_sys_info I want it to complete successfully against all the instances in my target pool.  Obviously that won't be the case if I try to run it against the "Versions" Registered Servers Group since SQL 2000 didn't have such a thing as "DMVs".  It would also fail (possibly) if I ran it against the "SQL 2005 and Newer (PROD)" group since that specific DMV seems to undergo a schema change each time a new version of SQL Server is released.  In this case I'd want to run the query against the individual set of instances assigned to the specific SQL version if I know that I'm going to be querying anything to do with the memory columns since they are measured (and named) differently in SQL 2008R2 versus SQL 2012 or SQL 2014.  I also would want to test any script I intend to run in such a fashion against a non-production set of instances first.  This is why I split out PROD from NON-PROD. I am sure I go overboard to some extreme in this structure but I know - without a shadow of a doubt - how my environment is organized because of how I have Registered Servers organized.  I also definitely don't want to fall into the "now what is that server named where the company has the application that does the thing..." spiral.  You know, the one where you start blindly entering in server names to get them to show up in Object Explorer.  In my system I can simply navigate to where I need to go in Registered Servers, right click and select Open in Object Explorer.  No need to type a server name ever again.

If you're going to do something more than once then set up a process that is repeatable without the need to go through all the repeatable motions each time.  In other words:  Operationalize and Parameterize.  I cover this topic in more detail next under Mise En Place and Consistency.

Then there is Template Explorer.  The contents of that window are defined by the contents of a specific folder on the machine you're running SSMS (in SQL 2012 it's in the UsersAppDataRoamingMicrosoftMicrosof SQL Server Management Studio11.0Templates.  In SQL 2014 it's in the UsersAppDataRoamingMicrosoftMicrosoft SQL Server Management Studio12.0Templates.)   Note also the contents are also sorted in alphabetical order. (but if you add content inside of Template Explorer the resort does not occur until the next time you launch SSMS.)  Since the content is alphabetized I create a folder in Template Explorer with a name that tends to be a bit heavy on the leading letters so it rests at the top of the Template Explorer.  It is possible to add folders and templates either within SSMS or inside of the source directory for your specific version of SSMS but just like the sorting behavior inside of SSMS, any new content added in the source directory is not re-populated until the next time you launch SSMS.  This folder inside Template Explorer is where I keep all my custom scripts which I talk about next in regards to "consistency."

Mise En Place and Consistency

As stated above:  if you're going to do something more than once then take the time to script it out, parameterize what could possibly change, and then save it as either a Template inside the Template Explorer or as a Snippet thanks to third party tools that offer such functionalities (see next session.)  This way you know you're going to get consistent results after going through the process of building a template, testing it in a non-production environment and then saving it off for re-use.  For example, I know that on a regular basis I'm going to run into situations where I want to run a stored procedure in my administrative database I have on each SQL instance I support that provides information on free space, location, and auto-growth settings by file and database.  I have functionality that has two parameters that get passed in: @granularity and @database_name.  If I script out that stored procedure execution and then use template parameter syntax to set up those values to be something I can swap out on-the-fly I have something that looks like this:

EXEC iDBA..usp_sizing '', ;

Template parameterization is broken down into three parts surrounded by <> characters and delimited by commas:

In this case I know when I look at this code I'm going to need to swap out values for granularity ('database' or 'file' depending upon whether I want the information to present itself at the database or file level of granularity) and the database name surrounded by single quotes or NULL to return results for all databases.  Please see that I included the single quotes for the granularity parameter outside of the <> construct since either value of 'database' or 'file' will need to be in single quotes and everything between the <> characters get's replaced when you replace the template parameters by the keyboard shortcut of Control+Shift+M.

Consistency is not just about repeatable code.  When dealing with non-repeatable tasks I find it wise to always follow the same process.  In my case I use the same code formatting each time and document the code with comments.  It's always better to over-document rather than under-document in my opinion.  How many times have you had to go back and look at code you or someone else has written and wasted hours trying to figure things out (yes, even with your own code?)  This helps in that regard.  Sure, documentation adds to the time involved in completing the task at hand.  Does it end up saving time in the long run?  I'd find it hard to argue against that point. 

So, for repeatable tasks: script, paramterize and save off to a Template or a Snippet (if you have the third party code to do so.) 

For non-repeatable tasks follow the same process each time until you've developed the digital equivalent of muscle memory and document-document-document.

Consistency also place a role in SQL installs and configuration.  I've written here previously on the use of installation configuration files for ensuring identical installs of SQL Server across your environment.  I also have a set of six scripts I run after the install is complete to add my administrative database, create objects within it, set improved defaults for such things as Cost Threshold for Parallelism & Max Degree of Parallelism (MAXDOP), Max Server Memory and Minimum Server Memory as well as setting up Database Mail consistently cross all installs of SQL Server. 

Once completed every single SQL Server instance in my environment has the same drive structure, configuration, and naming convention:  consistently.

Mise En Place and SQL Tools

Finally we hit upon the only area that is budget-centric.  The one involving tools.  Like every good chef has his or her own set of knives weighted perfectly to fit their skill set and personal preference so should the SQL Professional have his or her tools at their disposal for making their work life more efficient.  Let's focus on that last word for a moment because it's key to all we're talking about here and it's the one that you'll need to drive home with those holding the funds for software in your organization: efficiency.

Efficiency means time saved for you.  It means stress relieved for you. 

It means money saved in terms of the speed at which you can get things done for your company.

I'm able to excel because of the third-party tools I have at my disposal.  In some cases it's cost me nothing (or at least a pittance.)  In the case of the monitoring software I employ it's a bit pricier, but in the long run it's a life saver for the company and a MONUMENTAL time saver for myself and my team.  I personally use and recommend SQLPrompt from Redgate.  It allows me to be a "Lazy Coder" because of it's auto-complete functionality that is far superior to the native auto-complete in SSMS.  It also allows for the use of Snippets which are like templates but without the need to store and navigate to the Template Explorer each time you want to launch the code.  Instead, with a Snippet you just type an abbreviation that you've associated with a Snippet and it's replaced in your query window.  The aforementioned SSMS Tools Pack also has this functionality and the functionality to have a predefined set of code included each time you launch a new query window.  Finally there is the monitoring software.  Whether you're an Idera, SolarWinds, ApexSQL, Dell, or in my case SQL Sentry customer or you have built your own scripts for monitoring your environment you need to have something in place to address those "My server was slow at 3 am last night.  What was going on?" questions.  That means those monitoring tools should be deployed consistently so you know exactly where to go and what to look for when things are not "right".  Of course that also means you should have baselines for each of your servers so you know when things are the opposite of "right".

Summing it All Up

Being an effective SQL Professional - heck any Technical Professional - can take a lesson from the culinary world.  By ensuring you understand how you've laid out your environment and tools and ensuring you maintain a consistent approach to your processes you'll easily shave not just minutes from your day - but hours.  Then take a step back and figure how you want to make use of those time savings, be it for training, additional work, or perhaps another cup or six of coffee.