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.

Execution cache

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.

sys.dm_exec_query_stats
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

The simple example in this article is very powerful in terms of usefulness. At any point, I can use the query to see how well my database is caching statements for reuse. I can use this same query to example the execution plans of these statements and make any tuning decisions based on that information.

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 chapman.tim@gmail.com.


—————————————————————————————–

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!