I recently had a friend 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, I asked them to run a few scripts and send me the results so that I could do a quick assessment. I also asked for a screenshot of task manager to see what the resource consumption was. What I immediately noticed was that anti-virus consuming a considerable amount of CPU. IO on tempdb was also considerably high. The virus scan was constantly scanning portions of tempdb, and the reason why is because when they created the additional tempdb data files, they named them tempdev.002, tempdev.003, and so on.
Their virus scan 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 extension 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 my buddy, 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.
The post Careful When Changing SQL Server Database File Extensions appeared first on Tim Radney.