Generally I would think that having a query in query analyzer and displaying
the estimated execution plan would not change anything in the system but ran
across an interesting problem today that makes me think otherwise.
On SQL Server 2000 sp3a, I have a table with 300,000 rows in it. The table
is basically a bulk insert from the SQL Error logs with an Identity column
for the primary key and int colums added for flaging error records and such.
A SQL job calls a store procedure that truncates the table and reloads it.
After that it does an update table set errorflag = 1 where logtext like
'%error%'
I found the job in an executing state for several days and investigated.
The wait time was 0, there was no blocking, and the cpu was increasing. I
killed the job and started manually trouble shooting.
The execution or just generating the estimated plan for the update statement
would never return. I cut the update statement down to just update table
set errorflag = 1 and got the same hung state. I could do selects on the
table. Next I displayed an estimated plan for update table set errorflag = 1 where pk < 20000. The returned immediately. After that everything
worked. I could display plans and run updates. So something in the system
changed.
Any ideas what?
DannyWild guess: Perhaps statistics were updated and/or created when you looked at the plan, so the
following query had a better plan?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Danny" <nomailbox@.nowhere.com> wrote in message news:Lg2ig.18341$9c7.5231@.trnddc06...
> Generally I would think that having a query in query analyzer and displaying the estimated
> execution plan would not change anything in the system but ran across an interesting problem today
> that makes me think otherwise.
> On SQL Server 2000 sp3a, I have a table with 300,000 rows in it. The table is basically a bulk
> insert from the SQL Error logs with an Identity column for the primary key and int colums added
> for flaging error records and such.
> A SQL job calls a store procedure that truncates the table and reloads it. After that it does an
> update table set errorflag = 1 where logtext like '%error%'
> I found the job in an executing state for several days and investigated. The wait time was 0,
> there was no blocking, and the cpu was increasing. I killed the job and started manually trouble
> shooting.
> The execution or just generating the estimated plan for the update statement would never return.
> I cut the update statement down to just update table set errorflag = 1 and got the same hung
> state. I could do selects on the table. Next I displayed an estimated plan for update table set
> errorflag = 1 where pk < 20000. The returned immediately. After that everything worked. I could
> display plans and run updates. So something in the system changed.
> Any ideas what?
> Danny
>
