Investigation for Slow SQL Query : SQL Profiler & Server Side Traces


Its really very time consuming to identify the slow query running on the MS SQL server earlier we were not aware about the tools but after SQL Server Profiler we got some understanding for query analysis . We will be following below approach for carrying out the investigation :

  1. Capture a profiler trace for a short period while the server is active. This will identify the queries that constitute a typical workload.
  2. Import the trace results into a database table and analyse them to find the queries or stored procedures that are having the most impact on the server.
  3. Run those stored procedures in Management Studio against a test database, and examine the execution plans and query statistics.
  4. Use the execution plan and statistics to identify queries that need tuning and indexes that need creating.
  5. Implement the changes and review the effects.

Server side traces can also be used in case of profiler as it consumes more memory on the busy SQL server. To collect the server side traces we will be tracing below events in Trace Script :

  • The SQL Server events that you would like to capture
  •  The data columns that you would like to capture for each event
  • Any filters that you would like to create, in order to omit from the trace any occurrences of the event in which you are uninterested. For example, it is common to capture only those events that are generated by a particulate user, application or database.

In order to generate the trace definition, I must first create a trace within Profiler that defines all of the events, columns and filters that I want. For identifying poorly performing stored procedures, the most important events are:

  • RPC:Completed (in the stored procedures event category) This event is fired whenever a remote procedure call completes. An example of a Remote Procedure Call would be a stored procedure executed from a .NET application where the SQLCommandobject’s command type is StoredProcedure.
  • TSQL:BatchCompleted (in the T-SQL event category). This event is fired whenever an ad-hoc SQL batch completes executing. An example of an ad-hoc SQL batch would be a query run  from Management Studio, or a query executed from a .net application where the SQLCommand object’s command type is Text.

The most important data columns are TextData, CPU, Reads, Writes and Duration. Other columns, such as the LoginName,ApplicationName and HostName, may be useful for identifying where the query comes from, but they are not essential for identifying which queries are performing poorly.

 

 

Advertisements

About Devendra Singh

A guy with self learning capability , Analytical Ability , Exploring in nature , Just looking for ways for spreading happiness among everyone. Little aggressive some time. Just want peace everywhere in this world.
Aside | This entry was posted in Big Data, Database and tagged , , , , , , . Bookmark the permalink.

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