|
|
Posted by cbrichards via SQLMonster.com on 09/15/06 16:14
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
Navigation:
[Reply to this message]
|