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_name')
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:
> 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_name')
> 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?|||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:
> 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...
> > 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?

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 Not Working Right!

Hello All,
I am probably doing something small with my query that is causing me pain, but somehow the query is acting funky. What I am trying to do is do a search statement to find documents from a table. But the catch is it is taking three parameters. ThesearchString,Typeand theLocation (where the user who is searching belongs to).
When I run my query I get all documents where the location and type is correct. But the searchstring does not even work.

For example:
Lets say I have 3 documents for a LocationID of '2' and the Type for all documents is '0'. Now imagine that the name of the documents as follow: Doc1 = a , Doc2 = b, Doc3 = c.
So now a user wants to search for all docs that starts with 'a'. Remember, Loc ID = '2' and Type = '0'. The result of the query should be Doc1 and only Doc1. But somehow I am getting all three Docs b/c they belong and are the type of the give parameters.
Any help would be greatfull.

Query:

SELECT
Client.FirstName, Client.LastName, Client.MiddleName, Client.LocID, ClientDocuments.DocID, ClientDocuments.DirName, ClientDocuments.LeafName, ClientDocuments.Type, ClientDocuments.CreatedByUser, ClientDocuments.CreatedDate

FROM Client INNER JOIN ClientDocuments ON Client.ClientID = ClientDocuments.ClientID

WHERE ClientDocuments.Type = '0' AND Client.LocID = '3' AND ([ClientDocuments.LeafName] LIKE '%' + @.SR + '%' OR [Client.SSN] LIKE '%' + @.SR + '%' OR [Client.LastName] LIKE '%' + @.SR + '%' OR [Client.FirstName] LIKE '%' + @.SR + '%' OR [Client.MiddleName] LIKE '%' + @.SR + '%' )

The last search criteria in your query will return rows when anyone of the[ClientDocuments.LeafName]/[Client.SSN]/[Client.LastName]/ [Client.FirstName][Client.MiddleName] contains@.SR, right? Make sure you need to do such a search, because there are so many fields to be searched so maybe some field meets the criteria while you're not aware of this. BTW, LIKE '%' + @.SR + '%'means any string contains@.SR, not only string which starts with@.SR|||

Start by either deleting all your brackets or use them correctly. Brackets are for quoting identifiers. The period in Client.MiddleName separates two identifiers (Table Name, and Field Name). [Client.MiddleName] would be a field named... Client.MiddleName. It's incorrect usage, and while it might fly sometimes, you should fix it. It's possibly causing the SQL Parser to go a little crazy.

The brackets as is are not necessary since neither Client nor MiddleName, etc have any characters in it that would otherwise be illegal without. You specify it as either Client.MiddleName or [Client].[MiddleName], but not [Client.MiddleName].

You should note also that while in your example text, you specify there are 3 documents for LocationID of '2', your query says "Client.LocID = '3'". Typo?

|||

Motley:

You should note also that while in your example text, you specify there are 3 documents for LocationID of '2', your query says "Client.LocID = '3'". Typo?

Haha, Hey I did not even see that, yeah it is a typo. Anyways, I will try breaking out the brackets. But do you think that the brackets would have cause the query to act funny?|||Looking it over, it appears to me that everything else is ok, but then again, sometimes I miss the obvious. Your logic is sound atleast.|||

Hey Thanks everyone for there help. I got the query to work perfectly.

Thanks again.