March 2009

You are currently browsing the monthly archive for March 2009.


Прочитал сегодня интересный рецепт, как быстро определить, кто даёт слабину, процессор или дисковая подсистема. Ну и заодно в очередной раз убедился в полезности книжки: Проектирование и оптимизация доступа к базам данных Microsoft SQL Server 2005. Учебный курс Microsoft (+ CD-ROM)

Авторы этого учебного курса считают, что соотношение суммарных ожиданий процессоров и ввода-вывода можно использовать для того, чтобы определить, кто из них из-за кого простаивает. Впрочем, вот выдержка из этой замечательной книги, со страницы 400:

Одной из самых сложных задач, с которыми сталкиваются разработчики баз данных, является прогнозирование поведения приложения в реальных условиях. Чтобы иметь точное представление о характеристиках производительности приложения, разработчик должен знать конкретную причину любого состояния ожидания, которое возникает во время выполнения приложения. На самом общем уровне состояния ожидания можно разбить на две категории: ожидание сигнала и ожидание ресурса. В SQL Server ожидание сигнала происходит, когда планировщик ждет процессор, чтобы запланировать задачу, а ожидание ресурса возникает, когда задача получила время процессора, но находится в ожидании ресурсов диска или памяти. При исследовании состояний ожидания в приложении нужно иметь в виду следующее. Если отношение времени ожидания сигнала ко времени ожидания ресурса велико (т.е. если состояний ожидания сигнала значительно больше, чем состояний ожидания ресурса), это может служить показателем неэффективного использования процессора. Если же велик коэффициент отношения времени ожидания ресурса ко времени ожидания сигнала, это может указывать на неэффективное использование ресурсов диска. Приступая к оценке производительности приложения, разработчики должны понимать, какой из типов ожидания преобладает. К счастью, SQLOS предоставляет “окно” в систему, достаточно прозрачное для того, чтобы разработчики могли использовать его для ответа на этот вопрос.

Представляю вам слегка модифицированный запрос из примера в этой книге, который использует динамическое административное представление sys.dm_os_wait_states, чтобы запросить у SQLOS информацию о суммарных временах ожидания ресурса и сигнала:

    USE master
    GO
    --DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
    --GO
    WITH ByWaitTypes([Тип ожидания], [ожидания сигнала %], [ожидания ресурса %], [ожидания ms]) AS
    (
    SELECT TOP 20 wait_type
       , cast(100.0 * sum(signal_wait_time_ms)/sum(wait_time_ms) AS NUMERIC (20,2))
       , cast(100.0 * sum(wait_time_ms - signal_wait_time_ms)/sum(wait_time_ms) AS NUMERIC(20,2))
       , sum(wait_time_ms)
    FROM sys.dm_os_wait_stats
    WHERE wait_time_ms <> 0
    GROUP BY wait_type
    ORDER BY sum(wait_time_ms) DESC
    )
    SELECT TOP 1 'Тип ожидания' = N'BCE!'
       , 'ожидания сигнала %' = (SELECT cast(100.0 * sum(signal_wait_time_ms)/
        sum (wait_time_ms) AS NUMERIC (20,2)) FROM sys.dm_os_wait_stats)
       , 'ожидания ресурса %' =(SELECT cast(100.0 * sum(wait_time_ms - signal_wait_time_ms)/
        sum(wait_time_ms) AS NUMERIC(20,2)) FROM sys.dm_os_wait_stats)
       , 'ожидания ms' =(SELECT sum(wait_time_ms) FROM sys.dm_os_wait_stats)
    FROM sys.dm_os_wait_stats
    UNION
    SELECT [Тип ожидания], [ожидания сигнала %], [ожидания ресурса %], [ожидания ms]
    FROM ByWaitTypes
    ORDER BY 'ожидания ms' DESC


Один из моих подопечных серверов вернул следующие результаты:


Тип ожидания ожидания сигнала % ожидания ресурса % ожидания ms
BCE! 10.18 89.82 109070003
LCK_M_U 0.19 99.81 63195933
BROKER_TASK_STOP 0.14 99.86 4831680
LAZYWRITER_SLEEP 0.02 99.98 4768764
REQUEST_FOR_DEADLOCK_SEARCH 100.00 0.00 4764971
XE_TIMER_EVENT 100.00 0.00 4741221
LOGMGR_QUEUE 4.04 95.96 4729823
FT_IFTS_SCHEDULER_IDLE_WAIT 0.00 100.00 4680082
CHECKPOINT_QUEUE 0.00 100.00 4519827
WRITELOG 21.61 78.39 2546141
SLEEP_TASK 0.26 99.74 2398521
BROKER_TO_FLUSH 0.03 99.97 2384867
XE_DISPATCHER_WAIT 0.00 100.00 1770518
PAGEIOLATCH_EX 0.75 99.25 1307584
CXPACKET 14.20 85.80 955856
PAGELATCH_EX 84.97 15.03 486757
IO_COMPLETION 2.24 97.76 270499
SLEEP_BPOOL_FLUSH 2.22 97.78 224846
PAGEIOLATCH_SH 1.04 98.96 151759
SOS_SCHEDULER_YIELD 99.89 0.11 133034
ASYNC_NETWORK_IO 5.06 94.94 79275


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


В книге утверждается что, как правило, системы, имеющие низкий показатель ожидания сигнал/ресурс демонстрируют лучшую общую производительность. Так ли это, я надеюсь, обсудить вместе с вами, поскольку мои наблюдения говорят о том, сто слишком низкие показатели тоже говорят о проблемах (например, ввода-вывода). А как обстоит дело на ваших серверах?


Остались в прошлом те времена, когда средствами SQL Server можно было получить состояния счётчиков производительности, которые относились только к самому SQL Server. Для этого традиционно использовались системные представления sys.sysperfinfo и sys.dm_os_performance_counters.


С помощью Power Shell и нового в SQL Server 2008 типа шага в заданиях по расписанию, который позволяет запускать под управлением SQL Server Agent сценарии  Power Shell, теперь можно получить абсолютно любые счётчики производительности. Причём, сделать это можно как для локального, так и удалённого в сети сервера. А получение сведений о счётчиках посредством WMI избавляет от необходимости агрегации сырых значений, что делает этот метод простым и понятным.


Ниже представлен макет сценария, который создаёт администраторскую базу данных и в ней таблицу для хранения данных о двух счётчиках производительности: PercentDiskTime и PercentProcessorTime. Данные в эту таблицу поставляет создаваемое в сценарии задание по расписанию, в единственном шаге которого запускается сценарий Power Shel. Это сценарий подключается к указанному серверу, посредством WMI получает значения заданных счётчиков и записывает эти значения в созданную ранее таблицу.


Чтобы протестировать этот метод, в примере сценария замените имена серверов и учётных записей на те, которые действительны в вашей тестовой среде.


 

USE [master]

GO

 

CREATE DATABASE [_DBA_]

GO

ALTER DATABASE [_DBA_] SET COMPATIBILITY_LEVEL = 100

GO

 

USE [_DBA_]

GO

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING OFF

GO

CREATE TABLE [dbo].[HostUtilization](

      [countertime] [datetime] NOT NULL,

      [PercentDiskTime] [tinyint] NOT NULL,

      [PercentProcessorTime] [tinyint] NOT NULL,

      [Host] [varchar](30) NOT NULL,

 CONSTRAINT [PK_HostUtilization] PRIMARY KEY CLUSTERED

(

      [Host] ASC,

      [countertime] ASC

))

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[HostUtilization]

ADD  CONSTRAINT [DF_HostUtilization_countertime] 

DEFAULT (getdate()) FOR [countertime]

GO

ALTER TABLE [dbo].[HostUtilization]

ADD  CONSTRAINT [DF_HostUtilization_PercentDiskTime] 

DEFAULT ((100)) FOR [PercentDiskTime]

GO

ALTER TABLE [dbo].[HostUtilization]

ADD  CONSTRAINT [DF_HostUtilization_PercentProcessorTime] 

DEFAULT ((100)) FOR [PercentProcessorTime]

GO

ALTER TABLE [dbo].[HostUtilization]

ADD  CONSTRAINT [DF_HostUtilization_Host] 

DEFAULT (‘.’) FOR [Host]

GO

 

 

USE [msdb]

GO

DECLARE @jobId BINARY(16)

EXEC msdb.dbo.sp_add_job

     @job_name=N’HostUtilization’,

     @enabled=1,

     @notify_level_eventlog=2,

     @notify_level_email=0,

     @notify_level_netsend=0,

     @notify_level_page=0,

     @delete_level=0,

     @description=N’Monitoring server name: MICROSOFT

     @category_name=N'[Uncategorized (Local)]’,

     @owner_login_name=N’RU\AGladchenko’,

     @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=3,

     @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 = “MICROSOFT”

$PercentDiskTime=(gwmi Win32_PerfFormattedData_PerfDisk_PhysicalDisk -computerName $RemoteHost -filter “name = ”_Total””).PercentDiskTime

$PercentProcessorTime=(gwmi Win32_PerfFormattedData_PerfOS_Processor -computerName $RemoteHost -filter “name = ”_Total””).PercentProcessorTime

$conn = new-object system.data.oledb.oledbconnection

$connstring = “provider=sqloledb;data source=MYSUPERPUPERSERVERNAME;initial catalog=_DBA_;integrated security=SSPI”

$conn.connectionstring = $connstring

$conn.open()

$RemoteHost = “””+$RemoteHost+”””

$sqlquery = “INSERT INTO [_DBA_].[dbo].[HostUtilization] ([countertime],[PercentDiskTime],[PercentProcessorTime],[Host] ) VALUES (DEFAULT, $PercentDiskTime, $PercentProcessorTime, $RemoteHost)”

$cmd = New-Object system.data.oledb.oledbcommand

$cmd.connection = $conn

$cmd.commandtext = $sqlquery

$cmd.executenonquery() > NULL

$conn.close()’,

     @database_name=N’master’,

     @flags=32

EXEC msdb.dbo.sp_update_job

     @job_id = @jobId,

     @start_step_id = 1

EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’1′,

     @enabled=1,

     @freq_type=4,

     @freq_interval=1,

     @freq_subday_type=2,

     @freq_subday_interval=30,

     @freq_relative_interval=0,

     @freq_recurrence_factor=0,

     @active_start_date=20090206,

     @active_end_date=99991231,

     @active_start_time=0,

     @active_end_time=235959,

     @schedule_uid=N’1b20bdf0-4627-4d31-8036-d85ff30cd664′

 

EXEC msdb.dbo.sp_add_jobserver

     @job_id = @jobId,

     @server_name = N'(local)’

GO