I recently had a client reach out to me about database performance dropping drastically. I asked if anything had changed recently on the server and they told me that all they had done was increase the number of files for tempdb from 1 to 8 per best practice.
Knowing that this shouldn’t have a negative impact on the system, we agreed for me to spend an hour or so troubleshooting. What I immediately noticed was that McAfee consuming a considerable amount of CPU. IO on tempdb was also considerably high. McAfee was constantly scanning portions of tempdb, and the reason why is because the dba named the additional data files as tempdev.002, tempdev.003, and so on.
McAfee had exclusions for the standard naming conventions of .mdf, .ndf, and .ldf. There was no exclusion for .00X. Although .mdf, .ndf, and .ldf are the default naming convention, SQL Server doesn’t really care what extensions you use. However, if you plan to deviate from the standards, make sure that any antivirus or driver based filters account for the new naming standard.
What happened for this client, is they performed a denial of service against tempdb with their antivirus software. Lesson learned, if you are going to deviate from a standard, make sure to test in a development or QA environment first.
I hope you found this post helpful! And, if you want to find all of our SQLskills SQL101 blog posts – check out: SQLskills.com/help/SQL101