If you’re using Windows 2016 in, its default config you may not get getting the IO performance that you were expecting. If you try a Windows 2012 server, the problem magically goes away. The question is why.
The answer is shockingly straightforward, Windows 2016 ships with Windows Defender installed by default, where Windows 2012 R2 didn’t. Windows Defender, if not disabled by GPO can have a significant impact on your server. On a client machine in Azure that was having the issue, DiskSpd on a disk which should have 7500 IOPs available was getting just 1100. The same applied to a stripe of 1TB disks which should have given us 20,000 IOPs was also seeing only 1100 IOPs. As soon as we disabled Windows Defender, we got the speed we were expecting.
Now I’m not going to recommend that you disable Windows Defender automatically, but you’ll want to set exclusions for it to ignore MDF and LDF files as well as BAK files (and any folders that you’re using for FILESTREAM data) so that Windows Defender it’s making your SQL Servers extremely slow.
DennyContact the Author | Contact DCAC