This week I had an application team report the error “SQL Connection Lost”.  Fortunately they also reported the exact times of occurrence, which made it much easier to hunt down.  I did some digging through the SQL logs and DMVs, but did not find anything that indicated SQL was dropping the connection.  I also did some digging in the application logs, but I didn’t find anything more specific than the generic error the application team reported.  My next stop was to start combing the system logs to see what I could find.  The one and only thing that coincided with the times of the issue was a full AV scan.

IO is generally the biggest bottle neck for SQL and there is nothing that can generate some serious additional IO than a virus scan.  This application does a large nightly import of data, so we just changed the import time.  We could have also changed the AV scanning time, but that is managed by another group and would take way too long to get changed.  That fixed the issue, but changing the import time is not the long term solution we want.  We’ve got the AV team working on changing the scan time, so we can change the import time back to where we had it.  So how should we configure our AV software on our SQL servers?  Here are some tips.

• AV software creates a lot of IO so make sure the scans don’t coincide with large SQL jobs like imports or SSIS packages
• Create a scan exemption for .MDF file extensions
• Create a scan exemption for .NDF file extensions
• Create a scan exemption for .LDF file extensions
• If you have drives dedicated to your database files, CONSIDER an exemption for that drive.  Did you see that word in bold in the prior sentence?  Be aware that this can be dangerous if something like a keylogger or executable gets dropped on that drive, because now you’ll never catch it.  It is far better to make exemptions based on file extensions as mentioned above.  I mention this only because it is an option, albeit a very dangerous one.
• Create a scan exemption for .BAK and .TRN extensions.  I suggest you only do this if your backups are on the same drive as your database files (shame on you for that, but sometimes we have no choice).