加速数据库更新
有一个SqlServer2000数据库我们必须在周末更新。
它的大小接近10G。
更新范围从架构更改、主键更新到更新、更正或插入的数百万条记录。
周末对于这项工作来说几乎不够。
我们为这项工作设置了专用服务器， 将数据库变成 SINGLE_USER 进行了我们能想到的任何优化：删除/重新创建索引、关系等。
您能提出任何加速该过程的建议吗？
SQL SERVER 2000 是不可否定的（不是我的决定）。 更新是通过定制程序运行的，而不是批量插入。
编辑：
架构更新由查询分析器 TSQL 脚本完成（每个版本更新一个脚本）
数据更新由 C# .net 3.5 应用程序完成。
数据来自一堆文本文件（有很多问题）并写入本地数据库。
计算机未连接到任何网络。
There is a SqlServer2000 Database we have to update during weekend.
It's size is almost 10G.
The updates range from Schema changes, primary keys updates to some Million Records updated, corrected or Inserted.
The weekend is hardly enough for the job.
We set up a dedicated server for the job,
turned the Database SINGLE_USER
made any optimizations we could think of: drop/recreate indexes, relations etc.
Can you propose anything to speedup the process?
SQL SERVER 2000 is not negatiable (not my decision). Updates are run through custom made program and not BULK INSERT.
EDIT:
Schema updates are done by Query analyzer TSQL scripts (one script per Version update)
Data updates are done by C# .net 3.5 app.
Data come from a bunch of Text files (with many problems) and written to local DB.
The computer is not connected to any Network.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
尽管删除多余的索引可能会有所帮助，但您需要确保保留这些索引，以使升级脚本能够轻松找到需要更新的行。
否则，请确保服务器中有足够的内存（尽管 SQL Server 2000 Standard 限制为 2 GB），并且如果需要，请预先增长 MDF 和 LDF 文件以应对任何增长。
如果可能，您的自定义程序应该按集合而不是逐行处理更新。
编辑：
理想情况下，尝试确定哪个操作导致性能不佳。 如果是架构更改，则可能是因为您使列变大并导致发生大量页面拆分。 但是，出于同样的原因，插入和更新时也可能会发生页面拆分 - 该行不再适合页面。
如果您的 C# 应用程序是瓶颈，您是否可以首先将更改运行到临时表中（在维护时段之前），然后对实际表执行单个更新？ 单次更新 100 万行比应用程序进行 100 万次更新调用更有效。 诚然，如果您需要在本周末执行此操作，您可能没有太多时间进行设置。
Although dropping excess indexes may help, you need to make sure that you keep those indexes that will enable your upgrade script to easily find those rows that it needs to update.
Otherwise, make sure you have plenty of memory in the server (although SQL Server 2000 Standard is limited to 2 GB), and if need be pre-grow your MDF and LDF files to cope with any growth.
If possible, your custom program should be processing updates as sets instead of row by row.
EDIT:
Ideally, try and identify which operation is causing the poor performance. If it's the schema changes, it could be because you're making a column larger and causing a lot of page splits to occur. However, page splits can also happen when inserting and updating for the same reason - the row won't fit on the page anymore.
If your C# application is the bottleneck, could you run the changes first into a staging table (before your maintenance window), and then perform a single update onto the actual tables? A single update of 1 million rows will be more efficient than an application making 1 million update calls. Admittedly, if you need to do this this weekend, you might not have a lot of time to set this up.
这个“定制程序”到底是什么样的？ 即它如何与数据对话？ 最大限度地减少网络 IO 量（从数据库服务器到应用程序）将是一个好的开始……通常这可能意味着在 TSQL 中做大量工作，但即使只是在数据库服务器上运行应用程序也可能会有所帮助。 ..
如果应用程序正在重写大块数据，它仍然可以使用批量插入来提交新的表数据。 通过命令行（bcp 等）或通过代码（.NET 中的
SqlBulkCopy）。 这通常比单独插入等更快。但这实际上取决于这个“定制程序”。
What exactly does this "custom made program" look like? i.e. how is it talking to the data? Minimising the amount of network IO (from a db server to an app) would be a good start... typically this might mean doing a lot of work in TSQL, but even just running the app on the db server might help a bit...
If the app is re-writing large chunks of data, it might still be able to use bulk insert to submit the new table data. Either via command-line (bcp etc), or through code (
SqlBulkCopyin .NET). This will typically be quicker than individual inserts etc.But it really depends on this "custom made program".