Eldergriffon's questions

I have a SQL Server instance (SQL Server 2008 R2, Windows 2008 R2) that complains, for very short, random periods of about 15-20 seconds, that some of its I/O requests are taking longer than 15 seconds. ("SQL Server has encountered x occurrence(s) of I/O requests taking longer than 15 seconds to complete on file x") The disks in question are part of a SAN. Typically, in such a scenario, it's common to see IOPS or throughput demands on the disk spike, thus producing the latency, and suggesting perhaps that the LUNs need to be beefed up to match the server's needs. In this case, however, there is no such spike--on the contrary, according to perfmon, activity on the affected disk goes from a steady state to almost nothing at all, and latency actually improves a good deal. (And, I should add, we've searched on the SQL Server side for evidence of any sudden burst of activity, to no avail. The nature of the workload is such that a sudden drop in server activity is not possible.) There is a brief compensatory spike after the slow I/O incident, as requests catch up after the interruption.

The SAN folks have gone over everything with a fine-toothed comb (including the configuration of the host) and declare that nothing is wrong from their perspective. It so happens that we are using both anti-virus on this server (with proper file exclusions) and an encryption solution that operates like a file system driver, so I am naturally suspicious that either or both of these may be the source of the problem. But I'd like to be able to present a smoking gun when I call everyone into the sitting room to reveal the murderer. Other than consulting the vendors (which naturally we are doing), any suggestions for troubleshooting intermittent latency issues that may be caused by an application intercepting file system requests? Any tools or techniques, perhaps, that might show exactly what's slowing things down? I'm afraid that turning off either the AV or the encryption to see what happens is a non-starter. Just to complicate matters, this problem, so far, cannot be reproduced on demand.

There is code in our shop that must query a SQL Server 2008 server, determine the Active Directory domain that the host belongs to, and, in SQL, create Windows login principals based on this information. Under Windows 2003 server, it was possible to query the domain's name through SQL Server like so:

DECLARE @Domain nvarchar(255) 
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon', N'CachePrimaryDomain',@Domain OUTPUT 

SELECT @Domain AS Domain

However, this no longer works in Windows 2008 ('CachePrimaryDomain' registry key doesn't exist anymore). Anyone know if there is a registry key that reliably reports the Active Directory domain a Windows 2008 server belongs to? Better yet, is there an entirely different way of handling this that makes more sense? Thanks.

Please help refine a discussion going on in our shop.

Consider the following scenario. There is a Microsoft VPC running several apps and services (Windows 2003 server). The server has two or three critical roles. Every so often, CPU utilization goes to 100% on a sustained basis. One of the culprits in this is a legacy application, for which the only real solution, at this time, is to restart the service. After this, CPU utilization returns to something reasonable (on average, 60-80%). However, less often, when the server is at 100% CPU, another service appears to be using the lion's share, a security application that parses logs. Our operations team's impulse is to restart that as well when the CPU becomes pegged. Our security team points out that this is pointless, as this service is running at BelowNormal priority, so effectively it is not depriving any other process of CPU. Security argues that the 100% CPU usage in those cases should really not be considered a critical condition. If a BelowNormal priority process is using most of the CPU, then there is actually no CPU deficit at all. Operations, on the other hand, is skeptical that 100% CPU utilization could really be a condition without adverse consequences, and doesn't want to ignore it. Who is right? Is Security right that it's nothing to worry about or Operations that we ought to do something?