Ответ 1
Дополнение: SQL Server 2012 показывает некоторую улучшенную производительность в этой области, но, похоже, не рассматривает конкретные проблемы, отмеченные ниже. Эта должен по-видимому, исправлен в следующей крупной версии послеSQL Server 2012!
В вашем плане показаны отдельные вставки с использованием параметризованных процедур (возможно, с автоматическим параметрированием), поэтому время их разбора/компиляции должно быть минимальным.
Мне показалось, что я посмотрю на это немного больше, поэтому настроил цикл (script) и попытался настроить число из VALUES и записывать время компиляции.
Затем я разделил время компиляции на количество строк, чтобы получить среднее время компиляции для каждого предложения. Результаты ниже
До тех пор, пока не будет представлено 250 предложений VALUES, время компиляции/количество предложений имеет небольшой восходящий тренд, но ничего слишком драматичного.
Но затем происходит внезапное изменение.
Этот раздел данных показан ниже.
+------+----------------+-------------+---------------+---------------+
| Rows | CachedPlanSize | CompileTime | CompileMemory | Duration/Rows |
+------+----------------+-------------+---------------+---------------+
| 245 | 528 | 41 | 2400 | 0.167346939 |
| 246 | 528 | 40 | 2416 | 0.162601626 |
| 247 | 528 | 38 | 2416 | 0.153846154 |
| 248 | 528 | 39 | 2432 | 0.157258065 |
| 249 | 528 | 39 | 2432 | 0.156626506 |
| 250 | 528 | 40 | 2448 | 0.16 |
| 251 | 400 | 273 | 3488 | 1.087649402 |
| 252 | 400 | 274 | 3496 | 1.087301587 |
| 253 | 400 | 282 | 3520 | 1.114624506 |
| 254 | 408 | 279 | 3544 | 1.098425197 |
| 255 | 408 | 290 | 3552 | 1.137254902 |
+------+----------------+-------------+---------------+---------------+
Размер кешированного плана, который рос линейно, внезапно падает, но CompileTime увеличивается в 7 раз, и CompileMemory стреляет вверх. Это точка отсечения между планом, являющимся автоматической параметризованной (с 1000 параметрами), к не параметризованной. После этого он, кажется, становится линейно менее эффективным (с точки зрения количества предложений стоимости, обработанных за данный момент времени).
Не знаю, почему это должно быть. Предположительно, когда он компилирует план для конкретных значений буквального значения, он должен выполнять некоторую активность, которая не масштабируется линейно (например, сортировка).
Это не влияет на размер кэшированного плана запросов, когда я пытался выполнить запрос, состоящий полностью из повторяющихся строк, и ни один из них не влияет на порядок вывода таблицы констант (и когда вы вставляете в кучу время, затраченное на сортировку, было бы бессмысленным, даже если бы оно и было).
Кроме того, если кластерный индекс добавляется в таблицу, в плане по-прежнему отображается явный шаг сортировки, поэтому он не сортирует во время компиляции, чтобы избежать сортировки во время выполнения.
Я попытался рассмотреть это в отладчике, но общедоступные символы для моей версии SQL Server 2008, похоже, не доступны, поэтому вместо этого мне пришлось посмотреть эквивалентную конструкцию UNION ALL в SQL Server 2005.
Типичная трассировка стека ниже
sqlservr.exe!FastDBCSToUnicode() + 0xac bytes
sqlservr.exe!nls_sqlhilo() + 0x35 bytes
sqlservr.exe!CXVariant::CmpCompareStr() + 0x2b bytes
sqlservr.exe!CXVariantPerformCompare<167,167>::Compare() + 0x18 bytes
sqlservr.exe!CXVariant::CmpCompare() + 0x11f67d bytes
sqlservr.exe!CConstraintItvl::PcnstrItvlUnion() + 0xe2 bytes
sqlservr.exe!CConstraintProp::PcnstrUnion() + 0x35e bytes
sqlservr.exe!CLogOp_BaseSetOp::PcnstrDerive() + 0x11a bytes
sqlservr.exe!CLogOpArg::PcnstrDeriveHandler() + 0x18f bytes
sqlservr.exe!CLogOpArg::DeriveGroupProperties() + 0xa9 bytes
sqlservr.exe!COpArg::DeriveNormalizedGroupProperties() + 0x40 bytes
sqlservr.exe!COptExpr::DeriveGroupProperties() + 0x18a bytes
sqlservr.exe!COptExpr::DeriveGroupProperties() + 0x146 bytes
sqlservr.exe!COptExpr::DeriveGroupProperties() + 0x146 bytes
sqlservr.exe!COptExpr::DeriveGroupProperties() + 0x146 bytes
sqlservr.exe!CQuery::PqoBuild() + 0x3cb bytes
sqlservr.exe!CStmtQuery::InitQuery() + 0x167 bytes
sqlservr.exe!CStmtDML::InitNormal() + 0xf0 bytes
sqlservr.exe!CStmtDML::Init() + 0x1b bytes
sqlservr.exe!CCompPlan::FCompileStep() + 0x176 bytes
sqlservr.exe!CSQLSource::FCompile() + 0x741 bytes
sqlservr.exe!CSQLSource::FCompWrapper() + 0x922be bytes
sqlservr.exe!CSQLSource::Transform() + 0x120431 bytes
sqlservr.exe!CSQLSource::Compile() + 0x2ff bytes
Таким образом, чтобы скрыть имена в трассировке стека, он тратит много времени на сравнение строк.
Эта статья в KB указывает, что DeriveNormalizedGroupProperties связана с тем, что раньше называлось нормализация этап обработки запросов
Этот этап теперь называется связыванием или алгебраизацией, и он берет дерево синтаксиса выражения, выводимое из предыдущей стадии синтаксического анализа, и выводит дерево алгебраических выражений (дерево обработчиков запросов), чтобы перейти к оптимизации (в этом случае оптимизация тривиального плана) [ref].
Я попробовал еще один эксперимент (script), который должен был повторно запустить исходный тест, но рассмотрел три разных случая.
- Имя и фамилия Строки длиной 10 символов без дубликатов.
- Имя и фамилия Строки длиной 50 символов без дубликатов.
- Имя и фамилия Строки длиной 10 символов со всеми дубликатами.
Ясно видно, что чем длиннее струны, тем хуже получается, и наоборот, чем больше дублирует, тем лучше получается. Как уже упоминалось ранее, дубликаты не влияют на размер кеш-плана, поэтому я предполагаю, что при построении дерева алгебраированного выражения должен существовать процесс дублирования идентификации.
Edit
Одно место, где используется эта информация, показано здесь @Lieven
SELECT *
FROM (VALUES ('Lieven1', 1),
('Lieven2', 2),
('Lieven3', 3))Test (name, ID)
ORDER BY name, 1/ (ID - ID)
Потому что во время компиляции он может определить, что столбец Name не имеет дубликатов, он пропускает упорядочение по вторичному выражению 1/ (ID - ID) во время выполнения (тип в плане имеет только столбец ORDER BY) и не делит на ошибка нуля. Если в таблицу добавлены дубликаты, то оператор сортировки показывает два порядка по столбцам и ожидаемую ошибку.