Skip Navigation Links
xFDN Home
Contributions

SQL Server Profiler Log Analysis

Author: Chetan Bhattad

SQL Server Profiler shows how Microsoft SQL Server resolves queries internally. This allows administrators to see exactly what Transact-SQL statements or Multi-Dimensional Expressions are submitted to the server and how the server accesses the database or cube to return result sets. In this article, I will show how xFusion Studio helps analyze long running queries and store procedures.

Let us first take a quick look at some predefined templates.

SQL Profiler Templates

We can use SQL Server Profiler to create templates that define the event classes and data columns to include in traces. After you define and save the template, you can run a trace that records the data for each event class you selected. Some templates which allow you to easily configure the event classes are
  1. SP_Counts
  2. Standard
  3. TSQL
  4. TSQL_Duration
  5. TSQL_Grouped
  6. TSQL_Locks
  7. TSQL_Replay
  8. TSQL_SPs
  9. Tuning
1.SP_Counts
Purpose: It Captures stored procedure execution behavior over time. It contains events to track how often stored procedures are executed.
Event Classes: SP:Starting

2.Standard:
Purpose: It generates starting point for creating a trace. It captures all stored procedures and Transact-SQL batches that are run. Basically Use to monitor general database server activity. Captures an extensive range of events and data.
Event Classes: Audit Login, Audit Logout, ExistingConnection, RPC:Completed, SQL:BatchCompleted, SQL:BatchStarting

3.TSQL
Purpose: It captures all Transact-SQL statements that are submitted to SQL Server by clients and the time issued. Basically Use to debug client applications. Use to intercept and verify SQL statements for debugging purposes.
Event Classes: Audit Login, Audit Logout, ExistingConnection, RPC:Starting, SQL:BatchStarting

4.TSQL_Duration
Purpose: Captures all Transact-SQL statements submitted to SQL Server by clients, their execution time (in milliseconds), and groups them by duration. Mainly Use to identify slow queries. Event Classes: RPC:Completed, SQL:BatchCompleted

5.TSQL_Grouped
Purpose: Captures all Transact-SQL statements submitted to SQL Server and the time they were issued. Groups information by user or client that submitted the statement. Use to investigate queries from a particular client or user. Event Classes: Audit Login, Audit Logout, ExistingConnection, RPC:Starting, SQL:BatchStarting

6.TSQL_Replay
Purpose: Captures detailed information about Transact-SQL statements that is required if the trace will be replayed. Use to perform iterative tuning, such as benchmark testing. Event Classes: CursorClose, CursorExecute, CursorOpen, CursorPrepare, CursorUnprepare, Audit Login, Audit Logout, Existing Connection, RPC Output Parameter, RPC:Completed, RPC:Starting, Exec Prepared SQL, Prepare SQL, SQL:BatchCompleted, SQL:BatchStarting

7.TSQL_SPs
Purpose: Captures detailed information about all executing stored procedures. Use to analyze the component steps of stored procedures. Add the SP:Recompile event if you suspect that procedures are being recompiled. Event Classes: Audit Login, Audit Logout, ExistingConnection, RPC:Starting, SP:Completed, SP:Starting, SP:StmtStarting, SQL:BatchStarting

8.Tuning
Purpose: Captures information about stored procedures and Transact-SQL batch execution. Use to produce trace output that Database Engine Tuning Advisor can use as a workload to tune databases. Event Classes: RPC:Completed, SP:StmtCompleted, SQL:BatchCompleted

Scenario for using xFusion Studio

  • Find the worst-performing queries.
  • Monitor stored procedure performance.
  • Audit SQL Server activity.
  • Monitoring Transact-SQL activity per user.

Example 1: Find the worst-performing queries

For example, you can create a trace that captures events that relate to the T-SQL and stored procedure event classes (RPC:Completed and SQL:BatchCompleted).
SQL Server Profiler saves trace in table and xFusion Studio can analyse that data. xFusion Studio can apply its own transformation functions to filter out data as per requirement.
If you specify that the duration of the event (must be at least 300 mili-seconds), you can filter by duration to measure query performance.




It shows only those queries that take longer than 300 mili-seconds to run.
Similarly you can use GroupBy function to identify bad queries. By grouping on duration, you can easily see what SQL statements, batches, or procedures are running the slowest. It is very important to look not only at the time when the problem is occurring, but also to get a baseline of when performance is good to compare against.
Once you identify the bad queries, you can work on those individual queries to address the problems.
Note: Your duration times may vary from those shown, and may even appear as zero if the database server has a small load.

If the CPU usage is high, group by CPU to see what queries are the biggest users of CPU time.



If disk IO is the bottleneck, group by reads and writes. View the application name, NT user name, and SQL user name fields to help isolate the source of a long-running query.

Example 2: Monitor stored procedure performance

SQL Server Profiler can create a trace that captures events that relate to Stored Procedure event classes (SP:Completed, SP:Starting, SP:StmtCompleted and SP:StmtStarting), and T-SQL event classes (SQL:BatchStarting and SQL:BatchCompleted).
xFusion Studio can analyze and identify the bad store procedures, so that you can work on those individual procedures to address the problems. If you want to monitor only one database at a time, use the database ID filter to specify a value for the database ID event criterion.




Similarly, if you want to monitor only one stored procedure at a time, use the object ID filter to specify a value for the object ID event criterion



Example 3: Audit SQL Server activity

For example, if the security administrator always needs to know which users are logged on to the server, xFusion can provide a complete record of users that have logged on or off the server. This information can then be used for legal or technical purposes.
Create a trace, selecting the audit login event. To return the appropriate information, specify the following data columns: EventClass (selected by default), EventSubClass, LoginSID, LoginName



Example 4: Only insert records from trace

Once trace is created and if user is interested in whether a table is being accessed by queries(SELECT, INSERT, or DELETE statements), xFusion Studio can filter only those records.



xFusion can give you information on all of the SQL Server Profiler templates in single package.



Analysis of result

Summary of the traces can be sent to the development team on a regular basis, so they will be able to tune the bad performing units.
xFusion Studio can summarize the traced data in few modes, such as:
  • Top X commands order by Duration descending
  • Top X commands order by Reads descending
  • Top X commands order by Writes descending
  • Top X commands order by CPU descending

With regular use of SQL Profiler along with xFusion Studio during development (and a development environment consistent with production in terms of data) you can see the queries that might be problems in the future. We have challanges and they must be managed, xFusion Studio is powerful tool to analyze the trace. Using xFusion Studio, you can analyze bad performing T-SQL in your servers and send the information to the developers on a regular basis allowing them to be pro-active to performance issues.

©2010 Software Labs, Inc.