How to improve SQL Server Execution Plan Reuse

10 November,2012 by Tom Collins

 Before the first time a query is run , SQL Server Query Optimizer compiles a cost effective execution plan. Compiling the plan is CPU cycle expensive. Maximising Execution Plan Reuse helps query response time.

Recompilations are normal in SQL Server. For example these conditions will make  an Excecution Plan invalid and cause a recompile. From BOL

1) Changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW).

2)Changes to any indexes used by the execution plan.

3)Updates on statistics used by the execution plan, generated either explicitly from a statement, such as UPDATE STATISTICS, or generated automatically.

4)Dropping an index used by the execution plan.

How to identify excessive recompilations and troubleshoot

1)    Find statements with Recompilations –   This query returns the top 5 statements based on plan generation number

        
  SELECT TOP 5
qst.plan_generation_num,
qst.execution_count,
stm.TEXT,
DB_NAME(stm.dbid) AS DbName,
stm.objectid

FROM sys.dm_exec_query_stats qst
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS stm
ORDER BY qst.plan_generation_num DESC

 

Another useful tool is  Profiler .Check the SQL:StmtRecompile event class

2)    Identify the lists of queries with high levels of recompilations. Are they Adhoc or Dynamic Queries?

3)    If Adhoc or Dynamic Queries exist, rewrite the query . Try and write as Prepared Statements, Stored Procedures or sp_executesql

4)    If the list of queries with recompilations are not Adhoc or Dynamic queries but the levels of recompilations remain high, review the query for a rewite. Try and write as Prepared Statements, Stored Procedures or sp_executesql. If that doesn’t improve the recompilation levels – consider using the PARAMETERIZATION between forced and simple

 Read More

SQL Server execution plan as text - SQL Server DBA

SQL Server – SQL Execution plan and SET SHOWPLAN_XML ...

SQL Server Query Optimizer and Statistics

 


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to improve SQL Server Execution Plan Reuse


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer