CHAPTER 28 ■ PERFORMANCE TUNING
No , I’ll query the sys.dm_exec_query_stats dynamic management vie , which contains statistical information regarding queries cached on the SQL Server instance. This view contains a sql_handle, which I’ll use as an input to the sys.dm_exec_sql_text dynamic management function. This function is used to return the text of a Transact-SQL statement:
SELECT t.text, st.total_logical_reads, st.total_physical_reads, s t . t o t a l _ e l a p s e d _ t i m e / 1 0 0 0 0 0 0 T o t a l _ T i m e _ S e c st.total_logical_writes FROM sys.dm_exec_query_stats st CROSS APPLY sys.dm_exec_sql_text(st.sql_handle) t s ,
SELECT CustomerID, ContactID, Demographics, ModifiedDate FROM Sales.Individual
t o t a l l o g i c a l _ r e a d s
t o t a l _ p h y s i c a l _ r e a d s T o t a l _ T i m e _ S e c s
total_ logical writes 0
How It Works
This recipe demonstrated clearing the procedure cache, then executing a query that took a few sec- onds to finish executing. After that, the sys.sm_exec_query_stats dynamic management view was queried to return statistics about the cached execution plan.
The SELECT clause retrieved information on the Transact-SQL text of the quer , number of logical and physical reads, total time elapsed in seconds, and logical writes (if any):
SELECT t.text, st.total_logical_reads, st.total_physical_reads, s t . t o t a l _ e l a p s e d _ t i m e / 1 0 0 0 0 0 0 T o t a l _ T i m e _ S e c st.total_logical_writes s ,
The total elapsed time column was in microseconds, so it was divided by 1000000 in order to return the number of full seconds.
In the FROM clause, the sys.dm_exec_query_stats dynamic management view was cross applied against the sys.dm_exec_sql_text dynamic management function in order to retrieve the SQL text of the cached query:
FROM sys.dm_exec_query_stats st CROSS APPLY sys.dm_exec_sql_text(st.sql_handle) t
This information is useful for identifying read- and/or write-intensive queries, helping you determine which queries should be optimized. Keep in mind that this recipe’s query can only retrieve information on queries still in the cache.
This query returned the totals, but the sys.dm_exec_query_stats also includes columns that track the min, max, and last measurements for reads and writes. Also note that the sys.dm_exec_query_stats has other useful columns that can measure CPU time (total_worker_time, last_worker_time, min_worker_time, and max_worker_time) and .NET CLR object execution time (total_clr_time, last_clr_time, min_clr_time, max_clr_time).