SQL Admin

How to Trace Deadlocks:
SQL Management Studio >> Tools >> SQL Profiler >> log in >> select blank template >> Events Selection >> Locks >> Deadlock graph

Note: the process that uses less "Log" will be chosen as the victim of a deadlock

Select object_name([object_id])
From sys.partitions
Where hobt_id = {hobt_id from profiler}

----------------------------------------------

Tune Index:
Collect workload - use SQL Server Profiler
Determine if indexes are appropriate for workload - Tuning Advisor
Update index

Check indexes:
Right-click table >> Design >> right-click Key >> Indexes >> check "Create as Clustered" field

https://blog.kimconnect.com/sql-server-use-profiler-to-tune-database-indexes/
SQL Profiler >> Use the template "Tuning" >> run to capture >> save as >> Tools >> Database tuning advisor >> browse to select the captured file >> Open >> select the database(s) to optimize >> Start Analysis >> if error, increase the tuning options space >> click on Recommendations >> execute recommendations

----------------------------------------------

--Check for blocking processes
exec sp_who2
Note: look for "BlkBy"
--View locks
exec sp_lock
Note: mode X (exclusive) and Sch-M (schema modification)
--narrow down problematic proccess
DBCC INPUTBUFFER (SPID)
--Kill SPID
kill SPID

--Check execution plan
select * from sys.dm_exec_query_stats
--
select * from sys.dm_exec_query_stats order by creation_time_desc
--
select * from sys.dm_exec_query_stats order by total_worker_time desc
--
select * from sys.dm_exec_query_plan ({plan_handle})

--Narrow down active database(s)
select plan_handle,creation_time,last_execution_time,execution_count,total_worker_time from sys.dm_exec_query_stats
order by total_worker_time desc;
select * from sys.dm_exec_query_plan(0x05000A0090FB8A2C4000E8430700000001000000000000000000000000000000000000000000000000000000);
select db_name (10);

note: execution_count and total_worker_time will be incremented together if SQL statements are static. If SQL queries are dynamic, then new execution plans are created. High worker time with low execution_count is suspect of locks.

--Tool SQL Load Generator

--Reports CPU utiliztion by proccess
Right-click Server node >> Reports >> Standard Reports >> Performance - Top Queries by Total CPU Time (or Average CPU Time)

--Enable trace of deadlocks and blocking in Error Logs


--Hot to check enabled traces on SQL Server
DBCC TRACESTATUS();
GO

--Check whether deadlock traces are ON of OFF
DBCC TRACESTATUS(1244,1222);
GO

--How to turn ON Deadlock Trace
DBCC TRACEON(1204,-1)
DBCC TRACEON(1222,-1)

--How to turn OFF Deadlock Trace
DBCC TRACEOFF(1204,-1)
DBCC TRACEOFF(1222,-1)


--How to turn on blocked processes report (data point every 10 seconds, or 1800 seconds if set for all day)
SP_CONFIGURE 'show advanced options',1; GO
RECONFIGURE; GO
SP_CONFIGURE'blocked process threshold',10 ; GO
RECONFIGURE; GO

--How to turn OFF blocked processes report
SP_CONFIGURE'blocked process threshold',0 ; GO
RECONFIGURE; GO