--=_NextPart_000_001D_01C3E3FD.CFFEF710
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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 involving 1000's of records at a time and more than = 100,000 records a few times a day.
Because of slow query response we started doing the DBREINDEX every = night and it improved performance.
Than we started getting the following errors:
Cannot retrieve row from page (64:2562414) by RID because the slotid = (742594184) is not valid.
This was solved after the nightly DBREINDEX, but since we cannot = DBREINDEX during the day while users are in the system we started = running sp_updatestats 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 =3D 359884
AND Circ_ID =3D 81670
If we take out the AND Circ_ID =3D 81670 statement and do an order by on = the table we see that we have data that match the criteria, or if we add = a 3rd criteria 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 =3D 359884 AND a.Circ_ID =3D 81670 and b.geography =3D = a.geography
I ran sp_updatestats on the database and we still did not get any = records from 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 = problems 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 space.
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 these 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 =3D 90 ON [PRIMARY]
GO
CREATE UNIQUE INDEX [CircTailorGeoType] ON = [dbo].[MV_Detail]([Circ_ID], [Tailor_ID], [geography], [circtype]) WITH = FILLFACTOR =3D 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Circ_Trigger] ON [dbo].[MV_Detail]([Circ_ID], = [Tailor_ID], [circtype]) WITH FILLFACTOR =3D 90 ON [PRIMARY]
GO
--=_NextPart_000_001D_01C3E3FD.CFFEF710
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
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 involving 1000's of records at a time = and more than 100,000 records a few times a day.
Because of slow query response we = started doing the DBREINDEX every night and it improved performance.
Than we started getting the following errors:
Cannot retrieve row from page = (64:2562414) by RID because the slotid (742594184) is not = valid.
This was solved after the nightly = DBREINDEX, but since we cannot DBREINDEX during the day while users are in the system = we started running sp_updatestats 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 =3D 359884AND = Circ_ID =3D 81670
If we take out the AND Circ_ID =3D = 81670 statement and do an order by on the table we see that we have data that match = the criteria, or if we add a 3rd criteria 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 =3D 359884 AND a.Circ_ID =3D 81670 and b.geography =3D a.geography
I ran sp_updatestats on the database and we still did not get any = records from 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 problems 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 space.
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 these 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 =3D 90 = ON [PRIMARY]GO
CREATE UNIQUE INDEX [CircTailorGeoType] ON [dbo].[MV_Detail]([Circ_ID], [Tailor_ID], = [geography], [circtype]) WITH FILLFACTOR =3D 90 ON [PRIMARY]GO
CREATE INDEX = [IX_Circ_Trigger] ON [dbo].[MV_Detail]([Circ_ID], [Tailor_ID], [circtype]) WITH = FILLFACTOR =3D 90 ON [PRIMARY]GO
--=_NextPart_000_001D_01C3E3FD.CFFEF710--This is a multi-part message in MIME format.
--=_NextPart_000_0048_01C3E425.3A26CC90
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Mike,
It sounds like data corruption. See if this article helps:
http://support.microsoft.com/default.aspx?scid=3Dkb;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 involving 1000's of records at a time and more than =100,000 records a few times a day.
Because of slow query response we started doing the DBREINDEX every =night and it improved performance.
Than we started getting the following errors:
Cannot retrieve row from page (64:2562414) by RID because the slotid =(742594184) is not valid.
This was solved after the nightly DBREINDEX, but since we cannot =DBREINDEX during the day while users are in the system we started =running sp_updatestats 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 =3D 359884
AND Circ_ID =3D 81670
If we take out the AND Circ_ID =3D 81670 statement and do an order by =on the table we see that we have data that match the criteria, or if we =add a 3rd criteria 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 =3D 359884 AND a.Circ_ID =3D 81670 and b.geography ==3D a.geography
I ran sp_updatestats on the database and we still did not get any =records from 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 problems 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 space.
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 these 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 =3D 90 ON [PRIMARY]
GO
CREATE UNIQUE INDEX [CircTailorGeoType] ON =[dbo].[MV_Detail]([Circ_ID], [Tailor_ID], [geography], [circtype]) WITH =FILLFACTOR =3D 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Circ_Trigger] ON [dbo].[MV_Detail]([Circ_ID], =[Tailor_ID], [circtype]) WITH FILLFACTOR =3D 90 ON [PRIMARY]
GO
--=_NextPart_000_0048_01C3E425.3A26CC90
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Mike,
It sounds like data corruption. See if =this article helps:
SK
"Mike"
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 involving 1000's of records =at a time and more than 100,000 records a few times a day.
Because of slow query response we =started doing the DBREINDEX every night and it improved performance.
Than we started getting the following = errors:
Cannot retrieve row from page = (64:2562414) by RID because the slotid (742594184) is not valid.
This was solved after the nightly =DBREINDEX, but since we cannot DBREINDEX during the day while users are in the system =we started running sp_updatestats 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 =3D 359884AND =Circ_ID =3D 81670
If we take out the AND Circ_ID =3D =81670 statement and do an order by on the table we see that we have data that =match the criteria, or if we add a 3rd criteria 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 =3D 359884 AND a.Circ_ID =3D 81670 and b.geography =3D =a.geography
I ran sp_updatestats on the database and we still did not get any =records from 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 problems 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 space.
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 these 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 =3D =90 ON [PRIMARY]GO
CREATE UNIQUE INDEX = [CircTailorGeoType] ON [dbo].[MV_Detail]([Circ_ID], [Tailor_ID], =[geography], [circtype]) WITH FILLFACTOR =3D 90 ON =[PRIMARY]GO
CREATE INDEX =[IX_Circ_Trigger] ON [dbo].[MV_Detail]([Circ_ID], [Tailor_ID], [circtype]) WITH =FILLFACTOR =3D 90 ON [PRIMARY]GO
--=_NextPart_000_0048_01C3E425.3A26CC90--|||This is a multi-part message in MIME format.
--=_NextPart_000_0016_01C3F14B.ACF0CF90
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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@.TK2MSFTNGP12.phx.gbl...
Mike,
It sounds like data corruption. See if this article helps:
http://support.microsoft.com/default.aspx?scid=3Dkb;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 involving 1000's of records at a time and more than =100,000 records a few times a day.
Because of slow query response we started doing the DBREINDEX every =night and it improved performance.
Than we started getting the following errors:
Cannot retrieve row from page (64:2562414) by RID because the slotid =(742594184) is not valid.
This was solved after the nightly DBREINDEX, but since we cannot =DBREINDEX during the day while users are in the system we started =running sp_updatestats 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 =3D 359884
AND Circ_ID =3D 81670
If we take out the AND Circ_ID =3D 81670 statement and do an order =by on the table we see that we have data that match the criteria, or if =we add a 3rd criteria 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 =3D 359884 AND a.Circ_ID =3D 81670 and b.geography ==3D a.geography
I ran sp_updatestats on the database and we still did not get any =records from 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 problems 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 space.
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 these 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 =3D 90 ON [PRIMARY]
GO
CREATE UNIQUE INDEX [CircTailorGeoType] ON =[dbo].[MV_Detail]([Circ_ID], [Tailor_ID], [geography], [circtype]) WITH =FILLFACTOR =3D 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Circ_Trigger] ON [dbo].[MV_Detail]([Circ_ID], =[Tailor_ID], [circtype]) WITH FILLFACTOR =3D 90 ON [PRIMARY]
GO
--=_NextPart_000_0016_01C3F14B.ACF0CF90
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
If it is a hardware issue, would it be =capturing it in error logs anywhere?
Mike
"Steve Kass"
Mike,
It sounds like data corruption. See =if this article helps:
SK
"Mike"
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 involving 1000's of records =at a time and more than 100,000 records a few times a =day.
Because of slow query response we =started doing the DBREINDEX every night and it improved performance.
Than we started getting the =following errors:
Cannot retrieve row from =page (64:2562414) by RID because the slotid (742594184) is not valid.
This was solved after the nightly =DBREINDEX, but since we cannot DBREINDEX during the day while users are in the =system we started running sp_updatestats 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 =3D 359884AND =Circ_ID =3D 81670
If we take out the AND Circ_ID =3D =81670 statement and do an order by on the table we see that we have =data that match the criteria, or if we add a 3rd criteria 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 =3D 359884 AND a.Circ_ID =3D 81670 and b.geography =3D =a.geography
I ran sp_updatestats on the database and we still did not get =any records from 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 problems 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 space.
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 these 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 ==3D 90 ON [PRIMARY]GO
CREATE UNIQUE =INDEX [CircTailorGeoType] ON [dbo].[MV_Detail]([Circ_ID], [Tailor_ID], [geography], [circtype]) WITH FILLFACTOR =3D 90 ON [PRIMARY]GO
CREATE INDEX =[IX_Circ_Trigger] ON [dbo].[MV_Detail]([Circ_ID], [Tailor_ID], [circtype]) WITH =FILLFACTOR =3D 90 ON =[PRIMARY]GO
--=_NextPart_000_0016_01C3F14B.ACF0CF90--sql
No comments:
Post a Comment