Cluster indexing time increasing each week

SQL 7.0
There's a table with 100M records (and growing) that's
needing to be reindexed every week. If I don't recreate
the index, the fragmentation causes performance of the
queries to get slower and slower. I have a job that runs
the following script, but it's taking a little longer
each week and I'm wondering if there's a way to speed it
up...
Any help appreciated.
Thx,
Don
job script:
CREATE UNIQUE CLUSTERED
INDEX [SD] ON [dbo].[DATA] ([Name], [Date])
WITH
FILLFACTOR = 90
,DROP_EXISTING
ON [PRIMARY]
table structure:
CREATE TABLE [dbo].[DATA] (
[Name] [varchar] (32) NOT NULL ,
[Date] [smalldatetime] NOT NULL ,
[TD] [smalldatetime] NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[SP] [decimal](18, 6) NULL ,
[SD] [smalldatetime] NULL ,
[DV] [int] NOT NULL ,
[DI] [int] NOT NULL ,
[Fg] [int] NULL
) ON [PRIMARY]
GODon wrote:
> SQL 7.0
> There's a table with 100M records (and growing) that's
> needing to be reindexed every week. If I don't recreate
> the index, the fragmentation causes performance of the
> queries to get slower and slower. I have a job that runs
> the following script, but it's taking a little longer
> each week and I'm wondering if there's a way to speed it
> up...
> Any help appreciated.
> Thx,
> Don
> job script:
> CREATE UNIQUE CLUSTERED
> INDEX [SD] ON [dbo].[DATA] ([Name], [Date])
> WITH
> FILLFACTOR = 90
> ,DROP_EXISTING
> ON [PRIMARY]
> table structure:
> CREATE TABLE [dbo].[DATA] (
> [Name] [varchar] (32) NOT NULL ,
> [Date] [smalldatetime] NOT NULL ,
> [TD] [smalldatetime] NULL ,
> [DO] [decimal](18, 6) NOT NULL ,
> [DH] [decimal](18, 6) NOT NULL ,
> [DL] [decimal](18, 6) NOT NULL ,
> [DC] [decimal](18, 6) NOT NULL ,
> [SP] [decimal](18, 6) NULL ,
> [SD] [smalldatetime] NULL ,
> [DV] [int] NOT NULL ,
> [DI] [int] NOT NULL ,
> [Fg] [int] NULL
> ) ON [PRIMARY]
> GO
Is there a reason you chose those columns for the clustered index? What
is the PK on the table? Are there other indexes? One reason for
fragmentation has to do with clustered index keys that change. Or it
could be caused by newly inserted keys that require new pages. You could
try using DBCC INDEXDEFRAG (oh, that's SQL 2000 and you are on 7). It is
an online operation and may keep the table from getting too fragmented.
I don't know if you have other indexes, but if you do, rebuilding a
clustered index can cause the non-clustered indexes to rebuild as well,
although using the DROP_EXISTING should deal with this somewhat. Another
thing to remember is that the clustered keys are a part of all
non-clustered indexes. A large clustered index key like yours can cause
the non-clustered index to bulk up.
I assume you are using a 90 Fill Factor because you have a lot of
inserts that occur over the course of a week. Leaving 10% of each page
free is a lot of available space and makes the table that much larger.
It does give you some room for growth before page splits start to occur,
but you might consider lowering the free space if your inserting reach
high levels. Are you really adding 10% to the table each week? If not,
consider leaving less space.
What types of queries are you running on this table? Do the clustered
keys get updated? Do you have other indexes? Have you considering using
the clustered index for something else? It would help to understand your
table and its use a little more.
--
David Gugick
Imceda Software
www.imceda.com|||I don't know what values you are putting into name and date columns. If date
values are subsequent then making date first key in clustered index would
decrease fragmentation.
--
Thank you,
Alex
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:09bf01c4f1c0$c086a180$a501280a@.phx.gbl...
> SQL 7.0
> There's a table with 100M records (and growing) that's
> needing to be reindexed every week. If I don't recreate
> the index, the fragmentation causes performance of the
> queries to get slower and slower. I have a job that runs
> the following script, but it's taking a little longer
> each week and I'm wondering if there's a way to speed it
> up...
> Any help appreciated.
> Thx,
> Don
> job script:
> CREATE UNIQUE CLUSTERED
> INDEX [SD] ON [dbo].[DATA] ([Name], [Date])
> WITH
> FILLFACTOR = 90
> ,DROP_EXISTING
> ON [PRIMARY]
> table structure:
> CREATE TABLE [dbo].[DATA] (
> [Name] [varchar] (32) NOT NULL ,
> [Date] [smalldatetime] NOT NULL ,
> [TD] [smalldatetime] NULL ,
> [DO] [decimal](18, 6) NOT NULL ,
> [DH] [decimal](18, 6) NOT NULL ,
> [DL] [decimal](18, 6) NOT NULL ,
> [DC] [decimal](18, 6) NOT NULL ,
> [SP] [decimal](18, 6) NULL ,
> [SD] [smalldatetime] NULL ,
> [DV] [int] NOT NULL ,
> [DI] [int] NOT NULL ,
> [Fg] [int] NULL
> ) ON [PRIMARY]
> GO
>|||"Alex" <alex_removethis_@.healthmetrx.com> wrote in message
news:10tjnve4gf28cd7@.corp.supernews.com...
> I don't know what values you are putting into name and date columns. If
date
> values are subsequent then making date first key in clustered index would
> decrease fragmentation.
subsequent = sequential, of course|||Thank you Mark, that's what I meant, sorry for confusion, English is not my
first language.
--
Thank you,
Alex
"Mark Wilden" <mark@.mwilden.com> wrote in message
news:NtWdnTDXGpYUeUTcRVn-qw@.sti.net...
> "Alex" <alex_removethis_@.healthmetrx.com> wrote in message
> news:10tjnve4gf28cd7@.corp.supernews.com...
> > I don't know what values you are putting into name and date columns. If
> date
> > values are subsequent then making date first key in clustered index
would
> > decrease fragmentation.
> subsequent = sequential, of course
>|||On Mon, 3 Jan 2005 10:19:23 -0800, "Don"
<anonymous@.discussions.microsoft.com> wrote:
>SQL 7.0
>There's a table with 100M records (and growing) that's
>needing to be reindexed every week. If I don't recreate
>the index, the fragmentation causes performance of the
>queries to get slower and slower. I have a job that runs
>the following script, but it's taking a little longer
>each week and I'm wondering if there's a way to speed it
>up...
Are these SELECT queries or INSERT, UPDATE, or DELETE, and whichever,
do they reference the clustered index?
If the file grows each week then yes, it is going to take a little
longer every week.
Maybe you should look into partitioned tables?
J.|||You should really consider moving non-current data to a DSS or Warehouse
structure. I suspect that it is not fragmentation that is your problem as
much as it is the statistics. As the base number of records increases, the
more new records require to be inserted or modified before the AUTOSTATS
kicks in. You might just try updating the statistics more frequently and
rely less on the index rebuild operations.
Sincerely,
Anthony Thomas
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:09bf01c4f1c0$c086a180$a501280a@.phx.gbl...
SQL 7.0
There's a table with 100M records (and growing) that's
needing to be reindexed every week. If I don't recreate
the index, the fragmentation causes performance of the
queries to get slower and slower. I have a job that runs
the following script, but it's taking a little longer
each week and I'm wondering if there's a way to speed it
up...
Any help appreciated.
Thx,
Don
job script:
CREATE UNIQUE CLUSTERED
INDEX [SD] ON [dbo].[DATA] ([Name], [Date])
WITH
FILLFACTOR = 90
,DROP_EXISTING
ON [PRIMARY]
table structure:
CREATE TABLE [dbo].[DATA] (
[Name] [varchar] (32) NOT NULL ,
[Date] [smalldatetime] NOT NULL ,
[TD] [smalldatetime] NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[SP] [decimal](18, 6) NULL ,
[SD] [smalldatetime] NULL ,
[DV] [int] NOT NULL ,
[DI] [int] NOT NULL ,
[Fg] [int] NULL
) ON [PRIMARY]
GO