April 2009

You are currently browsing the monthly archive for April 2009.


Как вы наверняка знаете, повторное исполнение команды 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
  •  

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

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

    -- Процент исполнения длительного запроса ввода-вывода
    SELECT ost.session_id
         , DB_NAME(ISNULL(s.dbid,1)) AS dbname
         , er.command
         , er.percent_complete
         , dateadd (ms, er.estimated_completion_time, getdate()) AS [Прогноз завершения]
         , 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 2008, на предыдущих не смотрел), которая позволяет узнать порядок сортировки для каждой из колонок таблицы. Вызывается она просто и результат очевиден:



Одной из трудно оптимизируемых задач 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%.

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

В тему: