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

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...
>
> date
would[vbcol=seagreen]
> 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]
GOsqlsql

Cluster active/active/active

Dear, I am needing to grant tolerance to shortcomings to a Server
SQL. The consultation is the following one, in Windows 2003 can have up to 8
nodes
in a Cluster, in my Cluster at the moment have two instances of SQL, it is
to say, an active/active, according to the above-mentioned, I can add him a
third
node to the Cluster, but I don't have clear if I can install a third
instance
in that same cluster, a thing like activo/activo/activo, somebody has him
fact or is it theoretically and technically fctible...?, according to my
theory,
it could be feasible, since to the moment to install the third instance
Virtual, he/she should only choose available two nodes of the three....,
now
not you if this is feasible in he/she practices it.
Thank you for their attention.
Greetings.
Atte.,
Carlo Sorrel
Hi
Windows 2003 supports 8 node clusters.
SQL Server 2000 supports 4 node clusters, with up to 16 instances of SQL
Server per cluster.
Yes, adding a 3rd node, and running a 3rd instance is possible as is being
used by various customers of Microsoft.
Done think about active/passive active/active etc, think using the new
terminology of "instances".
The biggest thing you have to worry about in multiple instance clusters is
if all instances were to be failed over to 1 node, does the 1 node have
enough RAM to support them all? If you have 3 nodes, each node with 4 GB RAM
and you allocate 2GB per instance, and you failed all 3 instances over to 1
node, the 1 node would not be able to start the last instance as there is
2GB RAM too little.
If you had 2 nodes, in effect, you could run 8 instances on node 1 and 8
instances on node 2, or any combination, adding up to 16, assuming you have
enough resources.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Carlo Sorrel" <csorrel@.hotmail.com> wrote in message
news:uYaN9BhbFHA.1040@.TK2MSFTNGP10.phx.gbl...
> Dear, I am needing to grant tolerance to shortcomings to a Server
> SQL. The consultation is the following one, in Windows 2003 can have up to
> 8 nodes
> in a Cluster, in my Cluster at the moment have two instances of SQL, it is
> to say, an active/active, according to the above-mentioned, I can add him
> a third
> node to the Cluster, but I don't have clear if I can install a third
> instance
> in that same cluster, a thing like activo/activo/activo, somebody has him
> fact or is it theoretically and technically fctible...?, according to
> my theory,
> it could be feasible, since to the moment to install the third instance
> Virtual, he/she should only choose available two nodes of the three....,
> now
> not you if this is feasible in he/she practices it.
> Thank you for their attention.
> Greetings.
> Atte.,
> Carlo Sorrel
>