I was asked a question about this recently, so I thought I’d start this blog off with a brief overview of SQL Server’s execution plans.
Simply put, a query’s execution plan is the query translated into a hierarchical set of operators that the query execution engine understands. The query optimiser is responsible for producing this plan.
A query can have a number of different possible execution plans, depending on the resources available on the server, the amount of data in the source tables, the load on the server.
There are a few ways to get the execution plan of a query. The simplest is to use Query Analyser or SQL Management Studio and retrieve either the estimated execution plan (Ctrl-L), or to run the query with the execution plan option enabled (Ctrl-M)
The estimated execution plan option does not run the query, and hence will be missing some information that is present in the actual execution plan. Mainly this is the actual rows affected and data sizes.
SQL Profiler can be used to capture execution plans both as they are compiled and as they are run. This can be very useful for seeing when procedures are recompiled.
Finally, in SQL Server 2005, the dynamic management view sys.dm_exec_query_plan can be queried to retrieve cached execution plans. A sample query to find all plans in the cache, along with the query and some of its performance stats is
select creation_time, execution_count, total_worker_time, total_logical_reads, total_elapsed_time, query_plan, text from sys.dm_exec_query_stats qs cross apply sys.dm_exec_query_plan(qs.plan_handle) qp cross apply sys.dm_exec_sql_text(qs.sql_handle) st
This query can run for some time on servers with large amount of memory, so take care using it on production servers.
On some complex queries, the xml plan can exceed the maximum permissible depth of 128 nodes. On versions of SQL 2005 prior to service pack 2 this caused an error. In SP2 the query_plan returns null and another plan DMV was introduced in Service Pack 2 that could show the plans even for very complex queries. (sys.dm_exec_text_query_plan)
That’s enough for now. I’ll discuss plan caching and execution plans again in the future.