Логирование запросов с помощью процедуры WhoIsActive

Время прочтения: 4 мин.

Существуют различные способы идентификации запросов на сервере: системные процедуры sp_who и sp_who2, Activity Monitor, Query Store.

Но в этом материале речь пойдёт о хранимой процедуре sp_WhoIsActive. Она не является разработкой Microsoft, а создана программистом Адамом Мачаником и распространяется бесплатно.

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

Итак, скрипт, с помощью которого можно запустить процедуру с созданием соответствующих таблиц в случае их отсутствия:

SET NOCOUNT ON;

DECLARE
        --логирующая таблица
        @dst_tbl varchar(500) = 'MySchema.WhoIsActive',
	 --временная таблица, куда мы будем писать результаты работы WhoIsActive
	 @dst_tmp_tbl varchar(500) = '##WhoIsActiveTmp',
	 --база данных, где будет лежать логирующая таблица;
        @dst_db varchar(100) = 'DestDb',
	 --база данных, запросы к которой нужно логировать
	 @log_db varchar(100)= 'MyDb',
	 --переменная, содержащая SQL-запрос
        @SQL nvarchar(4000),
	 --параметры
        @params nvarchar(500),
	 --флаг существования объекта
        @exists bit;

SET @dst_tbl = @dst_db + '.' + @dst_tbl;
 
--проверим существует ли логирующая таблица с соответствующим названием
IF OBJECT_ID(@dst_tbl) IS NULL
BEGIN;
--получим с помощью параметра @schema информацию о возвращаемых значениях sp_WhoIsActive
        EXEC dbo.sp_WhoIsActive  @get_transaction_info = 1, @get_outer_command = 1,
                        @get_plans = 1, @return_schema = 1, @schema = @SQL OUTPUT;
	 --инициализация запроса для создания логирующей таблицы
        SET @SQL = REPLACE (@SQL, '<table_name>', @dst_tbl);
	 --CREATE TABLE
        EXEC (@SQL);

END;

-- создадим временную таблицу для того, чтобы поместить туда результат WhoIsActive для последующей фильтрации
--проверяем наличие временной таблицы
IF OBJECT_ID ('tempdb..'+@dst_tmp_tbl) IS NULL
BEGIN;
	 --аналогично запускаем CREATE TABLE для временной таблицы
 EXEC dbo.sp_WhoIsActive  @get_transaction_info = 1, @get_outer_command = 1,
                        @get_plans = 1, @return_schema = 1, @schema = @SQL OUTPUT;
        SET @SQL = REPLACE(@SQL, '<table_name>', @dst_tmp_tbl);
        EXEC (@SQL);
END;

--проверим наличие кластерного индекса на таблице
SET @SQL
      = 'USE ' + @dst_db +  ' IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(@dst_tbl) AND name = N''PK_WhoIsActive'') SET @exists = 0';
SET @params = N'@dst_tbl varchar(500), @exists bit OUTPUT';
EXEC sys.sp_executesql @SQL, @params, @dst_tbl = @dst_tbl, @exists = @exists OUTPUT;

--чтобы поиск по логирующей таблице был пошустрей, создадим на ней кластерный индекс
IF @exists = 0
    BEGIN;
        SET @SQL = 'CREATE CLUSTERED INDEX PK_WhoIsActive ON ' + @dst_tbl + '(collection_time ASC)';
        EXEC (@SQL);
    END;

--запускаем скрипт, записываем результат во временную таблицу
EXEC dbo.sp_WhoIsActive @get_transaction_info = 1,
                        @get_outer_command = 1,
                        @get_plans = 1,
                        @destination_table= @dst_tmp_tbl;

--переместим из временной таблицы в логирующую
SET @SQL = '						
	INSERT INTO ' + @dst_tbl + 
	+ ' SELECT *
		FROM ' + @dst_tmp_tbl + '
		WHERE [database_name] = ' +  QUOTENAME(@log_db, '''');
EXEC (@SQL)

EXEC ('TRUNCATE TABLE ' + @dst_tmp_tbl);

Далее в Агент SQL Server, создаём задание, добавляем скрипт и ставим на расписание, допустим, каждые 15 минут:

После этого вставляем скрипт в шаг задания:

В результате мы получили инструмент, который каждые 15 минут логирует текущую активность в базе данных. Конечно, срез в 15 минут – это слишком большой интервал времени. Гораздо уместнее запускать процедуру каждые, например, 30 секунд, так как, порой, выполнение запроса укладывается в считанные секунды. И всё же, длительные запросы, которые часто представляют гораздо больший интерес из-за своей “тяжести”, эдакие киты среди запросов, мы без проблем обнаружим.