Query plan miscalculates row count causing system slow down?

Has anyone seem this before. I've actually caught it once but I think
it's happening multiple times. I have a partitioned view and
performance is generally really good. However, every once in a great
while we'll see a stored procedure "hang" and just run for hours.
Doing much digging the only abnormalities I found where these: 1) the
server was requesting a large amount of PAGE and KEY locks (meaning an
index lock was requested. Checking the locks for that SPID confirmed
this) 2) At one time during testing I saw that the execution plan
called for an estimated 1 billion rows to be returned. Amazing since
the underlying table only have 400,000 records. Now, the theory is
this: Since I'm using a partitioned view and Microsoft claims that
they retain no histogram (statistical data from which to build an
"intelligent" execution plan) somehow it's figuring this 1 billion
estimated row count again and is entering some kind of horrendous
loop. Any thoughts?Have you checked the fragentation of the indexes on the table(s) that the sp
uses?
dbcc showcontig ('table_name').
If the scan desity is low, you might want to do a defrag.
dbcc indexdefrag ('database_name','table_name','index_nam
e')
or in 2005
alter index <index_name> | ALL
rebuild with online = ON
on <table_name>
Sometimes rebuilding is better because is certain cases, indexdefrag doesn't
make the all of index pages contiguous.
--
MG
"tmorris" wrote:

> Has anyone seem this before. I've actually caught it once but I think
> it's happening multiple times. I have a partitioned view and
> performance is generally really good. However, every once in a great
> while we'll see a stored procedure "hang" and just run for hours.
> Doing much digging the only abnormalities I found where these: 1) the
> server was requesting a large amount of PAGE and KEY locks (meaning an
> index lock was requested. Checking the locks for that SPID confirmed
> this) 2) At one time during testing I saw that the execution plan
> called for an estimated 1 billion rows to be returned. Amazing since
> the underlying table only have 400,000 records. Now, the theory is
> this: Since I'm using a partitioned view and Microsoft claims that
> they retain no histogram (statistical data from which to build an
> "intelligent" execution plan) somehow it's figuring this 1 billion
> estimated row count again and is entering some kind of horrendous
> loop. Any thoughts?
>|||Look in BOL for update statistics
TheSQLGuru
President
Indicium Resources, Inc.
"tmorris" <TheRealPawn@.gmail.com> wrote in message
news:1177340474.292787.166810@.q75g2000hsh.googlegroups.com...
> Has anyone seem this before. I've actually caught it once but I think
> it's happening multiple times. I have a partitioned view and
> performance is generally really good. However, every once in a great
> while we'll see a stored procedure "hang" and just run for hours.
> Doing much digging the only abnormalities I found where these: 1) the
> server was requesting a large amount of PAGE and KEY locks (meaning an
> index lock was requested. Checking the locks for that SPID confirmed
> this) 2) At one time during testing I saw that the execution plan
> called for an estimated 1 billion rows to be returned. Amazing since
> the underlying table only have 400,000 records. Now, the theory is
> this: Since I'm using a partitioned view and Microsoft claims that
> they retain no histogram (statistical data from which to build an
> "intelligent" execution plan) somehow it's figuring this 1 billion
> estimated row count again and is entering some kind of horrendous
> loop. Any thoughts?
>|||DBCC dbreindex is preformed on all tables once a week as a form of
matanance.
We've concided doing this again midweek or one of the other processes
that update statistics on columns and indexes in SQL 2000. However, in
the test enviroment it doesn't seem to have any impact one way or
another. Like I, and microsoft said, partitioned views don't contain
any statistical history from which to base an exection plan. Thanks
for the suggestion though. I'm looking more of someone to confirm my
theory or say it's complete bunk.
Now, the theory is this: Since I'm using a partitioned view and
Microsoft claims that they retain no histogram (statistical data from
which to build an "intelligent" execution plan) somehow it's figuring
this 1 billion estimated row count again and is entering some kind of
horrendous loop. Any thoughts?
On Apr 23, 11:26 am, Hurme <michael.ge...@.thomson.com> wrote:[vbcol=seagreen]
> Have you checked the fragentation of the indexes on the table(s) that the
sp
> uses?
> dbcc showcontig ('table_name').
> If the scan desity is low, you might want to do a defrag.
> dbcc indexdefrag ('database_name','table_name','index_nam
e')
> or in 2005
> alter index <index_name> | ALL
> rebuild with online = ON
> on <table_name>
> Sometimes rebuilding is better because is certain cases, indexdefrag doesn
't
> make the all of index pages contiguous.
> --
> MG
> "tmorris" wrote:|||We Run DBCC ReIndex on all tables as a weekly maintenance job. And we
looked as doing update statistics 25% midweek but it seemed to have no
effect in our test environment
On Apr 23, 12:42 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> Look in BOL for update statistics
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "tmorris" <TheRealP...@.gmail.com> wrote in message
> news:1177340474.292787.166810@.q75g2000hsh.googlegroups.com...
>

Query Perormance (Problem bookmark)

I have a bookmark caused by the a15.Tr_type_id where condition below. I thin
k
it is because the query tries to satisfy the date condition before going to
get the a15.Tr_type_id condition. If I comment out the a15.Tr_type_id, the
bookmark disappears and performance boosts. I tried a clustered index on
a15.Tr_type_id and it improved it a bit more. Any recommendations much
appreciated?
select a14.Ra_licence_Group_desc Ra_licence_Group_desc,
a14.Ra_gender_id Ra_gender_id,
a14.RA_yr_band_HFI_id RA_yr_band_HFI_id,
a14.RA_yr_band_New_id RA_yr_band_New_id,
a12.Pr_cover_id Pr_cover_id,
count((case when a12.Pr_Group_id = 5 then a11.Vehicle_id else a11.Policy_id
end)) WJXBFS1
into #ZZT5J0302LPMD004
from Z_fat_bse_po_risk_detail a11
join Z_prt_lu_product a12
on (a11.Product_id = a12.Product_id)
join Z_POt_lu_policy a13
on (a11.Policy_id = a13.Policy_id)
join Z_RAt_lu_Rated a14
on (a11.Rated_driver_id = a14.Rated_driver_id)
join Z_TRt_lu_Trans_Subtype a15
on (a11.Tr_sub_type_id = a15.Tr_sub_type_id)
where (a12.Pr_cover_id in ('C', 'F')
and a14.Ra_gender_id in ('F', 'M')
and a14.Ra_licence_id in ('F', 'P')
and a12.Pr_Group_id in (2, 3)
and a14.RA_yr_band_New_id not in (1)
and a11.Po_tr_bus_type_id in (0)
and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a11.Cur_trn_dt between CONVERT(datetime, '2004-03-07 00:00:00', 120)
and CONVERT(datetime, '2005-03-05 00:00:00', 120)
and a15.Tr_type_id in ('HNB', 'HNC', 'HRN', 'INB', 'IRN', 'HPR')
and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a12.Pr_Group_id in (2, 3)
and a13.Po_corp_unit_id in ('GEI')
and a11.Entered_by_id not in (7561, 7570)
and a12.Pr_cover_id in ('C', 'F', 'T')
and a14.Ra_gender_id in ('F', 'M')
and a13.Po_market_src_id not in (14))
group by a14.Ra_licence_Group_desc,
a14.Ra_gender_id,
a14.RA_yr_band_HFI_id,
a14.RA_yr_band_New_id,
a12.Pr_cover_idgolden rule, check statistics job has run before you believe an end user!
"marcmc" wrote:

> I have a bookmark caused by the a15.Tr_type_id where condition below. I th
ink
> it is because the query tries to satisfy the date condition before going t
o
> get the a15.Tr_type_id condition. If I comment out the a15.Tr_type_id, the
> bookmark disappears and performance boosts. I tried a clustered index on
> a15.Tr_type_id and it improved it a bit more. Any recommendations much
> appreciated?
> select a14.Ra_licence_Group_desc Ra_licence_Group_desc,
> a14.Ra_gender_id Ra_gender_id,
> a14.RA_yr_band_HFI_id RA_yr_band_HFI_id,
> a14.RA_yr_band_New_id RA_yr_band_New_id,
> a12.Pr_cover_id Pr_cover_id,
> count((case when a12.Pr_Group_id = 5 then a11.Vehicle_id else a11.Policy_
id
> end)) WJXBFS1
> into #ZZT5J0302LPMD004
> from Z_fat_bse_po_risk_detail a11
> join Z_prt_lu_product a12
> on (a11.Product_id = a12.Product_id)
> join Z_POt_lu_policy a13
> on (a11.Policy_id = a13.Policy_id)
> join Z_RAt_lu_Rated a14
> on (a11.Rated_driver_id = a14.Rated_driver_id)
> join Z_TRt_lu_Trans_Subtype a15
> on (a11.Tr_sub_type_id = a15.Tr_sub_type_id)
> where (a12.Pr_cover_id in ('C', 'F')
> and a14.Ra_gender_id in ('F', 'M')
> and a14.Ra_licence_id in ('F', 'P')
> and a12.Pr_Group_id in (2, 3)
> and a14.RA_yr_band_New_id not in (1)
> and a11.Po_tr_bus_type_id in (0)
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a11.Cur_trn_dt between CONVERT(datetime, '2004-03-07 00:00:00', 120)
> and CONVERT(datetime, '2005-03-05 00:00:00', 120)
> and a15.Tr_type_id in ('HNB', 'HNC', 'HRN', 'INB', 'IRN', 'HPR')
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a12.Pr_Group_id in (2, 3)
> and a13.Po_corp_unit_id in ('GEI')
> and a11.Entered_by_id not in (7561, 7570)
> and a12.Pr_cover_id in ('C', 'F', 'T')
> and a14.Ra_gender_id in ('F', 'M')
> and a13.Po_market_src_id not in (14))
> group by a14.Ra_licence_Group_desc,
> a14.Ra_gender_id,
> a14.RA_yr_band_HFI_id,
> a14.RA_yr_band_New_id,
> a12.Pr_cover_id
>|||ps: do MVP's ever visit this forum
"marcmc" wrote:

> I have a bookmark caused by the a15.Tr_type_id where condition below. I th
ink
> it is because the query tries to satisfy the date condition before going t
o
> get the a15.Tr_type_id condition. If I comment out the a15.Tr_type_id, the
> bookmark disappears and performance boosts. I tried a clustered index on
> a15.Tr_type_id and it improved it a bit more. Any recommendations much
> appreciated?
> select a14.Ra_licence_Group_desc Ra_licence_Group_desc,
> a14.Ra_gender_id Ra_gender_id,
> a14.RA_yr_band_HFI_id RA_yr_band_HFI_id,
> a14.RA_yr_band_New_id RA_yr_band_New_id,
> a12.Pr_cover_id Pr_cover_id,
> count((case when a12.Pr_Group_id = 5 then a11.Vehicle_id else a11.Policy_
id
> end)) WJXBFS1
> into #ZZT5J0302LPMD004
> from Z_fat_bse_po_risk_detail a11
> join Z_prt_lu_product a12
> on (a11.Product_id = a12.Product_id)
> join Z_POt_lu_policy a13
> on (a11.Policy_id = a13.Policy_id)
> join Z_RAt_lu_Rated a14
> on (a11.Rated_driver_id = a14.Rated_driver_id)
> join Z_TRt_lu_Trans_Subtype a15
> on (a11.Tr_sub_type_id = a15.Tr_sub_type_id)
> where (a12.Pr_cover_id in ('C', 'F')
> and a14.Ra_gender_id in ('F', 'M')
> and a14.Ra_licence_id in ('F', 'P')
> and a12.Pr_Group_id in (2, 3)
> and a14.RA_yr_band_New_id not in (1)
> and a11.Po_tr_bus_type_id in (0)
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a11.Cur_trn_dt between CONVERT(datetime, '2004-03-07 00:00:00', 120)
> and CONVERT(datetime, '2005-03-05 00:00:00', 120)
> and a15.Tr_type_id in ('HNB', 'HNC', 'HRN', 'INB', 'IRN', 'HPR')
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a12.Pr_Group_id in (2, 3)
> and a13.Po_corp_unit_id in ('GEI')
> and a11.Entered_by_id not in (7561, 7570)
> and a12.Pr_cover_id in ('C', 'F', 'T')
> and a14.Ra_gender_id in ('F', 'M')
> and a13.Po_market_src_id not in (14))
> group by a14.Ra_licence_Group_desc,
> a14.Ra_gender_id,
> a14.RA_yr_band_HFI_id,
> a14.RA_yr_band_New_id,
> a12.Pr_cover_id
>|||"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:E13FCDE5-D0FD-4D1F-922B-CAEC5C9A584A@.microsoft.com...
> ps: do MVP's ever visit this forum
Yes. I see 12 messages posted here since 2:09PM PST yesterday, the 9th, and
exactly half of those posts came from MVPs Mike Epprecht, Jasper Smith, and
Sue Hoegemeier.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.|||Ahh I see now. It's just that when I used to post on compact framework site
there was a little bubble with mvp which was easily noticeable. Thx.
"Stephen Dybing [MSFT]" wrote:

> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:E13FCDE5-D0FD-4D1F-922B-CAEC5C9A584A@.microsoft.com...
> Yes. I see 12 messages posted here since 2:09PM PST yesterday, the 9th, an
d
> exactly half of those posts came from MVPs Mike Epprecht, Jasper Smith, an
d
> Sue Hoegemeier.
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Please reply to the newsgroups only, thanks.
>
>|||> Ahh I see now. It's just that when I used to post on compact framework
> site
> there was a little bubble with mvp which was easily noticeable.
I guess the SQL Server MVPs are a bit more low-key :-)
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:0A8A3596-B262-4A78-93B3-861293435199@.microsoft.com...[vbcol=seagreen]
> Ahh I see now. It's just that when I used to post on compact framework
> site
> there was a little bubble with mvp which was easily noticeable. Thx.
> "Stephen Dybing [MSFT]" wrote:
>|||I just noticed...if we post through the web based news reader on Microsoft's
communities site, it puts the little MVP bubble thing by our names. My posts
earlier using Agent newsreader (over 99% of my posts) don't have the bubble
thing.
So if you want a bubble by your name, that's how you can have one!
-Sue
"Dan Guzman" wrote:

> I guess the SQL Server MVPs are a bit more low-key :-)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:0A8A3596-B262-4A78-93B3-861293435199@.microsoft.com...
>
>