The sys.dm_exec_query_stats DMV only started exposing row-count information as on Denali and so the SQL pasted below is only applicable to SQL Server 2012 and later.
Reading through online performance monitoring tools brought me once again to MS SQL Server’s Dynamic Management Views; specifically sys.dm_exec_query_stats:
select tblQueryStat.total_worker_time , tblQueryStat.total_elapsed_time , tblQueryStat.total_rows , tblQueryStat.total_physical_reads , tblQueryStat.total_logical_reads , tblQueryStat.total_logical_writes , tblSQLText.text , [numberofRowsEstimated] = tblQueryPlan.[query_plan].value ( '(//@EstimateRows)' , 'varchar(128)' ) , tblQueryStat.last_rows as nunberofRowsActual , tblQueryStat.execution_count , tblQueryStat.plan_generation_num from sys.dm_exec_query_stats tblQueryStat cross apply sys.[dm_exec_sql_text](tblQueryStat.sql_handle) as tblSQLText cross apply sys.[dm_exec_query_plan](tblQueryStat.plan_handle) as tblQueryPlan order by tblQueryStat.total_worker_time desc
Our results confirmed what we knew all along:
By far our biggest culprit was this one query.
But, also was able to confirm the following:
- That we have a lot of parallelism going on — this is confirmed due to the fact that the total_worker_time is a bit greater than total_elapsed_time
- We were not terribly skewed per estimated number of rows and actual number of rows. If actual number of rows were not aligned as to estimated number of rows, then it will be advisable to update the statistics
- Execution Count is also note worthy
- As is plan_generation_num — when high it means the query plan is constantly being re-generated. When so, see about using Stored Procedures. And, if within SP’s see if there are statements within you SP that is causing the query plans to be re-generated
Confessionally, I wish life was good. And, I could have as much fun @ work as say “Joshua Radin”.
Joshua Radin – In her eyes
- Measuring IO Wait
- Detecting IO Cardinality Issues with sys.dm_exec_query_stats