SQL Server Execution Plan Actual Row Count Far Too Large For Simple Select

Question

(Long story short - #3 in my list of strange execution plan notes is the problem, I think).

I'm currently encountering some pretty bad performance with a stored procedure that in the past has performed well - this is in the context of performance testing in which we restore a database, and then put load on the system. I can't figure out what has changed in this portion of our system. There is no substantive blocking according to blocking reports (we set the threshold to 1 second for some time to check this).

By capturing the execution plan, we have identified that a single query within the proc is to blame. The query is a simple

INSERT INTO #table ()
SELECT  
FROM table
WHERE  > @binaryArgumentPassedIntoProc

For the source table: The timestamp column in the WHERE clause is indexed. One of the columns being selected is the PK and is of type varchar. 3 other columns are being selected (for a total of 5). The temp table has no indices/keys or constraints.

The schema on this table hasn't changed with one slight exception - one of the columns on the source table now has TrimTrailingBlanks set to yes. This column is not in the column set being selected from. I can't imagine that this matters but wanted to call it out.

When I restore point-in-time I am unable to replicate the slowness. During production execution (meaning when it ran slowly in performance testing environment) the query takes 30+ seconds sometimes. In the point-in-time backup the query takes under 1 second.

By comparing the execution plan data captured from both the production execution and point-in-time execution, I'm seeing a few confusing pieces. Note that the query (in both situations) inserts 180 rows into the temp table.

The EstimatedRow count in the point in time backup execution is 176.683. It is 2 in the production execution. This is true for the select and insert
The TableCardinality in the production execution for the select is 2, while it is 1578 in the point in time execution. This indicates that the statistics were bad in production, which isn't surprising though I'm not sure how to deal with this in realtime when the system is under such heavy load (there are actually 1578 rows in the table during execution). That being said, this hasn't been an issue in the past when the performance was fine.
The Actual Number of Rows noted in the execution plan that are inserted into the temp table is listed as 3222 in the production execution. In the point in time execution, the Actual Number of Rows is listed as 180.

Regarding (3) - My understanding of Actual Number of Rows is that it is the number of times the iterator called GetNext() when executing the query. I would think that with a simple select (primarily no joins) the Actual Number of Rows value would, at a maximum, be equal to the number of rows in the table at that point (1578).

Finally, by capturing performance counters for the physical and logical disk, it doesn't like like there is significant disk queuing nor a correlation between disk queuing and the slow executions.

Does anyone have any suggestions regarding how I can troubleshoot this further?

We are running SQL Server 2008 R2 on a Windows Server 2008 R2 SP1 machine. The machine has 16 cores and 128 GB of RAM (64 dedicated to SQL server).

UPDATE: I seeded the tables with data after restoring and before the test, and then updated statistics and recompiled the procedure. The actual and expected row counts as well as table cardinality reflected realistic values, but we were still seeing very long executions.

Here is the XML showplan profile statistics trace event output for one of the bad queries in this latest test:

SQL Server Execution Plan Actual Row Count Far Too Large For Simple Select

Answers (1)

Related Questions