Мониторинг активных подключений к SQL Server: команды и детали

Быстрый ответ

Чтобы узнать о существующих в данный момент подключениях к SQL Server, следует выполнить T-SQL запрос:

SELECT 
    spid,  -- уникальный номер процесса
    status,  -- состояние сессии
    loginame,  -- логин пользователя
    hostname,  -- имя хоста
    db_name(dbid) as 'Database',  -- название базы данных
    cmd   -- выполняемая команда
FROM 
    sys.sysprocesses
WHERE 
    spid > 50;

Этот запрос отобразит ключевую информацию: идентификатор сессии (spid), ее статус, имя пользователя (loginame), имя хоста (hostname), название базы данных и текущую команду.

Инструментарий: доступные варианты

SQL Server предлагает несколько инструментов для мониторинга активных подключений:

• Монитор активности (Activity Monitor): визуализирует статистику подключений в SQL Server Management Studio, включая IP-адреса клиентов и используемые ими базы данных.
• Хранимые процедуры: sp_who и sp_who2 дают подробную информацию о всех текущих сессиях и подключениях.
• Динамические представления управления (DMV): sys.dm_exec_sessions и sys.dm_exec_connections предоставляют детальные сведения об активных подключениях.

Подробный анализ: детализированный анализ

Для более глубокого анализа подключений можно использовать:

• Процедуру sp_who c фильтрацией для отбора данных по конкретным сессиям или базам данных.
• sys.dm_exec_sql_text и функцию COALESCE для получения SQL-запроса, выполняемого в данный момент.
• @@SPID применяется для исключения собственной сессии из результатов, чтобы сделать данные более актуальными для анализа.
• sys.dm_tran_locks помогает выявить подключения, вызывающие конфликты блокировок.

Здоровье системы: мониторинг и оптимизация

Регулярный контроль активных подключений способствует повышению производительности SQL Server:

• Использование I/O: мониторинг параметров num_reads, num_writes и last_read поможет понять, как используются ресурсы чтения и записи информации.
• Блокировки запросов: постоянное отслеживание позволяет определить активные и ожидающие запросы на блокировку, что крайне важно для предотвращения взаимных блокировок.

Администрирование: права доступа и последствия

При работе с информацией о подключениях следует помнить о следующем:

• Необходимо иметь соответствующие права доступа к DMV.
• Следует с осторожностью использовать команду KILL для завершения сессий — только как крайнюю меру.

Визуализация

В качестве аналогии можно представить SQL Server как железнодорожный вокзал (🚉), где подключения – это поезда:

SELECT 
    DB_NAME(dbid) as DatabaseName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame;

Результат будет напоминать табло отправления поездов:

| Пункт назначения (База Данных) | Количество поездов (Подключений) | Имя пассажира (Логин) |
| ------------------------------- | -------------------------------- | --------------------- |
| БухгалтерияDB                   | 3                                | JohnDoe               |
| ПродажиDB                       | 5                                | JaneSmith             |
| ОтделКадровDB                   | 2                                | MikeBrown             |

Каждая строка отображает количество активных подключений для каждой базы данных, а также имена пользователей.

Решение проблем: распространенные сложности

В процессе мониторинга подключений встречается ряд типичных проблем:

• Блокировка процессов: Периодические проверки помогут избежать отключения баз данных из-за заблокированных сессий.
• Незавершённые запросы: Совместное использование sys.dm_exec_requests и sys.dm_exec_sql_text поможет определить и завершить времянка.
• Чрезмерное количество подключений: Счетчики подключений позволят выявить узкие места, связанные с незавершенными транзакциями или зависшими сеансами.

Автоматизация: программное решение проблем

Автоматизация может упростить управление проблемами подключений:

• Использование скриптов для отправки предупреждений о нестандартных паттернах подключений.
• Внедрение инструментов мониторинга в реальном времени, которые оперативно уведомляют администраторов.
• Создание настраиваемых дашбордов мониторинга, показывающих ключевые индикаторы из различных DMV.

Полезные материалы

1. Проблема с производительностью хранимой процедуры в отчете Sharepoint
2. Ускоряем SQL Server запросы с PREFETCH
3. SQL Server Пинала Дэва – Найти выполняющийся в данный момент запрос
4. Управление активными подключениями в учебнике SQL Server
5. Страница удалена или недоступна – TechNet Wiki