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:

Execute the query in the blue rectangle
Execute the query in green rectangle
Execute the query highlighted in grey
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:

Oval with a blue cross represents the transaction/process that was chosen as the deadlock victim by SQL Server
The ovals represents the processes, the one without cross represents the transaction/process which completed successfully
The deadlock priority is set to default i.e. 0
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.
The rectangles represents the resource nodes.
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
The arrows represent the types of locks we have on each resource node
The notations X and S on the arrows represents the exclusive and shared locks

Sources:

Originally published at xameeramir.github.io.