Оптимизация запросов SQL Server. Часть IV

Содержание:
1. Часть I;
2. Часть II;
3. Часть III;
4. Часть IV (Вы читаете данный раздел);
5. Часть V.

---

При использовании предиката BETWEEN жестко заданные предположения зависят от сценария и применяемой CE. В старых CE во всех случаях используется оценка 9%. Это демонстрирует следующий запрос. Флаг трассировки 9481 запроса используется, чтобы применить старую CE.

DECLARE @FromQty AS INT = 40, @ToQty AS INT = 41;

SELECT ProductID, COUNT(*) AS NumOrders

FROM Sales.SalesOrderDetail

WHERE OrderQty BETWEEN @FromQty AND @ToQty

GROUP BY ProductID

OPTION(QUERYTRACEON 9481);

План для этого запроса показан на рисунке выше. Оценка 0,09 * 121317 = 10918,5.

В новой CE задействованы различные оценки при применении констант и отсутствующей гистограмме и при использовании переменных или параметров с отключенным прослушиванием. В первом случае используется оценка 9%; во втором — оценка 16,4317%. Ниже приводится пример использования констант. Обязательно удалите любую существующую статистику для столбца и отключите автоматическое создание статистики, как показано выше, перед выполнением теста и включите после его завершения.

DECLARE @FromQty AS INT = 40, @ToQty AS INT = 41;

SELECT ProductID, COUNT(*) AS NumOrders

FROM Sales.SalesOrderDetail

WHERE OrderQty BETWEEN @FromQty AND @ToQty

GROUP BY ProductID

OPTION(QUERYTRACEON 9481);

Я получил такой же план, как на рисунке выше, с оценкой 9%.

Ниже приводится пример, демонстрирующий применение переменных (то же поведение, что и при использовании параметров с отключенным прослушиванием):

DECLARE @FromQty AS INT = 40, @ToQty AS INT = 41;

SELECT ProductID, COUNT(*) AS NumOrders

FROM Sales.SalesOrderDetail

WHERE OrderQty BETWEEN @FromQty AND @ToQty

GROUP BY ProductID;

Я получил план, приведенный на рисунке выше, показывающий оценку 16,4317%.

При использовании предиката LIKE во всех сценариях оптимизации для неизвестного как в старых, так и в новых CE применяется оценка 9%. Ниже приведен примере использованием локальных переменных:

DECLARE @Carrier AS NVARCHAR(50) = N'4911-403C-%';

SELECT ProductID, COUNT(*) AS NumOrders

FROM Sales.SalesOrderDetail

WHERE CarrierTrackingNumber LIKE @Carrier

GROUP BY ProductID;

Вы увидите ту же оценку 9%, как показано на рисунке «План, показывающий оценку 9%», хотя в данном случае действительное число строк 12, а ранее было 3.


При использовании оператора = различают три основных случая:
• уникальный столбец;
• неуникальный столбец и доступная плотность;
• неуникальный столбец и недоступная плотность.

Если фильтруемый столбец уникален (для него определены уникальный индекс, ограничение PRIMARY KEY или UNIQUE), то оптимизатору известно, что совпадений не может быть более одного, поэтому оценка равна 1. Ниже приводится запрос, демонстрирующий этот случай:

DECLARE @rowguid AS UNIQUEIDENTIFIER = 'B207C96D-D9E6-402B-8470-2CC176C42283';

SELECT *

FROM Sales.SalesOrderDetail

WHERE rowguid = @rowguid;

На рисунке выше показан план для этого запроса с оценкой 1.

Если столбец не уникален и оптимизатору доступна информация о плотности (средний процент для отдельного значения), то оценка основывается на плотности. Если не отключено автоматическое создание статистики или для столбца сформирован индекс, то эта информация будет доступна оптимизатору. Чтобы продемонстрировать это, сначала убедитесь, что автоматическое создание статистики включено, выполнив следующий программный код:

ALTER DATABASE AdventureWorks2014 SET AUTO_CREATE_STATISTICS ON;

Затем выполните следующий запрос:

DECLARE @Qty AS INT = 1;

SELECT ProductID, COUNT(*) AS NumOrders

FROM Sales.SalesOrderDetail

WHERE OrderQty = @Qty

GROUP BY ProductID;