Antivirus Software on SQL Server
It’s an old story, but still present - Why or why not installing antivirus software on a database server?
First of all, I’ve been on both sides of the debate with respect, but I can only, only partially agree to install antivirus software on a database server - e.g Microsoft SQL Server. And this only partially agreement means, first not to install antivirus software on SQL Server as many IT departments do in a standard procedure. In addition, the default settings are left untouched, so that every virtually byte is examined in real time.
Have you ever examined the amount of memory, high CPU spikes or high CPU usage and last but not least your high consumer of I/O activities on your (busy) SQL Server?
So let us take a closer look by using Process Explorer to identify antivirus and SQL Server tasks since start-up.
Identifying the SQL Server and Antivirus consumers
I prefer using Process Explorer to take a immediate first look what happens on the server, he includes many interesting counters for regarding some performance issues and more. By selecting View-Select Columns…, we can add interesting counters such as Private Bytes, Working Set, Threads, Handles, I/O Reads, I/O Read Bytes, I/O Writes, I/O Write Bytes, CPU Time – as shown in the screenshot below.
The screenshot above shows that while the ntrtscan.exe (Real-time Scan Service) has read 70% of the bytes of SQL Server, it has performed 27 times as much read I/O as the read activity isn’t as efficient as SQL Server. And the tmlisten.exe (antivirus communication service) has only write 7% of the bytes of SQL Server, but it has performed four times as much write I/O as the write activity of SQL Server.
Now I want to see the leaders of high I/O Reads on my server by filtering I/O Reads (see screenshot below).
The first three consumers of I/O Reads are:
- Antivirus (ntrtscan) with 111.641.100 Reads
- SQL Server (sqlservr) with 4.622.225 Reads
- Antivirus (tmlisten) with 2409809 Reads
Same doing on I/O Writes:
- Antivirus (tmlisten) with 31.124.046 Writes
- SQL Server (sqlservr) with 9.785.283 Writes
- ReportingServices with 1.715.489 Writes
So, it’s possible for antivirus to consume many times of I/O’s of SQL Server which can really hamper performance on your (busy) SQL Server. It is possible that the antivirus is consuming more than 50% of total I/O activities! (Please note the recommendations to Microsoft SQL Server Database Engine Input/Output Requirements)
And what does Microsoft say for antivirus on your SQL Server (KB309422)?
"We strongly recommend 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, we recommend that before you roll out any virus-protection project, you test the whole system under a full load to measure any changes in 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 whether there is any performance effect on the computer that is running SQL Server."
In my opinion it is very carefully worded, with a “It depends on …” – but they help us!
Read this KB very carefully with you Antivirus Administrator, talk with him about virus risks on the server, performance effects on a (busy) SQL Server and how intrusive the antivirus is at the ratio of the security risk level. In addition read also KB822158, there are further recommendations for the windows server and should be applied as well and first!
I don’t want to repeat these KBs, but here are some recommended must reads:
- Turn off scanning of Windows Update or Automatic Update related files
- Turn off scanning of Windows Security files
- Turn off scanning of Group Policy related files
- Directories and file-name extensions to exclude from virus scanning
- Processes to exclude from virus scanning
- Considerations for clustering
AND - consider an external virus scan against your SQL Server, which is possible in most enterprise antivirus products – often you needn’t a real time virus scan, it belongs to the security risk level!
I wish you a nice day,
|Print article||This entry was posted by Torsten Schuessler on 2012-09-20 at 16:37:00 . Follow any responses to this post through RSS 2.0.|
backup «best practices» books bug ctp «cumulative update» datetime dmv ebook humor index indexoptimize integrity kbfix links loginproperty maintenance «ms sql server 2008» performance php profiler «reporting services» reviews rtm «service pack» «service pack 3» «service packs» sharepoint sp_msforeachdb «sql 2012» «sql pass» «sql server» «sql server 2005» «sql server 2008 r2» «sql server 2012» «sql server builds» sqlcat sqlpass «sqlpass franken» ssms ssmstoolspack «stacia misner» sysadmin t-sql «technical note» tempdb tools troubleshooting version whitepapers