I have come across a strange behaviour in 32bit MS SQL Server 2005 running on 64bit Windows Server 2003. During a query execution, the SQL Server started using 100 % of one CPU and got stuck at

ntoskrnl.exe!IoIs32bitProcess+0x45
ntoskrnl.exe!FsRtlAddBaseMcbEntry+0xa2
...

The query was — perhaps inefficiently — using a view that selected rows from a table that was also used in the select statement. To illustrate, the query’s structure was this:

select
    *
from
    Table1 t1 inner join
    Table2 t2 on t1.table1 = t2.id inner join
    View1 v on v.table1 = t2.id
...

where the view View was selecting rows from Table2 and (some other) Table3.
Removing the view fixed the problem and SQL server executed the query without any problems. It could all be a coincidence, but if you ever get to this situation, check your views.

Tags: hang, SQL, SQL Server 2005

This entry was posted on Friday, September 26th, 2008 at 6:46 pm and is filed under Jan's Blog. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.