Ravi Khanal

Tips for Performance Monitoring

Posted by Ravi Khanal on Jul 20, 2008

Performance Tuning is the most important job you should perform as a Database Administrator. The most important thing you should consider for monitoring the performance of the server are:

Troubleshoot the Slow running queries:

• Try to see whether a batch of commands are running or a single query is running.
• Examine the Execution plan by using SQL Profiler and SQL Query Analyzer. 
• Verify the existence of the correct indexes by performing Index analysis option in SQL Query analyzer or Index tuning wizard. Check for the Table Scan or Index Scan or Index Seek. If there is Table Scan then there is no index in your table. If there is Index Scan then your index is not being used. If there is Index seek then your Index is performing good, check for other issues.  
• Run the following DBCC Commands:

                   DBCC SHOWCONTIG(’JOBS’)

                   DBCC INDEXDEFRAG(NORTHWIND, ORDERS, CUSTOMERS )

                   DBCC DBREINDEX(AUTHORS, ‘clustered index’, 70)

• Remove any Hints and re-evaluate the performance.
• If a particular query is Slow when it is using Parallel execution, you can try forcing a non-parallel execution plan.

  Read the rest of this entry »