Just recently, my local .NET user group asked me to present at their meeting on the topic of graphical execution plans. I love presenting for developers on SQL Server topics. I’m often describing something well known by database administrators, but completely new to the devs. I think the group learned a lot from me that evening and were glad they attended.

Microsoft provide graphical execution plans to show just what is going on behind the scenes when a query runs. For example, the plan tells you which tables, indexes, or views were accessed and how joins are physically performed. It’s not a mystery once you know what is going on.

Many people working with T-SQL don’t think they need to care about execution plans. As long as the query returns the expected results, that’s good enough. The problems begin, however, when just adding a column to a query causes it to slow down, sometimes slowing it down to an extreme degree. You wouldn’t think adding a column would make such a difference, but it can cause the optimizer to come up with a completely different plan.

In typical application development, the code tells the computer what to do. SQL queries, on the other hand, are declarative. You tell SQL Server what you want. It’s up to the optimizer to figure out how to do it. A single query can be pages and pages long, made up of several levels of nested subqueries, common table expressions, unions, complex WHERE clauses with even more subqueries, calculations, and more. Imagine writing an algorithm to make sense of that! There are thousands of possible plans, and the optimizer must try to find the best one in milliseconds.

Another thing that developers may not realize is that using LINQ or another tool for database access still ends up creating T-SQL queries. Often, these queries are less than optimal, and graphical execution plans can help you figure that out.

It’s very frustrating when the query runs slowly in the application but runs fast when you copy and paste it into SSMS (SQL Server Management Studio) or SQL Operations Studio. It’s likely that the optimizer has come up with a completely different plan in SSMS due to varying settings or parameter values. This is where a monitoring tool for SQL Server can really come in handy. These tools (one of them is Redgate’s SQL Monitor) will show you the worst performing queries and provide the actual execution plans to you without your attempts to recreate them.

There were over 40 developers in my session, and only a couple of them knew about graphical execution plans before the talk. When you don’t understand execution plans, SQL Server can be a total mystery. The performance of queries becomes unpredictable. Yet, everything can be explained when you know where to look.