Sql-server – get top 10 remote clients by average query count per minute

sql serversql server 2014

Is there any way for me to see the top clients of my sql server?
I want to figure who is executing so many queries on my server. Ultimately I would love to see the application name(like in sql profiler) and IP/name of the server, but even knowing which web server the queries come from would help alot.

Remote Client | Database         | Executions/min
--------------------------------------------------
    XXXX      | FooDb            | 88.00
--------------------------------------------------
    XXXX      | BarDb            | 43.00
--------------------------------------------------

Instead of XXXX it could be – remote server name, IP, ApplicationName (like in SQL Profiler) – any of these.
I think something like this is possible in SQL Profiler, but I cannot run it because this is production environment, and the performance is already bad

Best Answer

Not sure it is possible to get exactly what you want. It probably depends on the setup of the application calling your database, how its connections are pooled etc. But the following will give you the number of current connections, plus their IP and application names:

SELECT c.client_net_address, s.[program_name], s.login_name
    , CURRENT_TIMESTAMP, COUNT(*) as ConnCount
FROM sys.dm_exec_connections c 
INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id 
GROUP BY c.client_net_address, s.[program_name], s.login_name

Unfortunately this doesn't indicate the activity on the connections. I don't know if the dmv's will be able to return you a history of all the calls broken down by connection/session, which sounds like what you want. This is exactly the sort of thing extended events are for, so in the absence using extended events, you may be stuck.

Coming from an execution side, you could do it for all queries in the last minute using something like the below:

SELECT  eqs.last_execution_time AS [Date Time], execsql.text AS [Script], [sql_handle]
FROM sys.dm_exec_query_stats eqs
CROSS APPLY sys.dm_exec_sql_text(eqs.[sql_handle]) AS execsql
WHERE eqs.last_execution_time >= DATEADD(mi,-1,CURRENT_TIMESTAMP)
ORDER BY eqs.last_execution_time DESC

Unfortunately this does not show connection information. What you are really after is a combination of the above two, all connections in the past minute and how many executions they performed. Unfortunately the dmv sys.dm_exec_connections only lists the most recent sql query, otherwise it would be a good candidate.

If you really needed this information, you could store both the above queries in tables, running inserts into the tables every minute using a job that is scheduled to run every minute. You could then analyse both tables and make some assumptions about what is calling the procedures.

There may be a way to provide all historic executions from a session/connection using dmv's which I don't know, hopefully this will put you on the right track.

Related Question