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

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 (http://blog.sqlauthority.com)

About these ads

6 thoughts on “SQL SERVER – Identify Most Resource Intensive Queries – SQL in Sixty Seconds #028 – Video

  1. 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.

    Thanks

  2. 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.

  3. Jayesh Gangrade
    Hi pinal,

    please give me solution of following error

    The TCP/IP connection to the host 11.01.0.45, 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.”.
    1000
    com.websym.common.exception.DBException: org.hibernate.exception.JDBCConnectionException: Cannot open connection

    Jayesh G

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #049 | Journey to SQL Authority with Pinal Dave

  5. Pingback: SQL SERVER – Root Cause to Performance Problems – Notes from the Field #002 | Journey to SQL Authority with Pinal Dave

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