Ravi Khanal

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.

Troubleshoot the SQL Server:

• First check the processor and memory usage to see whether the processor is not above 80% and memory is not above 40-45% utilization.
• Check the disk utilization using performance monitor.
• Use SQL profiler to check for the users and current SQL activities and job running which might be a problem.
• Run UPDATE_STATISTICS command to update the indexes.
• Check whether the tempdb is full.
• Check whether the transaction log is full.

Counters to watch in Performance Monitor:

The most common counters that I used to momitor are:

• Memory-Pages/Sec
• Network Interface-Bytes Total/Sec
• Physical Disk-%Disk Time-_Total
• Physical Disk-Current disk Queue length-Total
• System-%total Processor Time
• System-%Processor Queue Length
• SQL Server-General Statistics-User Connections
• SQL Server-Access Methods-Page Splits/Sec
• SQL Server- Buffer Manager-Buffer Cache hit ratio
• SQL Server-Target Server Memory
• SQL Server-Memory Manager-Total Server Memory