Posted by Dan Guzman on 10/01/16 11:39

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" <paulwragg2323@hotmail.com> wrote in message
news:1139483638.010742.93430@o13g2000cwo.googlegroups.com...
> Hi all
>
> Please read my previous post here if this interests you:
>
> http://groups.google.co.uk/group/comp.databases.ms-sqlserver/browse_frm/thread/d5b12d2dadd16465?page=end&q=100%25+cpu+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
>

[Back to original message]