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 :
- Capture a profiler trace for a short period while the server is active. This will identify the queries that constitute a typical workload.
- 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.
- Run those stored procedures in Management Studio against a test database, and examine the execution plans and query statistics.
- Use the execution plan and statistics to identify queries that need tuning and indexes that need creating.
- 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.