1. CPU Utilization We will check the
performance of CPU usage of the server in Task Manager
2. Memory We will check memory manager
counters
3. Disk IO We will check Disk IO counters
4. Blocking
i. We will identify if blocking is
really present or not?
Select * from sys.sysprocesses where blocked<>0
ii. After identifying the SPID's, find out which SPID is
doing what task
DBCC INPUTBUFFER (SPID1)
DBCC INPUTBUFFER (SPID2)
iii. Identify which SPID belongs to which
application/login/network address
Select * from sys.sysprocesses where
spid=<SPID1>
iv. Priority should be chosen based on Time started, CPU
Time, Memory usage, IO Usage
sp_who2
v. If confused on the priority
immediately escalate to respective application Team Lead/Application Contact
Person. Also approach any escalation in your team.
5. Deadlock
1. As per the request we will enable traces and find the
deadlock issues.
DBCC traceon(1204)
DBCC traceon(1222)
2. Please stop the trace, once monitoring is completed. Else
it would lead to performance issues.
DBCC traceoff(1204) DBCC traceoff(1222)
3. Enable Traces or Profiler so that the deadlock occurrence
reason can be found Runàprofiler (2000)/profiler 90 (2005)
4. DBCC TRACEON(1204) – will write error to SQL Server Log`s
5. DBCC TRACEON (1222,-1)- will write error to XML file
6. Finding details through Profiler Profileràlocksàdeadlock
chain àdeadlock graphàsel batch completedàsel batch stared àRPC completed.
Inform Application team that they have to run the query again so that you can
enable the trace and then find the reason for deadlock (if it occurs again).
Explain application team that DBA Team can troubleshoot only when
trace/profiler are enabled.
7. If trace 1204 is enabled information is written into SQL
Server logs. · SPID of both processes Sp_who sp_who2 select *From
sys.sysprocess · what queries they were running dbcc inputbuffer(spid) · For
how long that deadlock occurred By default 5 seconds detected the deadlock ·
Which process was killed Dead loc priorities
8. Finally once the information is handy work with
Application team in modifying/code enhancements in the queries used for the
application.
9. Additional option is to set event "Deadlock Graph"
under Locks segment in Profiler and monitor the process, object, query and much
more additional information about the deadlock. Profiler -àlocksàdeadlock
chainàdeadlock Graph
10. Additional Points: DBA/App Team/Developers can control
the deadlock priority using SET DEADLOCK_PRIORITY [LOW|NORMAL|HIGH]
How is the instance performing Configuration Tools
>> SQL Server Configuration Manager >> SQL Server Services, and
check if SQL Server service status is “Running”. 7. How many logins/sessions
are connected SELECT login_name, count(session_id) as session_count FROM
sys.dm_exec_sessions GROUP BY login_name 8. What is the current load on the
system Open Task Manager, switch to Performance tab, you will find your entire
four cores are listed.
1. Run a single task; watch the changes of the CPU Usage
History of every core.
2. You can find the load may switch between cores.
3. Switch to Processes tab, right-click the task’s process, chooses Set
Affinity. You will find all four cores are selected.
4. You can uncheck three cores, click Ok.
5. Switch back to Performance tab, check if the task load switches between
cores. You may close and rerun the task to watch the changes.