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

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

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

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

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

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

6. Исследовательские инструменты • 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.

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

8. OPTION (FASTN): пример • Разница заметна даже на небольших таблицах • 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

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

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

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

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

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

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

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

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

17. Одно распространенное заблуждение • 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

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

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

20. Индексы и блокировки • Демо: 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 с индексных страниц

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

22. Тип индекса и блокировки • Слегка разнообразим предыдущий пример • Есть некластерный индекс • 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

23. Почему так случилось? • 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

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

25. Домашнее задание • Проверить поведение при уровнях изоляции, отличных от 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

26. Исследовательские инструменты • 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

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

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

29. Требования к индексированным представлениям • Довольно строгие, см.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

30. Индексированные представления и 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

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

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

33. Вложенные и коррелированные подзапросы • Очевидные способы решения: • 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_orderwhere [Order Details].OrderID = avg_q_by_order.OrderID and [Order Details].Quantity > avg_q_by_order.avg_quantity

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

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

36. Вложенные и коррелированные подзапросы • У меня не получилось добиться от оптимизатора 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_salwhere emp.deptno = davg_sal.deptno and emp.sal > davg_sal.avg_sal; • первый (коррелированный) всегда выполнялся вложенным циклом без попыток оптимизировать его ко второму виду (вложенному) • И Oracle, и SQL Server были просто поставлены на чистые партиции • Ни там, ни там ничего специально не настраивалось

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

38. Оптимизация распределенных запросов • Смотрим, естественно, планы и видим: • 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 • Почему так происходит и как с этим бороться?

39. Оптимизация распределенных запросов • Возможно, 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 • Проверяется включением соотв.флагов – см.КВ

40. Оптимизация распределенных запросов • Ремоутинг джойна • select r.CustomerID from Northwind.dbo.Orders linner 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 и выполняет его для каждого локального значения

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

42. Оптимизация распределенных запросов • Возможно, провайдер удаленного сервера не параметризует запрос • 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 • Самая правая таблица д.б. удаленной, иначе локально

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

44. Дополнительные ресурсы • Российский веб-сервер компании Microsofthttp://www.microsoft.com/rus • Microsoft TechNet http://www.microsoft.com/rus/technet/ • Партнёры Microsofthttp://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/

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

46. Вопросы?