How to understand SQL Server execution plans

SQL Server Execution Plans by Grant Fritchey

Query Execution Plans are something that a DBA or TSQL developer will get to encounter pretty soon after starting to work with SQL Server. They are found in a number of guises - graphical, text or even XML. In either format they are a daunting array of information that seems really important but also pretty complex and technical. With Grant's book by your side you will soon be reading the execution plans and relating them to the query you have written and then be able to alter your script or adjust parts of the database to improve the efficiency of your queries.

The book starts of in a basic way, with worked examples that you can follow on any version of SQL Server that has an AdventureWorks database. You will work your way from the simplest of queries up in complexity to examples using JOINS and GROUP BY and then examining what indexes are being used (or possibly more importantly, why they are not being used).

The book also covers how to see what the execution plans are and also how to control the way the execution plan is created with HINTS so that it is compiled the way you want it if you have a preference for a certain method.

This book is something that I will be referring to regularly as I build up my understanding of SQL Server Execution Plans. An excellent cover-to-cover read and a great reference book.


There are various versions of the book:
electronic version available from Simple Talk Publishing - SQL Server Execution Plans - eVersion
in print from Amazon.com - SQL Server Execution Plans- in print
and it should be available soon via Amazon.co.uk I will put a link here when it is.

Please be sure to read the code disclaimer page before you use any code in this blog.

Writing TSQL faster with templates

SSMS Templates

The management tools that come with SQL Server are the way that most DBAs access and administer their database environment. SSMS has plenty of shortcuts and times saving features, one of which is the use T-SQL templates. SSMS templates are accessed via their own menu and are stored in folders just like you would access files on a windows explorer window. SSMS comes with a whole load of templates ready to run but you can add to and alter these very easily.

Firstly, lets look at getting a template onto the query editor and running it successfully.
Right, open up an SSMS session and hit Alt + N (or Ctrl + N for SSMS for SQL 2005) to open a new TSQL editor window. Now its possible that you will have the templates toolbar ready to use already but hit Ctrl + Alt + T to make it active. Lets pick something simple to start with, open up the Database folder and drag the Create Database template over to your query editor page. Right, we have the text on the page but we have to do a little work before we can hit F5 or click Execute.

You'll see that there are sections of code inside < > brackets. These are SSMS template parameters, we need to take this code out and replace it with something specific to our environment in order to make the script parse and then execute successfully. We could swipe the parameter with our mouse but that could get tedious if we pop out a template with loads of them, its easier to just press Alt + Q, Alt + S (Ctrl + Shift + M for SQL 2005 and earlier). This brings up the parameter completion dialog for you.

Simply type the database name in the higlighted area and press Enter. Job done, it is now ready to run, the text that you entered in the parameters dialog has been put in three places and the script will now successfully create a database (with all the default options such as file locations that are set at server level) providing you have security on the server to execute this command.

Unblocking your SQL Server

Finding which process is locking up your SQL Server can cause some headaches to people new to DBA activities. Digging deaper into the information that is available and refining the data to inform how you proceed is something that can still cause a well established DBA.

sp_who is a stored procedure that ships with SQL Server. If you run EXEC sp_who from a Management Studio query window then you have a grid of data returned that details the connection details of all activity on your server. This is useful as it shows who is connected to which database and what they are up to. Now this can be a small amount of data on your test server but on a production server could return hundreds of rows. This is also instantaneous, in so far as when you run it next it could all have changed.

I find it useful to get these results into a temporary table so that I can shake it up a little and refer to the same set of results more than once while I am trying to resolve block or some such. To do this you need to get the output of sp_who into the aforementioned temporary table with a section of code like this:

IF OBJECT_ID('tempdb.dbo.#who') > 0
BEGIN
TRUNCATE TABLE #who
END
ELSE
BEGIN
CREATE TABLE #who
(SPID INT ,
ECID INT ,
[Status] NVARCHAR(255) ,
[LOGINNAME] NVARCHAR(255) ,
[HostName] NVARCHAR(255) ,
[Blk] NVARCHAR(255) ,
[DBName] NVARCHAR(255) ,
[Cmd] NVARCHAR(255) ,
[REQUESTID] INT)
END
go
INSERT INTO #who EXEC sp_who ;

You can then recall this information at any time by running
SELECT * FROM [#who]

If your headache is blocking flavoured then running this will show you which SPID is the cause:

SELECT
CAST([w].[Status] AS NVARCHAR(30)) AS [status],
[w].[LOGINNAME] ,
[w].[SPID] ,
[w].[ECID] ,
[w].[HostName] ,
[w].[Cmd] ,
[w].[REQUESTID] ,
[w].[Blk] AS [Blocked by],
[w].[DBName]FROM
[#who] AS w
ORDER BY [DBName], [blocked BY]

The Blocked by column will have a number in it if that process is being blocked. The value in the column is the SPID of the problem process or, if that process itself has a number in its Blocked By column then it too is being blocked. Keep following the trail until you reach a process that has a zero in its Blocked by column but is being shown as blocking a different process.

Using a temporary table means that the information gathered is only available in the single process and will be dropped when you close the query.

I have this script set up as a SQL Snippet in SQL Prompt so that it is readily available in 3 keystrokes. SQL Prompt is an application that provides shortcuts to SQL Editors, making SQL development simpler and faster from Red Gate. Other editors are available and it is possible to create a script template in SSMS if you are not fortunate enough to have any third party tools working for you.

sp_who is documented in Books Online here so please read more about it there. There is also an undocumented version called sp_who2 that is very similar but has even more information regarding each process that is connecting to your databases.