檢查索引重建操作是否線上的 T-SQL 腳本已關閉

我想看看是否可以使用一些 T-SQL 確定我的索引是線上重建還是離線重建。我目前正在嘗試對重建過程中使用的額外磁碟空間進行故障排除，我認為這可能是一個不錯的起點。

參考Capture Index Maintenance Operations中的資訊，您也許可以使用擴展事件來擷取線上索引操作。

從那個文章：

創建擴展事件

請注意，此範例使用的是環形緩衝區，因此您可能需要選擇不同的目標。該範例也在尋找database_id = 5，因此進行相應調整

-- Create the Event Session
IF EXISTS (
       SELECT *
       FROM sys.server_event_sessions
       WHERE NAME = 'OnlineIXOps'
       )
   DROP EVENT SESSION OnlineIXOps ON SERVER;
GO

CREATE EVENT SESSION OnlineIXOps ON SERVER
   --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
   --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
   /* The online index operations */
   --ADD EVENT sqlserver.progress_report_online_index_operation
   --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
   --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
   ADD EVENT sqlserver.progress_report_online_index_operation (
   ACTION(sqlserver.database_name, sqlserver.client_hostname, sqlserver.client_app_name, sqlserver.sql_text, sqlserver.session_id)
   --Change this to match the database in question, 
   WHERE sqlserver.database_id = 5
   ) ADD TARGET package0.ring_buffer
   WITH (MAX_DISPATCH_LATENCY = 5 SECONDS)
GO

-- Start the Event Session
ALTER EVENT SESSION OnlineIXOps ON SERVER STATE = START;
GO

現在，執行某種線上索引操作。從文章：

ALTER INDEX IX_PolicyUserRole ON PolicyUserRole REBUILD PARTITION = ALL WITH (online = ON)

線上重建該索引後，您可以使用 tsql 使用類似於以下的查詢來查詢 Event 會話：

SELECT event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name
   ,event_data.value('(event/@timestamp)[1]', 'varchar(50)') AS TIMESTAMP
   ,event_data.value('(event/action[@name="database_name"]/value)[1]', 'varchar(max)') AS DBName
   ,event_data.value('(event/data[@name="object_name"]/value)[1]', 'varchar(max)') AS ObjName
   ,event_data.value('(event/data[@name="index_name"]/value)[1]', 'varchar(max)') AS index_name
   ,event_data.value('(event/data[@name="partition_number"]/value)[1]', 'varchar(max)') AS PartitionNumber
   ,event_data.value('(event/action[@name="session_id"]/value)[1]', 'varchar(max)') AS SessionID
   ,event_data.value('(event/data[@name="build_stage"]/value)[1]', 'varchar(max)') AS Build_Stage
   ,event_data.value('(event/data[@name="build_stage"]/text)[1]', 'varchar(max)') AS BuildStage_Description
   ,event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'varchar(max)') AS Client_hostName
   ,event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'varchar(max)') AS Client_AppName
   ,event_data.value('(event/action[@name="sql_text"]/value)[1]', 'varchar(max)') AS sql_text
   ,event_data.value('(event/data[@name="duration"]/value)[1]', 'Decimal(18,2)') / 1000 AS Duration_ms
   ,event_data.value('(event/data[@name="rows_inserted"]/value)[1]', 'varchar(max)') AS rows_inserted
FROM (
   SELECT evnt.query('.') AS event_data
   FROM (
       SELECT CAST(target_data AS XML) AS TargetData
       FROM sys.dm_xe_sessions AS s
       INNER JOIN sys.dm_xe_session_targets AS t ON s.address = t.event_session_address
       WHERE s.NAME = 'OnlineIXOps'
           AND t.target_name = 'ring_buffer'
       ) AS tab
   CROSS APPLY TargetData.nodes('RingBufferTarget/event') AS split(evnt)
   ) AS evts(event_data)
ORDER BY TIMESTAMP
   ,Build_Stage

當我測試非線上重建時，我們沒有擷取到擴展事件。

你的重建程序是什麼？您最好的選擇就是檢查程式碼並查看它在做什麼，您是否有理由不能這樣做？

引用自：https://dba.stackexchange.com/questions/203224