MSSQL Slow log

Elon Musk 161 Reputation points
2023-02-28T03:02:49.5233333+00:00

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
    2023-02-28T07:04:15.6366667+00:00

    Hi,

    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;
    
    SELECT TOP 10
        t.text AS query_text,
        qs.total_worker_time / 1000000.0 AS total_worker_time_seconds,
        qs.execution_count
    FROM 
        sys.dm_exec_query_stats qs
    CROSS APPLY 
        sys.dm_exec_sql_text(qs.sql_handle) t
    WHERE 
        qs.total_worker_time / 1000000.0 >= @ThresholdSeconds
    ORDER BY 
        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,

    Seeya


    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