100% CPU Usage (Part 2)

Hi all

Please read my previous post here if this interests you:

http://groups.google.co.uk/group/co...pu+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