Не знаю как вы, а я сторонник использования приемов, с помощью которых можно упростить решение профессиональных задач. В данной статье речь пойдет о простом приеме, с помощью которого мне многократно удавалось найти ответ на вопрос о том, когда завершится тот или иной процесс.

Мои рекомендации предназначены для администраторов баз данных, так как чаще всего этот вопрос возникает по отношению к резервному копированию, обслуживанию индекса, DBCC CHECKDB, восстановлению, возврату в прежнее состояние, а иногда даже DBCC SHRINKFILE. Чтобы воспользоваться предлагаемым приемом, необходимо обладать рабочими знаниями динамических объектов управления (DMO) SQL Server. Мы сосредоточимся на тех объектах DMO, с которыми связаны активные выполняющиеся запросы к экземпляру SQL Server:

  • sys.dm_exec_requests
  • sys.dm_exec_sql_text

sys.dm_exec_requests выдает метаданные для активно выполняющихся запросов на локальном экземпляре SQL Server, а sys.dm_exec_sql_text — функция, которая при передаче sql_handle (уникального идентификатора для пакета текста T-SQL, выполняемого на вашем сервере) возвращает текст T-SQL.

Конечным результатом этого запроса будет любой активно исполняемый запрос с заполненным столбцом percent_complete в sys.dm_exec_requests. Согласно официальной документации (см. документ по адресу: https://docs.microsoft.com/

en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql), для этого объекта DMO данную метрику предоставляют следующие операции:

  • ALTER INDEX REORGANIZE
  • параметр AUTO_SHRINK с ALTER DATABASE
  • BACKUP DATABASE
  • DBCC CHECKDB
  • DBCC CHECKFILEGROUP
  • DBCC CHECKTABLE
  • DBCC INDEXDEFRAG
  • DBCC SHRINKDATABASE
  • DBCC SHRINKFILE
  • RECOVERY
  • RESTORE DATABASE
  • ROLLBACK
  • TDE ENCRYPTION

sys.dm_exec_requests предоставляет sql_handle, в котором нуждается sys.dm_exec_sql_text, чтобы возвратить пакетную команду для запроса. sys.dm_exec_requests также предоставляет информацию через два столбца: statement_start_offset и statement_end_offset, которые идентифицируют текущую инструкцию в пакете, исполняемом в данный момент. Текущий сеанс исключается из результатов путем использования предиката @@spid.

Запрос, рассматриваемый в данной статье, показан в листинге 1. Когда он выполняется, при наличии активности одного из перечисленных выше типов будут возвращены следующие ключевые индикаторы производительности:

  • session_id — уникальный идентификатор для сеанса на локальном экземпляре SQL Server;
  • percent_complete — текущий процент выполнения для выполняемого запроса;
  • elapsed_seconds — сколько времени (в секундах) выполняется этот процесс;
  • wait_type — если процесс ожидает ресурсы, то каков тип ожидания;
  • wait_time — накопленное время ожидания в миллисекундах;
  • last_wait_type — предыдущее время ожидания, если текущей ситуации ожидания предшествовал другой период ожидания;
  • est_completion_time — вычисляемый столбец на основе скорости выполнения, дающий довольно точную оценку времени, когда процесс будет завершен;
  • batch_text — полная инструкция, выполняемая как часть запроса;
  • statement_executing — активная инструкция в выполняемом в данный момент пакете.

Для наглядности я воспользуюсь пакетной командой T-SQL из листинга 2.

Если выделить и запустить только первую команду (DBCC CHECKDB), а затем выполнить запрос, он даст сразу полные данные. После этого результат будет выглядеть примерно так, как показано на рисунке 1.

 

Результаты запроса после запуска DBCC CHECKDB
Рисунок 1. Результаты запроса после запуска DBCC CHECKDB

Если выполнить полный пакет, состоящий из команды DBCC CHECKDB и команды BACKUP, и дождаться начала резервного копирования, прежде чем запросить информацию о завершении, то результат будет выглядеть примерно так, как показано на рисунке 2.

 

Результаты запроса после запуска команд DBCC CHECKDB и BACKUP
Рисунок 2. Результаты запроса после запуска команд DBCC CHECKDB и BACKUP

Конечно, в этом примере используется локальная база данных на моем ноутбуке, поэтому время завершения находится не в отдаленном будущем. Представьте, насколько полезными будут сведения, если вам придется иметь дело с базой данных на 20 Тбайт, как это нередко случается со мной. Я подготовил этот сценарий почти 10 лет назад и с тех пор регулярно использую его. Последний раз это произошло за неделю до написания статьи, после того как я в 2 часа ночи получил сообщение об отказе процесса ETL, от которого зависит длительный процесс резервного копирования. Я потратил больше времени на регистрацию в системе, чем на то, чтобы определить, когда должно было завершиться резервное копирование, а затем заменить задание агентов SQL Server, которое не выполнялось после ожидаемого времени завершения. Поэтому мне не пришлось тратить полчаса на устранение неисправности, и я вернулся в постель, прежде чем успела остыть моя подушка.

Листинг 1. Выявление активных запросов
SET NOCOUNT ON;

SELECT R.session_id
        , R.percent_complete
        , R.total_elapsed_time/1000 AS elapsed_seconds
        , R.wait_type
        , R.wait_time
        , R.last_wait_type
        , DATEADD(s,100/((R.percent_complete)/ (R.total_elapsed_time/1000)), R.start_time)
        AS est_complete_time
        , ST.text AS batch_text

        , CAST(SUBSTRING(ST.text, R.statement_start_offset / 2,
                (
                        CASE WHEN R.statement_end_offset = -1 THEN DATALENGTH(ST.text)
                        ELSE R.statement_end_offset
                        END - R.statement_start_offset
                ) / 2
        ) AS varchar(1024)) AS statement_executing
FROM sys.dm_exec_requests AS R
        CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) AS ST
WHERE R.percent_complete > 0
        AND R.session_id <> @@spid;
Листинг 2. Запуск процессов пакетной командой T-SQL
--Первый DBCC
DBCC CHECKDB (FOO);

--Затем резервная копия
BACKUP DATABASE [FOO] TO  DISK = N'C:\SQL\Backup\FOO.bak'
WITH NOFORMAT,
        NOINIT, 
        NAME = N'FOO-Full Database Backup',
        SKIP, NOREWIND, NOUNLOAD, COMPRESSION, 
        STATS = 10
GO