严重碎片索引 - SQL Server 2005
我最近继承了一个维护不善的生产数据库，其索引碎片严重（大多数索引碎片超过 80%）。我向经理请求停机以执行索引重建，但不幸的是目前不允许停机。如果在线索引重组也不是选项，我可以执行以下操作吗？
- 将新的生产副本恢复到测试实例
- 重建索引，更新统计信息
- 从测试实例覆盖产品数据库
- 应用事务日志以获取数据库。
上述方法虽然也需要停机时间，但相对较少。我想知道是否有人可以做到这一点，或者我只是很愚蠢:) 请告知
RK
I recently inherited a poorly maintained production database with heavily fragmented indexes (most of the indexes with more than 80% fragmented). I requested downtime with my manager to perform Index rebuild, but unfortunately downtime is not allowed at the moment. If online Index reorganize too is not option, can I do the following?
- Restore a fresh production copy to a test instance
- Rebuild Indexes, update statistics
- Overwrite the prod database from test instance
- Apply transaction logs to get the database.
Though the above method too requires downtime, but its relatively less. I wanted to know whether one can do this or I am just being stupid :) Please advise
RK
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果应用程序的大部分访问是通过查找进行的，那么碎片就不是问题。否则，我会尝试找到一些时间，使索引重建的影响最小，并通过计划的作业来完成。当然，该应用程序不会 24/7/365 运行且维护不善，公司也没有预料到会出现一些问题。 （他们会更换汽车的机油吗？）
就您的 4 步解决方案而言，将表复制到另一个数据库，重建索引，然后将其复制回来，除了重建现有索引之外，不会完成任何其他任务。无论如何，在将其复制回来时，索引都会重建，因此只需尝试一次安排几个表，直到完成所有操作。
祝你好运。
If most of the application's access is via seeks, then the fragmentation is not a problem. Otherwise I'd try to find some time in which the index rebuild will have minimal effect, and do it via a scheduled job. Surely the application isn't running 24/7/365 with poor maintenance, without the company expecting some problem to occur. (Do they change the oil on their cars?)
As far as your 4 step solution, copying the table to another database, rebuilding the index, and copying it back won't accomplish anything more than just rebuilding the existing index. On copying it back the index is rebuilt anyway, so just try and schedule a couple of tables at a time, until you get everything done.
Good luck.
SQL Server 2005 有 在线索引重建（即非阻塞）。
否则，它永远不会离线（即数据库或服务器离线），但在正在重建的表/索引上有独占锁。
SQL Server 2005 has online index rebuilds (that is, non-blocking).
Otherwise, it's never offline (that is, database or server off line) but does have an exclusive locks on the table/index being rebuilt.