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