Showing posts with label text. Show all posts
Showing posts with label text. Show all posts
Monday, March 26, 2012
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.
Friday, March 23, 2012
Index problems
This is a multi-part message in MIME format.
--=_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" wrote in message news:u$1HcAD5DHA.564@.T=K2MSFTNGP10.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 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" wrote in message news:OEbuj8E5DHA.1804=@.TK2MSFTNGP12.phx.gbl...
Mike,
It sounds like data corruption. See =if this article helps:
SK
"Mike" wrote in message news:u$1HcAD5DHA.564@.T=K2MSFTNGP10.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 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
--=_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
Monday, March 19, 2012
Index on 4 columns does not return results in expected order
We have a Full Text Index on 4 columns and it doesn’t return the results in
the order we think it should in 2000 or 2005. In 2005 I created a persisted
calculated column of these 4 columns and created the Full Text Index on it
and I returned the results in the order I expected them. In my mind both of
these methods should return the results in the same order. If not then I
don’t see how anyone could use Full Text Indexes on multiple columns.
I'm I doing something wrong or is this just the way it is?
Kenny wrote on Thu, 8 Feb 2007 09:14:04 -0800:
> We have a Full Text Index on 4 columns and it doesnt return the results
> in the order we think it should in 2000 or 2005. In 2005 I created a
> persisted calculated column of these 4 columns and created the Full Text
> Index on it and I returned the results in the order I expected them. In
> my mind both of these methods should return the results in the same order.
> If not then I dont see how anyone could use Full Text Indexes on multiple
> columns.
> I'm I doing something wrong or is this just the way it is?
>
Are you using the RANK value to sort them? If you have no ORDER BY clause in
your query, the row ordering is always indeterminate. If you use an ORDER BY
clause you should always get them in the same order (unless use RANK to
order them and the RANK calculations are different in 2000 and 2005, Hilary
would probably be able to tell you if this is the case).
Dan
|||I am ordering by Rank. I was trying to ask the question in a generic way but
maybe it would be better to see the code.
I’ve done some testing with FT Search and I’m not sure if it works like I
think it should. In my mind these two Indexes should return the same results
for the table below:
CREATE FULLTEXT INDEX ON dbo.wrkTestFTIndex
(cFirstName,cLastName,cTitle,vcEmailAddr)
KEY INDEX PK_wrkTestFTIndex
ON [TestFCCatalog]
CREATE FULLTEXT INDEX ON dbo.wrkTestFTIndex
(ccFullName)
KEY INDEX PK_wrkTestFTIndex
ON [TestFCCatalog]
The FT Index on ccFullName the persisted calculated column seems to return
better results than the other one (example below: Gary Walker is returned
with the highest ranking). Am I doing something wrong querying the other
index?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[wrkTestFTIndex](
[intFaclNbr] [int] NOT NULL,
[cFirstName] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cLastName] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cTitle] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[vcEmailAddr] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FCidentity] [int] IDENTITY(1,1) NOT NULL,
[ccFullName] AS ((((((rtrim(isnull([cFirstname],''))+'
')+rtrim(isnull([cLastName],'')))+' ')+rtrim(isnull([cTitle],'')))+'
')+rtrim(isnull([vcEmailAddr],''))) PERSISTED,
CONSTRAINT [PK_wrkTestFTIndex] PRIMARY KEY CLUSTERED
( [FCidentity] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
CREATE FULLTEXT CATALOG [TestFCCatalog]
ON FILEGROUP [FTIndex]
IN PATH 'd:\sql2000ftindex'
AS DEFAULT
Select Rank,fc.* from wrkTestFTIndex fc inner join
FREETEXTTABLE(wrkTestFTIndex, *,'Gary Walker') AS KEY_TBL
ON fc.FCidentity = KEY_TBL.[KEY]
order by Rank DESC
"Daniel Crichton" wrote:
> Kenny wrote on Thu, 8 Feb 2007 09:14:04 -0800:
>
> Are you using the RANK value to sort them? If you have no ORDER BY clause in
> your query, the row ordering is always indeterminate. If you use an ORDER BY
> clause you should always get them in the same order (unless use RANK to
> order them and the RANK calculations are different in 2000 and 2005, Hilary
> would probably be able to tell you if this is the case).
> Dan
>
>
the order we think it should in 2000 or 2005. In 2005 I created a persisted
calculated column of these 4 columns and created the Full Text Index on it
and I returned the results in the order I expected them. In my mind both of
these methods should return the results in the same order. If not then I
don’t see how anyone could use Full Text Indexes on multiple columns.
I'm I doing something wrong or is this just the way it is?
Kenny wrote on Thu, 8 Feb 2007 09:14:04 -0800:
> We have a Full Text Index on 4 columns and it doesnt return the results
> in the order we think it should in 2000 or 2005. In 2005 I created a
> persisted calculated column of these 4 columns and created the Full Text
> Index on it and I returned the results in the order I expected them. In
> my mind both of these methods should return the results in the same order.
> If not then I dont see how anyone could use Full Text Indexes on multiple
> columns.
> I'm I doing something wrong or is this just the way it is?
>
Are you using the RANK value to sort them? If you have no ORDER BY clause in
your query, the row ordering is always indeterminate. If you use an ORDER BY
clause you should always get them in the same order (unless use RANK to
order them and the RANK calculations are different in 2000 and 2005, Hilary
would probably be able to tell you if this is the case).
Dan
|||I am ordering by Rank. I was trying to ask the question in a generic way but
maybe it would be better to see the code.
I’ve done some testing with FT Search and I’m not sure if it works like I
think it should. In my mind these two Indexes should return the same results
for the table below:
CREATE FULLTEXT INDEX ON dbo.wrkTestFTIndex
(cFirstName,cLastName,cTitle,vcEmailAddr)
KEY INDEX PK_wrkTestFTIndex
ON [TestFCCatalog]
CREATE FULLTEXT INDEX ON dbo.wrkTestFTIndex
(ccFullName)
KEY INDEX PK_wrkTestFTIndex
ON [TestFCCatalog]
The FT Index on ccFullName the persisted calculated column seems to return
better results than the other one (example below: Gary Walker is returned
with the highest ranking). Am I doing something wrong querying the other
index?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[wrkTestFTIndex](
[intFaclNbr] [int] NOT NULL,
[cFirstName] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cLastName] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cTitle] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[vcEmailAddr] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FCidentity] [int] IDENTITY(1,1) NOT NULL,
[ccFullName] AS ((((((rtrim(isnull([cFirstname],''))+'
')+rtrim(isnull([cLastName],'')))+' ')+rtrim(isnull([cTitle],'')))+'
')+rtrim(isnull([vcEmailAddr],''))) PERSISTED,
CONSTRAINT [PK_wrkTestFTIndex] PRIMARY KEY CLUSTERED
( [FCidentity] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
CREATE FULLTEXT CATALOG [TestFCCatalog]
ON FILEGROUP [FTIndex]
IN PATH 'd:\sql2000ftindex'
AS DEFAULT
Select Rank,fc.* from wrkTestFTIndex fc inner join
FREETEXTTABLE(wrkTestFTIndex, *,'Gary Walker') AS KEY_TBL
ON fc.FCidentity = KEY_TBL.[KEY]
order by Rank DESC
"Daniel Crichton" wrote:
> Kenny wrote on Thu, 8 Feb 2007 09:14:04 -0800:
>
> Are you using the RANK value to sort them? If you have no ORDER BY clause in
> your query, the row ordering is always indeterminate. If you use an ORDER BY
> clause you should always get them in the same order (unless use RANK to
> order them and the RANK calculations are different in 2000 and 2005, Hilary
> would probably be able to tell you if this is the case).
> Dan
>
>
Sunday, February 19, 2012
index and query optimization plzzz
I have Table1, Table2,
Table1 Have ID(primary key),Court_ID (clustered index)..
Table2 Have Table1_ID,Rule_No(both are clustered primary key), Text (text)
Table 2 have 1,000,000 record, I use this query:
SELECT t2.[Rule_No], t2.[Text], FROM Table2 AS t2 right join Table1 AS t1
on (t1.ID = t2.[Table1_ID]) Where t1.Court_ID =1 and t2.[Text] like '%some
text%'.
I don't talk here about the (like '%%') performance..
When i look at execution plan i found it's estimated row count 1,000,000
record of table2 which i'm sure waste of time, I tried inner join also but
it's the same..
I want 'like' operator to scan only approx 86,000 record which to Court_ID =
1 not scan the whole table then filter it.
Any help plz to correct my indexes or write better query'"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:u23gign8FHA.2364@.TK2MSFTNGP12.phx.gbl...
>I have Table1, Table2,
> Table1 Have ID(primary key),Court_ID (clustered index)..
> Table2 Have Table1_ID,Rule_No(both are clustered primary key), Text (text)
> Table 2 have 1,000,000 record, I use this query:
> SELECT t2.[Rule_No], t2.[Text], FROM Table2 AS t2 right join Table1 AS t1
> on (t1.ID = t2.[Table1_ID]) Where t1.Court_ID =1 and t2.[Text] like '%some
> text%'.
> I don't talk here about the (like '%%') performance..
> When i look at execution plan i found it's estimated row count 1,000,000
> record of table2 which i'm sure waste of time, I tried inner join also but
> it's the same..
> I want 'like' operator to scan only approx 86,000 record which to Court_ID
> = 1 not scan the whole table then filter it.
> Any help plz to correct my indexes or write better query'
Post you actual table DDL.
David|||CREATE TABLE [AH_Master] (
[ID] [PKInt] NOT NULL ,
[Ma7kama_ID] [PKInt] NOT NULL ,
[Case_No] [int] NOT NULL ,
[Case_Year] [smallint] NOT NULL ,
[Case_Date] [datetime] NOT NULL ,
[Office_Year] [smallint] NULL ,
[Office_Sufix] [char] (2) COLLATE Arabic_CI_AI_KS_WS NULL ,
[Page_No] [smallint] NULL ,
[Master_Text] [varchar] (200) COLLATE Arabic_BIN NULL ,
[IF_Agree] [smallint] NULL CONSTRAINT [DF__AH_Master__IF_Ag__79A81403]
DEFAULT (0),
[Part_No] [smallint] NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [PK_AH_MASTER] PRIMARY KEY NONCLUSTERED
(
[ID]
) WITH FILLFACTOR = 80 ON [PRIMARY] ,
CONSTRAINT [FK_AH_MASTE_REFERENCE_AH_MA7AK] FOREIGN KEY
(
[Ma7kama_ID]
) REFERENCES [AH_Ma7akem] (
[ID]
) ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [AH_SubMaster] (
[Master_ID] [int] NOT NULL ,
[Fakra_No] [smallint] NOT NULL ,
[Fakra_Text] [text] COLLATE Arabic_BIN NOT NULL ,
[Tasneef_ID] [PKInt] NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [MyKey_PK_1] PRIMARY KEY NONCLUSTERED
(
[Master_ID],
[Fakra_No]
) WITH FILLFACTOR = 80 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
and my query is:
SELECT Sub.[Fakra_No], Sub.[Fakra_Text], Sub.[Tasneef_ID] FROM AH_SubMaster
AS Sub right join AH_Master AS MT on (Sub.Master_ID =MT.[ID]) Where
MT.Ma7kama_ID =1 and Sub.[Fakra_Text] like '%sometext%'
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23Ptp6Fq8FHA.476@.TK2MSFTNGP15.phx.gbl...
> "Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
> news:u23gign8FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Post you actual table DDL.
> David
>|||According to your DDL, there are no clustered indexes on your tables. Try
clustering AH_Master on Ma7kama_ID and clustering AH_SubMaster on MasterID
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:uHMFKgq8FHA.2492@.TK2MSFTNGP10.phx.gbl...
> CREATE TABLE [AH_Master] (
> [ID] [PKInt] NOT NULL ,
> [Ma7kama_ID] [PKInt] NOT NULL ,
> [Case_No] [int] NOT NULL ,
> [Case_Year] [smallint] NOT NULL ,
> [Case_Date] [datetime] NOT NULL ,
> [Office_Year] [smallint] NULL ,
> [Office_Sufix] [char] (2) COLLATE Arabic_CI_AI_KS_WS NULL ,
> [Page_No] [smallint] NULL ,
> [Master_Text] [varchar] (200) COLLATE Arabic_BIN NULL ,
> [IF_Agree] [smallint] NULL CONSTRAINT [DF__AH_Master__IF_Ag__79A81403]
> DEFAULT (0),
> [Part_No] [smallint] NULL ,
> [UserID] [int] NULL ,
> [LastModify] [datetime] NULL ,
> CONSTRAINT [PK_AH_MASTER] PRIMARY KEY NONCLUSTERED
> (
> [ID]
> ) WITH FILLFACTOR = 80 ON [PRIMARY] ,
> CONSTRAINT [FK_AH_MASTE_REFERENCE_AH_MA7AK] FOREIGN KEY
> (
> [Ma7kama_ID]
> ) REFERENCES [AH_Ma7akem] (
> [ID]
> ) ON UPDATE CASCADE
> ) ON [PRIMARY]
> GO
> CREATE TABLE [AH_SubMaster] (
> [Master_ID] [int] NOT NULL ,
> [Fakra_No] [smallint] NOT NULL ,
> [Fakra_Text] [text] COLLATE Arabic_BIN NOT NULL ,
> [Tasneef_ID] [PKInt] NULL ,
> [UserID] [int] NULL ,
> [LastModify] [datetime] NULL ,
> CONSTRAINT [MyKey_PK_1] PRIMARY KEY NONCLUSTERED
> (
> [Master_ID],
> [Fakra_No]
> ) WITH FILLFACTOR = 80 ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> and my query is:
> SELECT Sub.[Fakra_No], Sub.[Fakra_Text], Sub.[Tasneef_ID] FROM
> AH_SubMaster AS Sub right join AH_Master AS MT on (Sub.Master_ID =MT.[ID])
> Where MT.Ma7kama_ID =1 and Sub.[Fakra_Text] like '%sometext%'
>
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23Ptp6Fq8FHA.476@.TK2MSFTNGP15.phx.gbl...
>|||"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:u23gign8FHA.2364@.TK2MSFTNGP12.phx.gbl...
> I have Table1, Table2,
> Table1 Have ID(primary key),Court_ID (clustered index)..
> Table2 Have Table1_ID,Rule_No(both are clustered primary key),
Text (text)
> Table 2 have 1,000,000 record, I use this query:
> SELECT t2.[Rule_No], t2.[Text], FROM Table2 AS t2 right join
Table1 AS t1
> on (t1.ID = t2.[Table1_ID]) Where t1.Court_ID =1 and t2.[Text]
like '%some
> text%'.
> I don't talk here about the (like '%%') performance..
> When i look at execution plan i found it's estimated row count
1,000,000
> record of table2 which i'm sure waste of time, I tried inner join
also but
> it's the same..
> I want 'like' operator to scan only approx 86,000 record which to
Court_ID =
> 1 not scan the whole table then filter it.
> Any help plz to correct my indexes or write better query'
>
Isalamegy,
The predicate:
like '%some text%'
Will always cause an index or table scan as far as I know. The
potential exists for it to equal the column value of any row, and so
the column must be scanned.
Even if the column is indexed, depending on a variety of factors,
the query optimizer may decide the cost of scanning the index
reaches the point where the whole table might as well be scanned,
and so it will switch from index scan to table scan.
Sincerely,
Chris O.|||"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:uHMFKgq8FHA.2492@.TK2MSFTNGP10.phx.gbl...
<snip>
> [Ma7kama_ID] [PKInt] NOT NULL ,
<snip>
> [Tasneef_ID] [PKInt] NULL ,
BOL and Google don't seem to mention PKInt as a data type in SQL
Server.
What am I missing here?
Sincerely,
Chris O.|||There is no such datatype as PKInt. It is probably a user defined type.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in message
news:OaOdnfGosarMMxXeRVn-hA@.comcast.com...
> "Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
> news:uHMFKgq8FHA.2492@.TK2MSFTNGP10.phx.gbl...
> <snip>
>
> <snip>
>
>
> BOL and Google don't seem to mention PKInt as a data type in SQL
> Server.
> What am I missing here?
>
> Sincerely,
> Chris O.
>
>|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23pfZlur8FHA.1140@.tk2msftngp13.phx.gbl...
> There is no such datatype as PKInt. It is probably a user defined
type.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
Kalen Delaney,
Ah, I didn't think of that. Thank you.
Sincerely,
Chris O.|||Yes.. PKInt is a user defined datatype..
The clustered indexs that Tom suggest is exist in my tables ..
so my question. why even i narrow the search using Ma7kama_ID from table
master it still scan the whole AH_SubMaster Text column... then filter it!!
Why it don't search only in fields with the specified Ma7kama_ID'
am i have to add Ma7kama_ID field to table AH_SubMaster, I tried it and it
work as i expected but this way will duplicate ma7kama_ID in many of my
tables with text which i need to scan with the same way..
any ideas'
"Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in message
news:soydnYOHY8Ew1BTeRVn-vQ@.comcast.com...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23pfZlur8FHA.1140@.tk2msftngp13.phx.gbl...
> type.
> Kalen Delaney,
> Ah, I didn't think of that. Thank you.
>
> Sincerely,
> Chris O.
>|||You may want to try index hints in this case.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:eY1jNHz8FHA.2036@.TK2MSFTNGP14.phx.gbl...
Yes.. PKInt is a user defined datatype..
The clustered indexs that Tom suggest is exist in my tables ..
so my question. why even i narrow the search using Ma7kama_ID from table
master it still scan the whole AH_SubMaster Text column... then filter it!!
Why it don't search only in fields with the specified Ma7kama_ID'
am i have to add Ma7kama_ID field to table AH_SubMaster, I tried it and it
work as i expected but this way will duplicate ma7kama_ID in many of my
tables with text which i need to scan with the same way..
any ideas'
"Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in message
news:soydnYOHY8Ew1BTeRVn-vQ@.comcast.com...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23pfZlur8FHA.1140@.tk2msftngp13.phx.gbl...
> type.
> Kalen Delaney,
> Ah, I didn't think of that. Thank you.
>
> Sincerely,
> Chris O.
>
Table1 Have ID(primary key),Court_ID (clustered index)..
Table2 Have Table1_ID,Rule_No(both are clustered primary key), Text (text)
Table 2 have 1,000,000 record, I use this query:
SELECT t2.[Rule_No], t2.[Text], FROM Table2 AS t2 right join Table1 AS t1
on (t1.ID = t2.[Table1_ID]) Where t1.Court_ID =1 and t2.[Text] like '%some
text%'.
I don't talk here about the (like '%%') performance..
When i look at execution plan i found it's estimated row count 1,000,000
record of table2 which i'm sure waste of time, I tried inner join also but
it's the same..
I want 'like' operator to scan only approx 86,000 record which to Court_ID =
1 not scan the whole table then filter it.
Any help plz to correct my indexes or write better query'"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:u23gign8FHA.2364@.TK2MSFTNGP12.phx.gbl...
>I have Table1, Table2,
> Table1 Have ID(primary key),Court_ID (clustered index)..
> Table2 Have Table1_ID,Rule_No(both are clustered primary key), Text (text)
> Table 2 have 1,000,000 record, I use this query:
> SELECT t2.[Rule_No], t2.[Text], FROM Table2 AS t2 right join Table1 AS t1
> on (t1.ID = t2.[Table1_ID]) Where t1.Court_ID =1 and t2.[Text] like '%some
> text%'.
> I don't talk here about the (like '%%') performance..
> When i look at execution plan i found it's estimated row count 1,000,000
> record of table2 which i'm sure waste of time, I tried inner join also but
> it's the same..
> I want 'like' operator to scan only approx 86,000 record which to Court_ID
> = 1 not scan the whole table then filter it.
> Any help plz to correct my indexes or write better query'
Post you actual table DDL.
David|||CREATE TABLE [AH_Master] (
[ID] [PKInt] NOT NULL ,
[Ma7kama_ID] [PKInt] NOT NULL ,
[Case_No] [int] NOT NULL ,
[Case_Year] [smallint] NOT NULL ,
[Case_Date] [datetime] NOT NULL ,
[Office_Year] [smallint] NULL ,
[Office_Sufix] [char] (2) COLLATE Arabic_CI_AI_KS_WS NULL ,
[Page_No] [smallint] NULL ,
[Master_Text] [varchar] (200) COLLATE Arabic_BIN NULL ,
[IF_Agree] [smallint] NULL CONSTRAINT [DF__AH_Master__IF_Ag__79A81403]
DEFAULT (0),
[Part_No] [smallint] NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [PK_AH_MASTER] PRIMARY KEY NONCLUSTERED
(
[ID]
) WITH FILLFACTOR = 80 ON [PRIMARY] ,
CONSTRAINT [FK_AH_MASTE_REFERENCE_AH_MA7AK] FOREIGN KEY
(
[Ma7kama_ID]
) REFERENCES [AH_Ma7akem] (
[ID]
) ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [AH_SubMaster] (
[Master_ID] [int] NOT NULL ,
[Fakra_No] [smallint] NOT NULL ,
[Fakra_Text] [text] COLLATE Arabic_BIN NOT NULL ,
[Tasneef_ID] [PKInt] NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [MyKey_PK_1] PRIMARY KEY NONCLUSTERED
(
[Master_ID],
[Fakra_No]
) WITH FILLFACTOR = 80 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
and my query is:
SELECT Sub.[Fakra_No], Sub.[Fakra_Text], Sub.[Tasneef_ID] FROM AH_SubMaster
AS Sub right join AH_Master AS MT on (Sub.Master_ID =MT.[ID]) Where
MT.Ma7kama_ID =1 and Sub.[Fakra_Text] like '%sometext%'
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23Ptp6Fq8FHA.476@.TK2MSFTNGP15.phx.gbl...
> "Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
> news:u23gign8FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Post you actual table DDL.
> David
>|||According to your DDL, there are no clustered indexes on your tables. Try
clustering AH_Master on Ma7kama_ID and clustering AH_SubMaster on MasterID
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:uHMFKgq8FHA.2492@.TK2MSFTNGP10.phx.gbl...
> CREATE TABLE [AH_Master] (
> [ID] [PKInt] NOT NULL ,
> [Ma7kama_ID] [PKInt] NOT NULL ,
> [Case_No] [int] NOT NULL ,
> [Case_Year] [smallint] NOT NULL ,
> [Case_Date] [datetime] NOT NULL ,
> [Office_Year] [smallint] NULL ,
> [Office_Sufix] [char] (2) COLLATE Arabic_CI_AI_KS_WS NULL ,
> [Page_No] [smallint] NULL ,
> [Master_Text] [varchar] (200) COLLATE Arabic_BIN NULL ,
> [IF_Agree] [smallint] NULL CONSTRAINT [DF__AH_Master__IF_Ag__79A81403]
> DEFAULT (0),
> [Part_No] [smallint] NULL ,
> [UserID] [int] NULL ,
> [LastModify] [datetime] NULL ,
> CONSTRAINT [PK_AH_MASTER] PRIMARY KEY NONCLUSTERED
> (
> [ID]
> ) WITH FILLFACTOR = 80 ON [PRIMARY] ,
> CONSTRAINT [FK_AH_MASTE_REFERENCE_AH_MA7AK] FOREIGN KEY
> (
> [Ma7kama_ID]
> ) REFERENCES [AH_Ma7akem] (
> [ID]
> ) ON UPDATE CASCADE
> ) ON [PRIMARY]
> GO
> CREATE TABLE [AH_SubMaster] (
> [Master_ID] [int] NOT NULL ,
> [Fakra_No] [smallint] NOT NULL ,
> [Fakra_Text] [text] COLLATE Arabic_BIN NOT NULL ,
> [Tasneef_ID] [PKInt] NULL ,
> [UserID] [int] NULL ,
> [LastModify] [datetime] NULL ,
> CONSTRAINT [MyKey_PK_1] PRIMARY KEY NONCLUSTERED
> (
> [Master_ID],
> [Fakra_No]
> ) WITH FILLFACTOR = 80 ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> and my query is:
> SELECT Sub.[Fakra_No], Sub.[Fakra_Text], Sub.[Tasneef_ID] FROM
> AH_SubMaster AS Sub right join AH_Master AS MT on (Sub.Master_ID =MT.[ID])
> Where MT.Ma7kama_ID =1 and Sub.[Fakra_Text] like '%sometext%'
>
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23Ptp6Fq8FHA.476@.TK2MSFTNGP15.phx.gbl...
>|||"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:u23gign8FHA.2364@.TK2MSFTNGP12.phx.gbl...
> I have Table1, Table2,
> Table1 Have ID(primary key),Court_ID (clustered index)..
> Table2 Have Table1_ID,Rule_No(both are clustered primary key),
Text (text)
> Table 2 have 1,000,000 record, I use this query:
> SELECT t2.[Rule_No], t2.[Text], FROM Table2 AS t2 right join
Table1 AS t1
> on (t1.ID = t2.[Table1_ID]) Where t1.Court_ID =1 and t2.[Text]
like '%some
> text%'.
> I don't talk here about the (like '%%') performance..
> When i look at execution plan i found it's estimated row count
1,000,000
> record of table2 which i'm sure waste of time, I tried inner join
also but
> it's the same..
> I want 'like' operator to scan only approx 86,000 record which to
Court_ID =
> 1 not scan the whole table then filter it.
> Any help plz to correct my indexes or write better query'
>
Isalamegy,
The predicate:
like '%some text%'
Will always cause an index or table scan as far as I know. The
potential exists for it to equal the column value of any row, and so
the column must be scanned.
Even if the column is indexed, depending on a variety of factors,
the query optimizer may decide the cost of scanning the index
reaches the point where the whole table might as well be scanned,
and so it will switch from index scan to table scan.
Sincerely,
Chris O.|||"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:uHMFKgq8FHA.2492@.TK2MSFTNGP10.phx.gbl...
<snip>
> [Ma7kama_ID] [PKInt] NOT NULL ,
<snip>
> [Tasneef_ID] [PKInt] NULL ,
BOL and Google don't seem to mention PKInt as a data type in SQL
Server.
What am I missing here?
Sincerely,
Chris O.|||There is no such datatype as PKInt. It is probably a user defined type.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in message
news:OaOdnfGosarMMxXeRVn-hA@.comcast.com...
> "Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
> news:uHMFKgq8FHA.2492@.TK2MSFTNGP10.phx.gbl...
> <snip>
>
> <snip>
>
>
> BOL and Google don't seem to mention PKInt as a data type in SQL
> Server.
> What am I missing here?
>
> Sincerely,
> Chris O.
>
>|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23pfZlur8FHA.1140@.tk2msftngp13.phx.gbl...
> There is no such datatype as PKInt. It is probably a user defined
type.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
Kalen Delaney,
Ah, I didn't think of that. Thank you.
Sincerely,
Chris O.|||Yes.. PKInt is a user defined datatype..
The clustered indexs that Tom suggest is exist in my tables ..
so my question. why even i narrow the search using Ma7kama_ID from table
master it still scan the whole AH_SubMaster Text column... then filter it!!
Why it don't search only in fields with the specified Ma7kama_ID'
am i have to add Ma7kama_ID field to table AH_SubMaster, I tried it and it
work as i expected but this way will duplicate ma7kama_ID in many of my
tables with text which i need to scan with the same way..
any ideas'
"Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in message
news:soydnYOHY8Ew1BTeRVn-vQ@.comcast.com...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23pfZlur8FHA.1140@.tk2msftngp13.phx.gbl...
> type.
> Kalen Delaney,
> Ah, I didn't think of that. Thank you.
>
> Sincerely,
> Chris O.
>|||You may want to try index hints in this case.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:eY1jNHz8FHA.2036@.TK2MSFTNGP14.phx.gbl...
Yes.. PKInt is a user defined datatype..
The clustered indexs that Tom suggest is exist in my tables ..
so my question. why even i narrow the search using Ma7kama_ID from table
master it still scan the whole AH_SubMaster Text column... then filter it!!
Why it don't search only in fields with the specified Ma7kama_ID'
am i have to add Ma7kama_ID field to table AH_SubMaster, I tried it and it
work as i expected but this way will duplicate ma7kama_ID in many of my
tables with text which i need to scan with the same way..
any ideas'
"Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in message
news:soydnYOHY8Ew1BTeRVn-vQ@.comcast.com...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23pfZlur8FHA.1140@.tk2msftngp13.phx.gbl...
> type.
> Kalen Delaney,
> Ah, I didn't think of that. Thank you.
>
> Sincerely,
> Chris O.
>
Subscribe to:
Posts (Atom)