February 9, 2013 at 7:38 am
Here's a fun one for you. I a customer running SQL2k8 x64 on Windows Server 2008 R2 with Sophos 10.something Anti-Virus.
I've been battling query performance all week. But the behavior was odd. If I issued a large query (on well indexed tables) with a solid query plan the query would disappear for hours with nothing to show for it. Activity monitor would show almost no activity anywhere (barely showing 1k/s on the Disks), but TempDB would start growing like mad. The only way I could tell there was any activty going on in SQL Server was to watch my TempDB files grow. In the early stages, I was convinced the server was configured to use their LAN attached SAN for database storage.
After discovering (shame on me for not checking), that an AV program had been installed, I convinced the Network Admin to exclude the database files, logs, and TempDB... query performance on small queries improved, but this one query still seemed to force SQL Server into some kind of "sleep walking" state, dumping more data than existed in the tables into TempDB.
Then I found this Microsoft KB article: that talked about "Very slow response from SQL Server even when the concurrent amount of load is not unusually heavy and certain modules are loaded into the SQL Server process address space [sqlservr.exe].". The article goes on to specifically identify a problem with Sophos Libraries loading into the SQL Server address space.
I confirmed that Sophos was loading into the SQL Server address space with this query:
SELECT distinct olm.[name], olm.[file_version]
FROM sys.dm_os_virtual_address_dump ova
INNER JOIN sys.dm_os_loaded_modules olm
ON olm.base_address = ova.region_allocation_base_address
where name like '%SOPHO%'
ORDER BY name
I removed SOPHOS_DETOURED.DLL from the registry as the KB article suggested.
BLAMO, my query runs in 23 seconds... no TempDB activity.
Hooray! ... so I thought.
Now here is where it gets wierd. If I run the query from a connection inside Mangement Studio (on the server or from my desktop), or via an OLEDB connection, 23 seconds (50 seconds over the VPN).
Run the exact same query via an ODBC connection (on the server or remote), and the query will take an hour or more...oh, and TempDB growth is back.
Short of uninstalling Sophos (which isn't an option at this time, the coprorate IT guys will have none of that discussion nor discussions about different tool), any ideas why the connection type oledb vs. odbc would have an affect on query execution, and more importantly... how to work around it?
:blink:
November 15, 2013 at 3:16 pm
I know this is an old thread, but it's an interesting topic to me.
If you're still around I'd love to hear more of the story. Or if you got a chance to watch it with netop, wireshark, or procmon.
November 16, 2013 at 7:31 pm
Thanks for the reply Benjamin. Yeah, I'm still lurking around.
I had forgotten about this post. We did finally convince the IT guys to disable Sophos and we reran our tests. unfortunately... it didn't make a difference.
However, pulling the SQL Server resident library did help overall. We ended up putting Sophos back, but excluded the SQL Server directories.
After weeks of mashing of teeth, I discovered one afternoon, that the details of the VM configuration hosting the SQL Server instance were not "as advertised". We were told we had fixed memory and processor resources, a dedicated NIC, and a dedicated pathway to the SAN. None of these were true. The other issue we ran into was unbelievable index/data fragmentation; I'm still not sure I understand why. You could rebuild 20 indexes, and by the time you got to the 20th, the 1st index, with no other changes to the tables would be 100% fragmented; I'm blaming the SAN...
We did finally get the NIC and SAN pathway dedicated and for reasons I cannot explained eliminated the OLEDB/ODBC connection performance differential, although query performance overall suffers due to the index fragmentation issues, and load on the SAN.
So... the customer fell into a "this is as good as it gets" mentality, and no other changes were made to the configuration.
November 20, 2013 at 8:58 am
Thanks for the update!
November 20, 2013 at 9:37 am
jchapman (11/16/2013)
Thanks for the reply Benjamin. Yeah, I'm still lurking around.I had forgotten about this post. We did finally convince the IT guys to disable Sophos and we reran our tests. unfortunately... it didn't make a difference.
However, pulling the SQL Server resident library did help overall. We ended up putting Sophos back, but excluded the SQL Server directories.
LOL.. sorry the mention of SOPHOS made me chuckle. We had issues with this anti-virus when I worked as a support analyst at a previous shop. For months we couldn't figure out why our web application would just crash with a weird IE error. After weeks and weeks of investigation we discovered it was the anti-virus software lol.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply