MSSQL Slow log

Question

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.

Answer 1

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".