tracing

https://blog.nordible.com/SQL-Server-Profiler-Best-Practices-and-Tuning-Advisor/ and https://hackernoon.com/sql-server-profiler-best-practices-and-tuning-advisory-d8c746f54809

https://github.com/OleksiiKovalov/expressprofiler

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
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

deadlocks

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
this can be saved from File -> Export -> Extract SQL Server Events -> Extract Deadlock Events…
SELECT object_name([object_id]) from sys.partitions 
WHERE hobt_id = xxxxxx

Comments