(update 2013/04/18 Jonathan Kehayias helpfully provided a way to do it by adding the existing_connection event - but there appears to be a bug with the histogram, and it doesn't return the right data. Connect item if you want to vote on it: https://connect.microsoft.com/SQLServer/feedback/details/785042/extended-events-histogram-does-not-properly-save-existing-connection-results)
First of all, MANY thanks to Jonathon Kehayias for all the XE wisdom and code - I modified the snot out of his http://www.sqlskills.com/blogs/jonathan/tracking-sql-server-database-usage/ lock-tracking-using-a-histogram to build this...
Say I want to figure out what servers are connecting to my SQL Server. This is something you'd traditionally either poll sysprocesses for periodically, or probably run a trace. Both have issues, though, especially if they're busy servers. So, how can we get that? Extended Events.
The easiest way to do this would be using Extended Events - create a "bucketizer" (now called the histogram), and watch for new logins, saving the servername and incrementing a counter each time it happens. As a bonus, we could then filter it - exclude particular applications, servers, etc (see the commented out code). And, since it's a pretty basic XE, overhead is very low. The one caveat is that you can only get one piece of information out - servername. I'd LOVE to get the App Name & DB Name as well, but you can only get one piece of information at a time. Downer. : - (
One caveat: If you have servers that stay connected, and don't open new connections, you won't see them here. The obvious exclusion, then, is replication, but maybe your app hangs on for a long time (we've seen that with third-party tools). Easy answer for those is to use SP_WHOISACTIVE.
Any questions?
5 comments:
If you also add the existing_connection event, you will be able to also track the long connected applications in the same session without sp_whoisactive.
Question for you, Jonathan - I looked at doing that, but can't figure out how to get the histogram to add it.
First: say I run this. No problems - shows all the various hosts in the XML, exactly as expected.
CREATE EVENT SESSION [test_stopme] ON SERVER
ADD EVENT sqlserver.existing_connection(
ACTION(sqlserver.client_hostname))
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE=OFF)
GO
BUT, if I change the target to the histogram, all I ever get is _my_ client name (I'm running this against a different machine), with a count probably equal to the number of connections.
CREATE EVENT SESSION [test_stopme] ON SERVER
ADD EVENT sqlserver.existing_connection(
ACTION(sqlserver.client_hostname))
ADD TARGET package0.histogram
( SET slots = 50,
filtering_event_name='sqlserver.existing_connection',
source_type=1,
source='sqlserver.client_hostname'
)
Instead of
machinea 2
machineb 3
mybox 1
I get
mybox 6
Also, since it's a histogram, how do I tell it to combine the two EVENTs, the existing_connection & the login? When I add the histogram target, I have to give it an event to filter on, which would seem to mean I can get one or the other, not both.
Apologies in advance. I'm really hoping that after I spent an hour trying to get this work, you can go "oh, yeah", and type something in a minute or so. Thanks for any/all help!
You are specifying an event name for the histogram, but you don't have to. You can do:
CREATE EVENT SESSION [Logins] ON SERVER
ADD EVENT sqlserver.existing_connection(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname)),
ADD EVENT sqlserver.login(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname))
ADD TARGET package0.histogram(SET source=N'sqlserver.client_hostname');
and it should work by bucketing on the action for client_hostname across all events. I don't have a good place to verify it at the moment, but it should work as expected.
Cool! Tried skipping part of the definition, but apparently hadn't tried that. And you had my hopes up there, collecting both the app name and the client hostname for the histogram - was hoping you had a clever way to get both, some way to use the histogram as a low-impact GROUP BY target. Oh well. : )
However, I tried your code, and the existing_connection still only returns my desktop name when I run it against the other server. 25+ connections, it says, even though a trace on the ExistingConnection event came back with 25+ connections from various servers, of which 3 were my desktop. Bug, maybe?
Made a single-server example that shows that something's wrong with the Histogram. We save out the list of applications (client_app_name) to both the ring_buffer and the histogram, then query both.
To reproduce on your box, just connect SSMS and SQLCMD to your instance, then run the following. When I run it on my box I see 71 "Microsoft SQL Server Management Studio - Query" in the histogram, but 50 ".Net SqlClient Data Provider", 11 SSMS, SQL Agent... that all add up to 71.
Please excuse the ugliness of the code.
CREATE EVENT SESSION [Logins] ON SERVER
ADD EVENT sqlserver.existing_connection(
ACTION(sqlserver.client_app_name))
ADD TARGET package0.histogram(SET source=N'sqlserver.client_app_name'),
ADD TARGET package0.ring_buffer
-- Start the Event Session
ALTER EVENT SESSION [Logins]
ON SERVER
STATE = start ;
GO
SELECT slot.value('./@count', 'int') AS [Count] ,
slot.query('./value').value('.', 'varchar(150)')
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 = 'Logins'
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
DECLARE @target_data XML
SELECT @target_data = CAST(target_data AS XML)
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t
ON t.event_session_address = s.address
WHERE s.name = 'Logins'
AND t.target_name = 'ring_buffer'
;WITH cte AS(
SELECT
n.value('(data[@name="client_app_name"]/value)[1]', 'varchar(100)') as client_hostname
FROM @target_data.nodes('RingBufferTarget/event') AS q(n)
) select client_hostname, COUNT(*) FROM cte GROUP by client_hostname
GO
ALTER EVENT SESSION Logins
ON SERVER
STATE = STOP ;
GO
-- Drop the Event Session
DROP EVENT SESSION [Logins]
ON SERVER;
Post a Comment