Улучшение производительности SQL Server

 

Nicolae Mercioiu, Victor Vladucu

 

Аннотация ‒ С развитием клиент-серверных технологий и многоуровневых архитектур систем все чаще обсуждается вопрос эффективности. Недостаток знаний о методах оптимизации и инструментов предлагаемых СУБД не позволяет разработчикам приложений и администраторам баз данных, которые используют технологии Microsoft, проектировать и обслуживать высоко производительные системы. В этой статье мы рассмотрим задачи, которые следует выполнить для повышения производительности экземпляров SQL Server, также мы рассмотрим техники, используемые для оптимизации запросов. Мы объясним и покажем новые возможности оптимизации предлагаемые SQL Server 2008.

Ключевые слова: Запрос, Оптимизация, SQL Server.

 

1 Введение

 

Во многих ситуациях критерии эффективности и производительности являются последними критериями, которые учитываются при проектировании и разработке приложений, использующих базу данных. Эти критерии ставятся важными только после запуска и начала использования системы. Иногда выясняется, что приложение не может предоставить информацию из базы данных за приемлемое время или вообще не может предоставить её, из-за превышения установленного времени ожидания. Причиной может быть архитектура приложения, но чаще всего проблема в СУБД, которая не может вернуть данные достаточно быстро, из-за отсутствия индексов, неудачной структуры запросов и/или базы данных, высокой фрагментации, использования неточной статистики, неправильного использования курсоров или планов выполнения.

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

 

2 Улучшение производительности экземпляров SQL Server – Задачи

Основные задачи, которые необходимо решить, чтобы увеличить производительность SQL Server:

-         Разработать эффективную структуру данных

-         Оптимизировать индексы, хранимые процедуры и транзакции

-         Анализировать планы выполнения запросов и избегать их перекомпилирования

-         Следить за доступом к данным

-         Оптимизировать запросы

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

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

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

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

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

таблицы, которые часто опрашиваются и не содержат колонок типа

изображение или битовое поле.

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

Хранимые процедуры должны содержать параметр SET NOCOUNT ON. Он предотвращает отправку сообщений о количестве затронутых записей каждой операцией в хранимой процедуре.

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

 

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

Очень важно следить за статистикой (является ли она актуальной) и использовать планировщик для запросов выполняющихся очень долго, также для отслеживания использования ресурсов.

 

4 SQL Server 2008 – Новые возможности оптимизации

Некоторые приложения выполняют запросы один раз за сессию. Тот факт, что SQL Server сохраняет каждый план выполнения для повторного использования ведет к большим затратам оперативной памяти в подобных случаях, что приводит к уменьшению эффективности экземпляра. Чтобы решить эту проблему, Microsoft представили опцию оптимизации для таких запросов ‒ optimize for ad hoc workload ‒ присутствует во всех версиях SQL Server 2008. Как только эта опция активирована, СУБД при первом выполнении запроса будет сохранять лишь малую часть плана выполнения, а при повторном выполнении в течении сессии ‒ удалит её и создаст полный план выполнения. Для примера выполним запросы:

 

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

GO

USE AdventureWorks

GO

SELECT * FROM Person.Contact

GO

SELECT usecounts, cacheobjtype, objtype,

text

FROM sys.dm_exec_cached_plans

CROSS APPLY

sys.dm_exec_sql_text(plan_handle)

WHERE usecounts > 0 AND

text like '%SELECT * FROM Person.

Contact%'

ORDER BY usecounts DESC;

 

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

Перед повторным выполнением скрипта, активируем опцию оптимизации и немного изменим условие LIKE во втором запросе (text like 'SELECT * FROM Person.Contact%')

 

SP_CONFIGURE 'show advanced options',1

RECONFIGURE

GO

SP_CONFIGURE 'optimize for ad hoc workloads',

1 RECONFIGURE

GO

 

В этом случае второй запрос не вернет никаких результатов, показывая, что план выполнения не был сохранен для первого запроса. Если еще раз выполнить запросы, можно заметить что SQL Server 2008 сохранил план выполнения, несмотря на установленную опцию. Таким образом, можно сделать вывод ‒ активация опции optimize for ad hoc workload затрагивает лишь новые запросы, а не те, план выполнения которых уже сохранен.

 

Еще одна возможность оптимизации представлена в SQL Server 2008, которая позволяет создавать структуру («заморозить» план) для любого плана выполнения, существующего в памяти SQL Server. Эта опция также позволяет расширить создание структуры для всех команд DML. Стоит отметить, что в 2005 версии SQL Server, эта функция была доступна только для команды SELECT и позволяла задавать подсказки для запросов которые не могут быть изменены напрямую из приложения.

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

 

USE AdventureWorks

GO

DBCC FREEPROCCACHE

GO

SET STATISTICS XML ON

EXEC sp_executesql

N'SELECT *

FROM Sales.SalesOrderDetail

JOIN Sales.SalesOrderHeader

ON

Sales.SalesOrderHeader.SalesOrderID =

Sales.SalesOrderDetail.SalesOrderID'

SET STATISTICS XML OFF

GO

 

Далее мы «заморозим» ранее созданный план выполнения.

 

DECLARE @plan_handle varbinary(1000)

SELECT @plan_handle = plan_handle

FROM sys.dm_exec_query_stats qs

cross apply

sys.dm_exec_sql_text(qs.sql_handle) sqt

WHERE text like '%SalesOrderDetail%'

SELECT @plan_handle

EXEC sp_create_plan_guide_from_handle

'TEST_Plan_Guide_1',

@plan_handle=@plan_handle

 

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

 

Далее используем «замороженный» план и выполним запрос снова:

 

SET STATISTICS XML ON

EXEC sp_executesql

N'SELECT *

FROM Sales.SalesOrderDetail

JOIN Sales.SalesOrderHeader

ON

Sales.SalesOrderHeader.SalesOrderID =

Sales.SalesOrderDetail.SalesOrderID'

SET STATISTICS XML OFF

GO

 

Можно убедиться что используемый план – это ранее созданный.

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

 

DECLARE @C1 INT

DECLARE @C2 INT

SET @C1 = 5000000

SET @C2 = 400

SELECT * FROM TEST WHERE COL1 > @C1 or

COL2 > @C2 ORDER BY COL1;

 

Когда план созданный для предыдущего запроса удалялся и нетипичные значения 1 и 90 присваивались переменным С1 и С2, оптимизатор создавал при повторном выполнении запроса план, который мог отличаться от первоначального. На практике, т.к. запросы одинаковые, оптимизатор продолжал использовать первоначально созданный план, что приводило к потери производительности для последующих значений переменных С1 и С2. Чтобы этого избежать и воспользоваться преимуществом параметризации запросов, предыдущий запрос должен быть переписан как:

 

SELECT * FROM TEST WHERE COL1 > @C1 or

COL2 > @C2 ORDER BY COL1

OPTION (OPTIMIZE FOR (@C1 UNKNOWN, @C2

UNKNOWN))

 

5 Выводы

 

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

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

 

Литература

 

[1] G. Fritchey and S. Dam, SQL Server

2008 Query Performance Tuning Distilled,

Apress, 2009;

[2] MSDN, Data Type Conversion

[3] MSDN, Microsoft Patterns & Practices

[4] P. Dave, Journey to SQL Authority