План выполнения SQL Server Фактическое число строк слишком велико для простого выбора

Question

(Короче говоря, я думаю, что № 3 в моем списке странных заметок о плане выполнения - проблема).

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

Захватив план выполнения, мы определили, что виноват один запрос в рамках процесса. Запрос прост

INSERT INTO #table (<columnset>)
SELECT <columnset> 
FROM table
WHERE <binary type column> > @binaryArgumentPassedIntoProc

Для исходной таблицы: столбец метки времени в предложении WHERE индексируется. Один из выбранных столбцов является PK и имеет тип varchar. 3 других столбца выбираются (всего 5). Временная таблица не имеет индексов / ключей или ограничений.

Схема в этой таблице не изменилась, за исключением одного небольшого исключения - для одного из столбцов исходной таблицы теперь для TrimTrailingBlanks установлено значение yes. Этот столбец не входит в набор столбцов, из которого выбирается. Я не могу себе представить, что это имеет значение, но хотел это назвать.

Когда я восстанавливаю момент времени, я не могу повторить медлительность. Во время производственного выполнения (то есть, когда он выполнялся медленно в среде тестирования производительности) запрос иногда занимает более 30 секунд. В момент резервного копирования запрос занимает менее 1 секунды.

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

1. Число EstimatedRow в момент выполнения резервного копирования составляет 176.683. Это 2 в производственном исполнении. Это верно для выбора и вставки
2. TableCardinality в производственном выполнении для выбора - 2, в то время как в момент выполнения - 1578. Это указывает на то, что статистика работала плохо, что неудивительно, хотя я не уверен, как с этим справиться в режиме реального времени, когда система находится под такой большой нагрузкой (на самом деле во время выполнения в таблице 1578 строк). При этом, это не было проблемой в прошлом, когда производительность была хорошей.
3. Фактическое количество строк, отмеченных в плане выполнения и вставленных во временную таблицу, в производственном выполнении указано как 3222. В момент исполнения фактическое количество строк указано как 180.

Относительно (3) - мое понимание фактического количества строк заключается в том, что это число раз, которое итератор вызывал GetNext() при выполнении запроса. Я думаю, что при простом выборе (в основном без объединений) значение фактического количества строк будет максимально равно количеству строк в таблице в этой точке (1578).

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

Кто-нибудь есть какие-либо предложения относительно того, как я могу устранить эту проблему дальше?

Мы работаем с SQL Server 2008 R2 на компьютере под управлением Windows Server 2008 R2 с пакетом обновления 1 (SP1). Машина имеет 16 ядер и 128 ГБ оперативной памяти (64 выделены для сервера SQL).

ОБНОВЛЕНИЕ: я заполнил таблицы данными после восстановления и перед тестом, а затем обновил статистику и перекомпилировал процедуру. Фактическое и ожидаемое количество строк, а также количество элементов таблицы отражали реалистичные значения, но мы все еще видели очень длинные исполнения.

Вот вывод результатов трассировки статистики профиля showplan XML для одного из некорректных запросов в этом последнем тесте:

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.50.2500.0"><BatchSequence><Batch><Statements><StmtSimple><QueryPlan DegreeOfParallelism="1" CachedPlanSize="24" CompileTime="4" CompileCPU="4" CompileMemory="192"><RelOp NodeId="0" PhysicalOp="Table Insert" LogicalOp="Insert" EstimateRows="170" EstimateIO="0.010254" EstimateCPU="0.00017" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0154085" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="69" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[tempdb]" Schema="[dbo]" Table="[#sourceTable]"/><SetPredicate><ScalarOperator ScalarString="[#destinationTable].[record_Identifier] = RaiseIfNullInsert([Expr1008]),[#destinationTable].[modifiedTS] = [Expr1009],[#destinationTable].[Last_Update] = [DBName].[dbo].[sourceTable].[last_update],[#destinationTable].[LastValueUpdate] = [DBName].[dbo].[sourceTable].[LastValueUpdate],[#destinationTable].[OtherValue_lastUpdate] = [DBName].[dbo].[sourceTable].[OtherValue_lastUpdate]"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Table="[#destinationTable]" Column="record_Identifier"/><ScalarOperator><Intrinsic FunctionName="RaiseIfNullInsert"><ScalarOperator><Identifier><ColumnReference Column="Expr1008"/></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Assign><Assign><ColumnReference Table="[#destinationTable]" Column="modifiedTS"/><ScalarOperator><Identifier><ColumnReference Column="Expr1009"/></Identifier></ScalarOperator></Assign><Assign><ColumnReference Table="[#destinationTable]" Column="Last_Update"/><ScalarOperator><Identifier><ColumnReference Database="[DBName]" Schema="[dbo]" Table="[sourceTable]" Column="last_update"/></Identifier></ScalarOperator></Assign><Assign><ColumnReference Table="[#destinationTable]" Column="LastValueUpdate"/><ScalarOperator><Identifier><ColumnReference Database="[DBName]" Schema="[dbo]" Table="[sourceTable]" Column="LastValueUpdate"/></Identifier></ScalarOperator></Assign><Assign><ColumnReference Table="[#destinationTable]" Column="OtherValue_lastUpdate"/><ScalarOperator><Identifier><ColumnReference Database="[DBName]" Schema="[dbo]" Table="[sourceTable]" Column="OtherValue_lastUpdate"/></Identifier></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><RelOp NodeId="1" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="170" EstimateIO="0" EstimateCPU="1.7e-005" AvgRowSize="50" EstimatedTotalSubtreeCost="0.00498448" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[DBName]" Schema="[dbo]" Table="[sourceTable]" Column="last_update"/><ColumnReference Database="[DBName]" Schema="[dbo]" Table="[sourceTable]" Column="LastValueUpdate"/><ColumnReference Database="[DBName]" Schema="[dbo]" Table="[sourceTable]" Column="OtherValue_lastUpdate"/><ColumnReference Column="Expr1008"/><ColumnReference Column="Expr1009"/></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1008"/><ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(15),[DBName].[dbo].[sourceTable].[record_identifier],0)"><Convert DataType="varchar" Length="15" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Database="[DBName]" Schema="[dbo]" Table="[sourceTable]" Column="record_identifier"/></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1009"/><ScalarOperator ScalarString="CONVERT_IMPLICIT(binary(8),[DBName].[dbo].[sourceTable].[ModifiedTS],0)"><Convert DataType="binary" Length="8" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Database="[DBName]" Schema="[dbo]" Table="[sourceTable]" Column="ModifiedTS"/></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Top" LogicalOp="Top" EstimateRows="170" EstimateIO="0" EstimateCPU="1.7e-005" AvgRowSize="58" EstimatedTotalSubtreeCost="0.00496748" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[DBName]" Schema="[dbo]" Table="[sourceTable]" Column="record_identifier"/><ColumnReference Database="[DBName]" Schema="[dbo]" Table="[sourceTable]" Column="last_update"/><ColumnReference Database="[DBName]" Schema="[dbo]" Table="[sourceTable]" Column="LastValueUpdate"/><ColumnReference Database="[DBName]" Schema="[dbo]" Table="[sourceTable]" Column="ModifiedTS"/><ColumnReference Database="[DBName]" Schema="[dbo]" Table="[sourceTable]" Column="OtherValue_lastUpdate"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="69" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><Top RowCount="1" IsPercent="0" WithTies="0"><TopExpression><ScalarOperator ScalarString="(0)"><Const ConstValue="(0)"/></ScalarOperator></TopExpression><RelOp NodeId="3" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="170" EstimateIO="0.00460648" EstimateCPU="0.000344" AvgRowSize="58" EstimatedTotalSubtreeCost="0.00495048" TableCardinality="1402" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[DBName]" Schema="[dbo]" Table="[sourceTable]" Column="record_identifier"/><ColumnReference Database="[DBName]" Schema="[dbo]" Table="[sourceTable]" Column="last_update"/><ColumnReference Database="[DBName]" Schema="[dbo]" Table="[sourceTable]" Column="LastValueUpdate"/><ColumnReference Database="[DBName]" Schema="[dbo]" Table="[sourceTable]" Column="ModifiedTS"/><ColumnReference Database="[DBName]" Schema="[dbo]" Table="[sourceTable]" Column="OtherValue_lastUpdate"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="69" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[DBName]" Schema="[dbo]" Table="[sourceTable]" Column="record_identifier"/></DefinedValue><DefinedValue><ColumnReference Database="[DBName]" Schema="[dbo]" Table="[sourceTable]" Column="last_update"/></DefinedValue><DefinedValue><ColumnReference Database="[DBName]" Schema="[dbo]" Table="[sourceTable]" Column="LastValueUpdate"/></DefinedValue><DefinedValue><ColumnReference Database="[DBName]" Schema="[dbo]" Table="[sourceTable]" Column="ModifiedTS"/></DefinedValue><DefinedValue><ColumnReference Database="[DBName]" Schema="[dbo]" Table="[sourceTable]" Column="OtherValue_lastUpdate"/></DefinedValue></DefinedValues><Object Database="[DBName]" Schema="[dbo]" Table="[sourceTable]" Index="[idx_sourceTable_modifiedts]" IndexKind="Clustered"/><SeekPredicates><SeekPredicateNew><SeekKeys><StartRange ScanType="GT"><RangeColumns><ColumnReference Database="[DBName]" Schema="[dbo]" Table="[sourceTable]" Column="ModifiedTS"/></RangeColumns><RangeExpressions><ScalarOperator ScalarString="CONVERT_IMPLICIT(timestamp,[@modifiedts],0)"><Identifier><ColumnReference Column="ConstExpr1010"><ScalarOperator><Convert DataType="timestamp" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="@modifiedts"/></Identifier></ScalarOperator></Convert></ScalarOperator></ColumnReference></Identifier></ScalarOperator></RangeExpressions></StartRange></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></Top></RelOp></ComputeScalar></RelOp></Update></RelOp><ParameterList><ColumnReference Column="@modifiedts" ParameterCompiledValue="0x00000000008EB813" ParameterRuntimeValue="0x0000000000914056"/></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

Answer 1

Задача решена. Неправильный фактический счетчик строк был вызван плохой статистикой, а блокировка - большим количеством очень маленьких запросов блокировки в очереди, работающих с таблицей. Все они были слишком малы, чтобы генерировать отчеты о блокировке даже с порогом в 1 секунду, но достаточно, чтобы вызывать блокировку в диапазоне более 10 секунд. Я сделал выбор READUNCOMMITTED и изменил код приложения для работы с этим.