Tools and commands for recompilation

1.SQL Prifiler: Captures events of the event type SP and T-SQL (starting, stmtcompleted, Recompile, completed, CacheInsert, CacheHit, CacheMiss) can find the reason for recompilation.

The 2.SQLServer system is used to view the DMV that analyzes the execution plan, such as:

• Select St.text,cp.plan_handle,cp.usecounts,cp.size_in_bytes,cp.cacheobjtype,cp.objtype

From Sys.dm_exec_cached_plans CP

Cross apply Sys.dm_exec_text (Cp.plan_handle) St ORDER by cp.usecounts Desc

--Column: St.text: The SQL text of the query plan that is generated in the query.

Cp.usecounts: Number of times to reuse a scheduled query.

Cp.size_in_bytes: The number of bytes used in the query plan.

Cp.cacheobjtype: The type of the cached object.

• Select *from sys.dm_exec_cached_plans --Query cache execution plan
• select* from sys.dm_os_memory_cache_counters--Query cache object related information
• 20 query statements that take the longest time to find a database by executing the plan cache

SET TRANSACTION Isolation Level READ UNCOMMITTED

SELECT TOP 20

CAST (qs.total_elapsed_time/1000000.0 as DECIMAL (28, 2))

As [total Duration (s)]

, CAST (Qs.total_worker_time * 100.0/qs.total_elapsed_time

As DECIMAL (2)) as [% CPU]

, CAST ((qs.total_elapsed_time-qs.total_worker_time) * 100.0/

Qs.total_elapsed_time as DECIMAL (2)) as [% waiting]

, Qs.execution_count

, CAST (Qs.total_elapsed_time/1000000.0/qs.execution_count

As DECIMAL (2)) as [Average Duration (s)]

, SUBSTRING (Qt.text, (QS.STATEMENT_START_OFFSET/2) + 1,

(case when qs.statement_end_offset =-1

Then LEN (CONVERT (NVARCHAR (MAX), Qt.text)) * 2

ELSE Qs.statement_end_offset

End-qs.statement_start_offset)/2) + 1) as [individual Query

, Qt.text as [Parent Query]

, Db_name (qt.dbid) as DatabaseName

, Qp.query_plan

From sys.dm_exec_query_stats QS

Cross APPLY Sys.dm_exec_sql_text (qs.sql_handle) as Qt

Cross APPLY sys.dm_exec_query_plan (qs.plan_handle) QP

WHERE qs.total_elapsed_time > 0

ORDER by Qs.total_elapsed_time DESC

• Sys.dm_exec_cached_plans: contains the cached execution plan, one row for each execution plan.
• sys.dm_exec_plan_attributes: This is a system function, and each execution plan corresponds to a number of attributes, which are included in this system function.
• sys.dm_exec_sql_text: This is a system function that returns the execution plan for the text format.
• sys.dm_exec_query_plan: This is a system function that returns an XML-formatted execution plan.

3. Virtual tables

• SQL Server 2008 also provides a compatibility view of Sys.syscacheobject, which holds all the information about the execution plan.

Tools and commands for recompilation