sql server identify long running queries

It is always a big challenge to maintain application’s performance optimum.

Increasing data on regular basis is often the cause for performance degrades. In most of the cases end users report the slowness in the application, only then DBA’s/Developers jump in and begin the optimization exercise.

Ideally, DBA’s/Developers supporting the application should be the first in identifying the performance problems and should proactively optimize the faulty/poor code.

Well to acheive this there should be a way to identify the queries taking long time. SQL server has been evolving greatly. SQL server 2005 and above are shipped with very helpful DMV (Dynamic management views). These views expose performance related statistics.

Here is the query using couple of such DMV’s .

This query returns top 10(configurable) slow performing queries.

Either tune the query or analyze it in SQL DTA (database tuning advisor) and follow the recommendations.

 

Regards

Ritesh Medhe

Like us on FaceBook Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook