Prev: Primary Key - string or integer
Next: SQL Server Drops connection after long timeout with replication partner
From: John Hardin on 8 Mar 2010 19:47
Folks:

We have SP queries like:

UPDATE tbl
SET @var = col = col + 1
WHERE keycol = x; -- update single row, indexed

This query can sometimes sit for several _minutes_ on a MSSQL2000SP4 box,
but we don't see similar delays on a MSSQL2000SP4+postSP4hotfixes box under
large testing load.

Is the performance of this type of query under SQL2000SP4 a known problem
that was corrected by a post-SP4 hotfix? {/me crosses fingers}

--
John Hardin KA7OHZ
Senior Applications Developer, BI Specialist
EPICOR Retail
web: http://www.epicor.com
voice: (425) 245-1800
fax: (425) 670-1810
email: <jhardin(a)epicor.com>
20818 44th Ave. W., Suite 270
Lynnwood, WA 98036 USA
Worldwide Headquarters 18200 Von Karman, Suite 1000, Irvine CA 92612 USA
------------------------------------------------------------------------
The first time I saw a bagpipe, I thought the player was torturing an
octopus. I was amazed they could scream so loudly.
------------------------------------------------------------------------

From: Tibor Karaszi on 9 Mar 2010 01:16
I haven't heard of anything special in this regard. I would treat this as a
regular update statement. I.e., look at the execution plan and also see if
there are blocking sessions.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"John Hardin" <jhardin(a)epicor.com> wrote in message
news:ecu4YIyvKHA.4112(a)TK2MSFTNGP06.phx.gbl...
> Folks:
>
> We have SP queries like:
>
> UPDATE tbl
> SET @var = col = col + 1
> WHERE keycol = x; -- update single row, indexed
>
> This query can sometimes sit for several _minutes_ on a MSSQL2000SP4 box,
> but we don't see similar delays on a MSSQL2000SP4+postSP4hotfixes box
> under large testing load.
>
> Is the performance of this type of query under SQL2000SP4 a known problem
> that was corrected by a post-SP4 hotfix? {/me crosses fingers}
>
> --
> John Hardin KA7OHZ
> Senior Applications Developer, BI Specialist
> EPICOR Retail
> web: http://www.epicor.com
> voice: (425) 245-1800
> fax: (425) 670-1810
> email: <jhardin(a)epicor.com>
> 20818 44th Ave. W., Suite 270
> Lynnwood, WA 98036 USA
> Worldwide Headquarters 18200 Von Karman, Suite 1000, Irvine CA 92612 USA
> ------------------------------------------------------------------------
> The first time I saw a bagpipe, I thought the player was torturing an
> octopus. I was amazed they could scream so loudly.
> ------------------------------------------------------------------------
>
From: John Hardin on 9 Mar 2010 13:39
"Tibor Karaszi" wrote:
>
> "John Hardin" wrote:
>>
>> We have SP queries like:
>>
>> UPDATE tbl
>> SET @var = col = col + 1
>> WHERE keycol = x; -- update single row, indexed
>>
>> This query can sometimes sit for several _minutes_ on a MSSQL2000SP4 box,
>> but we don't see similar delays on a MSSQL2000SP4+postSP4hotfixes box
>> under large testing load.
>>
>> Is the performance of this type of query under SQL2000SP4 a known problem
>> that was corrected by a post-SP4 hotfix? {/me crosses fingers}
>
> I haven't heard of anything special in this regard. I would treat this as
> a
> regular update statement. I.e., look at the execution plan and also see if
> there are blocking sessions.

That's what I'm investigating now; the execution plan is simple (what you'd
expect, clustered index update) but I'm just not finding anything in the
trace prior to that point that could be blocking it.

Of course, when the first one wedges, all subsequent ones updating the same
key are blocked until the client side application times out the first one,
then all the rest complete in one big gush. That blocking chain is obvious,
but what is causing the _first_ query to block is _not_.

At this point I'm instrumenting the database per
http://support.microsoft.com/kb/271509/

I'm assuming SQL Profiler logs the execution plan _before_ the query
actually starts, so that I am actually looking at the blocked query and not
the query immediately before the one that's blocked...

--
John Hardin KA7OHZ
Senior Applications Developer, BI Specialist
EPICOR Retail
web: http://www.epicor.com
voice: (425) 245-1800
fax: (425) 670-1810
email: <jhardin(a)epicor.com>
20818 44th Ave. W., Suite 270
Lynnwood, WA 98036 USA
Worldwide Headquarters 18200 Von Karman, Suite 1000, Irvine CA 92612 USA
------------------------------------------------------------------------
The first time I saw a bagpipe, I thought the player was torturing an
octopus. I was amazed they could scream so loudly.
------------------------------------------------------------------------

From: Erland Sommarskog on 9 Mar 2010 17:48
John Hardin (jhardin(a)epicor.com) writes:
> That's what I'm investigating now; the execution plan is simple (what
> you'd expect, clustered index update) but I'm just not finding anything
> in the trace prior to that point that could be blocking it.

There does not happen to be a trigger on the table?

Another possibility is an autogrow event.

> Of course, when the first one wedges, all subsequent ones updating the
> same key are blocked until the client side application times out the
> first one, then all the rest complete in one big gush. That blocking
> chain is obvious, but what is causing the _first_ query to block is
> _not_.

There are several tools to look at blocking chains. My own contribution
is beta_lockinfo which you find at
http://www.sommarskog.se/sqlutil/beta_lockinfo.html.

It would tell you what the lead blocker is, and what it's up to.

> I'm assuming SQL Profiler logs the execution plan _before_ the query
> actually starts, so that I am actually looking at the blocked query and
> not the query immediately before the one that's blocked...

Depends on the event class, I guess. The Statistics Profile events
includes the actual rowcount etc, so they are obviously generated
when the statement completes.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: John Hardin on 12 Mar 2010 16:20
"Erland Sommarskog" wrote:
> John Hardin (jhardin(a)epicor.com) writes:
>> That's what I'm investigating now; the execution plan is simple (what
>> you'd expect, clustered index update) but I'm just not finding anything
>> in the trace prior to that point that could be blocking it.
>
> There does not happen to be a trigger on the table?

No, there is not.

> Another possibility is an autogrow event.

Not likely; this is a single-row UPDATE and it wedges multiple threads for
several minutes, bringing database activity to near zero until the client of
the first thread times out and gives up.

Here is a new data point that just came to light: When this occurs, the SPID
that's running the UPDATE that has all the others blocked appears in
Activity Monitor as "AWAITING COMMAND". I would expect it to report
"UPDATE", or some other "I'm active and actually doing something" status.

It does have an open transaction. It appears that the client is opening a
transaction and not closing it properly...

>> Of course, when the first one wedges, all subsequent ones updating the
>> same key are blocked until the client side application times out the
>> first one, then all the rest complete in one big gush. That blocking
>> chain is obvious, but what is causing the _first_ query to block is
>> _not_.
>
> There are several tools to look at blocking chains. My own contribution
> is beta_lockinfo which you find at
> http://www.sommarskog.se/sqlutil/beta_lockinfo.html.
>
> It would tell you what the lead blocker is, and what it's up to.

There's also a tool provided by MSFT at
http://support.microsoft.com/kb/271509/ - that's what we're using to analyze
this. It shows that there is _nothing_ blocking the lead SPID.

>> I'm assuming SQL Profiler logs the execution plan _before_ the query
>> actually starts, so that I am actually looking at the blocked query and
>> not the query immediately before the one that's blocked...
>
> Depends on the event class, I guess. The Statistics Profile events
> includes the actual rowcount etc, so they are obviously generated
> when the statement completes.

I was referring to the Execution Plan event class.

--
John Hardin KA7OHZ
Senior Applications Developer, BI Specialist
EPICOR Retail
web: http://www.epicor.com
voice: (425) 245-1800
fax: (425) 670-1810
email: <jhardin(a)epicor.com>
20818 44th Ave. W., Suite 270
Lynnwood, WA 98036 USA
Worldwide Headquarters 18200 Von Karman, Suite 1000, Irvine CA 92612 USA
------------------------------------------------------------------------
The first time I saw a bagpipe, I thought the player was torturing an
octopus. I was amazed they could scream so loudly.
------------------------------------------------------------------------

 |  Next  |  Last
Pages: 1 2
Prev: Primary Key - string or integer
Next: SQL Server Drops connection after long timeout with replication partner