Создание оптимального кода на стороне SQL Server:Transact-SQL


The Presentation inside:

Slide 0

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


Slide 1

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


Slide 2

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


Slide 3

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


Slide 4

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


Slide 5

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


Slide 6

Исследовательские инструменты set showplan_all


Slide 7

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


Slide 8

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


Slide 9

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


Slide 10

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


Slide 11

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


Slide 12

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


Slide 13

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


Slide 14

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


Slide 15

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


Slide 16

Одно распространенное заблуждение 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 -------- ------- ------ ------ ----------- ------- -------- ---------- ----------- ----------- ----------- ----------- 1 41 NULL NULL 2057058364 1 10 0 0 0 0 0 1 28 1 41 2057058364 0 1 0 1 29 1 210 1 29 1 41 2057058364 0 1 0 1 30 1 28 1 30 1 41 2057058364 0 1 0 1 31 1 29 1 31 1 41 2057058364 0 1 0 1 33 1 30 1 33 1 41 2057058364 0 1 0 1 34 1 31 1 34 1 41 2057058364 0 1 0 1 35 1 33 1 35 1 41 2057058364 0 1 0 1 36 1 34 1 36 1 41 2057058364 0 1 0 1 172 1 35 1 112 1 41 2057058364 0 1 0 1 113 1 194 ... На физически первой странице dbcc page('test', 1, 28, 3) уже лежат отнюдь не id = 1, 2: PAGE: (1:28) ------------ ... 441DEE00: 20202020 20202020 20202020 20202020 441DEE10: 20202020 01000002 010dbf00 000000 ........... id = 100 fld = bbb ... 441DFBBF: 20202020 20202020 20202020 20202020 441DFBCF: 20202020 000002 ... id = 101 fld = bbb --------------------------------------------------------------------------- Демо: ClusteredIndex.sql


Slide 17

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


Slide 18

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


Slide 19

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


Slide 20

Что происходит при наличии индекса


Slide 21

Тип индекса и блокировки Слегка разнообразим предыдущий пример Есть некластерный индекс create nonclustered index ix on t(id) На 1-м соединении: begin tran update t set fld = fld where id = 1 На 2-м соединении: update t set fld = fld where id = 1 На 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


Slide 22

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


Slide 23

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


Slide 24

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


Slide 25

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


Slide 26

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


Slide 27

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


Slide 28

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


Slide 29

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


Slide 30

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


Slide 31

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


Slide 32

Вложенные и коррелированные подзапросы Очевидные способы решения: select [outer].* from [Order Details] [outer] where [outer].Quantity > (select avg([inner].Quantity) from [Order Details] [inner] 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


Slide 33

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


Slide 34

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


Slide 35

У меня не получилось добиться от оптимизатора Oracle 9i аналогичной сообразительности Из запросов 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; первый (коррелированный) всегда выполнялся вложенным циклом без попыток оптимизировать его ко второму виду (вложенному) И Oracle, и SQL Server были просто поставлены на чистые партиции Ни там, ни там ничего специально не настраивалось Вложенные и коррелированные подзапросы


Slide 36

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


Slide 37

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


Slide 38

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


Slide 39

Ремоутинг джойна 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 10 1998 12:00AM)) |--Remote Query(SELECT r. "CustomerID" Col1003 FROM "Northwind"."dbo"."Orders" r WHERE r."orderid"=?) Параметризует удаленную часть запроса по колонке join’a и выполняет его для каждого локального значения Оптимизация распределенных запросов


Slide 40

Это плохой план: |--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_Orders] AS l), WHERE:(l.[RequiredDate]>=Jun 10 1998 12:00AM) ORDERED) Копирует удаленную таблицу к себе и джойнит локально Почему это может происходить? Оптимизация распределенных запросов


Slide 41

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


Slide 42

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


Slide 43

Дополнительные ресурсы Российский веб-сервер компании Microsoft http://www.microsoft.com/rus Microsoft TechNet http://www.microsoft.com/rus/technet/ Партнёры Microsoft http://www.microsoft.com/rus/licensing/where/certified_partners/ Обучение и сертификация http://www.microsoft.com/rus/ctec/ Некоммерческий веб-сервер по MS SQL Server и клиент-серверным технологиям http://www.sql.ru Дискуссии http://www.sql.ru/forum Рассылка "MS SQL Server - дело тонкое..." http://www.sql.ru/subscribe/ Платформа Microsoft .NET http://www.gotdotnet.ru/


Slide 44

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


Slide 45

Вопросы?


Slide 46


×

HTML:





Ссылка: