In terms of query execution, SQL Server displays an incorrect "actual row count" by the index used, and performance is terribly slow

Question

Today I came across an interesting performance issue with a stored procedure running on Sql Server 2005 SP2 in db running at compatible level 80 (SQL2000).

The run lasts about 8 minutes, and the execution plan indicates the use of an index with the actual number of rows 1.339.241.423, which is about 1000 times higher than the actual actual row number of the table, which is 1.144.640 as shown by the correctly estimated number of rows. So the actual row count set by the query plan optimizer is definitely wrong!

It is interesting that when copying the values ​​of procs parameters inside proc into local variables and using local variables in a real query, everything works fine - proc works for 18 seconds, and the correct actual line counter is displayed in the execution plan.

EDIT: As suggested by TrickyNixon, this seems to be a sign of the problem of sniffing a parameter. But in fact, I get in both cases the exact execution plan. The same indexes are used in the same order. The only difference I see is the path to the high actual number of rows in the PK_ED_Transitions index with direct use of parametervalues.

I have been doing dbcc dbreindex and UPDATE STATISTICS without any success. dbcc show_statistics also shows good data for the index.

The process is created using RECOMPILE, so every time it starts a new execution plan, compilation takes place.

To be more specific - this one runs quickly:

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 

And this version is very slow, but it creates exactly the same execution plan (in addition to the very high actual number of rows in the index used):

 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 

Any ideas? Does anyone know where Sql Server gets the actual row counter value when calculating query plans?

Update . I tried the request on another server if the copy mode is set to 90 (Sql2005). This is the same behavior. I think I will open the ms support call because it looks like an error.

Answer 1

Well, finally, I got to it myself.

The two query plans differ in small details, which I skipped first. slow uses the nested loop operator to combine the two subqueries together. And this leads to a large number in the current column row for the scan index operator, which is simply the result of multiplying the number of input lines a with the number of input lines b.

I still don’t know why the optimizer decides to use nested loops instead of a hash match, which in this case starts 1000 timers faster, but I could deal with this problem by creating a new index, so the engine makes index look for statt instead of index scan under nested loops.

Answer 2

When you check execution plans for stored proc against a copy / paste request, do you use settlement plans or real plans? Be sure to click "Request", "Enable Execution Plan", and then run each request. Compare these plans and see what the differences are.

Answer 3

This sounds like a case of sniffing parameters. Here's an excellent explanation and possible solutions: I smell the parameter!

Here is another StackOverflow thread that accesses it: The Sniffing (or Spoofing) option in SQL Server

Answer 4

It seems to me that the statistics were wrong. Restoring indexes does not necessarily update them.

Have you tried explicit UPDATE STATISTICS for the affected tables?

Answer 5

Did you run sp_spaceused so that SQL Server gets the correct summary for this table? I believe in SQL 2000 that the engine used such metadata when building execution plans. Previously, to update metadata in some fast-changing tables, we had to do DBCC UPDATEUSAGE , because SQL Server chose the wrong indexes due to invalid row counter data.

You are running SQL 2005, and BOL says that in 2005 you will no longer have to run UpdateUsage, but since you are in 2000 compatibility mode, you may find that it is still required.