Создание оптимального кода на стороне SQL Server: Transact-SQL Алексей Шуленин Microsoft Consulting Service. - презентация

1 Создание оптимального кода на стороне SQL Server: Transact-SQL Алексей Шуленин Microsoft Consulting Service

2 О чем пойдет речь в данном докладе Вещи, которых мы здесь коснемся, не являются необходимыми для разработчика Вещи, которых мы здесь коснемся, не являются необходимыми для разработчика Тем более, DBA Тем более, DBA SQL Server обладает достаточно продвинутыми алгоритмами, чтобы оптимизировать неоптимально составленный запрос SQL Server обладает достаточно продвинутыми алгоритмами, чтобы оптимизировать неоптимально составленный запрос При условии, что синтаксис в норме При условии, что синтаксис в норме И мы правильно выразили свои желания в терминах SQL И мы правильно выразили свои желания в терминах SQL Тем не менее... Тем не менее...

3 О чем пойдет речь в данном докладе Вашему вниманию предлагается несколько интересных этюдов по основам SQL Server, надерганных из Вашему вниманию предлагается несколько интересных этюдов по основам SQL Server, надерганных из печатных изданий печатных изданий форумов и дискуссий форумов и дискуссий опыта личного и коллег опыта личного и коллег Объединенных общей тематикой «загадочного» поведения очевидного T-SQLного кода Объединенных общей тематикой «загадочного» поведения очевидного T-SQLного кода

4 Итак, первое Что понимать под оптимальностью кода? Что понимать под оптимальностью кода? Можно встретить много критериев Можно встретить много критериев Читабельность, сопровождаемость, переносимость, надежность, безопасность, скорость... Читабельность, сопровождаемость, переносимость, надежность, безопасность, скорость... Все они так или иначе связаны друг с другом Все они так или иначе связаны друг с другом Нас в рамках данного доклада будет в первую очередь интересовать именно быстродействие Нас в рамках данного доклада будет в первую очередь интересовать именно быстродействие Ибо несмотря на все свои ограничения Т-SQL чрезвычайно богатый язык Ибо несмотря на все свои ограничения Т-SQL чрезвычайно богатый язык Так, что решать поставленную задачу чаще всего можно разными способами Так, что решать поставленную задачу чаще всего можно разными способами Как избежать при этом заведомо нецелесообразных и облегчить жизнь оптимизатору Как избежать при этом заведомо нецелесообразных и облегчить жизнь оптимизатору Не существует универсального рецепта Не существует универсального рецепта Практика, опыт, типовые примеры Практика, опыт, типовые примеры

5 Для разминки, или TOP-N-Engine Что и когда лучше: SET ROWCOUNT или SELECT TOP... Что и когда лучше: SET ROWCOUNT или SELECT TOP... Ну т.е. понятно, что ТОРу можно сказать PERCENT, довыводить хвосты (WITH TIES) Ну т.е. понятно, что ТОРу можно сказать PERCENT, довыводить хвосты (WITH TIES) С помощью ТОР можно сделать VIEW с ORDER BY С помощью ТОР можно сделать VIEW с ORDER BY Все это, безусловно, важно и удобно, но сейчас не об этом Все это, безусловно, важно и удобно, но сейчас не об этом Имеем достаточно большую неиндексированную таблицу Tbl и холодный кэш Имеем достаточно большую неиндексированную таблицу Tbl и холодный кэш Почему SELECT * FROM Tbl ORDER BY Fld выполняется с ТОР в разы быстрее, чем с ROWCOUNT при абсолютно одинаковых планах? Почему SELECT * FROM Tbl ORDER BY Fld выполняется с ТОР в разы быстрее, чем с ROWCOUNT при абсолютно одинаковых планах? Демо: TopNandRowcount.sql Демо: TopNandRowcount.sql

6 Исследовательские инструменты set statistics time set statistics time set statistics io set statistics io Table 'sales_fact_1998'. Scan count 1, logical reads 1128, physical reads 0, read-ahead reads Table 'sales_fact_1998'. Scan count 1, logical reads 1128, physical reads 0, read-ahead reads SQL Server Execution Times: SQL Server Execution Times: CPU time = 861 ms, elapsed time = 1546 ms. CPU time = 861 ms, elapsed time = 1546 ms. Table 'sales_fact_1998'. Scan count 1, logical reads 1128, physical reads 0, read-ahead reads Table 'sales_fact_1998'. Scan count 1, logical reads 1128, physical reads 0, read-ahead reads SQL Server Execution Times: SQL Server Execution Times: CPU time = 391 ms, elapsed time = 847 ms. CPU time = 391 ms, elapsed time = 847 ms.

7 Исследовательские инструменты set showplan_all set showplan_all StmtText… Node Id Pare nt Physical Op Logical Op… Estimate Rows… AvgRow Size TotalSub treeCost… select top 100 * from sales_fact_1998 order by store_sales10NULL 100NULL |--Sort(TOP 100, ORDER BY:([sales_fact_1998].[store_sales] ASC))31Sort TopN Sort |--Table Scan(OBJECT:([FoodMart].[dbo].[sales_fact_1998]))43 Table Scan

8 OPTION (FAST N): пример Разница заметна даже на небольших таблицах Разница заметна даже на небольших таблицах select * from #t order by Quantity select * from #t order by Quantity select * from #t order by Quantity option (fast 100) select * from #t order by Quantity option (fast 100) Сущ-т некласт.индекс по Quantity Сущ-т некласт.индекс по Quantity Сравнит.планы: Сравнит.планы: Сравнит.времена выполнения (set statistics time): Сравнит.времена выполнения (set statistics time): ( row(s) affected) ( row(s) affected) SQL Server Execution Times: CPU time = 430 ms, elapsed time = 2953 ms. SQL Server Execution Times: CPU time = 430 ms, elapsed time = 2953 ms. ( row(s) affected) ( row(s) affected) SQL Server Execution Times: CPU time = 541 ms, elapsed time = 2986 ms. SQL Server Execution Times: CPU time = 541 ms, elapsed time = 2986 ms. Демо: OptionFast.sql Демо: OptionFast.sql

9 Оптимизатор принял решение не исп. инд. по Quantity ввиду его невысокой селективности Оптимизатор принял решение не исп. инд. по Quantity ввиду его невысокой селективности Дешевле скан. табл., а затем отсорт. рез-ты, чем 100 тыс. раз пробегать по всей глубине инд. дерева, а потом еще вытаскивать страницу данных Дешевле скан. табл., а затем отсорт. рез-ты, чем 100 тыс. раз пробегать по всей глубине инд. дерева, а потом еще вытаскивать страницу данных Однако в этом случае он может вернуть клиенту только все 100 тыс. записей целиком Однако в этом случае он может вернуть клиенту только все 100 тыс. записей целиком После того, как они будут отсортированы После того, как они будут отсортированы Иногда лучше пойти на заведомо неопт.стратегию, чем заставлять клиента ждать около пустого грида Иногда лучше пойти на заведомо неопт.стратегию, чем заставлять клиента ждать около пустого грида option(fast n) аналогичен хинту (index =...) - заставляет оптимизатор исп. инд. поиск по полю сортировки option(fast n) аналогичен хинту (index =...) - заставляет оптимизатор исп. инд. поиск по полю сортировки В этом случае первые записи определяются сразу и могут быть возвращены В этом случае первые записи определяются сразу и могут быть возвращены Если индекса по полю сортировки нет, ничего не происходит Если индекса по полю сортировки нет, ничего не происходит аналогичен fastfirstrow в пред. версиях аналогичен fastfirstrow в пред. версиях OPTION (FAST N): вывод

10 Можно ли избавиться от ORDER BY? Как Вы видите из плана, оператор сортировки на порядок дороже, чем даже тупое сканирование таблицы Как Вы видите из плана, оператор сортировки на порядок дороже, чем даже тупое сканирование таблицы (Индексов по-прежнему нет) (Индексов по-прежнему нет) Нельзя ли на нем сэкономить, если записи требуется выводить в том же порядке, как они добавлялись в таблицу? Нельзя ли на нем сэкономить, если записи требуется выводить в том же порядке, как они добавлялись в таблицу? Понятно, что SQL – множественно- ориентированный язык, но ведь физически на диске записи хранятся в каком-то порядке Понятно, что SQL – множественно- ориентированный язык, но ведь физически на диске записи хранятся в каком-то порядке Внимание, вопрос: всегда ли этот порядок тождественен порядку вставки? Внимание, вопрос: всегда ли этот порядок тождественен порядку вставки? Демо: PhysicalStorage.sql Демо: PhysicalStorage.sql

11 Итак, что мы видим SQL Server частично заполнял пустоты, частично аллоцировал новые экстенты SQL Server частично заполнял пустоты, частично аллоцировал новые экстенты Логика алгоритма зависит от размера таблицы и кол-ва пользователей Логика алгоритма зависит от размера таблицы и кол-ва пользователей Кстати, отличить однопользовательский доступ к данной области от многопользовательского – вовсе не такая простая задача, как может показаться Кстати, отличить однопользовательский доступ к данной области от многопользовательского – вовсе не такая простая задача, как может показаться Однако даже в ситуации single user гарантировать ничего нельзя Однако даже в ситуации single user гарантировать ничего нельзя Иногда механизму хранения дешевле взять новый экстент, чем отыскать пустоту Иногда механизму хранения дешевле взять новый экстент, чем отыскать пустоту На случай непредвиденных всплесков активности SQL Server старается поддерживать некоторое дополнительное пространство На случай непредвиденных всплесков активности SQL Server старается поддерживать некоторое дополнительное пространство Кроме того, параметры могут меняться в з- ти от Service Packa Кроме того, параметры могут меняться в з- ти от Service Packa

12 Исследовательские инструменты DBCC PAGE, DBCC TAB, DBCC EXTENTINFO DBCC PAGE, DBCC TAB, DBCC EXTENTINFO Кроме того, требуется понимание дискового устройства файла данных: GAM, SGAM, PFS, IAM Кроме того, требуется понимание дискового устройства файла данных: GAM, SGAM, PFS, IAM

13 Возвращаясь к вопросу об ORDER BY Точнее, о его отсутствии В пред.примере записи выдавались не по порядку, потому что при повторном INSERT часть из них он записал на старые страницы В пред.примере записи выдавались не по порядку, потому что при повторном INSERT часть из них он записал на старые страницы Предположим, имеем девственную таблицу, физ.порядок к-й соотв-т порядку вставки Предположим, имеем девственную таблицу, физ.порядок к-й соотв-т порядку вставки Вопрос: в каких ситуациях SQL Server может начать читать ее с середины? Вопрос: в каких ситуациях SQL Server может начать читать ее с середины?

14 Чтение массивной таблицы Увеличить буферный пул насильственно нельзя Увеличить буферный пул насильственно нельзя SQL Server при старте грузит себя (.exe сервиса) и все остальное, что требует кусков памяти > 8k SQL Server при старте грузит себя (.exe сервиса) и все остальное, что требует кусков памяти > 8k - Сетевые библиотеки, dllи ODS (xp и OLE Automation), провайдеров, … - Плюс 500k * Max worker threads Все, что осталось, он выделяет под буферный пул (побит на стр.по 8k) Все, что осталось, он выделяет под буферный пул (побит на стр.по 8k) - страницы данных и индексов, кэши журнала транзакций, планы запросов, системные структуры (якобы syslocks), информация о сессиях (sp_who), … Все это хозяйство балансируется и наращивается динамически Все это хозяйство балансируется и наращивается динамически Т.е. я не могу заранее выделить достаточный буферный кэш под сканирование массивной таблицы Т.е. я не могу заранее выделить достаточный буферный кэш под сканирование массивной таблицы dbcc pintable неразумно dbcc pintable неразумно Таблицу читают несколько пользователей Таблицу читают несколько пользователей Ранние страницы вымоются из кэша, пока он доберется до конца таблицы, п.ч. таблица здоровая Ранние страницы вымоются из кэша, пока он доберется до конца таблицы, п.ч. таблица здоровая Следующий снова полезет за ними на диск Следующий снова полезет за ними на диск И т.д., короче, производительность умрет И т.д., короче, производительность умрет

15 Философское отступление С каждой версией в SQL Server остается все меньше конфиг. настроек, к-ми можно «играться» С каждой версией в SQL Server остается все меньше конфиг. настроек, к-ми можно «играться» В основном, он все норовит решать за администратора и подстраивается сам В основном, он все норовит решать за администратора и подстраивается сам Ну т.е. посмотреть эти вещи иногда еще удается (с пом. недокументированных команд очень часто) Ну т.е. посмотреть эти вещи иногда еще удается (с пом. недокументированных команд очень часто) Но подкрутить уже едва ли Но подкрутить уже едва ли По-видимому, здесь прослеживается общая тенденция MS По-видимому, здесь прослеживается общая тенденция MS Снять с разработчика заботу о системных вещах Снять с разработчика заботу о системных вещах С тем, чтобы он мог сконцентрироваться на бизнес-логике приложения С тем, чтобы он мог сконцентрироваться на бизнес-логике приложения Напр., то же самое мы видим в.NET Framework: выделение памяти, сборка мусора,... Напр., то же самое мы видим в.NET Framework: выделение памяти, сборка мусора,... Кстати, Oracle в последнее время тоже обнаруживает тяготение к самонастройке Кстати, Oracle в последнее время тоже обнаруживает тяготение к самонастройке Хотя там еще существует достаточно простора для «шаманства» Хотя там еще существует достаточно простора для «шаманства»

16 Карусельное сканирование (merry-go-round scan) Функциональность 2000EE Функциональность 2000EE Если план предполагает table scan, а движок обнаружил, что другой план этим уже занимается Если план предполагает table scan, а движок обнаружил, что другой план этим уже занимается Он его к нему присоединяет Он его к нему присоединяет - С текущей позиции первого сканирования Т.о. каждая страница читается лишь однажды Т.о. каждая страница читается лишь однажды - И потребляется коллективно обоими планами Затем он возвращается в начало таблицы Затем он возвращается в начало таблицы И дочитывает его до той позиции, с к-й присоединился И дочитывает его до той позиции, с к-й присоединился - Уже индивидуально Не путать с опережающим чтением (read ahead) Не путать с опережающим чтением (read ahead) Это отдельный процесс, никак с этим не связанный Это отдельный процесс, никак с этим не связанный Существует и в Std.Ed., но там оно ограничено Существует и в Std.Ed., но там оно ограничено - 4 экстента на файл, макс.32 файла в параллели В ЕЕ не ограничено, управляется динамически В ЕЕ не ограничено, управляется динамически

17 Одно распространенное заблуждение BOL: A clustered index determines the physical order of data in a table BOL: A clustered index determines the physical order of data in a table PageFID PagePID IAMFID IAMPID ObjectID IndexID PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID PageFID PagePID IAMFID IAMPID ObjectID IndexID PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID NULL NULL На физически первой странице dbcc page('test', 1, 28, 3) уже лежат отнюдь не id = 1, 2: PAGE: (1:28) DEE00: DEE10: dbf id = 100 fld = bbb DFBBF: DFBCF: id = 101 fld = bbb Демо: ClusteredIndex.sql Демо: ClusteredIndex.sql

18 Структура кластерного индекса Создание кластерного индекса не означает физическое упорядочивание записей в соответствии с задаваемым им порядком. Создание кластерного индекса не означает физическое упорядочивание записей в соответствии с задаваемым им порядком. Кластерный индекс ЛОГИЧЕСКИ упорядочивает страницы в виде двунаправленного списка при помощи полей Кластерный индекс ЛОГИЧЕСКИ упорядочивает страницы в виде двунаправленного списка при помощи полей NextPageFID, NextPagePID, PrevPageFID, PrevPagePID, NextPageFID, NextPagePID, PrevPageFID, PrevPagePID, которые в его отсутствие обычно нулевые которые в его отсутствие обычно нулевые Именно в порядке этих указателей, а не IAM, теперь происходит сканирование таблицы Именно в порядке этих указателей, а не IAM, теперь происходит сканирование таблицы

19 Исследовательские инструменты Те же + Те же + dbcc ind('Имя БД', 'Имя табл.', -1) dbcc ind('Имя БД', 'Имя табл.', -1) Выдает список индексных страниц, принадлежащих таблице Выдает список индексных страниц, принадлежащих таблице select indexproperty(object_id('t'), 'ix', 'IndexDepth') select indexproperty(object_id('t'), 'ix', 'IndexDepth') Глубина дерева Глубина дерева dbcc showcontig('t', 'ix') with tableresults, all_levels dbcc showcontig('t', 'ix') with tableresults, all_levels Информация по каждому уровню и кол-ву страниц на каждом: Информация по каждому уровню и кол-ву страниц на каждом:

20 Индексы и блокировки Демо: LocksandIndexes.sql Демо: LocksandIndexes.sql Здесь и далее предполагаем read committed по умолчанию Здесь и далее предполагаем read committed по умолчанию На 1-м соединении: На 1-м соединении: begin tran begin tran update t set fld = fld where id = 1 update t set fld = fld where id = 1 На 2-м соединении: На 2-м соединении: update t set fld = fld where id = 3 update t set fld = fld where id = 3 Не может прочитать ID первой записи, потому что 1-я держит ее эксклюзивно Не может прочитать ID первой записи, потому что 1-я держит ее эксклюзивно 2-я не знает: вдруг там 3? 2-я не знает: вдруг там 3? Вынуждена ждать Вынуждена ждать Результат – lock timeout Результат – lock timeout Выход – создать индекс Выход – создать индекс Чтобы 2-я сессия могла взять ID с индексных страниц Чтобы 2-я сессия могла взять ID с индексных страниц

21 Что происходит при наличии индекса Lock:AcquiredTableNULLttIX54 Lock:AcquiredPage1:28tixIU54 Lock:AcquiredKey0x7e tixU54 Lock:AcquiredPage1:15tNULLIS54 Lock:AcquiredRow1:15:00tNULLS54 Lock:AcquiredPage1:15tNULLIU54 Lock:AcquiredRow1:15:00tNULLU54 Lock:AcquiredPage1:15tNULLIX54 Lock:AcquiredRow1:15:00tNULLX54 Lock:AcquiredTableNULLttIX51 Lock:AcquiredPage1:28tixIU51 Lock:AcquiredKey0xa01efc2b0013tixU51 Lock:AcquiredPage1:15tNULLIS51 Lock:AcquiredRow1:15:02tNULLS51 Lock:AcquiredPage1:15tNULLIU51 Lock:AcquiredRow1:15:02tNULLU51 Lock:AcquiredPage1:15tNULLIX51 Lock:AcquiredRow1:15:02tNULLX51 Lock:ReleasedKey0xa01efc2b0013tixNULL51 Lock:ReleasedPage1:28NULL 51 Lock:ReleasedRow1:15:02NULL 51 Lock:ReleasedPage1:15NULL 51 Lock:ReleasedTableNULLtt 51 Lock:ReleasedKey0x7e tixNULL54 Lock:ReleasedPage1:28NULL 54 Lock:ReleasedRow1:15:00NULL 54 Lock:ReleasedPage1:15NULL 54 Lock:ReleasedTableNULLtt 54

22 Тип индекса и блокировки Слегка разнообразим предыдущий пример Слегка разнообразим предыдущий пример Есть некластерный индекс Есть некластерный индекс create nonclustered index ix on t(id) create nonclustered index ix on t(id) На 1-м соединении: На 1-м соединении: begin tran begin tran update t set fld = fld where id = 1 update t set fld = fld where id = 1 На 2-м соединении: На 2-м соединении: update t set fld = fld where id = 1 update t set fld = fld where id = 1 На 1-м соединении: На 1-м соединении: update t set fld = fld where id = 1 update t set fld = fld where id = 1 Это конец: Это конец: Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

23 Почему так случилось? Process 55 acquiring U lock on KEY: 2: :2 (1e00fe36278c) Process 55 acquiring U lock on KEY: 2: :2 (1e00fe36278c) Нашел нужную запись по индексу Нашел нужную запись по индексу Process 55 acquiring S lock on RID: 2:1:28:0 Process 55 acquiring S lock on RID: 2:1:28:0 Взял fld из записи со страницы данных Взял fld из записи со страницы данных Process 55 acquiring U lock on RID: 2:1:28:0 Process 55 acquiring U lock on RID: 2:1:28:0 Собирается ее обновлять Собирается ее обновлять Process 55 acquiring X lock on RID: 2:1:28:0 Process 55 acquiring X lock on RID: 2:1:28:0 Поэтому продвинул до Х Поэтому продвинул до Х Process 55 releasing lock on KEY: 2: :2 (1e00fe36278c) Process 55 releasing lock on KEY: 2: :2 (1e00fe36278c) Отпустил индекс Отпустил индекс Process 56 acquiring U lock on KEY: 2: :2 (1e00fe36278c) Process 56 acquiring U lock on KEY: 2: :2 (1e00fe36278c) Аналогично пошел 2-й коннект Аналогично пошел 2-й коннект Process 56 sleeping for lock Process 56 sleeping for lock Теперь нужно читать данные, а запись залочена эксклюзивно. Естественно, ждет Теперь нужно читать данные, а запись залочена эксклюзивно. Естественно, ждет Process 55 acquiring U lock on KEY: 2: :2 (1e00fe36278c)... result: TIMEDOUT Process 55 acquiring U lock on KEY: 2: :2 (1e00fe36278c)... result: TIMEDOUT Это 2-й UPDATE с 1-го коннекта. Блокировки U несовместимы. Он ждет 2-й коннект, который, в свою очередь ждет 1-й. Поэтому... Это 2-й UPDATE с 1-го коннекта. Блокировки U несовместимы. Он ждет 2-й коннект, который, в свою очередь ждет 1-й. Поэтому... Process 56 acquiring S lock on RID: 2:1:28:0 (class bit0 ref1) result: DEADLOCK Process 56 acquiring S lock on RID: 2:1:28:0 (class bit0 ref1) result: DEADLOCK

24 В случае кластерного индекса spiddbidObjIdIndIdTypeResourceModeStatus TAB IXGRANT PAG1:30IXGRANT KEY( bd04)XGRANT KEY( bd04)XWAIT PAG1:30IXGRANT TAB IXGRANT Перед применением 2-го UPDATE на 1-м коннекте Перед применением 2-го UPDATE на 1-м коннекте Листовой уровень кластерного индекса – страницы данных, поэтому блокировка ключа означает блокировку записи Листовой уровень кластерного индекса – страницы данных, поэтому блокировка ключа означает блокировку записи 2-й коннект ждет, но разделения на блокировку RID и KEY, а следовательно и deadlock'a не происходит 2-й коннект ждет, но разделения на блокировку RID и KEY, а следовательно и deadlock'a не происходит Аналогично, deadlock не возникает и в случае некластерного индекса, если он является покрывающим Аналогично, deadlock не возникает и в случае некластерного индекса, если он является покрывающим Т.е. update t set id = id where id = 1 Т.е. update t set id = id where id = 1

25 Домашнее задание Проверить поведение при уровнях изоляции, отличных от read committed Проверить поведение при уровнях изоляции, отличных от read committed Т.е. READ UNCOMMITTED, REPEATABLE READ, SERIALIZABLE Т.е. READ UNCOMMITTED, REPEATABLE READ, SERIALIZABLE Инструменты Инструменты SET TRANSACTION ISOLATION LEVEL SET TRANSACTION ISOLATION LEVEL Запросные хинты: SELECT … FROM Tbl WITH (NOLOCK / READUNCOMMITTED, REPEATABLEREAD, HOLDLOCK / SERIALIZABLE) Запросные хинты: SELECT … FROM Tbl WITH (NOLOCK / READUNCOMMITTED, REPEATABLEREAD, HOLDLOCK / SERIALIZABLE) При необходимости индекс указывать напрямую При необходимости индекс указывать напрямую... FROM Tbl (index = ix)... FROM Tbl (index = ix) На всякий случай На всякий случай Расширенная диагностика блокировок – флаги Расширенная диагностика блокировок – флаги – всех хинтов блокировки 8755 – всех хинтов блокировки 8602 – игнорирование всех индексных хинтов 8602 – игнорирование всех индексных хинтов 8722 – всех остальных 8722 – всех остальных dbcc traceon(-1, флаги) – в масштабах сервера dbcc traceon(-1, флаги) – в масштабах сервера - То же самое, что запустить сервер с ключом -Т dbcc tracestatus(-1) показывает список включенных флагов dbcc tracestatus(-1) показывает список включенных флагов Не забыть 3604 для QA или 3605 для errorlog Не забыть 3604 для QA или 3605 для errorlog

26 Исследовательские инструменты sp_lock / select * from master..syslockinfo sp_lock / select * from master..syslockinfo Дает мгновенный снимок блокированных ресурсов на данный момент времени Дает мгновенный снимок блокированных ресурсов на данный момент времени Чтобы проследить наложение / освобождение блокировок в динамике Чтобы проследить наложение / освобождение блокировок в динамике dbcc traceon(3604, 1200) dbcc traceon(3604, 1200) Или через Profiler Или через Profiler - Программно – процедуры sp_trace_* - Проще всего определить трассу в профайлере, сказать Script Trace и при необходимости доработать скрипт руками - Сохранить в таблицу SQL Server программно нельзя. Выход – select * from ::fn_trace_gettable('c:\temp\MyTrace.trc', default) Демо: CreateDemoTraceForLocks.sql Демо: CreateDemoTraceForLocks.sql

27 Исследовательские инструменты Для блокировок профайлер не дает читабельного вывода Для блокировок профайлер не дает читабельного вывода Тип и координаты блокированного ресурса сокрыты внутри поля BinaryData Тип и координаты блокированного ресурса сокрыты внутри поля BinaryData Идентично rsc_bin в syslockinfo Идентично rsc_bin в syslockinfo aa bb cccc dddddddd eeee ffffffffffff aa bb cccc dddddddd eeee ffffffffffff aa - флаг aa - флаг bb - тип блокируемого ресурса bb - тип блокируемого ресурса - Ключ, Запись, Страница, Экстент,... - соотв-е - см. в BOL на колонку req_type в syslockinfo сссс - ID базы данных сссс - ID базы данных Если блокируемым ресурсом выступает Запись, Страница, Экстент, то Если блокируемым ресурсом выступает Запись, Страница, Экстент, то - dddddddd - номер страницы - eeee - номер файла БД - ffffffffffff - номер слота на странице Если блокируемым ресурсом выступает индексный Ключ, то Если блокируемым ресурсом выступает индексный Ключ, то - dddddddd - ObjectID - eeee - IndexID - ffffffffffff - хэш индексного ключа

28 Индексированные представления Обычные – по сути краткая форма записи SQL-запроса Обычные – по сути краткая форма записи SQL-запроса Который выполняется всякий раз, когда происходит обращение к представлению Который выполняется всякий раз, когда происходит обращение к представлению Естественно, влияет на производительность Естественно, влияет на производительность Индексированные содержат результаты запроса Индексированные содержат результаты запроса Которые автоматически обновляются, как только меняются данные в таблицах, на основе которых оно построено Которые автоматически обновляются, как только меняются данные в таблицах, на основе которых оно построено Дает выигрыш в производительности на сложных запросах Дает выигрыш в производительности на сложных запросах В которых много joinов, группировок, агрегатов,... В которых много joinов, группировок, агрегатов,... Т.е. на задачах, носящих, скорее, аналит.характер, нежели OLTP Т.е. на задачах, носящих, скорее, аналит.характер, нежели OLTP - П.ч. поддержание материализованного view сопряжено с доп.затратами (подобно индексам) Демо: IndexedView.sql Демо: IndexedView.sql

29 Требования к индексированным представлениям Довольно строгие, см.BOL -> Creating and Maintaining Databases -> Creating an Indexed View Довольно строгие, см.BOL -> Creating and Maintaining Databases -> Creating an Indexed View Requirements for the View и Requirements for the CREATE INDEX Statement Requirements for the View и Requirements for the CREATE INDEX Statement soft%20SQL%20Server\80\Tools\Books\cre atedb.chm::/cm_8_des_06_9jnb.htm soft%20SQL%20Server\80\Tools\Books\cre atedb.chm::/cm_8_des_06_9jnb.htm soft%20SQL%20Server\80\Tools\Books\cre atedb.chm::/cm_8_des_06_9jnb.htm soft%20SQL%20Server\80\Tools\Books\cre atedb.chm::/cm_8_des_06_9jnb.htm

30 Индексированные представления и OLAP реального времени Демо Демо На стороне Analysis Services необходимо соблюдать дополнительные условия На стороне Analysis Services необходимо соблюдать дополнительные условия См. BOL -> Analysis Services -> Indexed Views for ROLAP Partitions См. BOL -> Analysis Services -> Indexed Views for ROLAP Partitions soft%20SQL%20Server\80\Tools\Books\ola pdmad.chm::/agadvpart_3jzn.htm soft%20SQL%20Server\80\Tools\Books\ola pdmad.chm::/agadvpart_3jzn.htm soft%20SQL%20Server\80\Tools\Books\ola pdmad.chm::/agadvpart_3jzn.htm soft%20SQL%20Server\80\Tools\Books\ola pdmad.chm::/agadvpart_3jzn.htm

31 Исследовательские инструменты sessionproperty(), objectproperty(), sp_dboption, sp_configure 'user options' sessionproperty(), objectproperty(), sp_dboption, sp_configure 'user options'

32 Вложенные и коррелированные подзапросы Задача: таблица Order Details содержит расшифровку каждой покупки в отдельные товары Задача: таблица Order Details содержит расшифровку каждой покупки в отдельные товары Вывести все товары, количество которых в покупке, превышает средний объем покупки Вывести все товары, количество которых в покупке, превышает средний объем покупки

33 Вложенные и коррелированные подзапросы Очевидные способы решения: Очевидные способы решения: select [outer].* from [Order Details] [outer] where [outer].Quantity > select [outer].* from [Order Details] [outer] where [outer].Quantity > (select avg([inner].Quantity) from [Order Details] [inner] where [inner].OrderID = [outer].OrderID) where [inner].OrderID = [outer].OrderID) select [Order Details].* from [Order Details], (select OrderID, avg(Quantity) avg_quantity from [Order Details] group by OrderID) avg_q_by_order where [Order Details].OrderID = avg_q_by_order.OrderID and [Order Details].Quantity > avg_q_by_order.avg_quantity select [Order Details].* from [Order Details], (select OrderID, avg(Quantity) avg_quantity from [Order Details] group by OrderID) avg_q_by_order where [Order Details].OrderID = avg_q_by_order.OrderID and [Order Details].Quantity > avg_q_by_order.avg_quantity

34 Вложенные и коррелированные подзапросы В первом случае вложенный подзапрос должен выполняться для каждой записи внешнего запроса В первом случае вложенный подзапрос должен выполняться для каждой записи внешнего запроса Во втором – сначала считается временный результат средних по каждой покупке, который затем джойнится с исходной таблицей Во втором – сначала считается временный результат средних по каждой покупке, который затем джойнится с исходной таблицей Поэтому второй запрос, очевидно, эффективнее Поэтому второй запрос, очевидно, эффективнее Смотрим планы выполнения: Смотрим планы выполнения:

35 Вложенные и коррелированные подзапросы Они одинаковы! Они одинаковы! Т.е. заведомо нерациональный первый запрос приводится к оптимальному варианту: предварительному группированию по OrderID Т.е. заведомо нерациональный первый запрос приводится к оптимальному варианту: предварительному группированию по OrderID Что еще раз подтверждает тезис, высказанный в начале презентации: процессор запросов SQL Servera достаточно смартов, чтобы оптимизировать кривые запросы Что еще раз подтверждает тезис, высказанный в начале презентации: процессор запросов SQL Servera достаточно смартов, чтобы оптимизировать кривые запросы Если, конечно, не подсовывать ему рельсу Если, конечно, не подсовывать ему рельсу

36 У меня не получилось добиться от оптимизатора Oracle 9i аналогичной сообразительности У меня не получилось добиться от оптимизатора Oracle 9i аналогичной сообразительности Из запросов Из запросов select outer.* from emp outer where outer.sal > (select avg(inner.sal) from emp inner where inner.deptno = outer.deptno); select outer.* from emp outer where outer.sal > (select avg(inner.sal) from emp inner where inner.deptno = outer.deptno); select emp.* from emp, (select deptno, avg(sal) avg_sal from emp group by deptno) davg_sal where emp.deptno = davg_sal.deptno and emp.sal > davg_sal.avg_sal; select emp.* from emp, (select deptno, avg(sal) avg_sal from emp group by deptno) davg_sal where emp.deptno = davg_sal.deptno and emp.sal > davg_sal.avg_sal; первый (коррелированный) всегда выполнялся вложенным циклом без попыток оптимизировать его ко второму виду (вложенному) первый (коррелированный) всегда выполнялся вложенным циклом без попыток оптимизировать его ко второму виду (вложенному) И Oracle, и SQL Server были просто поставлены на чистые партиции И Oracle, и SQL Server были просто поставлены на чистые партиции Ни там, ни там ничего специально не настраивалось Ни там, ни там ничего специально не настраивалось Вложенные и коррелированные подзапросы

37 Оптимизация распределенных запросов Ремоутинг фильтра Ремоутинг фильтра Есть Srv0, прилинкованный на Srv1 и Srv2 Есть Srv0, прилинкованный на Srv1 и Srv2 На них выполняется: На них выполняется: - update Srv0.Northwind.dbo.Products set ProductName = 'Chang' where ProductId = 2 На Srv2 в разы медленнее Srv1 На Srv2 в разы медленнее Srv1 - Куда смотрим и что крутим?

38 Смотрим, естественно, планы и видим: Смотрим, естественно, планы и видим: Srv1: Srv1: Remote Update |--Table Spool |--Remote Scan(select * from Northwind.dbo.Products where ProductID = 2) Remote Update |--Table Spool |--Remote Scan(select * from Northwind.dbo.Products where ProductID = 2) Srv2: Srv2: Remote Update |--Compute Scalar(DEFINE:([Expr1004]='Chang')) |--Table Spool |-- Filter(WHERE:([Srv0].[Northwind].[dbo].[Produc ts].[ProductID]=2)) |--Remote Scan("Northwind"."dbo"."Products") Remote Update |--Compute Scalar(DEFINE:([Expr1004]='Chang')) |--Table Spool |-- Filter(WHERE:([Srv0].[Northwind].[dbo].[Produc ts].[ProductID]=2)) |--Remote Scan("Northwind"."dbo"."Products") Т.е. Srv2 тянет к себе всю таблицу c Srv0 Т.е. Srv2 тянет к себе всю таблицу c Srv0 Почему так происходит и как с этим бороться? Почему так происходит и как с этим бороться? Оптимизация распределенных запросов

39 Возможно, HKLM\SOFTWARE\Microsoft\MSSQLServer\Providers\SQ LOLEDB на Srv2 имеет LevelZeroOnly = 1 Возможно, HKLM\SOFTWARE\Microsoft\MSSQLServer\Providers\SQ LOLEDB на Srv2 имеет LevelZeroOnly = 1 Этот уровень запрещает ремоутинг фильтра Этот уровень запрещает ремоутинг фильтра Если фильтр по полю char / varchar, то, возможно, Srv0 не отмечен на Srv2 как collation compatible Если фильтр по полю char / varchar, то, возможно, Srv0 не отмечен на Srv2 как collation compatible sp_serveroption sp_serveroption Переписать запрос так: Переписать запрос так: update openquery(Srv0, 'select * from Northwind.dbo.Products where ProductID = 2') set ProductName= 'Chang2' update openquery(Srv0, 'select * from Northwind.dbo.Products where ProductID = 2') set ProductName= 'Chang2' Если не SQLOLEDB, проверить возможности провайдера Если не SQLOLEDB, проверить возможности провайдера Поддерживает ли LIKE, DateLiteral, UnicodeLiteral в фильтре Поддерживает ли LIKE, DateLiteral, UnicodeLiteral в фильтре Возможно, Aggregation – если фильтр HAVING Возможно, Aggregation – если фильтр HAVING Или это вообще Scan only provider Или это вообще Scan only provider Проверяется включением соотв.флагов – см.КВ Проверяется включением соотв.флагов – см.КВ Оптимизация распределенных запросов

40 Ремоутинг джойна Ремоутинг джойна select r.CustomerID from Northwind.dbo.Orders l inner join Remote.Northwind.dbo.Orders r on l.OrderID = r.OrderID where l.RequiredDate >= '6/10/1998 select r.CustomerID from Northwind.dbo.Orders l inner join Remote.Northwind.dbo.Orders r on l.OrderID = r.OrderID where l.RequiredDate >= '6/10/1998 Это хороший план: Это хороший план: - |--Nested Loops(Inner Join) |--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders ] AS l), WHERE:(l.[RequiredDate]>=Jun :00AM)) |--Remote Query(SELECT r. "CustomerID" Col1003 FROM "Northwind"."dbo"."Orders" r WHERE r."orderid"=?) Параметризует удаленную часть запроса по колонке joina и выполняет его для каждого локального значения Параметризует удаленную часть запроса по колонке joina и выполняет его для каждого локального значения Оптимизация распределенных запросов

41 Это плохой план: Это плохой план: - |--Merge Join(Inner Join, MERGE:(r.[Northwind].[dbo].[Orders].[OrderID]) =(l.[OrderID]),...) |--Remote Query(SELECT r."CustomerID" Col1010, r."OrderID" Col1009 FROM "Northwind"."dbo"."Orders" r ORDER BY r."OrderID" ASC) |--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Or ders] AS l), WHERE:(l.[RequiredDate]>=Jun :00AM) ORDERED) Копирует удаленную таблицу к себе и джойнит локально Копирует удаленную таблицу к себе и джойнит локально Почему это может происходить? Почему это может происходить? Оптимизация распределенных запросов =Jun 10 1998 12:00AM) ORDERED) Копирует удаленную таблицу к себе и джойнит локально Копирует удаленную таблицу к себе и джойнит локально Почему это может происходить? Почему это может происходить? Оптимизация распределенных запросов">

42 Возможно, провайдер удаленного сервера не параметризует запрос Возможно, провайдер удаленного сервера не параметризует запрос HKLM\SOFTWARE\Microsoft\MSSQLServer\Providers\Провайдер, св-во DynamicParameters HKLM\SOFTWARE\Microsoft\MSSQLServer\Providers\Провайдер, св-во DynamicParameters Проверяется dbcc traceon(8760) Проверяется dbcc traceon(8760) Возможно, провайдер не предоставляет статистики Возможно, провайдер не предоставляет статистики На данный момент умеют SQLOLEDB и MSDAORA На данный момент умеют SQLOLEDB и MSDAORA Возможно, локальный сервер ее не потребляет Возможно, локальный сервер ее не потребляет Умеет SQL 2К, не умеет SQL 7.0 Умеет SQL 2К, не умеет SQL 7.0 Возможно, на основе статистики он действительно решил, что копирнуть быстрее Возможно, на основе статистики он действительно решил, что копирнуть быстрее Забить на статистику и вязать удаленно – хинт REMOTE Забить на статистику и вязать удаленно – хинт REMOTE SELECT * FROM tbl1 INNER REMOTE JOIN tbl2 ON... SELECT * FROM tbl1 INNER REMOTE JOIN tbl2 ON... Автоматом включает хинт FORCE ORDER Автоматом включает хинт FORCE ORDER Не работает в случае OUTER JOIN и CROSS JOIN Не работает в случае OUTER JOIN и CROSS JOIN Самая правая таблица д.б. удаленной, иначе локально Самая правая таблица д.б. удаленной, иначе локально Оптимизация распределенных запросов

43 Связь remote – remote Связь remote – remote Проверяем опции провайдера Проверяем опции провайдера Inner join – флаг 9123 Inner join – флаг 9123 Subquery Subquery Union support Union support NestedQueries - ? NestedQueries - ? - П.ч. может разрешаться через вложенный запрос Оптимизация распределенных запросов

44 Дополнительные ресурсы Российский веб-сервер компании Microsoft Российский веб-сервер компании Microsoft Microsoft TechNet Microsoft TechNet Партнёры Microsoft rtified_partners/ Партнёры Microsoft rtified_partners/ rtified_partners/ rtified_partners/ Обучение и сертификация Обучение и сертификация Некоммерческий веб-сервер по MS SQL Server и клиент-серверным технологиям Некоммерческий веб-сервер по MS SQL Server и клиент-серверным технологиям Дискуссии Дискуссии Рассылка "MS SQL Server - дело тонкое..." Рассылка "MS SQL Server - дело тонкое..." Платформа Microsoft.NET Платформа Microsoft.NET

45 Смотрите в следующей серии:

46 Вопросы?

47