Анализ активных сессий в БД MS SQL

Описание показателей¶

Пример скрипта и результаты его выполнения¶

Пример скрипта:

SELECT  host_name AS [ID источника подключения],
        CASE
            WHEN host_name = 'DC1-AS001' THEN 'Сервер приложений 1'
            WHEN host_name = 'DC1-AS002' THEN 'Сервер приложений 2'
            WHEN host_name = 'DC2-AS003' THEN 'Сервер приложений 3'
            WHEN host_name = 'DC1-SQL' THEN 'Сервер БД'
            WHEN host_name = 'MOS-WJR2' THEN 'Иванов И.И.'
            WHEN host_name = 'SPB-OS25' THEN 'Петров П.С.'
        END AS [Источник подключения],
        COUNT(*) AS [Количество активных сессий],
        AVG((cpu_time * 1.) / 1000) AS [Cреднее время использования CPU (сек.)],
        AVG((total_elapsed_time * 1.) / 1000) AS [Среднее общее время выполненния операций (сек.)],
        AVG((total_scheduled_time * 1.) / 1000) AS [Среднее запланированное время выполнения операций (сек.)],
        AVG(memory_usage * 1.) AS [Среднее использование памяти],
        AVG(reads * 1.) AS [Среднее количество операций чтения],
        AVG(writes * 1.) AS [Среднее количество операций записи],
        CASE
            WHEN AVG(cpu_time * 1.) > 0 AND AVG(total_elapsed_time * 1.) > 0 THEN AVG(cpu_time * 1.) / AVG(total_elapsed_time * 1.)
            ELSE 0
        END AS [Доля использования CPU],
        COUNT((CASE WHEN cpu_time > 60000 THEN cpu_time END * 1.)/1000) AS [Операции >6 сек],
        COUNT((CASE WHEN cpu_time > 300000 THEN cpu_time END * 1.)/1000) AS [Операции >5 мин],
        AVG(CASE WHEN status = 'sleeping' THEN DATEDIFF(SECOND, last_request_end_time, GETDATE()) END) AS [Время простоя (сек.)],
FROM sys.dm_exec_sessions
GROUP BY host_name
ORDER BY [Пользователи БД];

Пример результата выполнения скрипта:

🔍 Пример детального анализа данных из примера по группам¶

1. Неизвестные подключения (NULL)¶

Вывод:¶

• Системный анализ: Системные/фоновые процессы, не имеющие host_name
• Бизнес-анализ: Неидентифицируемые подключения — требуется выяснить их природу
• Анализ данных: Требуют дополнительного исследования через program_name и login_name

2. Иванов И.И. (6 сессий)¶

Вывод:¶

• Основная проблема: Экстремально высокие операции чтения и время простоя
• Вторичная проблема: Избыточное количество сессий
• CPU используется эффективно (42.3%), но операции слишком тяжелые

3. Петров П.С. (30 сессий)¶

Вывод:¶

• Катастрофическая ситуация по всем метрикам
• Наибольшая нагрузка на систему среди всех пользователей
• Имеются сверхдлительные операции (>5 минут CPU времени)
• Эффективность CPU нормальная, но объем работы экстремальный

4. Сервер БД (6 сессий)¶

Вывод:¶

• Низкая эффективность CPU — всего 15.4% (много времени в ожидании)
• Высокая нагрузка записи — возможны фоновые процессы обслуживания
• Катастрофическое время простоя — сессии не закрываются 14.5 дней!

5. Серверы приложений¶

DC1-AS001 (103 сессии)¶

DC1-AS002 (84 сессии)¶

DC1-AS003 (102 сессии)¶

Вывод по серверам приложений:¶

• Работают стабильно и эффективно
• Низкая доля CPU указывает на быстрые запросы с минимальными вычислениями
• Отличное управление соединениями — простоя почти нет

📋 Сводный анализ проблем по приоритетам¶

🔴 КРИТИЧЕСКИЕ ПРОБЛЕМЫ (Требуют немедленного вмешательства)¶

1. Катастрофические утечки соединений:
   • Сервер БД: 1,250,104 сек (14.5 дней)
   • Петров П.С.: 863,687 сек (10 дней)
   • Иванов И.И.: 162,735 сек (1.9 дня)
2. Экстремальные операции чтения:
   • Петров П.С.: 554,270 операций/сессию
   • Иванов И.И.: 339,852 операций/сессию
3. Сверхдлительные операции CPU:
   • Петров П.С.: операции >5 минут CPU времени

⚠️ СЕРЬЕЗНЫЕ ПРОБЛЕМЫ (Требуют срочной оптимизации)¶

1. Высокое время выполнения запросов:
   • Петров П.С.: 39.34 сек в среднем
   • Иванов И.И.: 15.15 сек в среднем
2. Избыточное количество сессий:
   • Петров П.С.: 30 сессий (норма: 1-5)
3. Низкая эффективность сервера БД:
   • Доля CPU всего 15.4% — много времени в ожидании

🛠️ Рекомендации по действиям¶

НЕМЕДЛЕННЫЕ ДЕЙСТВИЯ:

-- Закрыть висящие соединения
SELECT
    session_id,
    login_name,
    host_name,
    DATEDIFF(HOUR, last_request_end_time, GETDATE()) as idle_hours
FROM sys.dm_exec_sessions
WHERE status = 'sleeping'
AND DATEDIFF(HOUR, last_request_end_time, GETDATE()) > 1
ORDER BY idle_hours DESC;

-- KILL session_id для соединений старше 24 часов

ОПТИМИЗАЦИЯ:¶

1. Анализ запросов Петрова П.С. — найти и оптимизировать операции с 554K reads
2. Настройка пула соединений для пользовательских приложений
3. Внедрение мониторинга длительных операций в реальном времени
4. Оптимизация индексов для снижения операций чтения

ПРОФИЛАКТИКА:¶

1. Установка лимитов на время выполнения запросов
2. Настройка автоматического закрытия неактивных соединений
3. Обучение пользователей правильной работе с БД

📝 Заключение¶

Система находится в критическом состоянии из-за проблем управления соединениями и неоптимизированных запросов. Требуются срочные меры для предотвращения коллапса.