1

I'm trying to build an XE in order to find out which of our internal apps (that don't have app names and thus show up as .Net SQLClient Data Provider) are hitting particular servers. Ideally, I'd like to get the name of the Client and Database , but not sure if I can do that in one XE.

I figured for ease of use, I'd use a histogram/asynchronous_bucketizer, and save counts of what's trying to hit and how often. However, I can't seem to get it work on 2012, much less 2008. If I use sqlserver.existing_connection it works, but only gives me the count when it connects. I want to get counts during the day and see how often it occurs from each server, so I tried preconnect_completed. Is this the right event?

Also, and part of the reason I'm using XE, is that those servers can get thousands of calls a minute.

Here's what I've come up with thus far, which works but only gives me current SSMS connections that match - obviously, I'll change that to the .Net SQLClient Data Provider.

CREATE EVENT SESSION UnknownAppHosts 
ON SERVER 
ADD EVENT sqlserver.existing_connection(
    ACTION(sqlserver.client_hostname)
    WHERE ([sqlserver].[client_app_name] LIKE 'Microsoft SQL Server Management%')
    ) 
ADD TARGET package0.histogram
( SET slots = 50,
      filtering_event_name='sqlserver.existing_connection', 
      source_type=1, 
      source='sqlserver.client_hostname' 
)
WITH(MAX_DISPATCH_LATENCY =1SECONDS); 
GO
Ben Thul
  • 31,080
  • 4
  • 45
  • 68
mbourgon
  • 1,286
  • 2
  • 17
  • 35

1 Answers1

1

Aha! It's login, not preconnect_starting or preconnect_completed.

CREATE EVENT SESSION UnknownAppHosts 
ON SERVER 
ADD EVENT sqlserver.login(
    ACTION(sqlserver.client_hostname)
    WHERE ([sqlserver].[client_app_name] LIKE 'Microsoft SQL Server Management%')
    ) 
ADD TARGET package0.histogram
( SET slots = 50, 
      filtering_event_name='sqlserver.login', 
      source_type=1, 
      source='sqlserver.client_hostname' 
)
WITH(MAX_DISPATCH_LATENCY =1SECONDS); 
GO

Then to query it, some awesome code I made horrid:

-- Parse the session data to determine the databases being used.
SELECT  slot.value('./@count', 'int') AS [Count] ,
        slot.query('./value').value('.', 'varchar(20)')
FROM
(
    SELECT CAST(target_data AS XML) AS target_data
    FROM sys.dm_xe_session_targets AS t
    INNER JOIN sys.dm_xe_sessions AS s 
        ON t.event_session_address = s.address
    WHERE   s.name = 'UnknownAppHosts'
      AND t.target_name = 'Histogram') AS tgt(target_data)
CROSS APPLY target_data.nodes('/HistogramTarget/Slot') AS bucket(slot)
ORDER BY slot.value('./@count', 'int') DESC
mbourgon
  • 1,286
  • 2
  • 17
  • 35