Actual vs. Estimated Row Counts

I have a stored procedure that contains a complex query of approximately 16
joins, (a mixture of inner and left joins). These joins are mostly on
standard user tables, but includes joins on two temporary tables and two non-
indexable views.
I have rewritten the query so as to reduce the reads from 5 million plus, to
about 1.6 million reads using some dynamic sql and adding additional indexes.
Still, in the execution plan, several of the user tables have estimated vs.
actual row counts that are quite different, such as 1 Estimated row versus
135,000 actual rows. These tables are using, what I consider the appropriate
index, but, as stated, the actual vs. estimated are way off.
The procedure takes several arguments and I set those arguments as local
variables in the procedure to prevent parameter sniffing.
I was hoping to get a better plan by getting the actual versus estimated row
counts closer. I have ran "update statistics <table_name> with fullscan" on
all the major tables used in the query and especially on those tables whose
actual vs. estimated are way off. This had no effect on the actual vs.
estimated row counts. I have also run sp_updatestats, dbcc dbreindex on the
tables, sp_recompile, but in each case, the estimated vs. actual is still way
off.
I have considered reordering some of the inner and left joins, but was not
sure if that would have any affect.
Would you be able to advise on what else one might do to get the actual vs.
estimated more in line?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
its difficult to get a better and more accurate plan after your actions
(updating stats etc...)
Specially with left joins...
the only way is to force the way you want to do your joins (manually
specify the hash/ merge option in the joins, setup the better order for your
joins etc...)
in this case you force SQL Server to use your hints instead of using its own
generated plan.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:679360284efd2@.uwe...
>I have a stored procedure that contains a complex query of approximately 16
> joins, (a mixture of inner and left joins). These joins are mostly on
> standard user tables, but includes joins on two temporary tables and two
> non-
> indexable views.
> I have rewritten the query so as to reduce the reads from 5 million plus,
> to
> about 1.6 million reads using some dynamic sql and adding additional
> indexes.
> Still, in the execution plan, several of the user tables have estimated
> vs.
> actual row counts that are quite different, such as 1 Estimated row versus
> 135,000 actual rows. These tables are using, what I consider the
> appropriate
> index, but, as stated, the actual vs. estimated are way off.
> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
> I was hoping to get a better plan by getting the actual versus estimated
> row
> counts closer. I have ran "update statistics <table_name> with fullscan"
> on
> all the major tables used in the query and especially on those tables
> whose
> actual vs. estimated are way off. This had no effect on the actual vs.
> estimated row counts. I have also run sp_updatestats, dbcc dbreindex on
> the
> tables, sp_recompile, but in each case, the estimated vs. actual is still
> way
> off.
> I have considered reordering some of the inner and left joins, but was not
> sure if that would have any affect.
> Would you be able to advise on what else one might do to get the actual
> vs.
> estimated more in line?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200610/1
>
|||> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
That way, the optimizer uses hard-coded values to determine selectivity. Something like:
= 10% (actually, it might go by density in the statistics, if available)
> 30%
BETWEEN 25%
The percentage for the values differs from release to release, and can change even between service
packs. So, in other words, a variable is *not* the same thing as the optimizer knowing the value you
are looking for.
For the optimizer to *know* the value and have a static plan, you have to rely on parameter
sniffing. This value in the plan might of course be off for the subsequent usages of the plan.
Or, you would have to get a new plan for each execution. Either break out the critical parts of the
procedure to its own procedures and create them WITH RECOMPILE, or in 2005 you can add RECOMPILE
hint at the query level. Or you can in 2005 even say OPTIMIZE FOR a certain value.
You can of course add WITH RECOMPILE for the whole procedure, but then none of the DML statements in
the procedure will be pre-optimized.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:679360284efd2@.uwe...
>I have a stored procedure that contains a complex query of approximately 16
> joins, (a mixture of inner and left joins). These joins are mostly on
> standard user tables, but includes joins on two temporary tables and two non-
> indexable views.
> I have rewritten the query so as to reduce the reads from 5 million plus, to
> about 1.6 million reads using some dynamic sql and adding additional indexes.
> Still, in the execution plan, several of the user tables have estimated vs.
> actual row counts that are quite different, such as 1 Estimated row versus
> 135,000 actual rows. These tables are using, what I consider the appropriate
> index, but, as stated, the actual vs. estimated are way off.
> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
> I was hoping to get a better plan by getting the actual versus estimated row
> counts closer. I have ran "update statistics <table_name> with fullscan" on
> all the major tables used in the query and especially on those tables whose
> actual vs. estimated are way off. This had no effect on the actual vs.
> estimated row counts. I have also run sp_updatestats, dbcc dbreindex on the
> tables, sp_recompile, but in each case, the estimated vs. actual is still way
> off.
> I have considered reordering some of the inner and left joins, but was not
> sure if that would have any affect.
> Would you be able to advise on what else one might do to get the actual vs.
> estimated more in line?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200610/1
>
|||Thanks for your reply Jeje.
In regards to your comment of setting up a better order on my joins, is the
guide to setting up joins...ordering joins with the most selective join
first, followed by the next most selective join, etc., etc.?
Jeje wrote:[vbcol=seagreen]
>its difficult to get a better and more accurate plan after your actions
>(updating stats etc...)
>Specially with left joins...
>the only way is to force the way you want to do your joins (manually
>specify the hash/ merge option in the joins, setup the better order for your
>joins etc...)
>in this case you force SQL Server to use your hints instead of using its own
>generated plan.
>[quoted text clipped - 36 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1

Actual vs. Estimated Row Counts

I have a stored procedure that contains a complex query of approximately 16
joins, (a mixture of inner and left joins). These joins are mostly on
standard user tables, but includes joins on two temporary tables and two non
-
indexable views.
I have rewritten the query so as to reduce the reads from 5 million plus, to
about 1.6 million reads using some dynamic sql and adding additional indexes
.
Still, in the execution plan, several of the user tables have estimated vs.
actual row counts that are quite different, such as 1 Estimated row versus
135,000 actual rows. These tables are using, what I consider the appropriate
index, but, as stated, the actual vs. estimated are way off.
The procedure takes several arguments and I set those arguments as local
variables in the procedure to prevent parameter sniffing.
I was hoping to get a better plan by getting the actual versus estimated row
counts closer. I have ran "update statistics <table_name> with fullscan" on
all the major tables used in the query and especially on those tables whose
actual vs. estimated are way off. This had no effect on the actual vs.
estimated row counts. I have also run sp_updatestats, dbcc dbreindex on the
tables, sp_recompile, but in each case, the estimated vs. actual is still wa
y
off.
I have considered reordering some of the inner and left joins, but was not
sure if that would have any affect.
Would you be able to advise on what else one might do to get the actual vs.
estimated more in line?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1its difficult to get a better and more accurate plan after your actions
(updating stats etc...)
Specially with left joins...
the only way is to force the way you want to do your joins (manually
specify the hash/ merge option in the joins, setup the better order for your
joins etc...)
in this case you force SQL Server to use your hints instead of using its own
generated plan.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:679360284efd2@.uwe...
>I have a stored procedure that contains a complex query of approximately 16
> joins, (a mixture of inner and left joins). These joins are mostly on
> standard user tables, but includes joins on two temporary tables and two
> non-
> indexable views.
> I have rewritten the query so as to reduce the reads from 5 million plus,
> to
> about 1.6 million reads using some dynamic sql and adding additional
> indexes.
> Still, in the execution plan, several of the user tables have estimated
> vs.
> actual row counts that are quite different, such as 1 Estimated row versus
> 135,000 actual rows. These tables are using, what I consider the
> appropriate
> index, but, as stated, the actual vs. estimated are way off.
> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
> I was hoping to get a better plan by getting the actual versus estimated
> row
> counts closer. I have ran "update statistics <table_name> with fullscan"
> on
> all the major tables used in the query and especially on those tables
> whose
> actual vs. estimated are way off. This had no effect on the actual vs.
> estimated row counts. I have also run sp_updatestats, dbcc dbreindex on
> the
> tables, sp_recompile, but in each case, the estimated vs. actual is still
> way
> off.
> I have considered reordering some of the inner and left joins, but was not
> sure if that would have any affect.
> Would you be able to advise on what else one might do to get the actual
> vs.
> estimated more in line?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200610/1
>|||> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
That way, the optimizer uses hard-coded values to determine selectivity. Som
ething like:
= 10% (actually, it might go by density in the statistics, if available)
> 30%
BETWEEN 25%
The percentage for the values differs from release to release, and can chang
e even between service
packs. So, in other words, a variable is *not* the same thing as the optimiz
er knowing the value you
are looking for.
For the optimizer to *know* the value and have a static plan, you have to re
ly on parameter
sniffing. This value in the plan might of course be off for the subsequent u
sages of the plan.
Or, you would have to get a new plan for each execution. Either break out th
e critical parts of the
procedure to its own procedures and create them WITH RECOMPILE, or in 2005 y
ou can add RECOMPILE
hint at the query level. Or you can in 2005 even say OPTIMIZE FOR a certain
value.
You can of course add WITH RECOMPILE for the whole procedure, but then none
of the DML statements in
the procedure will be pre-optimized.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:679360284efd2@.uwe...[vbcol
=seagreen]
>I have a stored procedure that contains a complex query of approximately 16
> joins, (a mixture of inner and left joins). These joins are mostly on
> standard user tables, but includes joins on two temporary tables and two n
on-
> indexable views.
> I have rewritten the query so as to reduce the reads from 5 million plus,
to
> about 1.6 million reads using some dynamic sql and adding additional index
es.
> Still, in the execution plan, several of the user tables have estimated vs
.
> actual row counts that are quite different, such as 1 Estimated row versus
> 135,000 actual rows. These tables are using, what I consider the appropria
te
> index, but, as stated, the actual vs. estimated are way off.
> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
> I was hoping to get a better plan by getting the actual versus estimated r
ow
> counts closer. I have ran "update statistics <table_name> with fullscan" o
n
> all the major tables used in the query and especially on those tables whos
e
> actual vs. estimated are way off. This had no effect on the actual vs.
> estimated row counts. I have also run sp_updatestats, dbcc dbreindex on th
e
> tables, sp_recompile, but in each case, the estimated vs. actual is still
way
> off.
> I have considered reordering some of the inner and left joins, but was not
> sure if that would have any affect.
> Would you be able to advise on what else one might do to get the actual vs
.
> estimated more in line?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200610/1
>[/vbcol]|||Thanks for your reply Jeje.
In regards to your comment of setting up a better order on my joins, is the
guide to setting up joins...ordering joins with the most selective join
first, followed by the next most selective join, etc., etc.?
Jeje wrote:[vbcol=seagreen]
>its difficult to get a better and more accurate plan after your actions
>(updating stats etc...)
>Specially with left joins...
>the only way is to force the way you want to do your joins (manually
>specify the hash/ merge option in the joins, setup the better order for you
r
>joins etc...)
>in this case you force SQL Server to use your hints instead of using its ow
n
>generated plan.
>
>[quoted text clipped - 36 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1

Actual vs. Estimated Row Counts

I have a stored procedure that contains a complex query of approximately 16
joins, (a mixture of inner and left joins). These joins are mostly on
standard user tables, but includes joins on two temporary tables and two non-
indexable views.
I have rewritten the query so as to reduce the reads from 5 million plus, to
about 1.6 million reads using some dynamic sql and adding additional indexes.
Still, in the execution plan, several of the user tables have estimated vs.
actual row counts that are quite different, such as 1 Estimated row versus
135,000 actual rows. These tables are using, what I consider the appropriate
index, but, as stated, the actual vs. estimated are way off.
The procedure takes several arguments and I set those arguments as local
variables in the procedure to prevent parameter sniffing.
I was hoping to get a better plan by getting the actual versus estimated row
counts closer. I have ran "update statistics <table_name> with fullscan" on
all the major tables used in the query and especially on those tables whose
actual vs. estimated are way off. This had no effect on the actual vs.
estimated row counts. I have also run sp_updatestats, dbcc dbreindex on the
tables, sp_recompile, but in each case, the estimated vs. actual is still way
off.
I have considered reordering some of the inner and left joins, but was not
sure if that would have any affect.
Would you be able to advise on what else one might do to get the actual vs.
estimated more in line?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1its difficult to get a better and more accurate plan after your actions
(updating stats etc...)
Specially with left joins...
the only way is to force the way you want to do your joins (manually
specify the hash/ merge option in the joins, setup the better order for your
joins etc...)
in this case you force SQL Server to use your hints instead of using its own
generated plan.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:679360284efd2@.uwe...
>I have a stored procedure that contains a complex query of approximately 16
> joins, (a mixture of inner and left joins). These joins are mostly on
> standard user tables, but includes joins on two temporary tables and two
> non-
> indexable views.
> I have rewritten the query so as to reduce the reads from 5 million plus,
> to
> about 1.6 million reads using some dynamic sql and adding additional
> indexes.
> Still, in the execution plan, several of the user tables have estimated
> vs.
> actual row counts that are quite different, such as 1 Estimated row versus
> 135,000 actual rows. These tables are using, what I consider the
> appropriate
> index, but, as stated, the actual vs. estimated are way off.
> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
> I was hoping to get a better plan by getting the actual versus estimated
> row
> counts closer. I have ran "update statistics <table_name> with fullscan"
> on
> all the major tables used in the query and especially on those tables
> whose
> actual vs. estimated are way off. This had no effect on the actual vs.
> estimated row counts. I have also run sp_updatestats, dbcc dbreindex on
> the
> tables, sp_recompile, but in each case, the estimated vs. actual is still
> way
> off.
> I have considered reordering some of the inner and left joins, but was not
> sure if that would have any affect.
> Would you be able to advise on what else one might do to get the actual
> vs.
> estimated more in line?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1
>|||> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
That way, the optimizer uses hard-coded values to determine selectivity. Something like:
= 10% (actually, it might go by density in the statistics, if available)
> 30%
BETWEEN 25%
The percentage for the values differs from release to release, and can change even between service
packs. So, in other words, a variable is *not* the same thing as the optimizer knowing the value you
are looking for.
For the optimizer to *know* the value and have a static plan, you have to rely on parameter
sniffing. This value in the plan might of course be off for the subsequent usages of the plan.
Or, you would have to get a new plan for each execution. Either break out the critical parts of the
procedure to its own procedures and create them WITH RECOMPILE, or in 2005 you can add RECOMPILE
hint at the query level. Or you can in 2005 even say OPTIMIZE FOR a certain value.
You can of course add WITH RECOMPILE for the whole procedure, but then none of the DML statements in
the procedure will be pre-optimized.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:679360284efd2@.uwe...
>I have a stored procedure that contains a complex query of approximately 16
> joins, (a mixture of inner and left joins). These joins are mostly on
> standard user tables, but includes joins on two temporary tables and two non-
> indexable views.
> I have rewritten the query so as to reduce the reads from 5 million plus, to
> about 1.6 million reads using some dynamic sql and adding additional indexes.
> Still, in the execution plan, several of the user tables have estimated vs.
> actual row counts that are quite different, such as 1 Estimated row versus
> 135,000 actual rows. These tables are using, what I consider the appropriate
> index, but, as stated, the actual vs. estimated are way off.
> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
> I was hoping to get a better plan by getting the actual versus estimated row
> counts closer. I have ran "update statistics <table_name> with fullscan" on
> all the major tables used in the query and especially on those tables whose
> actual vs. estimated are way off. This had no effect on the actual vs.
> estimated row counts. I have also run sp_updatestats, dbcc dbreindex on the
> tables, sp_recompile, but in each case, the estimated vs. actual is still way
> off.
> I have considered reordering some of the inner and left joins, but was not
> sure if that would have any affect.
> Would you be able to advise on what else one might do to get the actual vs.
> estimated more in line?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1
>|||Thanks for your reply Jeje.
In regards to your comment of setting up a better order on my joins, is the
guide to setting up joins...ordering joins with the most selective join
first, followed by the next most selective join, etc., etc.?
Jeje wrote:
>its difficult to get a better and more accurate plan after your actions
>(updating stats etc...)
>Specially with left joins...
>the only way is to force the way you want to do your joins (manually
>specify the hash/ merge option in the joins, setup the better order for your
>joins etc...)
>in this case you force SQL Server to use your hints instead of using its own
>generated plan.
>>I have a stored procedure that contains a complex query of approximately 16
>> joins, (a mixture of inner and left joins). These joins are mostly on
>[quoted text clipped - 36 lines]
>> vs.
>> estimated more in line?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1

Actual execution plan vs Estimated execution plan

The benefit of the actual execution plan is that you can see the actual number of rows passing through each step - compared to the estimated number of rows.

But what about the "cost percentages" ?

I believe I've read somewhere that these percentages is still just an estimate and is not based on the real execution.

Does anyone know this and preferable have a link to something that documents it?

ThanksThe cost percentages is what let you know if you need to change table placement in JOINS or use index to improve performance. The information you are looking is in SQL Server Profiler in Management Studio or Enterprise Manager. You use the Profiler to find duration of SQL statements and more. Run a search for Profiler in SQL Server BOL (books online). Hope this helps.

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/Forum...eneral/200609/1So 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:

Quote:

Originally Posted by

>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/Forum...eneral/200609/1|||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:

Quote:

Originally Posted by

>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?
>

Quote:

Originally Posted by

>>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]

Quote:

Originally Posted by

>>
>>I am running SQL 2000 SP4.

--
Message posted via http://www.sqlmonster.com|||cbrichards via SQLMonster.com (u3288@.uwe) writes:

Quote:

Originally Posted by

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.

So this is what is happening: SQL Server engage in something known as
parameter sniffing. When SQL Server runs a stored procedure for which
there is no plan available in the procedure cache, the optimizer builds
a plan for the procedure. It then uses the actual values of the input
parameters as guidance for the plan.

This can sometimes backfire, for instance if the first invocation is
for an atypical value. It appears that in your case, there is little
difference for one of the input cases, but the balance tips over in
favour of a plan that is detrimental for the other input cases.

The situation is not entirely trivial to handle. There are several
options, but all have their drawbacks.

To start with DBCC FREEPROCACHE, since this affects the entire server, this
is a really bad idea.

If you always want the same plan, you can use an index hint, so that
say

dbo.Table_Z z WITH (INDEX = IDX1_pFID)

to force the use of the non-clustred index. This may still not give
the plan you want, though, as the optimizer can choose some other
combination. (On SQL 2005 you can force the exact plan if you wish.) A
more seroius problem with this approach is that in two years when
the data has changed, another plan is much better. So index hints should
only be used when you are convinced that the index in question is
always the best.

Another alternative is to add WITH RECOMPILE to the procedure definition.
In this case the procedure gets recompiled every time it's called, which
means that you can get a new plan each time. The obvious drawback is
that you get a higher overhead. If the procedure is just that statement,
it's not that big deal, but if the procedure is several hundread lines
long, there is reason for concern.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||cbrichards via SQLMonster.com wrote:

Quote:

Originally Posted by

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

Something likely unrelated to your issue (Erland answered that one
already) as it does not change the semantics of your statement but I
though I mention it nevertheless: it seems two of the last lines of this
SQL statement are pretty superfluous as all three tables are joined on
MainID anyway. So I'd leave only "AND x.MainID = @.MainID" in there
because Table_X seems to be your major table.

Ah, and another remark: I find these table and column names highly
unspecific. Changing them to something more meaningful (identifiers are
not restricted to 8 characters) will probably help maintain the
application.

Kind regards

robert