how that happen SP sql server

问题:

I get something weird. i ran this sql:

SELECT   Id , GameTypeId , PlayerId , BetAmount , Profit ,
         DateAndTime
FROM     Results
WHERE    DateAndTime >= DATEADD (DAY , -1 , SYSDATETIME ())
         AND
         DateAndTime < SYSDATETIME ()
ORDER BY DateAndTime ASC;

i have noncluster index on the date column
and the actual number of rows that return is
672 row from 1600016 rows in the table. (the estimated row was 1)

after that i ran this sql:

declare @d DATETIME2(7) 
set @d = DATEADD (DAY , -1 , SYSDATETIME ())
declare  @d2 DATETIME2(7)
set @d2  = SYSDATETIME ()

SELECT   Id , GameTypeId , PlayerId , BetAmount , Profit ,
         DateAndTime
FROM     Results
WHERE    DateAndTime >= @d
         AND
         DateAndTime < @d2
ORDER BY DateAndTime ASC;

and the actual execution plan was TABLE SCANE !!! and the actual number of rows that return is
672 row from 1600016 rows in the table. (the estimated row was 144000 r0ws)

some 1 know what happend here ?!?!?

回答1:

Because you're using variables for your values, the query optimizer doesn't know how selective your WHERE clause is, and decides to use a table scan. Try creating a clustered index on your DateAndTime field.

回答2:

Try

declare @d DATETIME2(7) 
set @d = DATEADD (DAY , -1 , SYSDATETIME ())
declare  @d2 DATETIME2(7)
set @d2  = SYSDATETIME ()

SELECT   Id , GameTypeId , PlayerId , BetAmount , Profit ,
         DateAndTime
FROM     Results
WHERE    DateAndTime >= @d
         AND
         DateAndTime < @d2
ORDER BY DateAndTime ASC
OPTION (RECOMPILE);

This will recompile the plan for the statement once the value of the variables are known and so allow accurate cardinality estimates to be used. If you know you will always be selecting a very small percentage you could just use the FORCESEEK (if SQL Server 2008) hint instead to avoid the recompiles but using this hint may be catastrophically bad for larger ranges because of the number of key lookups!

回答3:

In addition to Martin's answer...

Results.DateAndTime should be datetime2(7) too as per the variables. if not, you most likely have a datatype precedence issue

回答4:

If the number of estimated rows is very high, the optimizer comes to the conclusion that a full table scan might be more effective than an index seek + RID lookup.

so, the question is why the number of estimated rows is way off?

I'm, unfortunately, not so deep into SQL Server yet. However, I'd say that's because of the bind parameters; that can affect the cardinality estimates (estimates rows) quite a lot (at least in other databases).

回答5:

I've found that when the estimated number of rows is vastly different from the actual number, you're either missing statistics somewhere or the statistics that you have are out of date. You should be able to look at a query plan in SSMS and find out which statistics are either missing or out of date based on cardinality estimates for the different operators.