Разные предполагаемые строки в одной и той же операции индекса?

Question

Разные предполагаемые строки в одной и той же операции индекса?

Введение и история вопроса

Мне пришлось оптимизировать простой запрос (пример ниже). Несколько раз переписав его, я понял, что расчетное количество строк в одной и той же операции индекса отличается в зависимости от способа написания запроса.

Первоначально запрос выполнял сканирование кластеризованного индекса, так как производственная таблица содержит двоичный столбец, таблица довольно большая (около 100 ГБ), а полное сканирование таблицы занимает слишком много времени для выполнения.

Вопрос

Почему расчетное число строк отличается в одной и той же операции индекса (пример покажет)? Что здесь делает оптимизатор?

Пример базы данных - я использую SQL Server 2008 R2

Я попытался создать очень простую версию моих производственных таблиц, которая показывает поведение.

-- CREATE THE SAMPLE TABLES
----------------------------
CREATE TABLE dbo.MasterTable(
    MasterId    smallint NOT NULL,
    Name        varchar(5) NOT NULL,
    CONSTRAINT PK_MasterTable PRIMARY KEY CLUSTERED (MasterId ASC)
) ON  [PRIMARY]

GO

CREATE TABLE dbo.DetailTable(
    DetailId    bigint IDENTITY(1,1) NOT NULL,
    MasterId    smallint NOT NULL,
    Name        nvarchar(50) NOT NULL,
    CreateDate  datetime NOT NULL,
    CONSTRAINT PK_DetailTable PRIMARY KEY CLUSTERED (DetailId ASC)
) ON  [PRIMARY]

GO

ALTER TABLE dbo.DetailTable
    ADD  CONSTRAINT FK1
    FOREIGN KEY(MasterId) REFERENCES dbo.MasterTable (MasterId)

GO

CREATE NONCLUSTERED INDEX IX_DetailTable
    ON dbo.DetailTable( MasterId ASC, Name ASC )

GO

-- INSERT SOME SAMPLE DATA
----------------------------
SET NOCOUNT ON
GO

-- These are some Codes. In our system we always use these codes to search for "types" of data.

INSERT INTO dbo.MasterTable (MasterId, Name)
VALUES (1, 'N1'), (2, 'N2'), (3, 'N3'), (4, 'N4'), (5, 'N5'), (6, 'N6'), (7, 'N7'), (8, 'N8')

GO

-- ADD ROWS TO THE DETAIL TABLE
-- Takes about 1 minute to run
-- Don't care about the logic, it's just to get a distribution similar to production system
----------------------------
declare @x int = 1
DECLARE @MasterID INT
while (@x <= 400000)
begin
    SET @MasterID = ABS(CHECKSUM(NEWID())) % 8 + 1

    INSERT INTO dbo.DetailTable(MasterId,Name,CreateDate)
    VALUES(
        CASE
            WHEN @MasterID IN (1, 3, 4) AND @x % 20 != 0 THEN 2
            WHEN @MasterID IN (5, 6) AND @x % 20 != 0 THEN 7
            WHEN @MasterID = 8 AND @x % 100 != 0 THEN 7
            ELSE @MasterID
        END,
        NEWID(),
        DATEADD(DAY, - ABS(CHECKSUM(NEWID())) % 1000, GETDATE())
)

SET @x = @x + 1
end

go
-- DO THE INDEX AND STATISTIC MAINTENANCE
----------------------------
alter index all on dbo.DetailTable reorganize
alter index all on dbo.MasterTable reorganize
update statistics dbo.DetailTable WITH FULLSCAN
update statistics dbo.MasterTable WITH FULLSCAN
go

Подготовка завершена, начнем с запроса

Давайте сначала посмотрим на статистику, посмотрим на RANGE_HI_KEY=8есть 489 EQ_ROWS

-- CHECK THE STATISTICS
----------------------------
dbcc show_statistics ('dbo.DetailTable', IX_DetailTable)
GO

Теперь мы делаем запрос. Первый - это оригинальный запрос, который мне пришлось оптимизировать. Пожалуйста, активируйте текущий план выполнения при выполнении. Взгляните на операцию "поиск по индексу (некластеризованный) [DetailTable].[IX_DetailTable]"

-- ORIGINAL QUERY
----------------------------
SELECT d.DetailId
FROM dbo.DetailTable d
INNER JOIN dbo.MasterTable m ON d.MasterId = m.MasterId
WHERE m.Name = 'N8'
AND d.CreateDate > '20150312 11:00:00'

GO

-- FORCESEEK
----------------------------
SELECT d.DetailId
FROM dbo.DetailTable d WITH (FORCESEEK)
INNER JOIN dbo.MasterTable m ON d.MasterId = m.MasterId
WHERE m.Name = 'N8'
AND d.CreateDate > '20150312 11:00:00'

GO

-- Actual: 489, Estimated 50.000


-- TABLE VARIABLE
----------------------------
DECLARE @MasterId AS TABLE( MasterId SMALLINT )
INSERT INTO @MasterId (MasterId)
SELECT MasterID FROM dbo.MasterTable WHERE Name = 'N8'
SELECT d.DetailId
FROM dbo.DetailTable d WITH (FORCESEEK)
INNER JOIN @MasterId m ON d.MasterId = m.MasterId
WHERE d.CreateDate > '20150312 11:00:00'

GO

-- Actual: 489, Estimated 40.000

-- TEMP TABLE
----------------------------
CREATE TABLE #MasterId( MasterId SMALLINT )
INSERT INTO #MasterId (MasterId)
    SELECT MasterID FROM dbo.MasterTable WHERE Name = 'N8'

SELECT d.DetailId
FROM dbo.DetailTable d --WITH (FORCESEEK)
INNER JOIN #MasterId m ON d.MasterId = m.MasterId
WHERE d.CreateDate > '20150312 11:00:00'

-- Actual 489, Estimated 489

DROP TABLE #MasterId

GO

Проанализируйте и последний вопрос (ы)

Пожалуйста, взгляните на операцию "поиск по индексу (некластеризованный) [DetailTable].[IX_DetailTable]"

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

В нашей производственной среде эта таблица имеет 33 миллиона строк, оценочные строки в запросах выше отличаются от 3 миллионов до 16 миллионов.

Подвести итоги:

когда выполняется соединение между DetailTable и MasterTable, предполагаемое количество строк составляет 12,5% (в основной таблице 8 значений, это имеет смысл, вроде...)
когда выполняется соединение между DetailTable и табличной переменной, расчетное количество строк составляет 10%
когда выполняется соединение между DetailTable и временной таблицей, расчетное количество строк точно совпадает с фактическим количеством строк

Вопрос в том, почему эти значения отличаются?

Статистика актуальна, и оценка должна быть действительно простой.

Я просто хотел бы понять это.

2

sql-server optimization sql-server-2008-r2 histogram sql-server-performance

Источник

user4684181 18 мар '15 в 08:19

1 ответ

Решение

Другие вопросы по тегам sql-server optimization sql-server-2008-r2 histogram sql-server-performance

user1768223 23 мар '15 в 09:42 2015-03-23 09:42 · Accepted Answer · 2015-03-23 09:42

Поскольку никто не отвечает, я постараюсь дать ответ:

Пожалуйста, не заставляйте оптимизатор следовать за вами

(1) Объяснение вашего оригинального запроса:

SELECT d.DetailId
FROM dbo.DetailTable d
INNER JOIN dbo.MasterTable m ON d.MasterId = m.MasterId
WHERE m.Name = 'N8'
AND d.CreateDate > '20150312 11:00:00'

Почему этот запрос медленный?

этот запрос медленный, потому что ваши индексы не покрывают этот запрос, оба запроса используют сканирование индекса, а затем объединяются с помощью "Hash join":

введите описание изображения здесь

ПОЧЕМУ сканирует весь ряд на предмет мастер-таблицы? Поскольку индекс для главной таблицы находится в столбце MasterId, а не в столбце Name.

ПОЧЕМУ сканирует всю строку для Детализируемых? Потому что здесь также есть индекс (DETAILID) "CLUSTERED" И ( MasterId ASC, имя ASC) "NON CLUSTERED"
не в столбце "Создано".

наличие одного NONCLUSTERED индекса поможет этому столбцу запроса ON (CREATEDATE,MasterId) для этого конкретного запроса.

Если ваша мастер-таблица также огромна, вы можете создать NONCLUSTERED индекс для столбца (Имя).

(2) Объяснение на FORCESEEK:

- FORCESEEK

SELECT d.DetailId
FROM dbo.DetailTable d WITH (FORCESEEK)
INNER JOIN dbo.MasterTable m ON d.MasterId = m.MasterId
WHERE m.Name = 'N8'
AND d.CreateDate > '20150312 11:00:00'
GO

введите описание изображения здесь

Почему оптимизатор оценил 50000 строк?

Здесь вы присоединяетесь к столбцу d.MasterId = m.MasterId и оптимизатор FORCING выбирает поиск по таблице сведений, поэтому оптимизируйте, используя INDEX IX_DetailTable (), чтобы присоединиться к вашему Master table с помощью объединения LOOP.

Так как Optimizer выбрал Loop join для объединения всех строк (на самом деле ONE) таблицы MAster с таблицей Detail, поэтому он выберет один ключ из основной таблицы, затем выполнит поиск всего индекса и затем передаст соответствующее значение следующему итератору.

поэтому оптимизатор выбирает Среднее из строк на значение. 8 уникальных значений в столбце 40000 кардинальности таблицы (строки), поэтому 40000 / 8 - это 50000 строк оценки (достаточно справедливо).

(3) - ТАБЛИЦА ПЕРЕМЕННЫХ

Вот ваш запрос:

DECLARE @MasterId AS TABLE( MasterId SMALLINT )
INSERT INTO @MasterId (MasterId)
SELECT MasterID FROM dbo.MasterTable WHERE Name = 'N8'
SELECT d.DetailId
FROM dbo.DetailTable d WITH (FORCESEEK)
INNER JOIN @MasterId m ON d.MasterId = m.MasterId
WHERE d.CreateDate > '20150312 11:00:00'

GO

введите описание изображения здесь

Statatictic не поддерживает переменные таблицы, поэтому у оптимизатора нет идентификатора числа строк (так что он оценивает 1 строку), с которыми он имеет дело, чтобы получить хороший план, здесь также оцениваются строки 1, а фактическая строка 1 также поздравляет!!

но как оптимизатор оценил "40.000" строк

Лично я никогда не проверял это, и из-за этого вопроса я проводил тестирование уровней, но у меня нет ни малейшего представления о том, как оптимизатор вычисляет приблизительные строки, поэтому будет здорово, если кто-нибудь придет и осветит нас.

(4) - TEMP TABLE

Ваш запрос

CREATE TABLE #MasterId( MasterId SMALLINT )
INSERT INTO #MasterId (MasterId)
    SELECT MasterID FROM dbo.MasterTable WHERE Name = 'N8'

SELECT d.DetailId
FROM dbo.DetailTable d --WITH (FORCESEEK)
INNER JOIN #MasterId m ON d.MasterId = m.MasterId
WHERE d.CreateDate > '20150312 11:00:00'

-- Actual 489, Estimated 489
DROP TABLE #MasterId

введите описание изображения здесь

здесь оптимизатор также выбирает тот же план запроса, который был выбран в табличной переменной, но разница в том, что Статистика поддерживает временные таблицы, так что здесь в оптимизаторе запросов есть четкое представление о том, к какой строке он собирается присоединиться. Клавиша "N8" имеет 8, а в dbo.DetailTable приблизительно 8 строк.