Повышение производительности запроса

Создаем и применяем кластеризованные индексы

Запустите SQL Server Management Studio. Откройте окно New Query (Создать запрос) и измените контекст базы данных на Adventure Works.

Для создания уникального кластеризованного индекса в таблице Orders ведите и выполните следующую инструкцию. Код этого примера имеется в файлах примеров под именем Creating And Using Clustered Indexes.sql.

CREATE UNIQUE CLUSTERED INDEX CLIDX_Orders_SalesOrderID 
    ON dbo.Orders(SalesOrderID)

Теперь выполните эти же две инструкции SELECT, как делали это раньше, и изучите различия. Обязательно включите при выполнении запроса действительный план выполнения.

SET STATISTICS IO ON;

SELECT * FROM dbo.Orders;
SELECT * FROM dbo.Orders 
  WHERE SalesOrderID =46699;

SET STATISTICS IO OFF;

Перейдите на вкладку Execution Plan (План выполнения).

Мы видим, что SQL Server больше не использует просмотры таблицы. Теперь он выполняет операции индексирования, потому что данные больше не хранятся в структуре кучи. План выполнения запроса показывает, что в данном случае используются две основные операции с индексами из всех возможных:

Просмотр индекса.Просмотр уровня листовых вершин индекса со считыванием данных таблицы. Поскольку первая инструкция SELECT не имеет предложения WHERE, серверу SQL Server известно, что нужно возвратить все данные, которые хранятся на уровне листовых вершин индекса.

Поиск по индексу.Операция, при которой SQL Server выполняет поиск определенного значения, проходя по ветвям индекса, начиная от корневой вершины индекса.

Эти две операции могут также соединяться, чтобы извлечь определенный диапазон данных. В этом виде операции частичного просмотра SQL Server пытается найти начало диапазона, а затем просматривает дерево до конца этого диапазона.

Перейдите на вкладку Messages (Сообщения), как показано ниже.

Мы видим, что первая инструкция SELECT генерирует почти тот же объем считываний страниц, что и операция просмотра таблицы, если у нее структура кучи. Это не удивительно, поскольку инструкция SELECT требует все данные, и, следовательно, SQL Server должен возвратить все данные. Но второй запрос генерирует только два считывания страниц, а это значительное улучшение по сравнению с теми 178 считываниями страниц, которые были показаны раньше. SQL Server необходимо только выполнить поиск по индексу, что требует гораздо меньшего количества операций ввода/вывода, чем поиск в каждой странице данных.

Введите следующую инструкцию SELECT, которая возвращает данные в отсортированной форме, и нажмите (Ctrl+L), чтобы получить предполагаемый план выполнения.

SELECT * FROM dbo.Orders ORDER BY SalesOrderID;
SELECT * FROM dbo.Orders ORDER BY OrderDate;

Из рисунка видно, что первая инструкция выполняет только просмотр кластеризованного индекса и не сортирует данные. Причина заключается в том, что эти данные уже отсортированы по SalesOrderID, потому что этот столбец является ключом кластеризованного индекса. Следовательно, серверу SQL Server для того, чтобы получить строки в правильном порядке, остается только просмотреть данные на уровне листовых вершин и возвратить результат.

Во втором запросе данные были отсортированы после получения. Следовательно, после операции просмотра кластеризованного индекса имела место операция Sort (Сортировка), которая отсортировала данные по столбцу OrderDate. Поскольку сортировка является очень дорогостоящей операцией, второй запрос генерирует 93% общей стоимости обоих запросов. Таким образом, имеет смысл определять кластеризованный индекс в столбцах, которые часто используются в качестве аргумента сортировки или группировки критериев в агрегате, поскольку агрегация данных требует, чтобы SQL Server сначала сортировал данные в соответствии с критериями группировки.

Теперь создадим составной индекс в таблице OrderDetails. Составной индекс - это индекс, который определен более, чем для одного столбца. Ключи индекса сортируются сначала по первому ключевому столбцу индекса, затем по второму и т. д. Такой индекс полезен в тех случаях, когда два или более столбца используются совместно в качестве аргументов поиска в запросах или когда уникальность строки определена через несколько столбцов.

Создаем составной кластеризованный индекс

CREATE UNIQUE CLUSTERED INDEX CLIDX_OrderDetails 
  ON dbo.OrderDetails(SalesOrderID,SalesOrderDetailID)

SELECT * FROM dbo.OrderDetails 
   WHERE SalesOrderID = 46999 
SELECT * FROM dbo.OrderDetails 
   WHERE SalesOrderDetailID = 14147

Легко заметить, что в первом запросе, который выполняет поиск значения из первого столбца составного индекса, SQL Server для поиска строки использует поиск по индексам (метод seek). Во втором запросе он использует просмотр индекса, который является более дорогостоящей операцией. Просмотр индекса используется потому, что невозможно искать значение только во втором столбце составного индекса, ведь индекс изначально сортируется по первому столбцу. Следовательно, важно продумать порядок индексных столбцов в составном индексе. Запомните, что составной индекс следует использовать только тогда, когда поиск по дополнительным столбцам выполняется исключительно в сочетании с первым столбцом или если должна быть применена уникальность.

Некластеризованные индексы

В противоположность кластеризованным индексам некластеризованные индексы не содержат всех строк данных на уровне листовых вершин индекса. Вместо этого на уровне листовых вершин хранятся все ключевые столбцы и указатели на строки в таблице. Использование и запись указателей зависит от того, является ли базовая таблица кучей или имеет кластеризованный индекс.

CREATE [ UNIQUE ] NONCLUSTERED INDEX index name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] )

Создаем и применяем некластеризованные индексы

Запустите SQL Server Management Studio. Откройте окно New Query (Создать запрос) и измените контекст базы данных на Adventure Works.

Введите следующую инструкцию SELECT и нажмите клавиатурную комбинацию (Ctrl+L), чтобы отобразить предполагаемый план выполнения. Код этого примера имеется в файлах примеров под именем Creating And Using Nonclustered Indexes.sql.

SELECT DISTINCT SalesOrderID, CarrierTrackingNumber 
  FROM dbo.OrderDetails 
  WHERE ProductID = 776

SQL Server выполняет операцию просмотра кластеризованного индекса, потому что в таблице OrderDetails нет индекса. Чтобы ускорить выполнение этого запроса, SQL Server потребуется индекс в столбце ProductID. Поскольку кластеризованный индекс в таблице OrderDetails уже определен, приходится использовать некластеризованный индекс.

Примечание. Операция Sort (Сортировка) применяется в этом плане выполнения запроса для получения неповторяющегося результата.

Чтобы создать некластеризованный индекс в столбце Product ID таблицы OrderDetail, введите и выполните следующую инструкцию:

CREATE INDEX NCLIX_OrderDetails_ProductID 
  ON dbo.OrderDetails(ProductID)

Введите предыдущую инструкцию SELECT и нажмите клавиатурную комбинацию (Ctrl+L), чтобы отобразить предполагаемый план выполнения.

SELECT DISTINCT SalesOrderID, CarrierTrackingNumber 
  FROM dbo.OrderDetails 
  WHERE ProductID = 776

Если вы задержите указатель мыши над оператором IndexSeek, то увидите, что SQL Server выполняет поиск по индексу (метод seek) в таблице NCLIX_OrderDetails_ProductID, чтобы извлечь указатели на нужные записи. Поскольку в этой таблице существует кластеризованный индекс, SQL Server извлекает список ключей кластеризации в качестве указателей. Этот список передается на вход оператора Nested Loops (Вложенные циклы), который является разновидностью оператора Join (Соединение) (соединения будут рассмотрены далее в этой лекции). Оператор Nested Loops использует поиск (метод seek ) по кластеризованному индексу, чтобы возвратить нужные строки данных, которые затем переходят к оператору Sort (Сортировка), чтобы исключить повторяющиеся значения в результате. Так SQL Server извлекает строки при помощи некластеризованного индекса, если существует кластеризованный индекс.

Теперь посмотрим, как SQL Server осуществляет доступ к данным, когда существует некластеризованный индекс в таблице, не имеющей кластеризованного индекса. Введите и выполните следующую инструкцию DROP INDEX, чтобы удалить из таблицы OrderDetails кластеризованный индекс.

DROP INDEX OrderDetails.CLIDX_OrderDetails

Введите предыдущую инструкцию SELECT и нажмите клавиатурную комбинацию (Ctrl+L), чтобы отобразить предполагаемый план выполнения.

SELECT DISTINCT SalesOrderID, CarrierTrackingNumber 
  FROM dbo.OrderDetails 
  WHERE ProductID = 776

Мы видим, что в этом случае SQL Server использует оператор RID Lookup, потому что указатели, которые SQL Server получил в результате поиска по индексу (метод seek), представляют собой указатели на физические строки данных, а не на ключи кластеризации. Оператор RID Lookup - это оператор, который используется в SQL Server для извлечения данных непосредственно со страницы.

Введите и выполните следующую инструкцию, чтобы снова создать кластеризованный индекс.

CREATE UNIQUE CLUSTERED INDEX CLIDX_OrderDetails 
  ON dbo.OrderDetails(SalesOrderID,SalesOrderDetailID)

Использование покрывающих индексов

Не всегда нужно, чтобы при использовании некластеризованных индексов SQL Server на втором этапе извлекал всю строку. Эта ситуация возникает, когда некластеризованный индекс включает все данные таблицы, которые нужно SQL Server для выполнения операции. Когда это происходит, мы называем индекс покрывающим, потому что этот индекс покрывает весь запрос. Покрывающие индексы могут существенно увеличить производительность запроса; в этом легко убедиться на двух планах запросов из предыдущего примера. В таких запросах стоимость операторов, которые извлекают нужные строки данных, составляет 97% всей стоимости запроса. Другими словами, запрос без этой операции будет выполнен в 32 раза быстрее. Давайте рассмотрим, как работают покрывающие индексы.

Применяем покрывающие индексы

Запустите SQL Server Management Studio, Откройте окно New Query (Создать запрос) и измените контекст базы данных на Adventure Works.

Идея покрывающих индексов заключается в том, что они содержат все данные, необходимые для выполнения запросов. Если мы посмотрим на первый из следующих запросов, который уже использовался в предыдущем примере, то увидим, что SQL Server нужны столбцы SalesOrderID, CarrierTrackingNumber и ProductID.

Некластеризованный индекс NCLIX_OrderDetails_ProductID, который мы создали ранее, включает столбец ProductID, поскольку он построен на этом столбце, а также столбец SalesOrderID, поскольку этот столбец является ключевым столбцом кластеризованного индекса. Поэтому SalesOrderID является указателем, который SQL Server использует в некластеризованном индексе. Следовательно, серверу SQL Server, чтобы получить CarrierTrackingNumber, нужно возвратить строки данных, выполнив поиск (метод seek) только по кластеризованному индексу. Во втором запросе столбца CarrierTrackingNumber нет в списке SELECT. Введите и выполните инструкцию, включив действительный план выполнения, чтобы увидеть разницу. Код этого примера имеется в файлах примеров под именем Using Covered Indexes.sql.

SET STATISTICS IO ON
-не покрывающий
SELECT DISTINCT SalesOrderID, CarrierTrackingNumber
  FROM dbo.OrderDetails
  WHERE ProductID = 776 
-покрывающий 
SELECT DISTINCT SalesOrderID
  FROM dbo.OrderDetails
  WHERE ProductID = 776 
SET STATISTICS IO OFF

На рисунке, показанном ниже, видно, что серверу SQL Server для выполнения второго запроса нужно обратиться к кластеризованному индексу, потому что индекс покрывает запрос, если столбец CarrierTrackingNumber не выбран. Поскольку доступ к кластеризованному индексу для каждой строки очень дорого стоит, второй запрос составляет только 1% от общей стоимости пакета. Посмотрев на вкладку Messages (Сообщения), мы видим, что для того,. чтобы покрыть запрос, SQL Server нужно выполнить только 2 считывания страницы (вместо 709 считываний, необходимых для первого запроса).

Вы убедились, что покрывающий индекс может дать большой выигрыш в скорости выполнения. Конечно, невозможно удалить столбцы из запроса, если они нужны в результате. Но по общему правилу следует извлекать только те столбцы, которые вам действительно нужны, чтобы сделать более вероятным применение покрывающего индекса. Не следует использовать в инструкции SELECT символ звездочки (*) только потому, что так проще составить запрос.

Предположим, что столбец CarrierTrackingNumber нужен в запросе, но из соображений производительности следует использовать покрывающий индекс. В SQL Server 2005 можно включить этот столбец в некластеризованный индекс. Включенные столбцы сохраняются в ключах индекса на уровне листовых вершин некластеризованного индекса, что устраняет необходимость извлекать их из кластеризованного индекса. Чтобы включить столбец CarrierTrackingNumber в некластеризованный индекс, введите и выполните следующую инструкцию для удаления (DROP) индекса и повторного создания ( CREATE ) индекса с включенным столбцом.

DROP INDEX dbo.OrderDetails.NCLIX_OrderDetails_ProductID

CREATE INDEX NCLIX_OrderDetails_ProductID 
  ON dbo.OrderDetails(ProductID) 
  INCLUDE (CarrierTrackingNumber)

Выполните первый неохваченный запрос, чтобы посмотреть, будет ли он охвачен теперь.

SET STATISTICS IO ON
SELECT DISTINCT SalesOrderID, CarrierTrackingNumber
  FROM dbo.OrderDetails
  WHERE ProductID = 776 
SET STATISTICS IO OFF

Взглянув на план выполнения, вы увидите, что доступ к кластеризованному индексу больше не нужен. На вкладке сообщений видно, что для выполнения этого запроса теперь требуется только 5 считываний страниц, тогда как раньше требовалось 709.

Примечание. Включенные столбцы вызывают перегрузку сервера SQL Server при изменении данных, потому что SQL Server приходится изменять каждый индекс и еще потому, что для их хранения требуется больше места в файлах данных. Следовательно, включение столбцов в некластеризованные индексы - это хороший способ повышения производительности запросов, но не следует создавать индексы с включением столбцов для всех запросов в приложении. Эту функцию следует использовать исключительно для ускорения выполнения проблемных запросов.

Индексы в вычисляемых столбцах

В таблице dbo.OrderDetails у нас есть вычисляемый столбец LineTotal, который представляет значение LineTotal для строки и вычисляется по следующей формуле:

(isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0)))

При каждом доступе к этому столбцу SQL Server вычисляет значения на основе значений оригинальных строк, на которые ссылается формула. Этот процесс не представляет собой проблемы до тех пор, пока столбец LineTotal используется в предложении SELECT, но если использовать LineTotal в предикате поиска предложения WHERE или в агрегатных функциях, например, MAX или MIN, такая проблема может возникнуть. Если вычисляемые столбцы используются для поиска, SQL Server должен вычислять значения для каждой строки в таблице, и только потом искать в результатах нужные строки. Это очень неэффективный процесс, потому что здесь всегда требуется просмотр таблицы или полный просмотр кластеризованного индекса. Для этих видов запросов можно создать индексы в вычисляемых столбцах. Когда в вычисляемом столбце создан индекс, SQL Server вычисляет результат заранее и создает по нему индекс.

Создаем и используем индексы в вычисляемых столбцах

Запустите SQL Server Management Studio. Откройте окно New Query (Создать запрос) и измените контекст базы данных на Adventure Works.

Представьте себе, что вы хотите возвратить все значения SalesOrderID s, для которых LineTotal имеет определенную величину. Введите следующий запрос и нажмите (Ctrl+L), чтобы отобразить предполагаемый план выполнения для случая, когда в вычисляемом столбце не существует опорного индекса. Как видите, SQL Server должен выполнить просмотр кластеризованного индекса, вычислить значения в столбце и отфильтровать эти значения до нужных строк. Код этого примера можно найти среди файлов примеров под именем IndexesOnComputedColumns.sql.

SELECT SalesOrderID
FROM OrderDetails
WHERE LineTotal = 27893.619

Введите и выполните следующую инструкцию CREATE INDEX, чтобы создать индекс в вычисляемом столбце:

CREATE NONCLUSTERED INDEX NCL_OrderDetail_LineTotal ON dbo.OrderDetails(LineTotal)

Выделите первый запрос и нажмите (Ctrl+L), чтобы отобразить новый план выполнения запроса с индексом в вычисляемом столбце. Как показано ниже, SQL Server теперь использует вновь созданный индекс для извлечения данных, что повышает скорость выполнения запроса по сравнению с предыдущим случаем.

SELECT SalesOrderID
  FROM OrderDetails
  WHERE LineTotal = 27893.619

Закройте окно среды SQL Server Management Studio.

Индексы в столбцах XML

SQL Server 2005 имеет собственный тип данных XML. Экземпляры XML в столбцах типа данных XML хранятся как большие двоичные объекты (BLOB) и могут иметь размер до 2 Гбайт на каждый экземпляр. Для запросов к XML-данным можно использовать язык XQuery, но такие запросы к столбцам с типом данных XML без индекса могут занимать много времени. Это особенно справедливо для больших экземпляров XML, поскольку SQL Server должен разбирать большие двоичные объекты, содержащие XML, в процессе выполнения рабочего цикла оценки запроса. Чтобы повысить производительность запросов к столбцам с типом данных XML, столбцы XML можно индексировать. XML-индексы делятся на две категории: первичные XML-индексы и вторичные XML-индексы.

Создание и использование первичных XML-индексов

Первый индекс, который следует создать в столбце XML - это первичный XML-индекс. При создании этого индекса SQL Server разбирает XML-содержимое и создает несколько строк данных, которые включают такую информацию, как имя элемента и атрибута, путь к корневому узлу, тип узла и значения и т. д. Благодаря этой информации SQL Server будет гораздо проще поддерживать запросы XQuery. Чтобы создать первичный XML-индекс, в базовой таблице должен существовать первичный ключ с кластеризованным индексом. Ниже приводится синтаксическая конструкция для создания первичного XML-индекса:

CREATE PRIMARY XML INDEX index_name 
  ON <object> ( xml_column_name )

Создаем первичный XML-индекс

CREATE TABLE dbo.Products( ProductID int NOT NULL, 
             Name dbo.Name NOT NULL, CatalogDescription xml NULL,
CONSTRAINT PK_ProductModel_ProductID PRIMARY KEY CLUSTERED ( ProductID ));
INSERT INTO dbo.Products (ProductID,Name,CatalogDescription) 
SELECT ProductModelID, Name, CatalogDescription 
  FROM Production.ProductModel;

CREATE PRIMARY XML INDEX PRXML_Products_CatalogDesc 
  ON dbo.Products (CatalogDescription);

WITH XMLNAMESPACES
  ("http://schemas.microsoft.com/sqlserver/2004/07/adventure-works /ProductModelDescription'
    AS "PD")
SELECT ProductID, CatalogDescription
  FROM dbo.Products
  WHERE CatalogDescription.exist
    ("/PD:ProductDescription/PD:Features") = 1

Вторичные XML-индексы

Хотя первичные XML-индексы и повышают производительность запросов XQuery вследствие того, что XML-данные разбираются, SQL Server все же должен просматривать разобранные данные, чтобы найти среди них запрашиваемые данные. Чтобы еще больше повысить производительность запроса, можно создать поверх первичного XML-индекса вторичный XML-индекс. Существует три типа вторичных XML-индексов, и каждый тип поддерживает определенные типы запросов к XML-столбцам, предоставляя функции для создания только тех типов индексов, которые требуются для определенного сценария. Вот эти три типа вторичных XML-индексов:

CREATE XML INDEX index name 
  ON <object> ( xml column name ) 
  USING XML INDEX xml index name 
  FOR { VALUE | PATH | PROPERTY }

Создаем и используем вторичные XML-индексы

CREATE XML INDEX IXML Products CatalogDesc Path 
   ON dbo.Products (CatalogDescription) 
   USING XML INDEX PRXML Products CatalogDesc FOR PATH
CREATE XML INDEX IXML_Products_CatalogDesc_Value 
  ON dbo.Products (CatalogDescription) 
  USING XML INDEX PRXML_Products_CatalogDesc FOR VALUE
CREATE XML INDEX IXML_Products_CatalogDesc_Property 
  ON dbo.Products (CatalogDescription) 
  USING XML INDEX PRXML_Products_CatalogDesc FOR PROPERTY

WITH XMLNAMESPACES 
  ("http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/
    ProductModelDescription' AS "PD") SELECT *
  FROM dbo.Products
  WHERE CatalogDescription.exist
   ("/PD:ProductDescription/@ProductModelID[.="19"]') = 1;
WITH XMLNAMESPACES 
  ("http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/
   ProductModelDescription' AS "PD") 
SELECT *
  FROM dbo.Products
  WHERE CatalogDescription.exist ("//PD:*/@ProductModelID[.="19"]') = 1;
WITH XMLNAMESPACES 
  ("http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/
    ProductModelDescription' AS "PD")
SELECT CatalogDescription.value
    ("(/PD:ProductDescription/@ProductModelID)[1]",
     "int") as PID 
  FROM dbo.Products 
  WHERE CatalogDescription.exist 
     ("/PD:ProductDescription/@ProductModelID") = 1;

Индексы в представлениях

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

При индексации представление обрабатывается, и результат хранится в файле данных, как это делается для кластеризованной таблицы. SQL Server автоматически обслуживает этот индекс при изменении данных в базовой таблице. Индексы в представлениях могут существенно ускорить доступ к данным через представления, но, безусловно, индексация приводит к дополнительным затратам ресурсов при изменении данных в базовой таблице. Следовательно, можно предусмотреть использование индексации представлений, если представление обрабатывает много строк, как при использовании агрегатных функций, а также если данные в базовой таблице не слишком часто изменяются.

В SQL Server 2005 Enterprise, Developer или Evaluation Edition индексированные представления могут ускорить выполнение запросов, которые не ссылаются на представления напрямую. Если обрабатываемый запрос включает, например, агрегат, и Оптимизатор запросов SQL Server обнаруживает индексированное представление, в которое этот агрегат уже включен, он запросит агрегат из индекса, а не станет вычислять его.

Чтобы создать индексированное представление, выполните следующие действия:

Создаем индексированное представление

Создаем и используем индексированные представления

Запустите SQL Server Management Studio. Откройте окно New Query (Создать запрос) и измените контекст базы данных на Adventure Works.

Введите и выполните следующую инструкцию, чтобы создать представление, которое агрегирует итог LineTotal, сгруппированный по месяцам заказа. Код этого примера можно найти среди файлов примеров под именем CreatingAndUsingIndexedViews.sql.

CREATE VIEW dbo.vOrderDetails
  WITH SCHEMABINDING AS
SELECT DATEPART(yy,Orderdate) as Year,
    DATEPART(mm,Orderdate) as Month,
    SUM(LineTotal) as OrderTotal,
    COUNT_BIG(*) as LineCount 
  FROM dbo.Orders o INNER JOIN dbo.OrderDetails od
  ON o.SalesOrderID = od.SalesOrderID 
  GROUP BY DATEPART(yy,Orderdate),
  DATEPART(mm,Orderdate)

Введите и выполните следующую инструкцию SELECT. На вкладке Messages (Сообщения) видно, что для выполнения этой инструкции SQL Server требуется почти 1000 считываний страниц.

SET STATISTICS IO ON 
SELECT Year, Month, OrderTotal
  FROM dbo.OrderDetails
  ORDER BY Year, Month 
SET STATISTICS IO OFF

Введите и выполните следующую инструкцию CREATE INDEX, чтобы создать уникальный кластеризованный индекс в представлении vOrderDetails.

CREATE UNIQUE CLUSTERED INDEX CLIDX_vOrderDetails_Year_Month 
   ON dbo.vOrderDetails(Year,Month)

Выполните еще одну инструкцию SELECT. Обратите внимание на то, что SQL Server требуется только два считывания страниц, потому что результат уже вычислен и хранится в индексе.

SET STATISTICS IO ON
SELECT Year, Month, OrderTotal
   FROM dbo.OrderDetails
   ORDER BY Year, Month
SET STATISTICS IO OFF

Если у вас установлена одна из следующих версий пакета: SQL Server 2005 Enterprise, Developer или Evaluation edtition - введите и выполните следующую инструкцию SELECT, которая не ссылается на пред ставление, и нажмите (Ctrl+L), чтобы запросить предполагаемый план выполнения, как показано ниже.

SELECT DATEPART(yy,Orderdate) as Year,
      SUM(LineTotal) as YearTotal
   FROM dbo.Orders o INNER JOIN dbo.OrderDetails od
      ON o.SalesOrderID = od.SalesOrderID
   GROUP BY DATEPART(yy,Orderdate)

План выполнения предыдущего запроса показывает, что SQL Server использует кластеризованный индекс в представлении, чтобы извлечь данные, поскольку гораздо эффективнее создать агрегат YearTotal, вычислив сумму найденных в представлении агрегатов за месяц. Таким образом, мы видим, что при наличии индексированных представлений становятся возможным ускорить запрос, не изменяя код самого запроса.

Закройте окно среды SQL Server Management Studio.

Индексы, ускоряющие операции соединения

Операторы соединения используются для соединения таблиц или промежуточных результатов. В SQL Server используется три типа операторов соединения.

Изучаем операции соединения

Запустите SQL Server Management Studio. Откройте окно New Query (Создать запрос) и измените контекст базы данных на Adventure Works.

Введите и выполните следующую инструкцию, не забудьте включить действительный план выполнения запроса. Код этого примера можно найти в файлах примеров под именем Examining Join Operations.sql. План выполнения этой инструкции показан ниже. Мы видим, что SQL Server для того, чтобы извлечь строки внутреннего ввода (данные из таблицы OrderDetails ), использует поиск по индексу (метод seek ), а затем оператор Nested Loop Join, потому что во внешнем вводе есть только одна совпадающая строка (данные из таблицы Orders ). Совпадающие строки внешнего ввода возвращаются также с помощью поиска по индексу (метод seek ), поскольку существует совпадающий индекс. Для извлечения данных SQL Server требуется только пять считываний страниц, как указано на вкладке Messages (Сообщения).

SET STATISTICS IO ON
SELECT o.SalesOrderID, o.OrderDate, od.ProductID
   FROM dbo.Orders o INNER JOIN dbo.OrderDetails od
      ON o.SalesOrderID = od.SalesOrderID
   WHERE o.SalesOrderID = 43659

Измените запрос так, чтобы требовалось извлечь более одного значения SalesOrderID. В этом случае SQL Server выполнит соединение слиянием, поскольку в индексе есть отсортированные строки, а во внутреннем вводе много строк. Введите и выполните следующий запрос: Вы увидите, что для выполнения этого запроса SQL Server требуется 19 считываний страниц.

SELECT o.SalesOrderID, o.OrderDate, od.ProductID 
   FROM dbo.Orders o 
   INNER JOIN dbo.OrderDetails od 
      ON o.SalesOrderID = od.SalesOrderID 
   WHERE o.SalesOrderID BETWEEN 43659 AND 44000

Введите и выполните следующий пакет, который удаляет опорные индексы.

DROP INDEX CLIDX_Orders_SalesOrderID ON dbo.Orders
 DROP INDEX CLIDX_OrderDetails ON dbo.OrderDetails

Снова выполните инструкцию SELECT, которую мы использовали ранее (она снова показана ниже) и изучите изменения планов выполнения запросов и операций ввода/вывода.

SELECT o.SalesOrderID, o.OrderDate, od.ProductID 
   FROM dbo.Orders o INNER JOIN dbo.OrderDetails od 
     ON o.SalesOrderID = od.SalesOrderID 
   WHERE o.SalesOrderID = 43659
SELECT o.SalesOrderID, o.OrderDate, od.ProductID 
   FROM dbo.Orders o INNER JOIN dbo.OrderDetails od 
     ON o.SalesOrderID = od.SalesOrderID 
   WHERE o.SalesOrderID BETWEEN 43659 AND 44000

План выполнения предыдущего запроса показывает, что SQL Server снова использует соединение вложенных циклов для первого запроса, потому что внутренний ввод небольшой, и хэшированное соединение для второго запроса, потому что данные ввода больше не сортируются. Поскольку опорных индексов не существует, SQL Server приходится сканировать в обоих случаях базовые таблицы полностью, что требует более 1000 считываний страниц для каждого запроса.

Как вы убедились прочитав этот раздел, для соединений очень важно иметь опорные индексы. По общему правилу все ограничения по внешнему ключу должны быть индексированы, потому что они являются условиями соединения почти для всех запросов.

Распределение данных и статистика

В последнем примере мы видели, что SQL Sever выбирает разные операторы соединения, исходя из размера ввода для соединения. Кроме того, для других операций, например, для поиска по индексу (метод seek) или сканирования, SQL Server также нужно знать, сколько строк будет использоваться, чтобы определить, какой оператор лучше использовать. Такой оператор определяется на основе статистических данных, поскольку SQL Server должен сделать это до действительного доступа к данным. Эти статистические данные создаются и обновляются SQL Server автоматически по столбцам при помощи следующих шагов:

Таково поведение по умолчанию, но для большинства баз данных существует лучший вариант. Можно воспользоваться инструкцией ALTER DATABASE, чтобы информировать SQL Server о том, что нужно обновлять данные статистики асинхронно; это означает, что программа не будет ждать новых статистических данных при генерации плана выполнения запроса. Безусловно, это означает, что сгенерированный план выполнения запроса может не быть оптимальным, поскольку при его создании были использованы устаревшие статистические данные. В особых случаях может быть желательным сгенерировать или обновить данные статистики вручную. Это можно сделать с помощью инструкций CREATE STATISTICS или UPDATE STATISTICS. Можно также отключить автоматическое создание и обновление индекса на уровне базы данных, выполнив инструкцию ALTER DATABASE. Все эти варианты следует использовать только в особых ситуациях, потому что поведение по умолчанию прекрасно подходит для большинства случаев. Дополнительную информацию об этих вариантах можно прочитать в официальном документе "Statistics Used by the Query Optimizer in Microsoft SQL Server 2005" (Статистические данные, используемые Оптимизатором запросов в Microsoft SQL Server 2005), который можно найти по ссылке http://www.microsoft.com/technet/prodtechnol/sql/ 2005/qrystats.mspx.

Просматриваем статистику распределения данных

Введите и выполните следующий запрос, чтобы узнать, какие статистические данные существуют для таблицы dbo:OrderDetails. Код этого примера можно найти среди файлов примеров под именем DataDistributionStatistics.sql.

SELECT s.NAME, COL_NAME ( s.object_ID, sc.column_id) as CNAME 
  FROM sys.stats s INNER JOIN sys.stats_columns sc 
  ON s.stats_id = sc.stats_id 
  AND s.object_id = sc.object_id 
  WHERE s.object_id = OBJECT_ID('dbo.OrderDetails') 
  ORDER BY s.NAME;"

Результат сообщает нам имена статистических показателей и столбцов, для которых они рассчитаны. Статистические данные для индексированных столбцов получают названия по названию соответствующего индекса. Статистические данные с именами, которые начинаются с WA_Sys - это статистика, которую SQL Server создает автоматически для столбцов, не имеющих индекса.

DBCC SHOW_STATISTICS('dbo.OrderDetails', 'LineTotal')

Панель результатов, показанная ниже, отображает часть вывода инструкции DBCCSHOW_ STATISTICS. Первая часть - это общая информация, например, дата создания, количество строк в таблице и количество строк в выборке. Отображается также информация о плотности данных. Плотность - это величина, показывающая, сколько неповторяющихся значений в анализируемом столбце. Помимо этой общей информации SQL Server определяет диапазоны в данных, которые называются шагами, и сохраняет статистику распределения для этих шагов. С помощью этой статистики распределения SQL Server может, используя статистическую информацию для диапазона, к которому принадлежит указанное значение, предположить, сколько строк будет вовлечено в поиск указанного значения. Для каждого шага SQL Server хранит следующую информацию:

Фрагментация индекса

При создании кластеризованного индекса данные сохраняются на уровне листовых вершин в порядке сортировки. В некластеризованных индексах ключи индекса сортируются и хранятся на уровне листовых вершин. Чтобы получить максимальную производительность на уровне листовых вершин, страницы индекса упорядочиваются не только логически, но и физически. SQL Server сохраняет эти страницы индекса в соответствующем физическом порядке, чтобы можно было считывать данные с диска настолько быстро, насколько это возможно. Самой затратной частью считывания данных с диска является вывод рычага выборки с диска. Если страницы данных, которые нужно считывать, расположены последовательно, необходимое количество таких перемещений рычага уменьшается, тем самым повышается производительность считывания.

Когда выполняется вставка данных в таблицу, эти данные сохраняются на указанной странице среди страниц уровня листовых вершин кластеризованного индекса. Ключи некластеризованного индекса также должны быть вставлены на правильную страницу среди страниц уровня листовых вершин некластеризованного индекса. Если на этой странице нет места, то SQL Server должен выполнить разбор страниц, это означает выделение новой страницы и привязки новой страницы к соответствующему индексу. Подобные ситуации приводят к фрагментации индекса; в этой ситуации логический порядок страниц данных больше не соответствует физическому порядку. Фрагментация индекса может также произойти в результате выполнения инструкций UPDATE и DELETE.

Чтобы уменьшить фрагментацию, можно использовать при создании индекса параметр FILLFACTOR, который определяет, до какого процента должны быть заполнены страницы уровня листовых вершин индекса, когда создаются индексы. При более низком значении параметра FILLFACTOR фрагментация наименее вероятна, потому что на странице уровня листовых вершин можно поместить большие записи без разбиения страницы. С другой стороны, для более низких значений параметра FILLFACTOR индекс будет больше, потому что изначально на каждой странице уровня листовых вершин хранится меньше данных.

Если индексируемая таблица не является таблицей только для чтения, то ее индексы рано или поздно будут фрагментированы. Фрагмен-тированные индексы можно дефрагментировать для повышения скорости доступа к данным при помощи инструкции ALTER INDEX. Для дефрагментации предусмотрено два параметра:

Обслуживаем индексы

SELECT object_name(i.object_id) as object_name ,i.name as IndexName
   ,ps.avg_fragmentation_in_percent
   ,avg_page_space_used_in_percent 
  FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL , 'DETAILED') as ps
  INNER JOIN sys.indexes as i 
     ON i.object_id = ps.object_id AND i.index_id = ps.index_id 
  WHERE ps.avg_fragmentation_in_percent > 50 
    AND ps.index_id > 0 ORDER BY 1

ALTER INDEX PK_Employee_EmployeeID
   ON HumanResources.Employee
   REBUILD
 WITH (ONLINE = ON)

Настройка запросов с помощью Помощника по настройке ядра СУБД

Создание правильных индексов для проекта базы данных - непростая задача. Здесь необходимо учесть множество факторов:

Чтобы помочь пользователю в проектировании индексов, SQL Server предлагает инструмент, который называется Database Engine Tuning Advisor (Помощник по настройке ядра СУБД). Помощнику по настройке ядра СУБД необходим файл рабочей нагрузки, который может быть текстовым файлом, содержащим оптимизируемые инструкции, или файлом трассировки, который может быть сгенерирован при помощи компонента SQL Server Profiler. Затем Помощник по настройке ядра СУБД оптимизирует базу данных, которая использует оптимизатор запросов SQL Server и существующую базу данных, чтобы сгенерировать рекомендации по поводу изменений в физической структуре проекта (например, по поводу создания, изменения или удаления различных индексов).

Используем Помощник по настройке ядра СУБД

Запустите SQL Server Management Studio. Откройте окно New Query (Создать запрос) и измените контекст базы данных на Adventure Works.

Введите следующие инструкции, которые мы будем оптимизировать с помощью Помощника по настройке ядра СУБД. Код этого примера можно найти среди файлов примеров под именем UsingDatabaseEngineTuningAdvisor.sql.

USE AdventureWorks;
  SELECT o.SalesOrderID, o.OrderDate, od.ProductID 
     FROM dbo.Orders o INNER JOIN dbo.OrderDetails od 
       ON o.SalesOrderID = od.SalesOrderID 
     WHERE o.SalesOrderID = 43659;
  SELECT o.SalesOrderID, o.OrderDate, od.ProductID 
     FROM dbo.Orders o INNER JOIN dbo.OrderDetails od
       ON o.SalesOrderID = od.SalesOrderID
     WHERE o.SalesOrderID BETWEEN 43659 AND 44000;

Чтобы сохранить этот сценарий в качестве файла рабочей нагрузки, откройте меню File (Файл) и выберите команду Save As (Сохранить как). Сохраните файл под именем dta.sql.

В SQL Server Management Studio выберите из меню Tools (Сервис) команду Database Engine Tuning Advisor (Помощник по настройке ядра СУБД). Установите соединение с экземпляром SQL Server

Выберите файл, который мы сохранили в пункте 3 как файл рабочей нагрузки и выберите в качестве базы данных, подлежащей настройке,

Нажмите на панели инструментов кнопку Start Analysis (Начать анализ).

По завершении анализа откроется окно с рекомендациями, как показано на следующем рисунке:

Помощник по настройке ядра СУБД рекомендует создать два индекса. Чтобы сохранить сценарий для генерации индексов, выберите из меню Actions (Действия) команду Save Recommendations (Сохранить рекомендации).

Закройте окно Database Engine Tuning Advisor (Помощник по настройке ядра СУБД).

Как мы убедились, SQL Server в меру своих возможностей пытается оптимизировать два запроса. Это выгодно только в том случае, если эти запросы должны оптимизироваться без учета эффекта этой оптимизации на другие операции базы данных. Чтобы оптимизировать все индексы базы данных, неплохой идеей будет использование трассировки SQL Server Profiler, которую предоставляет Database Engine Tuning Advisor (Помощник по настройке ядра СУБД) с нормальной рабочей нагрузкой для всей базы данных. Благодаря этой информации Помощник по настройке ядра СУБД может оптимизировать запросы с другой рабочей нагрузкой в базе данных. После выполнения анализа рабочей нагрузки обязательно сохраните и просмотрите рекомендации.

Заключение

В этой лекции рассказывалось о том, как SQL Server хранит данные и осуществляет к ним доступ с использованием индексов и без них. На основе анализа планов запросов и статистики операций ввода/вывода был сделан вывод о важности существования корректных индексов для оптимизации производительности. Кроме того, вы научились использовать различные типы индексов (которые для наглядности объединены в представленной ниже таблице) и обслуживать их.

Из этой лекции вы узнали, как важно подобрать правильный тип индекса и как Помощник по настройке ядра СУБД может оказать помощь в разработке индекса.

Краткий справочник по 2 лекции

CREATE UNIQUE CLUSTERED INDEX 
<index_name> ON <table>(<column>)

CREATE [ UNIQUE ] NONCLUSTERED INDEX index_name ON <object> ( column [ ASC | DESC ] [ ,...n ] )

CREATE PRIMARY XML INDEX index_name ON <object> ( xml_column_name )

CREATE XML INDEX index_name 
ON <object> ( xml_column_name ) USING XML INDEX xml_index_name 
FOR { VALUE | PATH | PROPERTY }

ALTER INDEX <index> 
                     ON <table>.<column> 
                     REBUILD.