Sql-server – Index Maintenance Plan

The operating system returned error 23(Data error (cyclic redundancy check).) to SQL Server during a read

This indicates the operating system detected an issue reading data from the hard disk itself. When data is written onto the filesystem, the operating system calculates a CRC code for each block written; when that data is subsequently read back the operating system performs the CRC operation again on the read data and compares it with the CRC stored in the filesystem. If these two CRC codes don't match the OS reports error 23. I'd be very surprised if there isn't a hardware issue with either the drive itself, or perhaps some other component such as the motherboard or drive controller.

WITH (ONLINE=ON) in the Enterprise Edition of SQL Server will have no effect whatsoever on this issue. Online index operations vs offline index operations are no different at the operating system layer; data is read and written as necessary to either rebuild or recreate the index.

Defragmentation strategies help improve scan speed to/from disk.

The wide variety of opinions is because an environment's ideal defragmentation strategy should depends on many different factors. There are also multiple potential layers of fragmentation in play.

Saying that your databases are stored on a SAN isn't enough information. For example:

• Are database files stored on separate physical RAID groups or the same RAID group? What other processes are active on that same device? Are your backup files ending up there, too? You may have to ask your SAN admin for this information, because it's not always transparent.

• What are the access patterns for the databases? OLTP is generally random access, but sometimes an application is table-scan-happy and you can't change its behaviour (ISV app). Are the applications read-mostly, write-mostly, or somewhere in between?

• Are there performance SLAs in play during a recovery/failover period?

Brent's post assumes there is one giant pool of storage and everything shares it. This means the physical disks are rarely idle, and hence most access is random. If that is your situation, then the advice applies, and I agree with it for the most part. While this type of strategy is much easier to manage, it isn't necessarily (a) what you have in your environment, or (b) what is the best solution for your environment.

If index maintenance is burdensome, consider doing it less aggressively, and/or amortize the cost over the week (i.e., run light maintenance once/day, instead of heavy maintenance once/week).

You can also turn on the SortInTempdb option to potentially reduce the amount of logging that takes place in the user databases.