Performance issues

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.