is your Anti-Virus Strangling SQL Server?
Its very common when I arrive on site to do a performance review or some tuning that a good first place to look for is windows task manager and anti-virus. By selecting View-Select Columns we can add interesting counters such as Threads, Handles, I/O Reads (since start-up) and I/O Writes since start-up (a sample is below)
The screenshot above shows that while anti-virus has only read 10% of the bytes of SQL Server, it has performed three times as much read I/O as the read activity isn't as efficient as SQL Server - its a lot more ad-hoc with smaller avg read sizes.
If Anti Virus exceptions are not configured, its possible for the Anti-Virus to consume many times the I/O's of SQL Server which can really hamper performance. IMO Anti-Virus is just a utility program running on a server, and such it should be quietly running in the background, hopefully not consuming more than 5% of I/Os. On many servers it is consuming 50%+ of total I/O activity.
Now no one can say "don't use anti-virus" without being hung drawn and quartered by the security teams, but the Microsoft KB on anti-virus for SQL (KB309422) is carefully worded
"Microsoft strongly recommends that you individually assess the security risk for each computer that is running SQL Server in your environment and that you select the tools that are appropriate for the security risk level of each computer that is running SQL Server. Additionally, Microsoft recommends that before you roll out any virus protection project, test the whole system under a full load to measure any changes to stability and performance.
Virus protection software requires some system resources to execute. You must perform testing before and after you install your antivirus software to determine if there is performance impact to the computer that is running SQL Server."
We can re-word this to say "don't apply your common anti-virus policy to SQL Server and be careful if you do". From a practical perspective this means considering a few factors:
a) The level of virus risk to the server
b) How much performance can this SQL Server workload spare
c) How intrusive is the Anti Virus
For most servers, you can run anti-virus fine, but we just need to be careful as to the exceptions. The official exceptions in the KB are:
- SQL Server data files (*.mdf, *.ldf, *.ndf)
- backup files (*.trn, *.bak usually)
- full text catalog files. This is the FTData folder in SQL Server
- The directory that holds Analysis Services data
I would add these files/folders as well to be sure:
- Trace files (*.trc)
- The Log Folder. Something like "MSSQL\Log
Also, note that the KB has not yet been updated for SQL 2008 file stream. I'm not sure what the MS official policy will be, but I guess you "may" have to exclude the file stream folder and rely on a virus sweep instead of these externally stored BLOB's, or suffer a performance hit.
In addition, there are further recommendation for windows (KB822158) should be applied as well.
One last comment is pick your anti-virus product well. Most people buy anti-virus on the cost and features, I would add resource consumption as an additional thing to consider. AFAIK, no one has yet published a league table comparing products on this basis.