Sql-server – Log all than queries taking more than X seconds

performancesql serversql-server-2008-r2

I have a database Microsoft SQL Server 2008r2. I have some applications in different programming languages.

I would like to log all the queries that take more than X seconds.

I guess that the I should create an event session, but I don't understand them enough to do this specific task. The ideal answer would include a link to an online tutorial with examples on event sessions; even a book recommendation would go.

I did the training 6231A "Maintaining a Microsoft SQL Server Database" at Microsoft few years ago, but I have not practiced since; the courseware covers the argument but I cannot understand it.

Best Answer

The syntax you're looking for will be very similar to this:


create event session longrunning_statements on server
add event sqlserver.sql_statement_completed
(
where (duration > 5000000)
)
add target package0.asynchronous_file_target
(Set filename='c:\capture\xe_longrunning_statement.xel', 
metadatafile='c:\capture\xe_longrunning_statement.xem');

alter event sesession longrunning_statements on server state=start;

This will set up an event session which will track an sql statement that takes longer than 5 seconds to complete. The duration filter is based on microseconds btw.

If you wanted to restrict this to a specific database (in this case the db with database id=5, then you could change the filter to:

where (duration > 5000000 and database_id=5)

Lots of other filters as well to tie it down to specific sessions or users if required.

This event session will log it's results out to a file (c:\capture\xe_longrunning_statement.xel) which can be read in with the fn_xe_file_target_read_file function like so:

SELECT *
    FROM
        sys.fn_xe_file_target_read_file
        (
            'c:\capture\xe_longrunning_statement*.xel',
            'c:\capture\xe_longrunning_statement.xem',
            null,
            null
        );

(Note that I've used 2008R2 standards for tge xel/xem files and setting up the target. It's slightly different in 2012, but this syntax would still work). The * in the filename is to let the function know you want to load up all of the files if they've rolled over due to size.

You'll then need to parse the data out through your XML tools of choice.

Once you're done with the capture, you can tidy up as follows:


alter event session longrunning_statements on server state=stop;
drop event session longrunning_statements;

One of the best online resources is Jonathan Kehayais' 31 days of Exteneded Events - https://www.sqlskills.com/blogs/jonathan/an-xevent-a-day-31-days-of-extended-events/ - which walks you through the topic with plenty of examples.

Related Question