Re: Actual and Estimated Rows

So perhaps a little more information will be helpful.

First of all, this is the query statement that generates the differing actual
versus estimated rows:

(The variables are of type int, sent as arguments to the stored procedure.)

SELECT y.rpFID, x.dCode, x.Desc, x.dcUID
FROM dbo.Table_X x
JOIN dbo.Table_Y y
ON x.dcUID = y.dcFID
AND x.MainID = y.MainID
JOIN dbo.Table_Z z
ON y.rpFID = z.rpUID
AND y.MainID = z.MainID
WHERE z.pFID = @pID
AND z.MainID = @MainID
AND x.MainID = @MainID
AND y.MainID = @MainID


I have run:
UPDATE STATISTICS dbname.Table_X WITH FULLSCAN
UPDATE STATISTICS dbname.Table_Y WITH FULLSCAN
UPDATE STATISTICS dbname.Table_Z WITH FULLSCAN

When I execute the stored proc with the following arguments and in this order
I get the following results:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
go
exec dbo.dem_sel_combined @pID = 440, @MainID = 576021
go
exec dbo.dem_sel_combined @pID = 476, @MainID = 576003
go

What this did was drop the reads from 500,000 to 250,000, while the estimated
versus actual dropped on one clustered index seek (on Table_Z) from 85,000
actual and 1 estimated to 4 actual and 1 estimated. On the other hand, the
clustered index seek (on Table_Y) still reports 85,000 actual and 1
estimated.

If I just pull this query out and run on its own, there are 4 records
returned.


When I execute the stored proc with the following arguments and in this order
(note the arguments) I get the following results:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
go
exec dbo.dem_sel_combined @pID = 476, @MainID = 576003
go
exec dbo.dem_sel_combined @pID = 440, @MainID = 576021
go

What this did was drop the reads from 250,000 to 79, while the estimated
versus actual on one clustered index seek (on Table_Z) remained 4 actual and
1 estimated while the clustered index seek (on Table_Y) dropped from 85,000
actual and 1 estimated to 5 actual and 1 estimated.

Any ideas how to always have the plan optimized to the lower read count, or
stated another way, optimized to the correct actual versus estimated row
counts?



cbrichards wrote:
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

  • Update statement in trigger gets bad execution plan
    ... -- Columns 3 through 10 snipped from Usenet post ... I had about 7,800 rows in GenRgl ... bad choice of execution plan for this statement. ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Server Query Analyzer faster than ADO.Net SQL Data Provider
    ... Consider a stored proc defined as follows: ... In 6.5, at compile time SQL ... when compiling a plan. ... execution plans, but a key requirement for everything to work as expected ...
    (microsoft.public.sqlserver.programming)
  • Re: sql server issue.
    ... >but i doesn't know why for these reasons why it is making Clustered Index ... indicates that the chosen execution plan is optimal for this specific ... query and this specific sample. ... The execution plan shows that the rows are then sorted, ...
    (microsoft.public.sqlserver.programming)
  • Re: SP runs slower than same query in Query analyzer. Why??
    ... Consider a stored proc defined as follows: ... In 6.5, at compile time SQL ... > when compiling a plan. ... > execution plans, but a key requirement for everything to work as expected ...
    (microsoft.public.sqlserver.clients)
  • Re: Query optimizer issue
    ... Consider a stored proc defined as follows: ... when compiling a plan. ... parameter @p1 into the query at compile time before a plan for the query ... execution plans, but a key requirement for everything to work as expected ...
    (microsoft.public.sqlserver.server)