|
|
Posted by cbrichards via SQLMonster.com on 09/15/06 17:25
Adding on to my previous reply, when I execute the procedures in the
following order (getting the high reads and large actual rows), I obtain the
following explain plan:
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
Rows Executes StmtText
---- -------- --------
4 1 Nested Loops(Inner Join, OUTER REFERENCES:([y].
[dcFID]) OPTIMIZED)
4 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([y].
[rpFID]) OPTIMIZED)
82331 1 | |--Clustered Index Seek(OBJECT:([MyDB].[dbo].
[Table_Y].[IX_MainID_rpdcUID] AS [y]), SEEK:([y].[MainID]=[@MainID]) ORDERED
FORWARD)
4 82331 | |--Clustered Index Seek(OBJECT:([MyDB].[dbo].
[Table_Z].[IX_MainID_rpUID] AS [z]), SEEK:([z].[MainID]=[@MainID] AND [z].
[rpUID]=[MyDB].[dbo].[Table_Y].[rpFID] as [y].[rpFID]), WHERE:([MyDB].[dbo].
[Table_Z].[pFID] as [z].[pFID]=[@pID]) ORDERED FORWARD)
4 4 |--Clustered Index Seek(OBJECT:([MyDB].[dbo].
[Table_X].[IX_MainID_dcUID] AS [x]), SEEK:([x].[MainID]=[@MainID] AND [x].
[dcUID]=[MyDB].[dbo].[Table_Y].[dcFID] as [y].[dcFID]) ORDERED FORWARD)
when I execute the procedures in the following order (getting the low number
of reads and low number of actual rows), I obtain the following explain plan:
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
Rows Executes StmtText
---- -------- --------
4 1 Nested Loops(Inner Join, OUTER REFERENCES:([y].
[dcFID]) OPTIMIZED)
4 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([z].
[rpUID]))
5 1 | |--Index Seek(OBJECT:([MyDB].[dbo].[Table_Z].
[IDX1_pFID] AS [z]), SEEK:([z].[pFID]=[@pID]), WHERE:([MyDB].[dbo].[Table_Z].
[MainID] as [z].[MainID]=[@MainID]) ORDERED FORWARD)
4 5 | |--Clustered Index Seek(OBJECT:([MyDB].[dbo].
[Table_Y].[IX_MainID_rpdcUID] AS [y]), SEEK:([y].[MainID]=[@MainID] AND [y].
[rpFID]=[MyDB].[dbo].[Table_Z].[rpUID] as [z].[rpUID]) ORDERED FORWARD)
4 4 |--Clustered Index Seek(OBJECT:([MyDB].[dbo].
[Table_X].[IX_MainID_dcUID] AS [x]), SEEK:([x].[MainID]=[@MainID] AND [x].
[dcUID]=[MyDB].[dbo].[Table_Y].[dcFID] as [y].[dcFID]) ORDERED FORWARD)
Please help explain how I might always obtain the second explain plan, if
possible.
cbrichards wrote:
>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?
>
>>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
>[quoted text clipped - 27 lines]
>>
>>I am running SQL 2000 SP4.
--
Message posted via http://www.sqlmonster.com
Navigation:
[Reply to this message]
|