查询在这些表上运行的频率是多少？


-

Jack Vamvas

___________________________________

免费获得SQL提示 - www.ciquery.com/sqlserver.htm

< ha ***** @ yahoo.com>在消息中写道

news：11 ********************* @ p10g2000cwp.googlegro ups.com ...

How often are queries run on these tables?

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
<ha*****@yahoo.com> wrote in message
news:11*********************@p10g2000cwp.googlegro ups.com...

大家好，
我正在设计一个已经增长到近200GB的数据库的清除过程。
我的清除过程将删除5亿行中的1/3左右传播
七张桌子。目前，在这七个表中定义了大约35个索引。我的问题是，通过删除这些索引，进行清除以及重新创建索引可以获得性能提升。我担心留下这些索引会在我的删除语句中产生大量额外开销，因为必须维护
索引。我知道之后可能需要花费很多时间来重建索引，但我还是打算这样做。我之前想知道是否应该提前删除索引的原因是我可能无法立即执行整个清除工作并且可能需要访问表格清洗之间。如果发生这种情况，我需要准备好这些索引。

所以我应该在清除之前删除索引并稍后重新创建它们或者
我是否留下它们到位并重新索引它们？

在此先感谢

ph

Hi All,
I am designing a purge process for a db that has grown to almost 200GB.
My purge process will remove about 1/3 of the 500 million rows spread
over seven tables. Currently there are about 35 indexes defined on
those seven tables. My question is will there be a performance gain by
dropping those indexes, doing my purge, and re-creating the indexes. I
am afraid that leaving those indexes in place will create a lot of
extra overhead in my delete statements by having to maintain the
indexes. I know that it could take many hours to rebuild the indexes
afterward, but I am planning on doing that anyway. The reason that I
want to know whether I should drop the indexes ahead of time, is I may
not be able to do the entire purge at once and the tables may need to
be accessed between purges. If this occurs, I will need to have those
indexes in place.

So do I drop the indexes before the purge and re-create them later or
do I leave them in place and re-index them afterward?

Thanks In Advance

p.h.

我明白了这些表在白天经常访问。我知道我必须对影响响应时间非常敏感

对这些表的查询。我不是拥有这个

数据库的DBA，所以我无法访问有关查询的具体细节

对表。

I understand that the tables are accessed frequently during the day. I
know that I have to be very sensitive to affecting the response times
of queries against these tables. I am not the DBA that owns this
database, so I do not have access to specific details about the queries
against the tables.

在这段时间内不知道查询访问权限很难说，但

另一个想法

你可以创建一个临时表（一个持久的备份表，包含所有

日期，而不是sql临时表）并在那里移动记录。 （你想要的那些

要保留）

截断你的父表。并将记录移回原始表格

，并附上您想要的记录。

表格是否有聚集索引？

-

Jack Vamvas

___________________________________

免费获得SQL提示 - www.ciquery.com/sqlserver.htm

< ha ***** @ yahoo.com>在消息中写道

news：11 ******************** @ p10g2000cwp.googlegrou ps.com ...

Without knowing the query access during this time it''s hard to say, but
another idea
is you can create a Temporary Table (a persistent backup table with all the
date , not a sql temp table) and move the records there. (the ones you want
to keep)
Truncate your parent table. And move the records back to the original table
with the records you want.
Do the tables have clustered indexes?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
<ha*****@yahoo.com> wrote in message
news:11********************@p10g2000cwp.googlegrou ps.com...

我知道这些表在白天经常被访问。我知道我必须非常敏感地影响对这些表的查询的响应时间。我不是拥有此数据库的DBA，因此我无法访问针对表格的查询的具体细节。

I understand that the tables are accessed frequently during the day. I
know that I have to be very sensitive to affecting the response times
of queries against these tables. I am not the DBA that owns this
database, so I do not have access to specific details about the queries
against the tables.