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

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

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

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

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

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

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

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

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

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

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

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

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

Мы запускаем SQL Server 2008 R2 на машине с Windows Server 2008 R2 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>