2023年7月13日发(作者：)

SQLServer数据库维护优化的提⽰和技巧Database maintenance is very important, a critical part of our database administrators’ daily tasks. However, this aspect isfrequently underestimated which could lead to performance problems and respectively angry, unhappy customers. In thisarticle, we will take a look at the different maintenance operations we have in SQL Server and how we can optimize them andtake the maximum out of each.数据库维护⾮常重要，这是我们数据库管理员⽇常任务的关键部分。 但是，这⽅⾯经常被低估，这可能会导致性能问题以及分别使⽣⽓，不满意的客户。 在本⽂中，我们将研究SQL Server中进⾏的各种维护操作，以及如何优化它们并从中获得最⼤收益。索引重组操作 ( Index Reorganize operation )We are taking off with the indexes in our databases. I am not going to convince you how important is to keep our indexes ina good shape, so we are directly jumping into how we can maintain them.我们正在使⽤数据库中的索引。 我不会说服您保持索引良好状态的重要性，因此我们将直接跳⼊如何维护索引的过程。One of the possibilities that we have in SQL Server to keep our indexes defragmented, is the index reorganize operation is always online, uses minimal system resources, honors the fill factor that has been used during the creationof the index (common misconception is that reorganize operation does not take into account fill factor at all) and if you killit due to any reason, the work that has been done would still persist. So far so good! However there are several majordrawbacks:在SQL Server中使索引进⾏碎⽚整理的⼀种可能性是索引重组操作。 此操作始终在线，使⽤最少的系统资源，尊重在创建索引期间使⽤的填充因⼦（常见的误解是，重组操作根本不考虑填充因⼦），并且如果您由于任何原因⽽将其杀死因此，已经完成的⼯作仍将继续。 到⽬前为⽌，⼀切都很好！ 但是，有⼏个主要缺点：Index statistics are not being updated索引统计信息未更新Not efficient when you have a large fragmentation as it is only reorganizing the leaf-level pages当您的碎⽚很⼤时，效率不⾼，因为它只是重新组织叶级页⾯Cannot change the initial fill factor used during index creation⽆法更改索引创建期间使⽤的初始填充因⼦Let’s test this with one simple scenario:让我们⽤⼀个简单的场景进⾏测试：1. Create a test database to play with one simple table and insert some data with deliberately skipping some values toproduce page splits and larger fragmentation:创建⼀个测试数据库来处理⼀个简单的表，并故意跳过⼀些值以插⼊⼀些数据以产⽣页⾯拆分和更⼤的碎⽚： USE masterGOIF EXISTS (SELECT name FROM ses WHERE name = N'IndexMaintenance')DROP DATABASE [IndexMaintenance]GOCREATE DATABASE IndexMaintenance;GOUSE IndexMaintenance;GO

CREATE TABLE IndexTable (c1 INT, c2 CHAR (4000));CREATE CLUSTERED INDEX IndexTable_CL ON IndexTable (c1);GO

DECLARE @a INTSET @a = 1WHILE (@a<80)BEGINIF (@a=5 or @a=15 or @a=22 or @a=29 or @a=34 or @a=38 or @a=45) PRINT 'Nothing to insert'ELSE INSERT INTO IndexTable VALUES (@a, 'a')SET @a=@a + 1END

INSERT INTO IndexTable VALUES (5, 'a');GO

INSERT INTO IndexTable VALUES (15, 'a');GO

INSERT INTO IndexTable VALUES (22, 'a');GO

INSERT INTO IndexTable VALUES (29, 'a');GO

INSERT INTO IndexTable VALUES (34, 'a');GO

INSERT INTO IndexTable VALUES (38, 'a');GO

INSERT INTO IndexTable VALUES (45, 'a');GO

2. Check the index details – we are interested mainly in the fragmentation, page fullness and last statistics update forthis index:检查索引详细信息–我们主要对该索引的碎⽚，页⾯填充和最新统计信息感兴趣：

USE IndexMaintenanceas stats_updated FROM _db_index_physical_stats

(DB_ID(N'IndexMaintenance'), OBJECT_ID(N'able'), 1, NULL, 'DETAILED') AS aJOIN s AS b ON _id = _id AND _id = _id

SELECT _id, name, avg_fragmentation_in_percent, _count,_count,_type_desc,_page_space_used_in_percent,STATS_3. Our index has above 30 % fragmentation, so we need to do something. Try with Index Reorganize first:我们的索引有30％以上的碎⽚，因此我们需要做⼀些事情。 请先尝试使⽤“索引重组”： USE IndexMaintenanceALTER INDEX IndexTable_CL ON able REORGANIZE

4. Use the query from point 2 to see what happened with the index:使⽤第2点的查询来查看索引发⽣了什么：Fragmentation is lower than the initial 34 %, but there is still some left so our operation did not finish the jobcompletely. If you take a look at the page fullness, SQL Server tried to honor the fill factor, which is 100 % (or 0) in ourcase:碎⽚率低于最初的34％，但仍有⼀些碎⽚，因此我们的操作未能完全完成⼯作。 如果您看⼀下页⾯的填充度，SQL Server会尝试使⽤填充因⼦，在我们的例⼦中，填充因⼦为100％（或0）：Last but not least, statistics associated with this index, have not been touched.最后但并⾮最不重要的⼀点是，尚未触及与此索引相关的统计信息。5. Repeat steps 1 and 2 to recreate our setup and check index details. This time, use Index Rebuild operation:重复步骤1和2以重新创建我们的设置并检查索引详细信息。 这次，使⽤索引重建操作：

USE IndexMaintenanceALTER INDEX IndexTable_CL ON able REBUILD

6. Check the index details again after the rebuild with the script from point 2:使⽤第2点的脚本重建后，再次检查索引详细信息：Fragmentation is completely gone, fill factor was honored again and this time, index statistics have been updated!碎⽚完全消失了，填充因⼦再次得到了认可，这⼀次，索引统计信息已更新！7. Index Reorganize is a useful operation when your fragmentation is below 20-30 %, you do not need to change theoriginal fill factor of your index and you are planning on doing index statistics update at a later point in time.当您的碎⽚率低于20％⾄30％，您不需要更改索引的原始填充因⼦，并且计划在以后的某个时间进⾏索引统计信息更新时，索引重组是⼀项有⽤的操作。索引重建操作 ( Index Rebuild operation )We already covered that index rebuild is the approach you should use when the fragmentation is high. When you usethis method, there are two options:我们已经介绍了索引重建是在碎⽚过多时应该使⽤的⽅法。 使⽤此⽅法时，有两个选项：Offline离线Online线上Offline index rebuild means really offline! While an index is being created, dropped or rebuilt offline, the table cannot beaccessed and this is valid for non-clustered indexes as well:离线索引重建意味着真正的离线！ 在创建索引，离线删除或重建索引时，⽆法访问该表，这对于⾮聚集索引也有效：1. and restore the database to your SQL Server获取“ AdventureWorks2014”数据库并将数据库还原到您SQL Server2. Then run this script created by Jonathan Kehayias to enlarge several tables:然后运⾏Jonathan Kehayias创建的此脚本来放⼤⼏个表：

USE [AdventureWorks2014]GO

IF OBJECT_ID('rderHeaderEnlarged') IS NOT NULL DROP TABLE rderHeaderEnlarged;GO

CREATE TABLE rderHeaderEnlarged ( SalesOrderID int NOT NULL IDENTITY (1, 1) NOT FOR REPLICATION, RevisionNumber tinyint NOT NULL, OrderDate datetime NOT NULL, DueDate datetime NOT NULL, ShipDate datetime NULL, Status tinyint NOT NULL, OnlineOrderFlag NOT NULL, SalesOrderNumber AS (isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID],0),N'*** ERROR ***')), PurchaseOrderNumber umber NULL, AccountNumber tNumber NULL, CustomerID int NOT NULL, SalesPersonID int NULL, TerritoryID int NULL, BillToAddressID int NOT NULL, ShipToAddressID int NOT NULL, ShipMethodID int NOT NULL, CreditCardID int NULL, CreditCardApprovalCode varchar(15) NULL, CurrencyRateID int NULL, SubTotal money NOT NULL, TaxAmt money NOT NULL, Freight money NOT NULL, TotalDue AS (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))), Comment nvarchar(128) NULL, rowguid uniqueidentifier NOT NULL ROWGUIDCOL, ModifiedDate datetime NOT NULL ) ON [PRIMARY]GO SET IDENTITY_INSERT rderHeaderEnlarged ONGOINSERT INTO rderHeaderEnlarged (SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, Status, OnlineOrderFlag, PurchaseOrdeFROM rderHeader WITH (HOLDLOCK TABLOCKX)GOSET IDENTITY_INSERT rderHeaderEnlarged OFF

GOALTER TABLE rderHeaderEnlarged ADD CONSTRAINT PK_SalesOrderHeaderEnlarged_SalesOrderID PRIMARY KEY CLUSTERED

( SalesOrderID ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CREATE UNIQUE NONCLUSTERED INDEX AK_SalesOrderHeaderEnlarged_rowguid ON rderHeaderEnlarged ( rowguid ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GO

CREATE UNIQUE NONCLUSTERED INDEX AK_SalesOrderHeaderEnlarged_SalesOrderNumber ON rderHeaderEnlarged ( SalesOrderNumber ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GO

CREATE NONCLUSTERED INDEX IX_SalesOrderHeaderEnlarged_CustomerID ON rderHeaderEnlarged ( CustomerID ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GO

CREATE NONCLUSTERED INDEX IX_SalesOrderHeaderEnlarged_SalesPersonID ON rderHeaderEnlarged ( SalesPersonID ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GO

IF OBJECT_ID('rderDetailEnlarged') IS NOT NULL DROP TABLE rderDetailEnlarged;GOCREATE TABLE rderDetailEnlarged ( SalesOrderID int NOT NULL, SalesOrderDetailID int NOT NULL IDENTITY (1, 1), CarrierTrackingNumber nvarchar(25) NULL, OrderQty smallint NOT NULL, ProductID int NOT NULL, SpecialOfferID int NOT NULL, UnitPrice money NOT NULL, UnitPriceDiscount money NOT NULL, LineTotal AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))), rowguid uniqueidentifier NOT NULL ROWGUIDCOL, ModifiedDate datetime NOT NULL ) ON [PRIMARY]GO

SET IDENTITY_INSERT rderDetailEnlarged ONGOSELECT SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, Status, OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID,INSERT INTO rderDetailEnlarged (SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPriceFROM rderDetail WITH (HOLDLOCK TABLOCKX)GOSELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDGOSET IDENTITY_INSERT rderDetailEnlarged OFFGOALTER TABLE rderDetailEnlarged ADD CONSTRAINT PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID PRIMARY KEY CLUSTERED

( SalesOrderID, SalesOrderDetailID ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GOCREATE UNIQUE NONCLUSTERED INDEX AK_SalesOrderDetailEnlarged_rowguid ON rderDetailEnlarged ( rowguid ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GOCREATE NONCLUSTERED INDEX IX_SalesOrderDetailEnlarged_ProductID ON rderDetailEnlarged ( ProductID ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GO

BEGIN TRANSACTION

DECLARE @TableVar TABLE(OrigSalesOrderID int, NewSalesOrderID int)

INSERT INTO rderHeaderEnlarged

(RevisionNumber, OrderDate, DueDate, ShipDate, Status, OnlineOrderFlag,

PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID,

BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID,

CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment,

rowguid, ModifiedDate)OUTPUT t, rderID INTO @TableVarSELECT RevisionNumber, DATEADD(dd, number, OrderDate) AS OrderDate,

DATEADD(dd, number, DueDate), DATEADD(dd, number, ShipDate),

Status, OnlineOrderFlag,

PurchaseOrderNumber,

AccountNumber,

CustomerID, SalesPersonID, TerritoryID, BillToAddressID,

ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode,

CurrencyRateID, SubTotal, TaxAmt, Freight, SalesOrderID,

NEWID(), DATEADD(dd, number, ModifiedDate)FROM rderHeader AS soh WITH (HOLDLOCK TABLOCKX)CROSS JOIN ( SELECT number FROM ( SELECT TOP 10 number FROM _values WHERE type = N'P' AND number < 1000 ORDER BY NEWID() DESC

UNION SELECT TOP 10 number FROM _values WHERE type = N'P' AND number < 1000 ORDER BY NEWID() DESC

UNION SELECT TOP 10 number FROM _values WHERE type = N'P' AND number < 1000 ORDER BY NEWID() DESC ORDER BY NEWID() DESC

UNION SELECT TOP 10 number FROM _values WHERE type = N'P' AND number < 1000 ORDER BY NEWID() DESC

) AS tab) AS RandomizerORDER BY OrderDate, number

INSERT INTO rderDetailEnlarged

(SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID,

SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate)SELECT

esOrderID, CarrierTrackingNumber, OrderQty, ProductID,

SpecialOfferID, UnitPrice, UnitPriceDiscount, NEWID(), ModifiedDate

FROM rderDetail AS sodJOIN @TableVar AS tv ON rderID = lesOrderIDORDER BY rderDetailID

COMMIT

Note this might take several minutes to complete.请注意，这可能需要⼏分钟才能完成。3. After the script finishes, use the table “rderDetailEnlarged” which is already with a suitable size.脚本完成后，使⽤已经具有适当⼤⼩的表“ rderDetailEnlarged”。4.

1. Script for reading data (select * not a good command but it will work for test)读取数据的脚本（选择*不是⼀个好的命令，但可以⽤于测试）

SELECT * FROM [AdventureWorks2014].[Sales].[SalesOrderDetailEnlarged]

2. Rebuild offline one of the non-clustered indexes in this table:离线重建此表中的⾮聚集索引之⼀：

USE [AdventureWorks2014]GOALTER INDEX [AK_SalesOrderDetailEnlarged_rowguid] ON [Sales].[SalesOrderDetailEnlarged] REBUILD PARTITION = ALL

GO

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW3. Examine the locks in your SQL Server (note to change the session_ids in your environment):检查您SQL Server中的锁（注意在您的环境中更改session_ids）： SELECT session_id, blocking_session_id, wait_resource, wait_time, wait_typeFROM _exec_requests WHERE session_id = 64 or session_id = 63

SELECT request_session_id ,request_mode ,request_type ,request_status ,resource_typeFROM _tran_locksWHERE request_session_id = 63 or request_session_id=64ORDER BY request_session_id

5. If we run the query from a., it will work and return the result for around 1 minute without any blockings. Let’s startthe index rebuild from b., then switch to a. query and run it and observe the locks with the query c. This time, we arenot seeing any rows immediately coming from a. query (session 63) because the offline index rebuild on the non-clustered index is blocking our session (session 64):如果我们从a。运⾏查询，它将正常⼯作并返回结果约1分钟，⽽不会出现任何阻塞。 让我们从b。开始索引重建，然后切换到a。 查询并运⾏它，并观察查询的锁c。 这次，我们没有看到来⾃a的任何⾏。 查询（会话63），因为在⾮聚集索引上重建脱机索引会阻⽌我们的会话（会话64）：Even this was a non-clustered index, we were not able to read our data while the rebuild operation was taking place.即使这是⼀个⾮聚集索引，在进⾏重建操作时我们也⽆法读取数据。The only exception to this rule is that we are allowed to read data from the table while a non-clustered index is being createdoffline.该规则的唯⼀例外是，允许我们离线创建⾮聚集索引时从表中读取数据。We now know for sure that offline operations are really offline. I guess this is very intuitive so we did not reinvent the interesting point is when it comes to online operations. Are they really online?现在，我们确定离线操作实际上是离线的。 我想这是⾮常直观的，所以我们没有重新发明轮⼦。 有趣的⼀点是在线操作。 他们真的在线吗？Let’s see!让我们来看看！Before we go into more details, keep in mind that this is an Enterprise edition feature only.在我们进⾏详细讨论之前，请记住，这仅是企业版功能。Online operations are divided into three phases (if you are keen on learning more details, please check this ):在线操作分为三个阶段（如果您想了解更多详细信息，请查看此 ）：Preparation phase – you need to take at this point a Share lock for the row versioning and the lock is not compatiblewith an exclusive lock准备阶段–此时，您需要为⾏版本控制使⽤共享锁，并且该锁与排他锁不兼容Build phase – the phase of populating data into the new index. Typically you should not see any locks here构建阶段–将数据填充到新索引中的阶段。 通常，您在这⾥应该看不到任何锁Final phase – declare to SQL that the new index is ready. In order this to happen, we need a Schema Modification lockis not compatible with any locks.最后阶段–向SQL声明新索引已准备就绪。 为了做到这⼀点，我们需要⼀个“模式修改”锁与任何锁都不兼容。Example:例：1. and attach it.下载数据库“ AdventureWorksDW2012”并附加它。2. Get an exclusive lock on the table:在桌⼦上获得排他锁：

USE [AdventureWorksDW2012]GOBEGIN TRANSACTIONUPDATE [dbo].[FactProductInventory]SET UnitsIn=5

3. Start index rebuild operation in another tab:在另⼀个选项卡中开始索引重建操作：

USE [AdventureWorksDW2012]GOALTER INDEX [PK_FactProductInventory]ON [dbo].[FactProductInventory]REBUILD WITH (ONLINE= ON);

4. Examine the locks with the same script used above (again do not forget to put your session_ids):使⽤上⾯使⽤的相同脚本检查锁（同样不要忘记放置session_ids）： SELECT session_id, blocking_session_id, wait_resource, wait_time, wait_typeFROM _exec_requests WHERE session_id = 58 or session_id = 57

SELECT request_session_id ,request_mode ,request_type ,request_status ,resource_typeFROM _tran_locksWHERE request_session_id = 58 or request_session_id=57ORDER BY request_session_id

Session 57 (online index rebuild) is waiting for all of the exclusive locks to be released. This makes the operationunpredictable as we cannot say for sure when the maintenance operation will be able to take this lock and start itswork. This might take several seconds or minutes, but in some situations even hours!会话57（联机索引重建）正在等待释放所有排他锁。 这使操作变得不可预测，因为我们⽆法确定维护操作何时能够获取此锁定并开始其⼯作。 这可能需要⼏秒钟或⼏分钟，但在某些情况下甚⾄需要数⼩时！5. Rollback the transaction holding the lock (session 58 in my case) and immediately start it again (you have severalseconds to do this before the index rebuild operation finishes). Check the locks now:回滚持有该锁的事务（在本例中为会话58），然后⽴即再次启动它（在索引重建操作完成之前，您有⼏秒钟的时间来执⾏此操作）。现在检查锁：Oh no! We are waiting one more time, but for a Schema Modification lock to be granted so the SQL can complete therebuild operation. This is another unpredictable time interval at the end of our procedure.不好了！ 我们⼜等了⼀段时间，但是等待授予Schema Modification锁，以便SQL可以完成重建操作。 这是程序结束时另⼀个不可预测的时间间隔。Online maintenance is holding locks at the beginning and at the end of our operations, which might be problematicespecially on critical and busy production servers. Today online index rebuild might take 1 hour, but tomorrow mightneed 10 hours or even more!在线维护在我们运营的开始和结束时都处于锁定状态，这可能会出现问题，尤其是在关键和繁忙的⽣产服务器上。 今天的在线索引重建可能需要1个⼩时，但明天可能需要10个⼩时甚⾄更长的时间！低优先级等待 ( Wait at low priority )With SQL Server 2014, Microsoft has shipped a new functionality which could be very useful in relieving our online indexoperations. The feature is called: wait at low priority. It is now possible to have an influence on what is happening with ourmaintenance after a predefined period of time elapsed and we are still not able to acquire the required locks:Microsoft使⽤SQL Server 2014提供了⼀项新功能，该功能对于减轻我们的在线索引操作可能⾮常有⽤。 该功能称为：低优先级等待。经过预定的时间后，现在有可能影响我们的维护⼯作，⽽我们仍然⽆法获得所需的锁：

ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = BLOCKERS|SELF|NONE))

MAX_DURATION – how many minutes we will wait to take a lock on the table before SQL takes any actionsMAX_DURATION –在执⾏任何操作之前，我们将等待多少分钟来锁定表BLOCKERS – sessions, that are preventing our locks, will be killedBLOCKERS –阻⽌我们锁定的会话将被杀死SELF – our maintenance session will be killed⾃检–我们的维护⼯作将被取消NONE – we will keep waiting and nothing will happen (the same behavior we would see if we are not using thisfeature)⽆-我们将继续等待，并且不会发⽣任何事情（如果不使⽤此功能，我们将看到相同的⾏为）In this scenario we will use again “AdventureWorksDW2012” database – if you do not keep it from the previous demos,grab it from and attach it:在这种情况下，我们将再次使⽤“ AdventureWorksDW2012”数据库–如果您不保留以前的演⽰中的数据库，请从获取并附加它：1. Run this to take an exclusive lock:运⾏此命令以排它锁：

USE [AdventureWorksDW2012]GOBEGIN TRANSACTIONUPDATE [dbo].[FactProductInventory]SET UnitsIn=5

2. Start index rebuild operation, using wait at low priority with BLOCKERS option:使⽤具有BLOCKERS选项的低优先级等待来启动索引重建操作：

ALTER INDEX [PK_FactProductInventory] ON [dbo].[FactProductInventory]REBUILD WITH

(

ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) ))

3. Check the locks (note to put your session_ids):检查锁（注意放⼊session_ids）： SELECT session_id, blocking_session_id, wait_resource, wait_time, wait_typeFROM _exec_requests WHERE session_id = 52 or session_id = 57

SELECT request_session_id ,request_mode ,request_type ,request_status ,resource_typeFROM _tran_locksWHERE request_session_id = 52 or request_session_id=57ORDER BY request_session_id

Session 57, online index operation, is waiting again for a Shared lock, but, this time, waiting at low priority. After 1 minute inour case, SQL Server will kill the queries preventing us from taking this Shared lock on the table, if they have not succeededyet and the rebuild operation will start. The good news is that we have these stuff logged in the error log:会话57，联机索引操作，再次等待共享锁，但是这次，它以低优先级等待。 在本例中，经过1分钟后，SQL Server将终⽌查询，阻⽌我们在表上使⽤此共享锁（如果尚未成功），并且重新⽣成操作将开始。 好消息是我们在错误⽇志中记录了以下内容：MessageAn ‘ALTER INDEX REBUILD’ statement was executed on object ‘oductInventory’ by hostname ‘XXXXX’,host process ID 6124 using the WAIT_AT_LOW_PRIORITY options with MAX_DURATION = 1 and ABORT_AFTER_WAIT =BLOCKERS. Blocking user sessions will be killed after the max duration of waiting time.信息

使⽤WAIT_AT_LOW_PRIORITY选项（MAX_DURATION = 1且ABORT_AFTER_WAIT = BLOCKERS），主机名“ XXXXX”，主机进程ID 6124在对象“ oductInventory”上执⾏了“ ALTER INDEX REBUILD”语句。 在最长等待时间后，阻塞的⽤户会话将被终⽌。MessageAn ABORT_AFTER_WAIT = BLOCKERS lock request was issued on database_id = 8, object_id = 642101328. All blockinguser sessions will be killed.信息

在database_id = 8，object_id = 642101328上发出了ABORT_AFTER_WAIT = BLOCKERS锁定请求。所有阻塞的⽤户会话都将被杀死。MessageProcess ID 52 was killed by an ABORT_AFTER_WAIT = BLOCKERS DDL statement on database_id = 8, object_id =642101328.信息

进程ID 52被database_id = 8，object_id = 642101328的ABORT_AFTER_WAIT = BLOCKERS DDL语句杀死。The transaction holding the lock has been killed and we were able to complete our very important rebuild operation on time