朋友们，在一月底，我们将开设一门名为“ MS SQL Server Developer ”的新课程。 预期它的发布，我们请课程老师Kristina Kucherova编写作者的文章。 如果您的产品上有一个非常流行的表具有24/7访问权限，并且突然意识到您迫切需要添加索引并且在此过程中不破坏任何内容，那么本文将对您有所帮助。那该怎么办呢？ 传统的CREATE INDEX WITH方法（ONLINE = ON）不适合您，因为，例如，它导致系统崩溃和DBA心脏病发作，所有顶端都密切监视系统的响应时间，如果增加，它们就会来找您和您的DBA进行交谈关于您的工作报酬被高估的数字。
脚本和描述的技术在每分钟负载40万个请求的系统上使用，版本为SQL Server 2012和2016（企业版）。
有两种非常不同的创建索引的方法，这些方法的使用取决于表的大小。
案例1。一个很小但很受欢迎的桌子
一个有5万条记录的表（很小），但是很受欢迎（每分钟几千次点击）。 您需要一个新的索引，最短的停机时间和对表的锁定。
在应用程序中，对数据库的所有访问都只能通过过程进行。
如果发生错误，则应用程序将重试访问表。
您问简单地应用此索引有什么问题？ 句子WITH ONLINE = ON（是的，我们很幸运，而这是Enterprise）。
事实是，通过这种主动访问，需要花费一些时间来获得锁（即使使用Online = ON选项也需要最少的锁）。 在等待的过程中，新请求排队，队列不断累积，CPU不断增长，DBA汗流满面，紧张地对开发人员着眼睛，而在应用程序监视图上，您的响应时间开始逐渐增加，但不可避免。 您的工程师副总裁对由于响应时间的增加是否会导致某种系统停机而引起极大的兴趣，请问到年底该应用程序的可用性估计不是5个9（99,999），而是更低？ 然后，在可用性降低的情况下，公司将承担合同，义务和重罚，当然，我们也不会忘记声誉损失。
为了避免这种不幸的情况，我们做了什么？
系统仍然需要索引。
他们从除此表上的当前会话以外的所有人获取了权利。
应用索引。
是的，解决方案有一个缺点：在这几秒钟内转过桌子的每个人都会收到访问被拒绝的信息。 如果您的应用程序正常处理这种情况并向数据库重复查询，那么您应该仔细看一下该选项。 对于我们的项目，此方法效果很好。 同样，您可以安全地删除ONLINE = ON，因为我们知道在创建索引期间只有会话可以访问该表。
应用索引的代码：
REVOKE EXECUTE ON [dbo].[spUserLogin] TO [User1] REVOKE EXECUTE ON [dbo].[spUserLogin] TO [User2] REVOKE EXECUTE ON [dbo].[spUserCreate] TO [User1] REVOKE EXECUTE ON [dbo].[spUserCreate] TO [User2] CREATE NONCLUSTERED INDEX IX_Users_Email_Status ON [dbo].[Users] ([Email],[Status]); GRANT EXECUTE ON [dbo].[spUserCreate] TO [User1] GRANT EXECUTE ON [dbo].[spUserCreate] TO [User2] GRANT EXECUTE ON [dbo].[spUserLogin] TO [User1] GRANT EXECUTE ON [dbo].[spUserLogin] TO [User2]
负载下测试期间响应时间和错误百分比的计划。
如果在上述情况下您有一个小表，并且您知道在没有负载的情况下将在几秒钟内（或您可接受的时间内）创建索引，则可以应用该方法。 同时，从上图可以看出，应用程序的响应时间不会增加，尽管可以看出，无法访问表的错误率（以秒为单位）更高。
案例2。大桌子
如果您有一个大表并且需要更改其索引，那么通常最轻松的销售方法是在表旁边创建一个具有正确索引的表，然后将数据逐渐转移到新表中。
有两种方法：
- 如果您有用于修改表的特殊过程，则只需更改过程代码，以便仅将新数据插入到新表中，从这两个表中删除，也将更新应用于这两个表，并使用UNION ALL从两个表中进行选择。
- 如果您在代码中有许多不同的部分可以在其中更改表中的数据，那么有两种流行的技巧：使用触发器查看或重写代码的所有部分以将数据插入到新表中，从两个表中删除并更新两个表。 当您创建具有两个表的视图并将其重命名，将当前表重命名为TableOld并将视图重命名为Table时，具有触发器的视图是一个选项。 然后，您将自动获得对视图的所有表调用，在这里使用重命名也可能会出现问题，因为需要SchemaLock，但是重命名会很快通过。
有关重写对新表的调用的稍微详细的版本：
- 您具有Orders表，使用相同的方案创建一个新的OrdersNew表，但是具有所需的索引。 同时，如果您使用Indentity，则需要将新表中的identity的第一个值设置为等于旧表中的最大值+更改步长或可以承受的价格以偏离Orders最大值的差距。
- 创建一个OrdersView，在其中选择Orders UNION ALL OrdersNew
- 更改所有过程/调用以从视图中选择数据，将其插入OrdersNew中，删除并修改两个表。
- 例如，将数据从旧表迁移到新表，如下所示：
DECLARE @rowcount INT, @batchsize INT = 4999; SET IDENTITY_INSERT dbo.OrdersNew ON; SET @rowcount = @batchsize; WHILE @rowcount = @batchsize BEGIN BEGIN TRY DELETE TOP (@batchsize) FROM dbo.Orders OUTPUT deleted.Id ,deleted.Column1 ,deleted.Column2 ,deleted.Column3 INTO dbo.OrdersNew (Id ,Column1 ,Column2 ,Column3); SET @rowcount = @@ROWCOUNT; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage; THROW; END CATCH; END; SET IDENTITY_INSERT dbo.OrdersNew OFF;
- 使用一张表将所有过程返回到迁移之前的版本。 这可以通过更改或删除和创建过程来完成（然后不要忘记权限），然后可以将新表重命名为Orders，删除空表和视图。
在第2步中，如果加载允许，可以将主表Orders-> OrdersOld和OrdersView-> Orders以及视图本身重命名为OrdersOld UNION ALL OrdersNew，那么您不需要将表中所有可以选择的地方都更改。
当将块从一张表移动到另一张表时，数据将碎片化。
如果要更改的表被主动用于读取，但是其中的数据很少更改，则可以再次使用触发器-将所有更改的副本写入第三表-通过bcp out和bcp in（或大容量插入）将数据从表中传输到新表中，在数据传输后在其上创建索引，然后将具有更改日志的表中的更改应用于-将一个表切换到另一个表-当前表，将其重命名为TableOld，将新表从TableNew重命名为Table。
在这种情况下出错的可能性会稍高一些，因此请在这种情况下测试更改的应用和不同的切换情况。
所描述的选项不是唯一的。 我在一个负载很重的SQL Server数据库上使用了它们，并且在应用程序期间没有引起问题，这使我们的DBA团队感到满意。 当您可以在活动最少的时间段中安全地应用更改时，对于负载模式较为平稳的碱基通常不需要这种反弹。 使用上述方法的项目用户位于美国和欧洲，并在工作日和周末积极使用该应用程序，并且在工作中不断使用应用了更改的表格。 在开发人员和一名DBA审核脚本之后，通常通过Redgate Toolkit生成的自动脚本来更改更多“更安静”的对象。
对所有人都好！ 如果您使用这些方法中的任何一种或描述您的方法，请在评论中分享！ 我们还邀请您参加我们的新课程“ MS SQL Server Developer” 的公开课和开放日。