This article consolidates best practices for SQL Server 2005 or later server implementation. It also presents a simplistic process that can be used to tune and troubleshoot performance.
The focus is on Operating System and SQL Server settings that should be taken into consideration. The information contained in this article is geared toward servers that are dedicated to SQL. Consider each setting's potential effect on the Operating System and any other applications and services that may be hosted along-side your SQL instance before making changes. Make one change at a time and test its impact before making other changes to help keep your testing simple, and to actually be able to draw accurate conclusions about the impact of the change.
Use the steps and information below to identify and address specific performance issues.
Before you begin:
Memory - SQL Server
Settings - SQL Server Instance
Monitor CXPACKET waits on the SQL instance. The goal is that they be less than 5% of relevant waits. 6 If CXPACKET waits remain higher than 5%, consider lowering the Max Degree of Parallelism by half again.
Disk - SQL Server System Databases
Disk - SQL Server User Databases
Database Settings - User Databases
alter database Symantec_CMDB
set read_committed_snapshot on
with rollback after 30;
Continually shrinking and regrowing the data files can lead to physical fragmentation of the database file, which hurts both sequential transfers and random accesses.5