Ways to identify the worst performing queries

          

Interview questions

SQL Server - Ways to identify the worst performing queries

Next>>         SQL Server tutorial  

SQL Server - ways to identify the worst performing queries - Feb 27, 2010 at 11:50 AM by Shuchi Gauri

What are the ways to identify the worst performing queries in my SQL Server database application?

SQL Server Profiler can help in identifying worst performing queries. Create a new trace using the following steps:

  • Select SQL:BatchCompleted event class.
  • Select Groups->duration
  • Select EventClass, TextData, CPU, Application Name, LoginName, NTUserName, SPID as columns.
  • Select Application Name, DatabaseName, Duration as filters.

This trace will provide a result of queries sorted as per execution time in increasing order.

SQL Server - ways to identify the worst performing queries - May 05, 2009 at 22:00 PM by Rajmeet Ghai

What are the ways to identify the worst performing queries in my SQL Server database application?

Worst performing queries in my SQL Server database application can be identified using Dynamic Management views and Dynamic Management Functions. It helps to find the internal working of SQL instances and database objects. sys.dm_exec_query_stats, sys.dm_exec_cached_plans, sys.dm_exec_sql_text, and sys.dm_exec_plan_attributes are the different types that return the statistics for T-SQL statements executed on the instance. These types are used using simple select statements. The output shows statitcis of the SQL staments in terms of total CPU that a given T-SQL statement has consumed for all executions, total number of physical read, logical writes, total amount of elapsed time etc. 

SQL Server - ways to identify the worst performing queries - June 21, 2009 at 09:00 AM by Amit Satpute

The SQL Server Profiler makes the task of identifying the worst performing queries easier.
The following configurations need to be used to create a trace:

Event Classes:
1. SQL:BatchCompleted

Data Columns:
1. Groups
          Duration           
2. Columns
          EventClass         
          TextData
          CPU 
          Application Name
          LoginName
          NTUserName 
          SPID
Filters:
1. Application Name
2. DatabaseName
3. Duration

The duration of each query execution is sorted in ascending order of duration. 
The longest running queries are at the bottom due to which they can be easily identified.   


 


Next>>

Also read

Define Distributed Query and Linked Server?

Answer - Distributed Query is a query which can retrieve data from multiple data sources including distributed data........

SQL Server Optimization Tips

Answer - Restricting query result means return of required rows instead of all rows of the table. This helps in reducing network traffic......

What is SQL Server English Query?

Answer - SQL Server English Query helps to build applications that can accept query.....

What is the purpose of SQL Profiler in SQL server?

Answer - SQL Profiler captures SQL Server events from a server. The events are saved.....



Write your comment - Share Knowledge and Experience


 

 
Latest placement tests
Latest links
 
Latest MCQs
» General awareness - Banking » ASP.NET » PL/SQL » Mechanical Engineering
» IAS Prelims GS » Java » Programming Language » Electrical Engineering
» English » C++ » Software Engineering » Electronic Engineering
» Quantitative Aptitude » Oracle » English » Finance
Home | About us | Sitemap | Contact us | We are hiring