One of my favorite scripts which I discuss with my client during Comprehensive Database Performance Health Check is how to get a list of Expensive Queries.
If you have watched my following video, you probably know that it is easy to tune query if you know what resources, you want to tune for.
Here is the query which will help you find queries expensive for various resources – CPU, IO as well as for Elapsed time. If you pay attention there are a few commented ORDER BY clauses, you can use each of them to find resource expensive queries.
SELECT TOP(50) qs.execution_count AS [Execution Count], (qs.total_logical_reads)*8/1024.0 AS [Total Logical Reads (MB)], (qs.total_logical_reads/qs.execution_count)*8/1024.0 AS [Avg Logical Reads (MB)], (qs.total_worker_time)/1000.0 AS [Total Worker Time (ms)], (qs.total_worker_time/qs.execution_count)/1000.0 AS [Avg Worker Time (ms)], (qs.total_elapsed_time)/1000.0 AS [Total Elapsed Time (ms)], (qs.total_elapsed_time/qs.execution_count)/1000.0 AS [Avg Elapsed Time (ms)], qs.creation_time AS [Creation Time] ,t.text AS [Complete Query Text], qp.query_plan AS [Query Plan] FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK) CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp WHERE t.dbid = DB_ID() ORDER BY qs.execution_count DESC OPTION (RECOMPILE);-- frequently ran query -- ORDER BY [Total Logical Reads (MB)] DESC OPTION (RECOMPILE);-- High Disk Reading query -- ORDER BY [Avg Worker Time (ms)] DESC OPTION (RECOMPILE);-- High CPU query -- ORDER BY [Avg Elapsed Time (ms)] DESC OPTION (RECOMPILE);-- Long Running query
Once you find the expensive queries based on your requirement, you can go to the last column of this query and open the execution plan and start tuning the query. If you need further help you can always reach out to me or just post comments.
Here are my few recent videos and I would like to know what is your feedback about them. You can subscribe to my youtube channel here.
- Case-Sensitive Search – SQL in Sixty Seconds #158
- Wait Stats for Performance – SQL in Sixty Seconds #157
- Multiple Backup Copies Stripped – SQL in Sixty Seconds #156
- Forwarded Records and Performance – SQL in Sixty Seconds #155
- Hide Code in SSMS – SQL in Sixty Seconds #154
- Zoom in SSMS – SQL in Sixty Seconds #153
- Transfer Schema of Table – SQL in Sixty Seconds #152
- Find a Table in Execution Plan – SQL in Sixty Seconds #151
- Transactions and Variables – SQL in Sixty Seconds #150
- Count Table in Cache – SQL in Sixty Seconds #149
- List All Sessions – SQL in Sixty Seconds #148
- Line Numbers for SSMS Efficiency – SQL in Sixty Seconds #147
- Slow Running Query – SQL in Sixty Seconds #146
- Change Database and Table Collation – SQL in Sixty Seconds #145
- Infinite Loop – SQL in Sixty Seconds #144
- Efficiency Trick – Query Shortcut – SQL in Sixty Seconds #143
- SQL SERVER – 16 CPU vs 1 CPU : Performance Comparison – SQL in Sixty Seconds #142
- SQL SERVER – TOP and DISTINCT – Epic Confusion – SQL in Sixty Seconds #141
Reference:Â Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)
