Admin CliCours
SQL Server 2008 24X7 Maintenance Considerations
Database Maintenance
What is the purpose of database maintenance?
To ensure consistent, high performance of databases while protecting the integrity of the schema, end user data, and the SQL Server.
Physical Server
Physical Disk Layout Considerations
How many spindles are enough to support the I/O workload?
How to distribute the data files onto a disk?
Selecting the correct block size.
Should you sector align the disk?
How many spindles are enough to support the I/O workload?
Ideally, you should be able to answer the following questions:
How many I/Os are going to the disk?
How long is it taking? To answer the How Many I/Os question use disk performance counters from the last several weeks. I prefer to use the physical disk because that captures I/O information for all partitions on the drive.
Disk Reads/Sec
Disk Writes/Sec
Disk Transfers/Sec
Once you have the I/Os you will also need to understand what type of RAID volume is hosting your data. The type of RAID will add additional I/Os.
RAID 1/10 – [Reads + (2*Writes)]/ # of Disks
RAID 5 – [Reads + (4*Writes)]/# of Disks
Generally speaking todays fiber channel SAS disks can support approximately 150 I/Os per second. Check the manufacturers specifications for exact details. We tune for 100 to 120 I/Os.To answer the How Long question once again use the disk performance counters from the last several weeks. I prefer to use the physical disk because that captures I/O information for all partitions on the drive.
Average Disk Sec/Reads
Average Disk Sec/Writes
Average Disk Sec/Transfers
Ideally all transfers to .mdf/.ndf files should be less than 10ms and all transfers to .ldf files should be 1 to 3ms.Where to locate database files?
Microsoft’s best practices recommend separating the main data file from the log data file onto physically separate spindles.What block size should the disk be formatted with?The official answer is it depends on the type of workload running on the server. In general a 64K block size has been found to provide a consistently high level of throughput for the majority of workloads.SQL Server scales the size of the read or write to the workload it’s processing. For example backups can be written in larger chunks than a log file write.
Wait Stats – What are they?
SQL Server’s method of letting us know what a given SQL Server instance is waiting on when trying to complete its workload.This information is invaluable when trying to determine where best to invest your troubleshooting time.
