BI SQL # 111 : SQL Server Script : Optimization : Find top N heavy and time consuming SQL operation at runtime at any time
Hi all
Now a days we need to optimized everything, Like our life traveling, Study , Office work .. Everything , Everywhere optimization is required.
But I am not life expert but surely know something about SQL Server.
In this article we are going to cover
- SQL Script to Find top N heavy and time consuming SQL operation at runtime at any time
- Query Result , output of given script
- Example of SQL Script to Find top N heavy and time consuming SQL operation at runtime at any time
So here are some quick way to find at runtime heavy and time consuming SQL operation by just firing query.
SQL Script
--Specify TOP N = Top 10 , number of query you wants to Identify DECLARE @TopNHeavyOperation INT = 10 SELECT TOP (@TopNHeavyOperation) ( SELECT SUBSTRING(est.[text], statement_start_offset / 2 + 1, ( CASE WHEN statement_end_offset = - 1 THEN LEN(CONVERT(NVARCHAR(max), est.[text])) * 2 ELSE statement_end_offset END - statement_start_offset ) / 2) FROM sys.dm_exec_sql_text(sql_handle) AS est ) AS query_text, total_logical_reads / execution_count AS [avg_logical_reads], qp.query_plan, total_logical_writes / execution_count AS [avg_logical_writes], total_worker_time / execution_count AS [avg_cpu_cost], execution_count, total_worker_time, total_logical_reads, total_logical_writes, ( SELECT DB_NAME(dbid) + ISNULL('..' + OBJECT_NAME(objectid), '') FROM sys.dm_exec_sql_text([sql_handle]) ) AS query_database, last_logical_reads, min_logical_reads, max_logical_reads, last_logical_writes, min_logical_writes, max_logical_writes, total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads, (total_logical_reads + (total_logical_writes * 5)) / execution_count AS io_weighting, plan_generation_num --into ExpensiveIOoperations FROM sys.dm_exec_query_stats OUTER APPLY sys.dm_exec_query_plan([plan_handle]) AS qp WHERE [dbid] >= 5 AND (total_worker_time / execution_count) > 100 ORDER BY io_weighting DESC;
Query Result
This query capture following 22 aspect in run time
Query output :
This is most useful SQL script for DBA to identify any running queries at any time.
Top N is configurable as per user , as this query is somewhat time consuming.
We can also see Query plan in the output of this query.
Hope you will like to Find top N heavy and time consuming SQL operation at runtime at any timeDrop Database .
If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.
If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :
For More information related to BI World visit my Mentalist Blog
SQL Server Mentalist >> SQL Learning Blog
Business Intelligence Mentalist >> BI World
Connect With me on
SQL Server Mentalist 