Включение столбцов nvarchar(max) в предложение select значительно увеличивает время выполнения

Question

У меня есть простая таблица, которая состоит из столбцов и индексов, как показано ниже.

Вот мой индекс, созданный по рекомендации советника по настройке ядра СУБД. Он включает в себя все столбцы.

CREATE NONCLUSTERED INDEX [_dta_index_PROPOSAL_PROCESS_12_13243102__K2_K7_K1_3_4_5_6_8_9_10] ON [dbo].[PROPOSAL_PROCESS]
(
    [PROPOSAL_ID] ASC,
    [IS_DELETED] ASC,
    [ID] ASC
)
INCLUDE (   [CREATOR_USER_ID],
    [CREATION_TIME],
    [LAST_UPDATER_USER_ID],
    [LAST_UPDATE_TIME],
    [CURRENT_PROPOSAL_OBJECT],
    [INTERFACTORING_XML],
    [OMDM_OUTPUT_XML]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

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

Если я не включу ни один из вышеупомянутых столбцов.

SET STATISTICS IO ON
SET STATISTICS TIME ON
CHECKPOINT; 
GO 
DBCC DROPCLEANBUFFERS; 
GO  
DECLARE @DynamicFilterParam_000001 bit = 0,
        @DynamicFilterParam_000002 bit = null,
        @EntityKeyValue1 int = 4419;    
SELECT 
    [Extent1].[ID] AS [ID], 
    --[Extent1].[CURRENT_PROPOSAL_OBJECT] AS [CURRENT_PROPOSAL_OBJECT], 
    --[Extent1].[INTERFACTORING_XML] AS [INTERFACTORING_XML], 
    --[Extent1].[OMDM_OUTPUT_XML] AS [OMDM_OUTPUT_XML], 
    [Extent1].[PROPOSAL_ID] AS [PROPOSAL_ID], 
    [Extent1].[CREATOR_USER_ID] AS [CREATOR_USER_ID], 
    [Extent1].[CREATION_TIME] AS [CREATION_TIME], 
    [Extent1].[LAST_UPDATER_USER_ID] AS [LAST_UPDATER_USER_ID], 
    [Extent1].[LAST_UPDATE_TIME] AS [LAST_UPDATE_TIME], 
    [Extent1].[IS_DELETED] AS [IS_DELETED]
    FROM [dbo].[PROPOSAL_PROCESS] AS [Extent1]
    WHERE (([Extent1].[IS_DELETED] = @DynamicFilterParam_000001) ) AND ([Extent1].[PROPOSAL_ID] = @EntityKeyValue1);

(Затронуто 54 строк) Таблица 'PROPOSAL_PROCESS'. Сканирование 1, логическое чтение 58, физическое чтение 2, чтение с опережением 55, логическое чтение с бита 0, физическое чтение с бита 0, чтение с опережением чтения 0.

(Затронут 1 ряд)

Время выполнения SQL Server: время ЦП = 31 мс, прошедшее время = 16 мс. Время разбора и компиляции SQL Server: время ЦП = 0 мс, прошедшее время = 0 мс.

Время выполнения SQL Server: время ЦП = 0 мс, прошедшее время = 0 мс.

Если я добавлю CURRENT_PROPOSAL_OBJECT, чтобы выбрать предложение предыдущего запроса

(Затронуто 54 строк) Таблица 'PROPOSAL_PROCESS'. Сканирование 1, логическое чтение 58, физическое чтение 2, чтение с опережением 55, логическое чтение с бита 0, физическое чтение с бита 0, чтение с опережением чтения 0.

(Затронут 1 ряд)

Время выполнения SQL Server: время ЦП = 0 мс, прошедшее время = 545 мс. Время разбора и компиляции SQL Server: время ЦП = 0 мс, прошедшее время = 0 мс.

Если я добавлю INTERFACTORING_XML, чтобы выбрать предложение предыдущего запроса

(Затронуто 54 строк) Таблица 'PROPOSAL_PROCESS'. Сканирование 1, логическое чтение 58, физическое чтение 2, чтение с опережением 55, логическое чтение с бита 1822, физическое чтение с бита 376, чтение с опережением чтения 0.

(Затронут 1 ряд)

Время выполнения SQL Server: время ЦП = 47 мс, прошедшее время = 2415 мс. Время разбора и компиляции SQL Server: время ЦП = 0 мс, прошедшее время = 0 мс.

Если я добавлю CURRENT_PROPOSAL_OBJECT, чтобы выбрать предложение предыдущего запроса

(Затронуто 54 строк) Таблица 'PROPOSAL_PROCESS'. Сканирование 1, логическое чтение 58, физическое чтение 2, чтение с опережением 55, логическое чтение с лота 5048, физическое чтение с 944, чтение с опережением чтения 0.

(Затронут 1 ряд)

Время выполнения SQL Server: время ЦП = 47 мс, прошедшее время = 6912 мс. Время разбора и компиляции SQL Server: время ЦП = 0 мс, прошедшее время = 0 мс.

Итак, в конце дня я столкнулся с 6912 миллисекундами. В чем причина этого ужасного выступления?

Я скучаю по созданию некоторых индексов? Это результат плохого дизайна, произошедшего от размещения большого размера nvarchar в одной таблице?

заранее спасибо

Изменить: Вот сценарии для восстановления проблемы. Я пытался создать ту же проблему на sql-fiddle, но он не вставил ни одной строки.

Изменить 2: Если я включу все столбцы в предложение select и просто предоставлю другой идентификатор предложения, который заставляет запрос вернуть 2 строки, время выполнения обычно остается примерно на уровне 100 мс. Время выполнения запроса становится все хуже и хуже в зависимости от увеличения числа возвращаемых строк. Нет связь с указателями.

Answer 1

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

Некластеризованный индекс для всех столбцов, если он фактически удваивает размер таблицы. Так как у вас будет кластерный индекс (фактическая таблица) и копия в некластеризованном индексе. Это удвоит количество записи, если вы вставляете в таблицу.

Если вы выбираете из одного начала таблицы, но у вас есть только два столбца в предложении where ключа вашего индекса, то индекс на Proposal_ID а также Is_Deleted, использование SET STATISTICS IO ON и план выполнения и посмотрите, что он делает, тогда вы можете играть оттуда.

Парсер статистики Ричи Румпа: http://statisticsparser.com/ предназначен для чтения результатов статистики io

Answer 2

Я скучаю по созданию некоторых индексов? Это результат плохого дизайна, произошедшего от размещения большого размера nvarchar в одной таблице?

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

если мой запрос начинается с 1 мс и занимает 8000 мс, это общее время запроса / истекшее время, но в это время ЦП может быть только 10 мс..

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

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

Answer 3

NVARCHAR(max) является заменой старого типа данных ntext.

ntext всегда будет хранить свою информацию в виде BLOB вне строки. Где это возможно, NVARCHAR(max) будет пытаться сохранить информацию в строке. Если это невозможно из-за размера, то текст будет сохранен в BLOB.

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

Советник по настройке эффективно говорит вам об этом. Сравните количество операций чтения lob (большой объект) в первом запросе (0) с теми, которые следуют и ссылаются на поля nvarchar(max).