Оптимизация приложений на базе SQL Server

2. DAT305 Старший консультант dimaa@microsoft.com Оптимизация приложений на базе SQL Server Дмитрий Артемов

3. 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

4. Зачем я здесь? Вторая из двух презентаций, в которых я постараюсь дать сводную картину инструментария, доступного в SQL Server 2008 R2 для анализа ситуации, выявления проблем и оптимизации инфраструктуры и приложения (кода и индексной схемы)

5. План • Введение – DMV/DMF • Указатели оптимизатору, польза и вред • Кеширование запросов\повторное использование планов • Как правильное кодирование позволяет оптимизировать использование кеша - параметризация • Оптимизация индексной схемы (кластерный \ некластерный \ фильтрованный) • Все ли индексы нам нужны и от каких можно избавиться • Средства поиска проблемного кода • Интерпретация результатов от DMV

6. Введение – 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_*,…

7. Указатели оптимизатору: польза и вред • При использовании указателей нужно тщательно тестировать • Часто обновление статистики снимает необходимость использования указателя • Однако, если оптимизатор настойчиво ошибается в построении плана, можно зафиксировать план указателем • Некоторые указатели фиксируют структуру плана (например FAST n требует обязательного использования LOOP JOIN) • Часто полезным оказывается RECOMPILE для устранения проблем с параметризацией • Иногда указание HASH JOIN существенно повышает производительность • Явное указание MAXDOP поможет ускорению тяжелых запросов

8. Ккеширование, планы, память

9. Кеширование запросов • Кеширование обеспечивает повторное использование планов • Снижается загрузка процессора • Повышается скорость исполнения запросов • Интенсивные компиляции могут «уложить» даже мощный сервер • SQL Server управляет очередью компиляций через три шлюза • DBCC MemoryStatus: Small/Medium/Big Gateway • Нужно _очень_ постараться, чтобы забить шлюзы, но это возможно

10. Кеширование запросов • sys.dm_exec_cached_plans – полный список планов в кеше • refcounts, usecounts – индикация повторного использования планов • size_in_bytes – размер плана в кеше • plan_handle – ссылка на план • sys.dm_exec_query_plan(plan_handle) • query_plan– план в формате XML

11. Статистика по выполненным запросам • 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 • Представления позволяют найти проблемные запросы

12. Статистика по выполненным запросам • Сортировка по 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– хеши запроса и плана позволяют группировать похожие планы и запросы • Хеш запроса рассчитывается при компиляции, пробелы, */полный список полей, (не) квалифицированное имя объекта не влияют на хеш • Запросы, имеющие одинаковый хеш запроса, но разные значения хеш плана, могут нуждаться в параметризации

13. Планы запросов • sys.dm_exec_query_plan (plan_handle) – выводит план запроса в XML • Сохраняем с расширением .sqlplanи можем исследовать • Иногда текст массивного запроса не попадает в план целиком и нужно добавить выборку из sys.dm_exec_sql_textдля получения текста запроса • В плане можно найти значения параметров, с которыми план компилировался • При первой компиляции <ParameterList> <ColumnReference Column="@BusinessEntityID" ParameterCompiledValue="(12)" /> </ParameterList>

14. Планы нужно как-то смотреть • В порядке удобства • SQL Sentry Plan Explorer – бесплатная утилита (http://www.sqlsentry.net) • XML Notepad – бесплатная утилита (http://msdn.microsoft.com/xml) • SQL Server Management Studio • Почувствуйте разницу

15. Управление памятью • Давление на память может быть внешним и внутренним • Внешнее давление – от процессов в системе • SQL Server может начать сокращение используемой памяти (если нет настройки Lock pages in memory) • Внутреннее – результат завышенных требований от компонентов внутри SQL Server • Компоненты, требующих выделения больших страниц (за рамками Buffer pool) • Запросы, требующиеслишком много памяти • SQL Server начинает перераспределять память между компонентами кеша

16. Управление памятью • По мере необходимости компоненты управления памятью балансируют объемом для оптимизации работы сервера • Уведомление→Менеджер памяти→ Memory clerk→ Clock hand • Если мы видим, что какая-то «стрелка» постоянно движется, нужно искать причины • Типов кеша существует несколько: • CACHESTORE_OBJCP - triggers,storedprocs, 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 cache • CACHESTORE_EVENTS - used to store event notifications for Service Broker purposes • CACHESTORE_CURSORS - Local TSQL cursors, Global TSQL cursor, and API cursors to be stored in this cachestore • USERSTORE_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 Гб)

17. Потребление памяти • 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, чтобы найти _его_

18. Содержимое буфера • sys.dm_os_buffer_descriptors • Показывает все страницы, находящиеся в памяти • database_id • file_id • page_id • page_level • allocation_unit_id • На современном сервере хранит миллионы дескрипторов • Любопытным можно посмотреть на динамику, оценить особенности буферизации…

19. Очистка памяти • DBCC • FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ] – удаляет все или указанные планы из кеша • FREESESSIONCACHE – очищает кеш с данными о соединениях распределенных запросов • FREESYSTEMCACHE ( 'ALL' [, pool_name ] ) – принудительно удаляет неиспользуемые элементы из кеша • FLUSHPROCINDB (db_id) – удаляет планы, относящиеся к определенной БД

20. Транзакции

21. Транзакции • 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можно найти, кто застрял на исполнении, а там и до плана/текста недалеко

22. Индексы

23. Краткое вступление • Кластерный, некластерный, фильтрованный,… • Кластерный ключ - узкий, уникальный, статичный, монотонно возрастающий • Кластерный индекс – вещь нужная • Дефрагментация таблицы • Секционирование • Поиск • Особенно по диапазону, но и точечный хорошо работает • Стабильность некластерных индексов при расщеплении страниц

24. Индексы • sys.dm_db_index_operational_stats(db_id, object_id, index_id, partition_number) • Операционная информация по индексам: вставки, сканирование, ожидания, блокировки • По мере заполнения/очистки кеша информация по индексам может появляться/исчезать

25. Индексы • sys.dm_db_index_usage_stats • Включает только то, что хоть раз было использовано с момента рестарта сервера • Выдает информацию по всем индексам всех БД (фильтр по database_id = …) • Не понимает секционирования • Включает некластеризованную таблицу как индекс (index_id = 0) • Насколько полезен индекс • user_seeks+ user_scans + user_lookups VS user_updates • User_lookups – для кластерного индекса (отслеживает Bookmark lookup’ы)

26. Индексы • sys.dm_db_index_physical_stats • Физическое состояние индексов: количество записей, фрагментация на всех уровнях, различные уровни детализации • Способна выдавать информацию на уровне сервера (все БД), отдельной БД, отдельной таблицы, отдельного индекса, отдельной секции • По умолчанию используется режим DEFAULT=NULL=LIMITED • Листовой уровень индекса не сканируется • Для некластеризованных таблиц информация берется из PFS & IAM страниц, страницы данных не сканируются • Часть полей выходного набора = NULL • SAMPLED – сканируется 1% страниц • Если таблица имеет менее 10 000 страниц используется режим DETAILED • DETAILED – полное сканирование • На больших таблицах – долго и затратно

27. Лирическое отступлениеИндексы и использование GUID • Примерно на порядок медленнее вставки и на порядок больше IO • По сравнению с индексированием по «нормальным» типам (например INT, BIGINT) • Фрагментация 99.9(9)% - нарастает очень быстро • Дефрагментация не имеет смысла • Тут же возвращается • За счет уплотнения страниц резко возрастает число расщеплений и производительность падает очень сильно • Использование секционирования позволяет поддерживать производительность на желаемом уровне • Размер секции нужно подбирать (зависит от объема данных, не числа записей) • Производительность держится даже без использования кластерного индекса

28. Лирическое отступлениеИндексы и производительность Тестируйте ваш подход!! • Факт • Индексы повышают скорость выборки • Индексы снижают скорость модификаций • Вопрос • А на сколько снижается скорость вставки? • Ответ • В нашем тесте, на таблице из 45 полей было построено 33 индекса • Производительность вставки упала вдвое (всего вставили 1 000 000 000 записей)

29. Дефрагментация • Если индекс есть, все просто – REBUILD/ REORGANIZE там, где нужно/полезно • Некластеризованная таблица • Если только вставки, то фрагментация может быть и так невелика • CREATE / DROP Clustered index – фрагментация частично возвращается • ALTER TABLE REBUILD – вообще непонятно что происходит • Не все однозначно • Лучше все-таки иметь кластерный индекс

30. Отсутствующие индексы • sys.dm_db_missing_index_* • Каждый раз, когда оптимизатор создает план и видит, что наличие того или иного индекса может улучшить производительность, он помещает в план раздел <MissingIndexes> <MissingIndexGroup Impact="95.8296"> <MissingIndex Database="[Db]" Schema="[Sch]" Table="[Tab]"> • По данным которого можно спланировать оптимизацию индексной схемы

31. Отсутствующие индексы • По счастью, не нужно разбирать план • sys.dm_db_missing_index_details • sys.dm_db_missing_index_group_stats • sys.dm_db_missing_index_groups • Позволяют найти наиболее полезные индексы и построить команду CREATE INDEX • Missing index details • equality_columns, inequality_columns, included_columns, statement • Missing index group stats • avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)

32. Отсутствующие индексы • Нужно помнить о некоторых ограничениях • Не предназначена для тонкой настройки конфигурации индексирования. • Не может собирать статистику более чем о 500 группах отсутствующих индексов. • Не указывает порядок использования столбцов в индексе. • Для запросов, содержащих только предикаты неравенства, возвращает менее точные сведения о стоимости. • Сообщает только о столбцах включения для некоторых запросов, поэтому ключевые столбцы индекса необходимо выбрать вручную. • Возвращает только необработанные сведения о столбцах, для которых индексы могут отсутствовать. • Не предлагает отфильтрованные индексы. • Может возвращать различные стоимости для одной группы отсутствующих индексов, которая несколько раз встречается в отчете инструкции XML Showplan. • Не рассматривает тривиальные планы запросов.

33. Отсутствующие индексы • Действительно, следует критически походить к предложениям и не строить слепо все, что предложено • В основном следует строить наиболее полезные индексы • Часто предлагается очень длинный список INCLUDE полей • За построенными индексами нужно следить средствами sys.dm_db_index_usage/operational_stats • Полезно именовать их особым образом

34. Все вместе

35. Как быть • Найти ожидания • Найти запросы, которые страдают от этих ожиданий • Определить причину страдания • Получить дополнительную информацию • Найти пути обхода/устранения • Протестировать • Найти ожидания • …

36. Спасибо! Я готов ответить на ваши вопросы Пожалуйста, не забудьте заполнить форму с оценкой