SQL Server Performance Tuning

It is daily question for any DBA or DB analyst what shall you do when App Support or end users forward to you any .net timeout or .net error that contains almost SQL vocabularies and you want to know if it is relevant or irrelevant to DB side….?

Here basically, you could have a main checklist of DB checks that it could be helpful for you while any DB investigation:

1. Check all locks /Deadlocks by the same time slot of .net timeout /.net errors

2. Check all expensive queries by the same time slot (Follow up my article for this regard http://www.sqlserverpath.org/blog/page/3/ )

3. Check all failed logins took place by the same time slot (Using Server Audit of 2008 better to check Network error codes as well)

4. Check all different types of overload on the 4 HW resources :

· IO Subsystem (Local /SAN storage )

· CPU

· Network

· Memory

5. If something found , you have to identify its reason which might be of 3 probable assumptions basically :

· Expensive Queries of end users

· SQL Server activities like rebuild indexes , check integrity ….etc

· Activities of other third party tools like Antivirus or backup tools…etc (Could be figured out using UCP monitor of 2008 R2)

6. Check DB files & storage capacity using UCP monitor of 2008 R2

7. Check SQL Server log for any strange incident

8. Check DTC accessibility between APP +DB Servers in case of that SQL Service depends on DTC Service for OLTP transactions.

9. Check event viewer of DB servers to find out any strange incidents that could correlate with these timeouts

10. Run profiler to trace specific events if some problem is frequently repeated at specific times.

If nothing, so it has nothing to do with DB end and APP support has to figure out relevant incidents from other layers like .net , network ..etc

Kindly Please let me know if you need any help regarding any of the above points