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_record is a view. Here's what it does:
select * from DPS_SAN..root.SAVEDI_RECORD
It's nothing more than an alias and does no grouping/sorting/etc so I don't think the problem lies here, but please enlighten me if I'm wrong about that.
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
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).
Find the session_id that is performing the select into:
SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) AS t
WHERE t.[text] LIKE '%select%into%adhoc..san_savedi%';
This should let you know if another session is blocking the select into or if it has a wait type that is causing a problem.
You can repeat the process in another window for the session that is trying to do the select. I suspect Martin is right and that my earlier comment about schema lock is relevant.
