Return to HomePage

---

How To: Tune SQL Server

Source: http://msdn.microsoft.com/library/en-us/dnpag/html/scalenetchapt17.asp
J.D. Meier, Srinath Vasireddy, Ashish Babbar, Rico Mariani, and Alex Mackman

In most cases SQL Server is self-tuning, so you do not need to change the default configuration settings. If you do make changes, test to ensure that the changes resolve the issue and help you meet your performance objectives.
Metrics
The following performance counters help you identify SQL Server bottlenecks.
Table 17.9: Performance Counters Used to Identify SQL Server Bottlenecks
Area Counter
Indexes SQLServer: Access Methods\Index Searches/sec
SQLServer: Access Methods\ Full Scans/sec
Cache SQL Server: Cache Manager\ Cache Hit Ratio
SQL Server: Cache Manager\Cache Use Counts/sec
SQL Server: Memory Manager\ SQL Cache Memory(KB)
Memory\ Cache Faults/sec
Transactions SQL Server: Databases\Transactions/sec
SQL Server: Databases\Active Transactions
Locks SQL Server: Locks\ Lock Requests/sec
SQL Server: Locks\ Lock Timeouts/sec
SQL Server: Locks\Lock Waits/sec
SQL Server: Locks\ Number of Deadlocks/sec
SQL Server: Locks\Average Wait Time (ms)
SQL Server: Latches\Average Latch Wait Time(ms)

For more information about how to measure these counters, their thresholds, and their significance, see “ADO.NET/Data Access” in Chapter 15, "Measuring .NET Application Performance."
Bottlenecks
Out-of-date statistics can be a common bottleneck in SQL Server. If statistics used by the SQL query optimizer are out of date, this can lead to poor performance. To monitor these statistics, you can either use SET STATISTICS PROFILE ON in Query Analyzer or you can use the SQL Profiler and monitor the profiler event named Performance:Showplan Statistics (Event Class 98). A big difference between the estimated row count and the actual row count can indicate that the optimizer had outdated or skewed statistics. For more information, see "Compare Actual vs. Estimated Rows and Executions" in Chapter 14, "Improving SQL Server Performance."
Tuning Options
In general, do not tune SQL Server configuration settings unless that is your only remaining option (meaning that you have already tuned your application and database design). Your tuning can work against the SQL Server self-tuning and can degrade performance. Make sure that you test to verify that you have fixed the problem.
To tune SQL Server, consider the following options:
* If there are other applications on the system, set SQL Server memory to a fixed amount.
* Update statistics.
* Choose hardware-level RAID rather than software RAID when you can.

If There Are Other Applications on the System, Set SQL Server Memory to a Fixed Amount
Assign SQL Server a fixed amount of memory only if your system is running applications other than SQL Server. Otherwise, you can assign SQL Server as much memory as possible. To assign SQL Server a fixed amount of memory, the SQL memory settings of the server needs to be set as "Use a Fixed Memory size." This value depends on your application and SQL Server load, so consider changing the value and testing the server with application load and SQL Server load to find an optimal value for this setting.
Update Statistics
If statistics are out of date, your indexes will be inefficient. You can update out-of-date statistics by using UPDATE STATISTICS WITH FULLSCAN.
Choose Hardware-Level RAID Rather Than Software RAID When You Can
Choose hardware-level RAID rather than software RAID when you can. Software RAID takes CPU cycles away from SQL Server.
Choose RAID 0+1 (Striped Mirror) Where You Can
Use RAID 0+1 (also known as RAID 01 or striped mirror) where possible. RAID 5 (striping with parity) can be used in some circumstances, but is generally less reliable and more expensive over time.

---

Return to HomePage