One of the challenges that comes along with a large number of SQL Servers, that aren’t always built out in a standard fashion, is that you run into problems whereby correct AV exclusions are not set, leading to performance problems (and other issues).

Going through and quickly grabbing the location of the SQL Server executable, or the directory in which it resides can be cumbersome. Doing it once is a pain, doing it 30 times multiplies that pain by a factor of about 400.

A quick query run against your servers (and done against server groups) can quickly return results for your machines and help you get on your way again.

The following query just returns the information from the dm_server_services dmv which has the data needed to set those binary exclusions.

SELECT AVExcludeSQLExecutable = SUBSTRING(filename, 2, CHARINDEX('"', filename, 2) - 2) ,
AVExclusionPath = LEFT(SUBSTRING(filename, 2, CHARINDEX('"', filename, 2) - 2),
LEN(SUBSTRING(filename, 2, CHARINDEX('"', filename, 2) - 2)) - CHARINDEX('',
REVERSE(SUBSTRING(filename, 2,
CHARINDEX('"', filename, 2) - 2))))
FROM sys.dm_server_services
WHERE servicename LIKE 'SQL Server (%';

This makes it easy to plug this in to your AV exclusion list and get back on with your day.

Of course it does not beat having a standardized installation location for your SQL binaries, but it’s better than manually logging on to servers to pull the data.