Defragmented and superfast of course! Don’t just leave them lying there – get you hands dirty and start digging around to see how fragmented they are.
All indexes will eventually get fragmented over time so it is good to plan and set up a defragmentation schedule.
Best times for defragmenting indexes is when your SQL Server workload is at its lowest with as few processes as possible accessing database tables.
When to run defragmentation?
Nights or weekends are usually perfect (if your workload is business hours), but make sure the index defragmentation doesn’t collide with other jobs since the process is pretty heavy on the disk IO.
If you are fortunate enough to have invested in a Enterprise version of SQL Server you may defragment your indexes without blocking access to the underlying tables.
Try to use a script/tool that checks which indexes are most fragmented and that only defragment those indexes to avoid unnecessary IO.
It’s also great if the script logs which indexes were defragmented with additional fragmentation %, index size, date so you can (and should!) review this log after a couple weeks/months to see if there are some indexes that need special attention.
You may find indexes that constantly gets scheduled for defragmentation or some that never do.
A popular script out there is this one
or the more basic, older, one:
Try out first!
Always try out new scripts in your development / test environment to avoid problems and try to always be online when running scripts in production for the first time.
Transaction log backups
Make sure your transaction log backups can handle your defragmentation. It is very easy to run out of transaction log space when executing an index defragmentation so be careful! Transaction log backups should run frequently so your defragmentation job doesn’t hit the roof.
Mirroring / Transaction Log Shipping
The same goes with mirrored databases or log shipping. The logs that are going to be shipped over are going to be huge so make shure there is disk space / network throughput to handle the extra load.