Actual and Estimated Rows

I have a stored procedure that will execute with less than 1,000 reads one
time (with a specified set of parameters), then with a different set of
parameters the procedure executes with close to 500,000 reads (according to
Profiler).

In comparing the execution plans, they are the same, except for the actual
and estimated number of rows. When the proc runs with parameters that produce
reads that are less than 1,000 the actual and estimated number of rows equal
1. When the proc runs with parameters that produce reads are near 500,000 the
actual rows are approximately 85,000 and the estimated rows equal 1.

Then I run:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

If I then reverse the order of execution by executing the procedure that
initially executes with close to 500,000 reads first, the reads drop to less
than 2,000. The execution plan shows the acutual number of rows equal to 1,
and the estimated rows equal to 2.27. Then when I run the procedure that
initially executed with less than 1,000 reads, it continues to run at less
than 1,000 reads, and the actual number of rows is equal to 1 and the
estimated rows equal to 2.27. When run in this order, there is consistency in
the actual and estimated number of rows and the reads for both executions
with differing parameters are within reason.

Do I need to run DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE on production
and then ensure that the procedure that ran close to 500,000 reads is run
first to ensure the proper plan, as well as using a KEEP PLAN option? Or,
what other options might you recommend?

I am running SQL 2000 SP4.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-general/200609/1

.

Relevant Pages

  • Re: Short-circuit does not appear to be working
    ... A query execution plan must be able to handle ALL possible values of the ... are two different execution plans and T-SQL keeps only one; ... one-pass compiler and was never intended for ... Univac had a killer FORTRAN compiler that optimized ...
    (microsoft.public.sqlserver.programming)
  • Re: execution plan for single stored procedure from profiler
    ... execution plans. ... get the execution plan for the procedure I'm watching and not the ... we get all execution tree data not just for ... likely we'll have many SPIDs that we'll see in the trace. ...
    (comp.databases.ms-sqlserver)
  • Re: Stored Proc runs well on one server, terribly on another
    ... querying are identical, the execution plans for both queries are ... identical and the i/o figures are identical but the execution times are ...
    (comp.databases.sybase)
  • Re: SUM() Taking Too Long
    ... Most likely the queries were using a different execution plans. ... > I've got a UDF that executes the following query: ... > The problem is this query takes about 12 seconds to execute. ...
    (microsoft.public.sqlserver.programming)
  • Re: Actual and Estimated Rows
    ... DBCC FREEPROCCACHE ... versus actual dropped on one clustered index seek from 85,000 ... In comparing the execution plans, they are the same, except for the actual ... The execution plan shows the acutual number of rows equal to 1, ...
    (comp.databases.ms-sqlserver)