January 18, 2018 at 2:38 pm
I need to show Userid, numberofQueries they ran on the day and the averageruntime of the query in seconds...I tried below and it's not working...It just gives me same result...Essentially, i need this on the server level.
If I don't add the logininfo, it works. But I need to show the logininfo as well. Please help.
SELECT TOP 20
-- l.loginname ,
CONVERT(VARCHAR(10), qs.last_execution_time, 101) AS Date ,
DATEPART(HOUR, qs.last_execution_time) AS HourOfTheDay ,
COUNT(*) AS NumberOfQueries ,
AVG(qs.total_elapsed_time / 1000000) AS AverageRunTimeinSecs
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
-- CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
-- JOIN sys.sysusers u ON pa.value = u.uid
-- JOIN syslogins l ON u.sid = l.sid
WHERE qt.encrypted = 0
-- AND pa.attribute = 'user_id'
AND last_execution_time >= DATEADD(MINUTE, -5, CURRENT_TIMESTAMP)
GROUP BY
--l.loginname ,
CONVERT(VARCHAR(10), qs.last_execution_time, 101) ,
DATEPART(HOUR, qs.last_execution_time)
--ORDER BY l.loginname DESC;
January 18, 2018 at 8:42 pm
SQL_Surfer - Thursday, January 18, 2018 2:38 PMI need to show Userid, numberofQueries they ran on the day and the averageruntime of the query in seconds...I tried below and it's not working...It just gives me same result...Essentially, i need this on the server level.If I don't add the logininfo, it works. But I need to show the logininfo as well. Please help.
SELECT TOP 20
-- l.loginname ,
CONVERT(VARCHAR(10), qs.last_execution_time, 101) AS Date ,
DATEPART(HOUR, qs.last_execution_time) AS HourOfTheDay ,
COUNT(*) AS NumberOfQueries ,
AVG(qs.total_elapsed_time / 1000000) AS AverageRunTimeinSecs
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
-- CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
-- JOIN sys.sysusers u ON pa.value = u.uid
-- JOIN syslogins l ON u.sid = l.sid
WHERE qt.encrypted = 0
-- AND pa.attribute = 'user_id'
AND last_execution_time >= DATEADD(MINUTE, -5, CURRENT_TIMESTAMP)
GROUP BY
--l.loginname ,
CONVERT(VARCHAR(10), qs.last_execution_time, 101) ,
DATEPART(HOUR, qs.last_execution_time)
--ORDER BY l.loginname DESC;
You can but that user_id from the plan attributes is probably not what you think. Most plans hopefully would have a -2 for user_id meaning that the plan is shareable among different users. And if not, it doesn't really map back to a user or login in server_principals (or database_principals if you try to use the database id). You can play around with it yourself by querying some table schema qualified and not schema qualified with your login and use some other one.
But that brings up that you may not be getting the data you want. What if a plan is resued - you don't know what users ran it or how many times they ran it when the plan is resued. And what if the query isn't in cache? Then you won't get anything for that. And what if the plan has been recompiled a lot - you don't know what users ran it or how many times.
So you can get close to what you are trying but it's not likely what you are looking for. With your query, you do need to add where pa.value = user_id. And you wouldn't want to join on users or logins (you would want to use principals instead if you did) since who knows what that value maps back to. If you were to change yours a bit and just pull back the user id as it is in the plan attributes, it's not likely to be what you think. It would be something like:
SELECT
pa.value as UserIDValue,
CONVERT(VARCHAR(10), qs.last_execution_time, 101) AS Date ,
DATEPART(HOUR, qs.last_execution_time) AS HourOfTheDay ,
COUNT(*) AS NumberOfQueries ,
AVG(qs.total_elapsed_time / 1000000) AS AverageRunTimeinSecs
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
WHERE qt.encrypted = 0
AND pa.attribute = 'user_id'
AND last_execution_time >= DATEADD(MINUTE, -5, CURRENT_TIMESTAMP)
GROUP BY
pa.value,
CONVERT(VARCHAR(10), qs.last_execution_time, 101) ,
DATEPART(HOUR, qs.last_execution_time)
--ORDER BY l.loginname DESC;
Sue
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply