Does displaying the estimated execution plan change anything?

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
>

Does displaying the estimated execution plan change anything?

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 a
t 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...[vbcol
=seagreen]
> Generally I would think that having a query in query analyzer and displayi
ng the estimated
> execution plan would not change anything in the system but ran across an i
nteresting problem today
> that makes me think otherwise.
> On SQL Server 2000 sp3a, I have a table with 300,000 rows in it. The tabl
e 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. T
he wait time was 0,
> there was no blocking, and the cpu was increasing. I killed the job and s
tarted manually trouble
> shooting.
> The execution or just generating the estimated plan for the update stateme
nt 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 pl
an for update table set
> errorflag = 1 where pk < 20000. The returned immediately. After that eve
rything worked. I could
> display plans and run updates. So something in the system changed.
> Any ideas what?
> Danny
>[/vbcol]

Does ASP 3.0 Support Varchar(Max) Data Type

Does ASP 3.0 support Varchar(Max) Data Type?

I am having problems displaying asp 3 recordset with varchar(max) data type...

ADO.NET 2.0 supports varchar(max) field. Older versions will support it as well since SQL will send back as text to older clients.

Document Map not displaying for some users.

I have noticed that with some users the document map pane is empty,
whereas on my machine and others it displays as it should. Has anyone
seen this behavior before and how do you think I could resolve this?Ok, I figured it out.
With reporting services 2000, the document maps do not appear
correctly with users running IE7. There is a hotfix you can download
from Microsoft's site to resolve this.
On Apr 10, 10:08=A0am, Just Another Reporter <Crystal.War...@.gmail.com>
wrote:
> I have noticed that with some users the document map pane is empty,
> whereas on my machine and others it displays as it should. =A0Has anyone
> seen this behavior before and how do you think I could resolve this?