Sql-server – How to decide Rebuilding Index with DETAILED or SAMPLED on SQL Server

If the rebuild is not specified as ONLINE (which you can't do except on Enterprise Edition), the SELECT is blocked because of LCK_M_SCH_S, and if you execute sp_lock you will see it is an exclusive lock. Even NOLOCK can't penetrate that. You can simulate this by:

1. In one window, start a transaction which rebuilds offline (this is so that you can investigate beyond the rebuild instead of trying to force a really long rebuild). Make note of the spid:

   USE AdventureWorks2012;
   SELECT @@SPID;
   BEGIN TRANSACTION;
   ALTER TABLE Sales.SalesOrderHeader REBUILD WITH (ONLINE = OFF);
   

2. In a second window, execute your NOLOCK query (again, make note of the spid):

   SELECT @@SPID;
   SELECT * FROM Sales.SalesOrderHeader WITH (NOLOCK);
   

3. In a third window, check:

   SELECT session_id, blocking_session_id, last_wait_type 
     FROM sys.dm_exec_requests 
     WHERE blocking_session_id IN (two spids above)
        OR session_id IN (two spids above);
   
   EXEC sp_lock;
   

   Scan the second resultset for all of the rows involved with the spid that is the main blocker.

Don't forget to rollback or commit...

It's a bug. This Connect ticket has a workaround for it from Kendra Little:

I was able to work around this issue by temporarily adding a scheduled to the Utility Information collection (@collection_set_id=4) with the SQL command below. After doing this I was able to complete the data collection configuration via the GUI successfully. Afterwards I went back and set the Utility Information collection to manual start, thereby removing the schedule.

declare @schedule_uid uniqueidentifier
select @schedule_uid = schedule_uid from dbo.syscollector_collection_sets where collection_set_id=3

exec dbo.sp_syscollector_update_collection_set
@collection_set_id=4
, @schedule_uid=@schedule_uid