What's blocking "Select top 1 * from TableName with (nolock)" from returning a result?
SELECT
queries with NOLOCK
don't actually take no locks, they still need a SCH-S
(schema stability) lock on the table (and as it is a heap it will also take a hobt
lock).
Additionally before the SELECT
can even begin SQL Server must compile a plan for the statement, which also requires it to take a SCH-S
lock out on the table.
As your long running transaction creates the table via SELECT ... INTO
it holds an incompatible SCH-M
lock on it until the statement completes.
You can verify this by looking in sys.dm_os_waiting_tasks
whilst while during the period of blocking.
When I tried the following in one connection
BEGIN TRANSELECT *INTO NewTFROM master..spt_values/*Remember to rollback/commit this later*/
And then executing (or just simply trying to view the estimated execution plan)
SELECT *FROM NewTWITH (NOLOCK)
in a second the reading query was blocked.
SELECT wait_type, resource_descriptionFROM sys.dm_os_waiting_tasksWHERE session_id = <spid_of_waiting_task>
Shows the wait type is indeed SCH_S
and the blocking resource SCH-M
wait_type resource_description---------------- -------------------------------------------------------------------------------------------------------------------------------LCK_M_SCH_S objectlock lockPartition=0 objid=461960722 subresource=FULL dbid=1 id=lock4a8a540 mode=Sch-M associatedObjectId=461960722
It very well may be that there are no locks... If dps_san..savedi_record is a view, then it may be taking a long time to execute, because it may be accessing tables without using an index, or it may be sorting millions of records, or whatever reason. Then your query, even a simple top or count, will be only as fast as that view can be executed.
A few issues to consider here. Is dps_san..savedi_record a view? If so, it could just be taking a really long time to get your data. The other thing I can think of is that you're trying to create a temp table by using the select into
syntax, which is a bad idea. select * into ...
syntax will lock the tempdb for duration of the select.
If you are creating the table using that syntax, then there is a workaround. First, create the table by throwing where 1=0
at the end of your initial statement:
select * into ... from ... where 1=0
This will create the table first (which is quick) which allows you to insert into
because the table exists now (without penalty of locking tempdb for duration of query).