Hi, the following query which has been used many times before, just never
seems to complete this time. Other than a table name, nothing has changed.
The table being updated has 16 million rows and the 2 views used in the
select have 216 and 360 million rows.
The query has been running for 18hrs now. It has necessary indexes. my
search online, found some results where it says that sometimes with SQL 2005,
SP2, some people have had this problem, but did not find anything that would
I checked for indexes,
ran the DBCC free proccache
killed and restarted the query with a different session (out of desperation)
nothing made difference.
When I checked activity monitor, someitmes, it would display as sleep_task.
The CPU and Disk I/O numbers keep changing, but the timestamp for the last
batch is the same as when I started the query. Also tried the SQL profiler, but did not find anything useful, as my query did not even show up in that trace.
There are actually multiple queries like this, and this is only one of them.
The server is a pretty robust one and also there were times, when there was
nothing else running on the SQL server and still the query won't complete.
with cte as
(select distinct p.PID id , p.HouseHoldID, Q.[Living Unit ID] , q.[source
code] sc, q.[sequence number] sn
from vw_All_HH_Persons p
join vw_All_Household_Details h on p.hhde
View Complete Post