Авторы: Сунил Агарвал (Sunil Agarwal), Борис Барышников (Boris Baryshnikov), Кит Элмор (Keith Elmore), Юрген Томас (Juergen Thomas), Кун Чен (Kun Cheng), Бурзин Патель (Burzin Patel)
Технические рецензенты: Жером Халманс (Jerome Halmans), Фабрисио Возника (Fabricio Voznika), Джордж Рейня (George Reynya)
Troubleshooting Performance Problems in SQL Server 2008
Краткое содержание: Иногда неправильно спроектированная база данных или система, неправильно настроенная под рабочую нагрузку, может вызывать замедление работы SQL Server. Администраторам необходимо активно предотвращать или минимизировать проблемы, а когда они возникают — диагностировать причину и принимать корректирующие меры. В этом документе содержатся пошаговые рекомендации по диагностике и устранению распространённых проблем производительности с использованием общедоступных инструментов, таких как SQL Server Profiler, Performance Monitor, динамические административные представления (DMV), а также расширенные события (Extended Events) и сборщик данных.
Введение
Нередко можно столкнуться с периодическими замедлениями работы базы данных под управлением Microsoft SQL Server. Причины могут варьироваться от неправильно спроектированной базы данных до системы, неправильно настроенной под рабочую нагрузку. Как администратор, вы хотите активно предотвращать или минимизировать проблемы; если они возникают, вы хотите диагностировать причину и принять корректирующие меры для исправления проблемы, когда это возможно. В этом техническом документе содержатся пошаговые рекомендации по диагностике и устранению распространённых проблем производительности с использованием общедоступных инструментов, таких как SQL Server Profiler, System Monitor (в Windows Server 2003) или Performance Monitor (в Windows Vista и Windows Server 2008), также известный как Perfmon, динамические административные представления (DMV), а также расширенные события (Extended Events) и сборщик данных, которые появились в SQL Server 2008. Мы ограничили область действия этого документа проблемами, обычно встречающимися в службе поддержки Microsoft Customer Service and Support, поскольку исчерпывающий анализ всех возможных проблем нецелесообразен.
Цели
Основная цель этого документа — предоставить общую методологию диагностики и устранения проблем производительности SQL Server в распространённых сценариях клиентов с использованием общедоступных инструментов.
Методология
Существует множество причин замедления работы SQL Server. Для начала диагностики проблем мы используем три ключевых симптома:
- Узкие места ресурсов: В этом документе рассматриваются узкие места ЦП, памяти и ввода-вывода. Сетевые проблемы не рассматриваются. Для каждого узкого места ресурсов мы описываем, как выявить проблему, а затем перебираем возможные причины. Например, узкое место памяти может привести к чрезмерному подкачиванию (paging), что в конечном итоге влияет на производительность.
- Узкие места tempdb: Поскольку для каждого экземпляра SQL Server существует только одна
tempdb, она может стать узким местом производительности и дискового пространства. Приложение может перегрузитьtempdbчрезмерными операциями DDL или DML и использованием слишком большого объёма пространства. Это может привести к замедлению или сбою несвязанных приложений, работающих на сервере. - Медленно выполняющийся пользовательский запрос: Производительность существующего запроса может ухудшиться, или новый запрос может выполняться дольше ожидаемого. Причин может быть много. Например:
- Изменения в статистической информации могут привести к неоптимальному плану запроса для существующего запроса.
- Отсутствие индексов может вынуждать выполнять сканирование таблиц и замедлять запрос.
- Приложение может замедляться из-за блокировок, даже если использование ресурсов нормальное.
- Чрезмерные блокировки могут быть вызваны плохой архитектурой приложения или схемы, либо выбором неподходящего уровня изоляции для транзакции.
Причины этих симптомов не обязательно независимы друг от друга. Неправильный выбор плана запроса может нагружать системные ресурсы и вызывать общее замедление рабочей нагрузки. Например, если в большой таблице отсутствует полезный индекс, или оптимизатор запросов решает не использовать его, запрос может замедлиться; эти условия также создают высокую нагрузку на подсистему ввода-вывода для чтения лишних страниц данных и на память (буферный пул) для хранения этих страниц в кэше. Аналогично, чрезмерная перекомпиляция часто выполняемого запроса может создавать нагрузку на ЦП.
Узкие места ресурсов
Прежде чем вы сможете определить, есть ли у вас узкое место ресурсов, вам необходимо знать, как ресурсы используются в нормальных условиях. Вы можете использовать методы, описанные в этом документе, для сбора базовой информации об использовании ресурсов (в то время, когда у вас нет проблем с производительностью).
Вы можете обнаружить, что проблема заключается в ресурсе, работающем на пределе своих возможностей, и SQL Server не может поддерживать рабочую нагрузку в текущей конфигурации. Чтобы решить эту проблему, вам может потребоваться добавить больше вычислительной мощности или памяти, либо увеличить пропускную способность вашего ввода-вывода или сетевого канала. Однако прежде чем предпринять этот шаг, полезно понять некоторые распространённые причины узких мест ресурсов. Некоторые решения, такие как перенастройка, не требуют добавления дополнительных ресурсов.
Инструменты для устранения узких мест ресурсов
Для устранения конкретного узкого места ресурсов можно использовать один или несколько из следующих инструментов:
- Динамические административные представления (DMV): См. электронную документацию SQL Server.
- Расширенные события (Extended Events): См. раздел «Расширенные события» далее в этом документе и электронную документацию SQL Server.
- Performance Monitor: Этот инструмент входит в состав Windows. Для получения дополнительной информации см. документацию Windows.
- SQL Server Profiler: См. SQL Server Profiler в группе Performance Tools в группе программ SQL Server.
- Сборщик данных и хранилище управляющих данных (MDW): См. разделы «Сборщик данных и MDW» далее в этом документе и электронную документацию.
- Команды DBCC: См. электронную документацию SQL Server и приложение А.
Узкие места ЦП
Узкое место ЦП может быть вызвано недостаточными аппаратными ресурсами для данной нагрузки. Однако чрезмерное использование ЦП часто можно снизить путём настройки запросов (особенно если произошло внезапное увеличение без дополнительной нагрузки или появления новых запросов на сервере), устранения факторов проектирования приложения и оптимизации конфигурации системы. Прежде чем спешить покупать более быстрые и/или дополнительные процессоры, определите крупнейших потребителей пропускной способности ЦП и посмотрите, можно ли настроить эти запросы или скорректировать факторы проектирования/конфигурации.
Performance Monitor обычно является одним из самых простых способов определить, является ли сервер ограниченным по ЦП. Посмотрите, высок ли счётчик Processor:% Processor Time; устойчивые значения, превышающие 80% процессорного времени на ЦП, обычно считаются узким местом.
Внутри SQL Server вы также можете проверить наличие узких мест ЦП, запросив DMV. Запросы, ожидающие с типом ожидания SOS_SCHEDULER_YIELD, или большое количество задач, готовых к выполнению (runnable tasks), могут указывать на то, что выполняемые потоки часто уступают процессор и что на процессоре может быть узкое место ЦП из-за затяжных логических чтений. Если вы включили сборщик данных, диаграмма SQL Server Waits в отчёте о активности сервера (Server Activity) является очень простым способом мониторинга узких мест ЦП с течением времени. Потребление ЦП и ожидания SOS_SCHEDULER_YIELD агрегируются в категорию CPU Wait в этом отчёте, а если вы видите высокое использование ЦП, вы можете детализировать (drill through) запросы, потребляющие больше всего ресурсов.
Следующий запрос даёт вам общее представление о том, какие кэшированные пакеты или процедуры используют больше всего ЦП. Запрос агрегирует ЦП, потреблённый всеми операторами с одинаковым plan_handle (то есть они являются частью одного пакета или процедуры). Если данный plan_handle имеет более одного оператора, вам, возможно, потребуется углубиться дальше, чтобы найти конкретный запрос, вносящий наибольший вклад в общее использование ЦП.
select top 50
sum(qs.total_worker_time) as total_cpu_time,
sum(qs.execution_count) as total_execution_count,
count(*) as number_of_statements,
qs.plan_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc
Чрезмерная компиляция и оптимизация запросов
Компиляция и оптимизация запросов — это процесс, интенсивно использующий ЦП. Стоимость оптимизации возрастает с увеличением сложности запроса и базовой схемы, но даже относительно простой запрос может потребовать 10–20 миллисекунд времени ЦП для синтаксического анализа и компиляции.
Чтобы снизить эти затраты, SQL Server кэширует и повторно использует скомпилированные планы запросов. Каждый раз, когда от клиента поступает новый запрос, SQL Server сначала выполняет поиск в кэше планов (иногда называемом кэшем процедур), чтобы проверить, существует ли уже скомпилированный план, который можно использовать повторно. Если соответствующий план запроса не найден, SQL Server анализирует и компилирует входящий запрос перед его выполнением.
Для рабочей нагрузки типа OLTP набор запросов, которые отправляются, относительно невелик и статичен. Часто оптимальный план запроса не зависит от точного значения или значений, используемых в качестве предикатов в запросе, поскольку поиск основан на ключах. Повторное использование планов запросов в этом типе рабочей нагрузки очень важно, поскольку стоимость компиляции может быть такой же высокой или выше, чем стоимость выполнения самого запроса. Однако рабочая нагрузка хранилища данных может значительно выиграть от использования динамического SQL и позволить оптимизатору запросов искать оптимальный план для каждого набора значений, поскольку время выполнения этих запросов обычно намного больше времени компиляции, и оптимальный план запроса с большей вероятностью изменится в зависимости от предикатов в запросе. Использование параметризованных запросов или хранимых процедур для приложений на основе OLTP значительно увеличивает шансы повторного использования кэшированного плана и может привести к существенному снижению потребления ЦП SQL Server. Вы можете включить параметризацию на уровне базы данных или запроса, используя соответственно параметр базы данных PARAMETERIZATION FORCED или подсказку запроса. Для получения информации о важных ограничениях, особенно если вы полагаетесь на индексы по вычисляемым столбцам или индексированные представления, см. электронную документацию SQL Server.
Однако лучшее место для параметризации запросов — это само приложение (на этапе проектирования), что также помогает снизить риск SQL-инъекций, избегая конкатенации строк с использованием значений параметров. Для получения дополнительной информации см. следующие разделы электронной документации SQL Server :
Обнаружение
Во время компиляции SQL Server вычисляет «сигнатуру» запроса и предоставляет её как столбец query_hash в sys.dm_exec_requests и sys.dm_exec_query_stats, а также как атрибут QueryHash в Showplan/Statistics XML. Сущности с одинаковым значением query_hash с высокой вероятностью ссылаются на один и тот же текст запроса, если бы он был написан в параметризованной форме. Запросы, различающиеся только литеральными значениями, должны иметь одинаковое значение. Например, первые два запроса имеют одинаковый хэш запроса, в то время как третий запрос имеет другой хэш запроса, поскольку он выполняет другую операцию.
select * from sys.objects where object_id = 100
select * from sys.objects where object_id = 101
select * from sys.objects where name = 'sysobjects'
Хэш запроса вычисляется на основе древовидной структуры, созданной во время компиляции. Пробелы игнорируются, как и различия в использовании явных списков столбцов по сравнению с использованием звёздочки (*) в списке SELECT. Кроме того, не имеет значения, использует ли один запрос полное имя, а другой — только имя таблицы, если они оба ссылаются на один и тот же объект. Все следующие запросы должны давать одинаковое значение query_hash.
Use AdventureWorks
Go
set showplan_xml on
go
-- Предполагается, что это выполняется пользователем, чья схема по умолчанию — Sales
select * from SalesOrderHeader h
select * from Sales.SalesOrderHeader h
select SalesOrderID,
RevisionNumber,
OrderDate,
DueDate,
ShipDate,
Status,
OnlineOrderFlag,
SalesOrderNumber,
PurchaseOrderNumber,
AccountNumber,
CustomerID,
ContactID,
SalesPersonID,
TerritoryID,
BillToAddressID,
ShipToAddressID,
ShipMethodID,
CreditCardID,
CreditCardApprovalCode,
CurrencyRateID,
SubTotal,
TaxAmt,
Freight,
TotalDue,
Comment,
rowguid,
ModifiedDate
from Sales.SalesOrderHeader h
go
set showplan_xml off
go
Обратите внимание, что часть полного имени, указывающая на базу данных, игнорируется при генерации значения query_hash. Это позволяет агрегировать использование ресурсов по всем запросам в системах, которые реплицируют одну и ту же схему и запросы для многих баз данных в одном экземпляре.
Простой способ обнаружить приложения, отправляющие множество динамических запросов, — это группировка по столбцу sys.dm_exec_query_stats.query_hash следующим образом.
select q.query_hash,
q.number_of_entries,
t.text as sample_query,
p.query_plan as sample_plan
from (select top 20 query_hash,
count(*) as number_of_entries,
min(sql_handle) as sample_sql_handle,
min(plan_handle) as sample_plan_handle
from sys.dm_exec_query_stats
group by query_hash
having count(*) > 1
order by count(*) desc) as q
cross apply sys.dm_exec_sql_text(q.sample_sql_handle) as t
cross apply sys.dm_exec_query_plan(q.sample_plan_handle) as p
go
Запросы, у которых значение number_of_entries достигает сотен или тысяч, являются отличными кандидатами для параметризации. Если вы посмотрите на атрибуты CompileTime и CompileCPU в теге <QueryPlan> образца XML-плана запроса и умножите эти значения на number_of_entries для этого запроса, вы можете получить оценку того, сколько времени компиляции и ЦП вы можете устранить, параметризуя запрос (что означает, что запрос компилируется один раз, а затем кэшируется и повторно используется для последующих выполнений). Устранение этих ненужных кэшированных планов имеет и другие нематериальные преимущества, такие как освобождение памяти для кэширования других скомпилированных планов (тем самым дополнительно снижая нагрузку на компиляцию) и оставление большего объёма памяти для буферного кэша.
Решение
SQL Server также генерирует значение query_plan_hash, которое представляет собой «сигнатуру» пути доступа плана запроса (то есть какой алгоритм соединения используется, порядок соединения, выбор индекса и так далее). Некоторые приложения могут полагаться на получение другого плана запроса на основе того, как оптимизатор оценивает конкретные значения параметров, переданных для этого выполнения запроса. Если это так, параметризовать запросы не нужно.
Вы можете использовать значения query_hash и query_plan_hash вместе, чтобы определить, привели ли динамические запросы с одинаковым query_hash к планам запросов с одинаковыми или разными значениями query_plan_hash, или путями доступа. Это делается с помощью небольшой модификации предыдущего запроса.
select q.query_hash,
q.number_of_entries,
q.distinct_plans,
t.text as sample_query,
p.query_plan as sample_plan
from (select top 20 query_hash,
count(*) as number_of_entries,
count(distinct query_plan_hash) as distinct_plans,
min(sql_handle) as sample_sql_handle,
min(plan_handle) as sample_plan_handle
from sys.dm_exec_query_stats
group by query_hash
having count(*) > 1
order by count(*) desc) as q
cross apply sys.dm_exec_sql_text(q.sample_sql_handle) as t
cross apply sys.dm_exec_query_plan(q.sample_plan_handle) as p
go
Обратите внимание, что этот новый запрос возвращает количество различных планов запросов (значений query_plan_hash) для данного значения query_hash. Строки, возвращающие большое число для number_of_entries и количество distinct_plans равное 1, являются хорошими кандидатами для параметризации. Даже если количество различных планов больше единицы, вы можете использовать sys.dm_exec_query_plan для извлечения различных планов запросов и их изучения, чтобы увидеть, является ли различие важным и необходимым для достижения оптимальной производительности.
После того как вы определите, какие запросы следует параметризовать, лучшее место для их параметризации — клиентское приложение. Детали того, как это сделать, немного различаются в зависимости от того, какой клиентский API вы используете, но общее для всех API — вместо построения строки запроса с литеральными предикатами вы создаёте строку со знаком вопроса (?) в качестве маркера параметра.
-- Отправка как динамического запроса
select * from Sales.SalesOrderHeader where SalesOrderID = 100
-- Отправка как параметризованного
select * from Sales.SalesOrderHeader where SalesOrderID = ?
Вы должны использовать соответствующие API для вашей технологии (ODBC, OLE DB или SQLClient), чтобы привязать значение к маркеру параметра. Затем клиентский драйвер или поставщик отправляет запрос в его параметризованной форме с использованием sp_executesql.
exec sp_executesql N'select * from Sales.SalesOrderHeader where SalesOrderID = @P1', N'@P1 int', 100
Поскольку запрос параметризован, он соответствует существующему кэшированному плану и повторно использует его.
Если вся рабочая нагрузка для данной базы данных подходит для параметризации и вы не контролируете (или не можете изменить) клиентское приложение, вы также можете включить опцию принудительной параметризации для базы данных. Учтите упомянутые ранее предостережения; это может помешать оптимизатору сопоставлять индексированные представления и индексы по вычисляемым столбцам.
ALTER DATABASE AdventureWorks SET PARAMETERIZATION FORCED
Если вы не можете параметризовать клиентское приложение или включить принудительную параметризацию для всей базы данных, вы всё равно можете создать шаблон руководства плана для конкретных запросов с подсказкой OPTION (PARAMETERIZATION FORCED). Для получения дополнительной информации о необходимых шагах см. раздел Forced Parameterization в электронной документации SQL Server.
Ненужная перекомпиляция
Когда пакет или вызов удалённой процедуры (RPC) отправляется в SQL Server, сервер проверяет правильность и корректность плана запроса перед началом выполнения. Если одна из этих проверок не пройдена, пакет может быть скомпилирован заново для создания другого плана запроса. Такие компиляции известны как перекомпиляции. Эти перекомпиляции обычно необходимы для обеспечения корректности и часто выполняются, когда сервер определяет, что может существовать более оптимальный план запроса из-за изменений в базовых данных. Компиляции по своей природе интенсивно используют ЦП, и поэтому чрезмерные перекомпиляции могут привести к проблеме производительности, связанной с нехваткой ЦП в системе.
В SQL Server 2000, когда SQL Server перекомпилирует хранимую процедуру, перекомпилируется вся хранимая процедура, а не только оператор, вызвавший перекомпиляцию. В SQL Server и SQL Server 2005 поведение изменено на перекомпиляцию на уровне операторов хранимых процедур. Когда SQL Server перекомпилирует хранимые процедуры, компилируется только оператор, вызвавший перекомпиляцию, а не вся процедура. Это использует меньше вычислительной мощности ЦП и приводит к меньшей конкуренции за ресурсы блокировок, такие как блокировки COMPILE. Перекомпиляция может происходить в ответ на различные условия, такие как:
- Изменения схемы
- Изменения статистики
- Отложенная компиляция
- Изменения параметров
SET - Изменения временных таблиц
- Создание хранимой процедуры с подсказкой
RECOMPILEили подсказкойOPTION (RECOMPILE)
Обнаружение
Вы можете использовать Performance Monitor и SQL Server Profiler для обнаружения чрезмерной компиляции и перекомпиляции.
Performance Monitor
Объект SQL Statistics предоставляет счётчики для мониторинга компиляции и типов запросов, отправляемых экземпляру SQL Server. Вы должны отслеживать количество компиляций и перекомпиляций запросов в сочетании с количеством полученных пакетов, чтобы выяснить, способствуют ли компиляции высокому использованию ЦП. В идеале отношение SQL Recompilations/sec к Batch Requests/sec должно быть очень низким, если только пользователи не отправляют динамические запросы.
Ключевые счётчики данных:
- SQL Server: SQL Statistics: Batch Requests/sec
- SQL Server: SQL Statistics: SQL Compilations/sec
- SQL Server: SQL Statistics: SQL Recompilations/sec
Трассировка SQL Server Profiler
Если счётчики Performance Monitor указывают на большое количество перекомпиляций, перекомпиляции могут способствовать высокому потреблению ЦП SQL Server. Посмотрите на трассировку Profiler, чтобы найти хранимые процедуры, которые перекомпилируются. Трассировка SQL Server Profiler предоставляет эту информацию вместе с причиной перекомпиляции. Вы можете использовать следующие события для получения этой информации.
SP:Recompile / SQL:StmtRecompile
Классы событий SP:Recompile и SQL:StmtRecompile указывают, какие хранимые процедуры и операторы были перекомпилированы. При компиляции хранимой процедуры генерируется одно событие для хранимой процедуры и по одному для каждого компилируемого оператора. Однако при перекомпиляции хранимой процедуры перекомпилируется только оператор, вызвавший перекомпиляцию. Некоторые из наиболее важных столбцов данных для класса событий SP:Recompile перечислены ниже. Столбец данных EventSubClass особенно важен для определения причины перекомпиляции. SP:Recompile вызывается один раз для процедуры или триггера, который перекомпилируется, и не вызывается для динамического пакета, который, вероятно, может быть перекомпилирован. В SQL Server более полезно отслеживать SQL:StmtRecompile, поскольку этот класс событий вызывается при перекомпиляции любого типа пакета, динамического запроса, хранимой процедуры или триггера.
Ключевые столбцы данных для просмотра в этих событиях:
EventClassEventSubClassObjectID(представляет хранимую процедуру, содержащую этот оператор)SPIDStartTimeSqlHandleTextData
Если у вас есть сохранённый файл трассировки, вы можете использовать следующий запрос, чтобы увидеть все события перекомпиляции, захваченные в трассировке.
select
spid,
StartTime,
Textdata,
EventSubclass,
ObjectID,
DatabaseID,
SQLHandle
from
fn_trace_gettable ( 'e:\recompiletrace.trc' , 1)
where
EventClass in(37,75,166)
-- EventClass 37 = Sp:Recompile, 75 = CursorRecompile, 166 = SQL:StmtRecompile
Вы можете дополнительно сгруппировать результаты этого запроса по столбцам SqlHandle и ObjectID или по различным другим столбцам, чтобы увидеть, связаны ли большинство перекомпиляций с одной хранимой процедурой или обусловлены какой-либо другой причиной (например, изменением параметра SET).
Showplan XML For Query Compile
Класс событий Showplan XML For Query Compile возникает, когда SQL Server компилирует или перекомпилирует оператор Transact-SQL. Это событие содержит информацию об операторе, который компилируется или перекомпилируется. Эта информация включает план запроса и идентификатор объекта рассматриваемой процедуры. Захват этого события создаёт значительную нагрузку на производительность, поскольку он захватывается для каждой компиляции или перекомпиляции. Если вы видите высокое значение счётчика SQL Compilations/sec в Performance Monitor, вам следует отслеживать это событие. С помощью этой информации вы можете увидеть, какие операторы часто перекомпилируются. Вы можете использовать эту информацию, чтобы изменить параметры этих операторов. Это должно уменьшить количество перекомпиляций.
DMV
Используя DMV sys.dm_exec_query_optimizer_info, вы можете получить хорошее представление о времени, которое SQL Server тратит на оптимизацию. Если сделать два снимка этого DMV, вы сможете получить хорошее представление о времени, затраченном на оптимизацию в заданный период времени.
select * from sys.dm_exec_query_optimizer_info
В частности, обратите внимание на elapsed time, который представляет собой время, затраченное на оптимизацию. Поскольку время, затраченное на оптимизацию, обычно близко к времени ЦП, используемому для оптимизации (поскольку процесс оптимизации очень сильно зависит от ЦП), вы можете получить хорошую оценку того, в какой степени время компиляции и перекомпиляции способствует высокому использованию ЦП.
Ещё одно DMV, полезное для захвата этой информации, — sys.dm_exec_query_stats.
Столбцы данных для просмотра:
Sql_handleTotal worker timePlan generation numberStatement Start Offset
В частности, plan_generation_num указывает, сколько раз запрос перекомпилировался. Следующий пример запроса даёт 25 лучших хранимых процедур, которые были перекомпилированы.
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from
sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where
plan_generation_num >1
order by plan_generation_num desc
Решение
Если вы обнаружили чрезмерную компиляцию и перекомпиляцию, рассмотрите следующие варианты:
- Если перекомпиляция произошла из-за изменения параметра
SET, используйте SQL Server Profiler, чтобы определить, какой параметрSETизменился. Избегайте изменения параметровSETвнутри хранимых процедур. Лучше устанавливать их на уровне подключения. Убедитесь, что параметрыSETне изменяются в течение времени жизни подключения. - Пороги перекомпиляции для временных таблиц ниже, чем для обычных таблиц. Если перекомпиляции во временной таблице связаны с изменениями статистики, вы можете заменить временные таблицы табличными переменными. Изменение кардинальности табличной переменной не вызывает перекомпиляции. Недостатком этого подхода является то, что оптимизатор запросов не отслеживает кардинальность табличной переменной, поскольку статистика не создаётся и не поддерживается для табличных переменных. Это может привести к менее оптимальным планам запросов. Вы можете протестировать различные варианты и выбрать лучший.
- Другой вариант — использовать подсказку
KEEP PLAN. Это устанавливает порог временных таблиц таким же, как и для постоянных таблиц. В столбцеEventSubClassотображается «Statistics Changed» для операции с временной таблицей. - Чтобы избежать перекомпиляций, вызванных изменениями в статистике (например, если план становится неоптимальным из-за изменения статистики данных), укажите подсказку
KEEPFIXED PLAN. При включении этой опции перекомпиляции могут происходить только для обеспечения корректности (например, когда изменилась структура базовой таблицы и план больше не применим), а не в ответ на изменения статистики. Например, перекомпиляция может произойти, если изменится схема таблицы, на которую ссылается оператор, или если таблица помечена с помощью хранимой процедурыsp_recompile. - Отключение автоматического обновления статистики для индексов и статистики, определённых для таблицы или индексированного представления, предотвращает перекомпиляции, вызванные изменениями статистики для этого объекта. Однако обратите внимание, что отключение функции автоматического обновления статистики с помощью этого метода обычно не является хорошей идеей. Это связано с тем, что оптимизатор запросов больше не чувствителен к изменениям данных в этих объектах, и это может привести к неоптимальным планам запросов. Используйте этот метод только в крайнем случае, после того как все другие альтернативы будут исчерпаны.
- Пакеты должны иметь полные имена объектов (например,
dbo.Table1), чтобы избежать перекомпиляции и неоднозначности между объектами. - Чтобы избежать перекомпиляций, вызванных отложенной компиляцией, не чередуйте DML и DDL и не создавайте DDL из условных конструкций, таких как операторы
IF. - Запустите помощник по настройке ядра базы данных (Database Engine Tuning Advisor, DTA), чтобы увидеть, могут ли какие-либо изменения индексов улучшить время компиляции и время выполнения запроса.
- Проверьте, была ли хранимая процедура создана с опцией
WITH RECOMPILEили использовалась подсказкаRECOMPILE. Если процедура была создана с опциейWITH RECOMPILE, в SQL Server вы можете воспользоваться подсказкойRECOMPILEна уровне оператора, если конкретный оператор внутри этой процедуры нуждается в перекомпиляции. Использование этой подсказки на уровне оператора позволяет избежать необходимости перекомпиляции всей процедуры при каждом её выполнении, позволяя при этом компилировать отдельный оператор.
Неэффективный план запроса
При генерации плана выполнения для запроса оптимизатор SQL Server пытается выбрать план, который обеспечивает максимальную скорость отклика для этого запроса. Обратите внимание, что максимальная скорость отклика не обязательно означает минимизацию используемого ввода-вывода и не обязательно означает использование минимального объёма ЦП — это баланс различных ресурсов.
Некоторые типы операторов более интенсивно используют ЦП, чем другие. По своей природе операторы Hash и Sort сканируют свои соответствующие входные данные. Если во время такого сканирования используется упреждающее чтение (read-ahead), страницы почти всегда доступны в буферном кэше до того, как они понадобятся оператору. Таким образом, ожидания физического ввода-вывода минимизируются или устраняются. Если эти типы операций больше не ограничены физическим вводом-выводом, они склонны проявляться в виде высокого потребления ЦП. Напротив, соединения вложенными циклами имеют множество поисков по индексу и могут быстро стать ограниченными по вводу-выводу, если поиски по индексу приводят к переходу к множеству различных частей таблицы, так что страницы не могут поместиться в буферный кэш.
Наиболее значимым входным параметром, который оптимизатор использует для оценки стоимости различных альтернативных планов запросов, являются оценки кардинальности для каждого оператора, которые вы можете увидеть в Showplan (атрибуты EstimateRows и EstimateExecutions). Без точных оценок кардинальности основной входной параметр, используемый для оптимизации, искажён, и во многих случаях таковым является и итоговый план.
Обнаружение
Неэффективные планы запросов обычно обнаруживаются сравнительно. Неэффективный план запроса может вызывать повышенное потребление ЦП.
Следующий запрос к sys.dm_exec_query_stats является эффективным способом определения того, какой запрос использует наибольшее суммарное ЦП.
select
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 50
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc
В качестве альтернативы вы можете выполнить запрос к sys.dm_exec_cached_plans, используя фильтры для различных операторов, которые могут быть интенсивными по ЦП, таких как '%Hash Match%', '%Sort%', чтобы найти подозрительные.
Решение
Рассмотрите следующие варианты, если вы обнаружили неэффективные планы запросов:
- Настройте запрос с помощью помощника по настройке ядра базы данных, чтобы увидеть, есть ли какие-либо рекомендации по индексам.
- Проверьте наличие проблем с плохими оценками кардинальности.
- Написаны ли запросы так, чтобы они использовали наиболее ограничивающее условие
WHERE? Неограниченные запросы по своей природе требуют много ресурсов. - Выполните
UPDATE STATISTICSдля таблиц, участвующих в запросе, и проверьте, сохраняется ли проблема. - Использует ли запрос конструкции, для которых оптимизатор не может точно оценить кардинальность? Рассмотрите возможность модификации запроса таким образом, чтобы проблема была устранена.
- Если невозможно изменить схему или запрос, вы можете использовать функцию руководств планов (plan guides) для указания подсказок запроса для запросов, соответствующих определённым текстовым критериям. Руководства планов могут быть созданы как для динамических запросов, так и для запросов внутри хранимой процедуры. Подсказки, такие как
OPTION (OPTIMIZE FOR), позволяют влиять на оценки кардинальности, оставляя оптимизатору полный набор возможных планов. Другие подсказки, такие какOPTION (FORCE ORDER)илиOPTION (USE PLAN), предоставляют различные степени контроля над планом запроса. SQL Server предлагает полную поддержку DML для руководств планов, что означает, что они могут быть созданы для операторовSELECT,INSERT,UPDATE,DELETEилиMERGE. - SQL Server также предлагает функцию, называемую замораживанием плана (plan freezing), которая позволяет заморозить план точно таким, каким он существует в кэше планов. Эта опция аналогична созданию руководства плана с указанной подсказкой
USE PLAN. Однако она устраняет необходимость выполнения длинных команд, требуемых при создании руководств планов. Она также минимизирует ошибки пользователей, которые могут возникнуть при выполнении этих длинных команд. Например, простой двухоператорный пакет, представленный ниже, — это всё, что нужно для замораживания плана для запроса, соответствующего указанным текстовым критериям.
DECLARE @plan_handle varbinary(64);
-- Извлечение plan_handle запроса.
SELECT @plan_handle = plan_handle FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE text LIKE N'Some query matching criteria%';
EXECUTE sp_create_plan_guide_from_handle
@name = N'Sample_PG1',
@plan_handle = @plan_handle,
@statement_start_offset = NULL;
GO
Внутризапросный параллелизм (Intraquery Parallelism)
При генерации плана выполнения для запроса оптимизатор SQL Server пытается выбрать план, обеспечивающий максимальную скорость отклика. Если стоимость запроса превышает значение, указанное в параметре cost threshold for parallelism, и параллелизм не отключён, оптимизатор пытается сгенерировать план, который может выполняться параллельно. Параллельный план запроса использует несколько потоков для обработки запроса, каждый поток распределяется по доступным ЦП и одновременно использует время ЦП каждого процессора. Максимальная степень параллелизма может быть ограничена для всего сервера с помощью параметра max degree of parallelism, на уровне группы рабочей нагрузки Resource Governor или на уровне каждого запроса с помощью подсказки OPTION (MAXDOP).
Решение о фактической степени параллелизма (DOP), используемой для выполнения (мера того, сколько потоков будут выполнять данную операцию параллельно), откладывается до времени выполнения. Перед выполнением запроса SQL Server определяет, сколько планировщиков недогружены, и выбирает DOP для запроса, который полностью использует оставшиеся планировщики. После выбора DOP запрос выполняется с выбранной степенью параллелизма до завершения. Параллельный запрос обычно использует аналогичное, но немного более высокое время ЦП по сравнению с соответствующим планом последовательного выполнения, но делает это за более короткое время. Пока нет других узких мест, таких как ожидания физического ввода-вывода, параллельные планы обычно должны использовать 100% ЦП на всех процессорах.
Один ключевой фактор (насколько простаивает система), который привёл к выполнению параллельного плана, может измениться после начала выполнения запроса. Например, если запрос поступает в период простоя, сервер может выбрать выполнение с параллельным планом и использовать DOP, равный четырём, и создать потоки на четырёх разных процессорах. После того как эти потоки начнут выполняться, существующие подключения могут отправлять другие запросы, которые также требуют много ЦП. В этот момент все различные потоки будут совместно использовать короткие кванты времени доступного ЦП, что приведёт к увеличению продолжительности запроса.
Выполнение с параллельным планом не является inherently плохим и должно обеспечивать максимальную скорость отклика для этого запроса. Однако время отклика для данного запроса должно быть взвешено с учётом общей пропускной способности и отзывчивости остальных запросов в системе. Параллельные запросы обычно лучше всего подходят для пакетной обработки и рабочих нагрузок поддержки принятия решений и могут быть не полезны в среде обработки транзакций.
Обнаружение
Проблемы внутризапросного параллелизма можно обнаружить с помощью следующих методов.
Performance Monitor
См. счётчик SQL Server: SQL Statistics – Batch Requests/sec. Поскольку запрос должен иметь оценочную стоимость, превышающую порог стоимости для настройки параллелизма (по умолчанию 5), прежде чем он будет рассмотрен для параллельного плана, чем больше пакетов в секунду обрабатывает сервер, тем менее вероятно, что пакеты выполняются с параллельными планами. Серверы, выполняющие много параллельных запросов, обычно имеют небольшое количество пакетов в секунду (например, значения менее 100).
DMV
На работающем сервере вы можете определить, выполняются ли какие-либо активные запросы параллельно для данного сеанса, с помощью следующего запроса.
select
r.session_id,
r.request_id,
max(isnull(exec_context_id, 0)) as number_of_workers,
r.sql_handle,
r.statement_start_offset,
r.statement_end_offset,
r.plan_handle
from
sys.dm_exec_requests r
join sys.dm_os_tasks t on r.session_id = t.session_id
join sys.dm_exec_sessions s on r.session_id = s.session_id
where
s.is_user_process = 0x1
group by
r.session_id, r.request_id,
r.sql_handle, r.plan_handle,
r.statement_start_offset, r.statement_end_offset
having max(isnull(exec_context_id, 0)) > 0
С помощью этой информации вы можете легко получить текст запроса с помощью sys.dm_exec_sql_text и план с помощью sys.dm_exec_cached_plan.
Вы также можете искать планы, которые могут выполняться параллельно. Для этого выполните поиск по кэшированным планам, чтобы увидеть, имеет ли реляционный оператор атрибут Parallel ненулевое значение. Эти планы могут не выполняться параллельно, но могут это сделать, если система не слишком загружена.
-- Поиск планов запросов, которые могут выполняться параллельно
select
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0
В общем, продолжительность запроса превышает время ЦП, потому что часть времени была потрачена на ожидание ресурсов, таких как блокировка или физический ввод-вывод. Единственный сценарий, в котором запрос может использовать больше времени ЦП, чем прошедшая продолжительность, — это когда запрос выполняется с параллельным планом, так что несколько потоков одновременно используют ЦП. Обратите внимание, что не все параллельные запросы демонстрируют это поведение (где время ЦП больше продолжительности).
select
qs.sql_handle,
qs.statement_start_offset,
qs.statement_end_offset,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.text
from
sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) as q
where
qs.total_worker_time > qs.total_elapsed_time
Решение
- Любой запрос, который выполняется с параллельным планом, является тем, который оптимизатор определяет как достаточно дорогой, чтобы превысить порог стоимости параллелизма, который по умолчанию равен 5. Любые запросы, определённые с помощью предыдущих методов, являются кандидатами для дальнейшей настройки.
- Используйте помощник по настройке ядра базы данных, чтобы увидеть, могут ли какие-либо изменения индексов, изменения индексированных представлений или изменения секционирования снизить стоимость запроса.
- Проверьте наличие значительных различий между фактической и оценённой кардинальностью, поскольку оценки кардинальности являются основным фактором при оценке стоимости запроса. Если обнаружены какие-либо значительные различия:
- Если параметр базы данных auto create statistics отключён, убедитесь, что в столбце
Warningsвывода Showplan нет записейMISSING STATS. - Попробуйте выполнить
UPDATE STATISTICSдля таблиц, где оценки кардинальности отличаются. - Убедитесь, что запрос не использует конструкцию, которую оптимизатор не может точно оценить, такую как многооператорные табличные функции или CLR-функции, табличные переменные или сравнения с переменной Transact-SQL (сравнения с параметром допустимы).
- Оцените, может ли запрос быть написан более эффективно с использованием других операторов или выражений Transact-SQL.
- Если параметр базы данных auto create statistics отключён, убедитесь, что в столбце
Плохое использование курсоров
В версиях SQL Server до SQL Server 2005 поддерживалось только одно активное общее подключение на соединение. Запрос, который выполнялся или имел ожидающие отправки клиенту результаты, считался активным. В некоторых ситуациях клиентскому приложению может потребоваться прочитать результаты и отправить другие запросы в SQL Server на основе строки, только что прочитанной из результирующего набора. Это невозможно было сделать с обычным результирующим набором, потому что он мог иметь другие ожидающие результаты. Распространённым решением было изменение свойств подключения для использования серверного курсора.
При использовании серверного курсора клиентское программное обеспечение базы данных (поставщик OLE DB или драйвер ODBC) прозрачно инкапсулирует клиентские запросы внутри специальных расширенных хранимых процедур, таких как sp_cursoropen или sp_cursorfetch. Это называется API-курсором (в отличие от курсора Transact-SQL). Когда пользователь выполняет запрос, текст запроса отправляется на сервер через sp_cursoropen; запросы на чтение из результирующего набора приводят к вызову sp_cursorfetch, который инструктирует сервер отправлять только определённое количество строк. Контролируя количество извлекаемых строк, драйвер ODBC или поставщик OLE DB может кэшировать строку или строки. Это предотвращает ситуацию, когда сервер ждёт, пока клиент прочитает все отправленные строки. Таким образом, сервер готов принять новый запрос на этом подключении.
Приложения, которые открывают курсоры и извлекают по одной строке (или небольшому количеству строк) за раз, могут легко стать ограниченными сетевой задержкой, особенно в глобальной сети (WAN). В быстрой сети с множеством различных пользовательских подключений нагрузка, необходимая для обработки множества запросов курсора, может стать значительной. Из-за накладных расходов, связанных с позиционированием курсора в соответствующем месте результирующего набора, обработкой каждого запроса и подобными операциями, серверу более эффективно обрабатывать один запрос, возвращающий 100 строк, чем обрабатывать 100 отдельных запросов, возвращающих те же 100 строк по одной строке за раз.
Обнаружение
Для устранения проблем с плохим использованием курсоров можно использовать следующие инструменты.
Performance Monitor
Посмотрев на счётчик SQL Server: Cursor Manager By Type – Cursor Requests/Sec, вы можете получить общее представление о том, сколько курсоров используется в системе. Системы с высоким использованием ЦП из-за маленького размера выборки обычно имеют сотни запросов курсора в секунду. Специальных счётчиков, перечисляющих размер буфера выборки, нет.
DMV
Вы можете использовать следующий запрос, чтобы определить подключения с API-курсорами (в отличие от курсоров Transact-SQL), которые используют размер буфера выборки, равный одной строке. Использовать больший буфер выборки, например 100 строк, гораздо эффективнее.
select
cur.*
from
sys.dm_exec_connections con
cross apply sys.dm_exec_cursors(con.session_id) as cur
where
cur.fetch_buffer_size = 1
and cur.properties LIKE 'API%' -- API курсор (курсоры Transact-SQL всегда имеют размер буфера выборки 1)
Трассировка SQL
Используйте трассировку, включающую класс событий RPC:Completed, и ищите операторы sp_cursorfetch. Значение четвёртого параметра — это количество строк, возвращаемых выборкой. Максимальное количество строк, запрашиваемое для возврата, указывается как входной параметр в соответствующем классе событий RPC:Starting.
Решение
- Определите, являются ли курсоры наиболее подходящим средством для выполнения обработки, или возможно ли выполнение операций на основе наборов, которое обычно более эффективно.
- Рассмотрите возможность включения нескольких активных результирующих наборов (MARS) при подключении к SQL Server.
- Обратитесь к соответствующей документации для вашего конкретного API, чтобы определить, как указать больший размер буфера выборки для курсора:
- ODBC —
SQL_ATTR_ROW_ARRAY_SIZE - OLE DB —
IRowset::GetNextRowsилиIRowsetLocate::GetRowsAt
- ODBC —
Узкие места памяти
В этом разделе рассматриваются состояния нехватки памяти и способы их диагностики, а также различные ошибки памяти, возможные причины и способы устранения.
Общие сведения
Очень часто для обозначения различных ресурсов памяти используется единый общий термин «память». Поскольку существует несколько типов ресурсов памяти, важно понимать и различать, на какой конкретный ресурс памяти ссылаются.
Виртуальное адресное пространство и физическая память
В операционной системе Windows каждый процесс имеет собственное виртуальное адресное пространство (иногда называемое VAS). Набор всех виртуальных адресов, доступных для использования процессом, составляет размер виртуального адресного пространства. Размер виртуального адресного пространства зависит от архитектуры (32-разрядная или 64-разрядная) и операционной системы. В контексте устранения неполадок важно понимать, что виртуальное адресное пространство является расходуемым ресурсом памяти, и приложение может его исчерпать, особенно на 32-разрядной платформе, даже если физическая память всё ещё доступна.
AWE, заблокированные страницы и SQL Server
Address Windowing Extensions (AWE) — это API, который позволяет 32-разрядному приложению управлять физической памятью за пределами 4 ГБ. Технически механизм AWE не является необходимым на 64-разрядной платформе. Однако он там присутствует. Страницы памяти, выделенные через механизм AWE, называются заблокированными страницами на 64-разрядной платформе.
Как на 32-разрядных, так и на 64-разрядных платформах память, выделенная через механизм AWE, не может быть выгружена (paged out). Это может быть полезно для приложения. Это также влияет на объём ОЗУ, доступный системе и другим приложениям, что может иметь негативные последствия. По этой причине для использования AWE учётной записи, от которой запускается SQL Server, должно быть предоставлено право Lock Pages in Memory.
С точки зрения устранения неполадок важно отметить, что буферный пул SQL Server использует память, выделенную через AWE; однако только страницы базы данных могут быть сопоставлены или отключены для использования дополнительной памяти, выделенной через AWE. Память, выделенная через механизм AWE, не отображается в диспетчере задач или в счётчике производительности Process: Private Bytes. Для получения этой информации необходимо использовать счётчики, специфичные для SQL Server, или динамические административные представления.
Давление памяти (Memory Pressures)
Давление памяти указывает на то, что доступно ограниченное количество памяти. Определение того, когда SQL Server работает в условиях нехватки памяти, поможет вам в устранении проблем, связанных с памятью. SQL Server реагирует по-разному в зависимости от типа присутствующего давления памяти. В следующей таблице обобщены типы давления памяти и их общие причины. Во всех случаях вы с большей вероятностью увидите сообщения о тайм-ауте или явные сообщения о нехватке памяти.
| Давление | Внешнее (External) | Внутреннее (Internal) |
|---|---|---|
| Физическое | Физическая память (ОЗУ) заканчивается. Это приводит к усечению рабочих наборов (trimming working sets) работающих процессов, что может привести к общему замедлению работы. SQL Server обнаруживает это состояние и, в зависимости от конфигурации, может уменьшить целевой объём фиксации (commit target) буферного пула и начать очистку внутренних кэшей. | SQL Server обнаруживает высокое потребление памяти внутри себя, вызывая перераспределение памяти между внутренними компонентами. Внутреннее давление памяти может быть результатом:
|
| Виртуальное | Заканчивается доступная память для фиксации (commit) (разница между Memory:Commit Limit и Memory:Committed Bytes мала). Это может быть связано с нехваткой места в файлах подкачки системы. Это состояние может привести к сбою выделения памяти. Это условие может привести к тому, что вся система будет реагировать очень медленно или даже остановится. | Заканчивается виртуальное адресное пространство (прямые выделения, DLL, загруженные в VAS SQL Server, большое количество потоков) или фрагментация VAS (много VAS доступно, но маленькими блоками). SQL Server обнаруживает это состояние и может освободить зарезервированные области VAS, уменьшить целевой объём фиксации буферного пула и начать сжимать кэши. |
Windows имеет механизм уведомлений, который сообщает, если физическая память работает на высоком или низком уровне. SQL Server использует этот механизм в своих решениях по управлению памятью.
Общие шаги по устранению неполадок
Общие шаги по устранению неполадок для каждого случая описаны в таблице 3.
| Давление | Внешнее | Внутреннее |
|---|---|---|
| Физическое |
|
|
| Виртуальное |
|
Следовать шагам внутреннего физического давления памяти. |
Обнаружение давления памяти
Давление памяти само по себе не указывает на проблему. Давление памяти является необходимым, но недостаточным условием для того, чтобы сервер впоследствии столкнулся с ошибками памяти. Работа в условиях давления памяти может быть нормальным рабочим состоянием для сервера. Однако признаки давления памяти также могут указывать на то, что сервер работает близко к своей ёмкости, и существует вероятность возникновения ошибок нехватки памяти. В случае нормально работающего сервера вы можете использовать информацию о давлении памяти в качестве базовой линии для определения причин последующих состояний нехватки памяти.
Инструменты для диагностики памяти
Для устранения проблем с памятью можно использовать следующие инструменты и источники информации:
- Общие DMV о состоянии системы и SQL Server, а также DMV, специфичные для памяти.
- Команда
DBCC MEMORYSTATUS. - Кольцевые буферы SQL Server (ring buffers).
- Счётчики производительности.
- Журнал ошибок SQL Server и журналы приложений и системы Windows. Вы можете использовать средство просмотра файлов журнала (Log File Viewer) в SQL Server Management Studio для просмотра журналов Windows и SQL Server в одном месте в синхронизированном по времени виде.
Полезные DMV
Несколько новых динамических административных представлений в SQL Server упрощают получение информации, которая может быть полезна при устранении проблем с памятью. В некоторых случаях новые DMV предоставляют информацию, ранее доступную только в выводе DBCC MEMORYSTATUS. Вот краткий обзор новых DMV для устранения проблем с памятью:
sys.dm_os_memory_brokers— предоставляет информацию о распределении памяти с использованием внутреннего диспетчера памяти SQL Server. Информация может быть полезна для определения очень больших потребителей памяти.sys.dm_os_memory_nodesиsys.dm_os_memory_node_access_stats— предоставляют информацию о физических узлах памяти с неоднородным доступом (NUMA) и статистике доступа к узлам, сгруппированной по типу страницы.sys.dm_os_nodes— предоставляет информацию о конфигурации узлов ЦП для SQL Server. Также отражает конфигурацию программной NUMA.sys.dm_os_process_memory— предоставляет обзорную информацию об использовании памяти SQL Server.sys.dm_os_sys_memory— предоставляет обзорную информацию об использовании системной памяти.sys.dm_resource_governor_configuration,sys.dm_resource_governor_resource_poolsиsys.dm_resource_governor_workload_groups— предоставляют информацию о состоянии функции регулятора ресурсов (Resource Governor) SQL Server. Некоторые параметры конфигурации регулятора ресурсов влияют на то, как SQL Server распределяет память; эти параметры следует проверять при устранении проблем с памятью.
Регулятор ресурсов
Регулятор ресурсов — это новая функция SQL Server Enterprise, которая позволяет изменять политики распределения памяти SQL Server. При устранении ошибок, связанных с памятью, помните, что ошибки нехватки памяти могут быть связаны с конфигурацией регулятора ресурсов. В частности:
- Сообщение об ошибке нехватки памяти (701) теперь всегда содержит ссылку на пул ресурсов регулятора ресурсов, независимо от того, используется регулятор ресурсов или нет. Эта ссылка не обязательно указывает на проблему с вашей конфигурацией регулятора ресурсов; она относится к тому факту, что неудачное выделение памяти произошло в рамках определённого пула ресурсов.
- Уменьшение
MAX_MEMORY_PERCENTдля пула ресурсов регулятора ресурсов может вызвать ошибки нехватки памяти в этом пуле, даже если память в остальном доступна. - Значения
MIN_MEMORY_PERCENT, превышающие ноль в некоторых пулах ресурсов, могут уменьшить объём памяти, доступный для других пулов ресурсов, вызывая эффекты, аналогичные уменьшениюMAX_MEMORY_PERCENT. - Для каждого пула ресурсов регулятора ресурсов существует набор брокеров памяти (memory brokers), которые отражаются в выводе
sys.dm_os_memory_brokersиDBCC MEMORYSTATUS. Внутреннее давление памяти может быть локализовано в конкретном пуле ресурсов в зависимости от нагрузки и конфигурации этого пула. Использование памяти для компиляции запросов, выполнения и кэшей разделяется на основе каждого пула ресурсов. - Команды, такие как
DBCC FREESYSTEMCACHEиDBCC FREEPROCCACHE, принимаютpool_idв качестве параметра, что позволяет очистить часть кэша, соответствующую определённому пулу ресурсов. - Набор DMV, которые можно использовать при устранении проблем с памятью (
sys.dm_exec_cached_plans,sys.dm_os_memory_cache_entries,sys.dm_exec_query_resource_semaphores,sys.dm_exec_query_memory_grants,sys.dm_exec_requests,sys.dm_exec_sessions), дополнены столбцамиpool_idи/илиgroup_id, отражающими конфигурацию регулятора ресурсов.
Внешнее физическое давление памяти
Посмотрите на счётчик производительности Memory: Available [M, K]Bytes. Если объём доступной памяти низкий, возможно, присутствует внешнее давление памяти. Точное значение зависит от многих факторов, таких как общий объём установленной физической памяти или архитектура (32-разрядная или 64-разрядная); однако вы можете начать исследование, когда значение упадет ниже 50–100 МБ. Как и в любой стратегии устранения неполадок, наличие базовой линии нормально работающей системы даёт вам хорошее справочное значение для сравнения.
Если внешнее давление памяти существует и вы видите ошибки, связанные с памятью, вам необходимо определить основных потребителей физической памяти в системе. Для этого посмотрите на счётчики производительности Process: Working Set и определите крупнейших потребителей.
Общее использование физической памяти в системе можно приблизительно оценить, суммируя следующие счётчики:
- Объект Process, счётчик Working Set для каждого процесса
- Объект Memory:
- Счётчик Cache Bytes для системного рабочего набора
- Счётчик Pool Nonpaged Bytes для размера невыгружаемого пула
- Счётчик Available Bytes
- Счётчик Modified Page List Bytes
- Если внешнего давления нет, счётчик Process: Private Bytes должен быть близок к размеру рабочего набора (Process: Working Set), что означает, что память не выгружается.
Обратите внимание, что соответствующие счётчики производительности не учитывают память, выделенную через механизмы AWE. Таким образом, информации недостаточно, если включен AWE или используются заблокированные страницы. В этом случае вам необходимо посмотреть на распределение памяти внутри SQL Server, чтобы получить полную картину. Вы можете использовать DMV sys.dm_os_memory_clerks следующим образом, чтобы узнать, сколько памяти SQL Server выделил через механизм AWE.
select
sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
from
sys.dm_os_memory_clerks
В 64-разрядной версии, если учётной записи, от которой запускается SQL Server, предоставлено право lock pages in memory и редакция (Enterprise или Developer) позволяет использовать заблокированные страницы, SQL Server выделяет память с использованием механизма AWE. Нет необходимости явно устанавливать параметр awe enabled. Память, выделенная через API AWE, будет отражена в выводе предыдущего запроса.
В SQL Server вы можете получить эту информацию, просто запросив DMV sys.dm_os_process_memory.
Столбец physical_memory_in_use указывает общее использование памяти сервером, включая выделение через API больших страниц и AWE. Столбцы large_page_allocations_kb и locked_pages_allocations_kb показывают объём памяти, выделенный с использованием API больших страниц и AWE соответственно.
process_physical_memory_low = 1 указывает, что процесс реагирует на уведомление о низком уровне физической памяти. Значение memory_utilization_percentage ниже 100% при нормальной нагрузке может потребовать расследования, если сервер испытывает внешнее давление памяти.
Аналогично, вы можете использовать DMV sys.dm_os_sys_memory в SQL Server для оценки состояния системы.
select
total_physical_memory_kb / 1024 as phys_mem_mb,
available_physical_memory_kb / 1024 as avail_phys_mem_mb,
system_cache_kb /1024 as sys_cache_mb,
(kernel_paged_pool_kb+kernel_nonpaged_pool_kb) / 1024
as kernel_pool_mb,
total_page_file_kb / 1024 as total_page_file_mb,
available_page_file_kb / 1024 as available_page_file_mb,
system_memory_state_desc
from sys.dm_os_sys_memory
Этот запрос возвращает информацию о состоянии системы (общие и доступные значения), а также глобальное состояние, если система обнаруживает низкий, высокий или стабильный уровень памяти. Индикатор Available physical memory is low в столбце system_memory_state_desc является ещё одним признаком внешнего давления памяти, требующего дальнейшего расследования. Снятие внешнего давления памяти путём выявления и устранения (если возможно) основных потребителей физической памяти и/или добавления большего объёма памяти обычно должно решать проблемы, связанные с памятью.
Внешнее виртуальное давление памяти
Вам необходимо определить, достаточно ли места в файле(ах) подкачки для размещения текущих выделений памяти. Чтобы проверить это, посмотрите на следующие счётчики: Memory: Commit Limit, Paging File: %Usage, Paging File: %Usage Peak. Commit Limit — это объём виртуальной памяти, который может быть зафиксирован (committed) без расширения пространства файла подкачки.
Вы можете приблизительно оценить объём выгруженной памяти на процесс, вычитая значение Process: Working Set из счётчика Process: Private Bytes.
Если Paging File: %Usage Peak высок, проверьте системный журнал событий на наличие событий, которые могут указывать на рост файла подкачки или уведомления о «нехватке виртуальной памяти». Возможно, вам потребуется увеличить размер вашего файла(ов) подкачки. Высокое значение Paging File: %Usage указывает на перегрузку по физической памяти и должно рассматриваться совместно с внешним физическим давлением памяти (крупные потребители, достаточный объём установленной ОЗУ).
В SQL Server вы можете оценить состояние ограничений файла подкачки из соответствующих столбцов DMV sys.dm_os_sys_memory. total_page_file_kb соответствует счётчику производительности Commit Limit. available_page_file_kb представляет доступную память для фиксации. Обратите внимание, что разница между этими двумя значениями не отражает фактическое использование файла подкачки. Она равна текущему значению commit charge для системы.
Внутреннее физическое давление памяти
Поскольку внутреннее давление памяти устанавливается самим SQL Server, логичным шагом является изучение распределения памяти внутри SQL Server на предмет любых аномалий в распределении буферов. Обычно буферный пул составляет большую часть памяти, зафиксированной SQL Server. Чтобы определить объём памяти, принадлежащей буферному пулу, посмотрите на вывод DBCC MEMORYSTATUS. В разделе Buffer Pool найдите значение Target.
Target вычисляется SQL Server как количество 8-КБ страниц, которое он может зафиксировать без вызова подкачки. Target пересчитывается периодически и в ответ на уведомления Windows о низком/высоком уровне памяти. Уменьшение количества целевых страниц на нормально загруженном сервере может указывать на реакцию на внешнее физическое давление памяти.
Если значение Committed превышает Target и вы исключили внешнее давление, продолжайте расследование крупнейших потребителей памяти внутри SQL Server.
Обратите внимание, что если сервер не загружен, Target, скорее всего, будет превышать Committed и значение, сообщаемое счётчиком производительности Process: Private Bytes, что является нормальным.
Если Target низкий, но счётчик Process: Private Bytes высок, вы можете столкнуться с внутренним давлением памяти от компонентов, использующих память вне буферного пула. Компоненты, загруженные в процесс SQL Server, такие как COM-объекты, связанные серверы, расширенные хранимые процедуры и SQLCLR, вносят вклад в потребление памяти вне буферного пула. Отслеживать память, потребляемую этими компонентами, нелегко, особенно если они не используют интерфейсы управления памятью SQL Server.
Компоненты, которые используют механизмы управления памятью SQL Server, используют буферный пул для небольших выделений памяти. Если выделение больше 8 КБ, эти компоненты используют память вне буферного пула через интерфейс многопоточного распределителя.
Вот быстрый способ проверить объём памяти, потребляемой через многопоточный распределитель.
-- объём памяти, выделенный через интерфейс многопоточного распределителя
select sum(multi_pages_kb) from sys.dm_os_memory_clerksВы можете получить более детальное распределение памяти, выделенной через многопоточный распределитель, с помощью следующего запроса.
select
type, sum(multi_pages_kb) as [KB]
from
sys.dm_os_memory_clerks
where
multi_pages_kb != 0
group by type
order by 2 desc
Если вы видите большие объёмы памяти, выделенные через многопоточный распределитель, проверьте конфигурацию сервера и попытайтесь определить компоненты, потребляющие большую часть памяти, с помощью предыдущего оператора SELECT. Базовые значения потребления памяти должны дать представление о том, не потребляет ли какой-либо конкретный компонент неожиданно большое количество памяти.
Если Target низкий, но в процентном соотношении он составляет большую часть памяти, потребляемой SQL Server, ищите источники внешнего давления памяти, как описано ранее в этом документе, или проверьте параметры конфигурации памяти сервера.
Если у вас установлены параметры max server memory и/или min server memory, вы должны сравнить Target с этими значениями. Параметр max server memory ограничивает максимальный объём памяти, потребляемый буферным пулом, в то время как сервер в целом всё ещё может потреблять больше. Параметр min server memory указывает серверу не освобождать память буферного пула ниже указанного значения. Если Target меньше min server memory и сервер находится под нагрузкой, проверьте наличие внешнего виртуального давления памяти, общих сбоев операционной системы при выделении памяти или ограничений используемой редакции SQL Server. Значение Target не может превышать параметр max server memory.
Сначала проверьте количество stolen pages из вывода DBCC MEMORYSTATUS.
Buffer Pool Value
---------------------------------------- -----------
Committed 11141
Target 238145
Database 5446
Dirty 94
In IO 0
Latched 0
Free 377
Stolen 5318
Reserved 0
Visible 238145
Stolen Potential 733608
Limiting Factor 13
Last OOM Factor 0
Page Life Expectancy 272532Высокий процент (более 75–80%) stolen pages по отношению к Target является индикатором внутреннего давления памяти.
Более подробную информацию о распределении памяти компонентами сервера можно получить с помощью DMV sys.dm_os_memory_clerks.
-- объём памяти, потребляемый компонентами вне буферного пула
-- обратите внимание, что мы исключаем single_pages_kb, так как они поступают из BPool
-- BPool учитывается следующим запросом
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb) as [Overall used w/o BPool, Kb]
from
sys.dm_os_memory_clerks
where
type <> 'MEMORYCLERK_SQLBUFFERPOOL'
-- объём памяти, потребляемый BPool
-- обратите внимание, что в настоящее время только BPool использует AWE
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb
+ awe_allocated_kb) as [Used by BPool with AWE, Kb]
from
sys.dm_os_memory_clerks
where
type = 'MEMORYCLERK_SQLBUFFERPOOL'
В SQL Server общую информацию о распределении памяти по узлам памяти (аналогичную начальному выводу DBCC MEMORYSTATUS) можно получить с помощью DMV sys.dm_os_memory_nodes.
Эту информацию можно использовать вместо выполнения DBCC MEMORYSTATUS для быстрого получения сводной информации об использовании памяти. Для компьютера с NUMA будет строка для каждого узла, а для SMP-компьютера будет одна строка вывода.
Если присутствует узел выделенного административного подключения (DAC), вы увидите дополнительную строку для node_id. node_id = 32 указывает на 32-разрядную версию SQL Server, а node_id = 64 — на 64-разрядную.
Детальную информацию для каждого компонента можно получить следующим образом (включая память, выделенную как внутри, так и вне буферного пула).
declare @total_alloc bigint
declare @tab table (
type nvarchar(128) collate database_default
,allocated bigint
,virtual_res bigint
,virtual_com bigint
,awe bigint
,shared_res bigint
,shared_com bigint
,topFive nvarchar(128)
,grand_total bigint
);
-- обратите внимание, что этот общий итог исключает зафиксированную память буферного пула, так как он является крупнейшим потребителем, что нормально
select
@total_alloc =
sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb)
from
sys.dm_os_memory_clerks
print
'Total allocated (including from bBuffer pPool): '
+ CAST(@total_alloc as varchar(10)) + ' Kb'
insert into @tab
select
type
,sum(single_pages_kb + multi_pages_kb) as allocated
,sum(virtual_memory_reserved_kb) as vertual_res
,sum(virtual_memory_committed_kb) as virtual_com
,sum(awe_allocated_kb) as awe
,sum(shared_memory_reserved_kb) as shared_res
,sum(shared_memory_committed_kb) as shared_com
,case when (
(sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb))/(@total_alloc + 0.0)) >= 0.05
then type
else 'Other'
end as topFive
,(sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb)) as grand_total
from
sys.dm_os_memory_clerks
group by type
order by (sum(single_pages_kb + multi_pages_kb + (CASE WHEN type <>
'MEMORYCLERK_SQLBUFFERPOOL' THEN virtual_memory_committed_kb ELSE 0 END) +
shared_memory_committed_kb)) desc
select * from @tab
Обратите внимание, что предыдущий запрос по-разному обрабатывает буферный пул, поскольку он предоставляет память другим компонентам через распределитель одной страницы. Чтобы определить десять крупнейших потребителей страниц буферного пула (через распределитель одной страницы), вы можете использовать следующий запрос.
-- топ-10 потребителей памяти из BPool
select
top 10 type,
sum(single_pages_kb) as [SPA Mem, Kb]
from
sys.dm_os_memory_clerks
group by type
order by sum(single_pages_kb) desc
Вы обычно не контролируете потребление памяти внутренними компонентами. Однако определение того, какие компоненты потребляют большую часть памяти при нормальной работе, и сравнение с моментом возникновения ошибок может помочь сузить круг расследования проблемы.
Вы также можете проверить следующие счётчики производительности на наличие признаков давления памяти:
- SQL Server: Buffer Manager object
- Высокое значение Checkpoint pages/sec
- Высокое значение Lazy writes/sec
Недостаток памяти и нагрузка на ввод-вывод обычно являются связанными узкими местами.
Кэши и давление памяти
Альтернативный способ взглянуть на внешнее и внутреннее давление памяти — посмотреть на поведение кэшей памяти.
В новых версиях SQL Server внутренняя реализация отличается от SQL Server 2000 своей унифицированной структурой кэширования. Для удаления записей из кэшей структура реализует алгоритм часов (clock algorithm). В настоящее время она использует две стрелки часов — внутреннюю и внешнюю.
Внутренняя стрелка часов контролирует размер кэша относительно других кэшей. Она начинает двигаться, когда структура предсказывает, что кэш приближается к своему пределу.
Внешняя стрелка часов начинает двигаться, когда SQL Server в целом попадает в условия давления памяти. Движение внешней стрелки часов может быть вызвано как внешним, так и внутренним давлением памяти. Не путайте движение внутренней и внешней стрелок часов с внутренним и внешним давлением памяти.
Информация о движении стрелок часов доступна через DMV sys.dm_os_memory_cache_clock_hands, как показано в следующем коде. Каждая запись кэша имеет отдельную строку для внутренней и внешней стрелки часов. Если вы видите увеличение rounds_count и removed_all_rounds_count, сервер испытывает внутреннее или внешнее давление памяти.
select *
from
sys.dm_os_memory_cache_clock_hands
where
rounds_count > 0
and removed_all_rounds_count > 0
Вы можете получить дополнительную информацию о кэшах, такую как их размер, присоединившись к DMV sys.dm_os_cache_counters следующим образом.
select
distinct cc.cache_address,
cc.name,
cc.type,
cc.single_pages_kb + cc.multi_pages_kb as total_kb,
cc.single_pages_in_use_kb + cc.multi_pages_in_use_kb as total_in_use_kb,
cc.entries_count,
cc.entries_in_use_count,
ch.removed_all_rounds_count,
ch.removed_last_round_count
from
sys.dm_os_memory_cache_counters cc
join sys.dm_os_memory_cache_clock_hands ch on (cc.cache_address = ch.cache_address)
/*
-- раскомментируйте этот блок, чтобы получить информацию только для кэшей с движущимися стрелками
where
ch.rounds_count > 0
and ch.removed_all_rounds_count > 0
*/
order by total_kb desc
Кольцевые буферы (Ring Buffers)
Значительный объём диагностической информации о памяти можно получить из DMV sys.dm_os_ring_buffers. Каждый кольцевой буфер хранит запись о последнем количестве уведомлений определённого типа. Вы можете узнать о непустых кольцевых буферах вместе с количеством их событий с помощью следующего запроса.
select
ring_buffer_type
, count(*) as [Event count]
from sys.dm_os_ring_buffers
group by ring_buffer_type
order by ring_buffer_type
RING_BUFFER_SCHEDULER_MONITOR
Информация из этого кольцевого буфера может быть использована для оценки общего состояния системы. Записи о состоянии системы хранятся с интервалом в 1 минуту и могут выглядеть следующим образом.
<Record id = "4560" type ="RING_BUFFER_SCHEDULER_MONITOR" time ="419955168">
<SchedulerMonitorEvent>
<SystemHealth>
<ProcessUtilization>1</ProcessUtilization>
<SystemIdle>77</SystemIdle>
<UserModeTime>13572087</UserModeTime>
<KernelModeTime>156001</KernelModeTime>
<PageFaults>291</PageFaults>
<WorkingSetDelta>184320</WorkingSetDelta>
<MemoryUtilization>100</MemoryUtilization>
</SystemHealth>
</SchedulerMonitorEvent>
</Record>Используя запрос, аналогичный следующему примеру, вы можете увидеть общее состояние сервера, поскольку в этом кольцевом буфере присутствуют записи SystemHealth.
-- чтобы связать события, преобразуйте временные метки во время
-- обратите внимание, что счётчик RDTSC зависит от переменной тактовой частоты ЦП
declare @ts_now bigint
select @ts_now = ms_ticks from sys.dm_os_sys_info
-- "разобрать" записи
select record_id,
dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime
,SQLProcessUtilization
,SystemIdle
,100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
,UserModeTime
,KernelModeTime
,PageFaults
,WorkingSetDelta
,MemoryUtilPct
from (
select
record.value('(./Record/@id)[1]', 'int') as record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/UserModeTime)[1]', 'int') as UserModeTime,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/KernelModeTime)[1]', 'int') as KernelModeTime,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/PageFaults)[1]', 'int') as PageFaults,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/WorkingSetDelta)[1]', 'int') as WorkingSetDelta,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/MemoryUtilization)[1]', 'int') as MemoryUtilPct,
timestamp
from (
select timestamp, convert(xml, record) as record
from sys.dm_os_ring_buffers
where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
and record like '%<SystemHealth>%') as x
) as y
order by record_id desc
RING_BUFFER_RESOURCE_MONITOR
Вы можете использовать информацию из уведомлений монитора ресурсов для выявления изменений состояния памяти. В этом кольцевом буфере есть запись для каждого изменения состояния памяти. Внутри SQL Server существует структура, которая отслеживает различные давления памяти. Когда состояние памяти меняется, задача монитора ресурсов генерирует уведомление. Это уведомление используется внутренне компонентами для корректировки их использования памяти в соответствии с состоянием памяти и предоставляется пользователю через DMV sys.dm_os_ring_buffers.
В SQL Server запись может выглядеть так:
<Record id="0" type="RING_BUFFER_RESOURCE_MONITOR" time="146278552">
<ResourceMonitor>
<Notification>RESOURCE_MEMPHYSICAL_HIGH</Notification>
<IndicatorsProcess>0</IndicatorsProcess>
<IndicatorsSystem>1</IndicatorsSystem>
<NodeId>0</NodeId>
<Effect type="APPLY_LOWPM" state="EFFECT_OFF" reversed="0">0</Effect>
<Effect type="APPLY_HIGHPM" state="EFFECT_ON" reversed="0">0</Effect>
<Effect type="REVERT_HIGHPM" state="EFFECT_OFF" reversed="0">0</Effect>
</ResourceMonitor>
<MemoryNode id="0">
<ReservedMemory>6282232</ReservedMemory>
<CommittedMemory>38712</CommittedMemory>
<SharedMemory>0</SharedMemory>
<AWEMemory>8192</AWEMemory>
<SinglePagesMemory>3208</SinglePagesMemory>
<MultiplePagesMemory>21896</MultiplePagesMemory>
</MemoryNode>
<MemoryRecord>
<MemoryUtilization>100</MemoryUtilization>
<TotalPhysicalMemory>6222536</TotalPhysicalMemory>
<AvailablePhysicalMemory>3044516</AvailablePhysicalMemory>
<TotalPageFile>12665292</TotalPageFile>
<AvailablePageFile>9388376</AvailablePageFile>
<TotalVirtualAddressSpace>8589934464</TotalVirtualAddressSpace>
<AvailableVirtualAddressSpace>8583481552</AvailableVirtualAddressSpace>
<AvailableExtendedVirtualAddressSpace>0</AvailableExtendedVirtualAddressSpace>
</MemoryRecord>
</Record>Из этой записи вы можете сделать вывод, что сервер получил уведомление о высоком уровне физической памяти. Вы также можете увидеть объёмы памяти в килобайтах. Используя запрос, аналогичный тому, что был в предыдущем разделе, вы можете получить информацию с временными метками об изменениях состояния памяти сервера.
При получении уведомления о низком уровне памяти буферный пул пересчитывает свой Target. Обратите внимание, что целевой счётчик остаётся в пределах, указанных параметрами min server memory и max server memory. Если новый зафиксированный целевой объём для буферного пула ниже, чем текущее количество зафиксированных буферов, буферный пул начинает сжиматься до тех пор, пока внешнее физическое давление памяти не будет снято. Обратите внимание, что SQL Server 2000 не реагировал на физическое давление памяти при работе с включенным AWE.
RING_BUFFER_OOM
Этот кольцевой буфер содержит записи, указывающие на состояние нехватки памяти на сервере. Запись может выглядеть так:
<Record id="0" type="RING_BUFFER_OOM" time="423802767">
<OOM>
<Action>FAIL_PAGE_ALLOCATION</Action>
<Resources>1</Resources>
<Task>0x000000000050FDC8</Task>
<Pool>258</Pool>
</OOM>
<MemoryNode id="0">
<ReservedMemory>6292472</ReservedMemory>
<CommittedMemory>48096</CommittedMemory>
<SharedMemory>0</SharedMemory>
<AWEMemory>114688</AWEMemory>
<SinglePagesMemory>28848</SinglePagesMemory>
<MultiplePagesMemory>26368</MultiplePagesMemory>
</MemoryNode>
<MemoryRecord>
<MemoryUtilization>100</MemoryUtilization>
<TotalPhysicalMemory>6222536</TotalPhysicalMemory>
<AvailablePhysicalMemory>2074616</AvailablePhysicalMemory>
<TotalPageFile>12665292</TotalPageFile>
<AvailablePageFile>8327120</AvailablePageFile>
<TotalVirtualAddressSpace>8589934464</TotalVirtualAddressSpace>
<AvailableVirtualAddressSpace>8583411684</AvailableVirtualAddressSpace>
<AvailableExtendedVirtualAddressSpace>0</AvailableExtendedVirtualAddressSpace>
</MemoryRecord>
<Stack>
... (вывод сокращён)
</Stack>
</Record>Эта запись сообщает, какая операция не удалась (фиксация, резервирование или выделение страницы), объём запрошенной памяти, в каком пуле ресурсов регулятора ресурсов произошло это выделение, а также дополнительную информацию о состоянии памяти сервера.
RING_BUFFER_MEMORY_BROKER и внутреннее давление памяти
Этот кольцевой буфер содержит записи уведомлений о памяти для каждого пула ресурсов регулятора ресурсов. Когда обнаруживается внутреннее давление памяти, уведомление о низком уровне памяти включается для компонентов, использующих буферный пул в качестве источника выделений памяти. Включение уведомления о низком уровне памяти позволяет освобождать страницы из кэшей и других компонентов, использующих их.
Внутреннее давление памяти также может быть вызвано, когда параметр max server memory изменён или когда процент stolen pages из буферного пула превышает 80%.
Уведомления о внутреннем давлении памяти («Shrink») можно наблюдать, запрашивая кольцевой буфер брокера памяти.
RING_BUFFER_BUFFER_POOL
Этот кольцевой буфер содержит записи, указывающие на серьёзные сбои буферного пула, включая состояния нехватки памяти в буферном пуле.
Эта запись сообщает, какой сбой произошёл (FAIL_OOM, FAIL_MAP, FAIL_RESERVE_ADJUST, FAIL_LAZYWRITER_NO_BUFFERS), и состояние буферного пула в этот момент.
Внутреннее виртуальное давление памяти
Потребление VAS можно отслеживать с помощью DMV sys.dm_os_virtual_address_dump. Сводку по VAS можно запросить с помощью следующего представления.
-- представление сводки по виртуальному адресному пространству
-- создаёт список регионов SQL Server
-- показывая количество зарезервированных и свободных областей заданного размера
CREATE VIEW VASummary AS
SELECT
Size = VaDump.Size,
Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 0 ELSE 1
END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 1 ELSE 0 END)
FROM
(
--- объединить все выделения в соответствии с базой выделения, не учитывать
--- выделения с нулевой базой выделения
SELECT
CONVERT(VARBINARY, SUM(region_size_in_bytes)) AS Size,
region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
--- мы не должны группировать выделения с нулевой базой выделения
--- просто получить их как есть
SELECT CONVERT(VARBINARY, region_size_in_bytes),
region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size
Следующие запросы могут быть использованы для оценки состояния VAS.
-- доступная память во всех свободных областях
SELECT SUM(Size*Free)/1024 AS [Total avail mem, KB]
FROM VASummary
WHERE Free <> 0
-- получить размер самой большой доступной области
SELECT CAST(MAX(Size) AS INT)/1024 AS [Max free size, KB]
FROM VASummary
WHERE Free <> 0
Если самая большая доступная область меньше 4 МБ, ваша система, вероятно, испытывает давление VAS. SQL Server отслеживают и реагируют на давление VAS.
В SQL Server вы можете оценить состояние низкого уровня виртуальной памяти с помощью DMV sys.dm_os_process_memory. Бит столбца process_virtual_memory_low указывает, было ли обнаружено состояние низкого уровня виртуальной памяти.
Общие шаги по устранению ошибок памяти
Следующий список содержит общие шаги, которые помогут вам в устранении ошибок памяти.
- Убедитесь, что сервер работает в условиях внешнего давления памяти. Если внешнее давление присутствует, попытайтесь сначала устранить его, а затем посмотрите, сохраняется ли проблема или ошибки.
- Соберите и сравните счётчики производительности, как описано в предыдущих разделах.
- Проверьте параметры конфигурации памяти (
sp_configure), min memory per query, min/max server memory, awe enabled и право Lock Pages in Memory. Обратите внимание на необычные настройки. Исправьте их при необходимости. - Проверьте наличие любых нестандартных параметров
sp_configure, которые могут косвенно влиять на сервер. - Проверьте наличие внутренних давлений памяти.
- Наблюдайте за выводом
DBCC MEMORYSTATUSи тем, как он меняется, когда вы видите сообщения об ошибках памяти. - Проверьте рабочую нагрузку (количество одновременных сеансов, выполняющиеся в данный момент запросы).
Ошибки памяти
701 — There is insufficient system memory in resource pool 'pool_name' to run this query.
Причины:
Это общая ошибка нехватки памяти для сервера. Она указывает на неудачное выделение памяти. Это может быть связано с различными причинами, включая достижение ограничений памяти при текущей рабочей нагрузке. С увеличением требований к памяти для SQL Server и определёнными настройками конфигурации (такими как параметр max server memory и параметры конфигурации регулятора ресурсов) пользователи с большей вероятностью увидят эту ошибку. Обычно транзакция, которая не удалась, не является причиной этой ошибки. Проверьте столбец out_of_memory_count в DMV sys.dm_resource_governor_resource_pools. Если этот счётчик локализован в определённом пуле ресурсов, наиболее вероятной причиной является конфигурация регулятора ресурсов.
Устранение:
Независимо от того, является ли ошибка постоянной и повторяющейся (то есть она остаётся в одном состоянии) или случайной (то есть появляется в случайные моменты времени с разными состояниями), вы должны исследовать распределение памяти сервера в то время, когда вы видите эту ошибку. Когда эта ошибка присутствует, возможно, что диагностические запросы не сработают. Когда вы видите эту ошибку, лучшее место для начала расследования — журнал ошибок. Он должен содержать вывод, который выглядит примерно так:
2009-01-28 04:27:15.43 spid51 Failed allocate pages: FAIL_PAGE_ALLOCATION 1или
2009-01-28 04:27:15.43 spid51 Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 65536Возможные сбои:
FAIL_PAGE_ALLOCATIONс указанием количества страниц, которые пытались выделить.FAIL_VIRTUAL_RESERVEс указанием количества байтов, которые пытались зарезервировать.FAIL_VIRTUAL_COMMITс указанием количества байтов, которые пытались зафиксировать.
Обычно задача, которая первой столкнулась с ошибкой нехватки памяти, не является задачей, вызвавшей это состояние. Скорее всего, это кумулятивный эффект выполнения нескольких задач. Для очень частого случая сбоя выделения одной страницы ваше расследование должно учитывать общую картину.
Следующая часть информации из журнала ошибок — это вывод состояния памяти. В зависимости от сбоя вы должны искать single page, multipage, virtual reserved или committed numbers для отдельных клерков памяти (memory clerks). Определение крупнейших потребителей памяти является ключом к продолжению расследования. Вы можете обнаружить, что крупнейшие потребители относятся к следующему типу:
- MEMORYCLERK_* — означает, что конфигурация сервера или рабочая нагрузка требуют выделения такого большого объёма памяти. Иногда проблемную рабочую нагрузку можно определить просто по клеркам памяти, но чаще вам придётся углубиться в объекты памяти, связанные с клерками, чтобы выяснить, что вызывает такое потребление памяти.
- CACHESTORE_*, USERSTORE_*, OBJECTSTORE_* — это типы кэшей. Большое потребление кэшем может означать следующее:
- Память выделена из кэша, но ещё не вставлена как запись, которую можно вытеснить. Это очень похоже на случай MEMORYCLERK, рассмотренный ранее.
- Все записи кэша используются, поэтому их нельзя вытеснить. Вы можете подтвердить это, посмотрев на DMV
sys.dm_os_memory_cache_countersи сравнив столбцыentries_countиentries_in_use_count. - Большинство записей кэша не используются. Этот случай, скорее всего, указывает на ошибку в сервере.
- MEMORYCLERK_SQLQERESERVATIONS — показывает, сколько памяти было зарезервировано для выполнения запросов с сортировками/соединениями.
Вывод состояния памяти в журнале ошибок также показывает, какой пул ресурсов регулятора ресурсов исчерпал память. Брокеры памяти для каждого пула показывают распределение памяти между stolen (компиляция), cached (кэшировано) и reserved (выделено для выполнения). Числа для трёх брокеров соответствуют трём пунктам в предыдущем списке. К сожалению, нет способа узнать, сколько памяти выделено для пула из данного клерка или объекта памяти. DMV sys.dm_os_memory_cache_entries расширен, чтобы показать pool_id, с которым связана каждая запись.
Возможные решения включают следующее:
- Устраните внешнее давление памяти.
- Увеличьте параметр max server memory, а затем скорректируйте настройки
MIN_MEMORY_PERCENTиMAX_MEMORY_PERCENTдля пула ресурсов. - Освободите кэши с помощью одной из следующих команд:
DBCC FREESYSTEMCACHE,DBCC FREESESSIONCACHEилиDBCC FREEPROCCACHE.
Если проблема повторяется, уменьшите рабочую нагрузку.
802 — There is insufficient memory available in the buffer pool.
Причины:
Эта ошибка не обязательно указывает на состояние нехватки памяти. Она может указывать на то, что память буферного пула используется кем-то другим. В SQL Server эта ошибка должна быть относительно редкой.
Устранение:
Используйте общие шаги по устранению неполадок и рекомендации, описанные для ошибки 701.
8628 — A time out occurred while waiting to optimize the query. Rerun the query.
Причины:
Эта ошибка указывает на то, что процесс компиляции запроса не удался, потому что не удалось получить объём памяти, необходимый для завершения процесса. По мере того как запрос проходит через процесс компиляции, который включает синтаксический анализ, алгебраизацию и оптимизацию, его требования к памяти могут увеличиваться. Таким образом, запрос конкурирует за ресурсы памяти с другими запросами. Если запрос превышает заданный тайм-аут (который увеличивается по мере увеличения потребления памяти запросом) во время ожидания ресурсов, возвращается эта ошибка. Наиболее вероятной причиной является наличие большого количества компиляций крупных запросов на сервере.
Устранение:
- Следуйте общим шагам по устранению неполадок, чтобы увидеть, затронуто ли общее потребление памяти сервера.
- Проверьте рабочую нагрузку. Проверьте объёмы памяти, потребляемые различными компонентами.
- Проверьте вывод
DBCC MEMORYSTATUSна наличие количества waiters для каждого шлюза (gateway). - Проверьте конфигурацию регулятора ресурсов.
- Уменьшите рабочую нагрузку, если возможно.
8645 — A timeout occurred while waiting for memory resources to execute the query in resource pool 'pool_name' (pool_id). Rerun the query.
Причины:
Эта ошибка указывает на то, что на сервере выполняется много одновременных запросов, интенсивно использующих память. Запросы, использующие сортировки (ORDER BY) и соединения, могут потреблять значительные объёмы памяти во время выполнения. Требования к памяти запроса значительно возрастают, если включена высокая степень параллелизма или если запрос работает с секционированной таблицей с несогласованными индексами. Запрос, который не может получить необходимые ресурсы памяти в течение заданного тайм-аута, получает эту ошибку. Обычно запрос, получающий ошибку, не является тем, который потребляет память.
Устранение:
- Следуйте общим шагам для оценки состояния памяти сервера.
- Определите проблемные запросы: проверьте, не работает ли значительное количество запросов с секционированными таблицами, не используют ли они несогласованные индексы и есть ли много запросов, включающих соединения и/или сортировки.
- Проверьте параметры
sp_configuredegree of parallelism и min memory per query. Попробуйте уменьшить степень параллелизма и убедитесь, что min memory per query не установлен на высокое значение. Если установлено высокое значение, даже небольшие запросы будут получать указанный объём памяти. - Узнайте, ожидают ли запросы
RESOURCE_SEMAPHORE. - Проверьте конфигурацию регулятора ресурсов.
8651 — Could not perform the operation because the requested memory grant was not available in resource pool '%ls' (%ld). Rerun the query, reduce the query load, or check resource governor configuration setting.
Причины:
Причины частично аналогичны ошибке 8645; это также может быть признаком общего состояния нехватки памяти на сервере. Слишком высокое значение параметра min memory per query также может вызвать эту ошибку.
Устранение:
- Следуйте общим шагам по устранению ошибок памяти.
- Убедитесь, что значение параметра
sp_configuremin memory per query не слишком высоко. - Проверьте настройки конфигурации регулятора ресурсов.
Узкие места ввода-вывода
Производительность SQL Server сильно зависит от подсистемы ввода-вывода. Если ваша база данных не помещается в физическую память, SQL Server постоянно перемещает страницы базы данных в буферный пул и из него. Это создаёт значительный трафик ввода-вывода. Аналогично, записи журнала должны быть сброшены на диск до того, как транзакция может быть объявлена зафиксированной. И, наконец, SQL Server использует tempdb для различных целей, таких как хранение промежуточных результатов, сортировка и хранение версий строк. Таким образом, хорошая подсистема ввода-вывода критична для производительности SQL Server.
Доступ к файлам журнала является последовательным, за исключением случаев, когда транзакция должна быть откатана, в то время как к файлам данных, включая tempdb, осуществляется случайный доступ. Таким образом, как общее правило, для лучшей производительности файлы журнала должны находиться на физическом диске, отдельном от файлов данных. Цель этого документа — не описать, как настраивать устройства ввода-вывода, а описать способы выявления наличия узкого места ввода-вывода. После выявления узкого места ввода-вывода вам может потребоваться перенастроить подсистему ввода-вывода.
Если у вас медленная подсистема ввода-вывода, ваши пользователи могут испытывать проблемы с производительностью, такие как медленное время отклика и задачи, которые не завершаются из-за тайм-аутов.
Вы можете использовать следующие счётчики производительности для выявления узких мест ввода-вывода. Обратите внимание, что эти средние значения (AVG) имеют тенденцию к смещению (в сторону занижения), если у вас редкий интервал сбора. Также не следует полагаться на один счётчик для определения узкого места; ищите несколько счётчиков для перекрёстной проверки достоверности ваших выводов.
- PhysicalDisk Object: Avg. Disk Queue Length — представляет среднее количество запросов на чтение и запись, которые были поставлены в очередь на выбранном физическом диске в течение периода выборки. Если ваша система ввода-вывода перегружена, больше операций чтения/записи будут ожидать. Если длина очереди диска часто превышает значение 2 во время пикового использования SQL Server, у вас может быть узкое место ввода-вывода.
- Avg. Disk Sec/Read — среднее время (в секундах) чтения данных с диска. Возможные диапазоны значений и их значения:
- Менее 10 мс — очень хорошо
- От 10 до 20 мс — нормально
- От 20 до 50 мс — медленно, требует внимания
- Более 50 мс — серьёзное узкое место ввода-вывода
- Avg. Disk Sec/Write — среднее время (в секундах) записи данных на диск. Рекомендации для Avg. Disk Sec/Read применимы и здесь.
- Physical Disk: %Disk Time — процент прошедшего времени, в течение которого выбранный дисковод был занят обслуживанием запросов на чтение или запись. Общее правило: если это значение превышает 50%, существует узкое место ввода-вывода.
- Avg. Disk Reads/Sec — скорость операций чтения на диске. Убедитесь, что это число меньше 85% от ёмкости диска. Время доступа к диску увеличивается экспоненциально за пределами 85% ёмкости.
- Avg. Disk Writes/Sec — скорость операций записи на диск. Убедитесь, что это число меньше 85% от ёмкости диска. Время доступа к диску увеличивается экспоненциально за пределами 85% ёмкости.
При использовании этих счётчиков вам может потребоваться скорректировать значения для конфигураций RAID, используя следующие формулы:
- RAID 0 — операций ввода-вывода на диск = (чтения + записи) / количество дисков
- RAID 1 — операций ввода-вывода на диск = [чтения + (2 * записи)] / 2
- RAID 5 — операций ввода-вывода на диск = [чтения + (4 * записи)] / количество дисков
- RAID 10 — операций ввода-вывода на диск = [чтения + (2 * записи)] / количество дисков
Вы также можете выявить узкие места ввода-вывода, изучив ожидания защёлок (latch waits). Эти ожидания защёлок учитывают ожидания физического ввода-вывода, когда страница запрашивается для чтения или записи и страница недоступна в буферном пуле. Когда страница не найдена в буферном пуле, асинхронный ввод-вывод инициируется, а затем проверяется состояние ввода-вывода. Если ввод-вывод уже завершён, рабочий процесс продолжается нормально. В противном случае он ожидает PAGEIOLATCH_EX или PAGEIOLATCH_SH в зависимости от типа запроса. Вы можете использовать следующий запрос DMV для поиска статистики ожиданий защёлок ввода-вывода.
Select wait_type,
waiting_tasks_count,
wait_time_ms
from sys.dm_os_wait_stats
where wait_type like 'PAGEIOLATCH%'
order by wait_type
Пример вывода:
wait_type waiting_tasks_count wait_time_ms signal_wait_time_ms
-----------------------------------------------------------------------
PAGEIOLATCH_DT 0 0 0
PAGEIOLATCH_EX 1230 791 11
PAGEIOLATCH_KP 0 0 0
PAGEIOLATCH_NL 0 0 0
PAGEIOLATCH_SH 13756 7241 180
PAGEIOLATCH_UP 80 66 0Когда ввод-вывод завершается, рабочий процесс помещается в очередь выполнения (runnable queue). Время между завершением ввода-вывода и фактическим планированием рабочего процесса учитывается в столбце signal_wait_time_ms. Вы можете выявить проблему ввода-вывода, если ваши waiting_task_counts и wait_time_ms значительно отклоняются от того, что вы видите обычно. Для этого важно получить базовую линию счётчиков производительности и ключевых выводов DMV, когда SQL Server работает гладко. Эти типы ожиданий могут указывать на то, испытывает ли ваша подсистема ввода-вывода узкое место, но они не предоставляют никакой видимости физического диска(ов), на котором возникает проблема.
Вы можете использовать следующий запрос DMV для поиска текущих ожидающих запросов ввода-вывода. Вы можете выполнять этот запрос периодически, чтобы проверять состояние подсистемы ввода-вывода и изолировать физический диск(и), участвующие в узких местах ввода-вывода.
select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle
Решение
Когда вы видите узкое место ввода-вывода, вашим первым инстинктом может быть модернизация подсистемы ввода-вывода для удовлетворения требований рабочей нагрузки. Это определённо поможет, но прежде чем тратить деньги на оборудование, изучите узкое место ввода-вывода, чтобы увидеть, не является ли оно результатом плохой конфигурации и/или планов запросов. Мы рекомендуем вам выполнить следующие шаги в строгом порядке.
- Конфигурация: Проверьте конфигурацию памяти SQL Server. Если SQL Server настроен с недостаточным объёмом памяти, он будет создавать больше накладных расходов на ввод-вывод. Вы можете изучить следующие счётчики для выявления давления памяти:
- Buffer Cache hit ratio
- Page Life Expectancy
- Checkpoint pages/sec
- Lazywrites/sec
- Планы запросов: Изучите планы выполнения и посмотрите, какие планы приводят к большему потреблению ввода-вывода. Возможно, что лучший план (например, индекс) может минимизировать ввод-вывод. Если есть пропущенные индексы, вы можете запустить помощник по настройке ядра базы данных, чтобы найти пропущенные индексы.
Следующий запрос DMV может быть использован для поиска пакетов или запросов, генерирующих наибольший ввод-вывод. Обратите внимание, что мы не учитываем физические записи. Это нормально, если учесть, как работают базы данных. Операторы DML и DDL в рамках запроса не записывают страницы данных напрямую на диск. Вместо этого физическая запись страниц на диск инициируется операторами только путём фиксации транзакций. Обычно физические записи выполняются либо контрольной точкой, либо ленивым писателем SQL Server. Вы можете использовать запрос DMV, подобный следующему, чтобы найти пять запросов, генерирующих наибольшее количество операций ввода-вывода. Настройка этих запросов так, чтобы они выполняли меньше логических чтений, может снизить нагрузку на буферный пул. Это позволяет другим запросам находить необходимые данные в буферном пуле при повторных выполнениях (вместо выполнения физического ввода-вывода). Таким образом, общая производительность системы улучшается.
SELECT TOP 5 (total_logical_reads/execution_count) AS avg_logical_reads, (total_logical_writes/execution_count) AS avg_logical_writes, (total_physical_reads/execution_count) AS avg_phys_reads, execution_count, statement_start_offset as stmt_start_offset, (SELECT SUBSTRING(text, statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX),text)) * 2 ELSE statement_end_offset END - statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, (SELECT query_plan from sys.dm_exec_query_plan(plan_handle)) as query_plan FROM sys.dm_exec_query_stats ORDER BY (total_logical_reads + total_logical_writes)/execution_count DESC
- Сжатие данных: Начиная с SQL Server 2008, вы можете использовать функцию сжатия данных для уменьшения размера таблиц и индексов, тем самым уменьшая размер всей базы данных. Достигаемое сжатие зависит от схемы и распределения данных. Обычно можно достичь 50-60% сжатия. В некоторых случаях мы видели до 90% сжатия. Это означает, что если вы сможете сжать активные данные на 50%, вы фактически уменьшили свои требования к вводу-выводу наполовину. Сжатие данных требует дополнительных ресурсов ЦП, что необходимо учитывать для вашей рабочей нагрузки. Вот некоторые общие стратегии.
Почему не стоит бездумно сжимать всю базу данных? В крайнем случае, если у вас есть интенсивно используемая таблица T с 10 страницами в базе данных с миллионами страниц, нет никакой выгоды от сжатия T. Даже если SQL Server сможет сжать 10 страниц до 1, вы едва ли уменьшите размер базы данных, но добавите некоторую нагрузку на ЦП. В реальной рабочей нагрузке выбор не так очевиден, но этот пример показывает, что перед сжатием нужно оценивать ситуацию. Наша рекомендация: прежде чем сжимать объект (например, индекс таблицы или секцию), посмотрите на его размер, использование и приблизительную экономию от сжатия с помощью хранимой процедуры
sp_estimate_data_compression_savings.Рассмотрим каждый из этих аспектов подробнее:
- Если размер объекта намного меньше общего размера базы данных, сжатие не даст большого выигрыша.
- Если объект интенсивно используется как для DML, так и для SELECT, вы добавите дополнительную нагрузку на ЦП, которая может повлиять на вашу рабочую нагрузку, особенно если она станет ограниченной по ЦП. Вы можете использовать
sys.dm_db_index_operational_stats, чтобы найти шаблоны использования объектов и определить, какие таблицы, индексы и секции наиболее активно используются. - Экономия от сжатия зависит от схемы и данных, и для некоторых объектов размер после сжатия может быть больше, чем до, или экономия места может быть незначительной.
Если у вас есть секционированная таблица, где данные в некоторых секциях запрашиваются нечасто, вы можете сжать эти секции и связанные с ними индексы с помощью сжатия страниц. Это распространённый сценарий для секционированных таблиц, где старые секции редко используются. Например, у вас может быть таблица, в которой данные о продажах секционированы по кварталам за многие годы. Обычно запросы выполняются по текущему кварталу; данные из других кварталов запрашиваются не так часто. Таким образом, когда текущий квартал заканчивается, вы можете изменить настройки сжатия для этой секции.
- Модернизация подсистемы ввода-вывода: Если вы подтвердили, что SQL Server настроен правильно, изучили планы запросов и всё ещё испытываете узкие места ввода-вывода, последний вариант — модернизировать подсистему ввода-вывода для увеличения пропускной способности ввода-вывода:
- Добавьте больше физических дисков в текущие дисковые массивы и/или замените текущие диски на более быстрые. Это помогает улучшить как время чтения, так и записи. Но не добавляйте больше дисков в массив, чем может поддерживать ваш контроллер ввода-вывода.
- Добавьте более быстрые или дополнительные контроллеры ввода-вывода. Рассмотрите возможность добавления большего объёма кэша (если возможно) к вашим текущим контроллерам.
tempdb
tempdb глобально хранит как внутренние, так и пользовательские объекты, а также временные таблицы, объекты и хранимые процедуры, создаваемые во время работы SQL Server.
Для каждого экземпляра SQL Server существует одна tempdb. Она может стать узким местом производительности и дискового пространства. tempdb может быть перегружена с точки зрения доступного пространства и чрезмерных операций DDL и DML. Это может привести к замедлению или сбою несвязанных приложений, работающих на сервере.
Некоторые из распространённых проблем с tempdb:
- Нехватка места в
tempdb. - Запросы, которые выполняются медленно из-за узкого места ввода-вывода в
tempdb. - Чрезмерные операции DDL, приводящие к узкому месту в системных таблицах.
- Конкуренция за выделение (allocation contention).
Прежде чем начать диагностику проблем с tempdb, давайте посмотрим, как используется пространство в tempdb. Его можно разделить на четыре основные категории:
| Категория | Описание |
|---|---|
| Пользовательские объекты |
Явно создаются пользовательскими сеансами и отслеживаются в системном каталоге. Включают:
|
| Внутренние объекты |
Это объекты с областью действия оператора, которые создаются и уничтожаются SQL Server для обработки запросов. Они не отслеживаются в системном каталоге. Включают:
Примечание: Временное хранение LOB имеет область действия пакета, а рабочая таблица курсора — область действия сеанса. |
| Хранилище версий | Используется для хранения версий строк. MARS, онлайн-индексы, триггеры и уровни изоляции на основе версионирования строк используют хранилище версий. |
| Свободное пространство | Дисковое пространство, доступное в tempdb. |
Мониторинг пространства tempdb
Лучше предотвратить проблему, чем решать её позже. Вы можете использовать счётчик производительности Free Space in tempdb (KB) для мониторинга используемого пространства tempdb. Этот счётчик отслеживает свободное пространство в tempdb в килобайтах. Администраторы могут использовать этот счётчик, чтобы определить, заканчивается ли свободное пространство в tempdb.
Однако более интересным и продуктивным вопросом является определение того, как различные категории, определённые ранее, используют дисковое пространство в tempdb.
Следующий запрос возвращает использование пространства tempdb пользовательскими и внутренними объектами. В настоящее время он предоставляет информацию только для tempdb.
Select
SUM (user_object_reserved_page_count)*8 as user_objects_kb,
SUM (internal_object_reserved_page_count)*8 as internal_objects_kb,
SUM (version_store_reserved_page_count)*8 as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb
From sys.dm_db_file_space_usage
Where database_id = 2
Устранение проблем с пространством
Пользовательские объекты, внутренние объекты и хранилище версий могут вызывать проблемы с пространством в tempdb. В этом разделе мы рассмотрим, как устранять проблемы для каждой из этих категорий.
Пользовательские объекты
Поскольку пользовательские объекты не принадлежат каким-либо конкретным сеансам, вам необходимо понять спецификации приложения, которое их создало, и соответствующим образом скорректировать требования к размеру tempdb. Вы можете найти пространство, используемое отдельными пользовательскими объектами, выполнив exec sp_spaceused @objname='<user-object>'. Например, вы можете запустить следующий скрипт для перечисления всех объектов tempdb.
DECLARE userobj_cursor CURSOR FOR
select
sys.schemas.name + '.' + sys.objects.name
from sys.objects, sys.schemas
where object_id > 100 and
type_desc = 'USER_TABLE'and
sys.objects.schema_id = sys.schemas.schema_id
go
open userobj_cursor
go
declare @name varchar(256)
fetch userobj_cursor into @name
while (@@FETCH_STATUS = 0)
begin
exec sp_spaceused @objname = @name
fetch userobj_cursor into @name
end
close userobj_cursor
go
Хранилище версий
SQL Server предоставляет механизм версионирования строк. В настоящее время следующие функции используют механизм версионирования строк:
- Триггеры
- MARS (Multiple Active Result Sets)
- Онлайн-индексы
- Уровни изоляции на основе версионирования строк
Версии строк являются общими для сеансов. Создатель версии строки не контролирует, когда версия строки может быть освобождена. Вам нужно найти, а затем, возможно, остановить самую длительную транзакцию, которая препятствует очистке версий строк.
Следующий запрос возвращает две самые длительные транзакции, которые зависят от версий в хранилище версий.
select top 2
transaction_id,
transaction_sequence_num,
elapsed_time_seconds
from sys.dm_tran_active_snapshot_database_transactions
order by elapsed_time_seconds DESC
Пример вывода показывает, что транзакция с XSN 3 и Transaction ID 8609 была активна в течение 6 523 секунд.
transaction_id transaction_sequence_num elapsed_time_seconds
-------------------- ------------------------ --------------------
8609 3 6523
20156 25 783Поскольку вторая транзакция активна относительно недолго, вы можете освободить значительный объём хранилища версий, остановив первую транзакцию. Однако невозможно оценить, сколько пространства версий будет освобождено при остановке этой транзакции. Возможно, потребуется остановить ещё несколько транзакций, чтобы освободить значительное пространство.
Вы можете смягчить эту проблему, либо правильно настроив размер tempdb с учётом хранилища версий, либо, по возможности, устраняя длительные транзакции с изоляцией снимков или длительные запросы с изоляцией чтения фиксированных данных с помощью снимков. Вы можете приблизительно оценить размер необходимого хранилища версий, используя следующую формулу (коэффициент 2 необходим для учёта наихудшего сценария, который возникает, когда две самые длительные транзакции перекрываются):
[Size of version store] = 2 * [version store data generated per minute] *
[longest running time (minutes) of the transaction]Во всех базах данных, для которых включены уровни изоляции на основе версионирования строк, данные хранилища версий, генерируемые в минуту для транзакции, примерно такие же, как и данные журнала, генерируемые в минуту. Однако есть некоторые исключения: при обновлениях регистрируются только различия; а новая вставленная строка данных не версионируется, но может регистрироваться, если это операция с неполным протоколированием и модель восстановления не является полной.
Вы также можете использовать счётчики производительности Version Generation Rate и Version Cleanup Rate для точной настройки ваших вычислений. Если ваш Version Cleanup Rate равен 0, длительная транзакция может препятствовать очистке хранилища версий.
Кстати, перед возникновением ошибки нехватки места в tempdb, SQL Server предпринимает последнюю попытку, принудительно сжимая хранилище версий. Во время процесса сжатия самые длительные транзакции, которые ещё не создали ни одной версии строк, помечаются как жертвы (victims). Это освобождает пространство версий, используемое ими. Для каждой такой транзакции-жертвы в журнале ошибок генерируется сообщение 3967. Если транзакция помечена как жертва, она больше не может читать версии строк в хранилище версий или создавать новые. Сообщение 3966 генерируется, и транзакция откатывается, когда транзакция-жертва пытается прочитать версии строк. Если сжатие хранилища версий успешно, в tempdb освобождается больше места. В противном случае в tempdb заканчивается место.
Внутренние объекты
Внутренние объекты создаются и уничтожаются для каждого оператора, с исключениями, описанными в таблице tempdb. Если вы замечаете, что выделяется огромное количество пространства tempdb, вы должны определить, какой сеанс или задача потребляют пространство, и затем, возможно, принять корректирующие меры.
SQL Server предоставляет два DMV: sys.dm_db_session_space_usage и sys.dm_db_task_space_usage, для отслеживания пространства tempdb, выделенного сеансам и задачам соответственно. Хотя задачи выполняются в контексте сеансов, пространство, используемое задачами, учитывается в сеансах только после завершения задач.
Вы можете использовать следующий запрос, чтобы найти сеансы, которые выделяют больше всего внутренних объектов. Обратите внимание, что этот запрос включает только задачи, которые были завершены в сеансах.
select
session_id,
internal_objects_alloc_page_count,
internal_objects_dealloc_page_count
from sys.dm_db_session_space_usage
order by internal_objects_alloc_page_count DESC
Вы можете использовать следующий запрос, чтобы найти сеансы пользователей, которые выделяют больше всего внутренних объектов, включая активные задачи.
Пример вывода:SELECT t1.session_id, (t1.internal_objects_alloc_page_count + task_alloc) as allocated, (t1.internal_objects_dealloc_page_count + task_dealloc) as deallocated from sys.dm_db_session_space_usage as t1, (select session_id, sum(internal_objects_alloc_page_count) as task_alloc, sum (internal_objects_dealloc_page_count) as task_dealloc from sys.dm_db_task_space_usage group by session_id) as t2 where t1.session_id = t2.session_id and t1.session_id >50 order by allocated DESC
session_id allocated deallocated
---------- -------------------- --------------------
52 5120 5136
51 16 0После того как вы изолировали задачу или задачи, генерирующие много выделений внутренних объектов, вы можете узнать, какой это оператор Transact-SQL и его план запроса для более детального анализа.
select
t1.session_id,
t1.request_id,
t1.task_alloc,
t1.task_dealloc,
t2.sql_handle,
t2.statement_start_offset,
t2.statement_end_offset,
t2.plan_handle
from (Select session_id,
request_id,
sum(internal_objects_alloc_page_count) as task_alloc,
sum (internal_objects_dealloc_page_count) as task_dealloc
from sys.dm_db_task_space_usage
group by session_id, request_id) as t1,
sys.dm_exec_requests as t2
where t1.session_id = t2.session_id and
(t1.request_id = t2.request_id)
order by t1.task_alloc DESC
Пример вывода:
session_id request_id task_alloc task_dealloc
---------------------------------------------------------
52 0 1024 1024
sql_handle statement_start_offset
-----------------------------------------------------------------------
0x02000000D490961BDD2A8BE3B0FB81ED67655EFEEB360172 356
statement_end_offset plan_handle
---------------------------------
-1 0x06000500D490961BA8C19503000000000000000000000000Вы можете использовать столбцы sql_handle и plan_handle, чтобы получить SQL-оператор и план запроса следующим образом:
select text from sys.dm_exec_sql_text(@sql_handle)
select * from sys.dm_exec_query_plan(@plan_handle)
Обратите внимание, что может случиться так, что план запроса не будет в кэше, когда вы захотите к нему обратиться. Чтобы гарантировать доступность планов запросов, часто опрашивайте кэш планов и сохраняйте результаты, желательно в таблице, чтобы их можно было запросить позже.
При перезапуске SQL Server размер tempdb возвращается к первоначально настроенному размеру и растёт в соответствии с требованиями. Это может привести к фрагментации tempdb и создать накладные расходы, включая блокировку выделения новых экстентов во время автоматического роста базы данных и увеличения размера tempdb. Это может повлиять на производительность вашей рабочей нагрузки. Мы рекомендуем предварительно выделить tempdb до соответствующего размера.
Чрезмерные операции DDL и выделения
Два источника конкуренции в tempdb могут привести к следующим ситуациям.
Создание и удаление большого количества временных таблиц и табличных переменных может вызвать конкуренцию за метаданные. В SQL Server локальные временные таблицы и табличные переменные кэшируются для минимизации конкуренции за метаданные. Однако должны выполняться следующие условия; в противном случае временные объекты не кэшируются:
- Не создаются именованные ограничения.
- Операторы DDL, влияющие на таблицу, не выполняются после создания временной таблицы, такие как
CREATE INDEXилиCREATE STATISTICS. - Временный объект не создаётся с использованием динамического SQL, например:
sp_executesql N'create table #t(a int)'. - Временный объект создаётся внутри другого объекта, такого как хранимая процедура, триггер или пользовательская функция; или временный объект является возвращаемой таблицей пользовательской табличной функции.
Обычно большинство временных/рабочих таблиц являются кучами; поэтому вставка, удаление или удаление могут вызывать сильную конкуренцию за страницы свободного пространства (Page Free Space, PFS). Если большинство этих таблиц меньше 64 КБ и используют смешанные экстенты для выделения или освобождения, это может создавать высокую нагрузку на страницы Shared Global Allocation Map (SGAM). SQL Server кэширует одну страницу данных и одну страницу IAM для локальных временных таблиц, чтобы минимизировать конкуренцию за выделение. Кэширование рабочих таблиц улучшено. Когда план выполнения запроса кэшируется, рабочие таблицы, необходимые для плана, не удаляются при многократных выполнениях плана, а просто усекаются. Кроме того, сохраняются первые девять страниц рабочей таблицы.
Поскольку страницы SGAM и PFS встречаются через фиксированные интервалы в файлах данных, легко найти их описание ресурса. Например, 2:1:1 представляет первую страницу PFS в tempdb (database-id = 2, file-id = 1, page-id = 1), а 2:1:3 представляет первую страницу SGAM. Страницы SGAM встречаются после каждых 511 232 страниц, а каждая страница PFS — после каждых 8 088 страниц. Вы можете использовать это, чтобы найти все остальные страницы PFS и SGAM во всех файлах tempdb. Всякий раз, когда задача ожидает захвата защёлки на этих страницах, это отображается в sys.dm_os_waiting_tasks. Поскольку ожидания защёлок являются временными, вы должны часто запрашивать эту таблицу (примерно раз в 10 секунд) и собирать эти данные для последующего анализа. Например, вы можете использовать следующий запрос, чтобы загрузить все задачи, ожидающие на страницах tempdb, в таблицу waiting_tasks в базе данных анализа.
-- получить текущую временную метку
declare @now datetime
select @now = getdate()
-- вставить данные в таблицу для последующего анализа
insert into analysis..waiting_tasks
select
session_id,
wait_duration_ms,
resource_description,
@now
from sys.dm_os_waiting_tasks
where wait_type like 'PAGE%LATCH_%' and
resource_description like '2:%'
Всякий раз, когда вы видите задачи, ожидающие захвата защёлок на страницах tempdb, вы можете проанализировать, связано ли это со страницами PFS или SGAM. Если это так, это указывает на конкуренцию за выделение в tempdb. Если вы видите конкуренцию на других страницах tempdb и можете определить, что страница принадлежит системной таблице, это указывает на конкуренцию из-за чрезмерных операций DDL.
Вы также можете отслеживать следующие счётчики производительности на предмет любого необычного увеличения активности по выделению/освобождению временных объектов:
- SQL Server: Access Methods\Workfiles Created /Sec
- SQL Server: Access Methods\Worktables Created /Sec
- SQL Server: Access Methods\Mixed Page Allocations /Sec
- SQL Server: General Statistics\Temp Tables Created /Sec
- SQL Server: General Statistics\Temp Tables for destruction
Решение
Если конкуренция в tempdb вызвана чрезмерными операциями DDL, посмотрите на своё приложение и посмотрите, можете ли вы минимизировать операции DDL. Вы можете попробовать следующие предложения:
- Начиная с SQL Server 2005, временные объекты кэшируются при условиях, описанных ранее. Однако если вы всё ещё сталкиваетесь с значительной конкуренцией DDL, вам необходимо выяснить, какие временные объекты не кэшируются и где они возникают. Если такие объекты возникают внутри цикла или хранимой процедуры, рассмотрите возможность их перемещения из цикла или хранимой процедуры.
- Посмотрите на планы запросов, чтобы увидеть, создают ли некоторые планы много временных объектов, буферов, сортировок или рабочих таблиц. Возможно, вам потребуется исключить некоторые временные объекты. Например, создание индекса на столбце, используемом в
ORDER BY, может исключить сортировку.
Если конкуренция связана с конкуренцией на страницах SGAM и PFS, вы можете смягчить её, попробовав следующее:
- Увеличьте файлы данных
tempdbна одинаковую величину, чтобы распределить рабочую нагрузку по всем дискам и файлам. В идеале должно быть столько файлов, сколько ЦП (с учётом привязки). - Используйте TF-1118, чтобы исключить выделения из смешанных экстентов.
Медленно выполняющиеся запросы
Медленно выполняющиеся или длительные запросы могут способствовать чрезмерному потреблению ресурсов. Они могут быть следствием заблокированных запросов.
Чрезмерное потребление ресурсов не ограничивается ресурсами ЦП, но также может включать пропускную способность ввода-вывода и памяти. Даже если запросы SQL Server разработаны так, чтобы избегать полных сканирований таблиц, ограничивая результирующий набор разумным условием WHERE, они могут не работать так, как ожидается, если нет подходящего индекса, поддерживающего этот конкретный запрос. Также условия WHERE могут динамически создаваться приложениями в зависимости от ввода пользователя. Учитывая это, существующие индексы не могут покрыть все возможные случаи ограничений. Чрезмерное потребление ЦП, ввода-вывода и памяти операторами Transact-SQL рассматривается ранее в этом документе.
В дополнение к отсутствующим индексам могут быть индексы, которые не используются. Поскольку все индексы должны обслуживаться, это не влияет на производительность запроса, но влияет на запросы DML.
Запросы также могут выполняться медленно из-за состояний ожидания логических блокировок и системных ресурсов, блокирующих запрос. Причиной блокировки может быть плохое проектирование приложения, плохие планы запросов, отсутствие полезных индексов и экземпляр SQL Server, неправильно настроенный для рабочей нагрузки.
Этот раздел фокусируется на двух причинах медленного выполнения запросов — блокировке и проблемах с индексами.
Блокировка
Блокировка — это в первую очередь ожидания логических блокировок, таких как ожидание получения монопольной блокировки (exclusive, X) на ресурс или ожидания, возникающие из-за примитивов синхронизации более низкого уровня, таких как защёлки.
Ожидания логических блокировок возникают, когда запрос на получение несовместимой блокировки на уже заблокированном ресурсе поступает. Хотя это необходимо для обеспечения согласованности данных на основе уровня изоляции транзакций, на котором выполняется конкретный оператор Transact-SQL, это создаёт у конечного пользователя впечатление, что SQL Server работает медленно. Когда запрос заблокирован, он не потребляет системные ресурсы, поэтому вы обнаружите, что он выполняется дольше, но потребление ресурсов низкое.
Ожидания примитивов синхронизации более низкого уровня могут возникнуть, если ваша система не настроена для обработки рабочей нагрузки.
Распространённые сценарии блокировок и ожиданий:
- Выявление блокирующего процесса.
- Выявление длительных блокировок.
- Блокировка по объекту.
- Проблемы с защёлками страниц.
- Общее влияние блокировки на производительность с использованием ожиданий SQL Server.
Сеанс SQL Server переводится в состояние ожидания, если системные ресурсы (или блокировки) в данный момент недоступны для обслуживания запроса. Другими словами, ожидание возникает, если у ресурса есть очередь ожидающих запросов. DMV могут предоставлять информацию для любых сеансов, ожидающих ресурсы.
SQL Server предоставляет подробную и согласованную информацию об ожиданиях, сообщая примерно о 125 типах ожиданий. DMV, предоставляющие эту информацию, варьируются от sys.dm_os_wait_statistics для общих и совокупных ожиданий SQL Server до sys.dm_os_waiting_tasks, которые разбивают ожидания по сеансам. Следующее DMV предоставляет подробности об очереди ожидания задач, ожидающих некоторый ресурс. Это одновременное представление всех очередей ожидания в системе. Например, вы можете узнать подробности о заблокированном сеансе 56, выполнив следующий запрос.
select * from sys.dm_os_waiting_tasks where session_id=56
Результат показывает, что сеанс 56 заблокирован сеансом 53 и что сеанс 56 ожидает блокировку в течение 1 103 500 миллисекунд.
Чтобы найти сеансы, которым были предоставлены блокировки или которые ожидают блокировки, вы можете использовать DMV sys.dm_tran_locks. Каждая строка представляет текущий активный запрос к диспетчеру блокировок, который либо был предоставлен, либо ожидает предоставления, так как запрос блокируется запросом, который уже был предоставлен. Для обычных блокировок предоставленный запрос указывает, что блокировка была предоставлена на ресурс запрашивающему. Ожидающий запрос указывает, что запрос ещё не был предоставлен. Например, следующий запрос и его вывод показывают, что сеанс 56 заблокирован на ресурсе 1:143:3, который удерживается в режиме X сеансом 53.
select
request_session_id as spid,
resource_type as rt,
resource_database_id as rdb,
(case resource_type
WHEN 'OBJECT' then object_name(resource_associated_entity_id)
WHEN 'DATABASE' then ' '
ELSE (select object_name(object_id)
from sys.partitions
where hobt_id=resource_associated_entity_id)
END) as objname,
resource_description as rd,
request_mode as rm,
request_status as rs
from sys.dm_tran_locks
Гранулярность блокировки и эскалация блокировок
Одним из ключей к пониманию блокировки являются уровни изоляции транзакций и гранулярность блокировки. Уровни изоляции транзакций управляют длительностью блокировок в режиме S, но не влияют на длительность монопольных блокировок (X), которые удерживаются в течение всей транзакции на всех уровнях изоляции. Гранулярность блокировки определяет, будет ли блокировка получена на уровне строки, страницы или таблицы. Очевидно, что чем выше гранулярность блокировки, тем ниже конкурентность. Например, если транзакция берёт монопольную блокировку на таблицу для изменения одной строки в таблице, она блокирует другие транзакции, которые хотят читать или изменять совершенно другие строки. С другой стороны, преимущество более высокой гранулярности блокировки заключается в том, что SQL Server не нужно получать так много блокировок, что экономит память и затраты ЦП на получение и освобождение блокировок. Гранулярность блокировки определяется SQL Server с помощью эвристической модели, которая работает довольно хорошо, но могут быть случаи, когда она ведёт себя не так, как ожидается. В таких случаях пользователь может использовать sp_tableoption или DDL ALTER INDEX для управления гранулярностью блокировки на объекте или с помощью подсказок блокировки.
Существует ещё один интересный нюанс с блокировками. SQL Server может повысить блокировку на таблицу, если во время выполнения определит, что количество блокировок, полученных на объект в операторе, превысило порог. Эскалация блокировки запускается, когда выполняется любое из следующих условий:
- Количество блокировок, удерживаемых (в отличие от тех, которые были получены и затем освобождены; например, когда одна или несколько строк читаются на уровне изоляции read committed) оператором на индексе или куче в рамках оператора, превышает порог, который по умолчанию установлен примерно в 5000. Эти блокировки включают также блокировки намерения. Обратите внимание, что эскалация блокировки не сработает, если:
- Транзакция получает 2500 блокировок на двух индексах или кучах в одном операторе.
- Транзакция получает 2500 блокировок на некластерном индексе и 2500 блокировок на соответствующей базовой таблице в одном операторе.
- Одна и та же куча или индекс упоминается более одного раза в операторе; блокировки на каждом экземпляре этих объектов подсчитываются отдельно. Например, в случае самосоединения на таблице
t1, если каждый экземпляр имеет 3000 блокировок в операторе, эскалация блокировки не запускается.
- Память, занимаемая ресурсами блокировок, превышает 40% от не-AWE (32-разрядная) или обычной (64-разрядной) включённой памяти, если параметр конфигурации блокировок установлен в 0, значение по умолчанию. В этом случае память блокировок выделяется динамически по мере необходимости.
Когда эскалация блокировки запускается, SQL Server пытается повысить блокировку до уровня таблицы, но попытка может не удаться, если есть конфликтующие блокировки. Например, если блокировки SH должны быть повышены до уровня таблицы, а на одной или нескольких строках или страницах целевой таблицы есть одновременные монопольные блокировки (X), попытка эскалации не удастся. Однако SQL Server периодически, примерно каждые 1250 новых блокировок, полученных владельцем блокировки (то есть транзакцией, инициирующей блокировку), пытается повысить блокировку. Если эскалация блокировки успешна, SQL Server освобождает блокировки более низкой гранулярности и связанную с ними память блокировок на индексе или куче. Поскольку во время эскалации блокировки не может быть конфликтующего доступа, успешная эскалация блокировки может потенциально привести к блокировке будущего одновременного доступа к индексу или куче транзакциями в конфликтующих режимах блокировки. Поэтому эскалация блокировки не всегда является хорошей идеей для всех приложений.
Отключение эскалации блокировок
SQL Server предоставляет следующие флаги трассировки для отключения эскалации блокировок:
- TraceFlag-1211: Отключает эскалацию блокировок на текущем пороге (5000) для каждого индекса или кучи на оператор. Если этот флаг трассировки активен, блокировки никогда не повышаются. Этот флаг трассировки также инструктирует SQL Server игнорировать память, полученную диспетчером блокировок, до максимальной статически выделенной памяти блокировок или 60% не-AWE (32-разрядная) или обычной (64-разрядной) динамически выделенной памяти. В этот момент генерируется ошибка нехватки памяти для блокировок. Это может быть потенциально опасно, так как приложение может исчерпать память SQL Server, получая большое количество блокировок. Это, в худшем случае, может остановить сервер или ухудшить его производительность до неприемлемого уровня. По этим причинам необходимо соблюдать осторожность при использовании этого флага трассировки.
- TraceFlag-1224: Этот флаг трассировки аналогичен флагу 1211 с одним ключевым отличием. Он включает эскалацию блокировок, если диспетчер блокировок получает 40% статически выделенной памяти или 40% не-AWE (32-разрядная) или обычной (64-разрядной) динамически выделенной памяти. Кроме того, если эта память не может быть выделена, потому что другие компоненты занимают больше памяти, эскалация блокировки может быть запущена раньше. SQL Server генерирует ошибку нехватки памяти, когда память, выделенная диспетчеру блокировок, превышает статически выделенную память или 60% не-AWE (32-разрядная) или обычной (64-разрядной) динамически выделенной памяти.
Если оба флага трассировки (1211 и 1224) установлены одновременно, преимущество имеет флаг трассировки 1211. Вы можете использовать команду DBCC TRACESTATUS (-1), чтобы узнать статус всех включённых флагов трассировки в SQL Server. Ограничение этих флагов трассировки заключается в том, что они являются грубыми по гранулярности (то есть на уровне экземпляра SQL Server). SQL Server решает эту проблему, предоставляя опцию на уровне таблицы для включения или отключения эскалации блокировок. Во-вторых, SQL Server обеспечивает эскалацию блокировок на уровне секций, так что эскалация блокировок из-за действий DML в одной секции не влияет на доступ к другим секциям.
Выявление длительных блокировок
Как упоминалось ранее, блокировки в SQL Server являются обычным явлением и являются проявлением логических блокировок, необходимых для поддержания согласованности транзакций. Однако когда ожидание блокировок превышает порог, это может повлиять на время отклика. Для выявления длительных блокировок вы можете использовать параметр конфигурации BlockedProcessThreshold, чтобы установить порог блокировки, настраиваемый пользователем для всего сервера. Порог определяет продолжительность в секундах. Любая блокировка, превышающая этот порог, вызовет событие, которое может быть захвачено трассировкой SQL.
Например, порог заблокированного процесса в 200 секунд можно настроить в SQL Server Management Studio следующим образом:
- Выполните
sp_configure 'blocked process threshold', 200. - Выполните
RECONFIGURE WITH OVERRIDE. - После установки порога заблокированного процесса захватите событие трассировки. События трассировки блокировок, превышающих настроенный пользователем порог, можно захватить с помощью SQL Trace или SQL Server Profiler.
- Если вы используете SQL Trace, используйте
sp_trace_seteventиevent_id=137. - Если вы используете SQL Server Profiler, выберите класс событий Blocked process report (в группе Errors and Warnings).
Блокировка по объекту с помощью sys.dm_db_index_operational_stats
DMV sys.dm_db_index_operational_stats предоставляет полную статистику использования индексов, включая блокировки. С точки зрения блокировок, оно предоставляет подробный учёт статистики блокировок для каждой таблицы, индекса и секции. Примеры этого включают историю обращений, блокировок (row_lock_count), блокировок (process_virtual_memory_low) и ожиданий (row_lock_wait_in_ms) для данного индекса или таблицы.
Тип информации, доступной через это DMV, включает:
- Количество удерживаемых блокировок, например, строк или страниц.
- Количество блокировок или ожиданий, например, строк или страниц.
- Продолжительность блокировок или ожиданий, например, строк или страниц.
- Количество ожиданий защёлок страниц. Продолжительность
page_latch_wait: Это связано с конкуренцией за конкретную страницу, например, для вставок с возрастающими ключами. В таких случаях «горячей точкой» является последняя страница, поэтому несколько писателей одной и той же последней страницы одновременно пытаются получить монопольную защёлку страницы. Это будет проявляться как ожиданияPagelatch. - Продолжительность
page_io_latch_wait: Защёлка ввода-вывода возникает, когда пользователь запрашивает страницу, которой нет в буферном пуле. Медленная или перегруженная подсистема ввода-вывода иногда может испытывать высокие ожиданияPageIOlatch, которые на самом деле являются проблемами ввода-вывода. Эти проблемы могут усугубляться сбросами кэша или отсутствующими индексами.
Общее влияние блокировки на производительность с использованием ожиданий
SQL Server предоставляет более 100 дополнительных типов ожиданий для отслеживания производительности приложений. Всякий раз, когда пользовательское подключение ожидает, SQL Server накапливает время ожидания. Например, приложение запрашивает ресурсы, такие как ввод-вывод, блокировки или память, и может ожидать, пока ресурс станет доступным. Эта информация об ожиданиях суммируется и классифицируется по всем подключениям, так что для данной рабочей нагрузки можно получить профиль производительности. Таким образом, типы ожиданий SQL Server идентифицируют и классифицируют ожидания пользователя (или потока) с точки зрения рабочей нагрузки приложения или пользователя.
Этот запрос выводит 10 самых больших ожиданий в SQL Server. Эти ожидания являются накопительными, но вы можете сбросить их с помощью DBCC SQLPERF ([sys.dm_os_wait_stats], clear).
select top 10 *
from sys.dm_os_wait_stats
order by wait_time_ms desc
Несколько ключевых моментов:
- Некоторые ожидания являются нормальными, например, ожидания фоновых потоков, таких как ленивый писатель.
- Некоторые сеансы долго ждали получения блокировки
SH. - Signal wait — это время между тем, как рабочему процессу был предоставлен доступ к ресурсу, и тем, как он получает время на ЦП. Длительное сигнальное ожидание может указывать на высокую конкуренцию за ЦП.
Статистика ожиданий на уровне сеанса
Для выявления статистики ожиданий на уровне сеанса или оператора, что было невозможно в предыдущих версиях SQL Server, расширенные события (Extended Events) являются идеальным инструментом. Они масштабируемы и способны отслеживать статистику ожиданий во время выполнения.
Этот пример сеанса Extended Events отслеживает все ожидания, включая как внутренние, так и внешние для SQL Server, для сеанса с id = 54.
-- sqlserver.session_id — это ID целевого сеанса, который вы хотите отслеживать. Я использую 54 в примере. Замените его соответствующим образом.
-- убедитесь, что папка C:\xevent существует
create event session session_waits on server
add event sqlos.wait_info
(action (sqlserver.sql_text, sqlserver.plan_handle, sqlserver.tsql_stack)
WHERE sqlserver.session_id=54 and duration>0)
, add event sqlos.wait_info_external
(action (sqlserver.sql_text, sqlserver.plan_handle, sqlserver.tsql_stack)
WHERE sqlserver.session_id=54 and duration>0)
add target package0.asynchronous_file_target
(SET filename=N'C:\xevent\wait_stats.xel', metadatafile=N'C:\xevent\wait_stats.xem');
alter event session session_waits on server state = start;
go
-- дождитесь завершения отслеживаемой рабочей нагрузки в целевом сеансе (54 в этом примере).
-- Чтобы прочитать результаты из выходного файла, выполните следующие запросы.
alter event session session_waits on server state = stop
drop event session session_waits on server
select
CONVERT(xml, event_data).value('(/event/data/text)[1]','nvarchar(50)') as 'wait_type',
CONVERT(xml, event_data).value('(/event/data/value)[3]','int') as 'duration',
CONVERT(xml, event_data).value('(/event/data/value)[6]','int') as 'signal_duration'
into #eventdata
from sys.fn_xe_file_target_read_file
(N'C:\xevent\wait_stats*.xel', N'C:\xevent\wait_stats*.xem', null, null)
-- сохранить во временную таблицу #eventdata
select wait_type, SUM(duration) as 'total_duration', SUM(signal_duration) as 'total_signal_duration'
from #eventdata
group by wait_type
drop table #eventdata
go
Мониторинг использования индексов
Ещё один аспект производительности запросов связан с запросами DML и запросами, которые удаляют, вставляют и изменяют данные. Чем больше индексов определено на конкретной таблице, тем больше ресурсов требуется для изменения данных. В сочетании с блокировками, удерживаемыми на время транзакций, более длительные операции изменения могут ухудшить конкурентность. Поэтому может быть очень важно знать, какие индексы используются приложением с течением времени. Затем вы можете выяснить, есть ли в схеме базы данных много индексов, которые никогда не используются.
SQL Server предоставляет DMV sys.dm_db_index_usage_stats, которое показывает, какие индексы используются и используются ли они пользовательским запросом или только системной операцией. При каждом выполнении запроса столбцы в этом представлении увеличиваются в соответствии с планом запроса, используемым для выполнения этого запроса. Данные собираются, пока SQL Server работает. Данные в этом DMV хранятся только в памяти и не сохраняются. Поэтому при остановке экземпляра SQL Server данные теряются. Вы можете периодически опрашивать эту таблицу и сохранять данные для последующего анализа.
Операции с индексами классифицируются на пользовательские и системные. Пользовательские относятся к операциям SELECT, INSERT, DELETE и UPDATE. Системные операции — это команды, такие как DBCC, команды DDL или обновление статистики. Столбцы для каждой категории операторов различаются на:
- Операции поиска (seek) по индексу (
user_seeksилиsystem_seeks). - Операции поиска по ключу (lookup) по индексу (
user_lookupsилиsystem_lookups). - Операции сканирования (scan) по индексу (
user_scansилиsystem_scans). - Операции обновления (update) по индексу (
user_updatesилиsystem_updates).
Для каждого из этих обращений к индексу также отмечается время последнего обращения.
Сам индекс идентифицируется тремя столбцами: database_id, object_id и index_id. index_id=0 представляет кучу, index_id=1 представляет кластерный индекс, а index_id>1 представляет некластерный индекс.
В течение нескольких дней работы приложения с базой данных список индексов, к которым обращались, в sys.dm_db_index_usage_stats растёт.
Правила и определения для поиска, сканирования и поиска по ключу:
- Поиск (Seek): Указывает, сколько раз структура B-дерева использовалась для доступа к данным. Не имеет значения, использовалась ли структура B-дерева только для чтения нескольких страниц каждого уровня индекса для извлечения одной строки данных или для чтения половины страниц индекса для чтения гигабайт данных или миллионов строк из базовой таблицы. Поэтому можно ожидать, что большинство обращений к индексу будут накапливаться в этой категории.
- Сканирование (Scan): Указывает, сколько раз уровень данных таблицы использовался для извлечения без использования одного из B-деревьев индекса. Это характерно для таблиц, на которых не определено ни одного индекса. В случае таблиц с определёнными индексами это может произойти, когда индексы, определённые на таблице, бесполезны для запроса, выполняемого против этого оператора.
- Поиск по ключу (Lookup): Указывает, что кластерный индекс, определённый на таблице, использовался для поиска данных, которые были идентифицированы путём поиска по некластерному индексу, также определённому на этой таблице. Это описывает сценарий, известный как поиск по закладке (bookmark lookup) в SQL Server 2000 или более ранних версиях. Он представляет сценарий, когда некластерный индекс используется для доступа к таблице, и некластерный индекс не покрывает столбцы списка
SELECTи столбцы, указанные в условииWHERE. SQL Server увеличивает значение столбцаuser_seeksдля использованного некластерного индекса и столбцаuser_lookupsдля записи кластерного индекса. Этот счётчик может стать очень высоким, если на таблице определено несколько некластерных индексов. Если количество поисков по кластерному индексу таблицы довольно высоко, количество поисков по ключу также довольно высоко, и если количество поисков по одному конкретному некластерному индексу также очень высоко, вы должны рассмотреть возможность сделать некластерный индекс с высоким счётчиком кластерным индексом.
Следующий запрос DMV можно использовать для получения полезной информации об использовании индексов для всех объектов во всех базах данных.
select object_id, index_id, user_seeks, user_scans, user_lookups
from sys.dm_db_index_usage_stats
order by object_id, index_id
Наиболее интересными столбцами sys.dm_db_index_usage_stats для просмотра являются столбцы пользовательского типа, включая user_seeks и user_scans. Столбцы системного использования, такие как system_seeks, можно рассматривать как результат существования индекса. Если бы индекса не существовало, его не нужно было бы обновлять в статистике и не нужно было бы проверять на согласованность. Поэтому анализ должен фокусироваться на четырёх столбцах, которые указывают на использование динамическими операторами или пользовательским приложением.
Чтобы получить информацию об индексах конкретной таблицы, которые не использовались с момента последнего запуска SQL Server, этот запрос можно выполнить в контексте базы данных, которой принадлежит объект.
select i.name
from sys.indexes i
where i.object_id=object_id('<table_name>') and
i.index_id NOT IN (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id = <dbid> )
Все индексы, которые ещё не использовались, можно получить с помощью следующего оператора.
select object_name(object_id), i.name
from sys.indexes i
where i.index_id NOT IN (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id = <dbid> )
order by object_name(object_id) asc
Расширенные события (Extended Events)
SQL Server представляет расширенные события как общую систему обработки событий для сервера. По сути, расширенные события — это инфраструктура, поддерживающая генерацию и обработку событий для интересных точек выполнения. Генерация событий может выполняться синхронно или асинхронно. Выполнение генерации событий синхронно гарантирует отсутствие потери данных. Аналогично, обработка событий также может выполняться обоими способами. Асинхронная обработка событий обеспечивает масштабируемость и создаёт минимальную нагрузку на серверную систему. Расширенные события разработаны как основа, которую пользователи могут настраивать для мониторинга и захвата различных типов данных, включая данные о производительности. Это гибкий и мощный способ предоставления информации с низкой гранулярностью о серверной системе.
На высоком уровне расширенные события реализованы в пакетах, которые являются контейнерами объектов, таких как события, цели и действия. Несколько пакетов могут быть реализованы в одном модуле, который может быть исполняемым файлом или динамически подключаемой библиотекой (DLL).
События (Events) — представляют точки выполнения процесса, в данном случае SQL Server. События несут соответствующую информацию сами по себе, и они также могут быть вызваны для запуска действий.
Цели (Targets) — потребляют события и указывают, где находится вывод, например, файл, ring_buffer или корзина с агрегацией. Цели могут обрабатывать события синхронно или асинхронно.
Действия (Actions) — выполняются синхронно в связи с привязанными событиями. Они могут использоваться для выполнения определённых задач или просто для предоставления дополнительной информации, релевантной событиям.
Типы (Types) — определяют тип объектов пакета. Они основаны на длине и шаблоне собираемых данных.
Предикаты (Predicates) — логические правила, используемые для фильтрации захваченных событий. Они помогают уменьшить объём захваченных данных и настроить вывод для анализа.
Карты (Maps) — таблицы, которые сопоставляют внутренние значения объектов с удобочитаемыми описаниями.
Создание сеанса Extended Events
Используя пакеты Extended Events, вы можете создавать сеансы для размещения движка Extended Events и выполнения действий с различными целями. Например, следующий код создаёт сеанс, который отслеживает события автоматического роста журнала базы данных.
-- убедитесь, что папка C:\xevent существует
create event session log_growth on server
ADD EVENT sqlserver.databases_log_growth
(action (sqlserver.database_id))
add target package0.asynchronous_file_target
(SET filename=N'C:\xevent\log_growth.xel', metadatafile=N'C:\xevent\log_growth.xem');
В этом определении сеанса событие — database_log_growth, которое принадлежит пакету sqlserver. Действие — сбор идентификатора базы данных, который указывает, какая база данных испытала рост журнала. Цель — асинхронный файл log_growth.xel. Чтобы запустить этот сеанс, выполните следующее.
alter event session log_growth on server state = start;
После того как событие роста журнала произойдёт, вы можете остановить сеанс и получить вывод с помощью кода, аналогичного следующему.
alter event session log_growth on server state = stop
select *
from sys.fn_xe_file_target_read_file
(N'C:\xevent\log_growth*.xel', N'C:\xevent\log_growth*.xem', null, null)
Другие распространённые задачи для сеансов Extended Events
Для просмотра всех созданных сеансов используйте этот запрос.
select * from sys.server_event_sessions
Для просмотра всех активных (работающих) сеансов используйте этот запрос.
select * from sys.dm_xe_sessions
Чтобы удалить сеанс, используйте этот запрос.
drop event session <session_name> on server
Информация о пакетах и объектах
Как видно из предыдущего примера, для создания осмысленного сеанса Extended Events необходимо знать, какое событие, действие, цель, предикат и так далее добавить. SQL Server поставляется с предопределёнными пакетами с соответствующей информацией об объектах. Чтобы просмотреть все зарегистрированные пакеты, используйте следующий пример.
select * from sys.dm_xe_packages
Чтобы просмотреть все доступные события, действия, цели и другую информацию об объектах для пакетов, используйте следующий пример.
select p.name as package, xo.name as object_name, xo.description, xo.object_type
from sys.dm_xe_objects xo
join sys.dm_xe_packages p
on xo.package_guid = p.guid
Системный сеанс событий «system_health»
Кроме того, SQL Server включает сеанс Extended Events по умолчанию «system_health», который автоматически запускается при запуске службы SQL Server. Если вы выполните запрос к sys.server_event_sessions, вы сможете его увидеть.
select event_session_id, name, startup_state from sys.server_event_sessions
Для startup_state значение 1 указывает, что сеанс настроен на запуск при запуске службы SQL Server. Чтобы узнать, действительно ли сеанс работает, вы можете запросить sys.dm_xe_sessions.
select name, create_time from sys.dm_xe_sessions where name = 'system_health'
Чтобы узнать потребителя событий (то есть цель), вы можете выполнить следующий запрос.
SELECT target_name
FROM sys.dm_xe_sessions x INNER JOIN sys.dm_xe_session_targets xt
ON x.address = xt.event_session_address
WHERE x.name = 'system_health'
Сеанс system_health ничем не отличается от любого пользовательского сеанса, такого как сеанс log_growth, созданный ранее в этом документе. Поэтому вы можете остановить и/или удалить сеанс system_health, как и любой пользовательский сеанс. Если вам нужно воссоздать сеанс, вы можете использовать установленный скрипт из ..\mssql\install\u_tables.sql. Изучив определение сеанса из скрипта, вы можете увидеть, что он отслеживает:
- Сообщения об ошибках с уровнем серьёзности 20 или выше.
- События невыполняющего уступку планировщика (nonyielding scheduler).
- Взаимоблокировки (deadlocks).
- Некоторые ожидания, которые длятся более 15 секунд.
Сборщик данных (Data Collector) и хранилище управляющих данных (MDW)
SQL Server содержит инструмент мониторинга производительности, называемый сборщиком данных. Сборщик данных хранит данные в хранилище управляющих данных (MDW). Существует пять различных типов сборщиков: T-SQL Query, SQL Trace, Performance Counters и Query Activity. SQL Server предоставляет следующие системные определения сбора данных:
- Disk Usage — собирает информацию об использовании локального диска для всех баз данных экземпляра SQL Server. Эта информация может помочь определить использование пространства и требования к диску для планирования ёмкости.
- Server Activity — собирает информацию об использовании ресурсов на уровне экземпляра SQL Server, таких как ЦП, память и ввод-вывод. Эта информация может помочь отслеживать тенденции использования ресурсов в краткосрочной и долгосрочной перспективе и выявлять потенциальные узкие места ресурсов в системе. Её также можно использовать для планирования ёмкости ресурсов.
- Query Statistics — собирает статистику запросов на уровне отдельных операторов, включая текст запроса и планы запросов. Эта информация может помочь выявить запросы, потребляющие больше всего ресурсов, для настройки производительности.
Сборщик данных реализован как пакеты SQL Server Integration Services (SSIS). Эти пакеты можно настроить на выполнение вручную, непрерывно или по расписанию как задания агента SQL Server для периодического сбора и загрузки данных в центральную базу данных, называемую хранилищем управляющих данных (MDW). MDW — это просто база данных, предназначенная для хранения собранных данных для просмотра и составления отчётов.
Одна база данных MDW может служить центральным репозиторием для сборщиков данных, работающих на нескольких целевых экземплярах SQL Server. Сборщики данных настраиваются на каждом целевом сервере, и они собирают и загружают данные в базу данных MDW, которая может находиться на удалённом сервере. Между моментом захвата данных и моментом их загрузки сборщик данных может записывать временные данные в файлы кэша на целевом сервере. Наборы сбора обычно выполняются как задания агента SQL Server, поэтому метаданные о частоте сбора, элементах сбора и т. д. хранятся в базе данных msdb. Системные наборы сбора имеют предопределённые отчёты, доступ к которым осуществляется через SQL Server Management Studio и которые используются для визуализации собранных данных.
Вы можете изменить расписание наборов сбора, если они настроены как задания по расписанию. Вы также можете указать, как долго хранить собранные данные и где хранить кэшированные данные перед загрузкой. Например, по умолчанию набор сбора Server Activity собирает данные каждую минуту и загружает данные каждые 15 минут. В зависимости от временного окна вашей задачи вы можете уменьшить частоту сбора до 5 минут, что позволит захватывать только одну пятую данных (в основном счётчики производительности).
Определение системных наборов сбора не может быть изменено. Однако вы можете определить свои собственные наборы сбора и хранить эту информацию в MDW (таблицы будут созданы в схеме custom_snapshots) и определять свои собственные отчёты для этих данных.
Пользовательский сбор данных
Как упоминалось ранее, MDW можно использовать как основу для создания пользовательского сбора данных для мониторинга производительности. Мы рассмотрим пример ниже для мониторинга нескольких счётчиков производительности.
use msdb;
Begin Transaction
Begin Try
Declare @collection_set_id_1 int
Declare @collection_set_uid_2 uniqueidentifier
EXEC [dbo].[sp_syscollector_create_collection_set]
@name=N'Disk Performance and SQL CPU',
@collection_mode=1,
@description=N'Collects logical disk performance counters and SQL Process CPU',
@target=N'',
@logging_level=0,
@days_until_expiration=7,
@proxy_name=N'',
@schedule_name=N'CollectorSchedule_Every_5min',
@collection_set_id=@collection_set_id_1 OUTPUT,
@collection_set_uid=@collection_set_uid_2 OUTPUT
Select @collection_set_id_1, @collection_set_uid_2
Declare @collector_type_uid_3 uniqueidentifier
Select @collector_type_uid_3 = collector_type_uid From [dbo].[syscollector_collector_types] Where name = N'Performance Counters Collector Type';
Declare @collection_item_id_4 int
EXEC [dbo].[sp_syscollector_create_collection_item]
@name=N'Logical Disk Collection and SQL Server CPU',
@parameters=N'<ns:PerformanceCountersCollector xmlns:ns="DataCollectorType">
<PerformanceCounters Objects="LogicalDisk"
Counters="Avg. Disk Bytes/Read"
Instances="*" />
<PerformanceCounters Objects="LogicalDisk"
Counters="Avg. Disk Bytes/Write"
Instances="*" />
<PerformanceCounters Objects="LogicalDisk"
Counters="Avg. Disk sec/Read"
Instances="*" />
<PerformanceCounters Objects="LogicalDisk"
Counters="Avg. Disk sec/Write"
Instances="*" />
<PerformanceCounters Objects="LogicalDisk"
Counters="Disk Read Bytes/sec"
Instances="*" />
<PerformanceCounters Objects="LogicalDisk"
Counters="Disk Write Bytes/sec"
Instances="*" />
<PerformanceCounters Objects="Process"
Counters="% Privileged Time"
Instances="sqlservr" />
<PerformanceCounters Objects="Process"
Counters="% Processor Time"
Instances="sqlservr" />
</ns:PerformanceCountersCollector>',
@collection_item_id=@collection_item_id_4 OUTPUT,
@frequency=5,
@collection_set_id=@collection_set_id_1,
@collector_type_uid=@collector_type_uid_3
Select @collection_item_id_4
Commit Transaction;
End Try
Begin Catch
Rollback Transaction;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorNumber INT;
DECLARE @ErrorLine INT;
DECLARE @ErrorProcedure NVARCHAR(200);
SELECT @ErrorLine = ERROR_LINE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorNumber = ERROR_NUMBER(),
@ErrorMessage = ERROR_MESSAGE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
RAISERROR (14684, @ErrorSeverity, 1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);
End Catch;
GO
Затем запустите сбор данных и прочитайте вывод, используя запросы, аналогичные приведённым в документе.
Приложение A: Описание DBCC MEMORYSTATUS
Некоторые типы информации доступны в первую очередь через использование команды DBCC MEMORYSTATUS. Однако часть этой информации также доступна через использование динамических административных представлений (DMV).
Для получения дополнительной информации о команде DBCC MEMORYSTATUS в SQL Server см. статью базы знаний: Использование команды DBCC MEMORYSTATUS для мониторинга использования памяти в SQL Server.
Приложение B: Сбор данных MDW
Это приложение содержит пример скрипта, который создаёт пользовательский набор сбора данных.
use msdb;
Begin Transaction
Begin Try
Declare @collection_set_id_1 int
Declare @collection_set_uid_2 uniqueidentifier
EXEC [dbo].[sp_syscollector_create_collection_set]
@name=N'Disk Performance and SQL CPU',
@collection_mode=1,
@description=N'Collects logical disk performance counters and SQL Process CPU',
@target=N'',
@logging_level=0,
@days_until_expiration=7,
@proxy_name=N'',
@schedule_name=N'CollectorSchedule_Every_5min',
@collection_set_id=@collection_set_id_1 OUTPUT,
@collection_set_uid=@collection_set_uid_2 OUTPUT
Select @collection_set_id_1, @collection_set_uid_2
Declare @collector_type_uid_3 uniqueidentifier
Select @collector_type_uid_3 = collector_type_uid From [dbo].[syscollector_collector_types] Where name = N'Performance Counters Collector Type';
Declare @collection_item_id_4 int
EXEC [dbo].[sp_syscollector_create_collection_item]
@name=N'Logical Disk Collection and SQL Server CPU',
@parameters=N'<ns:PerformanceCountersCollector xmlns:ns="DataCollectorType">
<PerformanceCounters Objects="LogicalDisk"
Counters="Avg. Disk Bytes/Read"
Instances="*" />
<PerformanceCounters Objects="LogicalDisk"
Counters="Avg. Disk Bytes/Write"
Instances="*" />
<PerformanceCounters Objects="LogicalDisk"
Counters="Avg. Disk sec/Read"
Instances="*" />
<PerformanceCounters Objects="LogicalDisk"
Counters="Avg. Disk sec/Write"
Instances="*" />
<PerformanceCounters Objects="LogicalDisk"
Counters="Disk Read Bytes/sec"
Instances="*" />
<PerformanceCounters Objects="LogicalDisk"
Counters="Disk Write Bytes/sec"
Instances="*" />
<PerformanceCounters Objects="Process"
Counters="% Privileged Time"
Instances="sqlservr" />
<PerformanceCounters Objects="Process"
Counters="% Processor Time"
Instances="sqlservr" />
</ns:PerformanceCountersCollector>',
@collection_item_id=@collection_item_id_4 OUTPUT,
@frequency=5,
@collection_set_id=@collection_set_id_1,
@collector_type_uid=@collector_type_uid_3
Select @collection_item_id_4
Commit Transaction;
End Try
Begin Catch
Rollback Transaction;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorNumber INT;
DECLARE @ErrorLine INT;
DECLARE @ErrorProcedure NVARCHAR(200);
SELECT @ErrorLine = ERROR_LINE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorNumber = ERROR_NUMBER(),
@ErrorMessage = ERROR_MESSAGE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
RAISERROR (14684, @ErrorSeverity, 1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);
End Catch;
GO
Комментариев нет:
Отправить комментарий