Alexander Gladchenko

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

• ALTER INDEX REORGANIZE
• AUTO_SHRINK с ALTER DATABASE
• BACKUP DATABASE
• CREATE INDEX
• DBCC CHECKDB
• DBCC CHECKFILEGROUP
• DBCC CHECKTABLE
• DBCC INDEXDEFRAG
• DBCC SHRINKDATABASE
• DBCC SHRINKFILE
• KILL (Transact-SQL)
• RESTORE DATABASE
• UPDATE STATISTICS

В сценарии использованы следующие административные представления и функции:

sys.dm_os_tasks
sys.dm_os_threads
sys.dm_exec_connections
sys.dm_exec_sql_text
sys.dm_os_waiting_tasks
sys.dm_exec_requests

Текст сценария:

-- Процент исполнения длительного запроса ввода-вывода
SELECT ost.session_id
     , DB_NAME(ISNULL(s.dbid,1)) AS dbname
     , er.command
     , er.percent_complete
     , er.status
     , osth.os_thread_id
     , ost.pending_io_count
     , ost.scheduler_id
     , osth.creation_time
     , ec.last_read
     , ec.last_write
     , s.text
     , owt.exec_context_id
     , owt.wait_duration_ms
     , owt.wait_type
FROM   master.sys.dm_os_tasks AS ost
JOIN   master.sys.dm_os_threads AS osth ON ost.worker_address = osth.worker_address
AND    ost.pending_io_count > 0 AND ost.session_id IS NOT NULL
JOIN   master.sys.dm_exec_connections AS ec ON ost.session_id = ec.session_id
CROSS  APPLY master.sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS s
JOIN   master.sys.dm_os_waiting_tasks AS owt ON ost.session_id = owt.session_id
AND    owt.wait_duration_ms > 0
JOIN   master.sys.dm_exec_requests AS er ON ost.session_id = er.session_id
AND    er.percent_complete > 0
ORDER BY ost.session_id
GO

В SQL Server 2008 с помощью службы SQL Server Agent и PowerShell можно достаточно просто соорудить задание, которое будет заглядывать в метаданные WMI локального или удалённого сервера, и сообщать по электронной почте, в случае если свободное место на указанном диске перешагнуло заданный порог. Ниже представлен облегчённый концепт сценария подобного задания (расписаний в нём нет и данные берутся по локальному серверу). Вам нужно будет заменить фиктивный адрес на реальный и указать почтовый профиль, если нельзя воспользоваться профилем по умолчанию.

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job
     @job_name=N'Наблюдение за местом на диске С: текущего сервера',
     @enabled=1,
     @notify_level_eventlog=0,
     @notify_level_email=0,
     @notify_level_netsend=0,
     @notify_level_page=0,
     @delete_level=0,
     @category_name=N'[Uncategorized (Local)]',
     @owner_login_name=N'sa', @job_id = @jobId OUTPUT
EXEC msdb.dbo.sp_add_jobstep
     @job_id=@jobId,
     @step_name=N'1',
     @step_id=1,
     @cmdexec_success_code=0,
     @on_success_action=1,
     @on_success_step_id=0,
     @on_fail_action=3,
     @on_fail_step_id=0,
     @retry_attempts=0,
     @retry_interval=0,
     @os_run_priority=0,
     @subsystem=N'PowerShell',
     @command=N'$RemoteHost = "."
$FreeSpace=((gwmi CIM_LogicalDisk -computerName $RemoteHost -filter "DeviceID = ''C:''").FreeSpace)/(1024*1024*1024)
IF ($FreeSpace -lt 1000) {
$conn = new-object system.data.oledb.oledbconnection
$connstring = "provider=sqloledb;data source=.;initial catalog=tempdb;integrated security=SSPI"
$conn.connectionstring = $connstring
$conn.open()
$cmd = New-Object system.data.oledb.oledbcommand
$cmd.connection = $conn
$FreeSpace = "''"+$FreeSpace+"''"
$sqlquery = "EXEC msdb.dbo.sp_send_dbmail @recipients=''AlexanderGladchenko@domen.com'',@subject=$FreeSpace"
$cmd.commandtext = $sqlquery
$cmd.executenonquery() > NULL
$cmd = New-Object system.data.oledb.oledbcommand
$conn.close()
} Else {$FreeSpace}',
     @database_name=N'master',
     @flags=0
EXEC msdb.dbo.sp_add_jobserver
     @job_id = @jobId,
     @server_name = N'(local)'
GO

Вашему вниманию предлагается сценарий, который для каждой таблицы текущей базы данных показывает статистику по операциям INSERT, UPDATE и DELITE. Кроме этого, вы может оценить, к чему эти операции приводят, с точки зрения роста строк данных, занимаемых страниц и фрагментации. Сценарий основан на использовании двух функций динамического управления: sys.dm_db_index_operational_stats и sys.dm_db_index_physical_stats

/* Внимание!!! Запрос исполняется несколько минут */
SELECT  t.name AS [TableName]
      , fi.page_count AS [Pages]
      , fi.record_count AS [Rows]
      , CAST(fi.avg_record_size_in_bytes AS int) AS [AverageRecordBytes]
      , CAST(fi.avg_fragmentation_in_percent AS int) AS [AverageFragmentationPercent]
      , SUM(iop.leaf_insert_count) AS [Inserts]
      , SUM(iop.leaf_delete_count) AS [Deletes]
      , SUM(iop.leaf_update_count) AS [Updates]
      , SUM(iop.row_lock_count) AS [RowLocks]
      , SUM(iop.page_lock_count) AS [PageLocks]
FROM    sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL) AS iop
JOIN    sys.indexes AS i
ON      ((iop.index_id = i.index_id) AND (iop.object_id = i.object_id))
JOIN    sys.tables AS t
ON      i.object_id = t.object_id
AND     i.type_desc IN ('CLUSTERED', 'HEAP')
JOIN    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS fi
ON      fi.object_id=CAST(t.object_id AS int)
AND     fi.index_id=CAST(i.index_id AS int)
AND     fi.index_id < 2
GROUP BY t.name, fi.page_count, fi.record_count
      , fi.avg_record_size_in_bytes, fi.avg_fragmentation_in_percent
ORDER BY [TableName]

Одной из трудно оптимизируемых задач SQL Server является вставка. Не раз мне приходилось сталкиваться с ситуациями, когда уже и схема оптимизирована под вставку, и сайзинг файлов вставке не препятствует, а желаемой производительности массовой или не массовой вставки достичь не удаётся. Не хватает совсем немногого…

Разработчики SQL Server 2008 позаботились о том, чтобы предоставить нам с вами в распоряжение некую «палочку – выручалочку», которая призвана как раз снизить затраты на вставку, путём её не полного журналирования. Для этого предлагается задействовать на серверах флаг трассировки 610, который по моим наблюдениям действительно может немного облегчить вставку. Флаг, и его побочные эффекты, подробно описан тут: http://msdn.microsoft.com/ru-ru/library/dd425070(en-us).aspx

Ещё одна мера, в дополнение к включению флага трассировки 610, описана в документе вендора: http://technet.microsoft.com/ru-ru/library/cc917672(en-us).aspx. Там, среди прочего, подробно описано исследование того, что будет эффективней, вставка в таблицу с единственным некластеризованным индексом, или вставка в таблицу с единственным кластеризованным индексом. Кластеризация данных может дать выигрыш на вставке до 3%.

Вот такие маленькие хитрости попались мне на глаза в документации Майкрософт. Быть может, кто-нибудь из читателей этого блога поделиться в комментариях своими маленькими хитростями?

В тему:

• Как справиться с PAGELATCH при высоко-параллельных INSERT-нагрузках