How to make SQL Server like MySQL with Slow Query Log (Slow Query Log Slow Log)

How to make SQL Server like MySQL with Slow Query Log (Slow Query Log Slow Log)

SQL Server has long been known to bedenounceOne of the problems is the lack of a slow logging feature like MySQL, where programmers and Ops have no way of knowing the past history of slow query statements for the database.

Because SQL Server by default does not capture the past history of long blocking SQL statements, leading to the assumption that SQL Server does not have a history of slow logging features

In fact, SQL Server provides extended events to allow users to capture the past history of long blocking SQL statements, but because it is not the default factory configuration and set up extended events on the primary user has a certain degree of difficulty, it can be said that this is not a pity, I hope that subsequent versions of SQL Server can be set up by default to slow logging of the relevant extended events, with the primary user can be I hope that subsequent versions of SQL Server can set up slow logging events by default, so that beginners can get started quickly.

Without further ado, this post focuses on the steps to set up extended events for slow logging and to make slow logs available for reading by third-party programs to provide reporting functionality.

Introduction to Extended Events

SQL Server Extended Events (XE) is a lightweight, highly customizable event handling system introduced from SQL Server 2008.
Designed to help database administrators and developers better monitor, debug, and optimize SQL Server performance.
Extended Events can be used to capture and analyze a variety of events that occur within SQL Server in order to identify and resolve performance bottlenecks and issues.

Extended event benefits include lightweight, unified event processing framework and integration. Events are designed to have minimal impact on system performance, ensuring stable operation even in high-load environments.
Extended Events can be used in conjunction with SQL Server Profiler and SQL Server Audit to provide users with comprehensive diagnostic and monitoring tools.

experimental step

Create databases and tables required for the environment

--Window 1
--tabulate

USE testdb
GO

CREATE TABLE Account(id INT, name NVARCHAR(200))


INSERT INTO [dbo].[Account]
SELECT 1,'Lucy'
UNION ALL
SELECT 2,'Tom'
UNION ALL
SELECT 3,'Marry'

--consult (a document etc)
SELECT * FROM [dbo].[Account]

Creating Extended Events

Enter an extended event name

Do not use templates

Event library search for block, select blocked_process_report

Acknowledgement of events

Select the fields you need

Select hereclient_app_name、client_hostname、database_id、database_name、plan_handle、query_hash、request_id、session_id、sql_textfield

Of course you can check off as many fields as you want, so here's just to throw it out there

replenish

Next step directly

It should be noted here that the extended event log can not be saved in full, so users need to consider how long to keep the extended events, assuming that the size of the extended events that can be generated in a day is 1GB, then each extended event file size 1GB, up to 5 extended event files means that you can not query the data 5 days ago

For example, you can't query the previous day 8 for extended events, which are utilized on a rolling basis.

Extended Event Creation Preview

tip: You can click script to generate a create script for this extension event, so that other servers don't have to use the interface to create it in such a tedious way.

Generated Extended Events

CREATE EVENT SESSION [slowquerylog]
ON SERVER
    ADD EVENT sqlserver.blocked_process_report
    (ACTION
     (
         sqlserver.client_app_name,
         sqlserver.client_hostname,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.plan_handle,
         sqlserver.query_hash,
         sqlserver.request_id,
         sqlserver.session_id,
         sqlserver.sql_text
     )
    )
    ADD TARGET package0.event_file
    (SET filename = N'E:\DBExtentEvent\')
WITH
(
    STARTUP_STATE = ON
);
GO

fulfillment

You can check the

a. Start the extension event as soon as it is created.

b. Viewing real-time captured data

Start the extended event immediately

Be sure to set the locked process threshold, otherwise there is no way to catch slow SQL statements, this option is similar to MySQL's long_query_time parameter.

locked process threshold is an option introduced by SQL Server2005, the following settings blocked for 10 seconds will be recorded

--Window 2
--locked process threshold is an option introduced in SQL Server 2005.

--Setting the blocking process threshold
sp_configure 'show advanced options', 1 ;  
GO  
RECONFIGURE ;  
GO  
sp_configure 'blocked process threshold', 10 ; -- 10 seconds
GO  
RECONFIGURE ;  
GO  

Execute an update statement without committing.

--Window 3
USE testdb;
GO

BEGIN tran
update Account
set name ='Test'
where ID = 2

--commit

Query Data

-- Window 4
USE testdb;
GO

-- This query is blocked by transactions in window 3
SELECT * FROM Account
WHERE ID = 2

After execution, you can see that the extension event has been logged

Double-click to view the statements inside the detailed session

It is clear to see who is the blocked statement and who is the actively blocking statement aka source

You can also see that the extension events have been logged to the xel file

Producing Slow Query Log Reports in Other Programming Languages

Microsoft provides an API for querying the contents of extended event XEL files using SQL Server Management Studio (SSMS) and T-SQL.

We can use the sys.fn_xe_file_target_read_file function to read the contents of an XEL file.
You can then export this data to a format that other programming languages can handle

SQL statement

-- Query Extended Event XEL File Contents
SELECT 
    event_data.value('(event/@name)[1]', 'VARCHAR(50)') AS event_name,
    event_data.value('(event/@timestamp)[1]', 'DATETIME2') AS event_timestamp,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'INT') AS duration,
    event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'VARCHAR(255)') AS client_app_name,
    event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'VARCHAR(255)') AS client_hostname,
    event_data.value('(event/action[@name="database_name"]/value)[1]', 'VARCHAR(255)') AS database_name,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'VARCHAR(MAX)') AS sql_text
FROM 
    sys.fn_xe_file_target_read_file('E:\DBExtentEvent\slowquerylog*.xel', NULL, NULL, NULL) AS t
CROSS APPLY 
    t.event_data.nodes('event') AS XEvent(event_data);
    

Reading XEL File Contents with Python
Use the pandas library and the pyodbc driver to export data from SQL Server and process it in Python.
Here is a sample script

import pyodbc
import pandas as pd

# Setting up a database connection
conn = (
    'DRIVER={SQL Server};'
    'SERVER=your_server_name;'
    'DATABASE=your_database_name;'
    'UID=your_username;'
    'PWD=your_password'
)

# Querying the contents of an XEL file
query = """
SELECT 
    event_data.value('(event/@name)[1]', 'VARCHAR(50)') AS event_name,
    event_data.value('(event/@timestamp)[1]', 'DATETIME2') AS event_timestamp,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'INT') AS duration,
    event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'VARCHAR(255)') AS client_app_name,
    event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'VARCHAR(255)') AS client_hostname,
    event_data.value('(event/action[@name="database_name"]/value)[1]', 'VARCHAR(255)') AS database_name,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'VARCHAR(MAX)') AS sql_text
FROM 
    sys.fn_xe_file_target_read_file('E:\DBExtentEvent\slowquerylog*.xel', NULL, NULL, NULL) AS t
CROSS APPLY 
    t.event_data.nodes('event') AS XEvent(event_data);
"""

# Reading data with pandas
df = pd.read_sql(query, conn)

# Close the database connection
()

# Display data
print(df)

# Save data as a CSV file
df.to_csv('', index=False)

One of the problems here is that you can't read the XEL file directly; the XEL file itself is a binary file that must be hooked up to an online SQL Server instance (any SQL Server instance will do; it doesn't have to be the one in the production repository)

Another way is to use a class in PowerShell to parse XEL files directly without hooking into a SQL Server instance.

Reads the contents of an XEL file and then exports a CSV file to be read by other programming languages

Step 1: Create PowerShell Script ReadXELFile.ps1

# Load the required assembly
Add-Type -Path "C:\Program Files\Microsoft SQL Server\140\SDK\Assemblies\"

# Defining XEL file paths
$xelFilePath = "E:\DBExtentEvent\slowquerylog*.xel"

# Creating an XEventData object
$events = New-Object ($xelFilePath)

# Initialize an empty array to store event data
$eventDataList = @()

# Iterate through each event and extract the required fields
foreach ($event in $events) {
    $eventData = New-Object PSObject -Property @{
        EventName      = $event.Name
        Timestamp      = $event.Timestamp
        Duration       = $event.Fields["duration"].Value
        ClientAppName  = $event.Actions["client_app_name"].Value
        ClientHostname = $event.Actions["client_hostname"].Value
        DatabaseName   = $event.Actions["database_name"].Value
        SqlText        = $event.Actions["sql_text"].Value
    }
    $eventDataList += $eventData
}

# Export event data to CSV file
$eventDataList | Export-Csv -Path "E:\DBExtentEvent\" -NoTypeInformation

Step 2: Python Script Read Exported CSV File

import pandas as pd

# Define CSV file path
csv_file_path = "E:\\DBExtentEvent\\"

# Reading CSV files using pandas
df = pd.read_csv(csv_file_path)

# Display data
print(df)

This method requires the use of powershell, which can also be a problem for those who are not familiar with powershell

summarize

This article describes SQL Server's extension to capture slow query statements, or what we call slow logging

Also, be sure to set the“blocked process threshold" parameter, otherwise setting the extension event has no effect

Overall, SQL Server, as an enterprise database, is really not as simple and straightforward as an open source database like MySQL

Need to set up more cumbersome extension events, not very friendly to novice users, the threshold is relatively high, but because the extension event function is very powerful

In addition to catching slow queries you can also catch deadlocks, missing indexes and other performance issues, so this is inevitable

This article is copyrighted and may not be reproduced without the author's permission.