Как поймёшь какой запрос выполняется на фронте через explain в MSSQL?

Использование плана выполнения в MSSQL для анализа запросов с фронтенда

Чтобы понять, какой запрос выполняется на фронтенде через EXPLAIN в MSSQL, нужно комбинировать несколько подходов, так как MSSQL не имеет прямого аналога EXPLAIN из MySQL/PostgreSQL. Вот комплексная стратегия:

1. Основные инструменты анализа планов выполнения

В MSSQL используется Actual Execution Plan и Estimated Execution Plan, которые можно получить несколькими способами:

Включение графического плана выполнения в SSMS:

-- Перед выполнением запроса включите опцию "Include Actual Execution Plan" (Ctrl+M)
-- или используйте:
SET STATISTICS PROFILE ON;
-- Выполните запрос
SELECT * FROM Users WHERE Email = 'user@example.com';
-- Затем просмотрите вкладку "Execution Plan"

Использование системных представлений:

-- Для текущих выполняемых запросов
SELECT 
    r.session_id,
    t.text AS query_text,
    p.query_plan,
    r.status,
    r.cpu_time,
    r.logical_reads
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) p
WHERE r.status = 'running';

2. Анализ запросов с фронтенда

Когда запрос приходит с фронтенда, нужно:

Идентифицировать проблемный запрос через мониторинг:

-- Найти самые ресурсоемкие запросы
SELECT TOP 10
    qs.execution_count,
    qs.total_worker_time/qs.execution_count AS avg_cpu_time,
    qs.total_logical_reads/qs.execution_count AS avg_logical_reads,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2) + 1) AS query_text,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC;

3. Практические шаги для анализа

Шаг 1: Захват выполняемого запроса

• Используйте SQL Server Profiler или Extended Events для отслеживания входящих запросов
• Настройте фильтрацию по конкретному приложению или пользователю

Шаг 2: Анализ плана выполнения

<!-- В плане выполнения обращайте внимание на: -->
1. <RelOp NodeId="1" PhysicalOp="Index Scan" LogicalOp="Index Scan">
2. Стоимость операций (Estimated Subtree Cost)
3. Предупреждения (Warnings) - желтые значки
4. Операции с высоким % стоимости
5. Отсутствующие индексы (Missing Indexes)

Шаг 3: Ключевые метрики для анализа

• Logical Reads - количество страниц, прочитанных из памяти
• CPU Time - время процессора
• Elapsed Time - общее время выполнения
• Index Scans vs Index Seeks - сканирование обычно медленнее поиска

4. Автоматизация мониторинга

Создание системы мониторинга проблемных запросов:

-- Хранение истории планов выполнения
CREATE TABLE QueryPlanHistory (
    Id INT IDENTITY PRIMARY KEY,
    QueryHash BINARY(8),
    QueryText NVARCHAR(MAX),
    QueryPlan XML,
    ExecutionCount INT,
    AvgDuration FLOAT,
    CreatedDate DATETIME DEFAULT GETDATE()
);

-- Регулярный сбор статистики
INSERT INTO QueryPlanHistory (QueryHash, QueryText, QueryPlan, ExecutionCount, AvgDuration)
SELECT 
    qs.query_hash,
    st.text,
    qp.query_plan,
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.last_execution_time > DATEADD(HOUR, -1, GETDATE());

5. Интеграция с фронтендом

Для эффективного отслеживания:

1. Логирование запросов - добавляйте комментарии с фронтенда:

/* Frontend: UserDashboard; Action: GetUserProfile; UserId: 12345 */
SELECT * FROM Users WHERE Id = @userId;

1. Использование Query Store (SQL Server 2016+):

-- Включение Query Store
ALTER DATABASE YourDatabase SET QUERY_STORE = ON;
ALTER DATABASE YourDatabase SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    INTERVAL_LENGTH_MINUTES = 60
);

-- Анализ регрессий производительности
SELECT * FROM sys.query_store_wait_stats;

1. Корреляция с логами приложения - используйте XEvents для связи SQL-запросов с HTTP-запросами фронтенда.

6. Рекомендации по оптимизации

Частые проблемы и решения:

Проблема N+1 запросов (типична для ORM):

• Вместо отдельных запросов для связанных данных используйте JOIN или EXISTS
• Настройте eager loading в ORM

Отсутствующие индексы:

-- Анализ рекомендаций по индексам
SELECT 
    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    'CREATE INDEX [IX_' + OBJECT_NAME(mid.object_id) + '_' + REPLACE(REPLACE(mid.equality_columns, ', ', '_'), '[', '') + '] ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') + 
    CASE WHEN mid.inequality_columns IS NULL THEN '' ELSE CASE WHEN mid.equality_columns IS NULL THEN '' ELSE ',' END + mid.inequality_columns END + ')' + 
    ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC;

Заключение

Для эффективного анализа запросов с фронтенда в MSSQL требуется:

1. Постоянный мониторинг через DMV и Query Store
2. Проактивный анализ планов выполнения
3. Интеграцию с логами приложения для корреляции
4. Регулярную оптимизацию на основе собранных данных

Используйте Extended Events для сложных сценариев отладки, так как они имеют минимальное влияние на производительность и предоставляют детальную информацию о выполнении запросов в реальном времени. Комбинация этих методов позволит точно определить, какие запросы с фронтенда вызывают проблемы производительности, и эффективно их оптимизировать.