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

Index not replicated to the device

Hi,

I have a table published on the SQL2K Merger Replication publisher. But when I add an index to the table it's not replicated to the device. I have checked the snapshot folder and the newly added index is already in the .dri file. Any ideas?

Cheers,
Justin
One more thing to add, the index is not replicated to the existing subscriber but it works with new subscibers. Does that mean I have to drop and re-add subscription everytime a new index is added on the publisher?

Justin|||

Justin,

Replication supports a wide range of schema changes to published objects. When you make those schema changes on the appropriate published object at a Microsoft SQL Server Publisher, that change is propagated by default to all SQL Server Subscribers. However, Explicitly adding, dropping, or altering indexes is not supported. That is why you see the index is not replicated to the existing subscriber but it works with new subscribers.

You don't need to drop and re-add subscription every time a new index is added on the publisher, you just need to reinitialize the subscribers. Or you can add the index manually at subscribers Or using sp_addscriptexec to populate the index creation script to all the subscribers.

Hope it helps.

Wanwen

|||Thx, Wanwen.|||One more thing...as there will be no notification from the publisher or exception generated from the device during the replication after a new index is add but not pulled down to the device, is there a way we could know when to reinitialize the subscription?