October 2, 2018 at 7:35 am
Is there a way / TSQL to see last <N> queries run by anyone on an instance of SQL Server or a particular DB that does not require VIEW SERVER STATE permissions?
When I run something like the below statement, I get "VIEW SERVER STATE permission was denied on object 'server', database 'master'. " error. It would take too long (weeks..)
to request and get such permissions in our shop but I need (preferably) to see who is running what today and tomorrow.
When I try to use Profiler, I get 'ALTER TRACE' permission denied error. Same thing, it would take way to long to obtain this permission as well. Is there any other way?
Thank you!
SELECT txt.TEXT AS [SQL Statement], qs.EXECUTION_COUNT [No. Times Executed],
qs.LAST_EXECUTION_TIME AS [Last Time Executed], DB_NAME(txt.dbid) AS [Database]
FROM SYS.DM_EXEC_QUERY_STATS AS qs CROSS APPLY SYS.DM_EXEC_SQL_TEXT(qs.SQL_HANDLE) AS txt
WHERE txt.dbid = DB_ID('WideWorldImporters')
ORDER BY qs.LAST_EXECUTION_TIME DESC
Likes to play Chess
October 4, 2018 at 4:28 pm
As far as I know you would need to set up some sort of activity collection that logs to a table. But even then, you might miss some things if the query is ran in between activity collection.
October 4, 2018 at 9:13 pm
VoldemarG - Tuesday, October 2, 2018 7:35 AMIs there a way / TSQL to see last <N> queries run by anyone on an instance of SQL Server or a particular DB that does not require VIEW SERVER STATE permissions?
When I run something like the below statement, I get "VIEW SERVER STATE permission was denied on object 'server', database 'master'. " error. It would take too long (weeks..)
to request and get such permissions in our shop but I need (preferably) to see who is running what today and tomorrow.
When I try to use Profiler, I get 'ALTER TRACE' permission denied error. Same thing, it would take way to long to obtain this permission as well. Is there any other way?Thank you!
SELECT txt.TEXT AS [SQL Statement], qs.EXECUTION_COUNT [No. Times Executed],
qs.LAST_EXECUTION_TIME AS [Last Time Executed], DB_NAME(txt.dbid) AS [Database]
FROM SYS.DM_EXEC_QUERY_STATS AS qs CROSS APPLY SYS.DM_EXEC_SQL_TEXT(qs.SQL_HANDLE) AS txt
WHERE txt.dbid = DB_ID('WideWorldImporters')
ORDER BY qs.LAST_EXECUTION_TIME DESC
I'm curious. Why do you need to know such a thing?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 9, 2018 at 11:33 pm
Here is the query to find all the queries in SQL Server
SELECT dest.TEXT AS [Query],
deqs.execution_count [Count],
deqs.last_execution_time AS [Time]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC
But the problem is there is no such guarantee that it will be accurate. It will do only the basic diagnosis of the system. Other option is to use SysTools SQL Log Analyser. The tool provides you the query detail according to the transaction name, time, date.
October 10, 2018 at 7:44 am
Jeff Moden - Thursday, October 4, 2018 9:13 PMVoldemarG - Tuesday, October 2, 2018 7:35 AMIs there a way / TSQL to see last <N> queries run by anyone on an instance of SQL Server or a particular DB that does not require VIEW SERVER STATE permissions?
When I run something like the below statement, I get "VIEW SERVER STATE permission was denied on object 'server', database 'master'. " error. It would take too long (weeks..)
to request and get such permissions in our shop but I need (preferably) to see who is running what today and tomorrow.
When I try to use Profiler, I get 'ALTER TRACE' permission denied error. Same thing, it would take way to long to obtain this permission as well. Is there any other way?Thank you!
SELECT txt.TEXT AS [SQL Statement], qs.EXECUTION_COUNT [No. Times Executed],
qs.LAST_EXECUTION_TIME AS [Last Time Executed], DB_NAME(txt.dbid) AS [Database]
FROM SYS.DM_EXEC_QUERY_STATS AS qs CROSS APPLY SYS.DM_EXEC_SQL_TEXT(qs.SQL_HANDLE) AS txt
WHERE txt.dbid = DB_ID('WideWorldImporters')
ORDER BY qs.LAST_EXECUTION_TIME DESCI'm curious. Why do you need to know such a thing?
BTW, I'm not asking the question above to trivialize your question in any manner. There are many different ways to do such a thing but they all have different capabilities and levels of obnoxiousness. Knowing the reason why you're looking for such information will help people decide which method would be better served to help you accomplish what you need.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 10, 2018 at 7:51 am
Johnson Welch - Tuesday, October 9, 2018 11:33 PMHere is the query to find all the queries in SQL Server
SELECT dest.TEXT AS [Query],
deqs.execution_count [Count],
deqs.last_execution_time AS [Time]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC
But the problem is there is no such guarantee that it will be accurate. It will do only the basic diagnosis of the system. Other option is to use SysTools SQL Log Analyser. The tool provides you the query detail according to the transaction name, time, date.
The reason is simply to know what team members are working and which r not 🙂
Likes to play Chess
October 10, 2018 at 8:02 am
VoldemarG - Wednesday, October 10, 2018 7:51 AMThe reason is simply to know what team members are working and which r not 🙂
Well, VIEW SERVER STATE or not, that query isn't going to tell you who executed what statement. Or does the smiley face indicate that your comment was somewhat facetious? I wouldn't want to work for an organisation where my productivity were measured in that way. You could try SQL Audit or Extended Events if you're determined to go through with it.
John
October 10, 2018 at 8:29 am
John Mitchell-245523 - Wednesday, October 10, 2018 8:02 AMVoldemarG - Wednesday, October 10, 2018 7:51 AMThe reason is simply to know what team members are working and which r not 🙂Well, VIEW SERVER STATE or not, that query isn't going to tell you who executed what statement. Or does the smiley face indicate that your comment was somewhat facetious? I wouldn't want to work for an organisation where my productivity were measured in that way. You could try SQL Audit or Extended Events if you're determined to go through with it.
John
Well, another reson is that when server gets slow and resources overconsumed, i want to know which query RUN BY WHOM could ve caused it last 30 or 60 minutes. So i can reach out to that person and see if i can kill his spid or not.
Likes to play Chess
October 10, 2018 at 8:31 am
If the spid is in a killable state you can see whose it is anyway.
October 10, 2018 at 8:37 am
VoldemarG - Wednesday, October 10, 2018 8:29 AMWell, another reson is that when server gets slow and resources overconsumed, i want to know which query RUN BY WHOM could ve caused it last 30 or 60 minutes. So i can reach out to that person and see if i can kill his spid or not.
A noble ambition, but a query that returns SQL statement, number of executions, time executed and database isn't going to realise that. And such data won't be added to the plan cache until the offending query has completed, so it won't work for current activity: for that you'd have to go with Beatrix's suggestion and use sp_who2, sp_whoisactive or something like that.
John
October 10, 2018 at 8:47 am
John Mitchell-245523 - Wednesday, October 10, 2018 8:37 AMVoldemarG - Wednesday, October 10, 2018 8:29 AMWell, another reson is that when server gets slow and resources overconsumed, i want to know which query RUN BY WHOM could ve caused it last 30 or 60 minutes. So i can reach out to that person and see if i can kill his spid or not.A noble ambition, but a query that returns SQL statement, number of executions, time executed and database isn't going to realise that. And such data won't be added to the plan cache until the offending query has completed, so it won't work for current activity: for that you'd have to go with Beatrix's suggestion and use sp_who2, sp_whoisactive or something like that.
John
Well.. theres always the SP_WHOISACTIVE by Adam Mechanic....
Likes to play Chess
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply