SQL Server Profiler Best Practices and Tuning Advisory

SQL Profiler is a great tool that allows you to see what’s going on inside SQL Server. You can find out what your worst performing queries are, how often a query is executed etc.

To implement best posible indexes do the 3 step process:

• Collect current workload information
• Determine if indexes are appropriate as per work load using Profiler
• Update indexes using Tuning advisor

The profiler automates collection of workload data and the tuning advisor takes the workload data generated by the profiler and come up with appropriate indexes. To trace on specific database use the column filter feature. Start the trace with tuning template and save the trace data to a trace file.

• Click on tools
• Click Database Engine Tuning advisor
• Provide the trace file
• Select the database on which the trace needs to be executed
• In the tuning options, go to advanced options maximum space for recommendation can be specified
• Click Start analysis

The results shows how much improvement can be done if the suggestions are implemented:

It has a definition column as well which shows what to implement to improve speed of the database like for example:

The SQL Profiler best practices:

• Never run SQL Profiler on the database server
• Trace directly to a file, instead of tracing to a table
• Filter the results, such as “Duration > 1000”
• Include only the events that you are interested in, such as SP:Completed and SQL:BatchCompleted
• Use filters like user name, server name & application name, etc
• Run SQL Profiler on a remote system
• Save trace events to a rollover file of a predefined size
• Don’t capture showplan event for long periods of time to avoid very large files
• Create templates for quick access
• For tuning purposes, use the recommended tuning template

How to capture deadlocks using SQL Server Profiler:

To capture a deadlock, first connect to a SQL Server database. To open the SQL Profiler in SQL Server Management Studio:

• Click on Tools
• Click on SQL Server Profiler
• Connect to the server on which we need to perform profiling
• On the Trace Properties window, under General tab, select the blank template
• On the Events Selection tab, select Deadlock graph under Locks leaf

Let’s execute the below queries:

In the following order:

1. Execute the query in the blue rectangle
2. Execute the query in green rectangle
3. Execute the query highlighted in grey
4. Execute the query highlighted in blue

Notice that the process ids are shown on the information bar in SQL Server:

The dead lock will be generated on execution of the 4th step:

For later analysis, this can be saved from File -> Export -> Extract SQL Server Events -> Extract Deadlock Events…

Analyzing the deadlock graph:

1. Oval with a blue cross represents the transaction/process that was chosen as the deadlock victim by SQL Server

2. The ovals represents the processes, the one without cross represents the transaction/process which completed successfully

3. The deadlock priority is set to default i.e. 0

4. We also have log used, this represents the transactional log used. If the transaction has done a lot of updates, the log size will be larger. Hence to roll the a transaction which has done a large number of updates would take a lot of cost. In our case, the deadlock victim is the one with less transaction log, because that would take less cost. The rectangles represents the resource nodes.

5. The rectangles represents the resource nodes.

6. The HoBtID (heap or binary tree id) associated with the resource node is used to find the database object involved in the deadlock from sys.partitions view by using the following query:

   SELECT object_name([object_id]) from sys.partitions WHERE hobt_id = xxxxxx

7. The arrows represent the types of locks we have on each resource node

8. The notations X and S on the arrows represents the exclusive and shared locks

Sources:

• SQL Server Profiler Best Practices
• Improve SQL Server performance using profiler and tuning advisor
• Photos

Originally published at xameeramir.github.io.