What's blocking "Select top 1 * from TableName with (nolock)" from returning a result?

Question

I'm currently running the following statement ``` select * into adhoc..san_savedi from dps_san..savedi_record ``` It's taking a painfully long time and I'd like to see how far along it is so I ran this: ``` select count(*) from adhoc..san_savedi with (nolock) ``` That didn't return anything in a timely manner so for the heck of it I did this: ``` select top 1 * from adhoc..san_savedi with (nolock) ``` Even that seems to run indefinitely. I could understand if there are millions of records that the count(*) could take a long time, but I don't understand why selecting the top 1 record wouldn't come back pretty much immediately considering I specified nolock. In the name of full disclosure, dps_san is a view that pulls from an odbc connection via linked server. I don't think that'd be affecting why I can't return the top row but just throwing it out there in case I'm wrong. So I want to know what is keeping that statement from running? EDIT: As I mentioned above, yes dps_san..savedi_reco…

Accepted Answer

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

SELECT *
INTO NewT
FROM master..spt_values

/*Remember to rollback/commit this later*/
```

And then executing (or just simply trying to view the estimated execution plan)

```
SELECT *
FROM NewT
WITH (NOLOCK)
```

in a second the reading query was blocked.

```
SELECT wait_type,
       resource_description
FROM sys.dm_os_waiting_tasks
WHERE 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
```

What's blocking "Select top 1 * from TableName with (nolock)" from returning a result?

Solution

Problem