![Search icon](https://hackernoon.imgix.net/search-new.png?w=19&h=19)
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:
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.
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:
SP:Completed
and SQL:BatchCompleted
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:
Let’s execute the below queries:
In the following order:
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.