Microsoft – SQL Server – System Review – Queries – sys.dm_exec_query_stats
One of things you want to take a look at when reviewing SQL Server Instances are the Dynamic Management Views.
A good DMV is sys.dm_exec_query_stats. It contains the following columns:
/* exec sp_help 'sys.dm_exec_query_stats' */ /* exec sp_help 'sys.dm_exec_query_stats' */ /* exec sp_help 'sys.dm_exec_query_stats' */ select top 50 max(tblSQL.text) as query , sum(tblQueryStat.execution_count) as executionCount , SUM(tblQueryStat.total_physical_reads) as totalPhysicalReads , SUM(tblQueryStat.total_logical_reads) as totalLogicalReads , SUM(tblQueryStat.total_logical_writes) as totalLogicalWrites , sum(tblQueryStat.total_rows) as totalRows , SUM(tblQueryStat.total_worker_time) as totalWorkerTime , SUM(tblQueryStat.total_elapsed_time) as totalElapsedTime , (SUM(tblQueryStat.total_elapsed_time) - SUM(tblQueryStat.total_worker_time)) as diffElapsedTimeAndWorkerTime , convert(varchar(30), max(last_execution_time), 100) as last_execution_time from sys.dm_exec_query_stats tblQueryStat cross apply sys.dm_exec_sql_text (tblQueryStat.sql_handle) tblSQL group by tblQueryStat.plan_handle order by totalElapsedTime desc , totalWorkerTime desc
There are a couple of areas you might be surprised:
- Differences between Elapsed Time (Total time to run query) and Execution time (Time spent by CPU).
- The difference is caused by waits (IO, Parallelism)
Logical Reads and Physical Reads:
- Logical Reads –> Reads request
- Physical Reads –> Reads that were actually conducted against the Disks
- Logical Reads – Physical Reads –> Reads satisfied through other means rather than I/O