Index Rebuilding

Hi,
I am going to Rebuild the index on tables of production database.
I will use 'dbcc dbreindex' command to do this index rebuilding.
Now, I want to know what are the Prerequisites should i take to do
this job.
And also what are the Precautions shuld i take.
Please guide me in this regard.
Thanks & Regards,
Sajid.DBREINDEX rebuilds indexes completely and is resource intensive. This may
cause blocking and performance issues so you should run it during minimal
activity. Also, DBREINDEX is fully-logged regardless of your database
recovery model so make sure you have sufficient log space available.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1149766763.732814.141490@.c74g2000cwc.googlegroups.com...
> Hi,
> I am going to Rebuild the index on tables of production database.
> I will use 'dbcc dbreindex' command to do this index rebuilding.
> Now, I want to know what are the Prerequisites should i take to do
> this job.
> And also what are the Precautions shuld i take.
> Please guide me in this regard.
>
> Thanks & Regards,
> Sajid.
>|||Dan Guzman wrote:
> DBREINDEX rebuilds indexes completely and is resource intensive. This may
> cause blocking and performance issues so you should run it during minimal
> activity. Also, DBREINDEX is fully-logged regardless of your database
> recovery model so make sure you have sufficient log space available.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csajid@.gmail.com> wrote in message
> news:1149766763.732814.141490@.c74g2000cwc.googlegroups.com...
> > Hi,
> >
> > I am going to Rebuild the index on tables of production database.
> >
> > I will use 'dbcc dbreindex' command to do this index rebuilding.
> >
> > Now, I want to know what are the Prerequisites should i take to do
> > this job.
> >
> > And also what are the Precautions shuld i take.
> >
> > Please guide me in this regard.
> >
> >
> > Thanks & Regards,
> > Sajid.
> >
It requires downtime, since it locks tables and if you are rebuilding
cluster index then you need to about 1.2 times table space for
rebuilding index or nonclustered index then about 1.2 time space than
size of non clustered index.
Log backup size also increases , so if you do not need log backup
during this time , change recovery model to simple. After completing
activity change recovery model to full again and take a full backup.
In SQL Server 2005 you can rebuild index dynamically , but please check
BOL for it.
Regards
Amish Shah.|||Hi,
Thanks Guys for your quick reply.
Thanks & Regards,
Sajid N. Chhapekar.
amish wrote:
> Dan Guzman wrote:
> > DBREINDEX rebuilds indexes completely and is resource intensive. This may
> > cause blocking and performance issues so you should run it during minimal
> > activity. Also, DBREINDEX is fully-logged regardless of your database
> > recovery model so make sure you have sufficient log space available.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > <csajid@.gmail.com> wrote in message
> > news:1149766763.732814.141490@.c74g2000cwc.googlegroups.com...
> > > Hi,
> > >
> > > I am going to Rebuild the index on tables of production database.
> > >
> > > I will use 'dbcc dbreindex' command to do this index rebuilding.
> > >
> > > Now, I want to know what are the Prerequisites should i take to do
> > > this job.
> > >
> > > And also what are the Precautions shuld i take.
> > >
> > > Please guide me in this regard.
> > >
> > >
> > > Thanks & Regards,
> > > Sajid.
> > >
> It requires downtime, since it locks tables and if you are rebuilding
> cluster index then you need to about 1.2 times table space for
> rebuilding index or nonclustered index then about 1.2 time space than
> size of non clustered index.
> Log backup size also increases , so if you do not need log backup
> during this time , change recovery model to simple. After completing
> activity change recovery model to full again and take a full backup.
> In SQL Server 2005 you can rebuild index dynamically , but please check
> BOL for it.
> Regards
> Amish Shah.

Index Rebuilding

Hi,
I am going to Rebuild the index on tables of production database.
I will use 'dbcc dbreindex' command to do this index rebuilding.
Now, I want to know what are the Prerequisites should i take to do
this job.
And also what are the Precautions shuld i take.
Please guide me in this regard.
Thanks & Regards,
Sajid.DBREINDEX rebuilds indexes completely and is resource intensive. This may
cause blocking and performance issues so you should run it during minimal
activity. Also, DBREINDEX is fully-logged regardless of your database
recovery model so make sure you have sufficient log space available.
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1149766763.732814.141490@.c74g2000cwc.googlegroups.com...
> Hi,
> I am going to Rebuild the index on tables of production database.
> I will use 'dbcc dbreindex' command to do this index rebuilding.
> Now, I want to know what are the Prerequisites should i take to do
> this job.
> And also what are the Precautions shuld i take.
> Please guide me in this regard.
>
> Thanks & Regards,
> Sajid.
>|||Dan Guzman wrote:
[vbcol=seagreen]
> DBREINDEX rebuilds indexes completely and is resource intensive. This may
> cause blocking and performance issues so you should run it during minimal
> activity. Also, DBREINDEX is fully-logged regardless of your database
> recovery model so make sure you have sufficient log space available.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csajid@.gmail.com> wrote in message
> news:1149766763.732814.141490@.c74g2000cwc.googlegroups.com...
It requires downtime, since it locks tables and if you are rebuilding
cluster index then you need to about 1.2 times table space for
rebuilding index or nonclustered index then about 1.2 time space than
size of non clustered index.
Log backup size also increases , so if you do not need log backup
during this time , change recovery model to simple. After completing
activity change recovery model to full again and take a full backup.
In SQL Server 2005 you can rebuild index dynamically , but please check
BOL for it.
Regards
Amish Shah.|||Hi,
Thanks Guys for your quick reply.
Thanks & Regards,
Sajid N. Chhapekar.
amish wrote:
> Dan Guzman wrote:
>
> It requires downtime, since it locks tables and if you are rebuilding
> cluster index then you need to about 1.2 times table space for
> rebuilding index or nonclustered index then about 1.2 time space than
> size of non clustered index.
> Log backup size also increases , so if you do not need log backup
> during this time , change recovery model to simple. After completing
> activity change recovery model to full again and take a full backup.
> In SQL Server 2005 you can rebuild index dynamically , but please check
> BOL for it.
> Regards
> Amish Shah.

Index rebuild

I run dbcc dbreindex command and send the output to the text file. Is there any other way to check that indexes were in fact rebuild?You could do a before and after DBCC SHOWCONTIG. But, if DBREINDEX retunrs no errors, it ran successfully.

Index problems

SQL2K EE SP3 , WIN 2K EE SP2
We have a 27 Gb database that continually has index / performance problems.
We do a DBCC DBREINDEX every night, and sp_updatestats every hour during the
day. The database has a lot of read, write and delete activity, usually inv
olving 1000's of records at a time and more than 100,000 records a few time
s a day.
Because of slow query response we started doing the DBREINDEX every night an
d it improved performance.
Than we started getting the following errors:
Cannot retrieve row from page (64:2562414) by RID because the slotid (742594
184) is not valid.
This was solved after the nightly DBREINDEX, but since we cannot DBREINDEX d
uring the day while users are in the system we started running sp_updatestat
s every hour and this fixes it as well.
Now we have had a few times were a query returns no records even though the
data exists. The last time we had the problem the following query was being
run:
SELECT *
FROM dbo.MV_Detail
WHERE Tailor_ID = 359884
AND Circ_ID = 81670
If we take out the AND Circ_ID = 81670 statement and do an order by on the t
able we see that we have data that match the criteria, or if we add a 3rd cr
iteria with a table join then we see the data.
SELECT *
FROM dbo.mv_Detail a,
(select distinct geography from tempdb.dbo.mv_Geo40499999) b
where a.Tailor_ID = 359884 AND a.Circ_ID = 81670 and b.geography = a.geograp
hy
I ran sp_updatestats on the database and we still did not get any records fr
om the first query. After we ran DBREINDEX the first query started to return
records again.
I have included the table create script for the main table were we have prob
lems with. This table is about 1/3 of the database size and contains almost
60 million records and uses about 5Gb in data space and 5.5 Gb in index spac
e.
Any ideas on how to improve this situation would be greatly appreciated. We
changed the fill factor on the indexes from 80 to 90 to conserve space, but
have added disk space since then. Could reducing the fill factor help with t
hese types of problems?
CREATE TABLE [dbo].[MV_Detail] (
[Circ_ID] [int] NOT NULL ,
[Tailor_ID] [int] NOT NULL ,
[geography] [varchar] (10) COLLATE SQL_Latin1_General_CP437_CI_AS NOT NULL ,
[circtype] [int] NOT NULL ,
[UseAny] [bit] NOT NULL ,
[Monday_Use] [bit] NOT NULL ,
[Tuesday_Use] [bit] NOT NULL ,
[Wednesday_Use] [bit] NOT NULL ,
[Thursday_Use] [bit] NOT NULL ,
[Friday_Use] [bit] NOT NULL ,
[Saturday_Use] [bit] NOT NULL ,
[Sunday_Use] [bit] NOT NULL ,
[monday] [int] NOT NULL ,
[tuesday] [int] NOT NULL ,
[wednesday] [int] NOT NULL ,
[thursday] [int] NOT NULL ,
[friday] [int] NOT NULL ,
[saturday] [int] NOT NULL ,
[sunday] [int] NOT NULL ,
[Date_Changed] [datetime] NULL ,
[Changed_By] [char] (8) COLLATE SQL_Latin1_General_CP437_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MV_Detail] ADD
CONSTRAINT [DF_MV_Detail_UseAny] DEFAULT (0) FOR [UseAny],
CONSTRAINT [DF_MV_Detail_Monday_Use] DEFAULT (0) FOR [Monday_Use],
CONSTRAINT [DF_MV_Detail_Tuesday_Use] DEFAULT (0) FOR [Tuesday_Use],
CONSTRAINT [DF_MV_Detail_Wednesday_Use] DEFAULT (0) FOR [Wednesday_Use],
CONSTRAINT [DF_MV_Detail_Thursday_Use] DEFAULT (0) FOR [Thursday_Use],
CONSTRAINT [DF_MV_Detail_Friday_Use] DEFAULT (0) FOR [Friday_Use],
CONSTRAINT [DF_MV_Detail_Saturday_Use] DEFAULT (0) FOR [Saturday_Use],
CONSTRAINT [DF_MV_Detail_Sunday_Use] DEFAULT (0) FOR [Sunday_Use]
GO
CREATE INDEX [IX_MV_Detail] ON [dbo].[MV_Detail]([Tailor_ID], [geography]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE UNIQUE INDEX [CircTailorGeoType] ON [dbo].[MV_Detail]([Circ_ID], [Tailor_ID], [geography], [circtype]) WITH FILLFACTOR =
90 ON [PRIMARY]
GO
CREATE INDEX [IX_Circ_Trigger] ON [dbo].[MV_Detail]([Circ_ID], [Tailor_ID], [circtype]) WITH FILLF
ACTOR = 90 ON [PRIMARY]
GOMike,
It sounds like data corruption. See if this article helps:
http://support.microsoft.com/defaul...kb;en-us;826433
SK
"Mike" <Mike@.Comcast.net> wrote in message news:u$1HcAD5DHA.564@.TK2MSFTNGP10
.phx.gbl...
SQL2K EE SP3 , WIN 2K EE SP2
We have a 27 Gb database that continually has index / performance problems.
We do a DBCC DBREINDEX every night, and sp_updatestats every hour during the
day. The database has a lot of read, write and delete activity, usually inv
olving 1000's of records at a time and more than 100,000 records a few time
s a day.
Because of slow query response we started doing the DBREINDEX every night an
d it improved performance.
Than we started getting the following errors:
Cannot retrieve row from page (64:2562414) by RID because the slotid (742594
184) is not valid.
This was solved after the nightly DBREINDEX, but since we cannot DBREINDEX d
uring the day while users are in the system we started running sp_updatestat
s every hour and this fixes it as well.
Now we have had a few times were a query returns no records even though the
data exists. The last time we had the problem the following query was being
run:
SELECT *
FROM dbo.MV_Detail
WHERE Tailor_ID = 359884
AND Circ_ID = 81670
If we take out the AND Circ_ID = 81670 statement and do an order by on the t
able we see that we have data that match the criteria, or if we add a 3rd cr
iteria with a table join then we see the data.
SELECT *
FROM dbo.mv_Detail a,
(select distinct geography from tempdb.dbo.mv_Geo40499999) b
where a.Tailor_ID = 359884 AND a.Circ_ID = 81670 and b.geography = a.geograp
hy
I ran sp_updatestats on the database and we still did not get any records fr
om the first query. After we ran DBREINDEX the first query started to return
records again.
I have included the table create script for the main table were we have prob
lems with. This table is about 1/3 of the database size and contains almost
60 million records and uses about 5Gb in data space and 5.5 Gb in index spac
e.
Any ideas on how to improve this situation would be greatly appreciated. We
changed the fill factor on the indexes from 80 to 90 to conserve space, but
have added disk space since then. Could reducing the fill factor help with t
hese types of problems?
CREATE TABLE [dbo].[MV_Detail] (
[Circ_ID] [int] NOT NULL ,
[Tailor_ID] [int] NOT NULL ,
[geography] [varchar] (10) COLLATE SQL_Latin1_General_CP437_CI_AS NOT NULL ,
[circtype] [int] NOT NULL ,
[UseAny] [bit] NOT NULL ,
[Monday_Use] [bit] NOT NULL ,
[Tuesday_Use] [bit] NOT NULL ,
[Wednesday_Use] [bit] NOT NULL ,
[Thursday_Use] [bit] NOT NULL ,
[Friday_Use] [bit] NOT NULL ,
[Saturday_Use] [bit] NOT NULL ,
[Sunday_Use] [bit] NOT NULL ,
[monday] [int] NOT NULL ,
[tuesday] [int] NOT NULL ,
[wednesday] [int] NOT NULL ,
[thursday] [int] NOT NULL ,
[friday] [int] NOT NULL ,
[saturday] [int] NOT NULL ,
[sunday] [int] NOT NULL ,
[Date_Changed] [datetime] NULL ,
[Changed_By] [char] (8) COLLATE SQL_Latin1_General_CP437_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MV_Detail] ADD
CONSTRAINT [DF_MV_Detail_UseAny] DEFAULT (0) FOR [UseAny],
CONSTRAINT [DF_MV_Detail_Monday_Use] DEFAULT (0) FOR [Monday_Use],
CONSTRAINT [DF_MV_Detail_Tuesday_Use] DEFAULT (0) FOR [Tuesday_Use],
CONSTRAINT [DF_MV_Detail_Wednesday_Use] DEFAULT (0) FOR [Wednesday_Use],
CONSTRAINT [DF_MV_Detail_Thursday_Use] DEFAULT (0) FOR [Thursday_Use],
CONSTRAINT [DF_MV_Detail_Friday_Use] DEFAULT (0) FOR [Friday_Use],
CONSTRAINT [DF_MV_Detail_Saturday_Use] DEFAULT (0) FOR [Saturday_Use],
CONSTRAINT [DF_MV_Detail_Sunday_Use] DEFAULT (0) FOR [Sunday_Use]
GO
CREATE INDEX [IX_MV_Detail] ON [dbo].[MV_Detail]([Tailor_ID], [geography]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE UNIQUE INDEX [CircTailorGeoType] ON [dbo].[MV_Detail]([Circ_ID], [Tailor_ID], [geography], [circtype]) WITH FILLFACTOR =
90 ON [PRIMARY]
GO
CREATE INDEX [IX_Circ_Trigger] ON [dbo].[MV_Detail]([Circ_ID], [Tailor_ID], [circtype]) WITH FILLF
ACTOR = 90 ON [PRIMARY]
GO|||If it is a hardware issue, would it be capturing it in error logs anywhere?
Mike
"Steve Kass" <skass@.drew.edu> wrote in message news:OEbuj8E5DHA.1804@.TK2MSFT
NGP12.phx.gbl...
Mike,
It sounds like data corruption. See if this article helps:
http://support.microsoft.com/defaul...kb;en-us;826433
SK
"Mike" <Mike@.Comcast.net> wrote in message news:u$1HcAD5DHA.564@.TK2MSFTNGP10
.phx.gbl...
SQL2K EE SP3 , WIN 2K EE SP2
We have a 27 Gb database that continually has index / performance problems.
We do a DBCC DBREINDEX every night, and sp_updatestats every hour during the
day. The database has a lot of read, write and delete activity, usually inv
olving 1000's of records at a time and more than 100,000 records a few time
s a day.
Because of slow query response we started doing the DBREINDEX every night an
d it improved performance.
Than we started getting the following errors:
Cannot retrieve row from page (64:2562414) by RID because the slotid (742594
184) is not valid.
This was solved after the nightly DBREINDEX, but since we cannot DBREINDEX d
uring the day while users are in the system we started running sp_updatestat
s every hour and this fixes it as well.
Now we have had a few times were a query returns no records even though the
data exists. The last time we had the problem the following query was being
run:
SELECT *
FROM dbo.MV_Detail
WHERE Tailor_ID = 359884
AND Circ_ID = 81670
If we take out the AND Circ_ID = 81670 statement and do an order by on the t
able we see that we have data that match the criteria, or if we add a 3rd cr
iteria with a table join then we see the data.
SELECT *
FROM dbo.mv_Detail a,
(select distinct geography from tempdb.dbo.mv_Geo40499999) b
where a.Tailor_ID = 359884 AND a.Circ_ID = 81670 and b.geography = a.geograp
hy
I ran sp_updatestats on the database and we still did not get any records fr
om the first query. After we ran DBREINDEX the first query started to return
records again.
I have included the table create script for the main table were we have prob
lems with. This table is about 1/3 of the database size and contains almost
60 million records and uses about 5Gb in data space and 5.5 Gb in index spac
e.
Any ideas on how to improve this situation would be greatly appreciated. We
changed the fill factor on the indexes from 80 to 90 to conserve space, but
have added disk space since then. Could reducing the fill factor help with t
hese types of problems?
CREATE TABLE [dbo].[MV_Detail] (
[Circ_ID] [int] NOT NULL ,
[Tailor_ID] [int] NOT NULL ,
[geography] [varchar] (10) COLLATE SQL_Latin1_General_CP437_CI_AS NOT NULL ,
[circtype] [int] NOT NULL ,
[UseAny] [bit] NOT NULL ,
[Monday_Use] [bit] NOT NULL ,
[Tuesday_Use] [bit] NOT NULL ,
[Wednesday_Use] [bit] NOT NULL ,
[Thursday_Use] [bit] NOT NULL ,
[Friday_Use] [bit] NOT NULL ,
[Saturday_Use] [bit] NOT NULL ,
[Sunday_Use] [bit] NOT NULL ,
[monday] [int] NOT NULL ,
[tuesday] [int] NOT NULL ,
[wednesday] [int] NOT NULL ,
[thursday] [int] NOT NULL ,
[friday] [int] NOT NULL ,
[saturday] [int] NOT NULL ,
[sunday] [int] NOT NULL ,
[Date_Changed] [datetime] NULL ,
[Changed_By] [char] (8) COLLATE SQL_Latin1_General_CP437_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MV_Detail] ADD
CONSTRAINT [DF_MV_Detail_UseAny] DEFAULT (0) FOR [UseAny],
CONSTRAINT [DF_MV_Detail_Monday_Use] DEFAULT (0) FOR [Monday_Use],
CONSTRAINT [DF_MV_Detail_Tuesday_Use] DEFAULT (0) FOR [Tuesday_Use],
CONSTRAINT [DF_MV_Detail_Wednesday_Use] DEFAULT (0) FOR [Wednesday_Use],
CONSTRAINT [DF_MV_Detail_Thursday_Use] DEFAULT (0) FOR [Thursday_Use],
CONSTRAINT [DF_MV_Detail_Friday_Use] DEFAULT (0) FOR [Friday_Use],
CONSTRAINT [DF_MV_Detail_Saturday_Use] DEFAULT (0) FOR [Saturday_Use],
CONSTRAINT [DF_MV_Detail_Sunday_Use] DEFAULT (0) FOR [Sunday_Use]
GO
CREATE INDEX [IX_MV_Detail] ON [dbo].[MV_Detail]([Tailor_ID], [geography]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE UNIQUE INDEX [CircTailorGeoType] ON [dbo].[MV_Detail]([Circ_ID], [Tailor_ID], [geography], [circtype]) WITH FILLFACTOR =
90 ON [PRIMARY]
GO
CREATE INDEX [IX_Circ_Trigger] ON [dbo].[MV_Detail]([Circ_ID], [Tailor_ID], [circtype]) WITH FILLF
ACTOR = 90 ON [PRIMARY]
GO