Tips and Tricks: Tuning SQL 2005

Author: Joseph Cuesta

June 2008

Situation
You have upgraded or migrated to SQL Server 2005 expecting a significant increase in performance.   

Problem
Your applications seem slower than before the upgrade.

Solution
Performance Tuning!
Three key symptoms to start diagnosing
1.    Resource bottlenecks:
Resource bottlenecks can be any or all of the following: CPU, memory, and I/O bottlenecks.
Before you can determine if you have a resource bottleneck, you need to know how resources are used under normal circumstances.
You can use these tools to collect baseline information about the use of the resource.
•    System Monitor (PerfMon)
•    SQL Server Profiler
•    DBCC commands
•    DMVs

2.    Tempdb bottlenecks:
There is a single tempdb for each SQL Server instance. It can be a performance and disk space bottleneck. The tempdb can become overloaded in terms of space available and excessive DDL/DML operations. This can cause unrelated applications running on the server to slow down or fail.
Some of the common issues with tempdb are as follows:
•    Running out of storage space in tempdb
•    Queries that run slowly due to the I/O bottleneck in tempdb
•    Excessive DDL operations leading to a bottleneck in the system tables
•    Allocation contention

3.    Slow running queries:
Queries can also run slowly because of wait states for logical locks and for system resources that are blocking the query. The cause of the blocking can be a poor application design, bad query plans, the lack of useful indexes, and a SQL Server instance that is improperly configured for the workload.
The two main causes of a slow running query are:
1.    Blocking
The important steps for diagnosing blocking/waits are:
•    Identifying the blocker
•    Identifying long blocks
•    Blocking per object
•    Page latching issues
•    Overall performance effect of blocking using SQL Server waits

2.    Index problems
Another aspect of query performance is related to DML queries, queries deleting, inserting and modifying data. The more indexes that are defined on a specific table, the more resources are needed to modify data. In combination with locks held over transactions, longer modification operations can hurt concurrency. Therefore, it can be very important to know which indexes are used by an application over time. You can then figure out whether there is a lot of weight in the database schema in the form of indices which never get used.

TIPS
•    Use the appropriate RAID configuration
•    Use multiple disk controllers
•    Maximize available memory
•    Locate logs and the tempdb database on separate devices from the data
•    Create your baseline with SQL Profiler
•    Use the new sys.dm_db_index_usage_stats view to get statistics on indexes
•    Rebuild highly used indexes nightly
•    Auto update statistics
•    Implement tuning wizard suggestions in test environment

This is just a small piece of the tuning process; there are many more considerations to take into account.
For a comprehensive and detailed discussion of SQL Server tuning, see Microsoft’s SQL Server 2005 Books Online and the white papers, Webcasts, and other resources listed in “SQL Server 2005 Resources”.

If you need more information, contact Gary Codeluppi at 937-431-1026 x123 or visit the Ross Group Inc web site at www.rossgroupinc.com.