In SQL Server 2005, you only need to recompile the statements inside stored procedures. This is much different than SQL Server 2000, where the entire procedure or batch may be recompiled. The benefit to the statement level recompile in SQL Server 2005 is that typically just a few statements inside of a batch or procedure are necessary for the database engine to recompile. This allows other statements to skip the recompile process when it is not necessary.
Before a statement is executed in SQL Server 2005, the database engine compiles a plan to find the best way to run the statement. SQL Server 2005 tries to reuse the plan as much as possible for speed and efficiency purposes and stores the plan in the procedure cache. The database engine maintains this cache and uses, inserts, and removes plans as necessary. You can query this execute plan cache through the use of dynamic management views (DMVs). You can also get a powerful amount of information in terms of the frequency and performance of the statements occurring on your database machine.
In my SQL Server 2005 Profiler article, I describe how you can capture and examine execution plans for statements as they execute on the database server. I will expand on that technique and explain the execution plans of statements as they exist in the procedure cache. This allows me to see how the most frequently used queries are performing and gives me insight into how to tweak them if necessary.
The DMV that I will use to look at the execution plans for statements in the procedure cache is sys.dm_exec_query_stats. It contains aggregated data for all statements in the cache; data such as the number of times the statement has been executed, the longest amount of time it took for the statement to execute, the maximum number of reads incurred by the statement, etc. It also contains a hash to data such as the execution plan and SQL statement being run. With a little more scripting, I can even see what the procedure name is and the database where it exists.
Finding statement plans
The following query returns the TOP 100 execution plans on my database system based upon the number of times that statement has been executed from the procedure cache. This will help me understand what procedures are being kept by SQL Server in the procedure cache.
In the query, I use sys.dm_exec_query_stats DMV, the CROSS APPLY operator, and the functions sys.dm_exec_sql_text and sys.dm_exec_query_plan to find the statement in the cache and the execution plan being used. (Note: Make sure you are running SQL Server 2005 Service Pack 2 for this example, as the OBJECT_NAME function has changed slightly to allow you to supply a database to the function for the accompanying lookup. If you are not using Service Pack 2, simply remove the second parameter from the OBJECT_NAME function.)
SELECT TOP 100 qs.execution_count, DatabaseName = DB_NAME(qp.dbid), ObjectName = OBJECT_NAME(qp.objectid,qp.dbid), StatementDefinition = SUBSTRING ( st.text, ( qs.statement_start_offset / 2 ) + 1, ( ( CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset ) / 2 ) + 1 ), query_plan, st.text, total_elapsed_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) qp WHERE st.encrypted = 0 ORDER BY qs.execution_count DESC
If I want to find the XML execution plan for the statement in the procedure cache, I click the link in the query_plan field; this will create an XML document that I can review. The beauty of this XML document is that I can save it as a .sqlplan file, which I can then open in SQL Server 2005 Management Studio to view the graphical execution plan. (My previous article offers a detailed example of how to save these XML files as .sqlplan documents.)
Simple yet powerful
Tim Chapman a SQL Server database administrator and consultant who works for a bank in Louisville, KY. Tim has more than eight years of IT experience, and he is a Microsoft certified Database Developer and Administrator. If you would like to contact Tim, please e-mail him at firstname.lastname@example.org.
Get database tips in your inbox
TechRepublic’s free Database Management newsletter, delivered each Tuesday, contains hands-on SQL Server and Oracle tips and resources. Automatically subscribe today!