MSSQL Slow log

Elon Musk 161 Reputation points

Recently we finish MYSQL slow log deployment using percona tool. but how can I monitor MSSQL slow log. For example, everytime one statement running over 5s, a job or logic triggered and log some basic information.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,591 questions
{count} votes

Accepted answer
  1. Seeya Xi-MSFT 16,436 Reputation points


    You can use SQL Server Agent to set up a job that runs periodically and checks for slow-running queries in the MSSQL slow log. Here are the steps you can follow:

    1. Create a new job in SQL Server Agent: Right-click on SQL Server Agent in Object Explorer, select New Job, and give the job a name and description.
    2. Add a new step to the job: Click on the Steps tab, and then click New to add a new step. Give the step a name, select "Transact-SQL script (T-SQL)" as the type, and then enter the T-SQL script to check for slow-running queries.

    Here's an example script that checks for queries that have run for more than 5 seconds:

    DECLARE @ThresholdSeconds INT = 5;
        t.text AS query_text,
        qs.total_worker_time / 1000000.0 AS total_worker_time_seconds,
        sys.dm_exec_query_stats qs
        sys.dm_exec_sql_text(qs.sql_handle) t
        qs.total_worker_time / 1000000.0 >= @ThresholdSeconds
        qs.total_worker_time DESC;

    This script will return the top 10 slow-running queries that have run for more than 5 seconds, along with their total execution time and number of times they've been executed.

    Best regards,


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

0 additional answers

Sort by: Most helpful