SQL SERVER – Identify Most Resource Intensive Queries – SQL in Sixty Seconds #028 – Video

SQL SERVER - Identify Most Resource Intensive Queries - SQL in Sixty Seconds #028 - Video sixtysecs During performance tuning conversation the very first question people often ask is what are the queries offending the server or in another word let us identify the queries which are the most resource intensive. The resources are often described as either Memory, CPU or IO. When we talk about the queries the same is applicable for them as well. The query which is doing lots of reads or writes are for sure resource intensive as well query which are taking maximum CPU time.

Performance tuning is a very deep subject and we all have our own preference regarding what should be the first step to tuning and what should be looked with the salt of grain. Though there is no denying that a query which uses more resources than what it should be using for sure require tuning. There are many ways to do identify query using intense resources (e.g. Extended events etc) but in this one we will go by simple DMV.

There is a small gotcha we all have to remember about usage of DMV is that it only brings back results from existing cache. So if you have a query which is very resource intensive but is not cached or if you have explicitly removed the query from the cache it will be not part of the result returned by this DMV. It is quite possible that a query is aged and removed from the cache if your cache is not huge. If your cache is large you may want to be careful in running this query during business hours as this query itself can be resource intensive.

Get Script to identify resource intensive query from Here

Related Tips in SQL in Sixty Seconds:

Reference: Pinal Dave (https://blog.sqlauthority.com)

Database, SQL in Sixty Seconds, SQL Scripts, SQL Server Management Studio
Previous Post
SQL SERVER – Solution – 2 T-SQL Puzzles – Display Star and Shortest Code to Display 1
Next Post
SQL SERVER – Importance of User Without Login – T-SQL Demo Script

Related Posts

4 Comments. Leave new

  • Please let me know will it return SQL Query statement which is available in Stored procedure

  • sir,
    i have a problem while using DTS.
    When we transfer tables from one database of a server to other database of another server then it will not create their constraints like Identity coloumn, primary key, default values
    and then we can do it manually which is very hactic & problematic.


  • Guruprasad Balaji
    October 4, 2012 2:38 am

    Hi Pinal,
    Im really little confused how this works, that is, I have SQL Server 2012 Adv. Exp. Edition installed with “Default Instance” and SQL Server 2008 Adv. Exp. Edition installed with SQLEXPRESS instance on mixed mode auth. Now im able to explore all the databases that has been attached to the Default Instance over SQL Server 2008 R2s SSMS and able to access them too. So is this a feature or a bug or something? Can i trust this and keep working on 2008 R2’s SSMS or will it fail oneday? Plz. throw some light upon this and kindly make me clear about it.
    Thanks in advance.

  • jayesh gangrade
    October 15, 2012 3:49 pm

    Jayesh Gangrade
    Hi pinal,

    please give me solution of following error

    The TCP/IP connection to the host, port 1433 has failed. Error: “Address already in use: connect. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.”.
    com.websym.common.exception.DBException: org.hibernate.exception.JDBCConnectionException: Cannot open connection

    Jayesh G


Leave a Reply