Windows file system compression doesn’t have a beneficial effect on large SQL Server databases. Hard to believe, I know, but it’s true. Trust me.
JLS.CX
You and me were never meant to be part of the future
Windows file system compression doesn’t have a beneficial effect on large SQL Server databases. Hard to believe, I know, but it’s true. Trust me.
There’s no benefit in compressing the filesystem once. Set it to compress 2 or 3 times, and you’ll start noticing a difference.
Also, don’t forget to encrypt it.
It took us a while to figure out why the DB server CPUs were pegged, but disk I/O was very low. It wasn’t obvious until we changed admin accounts and saw the telltale blue text in Windows Explorer – somehow that had been turned off in our usual account.
That DB server processes insert/update transactions at a rate of about 1/sec during business hours. Not terribly busy, but recompressing those DB files after modification is evidently not trivial. This might go a long way towards explaining the terribly fragmented indices we found on the server earlier last week.