Home > Microsoft SQL Server, Optimization, Query, Script, SQL, SQL Mentalist, SQL Query, SQL Server, SQL Tips, SQL Tips and Tricks, Technology,, TSQL, Vishal Pawar > BI SQL # 111 : SQL Server Script : Optimization : Find top N heavy and time consuming SQL operation at runtime at any time

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

image

Query output :

image

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 :

Link Resource Website

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

| FaceBook |Twitter | linkedIn| Google+ | WordPress | RSS |

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: