Vvmebel.com

Новости с мира ПК
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Оптимизация sql сервера

Microsoft SQL Server. Работа с оптимизатором запросов (часть 1)

На недавнем мероприятии SQL Saturday 178, мне задали вопрос, можно ли сделать так, чтобы оптимизатор не прекращал оптимизацию, когда посчитает что уже нашел хороший план или наступит таймаут, а исследовал все альтернативы. Я ответил, что документированных средств нет, либо я о таких не знаю. И это действительно так, однако, возможно есть какие-то недокументированные флаги трассировки, которыми можно влиять на этот процесс. Я решил провести небольшое исследование и в этой заметке расскажу о его результатах.

Забегая вперед, сразу сообщу об итогах исследования, для тех кому не важны технические подробности, а важны выводы. Оказывается, действительно можно сделать так, чтобы оптимизатор продолжал поиски «до упора», но вероятность, что он действительно найдет гораздо более удачный план невелика. Это логично, иначе, если бы оптимизатор очень часто «недооптимизировал» запросы, прекращая поиски раньше положенного, то следовало бы поменять механизм определения того самого момента, когда считается, что искать план дальше не имеет смысла. Между тем, оптимизатор довольно неплохо справляется со своей задачей, а когда не справляется, причина очень часто кроется не в самом оптимизаторе, а в том с чем ему приходится работать (неактуальная статистика, плохо написанный код и т.д.). Хотя, ради справедливости, стоит сказать, что бывают случаи, когда причина в самом оптимизаторе.

Далее, я расскажу о том, как заставить оптимизатор отбросить ограничения и продолжать оптимизацию до конца.

Теория

Основные понятия

Дерево логических операторов — дерево объектов, в котором каждым узлом является логический оператор, соответствующий той или иной операции в запросе. Это дерево — результат того, во что был превращен запрос после того как он попал на сервер и прошел некоторые стадии упрощения и преобразования. Короче говоря, это то с чем начинает работать оптимизатор.

Transformation Rule — правило преобразования. Это объект который содержит в себе методы по преобразованию одних логических операторов в другие логические (или физические) операторы.

Optimization Task — дословно, задача оптимизации, это операция предпринимаемая оптимизатором в процессе поиска плана. Это может быть например, применение правила преобразования к узлу дерева операторов.

Memo — структура в памяти сервера, которая используется для хранения и анализа получаемых в результате преобразований деревьев операторов.

Group — группа эквивалентности, часть структуры Memo, в которой хранятся эквивалентные выражения (операторы), например — Group 1: (A join B) , (B join A).

Group Expression — выражение в группе эквивалентности, например — Group 1: (A join B) , (B join A). (A join B) — одно из выражений группы Group 1.

Timeout — определенное количество задач оптимизации (Optimization Task), которое отводит себе оптимизатор перед тем, как начинает оптимизировать запрос («Я угадаю эту мелодию с 5 нот»!), т.е. некий бюджет на количество преобразований. По мере выполнения преобразований оптимизатор смотрит на этот счетчик, и как только потратил всё отведенное количество — прекращает оптимизацию и выдает тот план, который у него есть на данный момент. При этом, если в SSMS посмотреть на полученный план, выбрать корневой оператор SELECT и посмотреть свойства, то можно увидеть «Reason For Early Termination: Time Out».

Good Enough Plan — достаточно хороший план, это еще одно условие при котором оптимизация прекращается. Происходит это в том случае, если запас преобразований еще есть, но найденный на данном этапе план уже удовлетворяет внутреннему порогу оптимизатора. Это условие, также можно увидеть в свойствах плана в SSMS — «Reason For Early Termination: Good Enough Plan Found».

Алгоритм генерации альтернатив

Допустим есть запрос:

Соответствующее ему дерево логических операторов выглядит следующим образом:

Дерево копируется в начальное Memo (Copy In):

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

Optimize Group

  • На входе: группа, верхняя граница, требуемые свойства
  • Сохранение лучшего плана в memo

Explore Group

  • Итеративное исследование каждого выражения

Explore Expression

  • Применение правил
  • Генерирование альтернативных выражений
  • Работа с memo, чтобы избежать повторов (e.g. JoinCommute)
  • Битовая карта pattern memory определяет уже примененные правила

Apply Rule

  • Предшественник – Потомок
  • Привязка предшественников к правилам
  • Применение правила
  • Сохранение в memo (в том числе новых групп)
  • Запуск следующего задания в зависимости от типа потомка
  • Логический – Explore Expression
  • Физический – Optimize Inputs

Optimize Inputs

  • Подсчет наилучшего плана
  • Форсирование физических свойств
  • Отброс неэффективных ветвей

Все начинается с того, что на вход алгоритму, поступает корневая группа, на вход также поступают требуемые физические свойства, верхняя граница, выше которой (если стоимость превысит порог) не имеет смысла искать план. Поскольку план должен содержать физические операторы, то группа должна содержать физические операторы. Рекурсивно вызывается оптимизация дочерних групп.
В процессе оптимизации каждой из групп происходит исследование группы (Explore Group), если группа содержит несколько выражений, то исследование группы заключается в итеративном вызове (Explore Expression).
На этапе Explore Expression определяются правила, которые могут быть применены к этому выражению, ведется учет повторов, чтобы избежать одних и тех же преобразований, идет применение правил (Apply Rule). Важный момент: правила применяются не все подряд. А только те, что соответствуют некоторому шаблону для конкретного выражения группы (оператора). Правило применяется к выражению (предшественник) и генерирует новое выражение (потомок).
В зависимости от потомка, запускается либо задача Explore Expression, если потомок логический оператор. Либо Optimize Inputs, если потомок физический оператор. Либо Optimize Group, если применение правила породило потомка, который не входит ни в какую существующую группу, а образует новую.
Этап Optimize Inputs в свою очередь обеспечивает стратегию отброса (Discarding) неэффективных ветвей плана (Cost Based Pruning Factor), подсчет наилучшего плана и форсирование физических свойств (например, если у нас есть Merge join, который требует отсортированного входа, то будет форсирована операция сортировки).

В результате всего этого, в Memo сохраняются физические операторы, реализующие наиболее эффективный план.

После этого наиболее эффективный план копируется из Memo (Copy Out):

На протяжении всего этого процесса активно применяются две следующие концепции: Timeout, Cost Based Pruning Factor, Discarding.
Именно они влияют на то, как будет выбран план, и именно на них можно повлиять флагами трассировки.

Практика

Перейдем от теории к практике.

Отключаем Timeout

Первый флаг трассировки: 8780. Он позволяет «отключить» Timeout.

Для демонстрации, я буду использовать ту же простую БД opt, что использую в примерах почти всегда.
Для удобства приведу еще раз скрипт ее генерации:

Теперь, давайте выполним следующий умозрительный запрос, для того, чтобы получить Timeout.

Примечание: Для просмотра информации я использую недокументированный флаг трассировки 8675, который выводит информацию по стадиям оптимизации. Я уже неоднократно использовал этот флаг в рассказах про оптимизатор. Например, тут Оптимизатор (ч.3): Optimization: Full Optimization: Search 0.

Оптимизация запросов SQL Server

Здесь приведены жестко заданные предположения, используемые оптимизатором в рамках метода «оптимизация для неизвестного». По крайней мере, в этом случае вы знаете, как оптимизатор угадывает неизвестные величины. Оптимальная настройка запросов в значительной мере начинается с умения объяснить оценки числа строк, особенно неточные. В моих примерах запросы будут направлены к таблице Sales. SalesOrderDetail в тестовой базе данных AdventureWorks2014. Если вы захотите выполнить примеры из этой статьи, но не располагаете установленной базой данных, ее можно загрузить. Кроме того, стоит убедиться, что база данных настроена на уровень совместимости 120, при котором SQL Server по умолчанию использует новое средство СЕ (2014). Сделать это можно с помощью следующего программного кода:

— Убедитесь, что уровень совместимости базы данных >= 120
для использования по умолчанию нового средства СЕ

Оценки оптимизации для неиз­вестных я разделяю на следующие группы операторов:

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

Оптимизация для неизвестного для операторов: >, >=, , >=, = . Число строк в таблице 121 317, поэтому СЕ фильтра будет 0,3 * 121317 = 36395,1. Насколько эта величина близка к действительному числу строк в типичном варианте использования, решать вам; однако оптимизатор делает именно такое предположение.
Это первый раздел, в котором демонстрируется метод оптимизации для неизвестного, поэтому начнем с перечисления различных случаев использования данного метода наряду с готовыми к применению примерами. Метод оптимизации для неизвестного используется в следующих случаях.

Оптимальная настройка запросов начинается с умения объяснить оценки числа строк, особенно неточные

1. При работе с локальными переменными
В отличие от значений параметров, которые можно прослушивать, значения переменных обычно прослушать нельзя. Исключение будет описано немного позже. Причина проста: начальная единица оптимизации — весь пакет, а не только инструкция запроса. Объявление и задание значений переменным выполняются в оптимизируемом пакете. Точка, в которой запрос оптимизируется, предшествует заданию любой переменной, поэтому значения переменных нельзя прослушивать. В результате оптимизатору приходится использовать метод оптимизации для неизвестного.

Чтобы сравнить метод оптимизации для неизвестного с естественным методом оптимизации для известного, рассмотрим следующий запрос, имеющий предикат фильтра с оператором >= и известную константу в качестве входных данных:

План выполнения для этого запроса показан на рисунке 1. Классический инструмент, используемый оптимизатором, чтобы получить СЕ для фильтра, — гистограмма. Если ее не существовало для столбца OrderQty перед выполнением этого запроса и вы не отключили автоматическое создание статистики в базе данных, то SQL Server создает ее при выполнении запроса. Вы можете использовать запрос, приведенный в листинге, чтобы получить автоматически созданное имя статистики.

Выполнив этот программный код после предшествующего запроса, я получил имя статистики _ WA_Sys_00000004_44 СА3770. Запомните полученное вами имя. Затем используйте следующий код для просмотра гистограммы после замены имени статистики на полученное вами:

Таблица Последние несколько шагов гистограммы

Последние несколько шагов в полученной гистограмме показаны в таблице 1.
Мы ясно видим, что СЕ, показанная на рисунке 1, основана на последних трех шагах гистограммы. Оценка довольно точная: 4,00639 при действительном значении 4. В отличие от приведенного выше примера, в следующем запросе используется локальная переменная, что вынуждает оптимизатор применить метод оптимизации для неизвестного:

Читать еще:  Нагревается видеокарта ноутбука

План для этого запроса показан на рисунке 2.

Как было предсказано, это оценка 30% количества элементов ввода. Примечательно, что из-за неточности оценки оптимизатор выбрал неоптимальную стратегию статистической обработки. Здесь использован алгоритм статистической обработки Hash Match вместо сортировки и алгоритма Stream Aggregate. Это лишь одно из многих возможных последствий неточных оценок. Существует исключение, при котором оптимизатор может прослушивать переменные: событие перекомпиляции происходит на уровне инструкций. Дело в том, что по определению перекомпиляция на уровне инструкций происходит после того, как выполнено задание всех переменных. Автоматическая перекомпиляция всегда происходит на уровне инструкций. Так было все время после появления SQL Server 2005 и до написания данной статьи. Я тестирую программный код на SQL Server 2016. Для ручной перекомпиляции на уровне инструкций нужно добавить указание запроса RECOMPILE с использованием оператора OPTION:

Этот запрос формирует такой же план, как показанный на рисунке 1, где оценка является точной. Обратите внимание, что если указать параметр WITH RECOMPILE на уровне процедуры, то прослушивание не будет включено — это достигается только указанием в запросе OPTION (RECOMPILE). Перейдем к следующему случаю использования метода оптимизации для неизвестного.

2. При использовании параметров, но отключенном автоматическом прослушивании параметров с указанием OPTIMIZE FOR UNKNOWN или OPTIMIZE FOR (©parameter UNKNOWN) или с флагом трассировки 4136
Обычно значения параметров доступны для прослушивания, так как они задаются при выполнении процедуры или функции, прежде чем пакет передается оптимизатору. Однако можно применить метод оптимизации для неизвестного с двумя указаниями запроса. Если нужно отменить прослушивание параметров для всех входов, используйте указание OPTIMIZE FOR UNKNOWN. Если требуется отменить прослушивание для определенного параметра, используйте указание OPTIMIZE FOR (©parameter UNKNOWN). Также можно использовать флаг трассировки 4136 для отключения прослушивания параметров при разных детализациях: запроса, сеанса или глобальной детализации. Обратите внимание, что при использовании хранимой процедуры, скомпилированной в собственном коде, оптимизация для неизвестного выбирается по умолчанию. В качестве примера следующий программный код создает хранимую процедуру и отключает прослушивание параметров в запросе с использованием указания

Используйте следующий программный код для тестирования хранимой процедуры: EXEC dbo.Prod @Qty = 40; Я получил такой же план запроса, как показанный на рисунке 2, с оценкой 30%.

Рассмотрим еще один сценарий, в котором используется метод оптимизации для неизвестного.
3. Статистика недоступна
Возьмем случай, когда гистограмма для фильтруемого столбца отсутствует и вы не позволяете SQL Server создать гистограмму, отключив автоматическое создание статистики на уровне базы данных и не формируя индекс для столбца. Используйте следующий программный код, чтобы организовать такую среду для нашей демонстрации, заменив имя статистики именем, полученным в результате выполнения запроса, приведенного в листинге:

Затем выполните код, в котором используется константа в фильтре:

Выполнив этот запрос ранее, вы получили план, показанный на рисунке 1, с точной оценкой. Но на этот раз у оптимизатора не было гистограммы, поэтому используется метод оптимизации для неизвестного и создается план, показанный на рисунке 2, с оценкой 30%.

Выполните следующий программный код, чтобы повторно активировать автоматическое создание статистики в базе данных:

Вы можете повторно запустить запрос и убедиться, что вы получаете план, как на рисунке I.

Оценки оптимизации для неизвестных для операторов BETWEEN и LIKE
При использовании предиката BETWEEN жестко заданные предположения зависят от сценария и применяемой СЕ. В старых СЕ во всех случаях используется оценка 9%. Это демонстрирует следующий запрос. Флаг трассировки 9481 запроса используется, чтобы применить старую СЕ.

План для этого запроса показан на рисунке 3. Оценка 0,09 * 121317 = 10918,5.

В новой СЕ задействованы различные оценки при применении констант и отсутствующей гистограмме и при использовании переменных или параметров с отключенным прослушиванием. В первом случае используется оценка 9%; во втором — оценка 16,4317%. Ниже приводится пример использования констант. Обязательно удалите любую существующую статистику для столбца и отключите автоматическое создание статистики, как показано выше, перед выполнением теста и включите после его завершения.

Я получил такой же план, как на рисунке 3, с оценкой 9%. Ниже приводится пример, демонстрирующий применение переменных (то же поведение, что и при использовании параметров с отключенным прослушиванием):

Я получил план, приведенный на рисунке 4, показывающий оценку 16,4317%.
При использовании предиката LIKE во всех сценариях оптимизации для неизвестного как в старых, так и в новых СЕ применяется оценка 9%. Ниже приведен примере использованием локальных переменных:

Вы увидите ту же оценку 9%, как показано на рисунке 3, хотя в данном случае действительное число строк 12, а ранее было 3

Оценки оптимизации для неизвестных для оператора =
При использовании оператора = различают три основных случая:
• уникальный столбец;
• неуникальный столбец и доступная плотность;
• неуникальный столбец и недоступная плотность.
Если фильтруемый столбец уникален (для него определены уникальный индекс, ограничение PRIMARY KEY или UNIQUE), то оптимизатору известно, что совпадений не может быть более одного, поэтому оценка равна 1. Ниже приводится запрос, демонстрирующий этот случай:

На рисунке 5 показан план для этого запроса с оценкой 1. Если столбец не уникален и оптимизатору доступна информация о плотности (средний процент для отдельного значения), то оценка основывается на плотности. Если не отключено автоматическое создание статистики или для столбца сформирован индекс, то эта информация будет доступна оптимизатору. Чтобы продемонстрировать это, сначала убедитесь, что автоматическое создание статистики включено, выполнив следующий программный код:

Таблица 2 Оценки метода оптимизации для неизвестного для операторов

Очевидно, что метод, основанный на плотности, в целом хорош, когда входные данные, к которым чаще всего направляются запросы, имеют количество элементов, близкое к среднему. Очевидно, что наш случай в последнем примере иной. Величина 1 появляется чаще среднего, поэтому действительное число выше оценки. При использовании неуникального столбца и недоступной плотности в старой и новой СЕ применяются

различные методы. В старой СЕ используется оценка С^0,75 (степень три четвертых), где С — входное число элементов, а в новой используется оценка С^0,5 (квадратный корень).
Чтобы продемонстрировать это, сначала удалите любую статистику для столбца OrderQty и отключите автоматическое создание статистики, как было показано ранее:

Используйте следующий программный код для тестирования старого метода СЕ:

План для этого запроса показан на рисунке 7.

Оценка 6500,42 — результат вычисления 121317^З/4.
Используйте следующий программный код для тестирования нового метода СЕ:

План для этого запроса показан на рисунке 8.

Оценка 348,306 получена в результате вычисления 121317^0,5.

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

Таким образом, метод оптимизации для неизвестного используется оптимизатором SQL Server, чтобы создать оценку СЕ при неизвестных входных данных или недостатке статистики.

Иногда у оптимизатора нет иного выбора, кроме использования этого метода просто из-за нехватки информации. Иногда данный метод применяется принудительно, если метод оптимизации для известного не подходит. Итак, метод оптимизации для неизвестного применяется в следующих случаях:
1. Использование переменных (кроме случаев использования RECOMPILE на уровне инструкций).
2. Использование параметров с указанием OPTIMIZE FOR UNKNOWN или OPTIMIZE FOR (©parameter UNKNOWN) или флагом трассировки 4136 (всегда при использовании хранимой процедуры, скомпилированной в собственном коде).
3. Статистика недоступна.
В таблице 2 приведена сводка оценок оптимизации для неизвестного, используемых для различных групп операторов.

Оптимизация производительности ЦП SQL Server

Устранение проблем производительности системы базы данных может быть непреодолимой задачей. Важно знать, в чем заключается проблема, но еще более важно понимать механизм реагирования системы на определенный запрос. На производительность ЦП сервера базы данных может повлиять

ряд факторов: компиляция и перекомпиляция инструкций SQL, отсутствующие индексы, многопоточные операции, проблемы производительности дисков, узкие места со стороны памяти, процедуры обслуживания, действия по извлечению, преобразованию и загрузке и другие факторы. Использование ЦП само по себе не является проблемой — процессор предназначен для выполнения работы. Ключом к оптимальному использованию процессора является обеспечение того, что процессор выполняет необходимую работу, а не тратит время из-за плохо оптимизированного кода или оборудования с низкой производительностью.

Два пути, ведущие в одно место

Если рассматривать в целом, существует два пути определения проблем производительности процессора. Первый заключается в анализе производительности оборудования системы, что помогает определить места для поиска проблемы при переходе ко второму пути, оценке эффективности выполнения сервером запросов. Второй путь обычно более эффективен для определения проблем производительности SQL Server™. Однако если причины проблем производительности запросов точно не известны, всегда следует начинать с анализа производительности системы В конечном счете, обычно вы оказываетесь на обоих путях. Давайте рассмотрим некоторые основные моменты, чтобы можно было перейти к рассмотрению обоих путей.

Технология гиперпоточности

На технологии гиперпоточности стоит остановиться из-за того, как она влияет на SQL Server. Технология гиперпоточности фактически предоставляет операционной системе для одного физического процессора два логических процессора. По сути, технология гиперпоточности арендует время физических процессоров для полного использования возможностей каждого процессора. На веб-узле Intel (intel.com/technology/platform-technology/hyper-threading/index.htm) представлено гораздо более подробное описание работы технологии гиперпоточности.

В системах SQL Server DBMS фактически обрабатывает собственные чрезвычайно эффективные очереди и потоки для операционной системы, поэтому в системах с уже существующей высокой загрузкой процессоров технология гиперпоточности только еще больше перегружает физические ЦП. Когда SQL Server осуществляет постановку в очередь нескольких запросов для работы с несколькими планировщиками, операционной системе приходится переключать контекст потоков команд для обеспечения соответствия выполняемым запросам, даже если два логических процессора принадлежат одному физическому процессору. Если показатель «Контекстных переключений/сек» превышает 5000 для одного физического процессора, следует серьезно рассмотреть вопрос об отключении гиперпоточности в системе и повторном тестировании производительности.

Только в очень редких случаях приложения с высокой загрузкой процессора в SQL Server могут эффективно использовать гиперпоточность. Перед реализацией изменений в рабочих системах необходимо всегда проверять приложения в SQL Server с включенной и выключенной гиперпоточностью.

Читать еще:  Как отключить драйвер видеокарты

Основные моменты

Производительность мощного двухъядерного процессора всегда будет превосходить производительность ОЗУ компьютера, скорость которого в свою очередь будет больше скорости подключенного запоминающего устройства. Пропускная способность хорошего процессора приблизительно в шесть раз превосходит пропускную способность лучшей современной памяти DDR2 и приблизительно в два раза превосходит пропускную способность лучшей памяти DDR3. Пропускная способность обычной памяти более чем в 10 раз превышает пропускную способность самых быстрых оптоволоконных приводов. В свою очередь, жесткие диски могут выполнять только ограниченное число операций ввода/вывода в секунду (IOPS), это значение всецело зависит от количества операций поиска в секунду, которое может выполнять привод. Справедливости ради надо сказать, что для удовлетворения всех потребностей в хранении систем баз данных предприятий обычно используется несколько устройств хранения. Сегодня в большинстве установок используются сети хранилищ данных (SAN) на серверах баз данных предприятий или группы RAID большего размера, которые могут минимизировать или устранить проблему процессора дискового ввода-вывода. Важно помнить, что независимо от особенностей установки системы узкие места со стороны дисков и памяти могут повлиять на производительность процессоров.

Из-за различий в скорости ввода-вывода получение данных с диска намного дороже, чем получение данных из памяти. Размер страницы данных в SQL Server – 8 КБ. Блок в SQL Server состоит из восьми страниц размером 8 КБ каждая и соответственно имеет размер 64 КБ. Это важно понимать, поскольку когда SQL Server запрашивает определенную страницу данных с диска, выполняется получение всего блока, в который входит страница данных, а не только этой определенной страницы. Существует ряд причин, делающих это наиболее эффективным для SQL Server, но в данной статье я не будут рассматривать этот вопрос подробно. Получение страницы данных, уже кэшированной из буферного пула, с максимальной производительностью выполняется в течение половины миллисекунды; получение одного блока с диска в оптимальной среде занимает от 2 до 4 миллисекунд. Обычно чтение хорошо работающей исправной дисковой подсистемы занимает от 4 до 10 мс. Получение страницы данных из памяти обычно выполняется от 4 до 20 раз быстрее, чем получение страницы данных с диска.

Когда SQL Server запрашивает страницу данных, он выполняет поиск в буферном кэше в памяти, а затем в дисковой подсистеме. Найдя страницу данных в буферном пуле, процессор получит данные и выполнит необходимые операции. Это называется ошибкой страницы ОЗУ. Ошибки страниц ОЗУ идеальны для SQL Server, поскольку для использования данных, полученных в качестве части запроса, они должны находиться в буферном кэше. Страница данных, не найденная в буферном кэше, должна быть получена из дисковой подсистемы сервера. Необходимость получения операционной системой страницы данных диска называется ошибкой страницы физической памяти.

При согласовании производительности памяти, дисков и ЦП объективно оценить все компоненты помогает общий знаменатель: пропускная способность. Говоря ненаучно, пропускная способность – это максимальный объем данных, который может передавать конечный канал.

Путь 1. Производительность системы

Существует всего несколько способов определения наличия узких мест ЦП на сервере и не очень много возможных причин высокой загрузки процессора. Некоторые из этих проблем можно отследить с помощью системного монитора или сходного средства наблюдения за системой, другие же проблемы можно отследить с помощью профилировщика SQL или сходных средств. Другой способ заключается в использовании команд SQL в Query Analyzer или SQL Server Management Studio (SSMS).

При оценке производительности системы я использую подход «Начните широко, затем анализируйте подробно». Очевидно, исследование проблемных мест возможно только после их определения. После оценки общего использования ЦП с помощью специальной программы, например системного монитора, ее можно использовать для просмотра нескольких очень простых и понятных счетчиков производительности.

Одним из самых известных счетчиков производительности является счетчик «% загруженности процессора»; во время работы системного монитора этот счетчик выделяется после открытия окна «Add Counter» (Добавление счетчика). Счетчик «% загруженности процессора» отображает время, в течение которого процессоры остаются занятыми при выполнении операций. Как правило, загруженность процессоров считается высокой, когда это значение составляет 80 или более процентов в течение большей части времени работы с максимальной загрузкой. Иногда значение может внезапно повышаться до 100 процентов, даже если сервер работает с загрузкой менее 80 процентов, но это не является неполадкой.

Другим счетчиком, который необходимо использовать, является «Длина очереди процессора», который находится в объекте «Система» системного монитора. Счетчик «Длина очереди процессора» показывает количество потоков, ожидающих выполнения на процессоре. Управление работой SQL Server осуществляется с помощью планировщиков в механизме СУБД, где сервер помещает в очередь и обрабатывает собственные запросы. Поскольку работа SQL Server управляет им самим, он использует только один поток ЦП для каждого логического процессора. Это означает, что в очереди процессора системы, предназначенной для SQL Server, должно находиться минимальное количество потоков. Обычно количество потоков на выделенном сервере SQL Server в пять раз меньше количества физических процессоров, но я считаю, что если количество потоков в два раза превышает количество физических процессоров, это уже является проблемой. На серверах, где кроме СУБД используются и другие приложения, этот счетчик необходимо использовать вместе со счетчиками производительности «% загруженности процессора» и «Контекстных переключений/сек» (переключения контекста будут рассмотрены позже) для определения необходимости перемещения СУБД или приложений на другой сервер.

При возникновении очереди процессора и высокой загрузке ЦП я использую счетчики «Compilations/sec» (Компиляций/с) и «Re-Compilations/sec» (Повторных компиляций/с) объекта производительности «SQL Server: статистика SQL» (см. рис. 1 ). Компиляция и повторная компиляция планов запросов влияет на загрузку ЦП системы. Значения повторных компиляций должны быть около нуля, но необходимо проверить потоки системы, чтобы определить обычное поведение сервера и допустимое количество компиляций. Не всегда удается избежать повторных компиляций, но можно оптимизировать запросы и хранимые процедуры, чтобы свести к минимуму количество повторных компиляций и использований планов запросов. Сравните эти значения с фактическими поступающими в систему инструкциями SQL с помощью счетчика «Пакетных запросов/с», который также находится в объекте «SQL Server: статистика SQL». Если компиляции и повторные компиляции составляют значительный процент поступающих в систему запросов пакетов, это место необходимо проверить. Иногда разработчики SQL могут не понимать, как и почему их код может влиять на эти типы проблем системных ресурсов. Далее в этой статье я приведу ссылки, которые помогут свести к минимуму действия такого типа.


Рис. 1 Выбор счетчиков для отслеживания

В системном мониторе найдите счетчик производительности «Контекстных переключений/сек» (см. рис. 2).

Рис. 2 Используемые счетчики производительности

Регламентные операции на уровне субд для MS SQL Server, Оптимизация работы

Одной из часто встречающихся причин не оптимальной работы системы является неправильное или несвоевременное выполнение регламентных операций на уровне СУБД. Особенно важно выполнять эти регламентные процедуры в крупных информационных системах, которые работают под значительной нагрузкой и обслуживают одновременно большое количество пользователей. Специфика таких систем в том, что обычных действий, выполняемых СУБД автоматически (на основании настроек) оказывает недостаточно для эффективной работы.

Если в работающей системе наблюдаются какие-либо симптомы проблем с производительностью, следует проверить, что в системе правильно настроены и регулярно выполняются все рекомендуемые регламентные операции на уровне СУБД.

Выполнение регламентных процедур должно быть автоматизировано. Для автоматизации этих операций рекомендуется использовать встроенное средства MS SQL Server: Maintenance Plan. Существуют так же другие способы автоматизации выполнения этих процедур. В настоящей статье для каждой регламентной процедуры дан пример ее настройки при помощи Maintenance Plan для MS SQL Server 2005.

Для MS SQL Server рекомендуется выполнять следующие регламентные операции:

Обновление статистикОчистка процедурного КЭШаДефрагментация индексовРеиндексация таблиц базы данных

Рекомендуется регулярно контролировать своевременность и правильность выполнения данных регламентных процедур.

Обновление статистик

MS SQL Server строит план запроса на основании статистической информации о распределении значений в индексах и таблицах. Статистическая информация собирается на основании части (образца) данных и автоматически обновляется при изменении этих данных. Иногда этого оказывается недостаточно для того, что MS SQL Server стабильно строил наиболее оптимальный план выполнения всех запросов.

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

Для того, чтобы гарантировать максимально правильную работу оптимизатора MS SQL Server рекомендуется регулярно обновлять статистики базы данных MS SQL.

Для обновления статистик по всем таблицам базы данных необходимо выполнить следующий SQL запрос:

Обновление статистик не приводит к блокировке таблиц, и не будет мешать работе других пользователей. Статистика может обновляться настолько часто, насколько это необходимо. Следует учитывать, что нагрузка на сервер СУБД во время обновления статистик возрастет, что может негативно сказаться на общей производительности системы.

Оптимальная частота обновления статистик зависит от величины и характера нагрузки на систему и определяется экспериментальным путем. Рекомендуется обновлять статистики не реже одного раза в день.

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

Настройка автоматического обновления статистик (MS SQL 2005)

Запустите MS SQL Server Management Studio и подключитесь к серверу СУБД. Откройте папку Management и создайте новый план обслуживания:

Создайте субплан (Add Sublan) и назовите его «Обновление статистик». Добавьте в него задачу Update Statistics Task из панели задач:

Настройте расписание обновления статистик. Рекомендуется обновлять статистики не реже одного раза в день. При необходимости частота обновления статистик может быть увеличена.

Настройте параметры задачи. Для этого следует два раза кликнуть на задачу в правом нижнем углу окна. В появившейся форме укажите имя базу данных (или несколько баз данных) для которых будет выполняться обновление статистик. Кроме этого вы можете указать для каких таблиц обновлять статистики (если точно неизвестно, какие таблицы требуется указать, то устанавливайте значение All).

Читать еще:  Как установить новую видеокарту

Обновление статистик необходимо проводить с включенной опцией Full Scan.

Сохраните созданный план. При наступлении указанного в расписании срока обновление статистик будет запущено автоматически.

Очистка процедурного КЭШа

Оптимизатор MS SQL Server кэширует планы запросов для их повторного выполнения. Это делается для того, чтобы экономить время, затрачиваемое на компиляцию запроса в том случае, если такой же запрос уже выполнялся и его план известен.

Возможна ситуация, при которой MS SQL Server, ориентируясь на устаревшую статистическую информацию, построит неоптимальный план запроса. Этот план будет сохранен в процедурном КЭШе и использован при повторном вызове такого же запроса. Если Вы обновили статистику, но не очистили процедурный кэш, то SQL Server может выбрать старый (неоптимальный) план запроса из КЭШа вместо того, чтобы построить новый (более оптимальный) план.

Таким образом, рекомендуется всегда после обновления статистик очищать содержимое процедурного КЭШа.

Для очистки процедурного КЭШа MS SQL Server необходимо выполнить следующий SQL запрос:

Этот запрос следует выполнять непосредственно после обновления статистики. Соответственно, частота его выполнения должна совпадать с частотой обновления статистики.

Настройка очистки процедурного КЭШа

для (MS SQL 2005)

Поскольку процедурный КЭШ необходимо очищать при каждом обновлении статистики, данную операцию рекомендуется добавить в уже созданный субплан «Обновление статистик». Для этого следует открыть субплан и добавить в его схему задачу Execute T-SQL Statement Task. Затем следует соединить задачу Update Statistics Task стрелочкой с новой задачей.

В тексте созданной задачи Execute T-SQL Statement Task следует указать запрос «DBCC FREEPROCCACHE»:

Дефрагментация индексов

При интенсивной работе с таблицами базы данных возникает эффект фрагментации индексов, который может привести к снижению эффективности работы запросов.

Рекомендуется регулярное выполнение дефрагментации индексов. Для дефрагментации всех индексов всех таблиц базы данных необходимо использовать следующий SQL запрос (предварительно подставив имя базы):

Дефрагментация индексов не блокирует таблицы, и не будет мешать работе других пользователей, однако создает дополнительную нагрузку на SQL Server. Оптимальная частота выполнения данной регламентной процедуры должна подбираться в соответствии с нагрузкой на систему и эффектом, получаемым от дефрагментации. Рекомендуется выполнять дефрагментацию индексов не реже одного раза в день.

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

Настройка дефрагментации индексов (MS SQL 2005)

В ранее созданном плане обслуживания создайте новый субплан с именем «Дефрагментация индексов». Добавьте в него задачу Reorganize Index Task:

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

Настройте задачу, указав базу данных (или несколько баз данных) и выбрав необходимые таблицы. Если точно неизвестно, какие таблицы следует указать, то устанавливайте значение All.

Реиндексация таблиц включает полное перестроение индексов таблиц базы данных, что приводит к существенной оптимизации их работы. Рекомендуется выполнять регулярную переиндексацию таблиц базы данных. Для реиндексации всех таблиц базы данных необходимо выполнить следующий SQL запрос:

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

После выполнения реиндексации нет необходимости делать дефрагментацию индексов.

В ранее созданном плане обслуживания создайте новый субплан с именем «Дефрагментация индексов». Добавьте в него задачу Rebuild Index Task:

Задайте расписание выполнения для задачи реиндексирования таблиц. Рекомендуется выполнять задачу во время минимальной нагрузки на систему, не реже одного раза в неделю.

Настройте задачу, указав базу данных (или несколько баз данных) и выбрав необходимые таблицы. Если точно неизвестно, какие таблицы следует указать, то устанавливайте значение All.

Реиндексация таблиц базы данных

Необходимо осуществлять регулярный контроль выполнения регламентных процедур на уровне СУБД. Ниже приведен пример контроля выполнения плана обслуживания для MS SQL Server 2005.

Откройте созданный вами план обслуживания и выберите из контекстного меню пункт «View History»:

Откроется окно с протоколом выполнения всех заданных регламентных процедур.

Успешно выполненные задачи и задачи, выполненные с ошибками, будут помечены соответствующими иконками. Для задач, выполненных с ошибками, доступна подробная информация об ошибке.

Советы по оптимизации SQL Server tempdb для нового сервера?

Я планирую новую установку SQL Server 2005 на новой машине, которую я должен заказать. Я знаю это настройка tempdb очень важно для общего производительность экземпляра SQL Server.

Я читал, что лучше всего создавать столько файлов tempdb, сколько у вас есть CPU (или ядер?). Это верно? Есть ли другие рекомендации, например, для настройки жесткого диска / RAID, я должен обратить внимание чтобы?

5 ответов

Если бы мне пришлось выбрать одного человека, которому я доверяю за советом по настройке сервера, это был бы Денни черри. Он написал статью, объясняющую это.

вот что я исследовал сам из различных источников.

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

конфигурация физического диска

tempdb должен находиться на его собственные выделенные физические диски. Это позволяет разделить транзакции ввода-вывода из оставшихся томов на SQL Сервер.

чтобы переместить tempdb на новый диск, используйте ALTER DATABASE . Это ключевая команда T-SQL для выполнения этой операции. Microsoft предлагает хороший пример в электронной документации по SQL Server 2005. Имя статьи-ALTER DATABASE (Transact-SQL), а конкретный раздел —‘G. перемещение базы данных tempdb в новое место.’

база данных tempdb является очень высокой записи. Таким образом, массив RAID 5 не является подходящим местом для него. Вы должны поставить tempdb на RAID 0 или RAID 10 массив поскольку они оптимизированы для приложений с высокой записью. Если вы можете позволить себе дополнительные массивы RAID 0 или RAID 10 для каждого физического файла базы данных для tempdb, вы получите повышенную производительность.

файлы базы данных

вы должны есть!—12—>один физический файл на ядро процессора на сервере. Итак, если у вас есть двухчиповый двухъядерный сервер, у вас должно быть четыре физических файла базы данных для базы данных tempdb. При добавлении базы данных файлы, важно настроить файлы на тот же начальный размер и с теми же настройками роста. Таким образом, SQL Server будет записывать данные в файлы как можно более равномерно.

размер файла базы данных

размер базы данных tempdb может повлиять на производительность системы. Например, если размер, определенный для базы данных tempdb, слишком мал, часть нагрузки на обработку системы может быть С автоматическим увеличением базы данных tempdb размер, необходимый для поддержки рабочей нагрузки при каждом перезапуске экземпляра SQL Server. Вы можете избежать этих накладных расходов, увеличив размеры данных tempdb и файла журнала.

определение подходящего размера базы данных tempdb в рабочей среде зависит от многих факторов, включая существующую рабочую нагрузку и используемые функции SQL Server. Корпорация Майкрософт рекомендует проанализировать существующую рабочую нагрузку, выполнив следующие задачи в SQL Server тест среда:

  1. установить автозапуск для tempdb (в тестовой среде!).
  2. выполнение отдельных запросов или файлов трассировки рабочей нагрузки и мониторинг использования пространства tempdb.
  3. выполнение операций обслуживания индекса, таких как перестроение индексов и мониторинг пространства tempdb.
  4. используйте значения использования пространства из предыдущих шагов для прогнозирования общего использования рабочей нагрузки; настройте это значение для прогнозируемого параллельного действие, а затем установить размер tempdb соответственно.

рекомендации по минимальному размеру для tempdb следующие:

параметры базы данных

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

отказ от ответственности: настройки должны быть изменены осторожно. В зависимости от вида нагрузки, которую вы размещаете на tempdb, изменение настроек может негативно повлиять на производительность системы.

для достижения оптимальной производительности tempdb следуйте инструкциям и рекомендациям, приведенным в оптимизация производительности базы данных tempdb.

как контролировать использование tempdb?

под управлением из дискового пространства в tempdb можете вызывают значительные сбои в SQL Рабочая среда сервера и может препятствовать приложениям, которые работают от завершения операций.

можно использовать sys.dm_db_file_space_usage динамическое представление управления для мониторинга дискового пространства, используемого этими функциями в файлах tempdb. Кроме того, чтобы отслеживать действие выделения или освобождения страниц в базе данных tempdb на уровне сеанса или задачи, можно использовать sys.dm_db_session_space_usage и sys.dm_db_task_space_usage динамические административные представления.

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

ссылки:

из того, что я прочитал, лучше всего поместить tempDB на собственный физический диск (или массив). Для максимальной скорости вы можете ввести массив raid 0. Однако в случае сбоя одного из дисков сервер БД выйдет из строя. Если вы хотите больше избыточности, поместите ее в массив raid 10. Реальный ключ, похоже, отделяет его от других баз данных, потому что в TempDB может быть так много активности.

насколько далеко вы идете, зависит от других баз данных на сервере и делают ли они это много работы с временными таблицами и т. д.

Я ничего не слышал о нескольких файлах, основанных на количестве процессоров или ядер.

Смотрите также эту публикацию RAID журнала транзакций SQL Server который содержит обсуждение макета сервера, которое затрагивает некоторые проблемы вокруг tempdb.

Я бы рекомендовал создать файлы данных между 1/2 и 1/4 * количество ядер. вы должны разместить эти файлы на самый быстрый диск

для получения дополнительной информации вы можете посмотреть здесь: конфигурация SQL Server tempdb

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector