SQLServerCentral Article

SQL Server Execution Plans


High Performance SQL Server Series
SQL Server Execution PlansSQL Server Execution Plans by Grant Fritchey

(1st Edition, June 2008)

Download eBook (PDF): Free
Buy Printed Book (Amazon) : $19.79
Buy Kindle Edition : Coming Soon

" Every day, out in the various SQL Server forums, the same types of questions come up again and again: why is this query running slow? Why isn't my index getting used? And on and on. In order to arrive at the answer you have to ask the same return question in each case: have you looked at the execution plan? " – Grant Fritchey

View Table of Contents
View Sample Chapter

View the Code

View Related Books:

Editorial Review

Execution plans show you what's going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by SQL Server, including:

  • Which indexes are getting used and where no indexes are being used at all.
  • How the data is being retrieved, and joined, from the tables defined in your query.
  • How aggregations in GROUP BY queries are put together.
  • The anticipated load, and the estimated cost, that all these operations place upon the system.

All this information makes the execution plan a fairly important tool in the tool belt of database administrator, database developers, report writers, developers, and pretty much anyone who writes TSQL to access data in a SQL Server database.

Given the utility and importance of the tool, you'd think there'd be huge swathes of information devoted to this subject. To be sure, fantastic information is available from various sources, but there really isn't any one place you can go to for focused, practical information on how to use and interpret execution plans.

This is where my book comes in. My goal was to gather as much useful information on execution plans as possible into a single location, and to organize it in such as way that it provided a clear route through the subject, right from the basics of capturing plans, through their interpretation, and then on to how to use them to understand how you might optimize your SQL queries, improve your indexing strategy, and so on.

Specifically, I cover:

  • How to capture execution plans in graphical, as well as text and XML formats
  • A documented method for interpreting execution plans, so that you can create these plans from your own code and make sense of them in your own environment
  • How SQL Server represents and interprets the common SQL Server objects – indexes, views, derived tables etc – in execution plans
  • How to spot some common performance issues such as bookmark lookups or unused/missing indexes
  • How to control execution plans with hints, plans guides and so on, and why this s a double-edged sword
  • How XML code appears in execution plans
  • Advanced topics such as parallelism, forced parameterization and plan forcing.

Along the way, I tackle such topics as SQL Server internals, performance tuning, index optimization and so on. However, my focus is always on the details of the execution plan, and how these issues are manifest in these plans. If you are specifically looking for information on how to optimize SQL, or build efficient indexes, then you need a book dedicated to these topics. However, if you want to understand how these issues are interpreted within an execution plan, then this is the place for you.

About the Author

Grant is currently working as a development DBA for FM Global, an industry-leading engineering and insurance company. In his time as a DBA, he has worked at three failed dotcoms, a major consulting company and a global bank. He has developed large scale applications in languages such as VB, C# and Java and has lived with SQL Server from the hoary days of 6.0, right through to 2008. His nickname at work is "The Scary DBA". He even has an official name plate, and he displays it proudly.

Grant volunteers for the Professional Association of SQL Server Users (PASS) and has written and published articles on various topics relating to SQL Server at Simple-Talk, SQL Server Central, the PASS web site, SQL Standard and the SQL Server Worldwide Users Group. He is one of the founding officers of the Southern New England SQL Server Users Group (SNESSUG). Outside work, Grant kayaks, learns and teaches self-defense, brews his own beer, chops wood to heat his house, raises his kids and helps lead a pack of Cub Scouts.

Book Details

Paperback: 250 pages
Publisher: Red Gate Books
ISBN: 978-1-906434-02-1


4.93 (15)

You rated this post out of 5. Change rating




4.93 (15)

You rated this post out of 5. Change rating