Использование sys.dm_exec_sessions в SQL Server для диагностики клиентских подключений

Данный материал является переводом оригинальной статьи "MSSQLTips : Understanding and Using sys.dm_exec_sessions in SQL Server".

Известно, что SQL Server имеет много системных представлений и функций, которые можно использовать, чтобы понять, что происходит с нашим экземпляром SQL Server. Интересно здесь то, что SQL Server может сообщить о нам сессиях, присоединенных пользователях и приложениях. Давайте рассмотрим то, как можно получить подобную информацию.

Представление Dynamic Management View (DMV) sys.dm_exec_sessions даёт отчет о всех сессиях SQL Server. Здесь включены и внутренние процессы. Например, данный запрос скажет нам о процессах, которые созданы нашим SQL Server:

SELECT session_id, login_time, security_id, status
FROM sys.dm_exec_sessions
WHERE host_name IS NULL;

Ключевое здесь host_name в значении NULL. Это важный момент. SQL Server имеет много внутренних процессов, которые запущены и выполняют работу. В результатах этого запроса, мы видим 40 строк:

Однако, в большинстве случаев, нас не интересуют внутренние процессы. Мы больше озабочены подключениями к SQL Server и тем, что они делают. Итак, давайте начнем с простого объединения с sys.dm_exec_connections, чтобы увидеть их.

SELECT DEC.session_id, DEC.protocol_type, DEC.auth_scheme,
  DES.login_name, DES.login_time
FROM sys.dm_exec_sessions AS DES
  JOIN sys.dm_exec_connections AS DEC
    ON DEC.session_id = DES.session_id;

И мы увидим несколько соединений. Таким образом, мы можем быстро получить информацию о подключениях в связке с логинами - то, что мы не можем сделать с помощью только sys.dm_exec_connections.

Заметим также, что мы получили не 40 строк, как ранее, а лишь малую часть. Это связано с тем, что sys.dm_exec_connections возвращает только соединения внутри SQL Server. Поэтому, когда мы объединяем (с INNER JOIN) sys.dm_exec_sessions с sys.dm_exec_connections, мы автоматически отфильтровываем внутренние сессии.

Просмотр подключений к SQL Server и используемых клиентских интерфейсах

Мы продолжим объединение в оставшейся части примеров, потому что, вероятно, вы будете использовать комбинацию sys.dm_exec_connections и sys.dm_exec_sessions в вашем поиске и устранении неисправностей. Однако для целей этих примеров больше не нужна информация из sys.dm_exec_connections, потому что мы демонстрируем, что можем найти в sys.dm_exec_sessions. Здесь нам важно понять - кто подключается, что они используют для подключения, и какой клиентский интерфейс они используют.

SELECT DES.session_id, DES.login_name, DES.program_name, DES.client_interface_name
FROM sys.dm_exec_sessions AS DES
  JOIN sys.dm_exec_connections AS DEC
    ON DEC.session_id = DES.session_id;

Например, если пользователи не должны подключаться через Excel к базе данных, но я считаю, что кто-то, у кого есть нужные права, нарушил это правило, я обычно вижу что-то, что даст мне возможность продолжить исследование с использованием этого запроса. Как мы видим здесь, кто-то подключился через Microsoft Office 2010 (логин FromExcel):

Это не говорит нам точно, что это Microsoft Excel, но это отправная точка. Если мы не увидим ничего, кроме соединений через конкретное приложение и, возможно, некоторые административные логины через SQL Server Management Studio (SSMS), то мы знаем, что есть проблема. Существует такая уловка с именем program_name. Это то, что идентифицирует приложение. Таким образом, опытный пользователь может обманывать нас через иное имя. Это можно сделать, особенно с помощью File DSN, созданный через Data Sources (ODBC) в Administrative Tools для ОС. Также обратите внимание, что мы видим, через какую библиотеку / интерфейс сеанс был выполнен. В этом случае мы видим, что два соединения через SSMS были сделаны с помощью .Net SQLClient Data Provider. Мы видим, что интерфейс из Office был OLEDB. Если вы пытаетесь устранить неполадку, связанную с клиентским интерфейсом, то это полезная информация.

Просмотр настроек сеанса клиента SQL Server

Другим примером того, что мы, возможно, захотим устранить, является то, что у пользователя возникают проблемы внутри конкретной базы данных, особенно если они говорят, что запросы, которые, как мы знаем, должны работать, у них не работают. Обычный пример - когда NULL-ы начинают вести себя иначе, чем мы ожидаем.

SELECT DES.session_id,DES.login_name, DB_NAME(DES.database_id) AS DB,
  DES.date_format, DES.quoted_identifier, DES.ansi_nulls
FROM sys.dm_exec_sessions AS DES
  JOIN sys.dm_exec_connections AS DEC
    ON DEC.session_id = DES.session_id;

Если мы посмотрим на результаты этого запроса для примера, который я сгенерировал, мы увидим нечто интересное:

Обратите внимание, что одна из записей DemoLogin находится в базе данных AdventureWorks2014 и что ее значение ansi_nulls равно 0 (OFF). Это отличается от значения по умолчанию, которое равно 1 (ON). Специфичные для сеанса настройки, такие как формат даты, используемый сеансом (здесь все используют mdy, формат по умолчанию в США), как обрабатываются NULLs, если они используют идентификаторы в кавычках для запросов и т.д., все это можно найти с помощью sys.dm_exec_sessions. В результате, если у вас есть пользователи со странными результатами по своим запросам, но, все же, запросы выполняются отлично для всех остальных, и это не проблема с разрешениями, проверка настроек сеанса может выявить основную причину.

Следующие шаги

• Ознакомьтесь со статьёй "Return SQL Server Connections Information Using sys.dm_exec_connections", чтобы узнать о том, как ещё использовать DMV.

• Узнайте как использовать sys.dm_exec_input_buffer для получения последней команды T-SQL по статье "Retrieve Actively Running T-SQL Statements from SQL Server".

• Научитесь сравнивать различные сессионные настройки с использованием sys.dm_exec_sessions по статье "Find, compare and use the same session settings as another SQL Server user".