如何维护索引密集的应用程序。 [英] How can I maintain Index-heavy application.

问题描述

你好



我的任务是支持一个新实现的需要SQL 2000的现成软件。最近升级到该软件从根本上将底层数据库的分布式设计改为一个非常非常重量级的集中模型（它们丢弃了80％的PK'并添加了1,000'的索引）。我一直在随意关注来自服务器的性能统计数据，直到我得知计划执行几个大数据导入之后我才担心。我关心索引性能，文件增长和数据文件碎片。我有计划扩展数据和日志文件，但我想知道我是否应该对导入前后的索引做些什么。我该怎么做才能改进或准备这批次导入？


SQL Server 8.0.760

DATA~3 GB

LOG~1 GB

整理SQL_Latin1_General_CP1_CI_AS


提前致谢！

Hello


I''ve been tasked with supporting a newly implemented piece of off-the-shelf software that requires SQL 2000. A recent upgrade to the software radically changed the distributed design of the underlying database to a very, very index-heavy and centralized model (they dropped 80% of the PK''s and added 1,000''s of indexes). I''ve been casually keeping an eye on the performance stats coming from the server and wasn''t concerned until I learned of plans to perform several large data imports. I''m concerned about index performance, file growth, and data file fragmentation. I have plans to expand the data and log files, but I''m wondering if there isn''t something I should do with the indexes pre/post-import. What can I do to improve or prepare for this batch import?


SQL Server 8.0.760
DATA ~3 GB
LOG ~1 GB
Collation SQL_Latin1_General_CP1_CI_AS

Thanks in advance!

推荐答案

Jerry，


如你所知，主键和唯一索引实际上是道德等同物。


我不确定微软如何区别对待它们。


你可能会先问问自己输入的数据量是多少到您现有的数据批量。这可能会推动您的决定。


我们偶尔在Oracle世界中使用的一个技巧是在导入期间关闭补间（假设你可以这样做），然后重新构建它们。


除此之外，传入数据的卫生程度如何？它会与您现有的数据和约束相冲突，还是只是几乎没有任何困难？


祝你好运！

Jerry,

As you''re aware, a primary key and a unique index are really moral equivalents.

I''m not certain how Microsoft treats them differently, though.

You might start by asking yourself what the incoming data bulk is to your existing data bulk. This will probably drive your decisions.

One trick we''ve occasionally used in the Oracle world is to turn off the inidices during the import (assuming you can do so), and re-build them afterwards.

Outside of that, how sanitary is the incoming data? Will it collide with your existing data and constraints, or will it simply fit in with little-to-no difficulty?

Good Luck!

感谢您的回复。


是的，我知道他们''相当但我很担心，因为所有索引都是非唯一的，非集群 - 我检查了sysindexes。


根据我粗略估计的传入行数应该以1：3的比例将数据库的大小增加三倍，其中一行传入数据在数据库中产生一行数据（destTable，propertyTable，LogTable）。


我考虑过禁用批量导入的索引，但我不能确定我们将面临重建索引的停机时间。


批量数据应该直接进入数据库，没有任何与现有数据冲突的麻烦。


我可能会做很多事情如果我让我知道，但我有一个潜在的怀疑，DB会真的气球。我可以通过知道增长率为10％来计算增长操作的数量。

Thanks for the response.

yes, I know they''re equivalent but I''m a little concerned because all of the indexes are Non-Unique,Non-Clustered--I checked sysindexes.

According to my rough estimate the number of incoming rows should triple the size of the database on a 1:3 ratio where one row of incoming data produces one row of data in the database(destTable,propertyTable, LogTable).

I considered disabling indexes for the bulk import but I couldn''t be sure what kind of down-time we''d be facing rebuilding the indexes.

The bulk data should come directly into the database without any trouble colliding with the existing data.

I might be making much ado about nothing, and if I am let me know, but I have a sneaking suspicion the DB is going to really balloon. I might be able to calculate the number of grow operations by knowing the growth rate is 10 percent.

那么，备份需要多长时间？ :)）


最糟糕的情况是它在导入结束时全部向南。然后你就失去了时间和恢复停机时间。


您是否有可能备份数据库并首先针对备份运行更新，只是为了看看会发生什么？

Well, how long does a backup take? :))

Worst case is it all goes south at the end of the import. Then you have lost time and recovery downtime.

Is it possible for you to back up the DB and run an update against the backup first, just to see what happens?

这篇关于如何维护索引密集的应用程序。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！