Оптимизация приложений на базе sql server
DESCRIPTION
DAT305. Старший консультант. [email protected]. Оптимизация приложений на базе SQL Server. Дмитрий Артемов. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/1.jpg)
![Page 2: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/2.jpg)
DAT305
Оптимизация приложений на базе SQL Server
Дмитрий АртемовСтарший консультант[email protected]
![Page 3: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/3.jpg)
Analyzing Oracle wait events is the most important performance tuning task you’ll perform when troubleshooting a slow-running query. When a query is running slow, it usually means that there are excessive waits of one type or another
![Page 4: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/4.jpg)
Зачем я здесь?
Вторая из двух презентаций, в которых я постараюсь дать сводную картину инструментария, доступного в SQL Server 2008 R2 для анализа ситуации, выявления проблем и оптимизации инфраструктуры и приложения (кода и индексной схемы)
![Page 5: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/5.jpg)
ПланВведение – DMV/DMFУказатели оптимизатору, польза и вред Кеширование запросов\повторное использование планов
Как правильное кодирование позволяет оптимизировать использование кеша - параметризация
Оптимизация индексной схемы (кластерный \ некластерный \ фильтрованный)
Все ли индексы нам нужны и от каких можно избавитьсяСредства поиска проблемного кодаИнтерпретация результатов от DMV
![Page 6: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/6.jpg)
Введение – DMV/DMF
DMV/DMF – системные представления/функции, позволяющие заглянуть внутрь SQL Server
SQL Server 2008 R2 + SP1: 141 штукаНе все описаны в OFFLINE документацииSQL Server 2012 : 174 штуки
Именованы по подистемам: dm_db/os/io/exec…*В этой части мы будем в первую очередь рассматривать DM_EXEC_*, DM_TRAN_*, DM_DB_*,…Первая презентация рассматривала DM_OS_*, DM_IO_*,…
![Page 7: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/7.jpg)
Указатели оптимизатору: польза и вредПри использовании указателей нужно тщательно тестироватьЧасто обновление статистики снимает необходимость использования указателя
Однако, если оптимизатор настойчиво ошибается в построении плана, можно зафиксировать план указателем
Некоторые указатели фиксируют структуру плана (например FAST n требует обязательного использования LOOP JOIN)Часто полезным оказывается RECOMPILE для устранения проблем с параметризациейИногда указание HASH JOIN существенно повышает производительность
Явное указание MAXDOP поможет ускорению тяжелых запросов
![Page 8: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/8.jpg)
Ккеширование, планы, память
![Page 9: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/9.jpg)
Кеширование запросовКеширование обеспечивает повторное использование планов
Снижается загрузка процессораПовышается скорость исполнения запросовИнтенсивные компиляции могут «уложить» даже мощный сервер
SQL Server управляет очередью компиляций через три шлюза
DBCC MemoryStatus: Small/Medium/Big GatewayНужно _очень_ постараться, чтобы забить шлюзы, но это возможно
![Page 10: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/10.jpg)
Кеширование запросов
sys.dm_exec_cached_plans – полный список планов в кеше
refcounts, usecounts – индикация повторного использования плановsize_in_bytes – размер плана в кешеplan_handle – ссылка на план
sys.dm_exec_query_plan(plan_handle)query_plan – план в формате XML
![Page 11: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/11.jpg)
Статистика по выполненным запросам
sys.dm_exec_query_stats – на уровне командsys.dm_exec_procedure_stats – на уровне процедур
Накопленная с момента старта сервераПри вытеснении плана из кеша информация теряетсяSQL Server 2008 R2 SP1 получил доп. информацию
total_rows, last_rows, min_rows and max_rowsПолезно знать различия в выдаче, возможно потребуется RECOMPILE
Представления позволяют найти проблемные запросы
![Page 12: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/12.jpg)
Статистика по выполненным запросамСортировка по total_worker_time,total_physical_reads, total_logical_writes, total_logical_reads, возможно поделенные на execution_count, выделяет тяжелые запросы(total_elapsed_time - total_worker_time) позволит определить запросы, которые долго ожидали выполненияЕсли речь идет о процедуре, то sql_handle и plan_handle одинаковы для запроса и процедуры
С помощью statement_start_offset и statement_end_offset можно вычленить текст запроса
plan_generation_num позволяет определить число рекомпиляций запросаquery_hash, query_plan_hash – хеши запроса и плана позволяют группировать похожие планы и запросы
Хеш запроса рассчитывается при компиляции, пробелы, */полный список полей, (не) квалифицированное имя объекта не влияют на хешЗапросы, имеющие одинаковый хеш запроса, но разные значения хеш плана, могут нуждаться в параметризации
![Page 13: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/13.jpg)
Планы запросов
sys.dm_exec_query_plan (plan_handle) – выводит план запроса в XMLСохраняем с расширением .sqlplan и можем исследоватьИногда текст массивного запроса не попадает в план целиком и нужно добавить выборку из sys.dm_exec_sql_text для получения текста запросаВ плане можно найти значения параметров, с которыми план компилировался
При первой компиляции
<ParameterList> <ColumnReference Column="@BusinessEntityID" ParameterCompiledValue="(12)" /></ParameterList>
![Page 14: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/14.jpg)
Планы нужно как-то смотретьВ порядке удобства
SQL Sentry Plan Explorer – бесплатная утилита (http://www.sqlsentry.net)XML Notepad – бесплатная утилита (http://msdn.microsoft.com/xml)SQL Server Management Studio
Почувствуйте разницу
![Page 15: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/15.jpg)
Управление памятьюДавление на память может быть внешним и внутреннимВнешнее давление – от процессов в системе
SQL Server может начать сокращение используемой памяти (если нет настройки Lock pages in memory)
Внутреннее – результат завышенных требований от компонентов внутри SQL Server
Компоненты, требующих выделения больших страниц (за рамками Buffer pool)Запросы, требующие слишком много памятиSQL Server начинает перераспределять память между компонентами кеша
![Page 16: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/16.jpg)
Управление памятьюПо мере необходимости компоненты управления памятью балансируют объемом для оптимизации работы сервера
Уведомление → Менеджер памяти → Memory clerk → Clock handЕсли мы видим, что какая-то «стрелка» постоянно движется, нужно искать причины
Типов кеша существует несколько:CACHESTORE_OBJCP - triggers,stored procs, functions the CACHESTORE_OBJCP is used to cache the object compile plan.CACHESTORE_SQLCP - Adhoc and prepared sql will be used as queries to be stores in the SQLCP cachestore.CACHESTORE_PHDR - created for views and contains parsing and algebrized tree (so during query optimization).CACHESTORE_XPROC - used by system Xprocs. Xprocs are predefined sps like sp_executesql, sp_cursor*, sp_Trace*. CACHESTORE_TEMPTABLES - store temp objects (local temp table, global temp table, table variable)CACHESTORE_CLRPROC - SQLCLR procedure cacheCACHESTORE_EVENTS - used to store event notifications for Service Broker purposesCACHESTORE_CURSORS - Local TSQL cursors, Global TSQL cursor, and API cursors to be stored in this cachestoreUSERSTORE_TOKENPERM - This cache is used to store all login/user tokens as well as respective permission and access caches.USERSTORE_OBJPERM - An instance of this cache is created for each database and an additional one for server objects
Сервер управляет памятью сам и делает это динамически (в пределах установленных лимитов)
Кеш процедур: (75% памяти от 0 до 4 Гб) + (10% памяти от 4 до 64 Гб) + (5% памяти более 64 Гб)
![Page 17: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/17.jpg)
Потребление памятиsys.dm_exec_query_resource_semaphore
Общая информация по потреблению памяти: выделение, ожидание,…sys.dm_exec_query_memory_grants
Выделение памяти для текущих запросовПомогает найти запросы с чрезмерными требованиями или оценить ситуацию с нехваткой памятиideal_memory_kb column – «идеальное» требование к памяти на основе оценки выдачи записейrequested_memory_kb – запрошенный объем памяти, по достижении максимума может снижатьсяЕсли requested_memory_kb << ideal_memory_kb, запрос может начать сброс памяти на дискrequired_memory_kb – минимальная память, необходимая для выполнения sort и hash joinЕсли required_memory_kb >> used_memory_kb, возможно это план с завышенным самомнениемИспользуйте plan_handle, sql_handle, чтобы найти _его_
![Page 18: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/18.jpg)
Содержимое буфераsys.dm_os_buffer_descriptorsПоказывает все страницы, находящиеся в памяти
database_idfile_idpage_idpage_levelallocation_unit_id
На современном сервере хранит миллионы дескрипторовЛюбопытным можно посмотреть на динамику, оценить особенности буферизации…
![Page 19: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/19.jpg)
Очистка памяти
DBCCFREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ] – удаляет все или указанные планы из кешаFREESESSIONCACHE – очищает кеш с данными о соединениях распределенных запросовFREESYSTEMCACHE ( 'ALL' [, pool_name ] ) – принудительно удаляет неиспользуемые элементы из кешаFLUSHPROCINDB (db_id) – удаляет планы, относящиеся к определенной БД
![Page 20: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/20.jpg)
Транзакции
![Page 21: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/21.jpg)
Транзакцииsys.dm_tran_locks
request_status – поиск ожиданийsys.dm_tran_session_transactions
Session_id - связкаsys.dm_tran_database_transactions
Транзакции по всем БД сервера (database_id)database_transaction_log_* - анализ использования журнала транзакций
sys.dm_tran_current_transactionТранзакции в сессии. Если не используется snapshot isolation, нам не нужно
sys.dm_tran_active_transactionsИспользуя связку через session_idможно найти, кто застрял на исполнении, а там и до плана/текста недалеко
![Page 22: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/22.jpg)
Индексы
![Page 23: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/23.jpg)
Краткое вступление
Кластерный, некластерный, фильтрованный,…Кластерный ключ - узкий, уникальный, статичный, монотонно возрастающийКластерный индекс – вещь нужная
Дефрагментация таблицыСекционированиеПоиск
Особенно по диапазону, но и точечный хорошо работаетСтабильность некластерных индексов при расщеплении страниц
![Page 24: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/24.jpg)
Индексы
sys.dm_db_index_operational_stats (db_id, object_id, index_id, partition_number)Операционная информация по индексам: вставки, сканирование, ожидания, блокировкиПо мере заполнения/очистки кеша информация по индексам может появляться/исчезать
![Page 25: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/25.jpg)
Индексы
sys.dm_db_index_usage_statsВключает только то, что хоть раз было использовано с момента рестарта сервераВыдает информацию по всем индексам всех БД (фильтр по database_id = …)Не понимает секционированияВключает некластеризованную таблицу как индекс (index_id = 0)Насколько полезен индекс
user_seeks + user_scans + user_lookups VS user_updatesUser_lookups – для кластерного индекса (отслеживает Bookmark lookup’ы)
![Page 26: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/26.jpg)
Индексы
sys.dm_db_index_physical_statsФизическое состояние индексов: количество записей, фрагментация на всех уровнях, различные уровни детализацииСпособна выдавать информацию на уровне сервера (все БД), отдельной БД, отдельной таблицы, отдельного индекса, отдельной секцииПо умолчанию используется режим DEFAULT=NULL=LIMITED
Листовой уровень индекса не сканируетсяДля некластеризованных таблиц информация берется из PFS & IAM страниц, страницы данных не сканируютсяЧасть полей выходного набора = NULL
SAMPLED – сканируется 1% страницЕсли таблица имеет менее 10 000 страниц используется режим DETAILED
DETAILED – полное сканированиеНа больших таблицах – долго и затратно
![Page 27: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/27.jpg)
Лирическое отступлениеИндексы и использование GUID
Примерно на порядок медленнее вставки и на порядок больше IOПо сравнению с индексированием по «нормальным» типам (например INT, BIGINT)
Фрагментация 99.9(9)% - нарастает очень быстроДефрагментация не имеет смысла
Тут же возвращаетсяЗа счет уплотнения страниц резко возрастает число расщеплений и производительность падает очень сильно
Использование секционирования позволяет поддерживать производительность на желаемом уровне
Размер секции нужно подбирать (зависит от объема данных, не числа записей)Производительность держится даже без использования кластерного индекса
![Page 28: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/28.jpg)
Лирическое отступлениеИндексы и производительность
ФактИндексы повышают скорость выборкиИндексы снижают скорость модификаций
ВопросА на сколько снижается скорость вставки?
ОтветВ нашем тесте, на таблице из 45 полей было построено 33 индексаПроизводительность вставки упала вдвое (всего вставили 1 000 000 000 записей)
Тестируйте ваш подход!!
![Page 29: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/29.jpg)
ДефрагментацияЕсли индекс есть, все просто – REBUILD / REORGANIZE там, где нужно/полезноНекластеризованная таблица
Если только вставки, то фрагментация может быть и так невеликаCREATE / DROP Clustered index – фрагментация частично возвращаетсяALTER TABLE REBUILD – вообще непонятно что происходитНе все однозначно
Лучше все-таки иметь кластерный индекс
![Page 30: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/30.jpg)
Отсутствующие индексы
sys.dm_db_missing_index_*Каждый раз, когда оптимизатор создает план и видит, что наличие того или иного индекса может улучшить производительность, он помещает в план раздел
<MissingIndexes> <MissingIndexGroup Impact="95.8296"> <MissingIndex Database="[Db]" Schema="[Sch]" Table="[Tab]">
По данным которого можно спланировать оптимизацию индексной схемы
![Page 31: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/31.jpg)
Отсутствующие индексыПо счастью, не нужно разбирать план
sys.dm_db_missing_index_detailssys.dm_db_missing_index_group_statssys.dm_db_missing_index_groups
Позволяют найти наиболее полезные индексы и построить команду CREATE INDEXMissing index details
equality_columns, inequality_columns, included_columns, statement
Missing index group statsavg_total_user_cost * avg_user_impact * (user_seeks + user_scans)
![Page 32: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/32.jpg)
Отсутствующие индексы
Нужно помнить о некоторых ограниченияхНе предназначена для тонкой настройки конфигурации индексирования.Не может собирать статистику более чем о 500 группах отсутствующих индексов.Не указывает порядок использования столбцов в индексе.Для запросов, содержащих только предикаты неравенства, возвращает менее точные сведения о стоимости.Сообщает только о столбцах включения для некоторых запросов, поэтому ключевые столбцы индекса необходимо выбрать вручную.Возвращает только необработанные сведения о столбцах, для которых индексы могут отсутствовать.Не предлагает отфильтрованные индексы.Может возвращать различные стоимости для одной группы отсутствующих индексов, которая несколько раз встречается в отчете инструкции XML Showplan.Не рассматривает тривиальные планы запросов.
![Page 33: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/33.jpg)
Отсутствующие индексы
Действительно, следует критически походить к предложениям и не строить слепо все, что предложеноВ основном следует строить наиболее полезные индексыЧасто предлагается очень длинный список INCLUDE полейЗа построенными индексами нужно следить средствами sys.dm_db_index_usage/operational_stats
Полезно именовать их особым образом
![Page 34: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/34.jpg)
Все вместе
![Page 35: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/35.jpg)
Как бытьНайти ожиданияНайти запросы, которые страдают от этих ожиданийОпределить причину страданияПолучить дополнительную информациюНайти пути обхода/устраненияПротестироватьНайти ожидания…
![Page 36: Оптимизация приложений на базе SQL Server](https://reader036.vdocuments.us/reader036/viewer/2022081420/56815aa4550346895dc833ea/html5/thumbnails/36.jpg)
Спасибо!Я готов ответить на ваши вопросы
Пожалуйста, не забудьте заполнить форму с оценкой