100% CPU Usage (Part 2)

[Paul]

Hi all

Please read my previous post here if this interests you:

http://groups.google.co.uk/group/com...u+usage&hl=en&

I have (or rather a colleague has) found the situation that appears to
be causing this problem but I am still no further to finding a
solution.

It appears that if we execute a query against one particular table in
the database and that query requests data that is not indexed, or a
column in the where clause is not indexed then this is when problems
occur.

The table cannot be copied using DTS reporting the error:

"Error occurred copying row 3 - unspecified error".

However, we can query using:

"SELECT TOP n FROM <Table>"

where n so far has been between 3 and 100000.

However we cannot do SELECT * FROM <Table>.

All we can guess at for now is that the problem occurs when a query is
performed to try and fetch data that is not indexed, using an index in
the where clause.

i.e. SELECT <Non-indexed column> FROM <Table> WHERE <Indexed Column> =
n

Could it be possible that we have one 'rogue' row in the table somehow
that is causing the problem?

The only thing to dispel this is that we can do the following:

SELECT * FROM <Table> WHERE ID < 1

which returns 1 row with ID = -1.

We cannot do:

SELECT * FROM <Table> WHERE ID < 200 even though we know that the only
record matching this criteria is the record with ID = -1.

Therefore the exact same data should be returned yet one query fails
and one works.

The actual problem that we see is that memory usage climbs
dramatically, then once all memory is used, the CPU usage climbs to
100% and stays there until we have to restart SQL Server.

Any suggestions on this would be hugely appreciated.

Thanks,

Paul

[Dan Guzman]

Have you done a DBCC CHECKDB to rule out corruption?

SELECT * FROM <Table> WHERE ID < 1

which returns 1 row with ID = -1.

We cannot do:

SELECT * FROM <Table> WHERE ID < 200 even though we know that the only
record matching this criteria is the record with ID = -1.

Therefore the exact same data should be returned yet one query fails
and one works.

Do get an error message or does the 'failed' query not complete with 100%
CPU? Do you get the same execution plan with both queries?

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Paul" <pa***********@hotmail.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com... Hi all

Please read my previous post here if this interests you:

http://groups.google.co.uk/group/com...u+usage&hl=en&

I have (or rather a colleague has) found the situation that appears to
be causing this problem but I am still no further to finding a
solution.

It appears that if we execute a query against one particular table in
the database and that query requests data that is not indexed, or a
column in the where clause is not indexed then this is when problems
occur.

The table cannot be copied using DTS reporting the error:

"Error occurred copying row 3 - unspecified error".

However, we can query using:

"SELECT TOP n FROM <Table>"

where n so far has been between 3 and 100000.

However we cannot do SELECT * FROM <Table>.

All we can guess at for now is that the problem occurs when a query is
performed to try and fetch data that is not indexed, using an index in
the where clause.

i.e. SELECT <Non-indexed column> FROM <Table> WHERE <Indexed Column> =
n

Could it be possible that we have one 'rogue' row in the table somehow
that is causing the problem?

The only thing to dispel this is that we can do the following:

SELECT * FROM <Table> WHERE ID < 1

which returns 1 row with ID = -1.

We cannot do:

SELECT * FROM <Table> WHERE ID < 200 even though we know that the only
record matching this criteria is the record with ID = -1.

Therefore the exact same data should be returned yet one query fails
and one works.

The actual problem that we see is that memory usage climbs
dramatically, then once all memory is used, the CPU usage climbs to
100% and stays there until we have to restart SQL Server.

Any suggestions on this would be hugely appreciated.

Thanks,

Paul

[Paul]

It turns out that we have a table that is over 1GB in size. We only had
the server configured to allow SQL Server to use 800MB.

Therefore, when we were querying using a query resulting in a full
table scan there was not enough memory to perform the query. Instead of
returning an error it basically just stopped (or maybe it was doing the
query but it would take years!).

Anyway, thanks to all those that posted!

Paul

[Erland Sommarskog]

Paul (pa***********@hotmail.com) writes:

It turns out that we have a table that is over 1GB in size. We only had
the server configured to allow SQL Server to use 800MB.

Therefore, when we were querying using a query resulting in a full
table scan there was not enough memory to perform the query. Instead of
returning an error it basically just stopped (or maybe it was doing the
query but it would take years!).

There is no reason why SQL Server would failed to return all rows from
a table that is larger than the available memory. However, it should
not comes as any surprise that returning so many rows will take a
very long time, and will saturate both server and client.

Or are you saying that you get a problem if you do:

SELECT * FROM tbl WHERE nonindexedcol = oddvalue

While it will take some load as the table is scanned, and both brought
into cache and out again, it should not send SQL Server into complete
nirvana.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Greg D. Moore \(Strider\)]

"Paul" <pa***********@hotmail.com> wrote in message
news:11********************@o13g2000cwo.googlegrou ps.com...

It turns out that we have a table that is over 1GB in size. We only had
the server configured to allow SQL Server to use 800MB.

This may hurt performance but should not break things.

We routinely use tables multiple gigabytes in size.

But we did hit a bug (I forget details) in a query we were doing on a SQL
2000 SP3 DB.

SP4 fixed it.

Therefore, when we were querying using a query resulting in a full
table scan there was not enough memory to perform the query. Instead of
returning an error it basically just stopped (or maybe it was doing the
query but it would take years!).

Anyway, thanks to all those that posted!

Paul