Does selecting data from heap (table without clustered index) with nolock block writers?

[emran]

I tried replicating the scenario given here: https://www.sqlskills.com/blogs/paul/the-curious-case-of-the-bulk_operation-lock-during-a-heap-nolock-scan/

Created 2 tables - table1 with clustered index, table2 without any index.

When I write a select query with (lock) on table1, there is Sch-S lock applied. Similarly, when I write the same query on table2, there also it is a Sch-S lock.

Neither case applied the S lock like shown in the above link.

To check locks I use the following query:

SELECT * FROM sys.dm_tran_locks
  WHERE resource_database_id = DB_ID()
  AND resource_associated_entity_id = OBJECT_ID(N'dbo.table1');

Does selecting data from heap (table without clustered index) with nolock block writers?

[inna]

It worked for me. It helps if you flush the page cache before the table scan to slow it down. eg

dbcc dropcleanbuffers
go
select avg(somecol) ap
from someheap with (nolock)

And check the query plan to make sure you're getting an unordered table scan.

Another trick for observing quick things is to introduce a time-wasting scalar-valued function like:

create or alter function dbo.delay(@ms int)
returns int as
begin
   declare @start datetime2 = sysdatetime()
   while datediff(ms,@start,sysdatetime()) < @ms
   begin
     declare @h bigint = checksum(0x29374190273401923470912384709123)
   end
   return @ms
end

So you can make a scan as slow as you want, so long as you have a streaming plan.

In AdventureWorksDW, I created a large heap like this

select top 25000000 s.* 
into someheap 
from FactInternetSales s,  FactInternetSales s2

then ran

dbcc dropcleanbuffers
go
select avg(UnitPrice) ap
from someheap with (nolock)

which took long enough to switch over and run

SELECT
    [resource_type],
    [resource_subtype],
    [resource_associated_entity_id],
    [request_mode]
FROM sys.dm_tran_locks
WHERE
    [resource_type] != N'DATABASE';

and see the BULK_OPERATION lock