index and key: dbcc

Showing posts with label dbcc. Show all posts

Friday, March 30, 2012

Index troubles

We're seeing funny things with SQL Server 2000 when dealing with indexes on
a customer table. When we issue:
DBCC reindex (cust)
we see an error Message 169: a column has been specified more than once in
the order by list.
We see the same error during Update Statistics.
We're using the Query analyzer - index tuning wizard to create indexes on
this table. The table has 12 indexes, and the field Cust_num is used in 7 of
12 indexes.
We tried to remove indexes one by one, we get same Message 169.
Any clues? Thanks!!> The table has 12 indexes,
WOW! That isn't the most I've ever seen, but it's certainly more than I'm
used to. Why is one column covered in seven different indices?
> We tried to remove indexes one by one, we get same Message 169.
Can you show the result of:
EXEC sp_help tablename
EXEC sp_helpindex tablename|||Here is the output from sp_helpindex
I think the hypotheticals are added by the Index wizard ... how do you get
rid of these?
--
Address nonclustered located on PRIMARY Address
CustID nonclustered unique located on PRIMARY CustID
CustNum nonclustered unique located on PRIMARY CustNum
hind_1682821057_24A_2A nonclustered hypothetical auto create located on
PRIMARY PriceCode CustNum
hind_1682821057_2A_24A_50A nonclustered hypothetical auto create located
on PRIMARY CustNum PriceCode AssignedLocation
hind_1682821057_2A_50A nonclustered hypothetical auto create located on
PRIMARY CustNum AssignedLocation
hind_1682821057_50A_24A_2A nonclustered hypothetical auto create located
on PRIMARY AssignedLocation PriceCode CustNum
hind_1682821057_50A_2A nonclustered hypothetical auto create located on
PRIMARY AssignedLocation CustNum
hind_c_1682821057_24A clustered hypothetical auto create located on
PRIMARY PriceCode
hind_c_1682821057_24A_2A clustered hypothetical auto create located on
PRIMARY PriceCode CustNum
hind_c_1682821057_2A clustered hypothetical auto create located on PRIMARY
CustNum
hind_c_1682821057_2A_24A clustered hypothetical auto create located on
PRIMARY CustNum PriceCode
Name nonclustered located on PRIMARY Name
tblCust10 nonclustered located on PRIMARY CustNum Name City Phone
tblCust13 nonclustered located on PRIMARY CustNum SalesComm
tblCust2 clustered located on PRIMARY State CustNum
tblCust23 nonclustered located on PRIMARY CustNum PriceCode
tblCust25 nonclustered located on PRIMARY CustNum Name City State
tblCust26 nonclustered located on PRIMARY PriceCode CustNum Name City
State
tblCust31 nonclustered located on PRIMARY AssignedLocation PriceCode
tblCust7 nonclustered located on PRIMARY SalesComm DateNewComm
tblCust9 nonclustered located on PRIMARY CustNum PriceIncrease
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#oAjuKxLFHA.3844@.TK2MSFTNGP14.phx.gbl...
> > The table has 12 indexes,
> WOW! That isn't the most I've ever seen, but it's certainly more than I'm
> used to. Why is one column covered in seven different indices?
> > We tried to remove indexes one by one, we get same Message 169.
> Can you show the result of:
> EXEC sp_help tablename
> EXEC sp_helpindex tablename
>|||DROP INDEX should do it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Dean J Garrett" <info@.amuletc.com> wrote in message news:%23Sxvb3xLFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Here is the output from sp_helpindex
> I think the hypotheticals are added by the Index wizard ... how do you get
> rid of these?
> --
> Address nonclustered located on PRIMARY Address
> CustID nonclustered unique located on PRIMARY CustID
> CustNum nonclustered unique located on PRIMARY CustNum
> hind_1682821057_24A_2A nonclustered hypothetical auto create located on
> PRIMARY PriceCode CustNum
> hind_1682821057_2A_24A_50A nonclustered hypothetical auto create located
> on PRIMARY CustNum PriceCode AssignedLocation
> hind_1682821057_2A_50A nonclustered hypothetical auto create located on
> PRIMARY CustNum AssignedLocation
> hind_1682821057_50A_24A_2A nonclustered hypothetical auto create located
> on PRIMARY AssignedLocation PriceCode CustNum
> hind_1682821057_50A_2A nonclustered hypothetical auto create located on
> PRIMARY AssignedLocation CustNum
> hind_c_1682821057_24A clustered hypothetical auto create located on
> PRIMARY PriceCode
> hind_c_1682821057_24A_2A clustered hypothetical auto create located on
> PRIMARY PriceCode CustNum
> hind_c_1682821057_2A clustered hypothetical auto create located on PRIMARY
> CustNum
> hind_c_1682821057_2A_24A clustered hypothetical auto create located on
> PRIMARY CustNum PriceCode
> Name nonclustered located on PRIMARY Name
> tblCust10 nonclustered located on PRIMARY CustNum Name City Phone
> tblCust13 nonclustered located on PRIMARY CustNum SalesComm
> tblCust2 clustered located on PRIMARY State CustNum
> tblCust23 nonclustered located on PRIMARY CustNum PriceCode
> tblCust25 nonclustered located on PRIMARY CustNum Name City State
> tblCust26 nonclustered located on PRIMARY PriceCode CustNum Name City
> State
> tblCust31 nonclustered located on PRIMARY AssignedLocation PriceCode
> tblCust7 nonclustered located on PRIMARY SalesComm DateNewComm
> tblCust9 nonclustered located on PRIMARY CustNum PriceIncrease
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:#oAjuKxLFHA.3844@.TK2MSFTNGP14.phx.gbl...
>> > The table has 12 indexes,
>> WOW! That isn't the most I've ever seen, but it's certainly more than I'm
>> used to. Why is one column covered in seven different indices?
>> > We tried to remove indexes one by one, we get same Message 169.
>> Can you show the result of:
>> EXEC sp_help tablename
>> EXEC sp_helpindex tablename
>>
>

Monday, March 26, 2012

index rebuilds

I thought there was a DBCC command that would rebuild all the indexes in a
database with one statement. Can anyone help please.> I thought there was a DBCC command that would rebuild all the indexes in a
> database with one statement. Can anyone help please.
DBCC DBREINDEX can rebuild all indexes for a single table. You can use a
cusros to loop trough all tables and dynamically prepare and execute the
DBCC command for each table. There is also a shortcut:
http://www.mssqlcity.com/FAQ/Devel/sp_msforeachtable.htm.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Before you spend the time doing this, think about why you're doing it. You
should read the whitepaper
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
which explain when and how to reduced fragmentation in indexes.
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:#J8ghKrtEHA.1008@.tk2msftngp13.phx.gbl...
> > I thought there was a DBCC command that would rebuild all the indexes in
a
> > database with one statement. Can anyone help please.
> DBCC DBREINDEX can rebuild all indexes for a single table. You can use a
> cusros to loop trough all tables and dynamically prepare and execute the
> DBCC command for each table. There is also a shortcut:
> http://www.mssqlcity.com/FAQ/Devel/sp_msforeachtable.htm.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>

index rebuilds

I thought there was a DBCC command that would rebuild all the indexes in a
database with one statement. Can anyone help please.
> I thought there was a DBCC command that would rebuild all the indexes in a
> database with one statement. Can anyone help please.
DBCC DBREINDEX can rebuild all indexes for a single table. You can use a
cusros to loop trough all tables and dynamically prepare and execute the
DBCC command for each table. There is also a shortcut:
http://www.mssqlcity.com/FAQ/Devel/s...eachtable.htm.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Before you spend the time doing this, think about why you're doing it. You
should read the whitepaper
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
which explain when and how to reduced fragmentation in indexes.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:#J8ghKrtEHA.1008@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
a
> DBCC DBREINDEX can rebuild all indexes for a single table. You can use a
> cusros to loop trough all tables and dynamically prepare and execute the
> DBCC command for each table. There is also a shortcut:
> http://www.mssqlcity.com/FAQ/Devel/s...eachtable.htm.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
sql

index rebuilds

I thought there was a DBCC command that would rebuild all the indexes in a
database with one statement. Can anyone help please.> I thought there was a DBCC command that would rebuild all the indexes in a
> database with one statement. Can anyone help please.
DBCC DBREINDEX can rebuild all indexes for a single table. You can use a
cusros to loop trough all tables and dynamically prepare and execute the
DBCC command for each table. There is also a shortcut:
http://www.mssqlcity.com/FAQ/Devel/...reachtable.htm.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Before you spend the time doing this, think about why you're doing it. You
should read the whitepaper
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
which explain when and how to reduced fragmentation in indexes.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:#J8ghKrtEHA.1008@.tk2msftngp13.phx.gbl...
a[vbcol=seagreen]
> DBCC DBREINDEX can rebuild all indexes for a single table. You can use a
> cusros to loop trough all tables and dynamically prepare and execute the
> DBCC command for each table. There is also a shortcut:
> http://www.mssqlcity.com/FAQ/Devel/...reachtable.htm.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>

Index Rebuilding

Hi,
I am going to Rebuild the index on tables of production database.
I will use 'dbcc dbreindex' command to do this index rebuilding.
Now, I want to know what are the Prerequisites should i take to do
this job.
And also what are the Precautions shuld i take.
Please guide me in this regard.
Thanks & Regards,
Sajid.DBREINDEX rebuilds indexes completely and is resource intensive. This may
cause blocking and performance issues so you should run it during minimal
activity. Also, DBREINDEX is fully-logged regardless of your database
recovery model so make sure you have sufficient log space available.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1149766763.732814.141490@.c74g2000cwc.googlegroups.com...
> Hi,
> I am going to Rebuild the index on tables of production database.
> I will use 'dbcc dbreindex' command to do this index rebuilding.
> Now, I want to know what are the Prerequisites should i take to do
> this job.
> And also what are the Precautions shuld i take.
> Please guide me in this regard.
>
> Thanks & Regards,
> Sajid.
>|||Dan Guzman wrote:
> DBREINDEX rebuilds indexes completely and is resource intensive. This may
> cause blocking and performance issues so you should run it during minimal
> activity. Also, DBREINDEX is fully-logged regardless of your database
> recovery model so make sure you have sufficient log space available.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csajid@.gmail.com> wrote in message
> news:1149766763.732814.141490@.c74g2000cwc.googlegroups.com...
> > Hi,
> >
> > I am going to Rebuild the index on tables of production database.
> >
> > I will use 'dbcc dbreindex' command to do this index rebuilding.
> >
> > Now, I want to know what are the Prerequisites should i take to do
> > this job.
> >
> > And also what are the Precautions shuld i take.
> >
> > Please guide me in this regard.
> >
> >
> > Thanks & Regards,
> > Sajid.
> >
It requires downtime, since it locks tables and if you are rebuilding
cluster index then you need to about 1.2 times table space for
rebuilding index or nonclustered index then about 1.2 time space than
size of non clustered index.
Log backup size also increases , so if you do not need log backup
during this time , change recovery model to simple. After completing
activity change recovery model to full again and take a full backup.
In SQL Server 2005 you can rebuild index dynamically , but please check
BOL for it.
Regards
Amish Shah.|||Hi,
Thanks Guys for your quick reply.
Thanks & Regards,
Sajid N. Chhapekar.
amish wrote:
> Dan Guzman wrote:
> > DBREINDEX rebuilds indexes completely and is resource intensive. This may
> > cause blocking and performance issues so you should run it during minimal
> > activity. Also, DBREINDEX is fully-logged regardless of your database
> > recovery model so make sure you have sufficient log space available.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > <csajid@.gmail.com> wrote in message
> > news:1149766763.732814.141490@.c74g2000cwc.googlegroups.com...
> > > Hi,
> > >
> > > I am going to Rebuild the index on tables of production database.
> > >
> > > I will use 'dbcc dbreindex' command to do this index rebuilding.
> > >
> > > Now, I want to know what are the Prerequisites should i take to do
> > > this job.
> > >
> > > And also what are the Precautions shuld i take.
> > >
> > > Please guide me in this regard.
> > >
> > >
> > > Thanks & Regards,
> > > Sajid.
> > >
> It requires downtime, since it locks tables and if you are rebuilding
> cluster index then you need to about 1.2 times table space for
> rebuilding index or nonclustered index then about 1.2 time space than
> size of non clustered index.
> Log backup size also increases , so if you do not need log backup
> during this time , change recovery model to simple. After completing
> activity change recovery model to full again and take a full backup.
> In SQL Server 2005 you can rebuild index dynamically , but please check
> BOL for it.
> Regards
> Amish Shah.

Index Rebuilding

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

SQL2K EE SP3 , WIN 2K EE SP2
We have a 27 Gb database that continually has index / performance problems.
We do a DBCC DBREINDEX every night, and sp_updatestats every hour during the
day. The database has a lot of read, write and delete activity, usually inv
olving 1000's of records at a time and more than 100,000 records a few time
s a day.
Because of slow query response we started doing the DBREINDEX every night an
d it improved performance.
Than we started getting the following errors:
Cannot retrieve row from page (64:2562414) by RID because the slotid (742594
184) is not valid.
This was solved after the nightly DBREINDEX, but since we cannot DBREINDEX d
uring the day while users are in the system we started running sp_updatestat
s every hour and this fixes it as well.
Now we have had a few times were a query returns no records even though the
data exists. The last time we had the problem the following query was being
run:
SELECT *
FROM dbo.MV_Detail
WHERE Tailor_ID = 359884
AND Circ_ID = 81670
If we take out the AND Circ_ID = 81670 statement and do an order by on the t
able we see that we have data that match the criteria, or if we add a 3rd cr
iteria with a table join then we see the data.
SELECT *
FROM dbo.mv_Detail a,
(select distinct geography from tempdb.dbo.mv_Geo40499999) b
where a.Tailor_ID = 359884 AND a.Circ_ID = 81670 and b.geography = a.geograp
hy
I ran sp_updatestats on the database and we still did not get any records fr
om the first query. After we ran DBREINDEX the first query started to return
records again.
I have included the table create script for the main table were we have prob
lems with. This table is about 1/3 of the database size and contains almost
60 million records and uses about 5Gb in data space and 5.5 Gb in index spac
e.
Any ideas on how to improve this situation would be greatly appreciated. We
changed the fill factor on the indexes from 80 to 90 to conserve space, but
have added disk space since then. Could reducing the fill factor help with t
hese types of problems?
CREATE TABLE [dbo].[MV_Detail] (
[Circ_ID] [int] NOT NULL ,
[Tailor_ID] [int] NOT NULL ,
[geography] [varchar] (10) COLLATE SQL_Latin1_General_CP437_CI_AS NOT NULL ,
[circtype] [int] NOT NULL ,
[UseAny] [bit] NOT NULL ,
[Monday_Use] [bit] NOT NULL ,
[Tuesday_Use] [bit] NOT NULL ,
[Wednesday_Use] [bit] NOT NULL ,
[Thursday_Use] [bit] NOT NULL ,
[Friday_Use] [bit] NOT NULL ,
[Saturday_Use] [bit] NOT NULL ,
[Sunday_Use] [bit] NOT NULL ,
[monday] [int] NOT NULL ,
[tuesday] [int] NOT NULL ,
[wednesday] [int] NOT NULL ,
[thursday] [int] NOT NULL ,
[friday] [int] NOT NULL ,
[saturday] [int] NOT NULL ,
[sunday] [int] NOT NULL ,
[Date_Changed] [datetime] NULL ,
[Changed_By] [char] (8) COLLATE SQL_Latin1_General_CP437_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MV_Detail] ADD
CONSTRAINT [DF_MV_Detail_UseAny] DEFAULT (0) FOR [UseAny],
CONSTRAINT [DF_MV_Detail_Monday_Use] DEFAULT (0) FOR [Monday_Use],
CONSTRAINT [DF_MV_Detail_Tuesday_Use] DEFAULT (0) FOR [Tuesday_Use],
CONSTRAINT [DF_MV_Detail_Wednesday_Use] DEFAULT (0) FOR [Wednesday_Use],
CONSTRAINT [DF_MV_Detail_Thursday_Use] DEFAULT (0) FOR [Thursday_Use],
CONSTRAINT [DF_MV_Detail_Friday_Use] DEFAULT (0) FOR [Friday_Use],
CONSTRAINT [DF_MV_Detail_Saturday_Use] DEFAULT (0) FOR [Saturday_Use],
CONSTRAINT [DF_MV_Detail_Sunday_Use] DEFAULT (0) FOR [Sunday_Use]
GO
CREATE INDEX [IX_MV_Detail] ON [dbo].[MV_Detail]([Tailor_ID], [geography]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE UNIQUE INDEX [CircTailorGeoType] ON [dbo].[MV_Detail]([Circ_ID], [Tailor_ID], [geography], [circtype]) WITH FILLFACTOR =
90 ON [PRIMARY]
GO
CREATE INDEX [IX_Circ_Trigger] ON [dbo].[MV_Detail]([Circ_ID], [Tailor_ID], [circtype]) WITH FILLF
ACTOR = 90 ON [PRIMARY]
GOMike,
It sounds like data corruption. See if this article helps:
http://support.microsoft.com/defaul...kb;en-us;826433
SK
"Mike" <Mike@.Comcast.net> wrote in message news:u$1HcAD5DHA.564@.TK2MSFTNGP10
.phx.gbl...
SQL2K EE SP3 , WIN 2K EE SP2
We have a 27 Gb database that continually has index / performance problems.
We do a DBCC DBREINDEX every night, and sp_updatestats every hour during the
day. The database has a lot of read, write and delete activity, usually inv
olving 1000's of records at a time and more than 100,000 records a few time
s a day.
Because of slow query response we started doing the DBREINDEX every night an
d it improved performance.
Than we started getting the following errors:
Cannot retrieve row from page (64:2562414) by RID because the slotid (742594
184) is not valid.
This was solved after the nightly DBREINDEX, but since we cannot DBREINDEX d
uring the day while users are in the system we started running sp_updatestat
s every hour and this fixes it as well.
Now we have had a few times were a query returns no records even though the
data exists. The last time we had the problem the following query was being
run:
SELECT *
FROM dbo.MV_Detail
WHERE Tailor_ID = 359884
AND Circ_ID = 81670
If we take out the AND Circ_ID = 81670 statement and do an order by on the t
able we see that we have data that match the criteria, or if we add a 3rd cr
iteria with a table join then we see the data.
SELECT *
FROM dbo.mv_Detail a,
(select distinct geography from tempdb.dbo.mv_Geo40499999) b
where a.Tailor_ID = 359884 AND a.Circ_ID = 81670 and b.geography = a.geograp
hy
I ran sp_updatestats on the database and we still did not get any records fr
om the first query. After we ran DBREINDEX the first query started to return
records again.
I have included the table create script for the main table were we have prob
lems with. This table is about 1/3 of the database size and contains almost
60 million records and uses about 5Gb in data space and 5.5 Gb in index spac
e.
Any ideas on how to improve this situation would be greatly appreciated. We
changed the fill factor on the indexes from 80 to 90 to conserve space, but
have added disk space since then. Could reducing the fill factor help with t
hese types of problems?
CREATE TABLE [dbo].[MV_Detail] (
[Circ_ID] [int] NOT NULL ,
[Tailor_ID] [int] NOT NULL ,
[geography] [varchar] (10) COLLATE SQL_Latin1_General_CP437_CI_AS NOT NULL ,
[circtype] [int] NOT NULL ,
[UseAny] [bit] NOT NULL ,
[Monday_Use] [bit] NOT NULL ,
[Tuesday_Use] [bit] NOT NULL ,
[Wednesday_Use] [bit] NOT NULL ,
[Thursday_Use] [bit] NOT NULL ,
[Friday_Use] [bit] NOT NULL ,
[Saturday_Use] [bit] NOT NULL ,
[Sunday_Use] [bit] NOT NULL ,
[monday] [int] NOT NULL ,
[tuesday] [int] NOT NULL ,
[wednesday] [int] NOT NULL ,
[thursday] [int] NOT NULL ,
[friday] [int] NOT NULL ,
[saturday] [int] NOT NULL ,
[sunday] [int] NOT NULL ,
[Date_Changed] [datetime] NULL ,
[Changed_By] [char] (8) COLLATE SQL_Latin1_General_CP437_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MV_Detail] ADD
CONSTRAINT [DF_MV_Detail_UseAny] DEFAULT (0) FOR [UseAny],
CONSTRAINT [DF_MV_Detail_Monday_Use] DEFAULT (0) FOR [Monday_Use],
CONSTRAINT [DF_MV_Detail_Tuesday_Use] DEFAULT (0) FOR [Tuesday_Use],
CONSTRAINT [DF_MV_Detail_Wednesday_Use] DEFAULT (0) FOR [Wednesday_Use],
CONSTRAINT [DF_MV_Detail_Thursday_Use] DEFAULT (0) FOR [Thursday_Use],
CONSTRAINT [DF_MV_Detail_Friday_Use] DEFAULT (0) FOR [Friday_Use],
CONSTRAINT [DF_MV_Detail_Saturday_Use] DEFAULT (0) FOR [Saturday_Use],
CONSTRAINT [DF_MV_Detail_Sunday_Use] DEFAULT (0) FOR [Sunday_Use]
GO
CREATE INDEX [IX_MV_Detail] ON [dbo].[MV_Detail]([Tailor_ID], [geography]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE UNIQUE INDEX [CircTailorGeoType] ON [dbo].[MV_Detail]([Circ_ID], [Tailor_ID], [geography], [circtype]) WITH FILLFACTOR =
90 ON [PRIMARY]
GO
CREATE INDEX [IX_Circ_Trigger] ON [dbo].[MV_Detail]([Circ_ID], [Tailor_ID], [circtype]) WITH FILLF
ACTOR = 90 ON [PRIMARY]
GO|||If it is a hardware issue, would it be capturing it in error logs anywhere?
Mike
"Steve Kass" <skass@.drew.edu> wrote in message news:OEbuj8E5DHA.1804@.TK2MSFT
NGP12.phx.gbl...
Mike,
It sounds like data corruption. See if this article helps:
http://support.microsoft.com/defaul...kb;en-us;826433
SK
"Mike" <Mike@.Comcast.net> wrote in message news:u$1HcAD5DHA.564@.TK2MSFTNGP10
.phx.gbl...
SQL2K EE SP3 , WIN 2K EE SP2
We have a 27 Gb database that continually has index / performance problems.
We do a DBCC DBREINDEX every night, and sp_updatestats every hour during the
day. The database has a lot of read, write and delete activity, usually inv
olving 1000's of records at a time and more than 100,000 records a few time
s a day.
Because of slow query response we started doing the DBREINDEX every night an
d it improved performance.
Than we started getting the following errors:
Cannot retrieve row from page (64:2562414) by RID because the slotid (742594
184) is not valid.
This was solved after the nightly DBREINDEX, but since we cannot DBREINDEX d
uring the day while users are in the system we started running sp_updatestat
s every hour and this fixes it as well.
Now we have had a few times were a query returns no records even though the
data exists. The last time we had the problem the following query was being
run:
SELECT *
FROM dbo.MV_Detail
WHERE Tailor_ID = 359884
AND Circ_ID = 81670
If we take out the AND Circ_ID = 81670 statement and do an order by on the t
able we see that we have data that match the criteria, or if we add a 3rd cr
iteria with a table join then we see the data.
SELECT *
FROM dbo.mv_Detail a,
(select distinct geography from tempdb.dbo.mv_Geo40499999) b
where a.Tailor_ID = 359884 AND a.Circ_ID = 81670 and b.geography = a.geograp
hy
I ran sp_updatestats on the database and we still did not get any records fr
om the first query. After we ran DBREINDEX the first query started to return
records again.
I have included the table create script for the main table were we have prob
lems with. This table is about 1/3 of the database size and contains almost
60 million records and uses about 5Gb in data space and 5.5 Gb in index spac
e.
Any ideas on how to improve this situation would be greatly appreciated. We
changed the fill factor on the indexes from 80 to 90 to conserve space, but
have added disk space since then. Could reducing the fill factor help with t
hese types of problems?
CREATE TABLE [dbo].[MV_Detail] (
[Circ_ID] [int] NOT NULL ,
[Tailor_ID] [int] NOT NULL ,
[geography] [varchar] (10) COLLATE SQL_Latin1_General_CP437_CI_AS NOT NULL ,
[circtype] [int] NOT NULL ,
[UseAny] [bit] NOT NULL ,
[Monday_Use] [bit] NOT NULL ,
[Tuesday_Use] [bit] NOT NULL ,
[Wednesday_Use] [bit] NOT NULL ,
[Thursday_Use] [bit] NOT NULL ,
[Friday_Use] [bit] NOT NULL ,
[Saturday_Use] [bit] NOT NULL ,
[Sunday_Use] [bit] NOT NULL ,
[monday] [int] NOT NULL ,
[tuesday] [int] NOT NULL ,
[wednesday] [int] NOT NULL ,
[thursday] [int] NOT NULL ,
[friday] [int] NOT NULL ,
[saturday] [int] NOT NULL ,
[sunday] [int] NOT NULL ,
[Date_Changed] [datetime] NULL ,
[Changed_By] [char] (8) COLLATE SQL_Latin1_General_CP437_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MV_Detail] ADD
CONSTRAINT [DF_MV_Detail_UseAny] DEFAULT (0) FOR [UseAny],
CONSTRAINT [DF_MV_Detail_Monday_Use] DEFAULT (0) FOR [Monday_Use],
CONSTRAINT [DF_MV_Detail_Tuesday_Use] DEFAULT (0) FOR [Tuesday_Use],
CONSTRAINT [DF_MV_Detail_Wednesday_Use] DEFAULT (0) FOR [Wednesday_Use],
CONSTRAINT [DF_MV_Detail_Thursday_Use] DEFAULT (0) FOR [Thursday_Use],
CONSTRAINT [DF_MV_Detail_Friday_Use] DEFAULT (0) FOR [Friday_Use],
CONSTRAINT [DF_MV_Detail_Saturday_Use] DEFAULT (0) FOR [Saturday_Use],
CONSTRAINT [DF_MV_Detail_Sunday_Use] DEFAULT (0) FOR [Sunday_Use]
GO
CREATE INDEX [IX_MV_Detail] ON [dbo].[MV_Detail]([Tailor_ID], [geography]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE UNIQUE INDEX [CircTailorGeoType] ON [dbo].[MV_Detail]([Circ_ID], [Tailor_ID], [geography], [circtype]) WITH FILLFACTOR =
90 ON [PRIMARY]
GO
CREATE INDEX [IX_Circ_Trigger] ON [dbo].[MV_Detail]([Circ_ID], [Tailor_ID], [circtype]) WITH FILLF
ACTOR = 90 ON [PRIMARY]
GO

Monday, March 19, 2012

Index Monitoring

I monitor the index fragmentation through dbcc showcontig command , i get result as
Pages Scanned
Extents Scanned
Extent Switches
Avg. Pages per Extent
Scan Density [Best Count:Actual Count]
Logical Scan Fragmentation
Extent Scan Fragmentation
Avg. Bytes Free per Page
Avg. Page Density (full).

But i want to know can we get value of 'extent switches' and logical scan fragmentation' through system tables , because we know that all database details are updated in system tables.As you have posted a question in the article section it ts being moved to SQL Server Forum.

Monday, March 12, 2012

Index Maint DBCC assistance.

I am working with multiple servers, and numerous databases. I have
some large databases, some tables with 100's of millions of rows.
Some of the DB's are 24/7 so recreating the indexes is not really an
option. I am trying to come up with a "smart" index plan. I have
seen many examples of this, and I have one that does seem to work,
except that the DBCC showcontig seems to take forever! I am using a
variation on the following. The DBCC Showcontig seems to lock the
table, is this true?
Is there a faster way to get the DBCC fragmentation information?
Is it really worth the work identifying and defragmenting those
indexes that fall below a threshold or should I just defrag every
index?
Any other help on a "smart" maint plan for index maint would be
appreciated!
OS's : w2k Adv Server fully patched, 2003 Server Fully Patched
I am on SQL 2000, some at SP3a / SP4.
Sample of code below:
http://msdn2.microsoft.com/en-us/library/ms175008.aspx
-- Declare variables
SET NOCOUNT ON;
DECLARE @.tablename varchar(128);
DECLARE @.execstr varchar(255);
DECLARE @.objectid int;
DECLARE @.indexid int;
DECLARE @.frag decimal;
DECLARE @.maxfrag decimal;
-- Decide on the maximum fragmentation to allow for.
SELECT @.maxfrag = 30.0;
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
-- Create the table.
CREATE TABLE #fraglist (
ObjectName char(255),
ObjectId int,
IndexName char(255),
IndexId int,
Lvl int,
CountPages int,
CountRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecCount int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal);
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @.tablename;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
FETCH NEXT
FROM tables
INTO @.tablename;
END;
-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
-- Open the cursor.
OPEN indexes;
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
' + RTRIM(@.indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@.frag)) + '%';
SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
' + RTRIM(@.indexid) + ')';
EXEC (@.execstr);
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
END;
-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
-- Delete the temporary table.
DROP TABLE #fraglist;
GODo you have a test environment where you can load a copy of prod? If so,
generate the defrag script there but execute it against prod. This way, you
won't be locking up the table in prod with a share lock by running DBCC
SHOWCONTIG.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"David Hay" <david.hay@.gmail.com> wrote in message
news:368f7322-7f34-40c7-8e5e-cd32f1af00aa@.w56g2000hsf.googlegroups.com...
I am working with multiple servers, and numerous databases. I have
some large databases, some tables with 100's of millions of rows.
Some of the DB's are 24/7 so recreating the indexes is not really an
option. I am trying to come up with a "smart" index plan. I have
seen many examples of this, and I have one that does seem to work,
except that the DBCC showcontig seems to take forever! I am using a
variation on the following. The DBCC Showcontig seems to lock the
table, is this true?
Is there a faster way to get the DBCC fragmentation information?
Is it really worth the work identifying and defragmenting those
indexes that fall below a threshold or should I just defrag every
index?
Any other help on a "smart" maint plan for index maint would be
appreciated!
OS's : w2k Adv Server fully patched, 2003 Server Fully Patched
I am on SQL 2000, some at SP3a / SP4.
Sample of code below:
http://msdn2.microsoft.com/en-us/library/ms175008.aspx
-- Declare variables
SET NOCOUNT ON;
DECLARE @.tablename varchar(128);
DECLARE @.execstr varchar(255);
DECLARE @.objectid int;
DECLARE @.indexid int;
DECLARE @.frag decimal;
DECLARE @.maxfrag decimal;
-- Decide on the maximum fragmentation to allow for.
SELECT @.maxfrag = 30.0;
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
-- Create the table.
CREATE TABLE #fraglist (
ObjectName char(255),
ObjectId int,
IndexName char(255),
IndexId int,
Lvl int,
CountPages int,
CountRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecCount int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal);
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @.tablename;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
FETCH NEXT
FROM tables
INTO @.tablename;
END;
-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
-- Open the cursor.
OPEN indexes;
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
' + RTRIM(@.indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@.frag)) + '%';
SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
' + RTRIM(@.indexid) + ')';
EXEC (@.execstr);
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
END;
-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
-- Delete the temporary table.
DROP TABLE #fraglist;
GO|||The way to really solve this is to upgrade to 2005 as the equivalent of
Showcontig does not block like in 2000. But the code you have now seems to
be the fastest and lightest way to do this in 2000. I never recommend on
larger dbs to reindex blindly so your approach is what I would recommend
anyway. You can keep a history of the fragmentation by index and use that to
determine which to defrag. Chances are if the usage is roughly the same week
to week the indexes will be roughly the same in terms of fragmentation. So
collect samples for a few weeks and base your rebuilding off of those
numbers for x many future weeks.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"David Hay" <david.hay@.gmail.com> wrote in message
news:368f7322-7f34-40c7-8e5e-cd32f1af00aa@.w56g2000hsf.googlegroups.com...
>I am working with multiple servers, and numerous databases. I have
> some large databases, some tables with 100's of millions of rows.
> Some of the DB's are 24/7 so recreating the indexes is not really an
> option. I am trying to come up with a "smart" index plan. I have
> seen many examples of this, and I have one that does seem to work,
> except that the DBCC showcontig seems to take forever! I am using a
> variation on the following. The DBCC Showcontig seems to lock the
> table, is this true?
> Is there a faster way to get the DBCC fragmentation information?
> Is it really worth the work identifying and defragmenting those
> indexes that fall below a threshold or should I just defrag every
> index?
> Any other help on a "smart" maint plan for index maint would be
> appreciated!
> OS's : w2k Adv Server fully patched, 2003 Server Fully Patched
> I am on SQL 2000, some at SP3a / SP4.
> Sample of code below:
> http://msdn2.microsoft.com/en-us/library/ms175008.aspx
> -- Declare variables
> SET NOCOUNT ON;
> DECLARE @.tablename varchar(128);
> DECLARE @.execstr varchar(255);
> DECLARE @.objectid int;
> DECLARE @.indexid int;
> DECLARE @.frag decimal;
> DECLARE @.maxfrag decimal;
> -- Decide on the maximum fragmentation to allow for.
> SELECT @.maxfrag = 30.0;
> -- Declare a cursor.
> DECLARE tables CURSOR FOR
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE';
> -- Create the table.
> CREATE TABLE #fraglist (
> ObjectName char(255),
> ObjectId int,
> IndexName char(255),
> IndexId int,
> Lvl int,
> CountPages int,
> CountRows int,
> MinRecSize int,
> MaxRecSize int,
> AvgRecSize int,
> ForRecCount int,
> Extents int,
> ExtentSwitches int,
> AvgFreeBytes int,
> AvgPageDensity int,
> ScanDensity decimal,
> BestCount int,
> ActualCount int,
> LogicalFrag decimal,
> ExtentFrag decimal);
> -- Open the cursor.
> OPEN tables;
> -- Loop through all the tables in the database.
> FETCH NEXT
> FROM tables
> INTO @.tablename;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN;
> -- Do the showcontig of all indexes of the table
> INSERT INTO #fraglist
> EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
> FETCH NEXT
> FROM tables
> INTO @.tablename;
> END;
> -- Close and deallocate the cursor.
> CLOSE tables;
> DEALLOCATE tables;
> -- Declare the cursor for the list of indexes to be defragged.
> DECLARE indexes CURSOR FOR
> SELECT ObjectName, ObjectId, IndexId, LogicalFrag
> FROM #fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
> -- Open the cursor.
> OPEN indexes;
> -- Loop through the indexes.
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN;
> PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
> ' + RTRIM(@.indexid) + ') - fragmentation currently '
> + RTRIM(CONVERT(varchar(15),@.frag)) + '%';
> SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
> ' + RTRIM(@.indexid) + ')';
> EXEC (@.execstr);
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag;
> END;
> -- Close and deallocate the cursor.
> CLOSE indexes;
> DEALLOCATE indexes;
> -- Delete the temporary table.
> DROP TABLE #fraglist;
> GO|||And perhaps this "history of the index fragmentation" may show that some
indexes are fragmented too soon and maybe a new fill factor will be needed.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Andrew J. Kelly" wrote:
> The way to really solve this is to upgrade to 2005 as the equivalent of
> Showcontig does not block like in 2000. But the code you have now seems to
> be the fastest and lightest way to do this in 2000. I never recommend on
> larger dbs to reindex blindly so your approach is what I would recommend
> anyway. You can keep a history of the fragmentation by index and use that to
> determine which to defrag. Chances are if the usage is roughly the same week
> to week the indexes will be roughly the same in terms of fragmentation. So
> collect samples for a few weeks and base your rebuilding off of those
> numbers for x many future weeks.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "David Hay" <david.hay@.gmail.com> wrote in message
> news:368f7322-7f34-40c7-8e5e-cd32f1af00aa@.w56g2000hsf.googlegroups.com...
> >I am working with multiple servers, and numerous databases. I have
> > some large databases, some tables with 100's of millions of rows.
> > Some of the DB's are 24/7 so recreating the indexes is not really an
> > option. I am trying to come up with a "smart" index plan. I have
> > seen many examples of this, and I have one that does seem to work,
> > except that the DBCC showcontig seems to take forever! I am using a
> > variation on the following. The DBCC Showcontig seems to lock the
> > table, is this true?
> >
> > Is there a faster way to get the DBCC fragmentation information?
> >
> > Is it really worth the work identifying and defragmenting those
> > indexes that fall below a threshold or should I just defrag every
> > index?
> >
> > Any other help on a "smart" maint plan for index maint would be
> > appreciated!
> >
> > OS's : w2k Adv Server fully patched, 2003 Server Fully Patched
> >
> > I am on SQL 2000, some at SP3a / SP4.
> >
> > Sample of code below:
> >
> > http://msdn2.microsoft.com/en-us/library/ms175008.aspx
> >
> > -- Declare variables
> > SET NOCOUNT ON;
> > DECLARE @.tablename varchar(128);
> > DECLARE @.execstr varchar(255);
> > DECLARE @.objectid int;
> > DECLARE @.indexid int;
> > DECLARE @.frag decimal;
> > DECLARE @.maxfrag decimal;
> >
> > -- Decide on the maximum fragmentation to allow for.
> > SELECT @.maxfrag = 30.0;
> >
> > -- Declare a cursor.
> > DECLARE tables CURSOR FOR
> > SELECT TABLE_NAME
> > FROM INFORMATION_SCHEMA.TABLES
> > WHERE TABLE_TYPE = 'BASE TABLE';
> >
> > -- Create the table.
> > CREATE TABLE #fraglist (
> > ObjectName char(255),
> > ObjectId int,
> > IndexName char(255),
> > IndexId int,
> > Lvl int,
> > CountPages int,
> > CountRows int,
> > MinRecSize int,
> > MaxRecSize int,
> > AvgRecSize int,
> > ForRecCount int,
> > Extents int,
> > ExtentSwitches int,
> > AvgFreeBytes int,
> > AvgPageDensity int,
> > ScanDensity decimal,
> > BestCount int,
> > ActualCount int,
> > LogicalFrag decimal,
> > ExtentFrag decimal);
> >
> > -- Open the cursor.
> > OPEN tables;
> >
> > -- Loop through all the tables in the database.
> > FETCH NEXT
> > FROM tables
> > INTO @.tablename;
> >
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN;
> > -- Do the showcontig of all indexes of the table
> > INSERT INTO #fraglist
> > EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
> > WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
> > FETCH NEXT
> > FROM tables
> > INTO @.tablename;
> > END;
> >
> > -- Close and deallocate the cursor.
> > CLOSE tables;
> > DEALLOCATE tables;
> >
> > -- Declare the cursor for the list of indexes to be defragged.
> > DECLARE indexes CURSOR FOR
> > SELECT ObjectName, ObjectId, IndexId, LogicalFrag
> > FROM #fraglist
> > WHERE LogicalFrag >= @.maxfrag
> > AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
> >
> > -- Open the cursor.
> > OPEN indexes;
> >
> > -- Loop through the indexes.
> > FETCH NEXT
> > FROM indexes
> > INTO @.tablename, @.objectid, @.indexid, @.frag;
> >
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN;
> > PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
> > ' + RTRIM(@.indexid) + ') - fragmentation currently '
> > + RTRIM(CONVERT(varchar(15),@.frag)) + '%';
> > SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
> > ' + RTRIM(@.indexid) + ')';
> > EXEC (@.execstr);
> >
> > FETCH NEXT
> > FROM indexes
> > INTO @.tablename, @.objectid, @.indexid, @.frag;
> > END;
> >
> > -- Close and deallocate the cursor.
> > CLOSE indexes;
> > DEALLOCATE indexes;
> >
> > -- Delete the temporary table.
> > DROP TABLE #fraglist;
> > GO
>|||Yes good point Ben, I should have noted that as well.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:93BADA3C-FD11-414E-9044-21E3D63B622B@.microsoft.com...
> And perhaps this "history of the index fragmentation" may show that some
> indexes are fragmented too soon and maybe a new fill factor will be
> needed.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Andrew J. Kelly" wrote:
>> The way to really solve this is to upgrade to 2005 as the equivalent of
>> Showcontig does not block like in 2000. But the code you have now seems
>> to
>> be the fastest and lightest way to do this in 2000. I never recommend on
>> larger dbs to reindex blindly so your approach is what I would recommend
>> anyway. You can keep a history of the fragmentation by index and use that
>> to
>> determine which to defrag. Chances are if the usage is roughly the same
>> week
>> to week the indexes will be roughly the same in terms of fragmentation.
>> So
>> collect samples for a few weeks and base your rebuilding off of those
>> numbers for x many future weeks.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "David Hay" <david.hay@.gmail.com> wrote in message
>> news:368f7322-7f34-40c7-8e5e-cd32f1af00aa@.w56g2000hsf.googlegroups.com...
>> >I am working with multiple servers, and numerous databases. I have
>> > some large databases, some tables with 100's of millions of rows.
>> > Some of the DB's are 24/7 so recreating the indexes is not really an
>> > option. I am trying to come up with a "smart" index plan. I have
>> > seen many examples of this, and I have one that does seem to work,
>> > except that the DBCC showcontig seems to take forever! I am using a
>> > variation on the following. The DBCC Showcontig seems to lock the
>> > table, is this true?
>> >
>> > Is there a faster way to get the DBCC fragmentation information?
>> >
>> > Is it really worth the work identifying and defragmenting those
>> > indexes that fall below a threshold or should I just defrag every
>> > index?
>> >
>> > Any other help on a "smart" maint plan for index maint would be
>> > appreciated!
>> >
>> > OS's : w2k Adv Server fully patched, 2003 Server Fully Patched
>> >
>> > I am on SQL 2000, some at SP3a / SP4.
>> >
>> > Sample of code below:
>> >
>> > http://msdn2.microsoft.com/en-us/library/ms175008.aspx
>> >
>> > -- Declare variables
>> > SET NOCOUNT ON;
>> > DECLARE @.tablename varchar(128);
>> > DECLARE @.execstr varchar(255);
>> > DECLARE @.objectid int;
>> > DECLARE @.indexid int;
>> > DECLARE @.frag decimal;
>> > DECLARE @.maxfrag decimal;
>> >
>> > -- Decide on the maximum fragmentation to allow for.
>> > SELECT @.maxfrag = 30.0;
>> >
>> > -- Declare a cursor.
>> > DECLARE tables CURSOR FOR
>> > SELECT TABLE_NAME
>> > FROM INFORMATION_SCHEMA.TABLES
>> > WHERE TABLE_TYPE = 'BASE TABLE';
>> >
>> > -- Create the table.
>> > CREATE TABLE #fraglist (
>> > ObjectName char(255),
>> > ObjectId int,
>> > IndexName char(255),
>> > IndexId int,
>> > Lvl int,
>> > CountPages int,
>> > CountRows int,
>> > MinRecSize int,
>> > MaxRecSize int,
>> > AvgRecSize int,
>> > ForRecCount int,
>> > Extents int,
>> > ExtentSwitches int,
>> > AvgFreeBytes int,
>> > AvgPageDensity int,
>> > ScanDensity decimal,
>> > BestCount int,
>> > ActualCount int,
>> > LogicalFrag decimal,
>> > ExtentFrag decimal);
>> >
>> > -- Open the cursor.
>> > OPEN tables;
>> >
>> > -- Loop through all the tables in the database.
>> > FETCH NEXT
>> > FROM tables
>> > INTO @.tablename;
>> >
>> > WHILE @.@.FETCH_STATUS = 0
>> > BEGIN;
>> > -- Do the showcontig of all indexes of the table
>> > INSERT INTO #fraglist
>> > EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
>> > WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
>> > FETCH NEXT
>> > FROM tables
>> > INTO @.tablename;
>> > END;
>> >
>> > -- Close and deallocate the cursor.
>> > CLOSE tables;
>> > DEALLOCATE tables;
>> >
>> > -- Declare the cursor for the list of indexes to be defragged.
>> > DECLARE indexes CURSOR FOR
>> > SELECT ObjectName, ObjectId, IndexId, LogicalFrag
>> > FROM #fraglist
>> > WHERE LogicalFrag >= @.maxfrag
>> > AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
>> >
>> > -- Open the cursor.
>> > OPEN indexes;
>> >
>> > -- Loop through the indexes.
>> > FETCH NEXT
>> > FROM indexes
>> > INTO @.tablename, @.objectid, @.indexid, @.frag;
>> >
>> > WHILE @.@.FETCH_STATUS = 0
>> > BEGIN;
>> > PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
>> > ' + RTRIM(@.indexid) + ') - fragmentation currently '
>> > + RTRIM(CONVERT(varchar(15),@.frag)) + '%';
>> > SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
>> > ' + RTRIM(@.indexid) + ')';
>> > EXEC (@.execstr);
>> >
>> > FETCH NEXT
>> > FROM indexes
>> > INTO @.tablename, @.objectid, @.indexid, @.frag;
>> > END;
>> >
>> > -- Close and deallocate the cursor.
>> > CLOSE indexes;
>> > DEALLOCATE indexes;
>> >
>> > -- Delete the temporary table.
>> > DROP TABLE #fraglist;
>> > GO
>>|||Thanks to all for the information. I did run a test in Prod, and it
ran much faster. (Much larger box). I saw a script that loaded a
history table and I'll work on building that.
Thanks again to all responding.
David

Friday, March 9, 2012

index fragmentation

Hi,
I want to defrag all the indexes in a table. How do I use after DBCC
INDEXDEFRAG?
thanks,There is sample code for that in Books Online. Check under DBCC SHOWCONTIG.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"aoxpsql" <anonymous@.discussion.com> wrote in message news:ucfQLjSeEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to defrag all the indexes in a table. How do I use after DBCC
> INDEXDEFRAG?
> thanks,
>

index fragmentation

Using SS2000. Sometimes when I run DBCC SHOWCONTIG (D_Promotion_Date) WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS I get results like those below. The second line is a valid index - cognos_1 - but the first line doesn't have an index name but it show
s 90% fragmentation. Why does that line without an index name show up? Is there a way to get rid of it? Does it matter that it shows up?
d_promotion_date45357665400101132911268.37099999999999521454129.8999938964843798.39511108398437540.02590.080.0
d_promotion_date453576654cognos_1608NULLNULLNULLNULLNULL02NULLNULL33.333333333333329130.0NULL
Thanks,
Dan D.
It's the heap - look in BOL for DBCC SHOWCONTIG and it explains about the
null name and index id=0. It also explains that logical scan fragmentation
is not relevant for heaps.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:E5B09B34-D54E-4176-BE47-41B41E6D95E3@.microsoft.com...
> Using SS2000. Sometimes when I run DBCC SHOWCONTIG (D_Promotion_Date) WITH
FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS I get results like those below.
The second line is a valid index - cognos_1 - but the first line doesn't
have an index name but it shows 90% fragmentation. Why does that line
without an index name show up? Is there a way to get rid of it? Does it
matter that it shows up?
>
> d_promotion_date 453576654 0 0 10 1132 9 112 68.370999999999995 214 5 4
129.89999389648437 98.395111083984375 40.0 2 5 90.0 80.0
> d_promotion_date 453576654 cognos_1 6 0 8 NULL NULL NULL NULL NULL 0 2
NULL NULL 33.333333333333329 1 3 0.0 NULL
> Thanks,
> --
> Dan D.
|||Thanks.
Dan D.
"Paul S Randal [MS]" wrote:

> It's the heap - look in BOL for DBCC SHOWCONTIG and it explains about the
> null name and index id=0. It also explains that logical scan fragmentation
> is not relevant for heaps.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:E5B09B34-D54E-4176-BE47-41B41E6D95E3@.microsoft.com...
> FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS I get results like those below.
> The second line is a valid index - cognos_1 - but the first line doesn't
> have an index name but it shows 90% fragmentation. Why does that line
> without an index name show up? Is there a way to get rid of it? Does it
> matter that it shows up?
> 129.89999389648437 98.395111083984375 40.0 2 5 90.0 80.0
> NULL NULL 33.333333333333329 1 3 0.0 NULL
>
>

index fragmentation

Using SS2000. Sometimes when I run DBCC SHOWCONTIG (D_Promotion_Date) WITH F
AST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS I get results like those below.
The second line is a valid index - cognos_1 - but the first line doesn't hav
e an index name but it show
s 90% fragmentation. Why does that line without an index name show up? Is th
ere a way to get rid of it? Does it matter that it shows up?
d_promotion_date 453576654 0 0 10 1132
9 112 68.370999999999995 214 5 4 129
.89999389648437 98.395111083984375 40.0 2 5 90.0 80.0
d_promotion_date 453576654 cognos_1 6 0
8 NULL NULL NULL NULL NULL 0 2 NULL
NULL 33.333333333333329 1 3 0.0 NULL
Thanks,
--
Dan D.It's the heap - look in BOL for DBCC SHOWCONTIG and it explains about the
null name and index id=0. It also explains that logical scan fragmentation
is not relevant for heaps.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:E5B09B34-D54E-4176-BE47-41B41E6D95E3@.microsoft.com...
> Using SS2000. Sometimes when I run DBCC SHOWCONTIG (D_Promotion_Date) WITH
FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS I get results like those below.
The second line is a valid index - cognos_1 - but the first line doesn't
have an index name but it shows 90% fragmentation. Why does that line
without an index name show up? Is there a way to get rid of it? Does it
matter that it shows up?
>
> d_promotion_date 453576654 0 0 10 1132 9 112 68.370999999999995 214 5 4
129.89999389648437 98.395111083984375 40.0 2 5 90.0 80.0
> d_promotion_date 453576654 cognos_1 6 0 8 NULL NULL NULL NULL NULL 0 2
NULL NULL 33.333333333333329 1 3 0.0 NULL
> Thanks,
> --
> Dan D.|||Thanks.
--
Dan D.
"Paul S Randal [MS]" wrote:

> It's the heap - look in BOL for DBCC SHOWCONTIG and it explains about the
> null name and index id=0. It also explains that logical scan fragmentation
> is not relevant for heaps.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:E5B09B34-D54E-4176-BE47-41B41E6D95E3@.microsoft.com...
> FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS I get results like those belo
w.
> The second line is a valid index - cognos_1 - but the first line doesn't
> have an index name but it shows 90% fragmentation. Why does that line
> without an index name show up? Is there a way to get rid of it? Does it
> matter that it shows up?
> 129.89999389648437 98.395111083984375 40.0 2 5 90.0 80.0
> NULL NULL 33.333333333333329 1 3 0.0 NULL
>
>

Wednesday, March 7, 2012

Index defragmentation

Is there a way to run DBCC INDEXDEFRAG for all indexes, on all tables, in a
database?Hello,
Please take a look into the URL which contains the script.
http://www.databasejournal.com/scripts/article.php/3466971
Thanks
Hari
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:4BCDB5B7-FD22-46B1-A36D-78C860D58A7C@.microsoft.com...
> Is there a way to run DBCC INDEXDEFRAG for all indexes, on all tables, in
> a
> database?|||Rob,
this should work:
EXEC sp_msForEachTable @.COMMAND1= 'DBCC indexdefrag (''dbadata'', "?")'
However I'd recommend a more circumspect approach that takes accountof the
level of fragmentation before deciding what to reindex.
Paul Ibison|||On Mar 1, 9:36 am, Rob <R...@.discussions.microsoft.com> wrote:
> Is there a way to run DBCC INDEXDEFRAG for all indexes, on all tables, in a
> database?
http://realsqlguy.blogspot.com/2007/02/smart-index-defragmentation.html|||Rob wrote:
> Is there a way to run DBCC INDEXDEFRAG for all indexes, on all tables, in a
> database?
To add to the other replies, you can also look up DBCC SHOWCONTIG
command in BOL - here's an example of a script that can do what you ask
for.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

Index defragmentation

Is there a way to run DBCC INDEXDEFRAG for all indexes, on all tables, in a
database?Hello,
Please take a look into the URL which contains the script.
http://www.databasejournal.com/scri...cle.php/3466971
Thanks
Hari
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:4BCDB5B7-FD22-46B1-A36D-78C860D58A7C@.microsoft.com...
> Is there a way to run DBCC INDEXDEFRAG for all indexes, on all tables, in
> a
> database?|||Rob,
this should work:
EXEC sp_msForEachTable @.COMMAND1= 'DBCC indexdefrag (''dbadata'', "?")'
However I'd recommend a more circumspect approach that takes accountof the
level of fragmentation before deciding what to reindex.
Paul Ibison|||On Mar 1, 9:36 am, Rob <R...@.discussions.microsoft.com> wrote:
> Is there a way to run DBCC INDEXDEFRAG for all indexes, on all tables, in
a
> database?
http://realsqlguy.blogspot.com/2007...gmentation.html|||Rob wrote:
> Is there a way to run DBCC INDEXDEFRAG for all indexes, on all tables, in
a
> database?
To add to the other replies, you can also look up DBCC SHOWCONTIG
command in BOL - here's an example of a script that can do what you ask
for.
Regards
Steen Schlter Persson
Database Administrator / System Administrator

Friday, March 30, 2012

Index troubles

Monday, March 26, 2012

index rebuilds

index rebuilds

index rebuilds

Index Rebuilding

Index Rebuilding

Index rebuild

Friday, March 23, 2012

Index problems

Monday, March 19, 2012

Index Monitoring

Monday, March 12, 2012

Index Maint DBCC assistance.

Index Maint DBCC assistance.

Index Maint DBCC assistance.

Friday, March 9, 2012

index fragmentation

index fragmentation

index fragmentation

index fragmentation

Wednesday, March 7, 2012

Index defragmentation

Index defragmentation

index and key

Blog Archive

About Me