Click here to Skip to main content
15,881,172 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
Hi,
first of all Tones of thanks for helping !

I am looking for efficient ways to monitor SQL server 2014 onwards activity details with following column list for the executed / Past query (completed query) which i would like to pull from server at every 5 minute basically.

Please share query or detailed ways which i can try.

db_names
    ,r.STATUS
    ,r.blocking_session_id AS 'blocked_by' 
    , Blocked session
    ,r.wait_type
    ,r.wait_resource
    ,wait_time
    ,r.cpu_time
    ,r.logical_reads
    ,r.reads
    ,r.writes
,query_text
,Query_plan
,User_name
,program_name


What I have tried:

This web link has same details but this is only for current running server and does not track for past / executed query
https://sqlserver-help.com/2014/07/17/script-find-currently-executing-queries-blocking-waits-statement-procedure-cpu/
Posted
Updated 1-Feb-23 4:25am

Firstly "monitor" tends to refer to things that are running rather than analysing things that have happened in the past - changing your choice of words may open up your results when doing internet searches. E.g. you might come across this How to Check SQL Server Query History - Devart Blog[^]
or View the Job History - SQL Server Agent | Microsoft Learn[^] or even https://dev.to/jobgemws/how-to-see-a-history-of-queries-ran-on-a-sql-server-3fko[^]
Note - how far back you can go will depend on how the SQL Server Instance/SSMS has been set up
 
Share this answer
 
Comments
chandan kumar 2-Feb-23 11:54am    
Many Thanks, I really liked this link :https://blog.devart.com/sql-server-query-history.html
However 2 important column was still not found ie Wait duration and Wait type. Any idea which DMV , i should append to the given query in this link to get waits related information ?

Many Thanks
CHill60 2-Feb-23 12:01pm    
I've not come across information on Wait in logs before - it really does depend on how the trace has been set up - check out the links to the SQL Server Profiler - either via the 3rd link in my solution or the link in solution 2.
We left the "things have had to wait" side of things to the monitor, I was only concerned with which queries had been run
To monitor the SQL activity and log executed queries, you can use a tool such as the SQL Server Profiler, which is a graphical user interface provided by Microsoft SQL Server.

You can also use the following stored procedures to create a trace that captures the executed SQL statements.
sp_trace_create
sp_trace_setevent

You can also visit the following link regarding above store procedures:
sp_trace_create (Transact-SQL) - SQL Server | Microsoft Learn[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900