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
[youtube=http://www.youtube.com/watch?v=TvlYy-TGaaA]
Related Tips in SQL in Sixty Seconds:
- SQL SERVER – Find Most Expensive Queries Using DMV
- Simple Example to Configure Resource Governor – Introduction to Resource Governor
- SQL SERVER – DMV – sys.dm_exec_query_optimizer_info – Statistics of Optimizer
- SQL SERVER – Wait Stats – Wait Types – Wait Queues – Day 0 of 28
Reference: Pinal Dave (https://blog.sqlauthority.com)
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.
Thanks
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
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