Prev: REPOST: basic question re select
Next: Preaching does not help
From: CLM on 12 May 2010 12:01
This makes no sense to me. The SELECT below

SELECT SE.* FROM vw_UKSEAT SE WITH(NOLOCK) JOIN vw_UKTrans T WITH
(NOLOCK) ON SE.AccountDate = T.AccountDate AND SE.VaxEvid = T.VaxEvid AND
SE.VaxAcct = T.VaxAcct AND SE.TransNum = T.TransNum WHERE T.Transdate >= '
01 Jan 10' AND T.TransDate <= ' 20 Jan 10'

blocked this

UPDATE STATISTICS [dbo].[tbl_event] WITH SAMPLE 15 PERCENT
EXEC sp_recompile '[dbo].[tbl_EVENT]'

How can a SELECT with a bunch of NOLOCKs block an update stats and/or
recompile? A dirty read SELECT shouldn't block anything except a REINDEX,
right?

Any help would be much appreciated...
From: Erland Sommarskog on 12 May 2010 18:21
CLM (CLM(a)discussions.microsoft.com) writes:
> This makes no sense to me. The SELECT below
>
> SELECT SE.* FROM vw_UKSEAT SE WITH(NOLOCK) JOIN vw_UKTrans T WITH
> (NOLOCK) ON SE.AccountDate = T.AccountDate AND SE.VaxEvid = T.VaxEvid AND
> SE.VaxAcct = T.VaxAcct AND SE.TransNum = T.TransNum WHERE T.Transdate
> = '01 Jan 10' AND T.TransDate <= ' 20 Jan 10'
>
> blocked this
>
> UPDATE STATISTICS [dbo].[tbl_event] WITH SAMPLE 15 PERCENT
> EXEC sp_recompile '[dbo].[tbl_EVENT]'
>
> How can a SELECT with a bunch of NOLOCKs block an update stats and/or
> recompile?

I don't have a table of a size to make this easy to test right now, and
the hour is late. But I suspect that any of UPDATE STATISTCS and/or
sp_recompile wants a Sch-M lock, which is not compatible with the Sch-S
lock held by the NOLOCK query.


--
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

 | 
Pages: 1
Prev: REPOST: basic question re select
Next: Preaching does not help