I have a sysadmin who wants to frequently defrag my hard drives on my servers. Does physical file defragmentation cause my indexes to become more fragmented? Should I rebuild my indexes after ever disk defrag?
I know clustered indexes might benefit from disk defragmentation, but what about the nonclustered ones? That's what I am mostly asking about.
Thanks.
Unless you shut down SQL Server or take the database offline during the defragmentation process, the disk defragmenter won't even be able to touch the database files, because they're always open. So, practically speaking, running a disk-level defrag will simply have no effect whatsoever.
Even if you took SQL Server offline, the effect should be minimal, because physical files in a database don't get fragmented, unless you use some well-known bad practice like autoshrink. That's why all DBMSes have their own internal defragmentation routines like DBCC INDEXDEFRAG - the physical file can be perfect, but the individual indexes and tables contained within may still be heavily fragmented.
Just don't bother with the disk defrag at all, would be my advice. If you have file fragmentation on a database server then it is likely a symptom of some other problem.