"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

January 10, 2010

SQL Perf Tuning - Performance Counters and Values

I have come across lot of blogs with queries to find Top IO, CPU queries.
  • Learn to differentiate IO Issue, CPU Issue, Network Issue
  • IO Issue - Excessive IO can be due to a SCAN on a big table. There are ways to convert SCAN to SEEK. In a OLTP system query are expected to be highly selective, fetch few records
  • CPU Issue - During processing we might have temp tables, Sort a Temp tables. Sort Operator is usually CPU intensive. Wait Stats sys.dm_os_wait_stats DMV provides more info. Please check Joe Sack wait stats link mentioned below
  • Disk Issue - PhysicalDisk/LogicalDisk counter in below bookmarked excel and values defined for it for interpreting Disk Issues
  • Network Latency - Network Interface counter in below excel provides values for counters and interpreting them
List of Counters and Optimal values for them


From Perfmon counters interpret from values of perfmon counters. Attached XL is very very good.
Without writing any query how do we find expensive queries
  • Activity monitor check recently expensive queries
  • Goto Particular Database that you want to check. List Reports for Reports->Standard Reports->Top Transactions by Age, Locks
  • Check Parallelism Enabled/Not Link
Saleem has written a good article on step-by-step Analysis for query troubleshooting. Link is


Happy Learning!!!
Labels: , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)