Hi all,
I created load file (with few test queries) via Profiler and then I tried to
run tuning wizard. Wizards gave message "Workload does not contain any
events or queries that can be tuned against current database ..."
It's strange because load file contains events with queries and this queries
were run over current database (I also set filter condition for database
name to be sure ...)..
Any ideas what am I doing wrong ?
Regards,
Jan
Jan Hruz wrote:
> Hi all,
> I created load file (with few test queries) via Profiler and then I
> tried to run tuning wizard. Wizards gave message "Workload does not
> contain any events or queries that can be tuned against current
> database ..." It's strange because load file contains events with
> queries and this
> queries were run over current database (I also set filter condition
> for database name to be sure ...)..
> Any ideas what am I doing wrong ?
> Regards,
> Jan
I think the ITW requires SQL:BatchCompleted and RPC:Completed events
with the necessary columns. Please post the events and columns that were
captured as well as any filters you used during the trace.
David Gugick
Imceda Software
www.imceda.com
|||I used ProfilerTuning template. it is setup to trace events :
- RPC:Completed
- SQL:BatchCompleted
and data columns :
- EventClass
- TextData
- Duration
- BinaryData
- SPID
I added new filted condition (to track only my requests):
- DBUserName
- DatabaseName
Regards,
Jan
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O604jpHRFHA.3288@.TK2MSFTNGP14.phx.gbl...
> Jan Hruz wrote:
> I think the ITW requires SQL:BatchCompleted and RPC:Completed events with
> the necessary columns. Please post the events and columns that were
> captured as well as any filters you used during the trace.
> --
> David Gugick
> Imceda Software
> www.imceda.com
Showing posts with label message. Show all posts
Showing posts with label message. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Index restructuring
Is their a monitor counter or profiler trace that might tell me when an
index is being restructured during heavy write times?
--
Message posted via http://www.sqlmonster.comWhat do you mean by "restructured"? There are PerfMon counters for page
splits (AccessMethods:PageSplits/sec), is that what you're referring to?
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:b51b69db39ae463fb5aeaab744865dc4@.SQLMonster.com...
> Is their a monitor counter or profiler trace that might tell me when an
> index is being restructured during heavy write times?
> --
> Message posted via http://www.sqlmonster.com|||Yes, I believe so.
I have some application timeouts that occasionally happen and have ruled
out locking, blocking, deadlocks, log growth, data growth, cpu, and memory.
This table is heavy on the writes in comparison to the reads, with a
clustered primary key index with a fillfactor of 90%, and so I suspect the
index might be reaching a point where it is, for lack of a better term
"restructuring" due to running out of free space.
--
Message posted via http://www.sqlmonster.com|||Have you looked at the checkpoints? If you are write intensive when the db
issues a checkpoint it will attempt to write a bunch of dirty pages to disk.
If your drives can not handle the sudden massive requests the other users
will wait in the disk queue until the checkpoint is done. You never
mentioned Disks in your list. Check out the various disk related counters
and the Checkpoint pages per sec in perfmon and see if these correlate tot
he timeouts.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:e0defe3ebbe3418787f6050043594063@.SQLMonster.com...
> Yes, I believe so.
> I have some application timeouts that occasionally happen and have ruled
> out locking, blocking, deadlocks, log growth, data growth, cpu, and
> memory.
> This table is heavy on the writes in comparison to the reads, with a
> clustered primary key index with a fillfactor of 90%, and so I suspect the
> index might be reaching a point where it is, for lack of a better term
> "restructuring" due to running out of free space.
> --
> Message posted via http://www.sqlmonster.com
index is being restructured during heavy write times?
--
Message posted via http://www.sqlmonster.comWhat do you mean by "restructured"? There are PerfMon counters for page
splits (AccessMethods:PageSplits/sec), is that what you're referring to?
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:b51b69db39ae463fb5aeaab744865dc4@.SQLMonster.com...
> Is their a monitor counter or profiler trace that might tell me when an
> index is being restructured during heavy write times?
> --
> Message posted via http://www.sqlmonster.com|||Yes, I believe so.
I have some application timeouts that occasionally happen and have ruled
out locking, blocking, deadlocks, log growth, data growth, cpu, and memory.
This table is heavy on the writes in comparison to the reads, with a
clustered primary key index with a fillfactor of 90%, and so I suspect the
index might be reaching a point where it is, for lack of a better term
"restructuring" due to running out of free space.
--
Message posted via http://www.sqlmonster.com|||Have you looked at the checkpoints? If you are write intensive when the db
issues a checkpoint it will attempt to write a bunch of dirty pages to disk.
If your drives can not handle the sudden massive requests the other users
will wait in the disk queue until the checkpoint is done. You never
mentioned Disks in your list. Check out the various disk related counters
and the Checkpoint pages per sec in perfmon and see if these correlate tot
he timeouts.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:e0defe3ebbe3418787f6050043594063@.SQLMonster.com...
> Yes, I believe so.
> I have some application timeouts that occasionally happen and have ruled
> out locking, blocking, deadlocks, log growth, data growth, cpu, and
> memory.
> This table is heavy on the writes in comparison to the reads, with a
> clustered primary key index with a fillfactor of 90%, and so I suspect the
> index might be reaching a point where it is, for lack of a better term
> "restructuring" due to running out of free space.
> --
> Message posted via http://www.sqlmonster.com
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 12, 2012
Index is being rebuilt
Usually when I create an index in the Query Analyzer, no message is
returned other than the command completed. But sometimes I get a
message than the index is being rebuilt. I have come to ignore this
message as it appears to not be an issue that needs research.
returned other than the command completed. But sometimes I get a
message than the index is being rebuilt. I have come to ignore this
message as it appears to not be an issue that needs research.
I am now doing more and more in the VB.NET environment using ADO.NET
to maniupulate SQL objects. (Using SQL7, btw.) When I need to create
an index in VB.NET code I use the ExecuteNonQuery method since I do
not expect to process a result set. However, if the call results in
the index being rebuilt the return message blows up the
ExecuteNonQuery method with a general network error.
As I see it I have two options. One is to use the ExecuteReader method
instead and simply ignore anything returned. The other is to tell SQL
to not tell me that an index is being rebuilt as I don't care to even
know that. I could trap the general network error and ignore it but I
think it would be dangerous to ignore all network errors.
Any suggestions?
Please respond here as email address is invalid to avoid spam.Zack Sessions (zcsessions@.visionair.com) writes:
> Usually when I create an index in the Query Analyzer, no message is
> returned other than the command completed. But sometimes I get a
> message than the index is being rebuilt. I have come to ignore this
> message as it appears to not be an issue that needs research.
You get that message when you create, change or drop on a clustered index
on a table that also has a non-clustered index. Since the clustered index
serves as a the pointer to the data pages, non-clustered indexes must
be rebuilt when the clustered index is changed.
I have to admit that I am a little mystified. I tried first to get
the message on SQL 2000 SP3, but it remained silent. I know that I have
seen this message quite a bit, but I have not worked much with SQL 7,
so I suspect this message might have been silenced with SP3 of SQL 2000.
> I am now doing more and more in the VB.NET environment using ADO.NET
> to maniupulate SQL objects. (Using SQL7, btw.) When I need to create
> an index in VB.NET code I use the ExecuteNonQuery method since I do
> not expect to process a result set. However, if the call results in
> the index being rebuilt the return message blows up the
> ExecuteNonQuery method with a general network error.
That should not happen. I have a little test app for ADO .Net, and I was
not able to reproduce the problem. Which version of the .Net framework
are you using? Have you set up an InfoMessages event handler?
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your reply.
Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94D7F2295295EYazorman@.127.0.0.1>...
> Zack Sessions (zcsessions@.visionair.com) writes:
> > Usually when I create an index in the Query Analyzer, no message is
> > returned other than the command completed. But sometimes I get a
> > message than the index is being rebuilt. I have come to ignore this
> > message as it appears to not be an issue that needs research.
> You get that message when you create, change or drop on a clustered index
> on a table that also has a non-clustered index. Since the clustered index
> serves as a the pointer to the data pages, non-clustered indexes must
> be rebuilt when the clustered index is changed.
Ahh, mystery cleared up. I was wondering what the answer to "why" was.
> I have to admit that I am a little mystified. I tried first to get
> the message on SQL 2000 SP3, but it remained silent. I know that I have
> seen this message quite a bit, but I have not worked much with SQL 7,
> so I suspect this message might have been silenced with SP3 of SQL 2000.
Hmm, if so all the more reason to upgrade, eh? :-)
> > I am now doing more and more in the VB.NET environment using ADO.NET
> > to maniupulate SQL objects. (Using SQL7, btw.) When I need to create
> > an index in VB.NET code I use the ExecuteNonQuery method since I do
> > not expect to process a result set. However, if the call results in
> > the index being rebuilt the return message blows up the
> > ExecuteNonQuery method with a general network error.
> That should not happen. I have a little test app for ADO .Net, and I was
> not able to reproduce the problem. Which version of the .Net framework
> are you using? Have you set up an InfoMessages event handler?
Workstation is XP with VB.NET using ADO.NET. Server is NT4 SP6a, SQL7
SP4. Workstation and server are at .NET Framework 1.1.
Don't understand InfoMessages event handler. Can't find InfoMessages
mentioned in SQL7 or .NET Books Online. I am using a Try/Catch, that's
how I discovered the exception neing raised was a "general network
error".|||Zack Sessions (zcsessions@.visionair.com) writes:
> Erland Sommarskog <sommar@.algonet.se> wrote in message
news:<Xns94D7F2295295EYazorman@.127.0.0.1>...
>> I have to admit that I am a little mystified. I tried first to get
>> the message on SQL 2000 SP3, but it remained silent. I know that I have
>> seen this message quite a bit, but I have not worked much with SQL 7,
>> so I suspect this message might have been silenced with SP3 of SQL 2000.
> Hmm, if so all the more reason to upgrade, eh? :-)
I would say that upgrading because SQL2000 does not produce a message
that wrecks .Net Framework is the most convincing the reason to upgrade
I've seen. :-)
> Workstation is XP with VB.NET using ADO.NET. Server is NT4 SP6a, SQL7
> SP4. Workstation and server are at .NET Framework 1.1.
I have WinXP, and .Net Framwork 1.1. I have no idea, whether NT/XP could
make the difference.
By the way, if you just submit a print statement with ExecuteNonQuery,
does that too give General Network Error?
> Don't understand InfoMessages event handler. Can't find InfoMessages
> mentioned in SQL7 or .NET Books Online. I am using a Try/Catch, that's
> how I discovered the exception neing raised was a "general network
> error".
It's in the SqlConnection class. And it's InfoMessage.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Subscribe to:
Posts (Atom)