StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Server query execution plan shows wrong "actual row count" on an used index and performance is terrible slow
text
Body
copied!<p>Today i stumbled upon an interesting performance problem with a stored procedure running on Sql Server 2005 SP2 in a db running on compatible level of 80 (SQL2000).</p> <p>The proc runs about 8 Minutes and the execution plan shows the usage of an index with an actual row count of 1.339.241.423 which is about factor 1000 higher than the "real" actual rowcount of the table itself which is 1.144.640 as shown correctly by estimated row count. So the actual row count given by the query plan optimizer is definitly wrong!</p> <p><img src="https://i.stack.imgur.com/kESKH.png" alt="alt text"></p> <p>Interestingly enough, when i copy the procs parameter values inside the proc to local variables and than use the local variables in the actual query, everything works fine - the proc runs 18 seconds and the execution plan shows the right actual row count.</p> <p><strong>EDIT:</strong> As suggested by TrickyNixon, this seems to be a sign of the parameter sniffing problem. But actually, i get in both cases exact the same execution plan. Same indices are beeing used in the same order. The only difference i see is the way to high actual row count on the PK_ED_Transitions index when directly using the parametervalues.</p> <p>I have done dbcc dbreindex and UPDATE STATISTICS already without any success. dbcc show_statistics shows good data for the index, too.</p> <p>The proc is created WITH RECOMPILE so every time it runs a new execution plan is getting compiled.</p> <p>To be more specific - this one runs fast:</p> <pre><code>CREATE Proc [dbo].[myProc]( @Param datetime ) WITH RECOMPILE as set nocount on declare @local datetime set @local = @Param select some columns from table1 where column = @local group by some other columns </code></pre> <p>And this version runs terribly slow, but produces exactly the same execution plan (besides the too high actual row count on an used index):</p> <pre><code>CREATE Proc [dbo].[myProc]( @Param datetime ) WITH RECOMPILE as set nocount on select some columns from table1 where column = @Param group by some other columns </code></pre> <p>Any ideas? Anybody out there who knows where Sql Server gets the actual row count value from when calculating query plans?</p> <p><strong>Update</strong>: I tried the query on another server woth copat mode set to 90 (Sql2005). Its the same behavior. I think i will open up an ms support call, because this looks to me like a bug.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload