SQL Server – How to Find Unused Indexes

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.

Since your problem is a conflict between constant updates and being able to run reports without facing deadlocks, I would recommend you consider using READ_COMMITTED_SNAPSHOT isolation set ON.

This also has overhead, but usually prevents most deadlocking situations. (Nothing is magic, of course.) You should read the details at:

http://msdn.microsoft.com/en-us/library/ms173763.aspx

Note that there is overhead in tempdb required in order to hold a consistent view of the data, which is needed to avoid the deadlocks. There is another SNAPSHOT isolation level that can be turned on per transaction.

Since this changes the blocking behavior, you may run into some transactions that need to be tuned or given a new index in order to avoid problems.

Therefore, if you use the READ_COMMITTED_SNAPSHOT isolation level, which works fine for me, you will need to monitor it until you are sure that all is well in your instance.