DBfile usage: index

Showing posts with label index. Show all posts

Tuesday, March 27, 2012

DBCC SHRINKDATABASE

We previously had a problem (due to the fact the previous DBA made every
index a non-clustered instead of clustered) that caused our database file
size to grow out of control. We had databases that should have been around
10gb growing to well over 50gb. I made everything a clustered index and
reindex every table and the database size dropped to 30gb but with roughly
20gb of free space. I'm not trying to use the DBCC SHRINKDATABASE command to
reclain that free space and on some database it's dropping the size from 30gb
down to 10gb with a few gb of free space which is great, but on some it's not
reclaiming that space. I've tried using the command with the truncate only
option, specifying like 5 percent free space and I just can't find why on
some databases it will reclaim that space (more success witht he truncate
only option than the others) and other databases it's retaining like 80% of
the database size as free space. I'm using the update usage command as well
to get things in line. Any help would be appreciated. Thanks.Hi
Try using DBCC SHRINKFILE instead
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_8b51.asp
You may also want to check out other posts where SHRINKDATABASE has not
changed the size such as http://tinyurl.com/uz2om
John
"brogers5884" wrote:
> We previously had a problem (due to the fact the previous DBA made every
> index a non-clustered instead of clustered) that caused our database file
> size to grow out of control. We had databases that should have been around
> 10gb growing to well over 50gb. I made everything a clustered index and
> reindex every table and the database size dropped to 30gb but with roughly
> 20gb of free space. I'm not trying to use the DBCC SHRINKDATABASE command to
> reclain that free space and on some database it's dropping the size from 30gb
> down to 10gb with a few gb of free space which is great, but on some it's not
> reclaiming that space. I've tried using the command with the truncate only
> option, specifying like 5 percent free space and I just can't find why on
> some databases it will reclaim that space (more success witht he truncate
> only option than the others) and other databases it's retaining like 80% of
> the database size as free space. I'm using the update usage command as well
> to get things in line. Any help would be appreciated. Thanks.

Sunday, March 25, 2012

dbcc showcontig with tablersults

DBCC SHOWCONTIG WITH TABLERESULTS will show a ROW column for an index's row count. However, on an important table with quite a few indexes that I am using as a baseline, ROW is NULL for all indexes. Why? BOL definines ROWS as:-

"Number of data or index records at that level of the index. For a heap, this is the number of data records in the entire heap."

By the way, if I select from sysindexes for a one of the above indexes, there is a correct rowcount for the index.

Any ideas?

CliveI found it. I was using the 'WITH FAST' parameter. Without FAST, it returns the row count.

Clive

DBCC SHOWCONTIG Performances

I have decided to automate the Index Defragmentation process on all tha
tables of my database
with a store proc.
As a starting point, I used an example from SQL Server 2000 Help (Transact
SQL Reference -
DBCC ShowContig topic : Example E : Use DBCC SHOWCONTIG and DBCC INDEXDEFRAG
to defragment
indexes in a database).
I want to only consider indexes with a fragmentation over 20%.
My problem is :
When I run DBCC SHOWCONTIG on a large table (43,000,000 rows, 12 indexes),
it takes an average
of 65 minutes before I get a result.
Code : DBCC SHOWCONTIG (TFt01_Vente_hebdo)
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
I must consider over 60 tables.
My SQL Server runs on a Windows 2000 Server (SP4), with a dual 2.70 Ghz
Intel Xeon Mp CPU and
7680 MB of RAM.
Would you have any advise helping me increasing thoses performances ?
The perf of SHOWCONTIG (which I wrote) is proportional to the IO
capabilities of your system, and in FAST mode, is also proportional to the
fanout of your table. In FAST mode, it reads all the b-tree pages in the
parent level (one level above the leaf level of the index) - so the larger
the fanout, the smaller the number of pages it has to read.
For the example table you give, you need to do the following for each index:
1) work out index row size, R
2) divide 8000 / R = LF (leaf fanout)
3) divide 43000000 / LF = LN (number of pages required at leaf level)
4) work out index key size, K (this can be different from the row size,
depending on SQL 2005 features used and whether the index is unique or not -
see 2005 BOL for full details)
5) divide 8000 / K = TF (tree fanout)
6) divide LN / TF = P (number of pages SHOWCONTIG has to read for this
index)
Does the table have a clustered index? If not, SHOWCONTIG will have to read
all (i.e. 8000 / data row size) pages.
What's the max IO throughput of your IO subsystem? Are you running
SHOWCONTIG while there's heavy load?
Why do you need to do this for all tables and indexes? Are you able to
correlate increasing logical scan fragmentation with decreasing query
performance, for all these tables and indexes?
Have a read of this whitepaper for more info too - let me know if you have
any further questions.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Marc G." <Marc G.@.discussions.microsoft.com> wrote in message
news:5F40205F-2835-4AA1-9B9E-D43D32E6CABE@.microsoft.com...
>I have decided to automate the Index Defragmentation process on all tha
> tables of my database
> with a store proc.
> As a starting point, I used an example from SQL Server 2000 Help (Transact
> SQL Reference -
> DBCC ShowContig topic : Example E : Use DBCC SHOWCONTIG and DBCC
> INDEXDEFRAG
> to defragment
> indexes in a database).
> I want to only consider indexes with a fragmentation over 20%.
> My problem is :
> When I run DBCC SHOWCONTIG on a large table (43,000,000 rows, 12 indexes),
> it takes an average
> of 65 minutes before I get a result.
> Code : DBCC SHOWCONTIG (TFt01_Vente_hebdo)
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
> I must consider over 60 tables.
> My SQL Server runs on a Windows 2000 Server (SP4), with a dual 2.70 Ghz
> Intel Xeon Mp CPU and
> 7680 MB of RAM.
> Would you have any advise helping me increasing thoses performances ?

DBCC SHOWCONTIG - Please Help

How Do i read this DBCC SHOWCONTIG
DBCC SHOWCONTIG scanning 'SAP_ITEM_XXX_SF' table...
Table: 'SAP_ITEM_XXX_SF' (1341194707); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned........................: 168623
- Extents Scanned.......................: 21288
- Extent Switches.......................: 38573
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 54.64% [21078:38574]
- Logical Scan Fragmentation ..............: 6.83%
- Extent Scan Fragmentation ...............: 29.76%
- Avg. Bytes Free per Page................: 1009.8
- Avg. Page Density (full)................: 87.52%
Scan Density And Avg. Page Density kinda concerns me !! How should i Fix it
?
Thanks A Bunchlots of good data here.
The three that I really focus on are "Pages Scanned", Scan Density, and Avg
Page Density.
The # of Pages Scanned basically indicates how many pages on disk it takes
to store the data for the table in question.
Anything under 1,000 pages is considered small and will have little impact
on index performance.
Scan Density indicates the percentage of contiguous space is used to store
your data. 100% means ALL of the data is in contiguous space (Ideal).
Anything below 80%, I would start to consider an indication of
"Fragmentation" which indicates excessive page splits, resulting in poor IO.
Avg Page Density indicates the percentage that each page used is filled. If
pages are 100% full, that means that in order to add new data to your table,
a Page Split will be required. A High number here, indicates that page
splits are likely, and for Write Intensive tables, this would suggest a need
for modifying the indexes "Fill Factor" settings.
There is much more to these numbers than what I mentioned above, but that is
a "Nutshell" view.
Cheers
Greg Jackson
PDX, Oregon
"Girish" <anonymous@.discussions.microsoft.com> wrote in message
news:769774C6-426F-4181-BEE5-70A5F71D4D08@.microsoft.com...
> How Do i read this DBCC SHOWCONTIG
> DBCC SHOWCONTIG scanning 'SAP_ITEM_XXX_SF' table...
> Table: 'SAP_ITEM_XXX_SF' (1341194707); index ID: 1, database ID: 11
> TABLE level scan performed.
> - Pages Scanned........................: 168623
> - Extents Scanned.......................: 21288
> - Extent Switches.......................: 38573
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 54.64% [21078:38574]
> - Logical Scan Fragmentation ..............: 6.83%
> - Extent Scan Fragmentation ...............: 29.76%
> - Avg. Bytes Free per Page................: 1009.8
> - Avg. Page Density (full)................: 87.52%
>
> Scan Density And Avg. Page Density kinda concerns me !! How should i Fix
it ?
> Thanks A Bunch|||I agree with Jaxon, but I like to add one thing:
If the table (index) span several (mdf/ndf) files, then jump between files
will be considered fragmentation in Scan Density. So, in these cases, use
Logical Scan Fragmentation instead. The value you have seems fine. Look up
how to interpret that value in Books Online.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:O1jBUFY9DHA.3176@.TK2MSFTNGP11.phx.gbl...
> lots of good data here.
> The three that I really focus on are "Pages Scanned", Scan Density, and
Avg
> Page Density.
> The # of Pages Scanned basically indicates how many pages on disk it takes
> to store the data for the table in question.
> Anything under 1,000 pages is considered small and will have little impact
> on index performance.
> Scan Density indicates the percentage of contiguous space is used to store
> your data. 100% means ALL of the data is in contiguous space (Ideal).
> Anything below 80%, I would start to consider an indication of
> "Fragmentation" which indicates excessive page splits, resulting in poor
IO.
> Avg Page Density indicates the percentage that each page used is filled.
If
> pages are 100% full, that means that in order to add new data to your
table,
> a Page Split will be required. A High number here, indicates that page
> splits are likely, and for Write Intensive tables, this would suggest a
need
> for modifying the indexes "Fill Factor" settings.
>
> There is much more to these numbers than what I mentioned above, but that
is
> a "Nutshell" view.
>
> Cheers
> Greg Jackson
> PDX, Oregon
>
> "Girish" <anonymous@.discussions.microsoft.com> wrote in message
> news:769774C6-426F-4181-BEE5-70A5F71D4D08@.microsoft.com...
Fix
> it ?
>|||You should read the whitepaper bleow which explains how to interpret the
numbers, what to do about them, and whether you should even be worried by
them in the first place.
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:elW1RLa9DHA.3444@.TK2MSFTNGP09.phx.gbl...
> I agree with Jaxon, but I like to add one thing:
> If the table (index) span several (mdf/ndf) files, then jump between files
> will be considered fragmentation in Scan Density. So, in these cases, use
> Logical Scan Fragmentation instead. The value you have seems fine. Look up
> how to interpret that value in Books Online.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
> news:O1jBUFY9DHA.3176@.TK2MSFTNGP11.phx.gbl...
> Avg
takes
impact
store
> IO.
> If
> table,
> need
that
> is
> Fix
>

Thursday, March 22, 2012

Dbcc Showcontig

DBCC SHOWCONTIG scanning 'MYTAB' table...
Table: 'MYTAB'(50099219); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 150
- Extents Scanned.......................: 25
- Extent Switches.......................: 25
- Avg. Pages per Extent..................: 6.0
- Scan Density [Best Count:Actual Count]......: 73.08% [19:26]
- Logical Scan Fragmentation ..............: 1.33%
- Extent Scan Fragmentation ...............: 96.00%
- Avg. Bytes Free per Page................: 878.5
- Avg. Page Density (full)................: 89.15%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Since Extents Scanned and Extent Switches r equal, why the density is slow, what about pages per Extent, can it be manaully increased?

HowdyI think the reason lies in the fact that you have 2 pages per extent occupied by different objects. If you had 8 pages per extent, the table would take up approx. 19 extents (19*8-Best count). Look up DBCC DBREINDEX and DBCC INDEXDEFRAG in BOL. mojza

DBCC SHOWCONTIG

I have a table called Shipping_Log_Element
This table has two indexes: a Primary Key and an index on a foreign_key referrant (refers to primary key in another table)
When I do a DBCC SHOWCONTIG (Shipping_Log_Element) WITH FAST,TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
I receive 4 rows: one each for the indexes listed above, plus two others. One of the others shows no index name, and the other shows an index name of tShipping_Log_Element. Bot of these two are highly fragmented based on Logical and ExtentFragmentation.
I ran DBCC DBREINDEX on all tables last night, so am not expecting to see much fragmentation.
What are these? What does the fragmentation on these mean? (i.e. what should I do about it)?
Thanks.Hi,
Table fragmentation occurs through the process of data modifications
(INSERT, UPDATE, and DELETE statements) made against the table.
To remove the fragmentation you can execute,
DBCC INDEXDEFRAG / DBCC REINDEX / DROP & CREATE Indexes
Thanks
Hari
MCDBA
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:A04732B3-57E1-48B3-B70D-1C07523874A3@.microsoft.com...
> I have a table called Shipping_Log_Element
> This table has two indexes: a Primary Key and an index on a foreign_key
referrant (refers to primary key in another table)
> When I do a DBCC SHOWCONTIG (Shipping_Log_Element) WITH FAST,TABLERESULTS,
ALL_INDEXES, NO_INFOMSGS
> I receive 4 rows: one each for the indexes listed above, plus two others.
One of the others shows no index name, and the other shows an index name of
tShipping_Log_Element. Bot of these two are highly fragmented based on
Logical and ExtentFragmentation.
> I ran DBCC DBREINDEX on all tables last night, so am not expecting to see
much fragmentation.
> What are these? What does the fragmentation on these mean? (i.e. what
should I do about it)?
> Thanks.|||What's the index Id of the nameless index? The tShipping_Log_Element index
will have ID=255 and is the IAM chain containing all text/image data for the
table. It's fragmentation cannot be addressed in SQL Server 2000 or before
except by re-creating the table using bcp out/in (however, we've added that
functionality in SQL Server Yukon).
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:A04732B3-57E1-48B3-B70D-1C07523874A3@.microsoft.com...
> I have a table called Shipping_Log_Element
> This table has two indexes: a Primary Key and an index on a foreign_key
referrant (refers to primary key in another table)
> When I do a DBCC SHOWCONTIG (Shipping_Log_Element) WITH FAST,TABLERESULTS,
ALL_INDEXES, NO_INFOMSGS
> I receive 4 rows: one each for the indexes listed above, plus two others.
One of the others shows no index name, and the other shows an index name of
tShipping_Log_Element. Bot of these two are highly fragmented based on
Logical and ExtentFragmentation.
> I ran DBCC DBREINDEX on all tables last night, so am not expecting to see
much fragmentation.
> What are these? What does the fragmentation on these mean? (i.e. what
should I do about it)?
> Thanks.|||Thanks. The tShipping_Log_Element is indeed index id 255. So should I be concerned about this? ie. is this hurting performance?
The other 'unknown' index with fragmentation has an index id of 0. Any thoughts on what that is?
There are several tables in this condition. To bcp out/in would be an effort and require an outage. What about dropping statistics, dropping indexes, and then recreating?
Is there any other solution besides bcp out/in? (short of Yukon)
Thanks for the help!|||You could do a test with your app before and after export/import to see if it hurts performance. As
with most things, it depends. If the data stored in image column isn't much for each row, and you
have good selectivity for the queries, I doubt it will hurt much.
As for the indid 0, these are data pages for tables without clustered indexes. I would start
investigating why you have tables without clustered indexes in the first place. There is no easy way
to "defrag" such, one method is to create a clustered index and then drop it. Or export/import.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:9EBF3C3E-E486-4524-89F0-3694BFFEC54D@.microsoft.com...
> Thanks. The tShipping_Log_Element is indeed index id 255. So should I be concerned about this?
ie. is this hurting performance?
> The other 'unknown' index with fragmentation has an index id of 0. Any thoughts on what that is?
> There are several tables in this condition. To bcp out/in would be an effort and require an
outage. What about dropping statistics, dropping indexes, and then recreating?
> Is there any other solution besides bcp out/in? (short of Yukon)
> Thanks for the help!|||Yes, we should have no tables without clustered indexes. (sounds like the old song... no bananas)
I'll follow up on that
Thanks to all!!
Linda

DBCC SHOWCONTIG

quote:

> I have a table called Shipping_Log_Element
> This table has two indexes: a Primary Key and an index on a foreign_key

referrant (refers to primary key in another table)

quote:

> When I do a DBCC SHOWCONTIG (Shipping_Log_Element) WITH FAST,TABLERESULTS,

ALL_INDEXES, NO_INFOMSGS

quote:

> I receive 4 rows: one each for the indexes listed above, plus two others.

One of the others shows no index name, and the other shows an index name of
tShipping_Log_Element. Bot of these two are highly fragmented based on
Logical and ExtentFragmentation.

quote:

> I ran DBCC DBREINDEX on all tables last night, so am not expecting to see

much fragmentation.

quote:

> What are these? What does the fragmentation on these mean? (i.e. what

should I do about it)?

quote:

> Thanks.

|||What's the index Id of the nameless index? The tShipping_Log_Element index
will have ID=255 and is the IAM chain containing all text/image data for the
table. It's fragmentation cannot be addressed in SQL Server 2000 or before
except by re-creating the table using bcp out/in (however, we've added that
functionality in SQL Server Yukon).
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:A04732B3-57E1-48B3-B70D-1C07523874A3@.microsoft.com...

quote:

> I have a table called Shipping_Log_Element
> This table has two indexes: a Primary Key and an index on a foreign_key

referrant (refers to primary key in another table)

quote:

> When I do a DBCC SHOWCONTIG (Shipping_Log_Element) WITH FAST,TABLERESULTS,

ALL_INDEXES, NO_INFOMSGS

quote:

> I receive 4 rows: one each for the indexes listed above, plus two others.

One of the others shows no index name, and the other shows an index name of
tShipping_Log_Element. Bot of these two are highly fragmented based on
Logical and ExtentFragmentation.

quote:

> I ran DBCC DBREINDEX on all tables last night, so am not expecting to see

much fragmentation.

quote:

> What are these? What does the fragmentation on these mean? (i.e. what

should I do about it)?

quote:

> Thanks.

|||Thanks. The tShipping_Log_Element is indeed index id 255. So should I be co
ncerned about this? ie. is this hurting performance?
The other 'unknown' index with fragmentation has an index id of 0. Any thoug
hts on what that is?
There are several tables in this condition. To bcp out/in would be an effor
t and require an outage. What about dropping statistics, dropping indexes, a
nd then recreating?
Is there any other solution besides bcp out/in? (short of Yukon)
Thanks for the help!|||You could do a test with your app before and after export/import to see if i
t hurts performance. As
with most things, it depends. If the data stored in image column isn't much
for each row, and you
have good selectivity for the queries, I doubt it will hurt much.
As for the indid 0, these are data pages for tables without clustered indexe
s. I would start
investigating why you have tables without clustered indexes in the first pla
ce. There is no easy way
to "defrag" such, one method is to create a clustered index and then drop it
. Or export/import.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:9EBF3C3E-E486-4524-89F0-3694BFFEC54D@.microsoft.com...

quote:

> Thanks. The tShipping_Log_Element is indeed index id 255. So should I be concerned about
this?

ie. is this hurting performance?

quote:

> The other 'unknown' index with fragmentation has an index id of 0. Any tho
ughts on what that is?
> There are several tables in this condition. To bcp out/in would be an effort and require
an

outage. What about dropping statistics, dropping indexes, and then recreating?

quote:
d">
> Is there any other solution besides bcp out/in? (short of Yukon)
> Thanks for the help!

|||Yes, we should have no tables without clustered indexes. (sounds like the ol
d song... no bananas).
I'll follow up on that.
Thanks to all!!!
Lindasql

Sunday, March 11, 2012

DBCC INDEXDEFRAG

I index my database nightly since I have many changes to
cluster indexes and non-cluster indexes daily. I ran a
script the would tells me every table the number of rows,
index space, data space, and unused space. Next I
applied the DBCC INDEXDEFRAG to every table and each
index which ran for approximately 40 minutes. Then I ran
the script again to tell me every table the number of
rows, index space, data space, and unused space. There
was no change in values.
Please help me with this problem?
Don
My guess is that the script you are mentioning is using the sysindexes
system table. This table is updated asynchronously. Try to update the data
for a specific table using sp_spaceused
(http://msdn.microsoft.com/library/de...-us/tsqlref/ts
_sp_sa-sz_0kro.asp) system sp.
Next, what do you expect to change? N of rows should not change during index
defragmentation! DBCC INDEXDEFRAG is used primarily to defragment logical
fragmentation (logical <> physical order). Sometimes it might compact
indexes as well, if they are physically fragmented; if not, you won't see
any changes in index space
(http://msdn.microsoft.com/library/de...-us/tsqlref/ts
_dbcc_30o9.asp).
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1573e01c446c9$71f94970$a401280a@.phx.gbl...
> I index my database nightly since I have many changes to
> cluster indexes and non-cluster indexes daily. I ran a
> script the would tells me every table the number of rows,
> index space, data space, and unused space. Next I
> applied the DBCC INDEXDEFRAG to every table and each
> index which ran for approximately 40 minutes. Then I ran
> the script again to tell me every table the number of
> rows, index space, data space, and unused space. There
> was no change in values.
> Please help me with this problem?
> Don
>
|||In addition to what Dejan said, INDEXDEFRAG does not recompute the
statistics. You might want to consider running sp_updatestats after
INDEXDEFRAG.

Andrew J. Kelly
SQL Server MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1573e01c446c9$71f94970$a401280a@.phx.gbl...
> I index my database nightly since I have many changes to
> cluster indexes and non-cluster indexes daily. I ran a
> script the would tells me every table the number of rows,
> index space, data space, and unused space. Next I
> applied the DBCC INDEXDEFRAG to every table and each
> index which ran for approximately 40 minutes. Then I ran
> the script again to tell me every table the number of
> rows, index space, data space, and unused space. There
> was no change in values.
> Please help me with this problem?
> Don
>

Thursday, March 8, 2012

DBCC INDEXDEFRAG

I index my database nightly since I have many changes to
cluster indexes and non-cluster indexes daily. I ran a
script the would tells me every table the number of rows,
index space, data space, and unused space. Next I
applied the DBCC INDEXDEFRAG to every table and each
index which ran for approximately 40 minutes. Then I ran
the script again to tell me every table the number of
rows, index space, data space, and unused space. There
was no change in values.
Please help me with this problem?
DonMy guess is that the script you are mentioning is using the sysindexes
system table. This table is updated asynchronously. Try to update the data
for a specific table using sp_spaceused
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts
_sp_sa-sz_0kro.asp) system sp.
Next, what do you expect to change? N of rows should not change during index
defragmentation! DBCC INDEXDEFRAG is used primarily to defragment logical
fragmentation (logical <> physical order). Sometimes it might compact
indexes as well, if they are physically fragmented; if not, you won't see
any changes in index space
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts
_dbcc_30o9.asp).
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1573e01c446c9$71f94970$a401280a@.phx.gbl...
> I index my database nightly since I have many changes to
> cluster indexes and non-cluster indexes daily. I ran a
> script the would tells me every table the number of rows,
> index space, data space, and unused space. Next I
> applied the DBCC INDEXDEFRAG to every table and each
> index which ran for approximately 40 minutes. Then I ran
> the script again to tell me every table the number of
> rows, index space, data space, and unused space. There
> was no change in values.
> Please help me with this problem?
> Don
>|||In addition to what Dejan said, INDEXDEFRAG does not recompute the
statistics. You might want to consider running sp_updatestats after
INDEXDEFRAG.
Andrew J. Kelly
SQL Server MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1573e01c446c9$71f94970$a401280a@.phx.gbl...
> I index my database nightly since I have many changes to
> cluster indexes and non-cluster indexes daily. I ran a
> script the would tells me every table the number of rows,
> index space, data space, and unused space. Next I
> applied the DBCC INDEXDEFRAG to every table and each
> index which ran for approximately 40 minutes. Then I ran
> the script again to tell me every table the number of
> rows, index space, data space, and unused space. There
> was no change in values.
> Please help me with this problem?
> Don
>

DBCC INDEXDEFRAG

I index my database nightly since I have many changes to
cluster indexes and non-cluster indexes daily. I ran a
script the would tells me every table the number of rows,
index space, data space, and unused space. Next I
applied the DBCC INDEXDEFRAG to every table and each
index which ran for approximately 40 minutes. Then I ran
the script again to tell me every table the number of
rows, index space, data space, and unused space. There
was no change in values.
Please help me with this problem?
DonMy guess is that the script you are mentioning is using the sysindexes
system table. This table is updated asynchronously. Try to update the data
for a specific table using sp_spaceused
(http://msdn.microsoft.com/library/d...n-us/tsqlref/ts
_sp_sa-sz_0kro.asp) system sp.
Next, what do you expect to change? N of rows should not change during index
defragmentation! DBCC INDEXDEFRAG is used primarily to defragment logical
fragmentation (logical <> physical order). Sometimes it might compact
indexes as well, if they are physically fragmented; if not, you won't see
any changes in index space
(http://msdn.microsoft.com/library/d...n-us/tsqlref/ts
_dbcc_30o9.asp).
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1573e01c446c9$71f94970$a401280a@.phx
.gbl...
> I index my database nightly since I have many changes to
> cluster indexes and non-cluster indexes daily. I ran a
> script the would tells me every table the number of rows,
> index space, data space, and unused space. Next I
> applied the DBCC INDEXDEFRAG to every table and each
> index which ran for approximately 40 minutes. Then I ran
> the script again to tell me every table the number of
> rows, index space, data space, and unused space. There
> was no change in values.
> Please help me with this problem?
> Don
>|||In addition to what Dejan said, INDEXDEFRAG does not recompute the
statistics. You might want to consider running sp_updatestats after
INDEXDEFRAG.
Andrew J. Kelly
SQL Server MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1573e01c446c9$71f94970$a401280a@.phx
.gbl...
> I index my database nightly since I have many changes to
> cluster indexes and non-cluster indexes daily. I ran a
> script the would tells me every table the number of rows,
> index space, data space, and unused space. Next I
> applied the DBCC INDEXDEFRAG to every table and each
> index which ran for approximately 40 minutes. Then I ran
> the script again to tell me every table the number of
> rows, index space, data space, and unused space. There
> was no change in values.
> Please help me with this problem?
> Don
>

dbcc error 2511

We had a dbcc error 2511 on a user table - non-clustered
index.
>>There are 0 rows in 1 pages for
object 'sched_order_item'.
Msg 2511, Level 16, State 1, Server DCECANP1, Procedure ,
Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Table
Corrupt: Object
ID 1421716613, Index ID 5. Keys out of order on page
(1:758631), slots
120 and 121.<<
dbcc checktable(table_name,repair_rebuild) in single user
mode was run, but the error is back the night after. By
the way, table doesn't have any data in it. if anybody
have any previous experiance with it and could share it
with me, I would appreciate it. Thanks very muchBOL shows it to be an index out of order problem. Strange
as you have no rows. I think I have come across this where
table changes are made and not reflected in the index
because there is no data.
I would try scripting out the table, drop it and then
recreate it.
Regards
John

DBCC DBREINDEX\INDEXDEFRAG

I modified the DBCC INDEXDEFRAG script from BOL with the
DBCC DBREINDEX. If the LogicalFragmentation is greater
than 75 then DBCC REINDEX the index. If the
LogicalFragmentation is between 5 and 75 then used the
DBCC INDEXDEFRAG.
I applied the script listed below to a database that has
1500 indexes that is heavy fragmented. The script on
found approximately 75 indexes to reindex or defrag.
Please help me with the missing indexes that need to be
reindexed or defragmented.
Thanks,
Dan
DECLARE @.tablename VARCHAR (128)
DECLARE @.execstr VARCHAR (255)
DECLARE @.objectid INT
DECLARE @.indexid INT
DECLARE @.frag DECIMAL
DECLARE @.IndexName VARCHAR (255)
DECLARE @.maxfrag DECIMAL
DECLARE @.CmdType VARCHAR (255)
-- Decide on the maximum fragmentation to allow
SELECT @.maxfrag = 5
-- Declare 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) NULL,
ObjectId INT NULL,
IndexName CHAR (255) NULL,
IndexId INT NULL,
Lvl INT NULL,
CountPages INT NULL,
CountRows INT NULL,
MinRecSize INT NULL,
MaxRecSize INT NULL,
AvgRecSize INT NULL,
ForRecCount INT NULL,
Extents INT NULL,
ExtentSwitches INT NULL,
AvgFreeBytes INT NULL,
AvgPageDensity INT NULL,
ScanDensity DECIMAL NULL,
BestCount INT NULL,
ActualCount INT NULL,
LogicalFrag DECIMAL NULL,
ExtentFrag DECIMAL NULL)
-- 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 cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT #fraglist. ObjectName
, #fraglist. ObjectId
, #fraglist. IndexId
, #fraglist. LogicalFrag
, #fraglist. IndexName
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId,
IndexName, 'IndexDepth') > 0
ORDER BY LogicalFrag desc, ObjectName asc
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Executing Check on (0, ' + RTRIM(@.tablename)
+ ', ' + RTRIM(@.IndexName) + ') - fragmentation
currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
if @.frag > 75
select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM
(@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
else
if @.frag between 5 AND 75
SELECT @.cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM
(@.objectid) + ', ' + RTRIM(@.indexid) + ')'
EXEC (@.execstr)
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag,
@.IndexName
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GO
Dan,
You were executing the wrong variable (@.execstr), a variable you never assigned a value to. You should execute
the @.CmdType variable instead. You can use below code, but you need to comment out my print and remove the
comment for your EXEC:
DECLARE @.tablename VARCHAR (128)
DECLARE @.execstr VARCHAR (255)
DECLARE @.objectid INT
DECLARE @.indexid INT
DECLARE @.frag DECIMAL
DECLARE @.IndexName VARCHAR (255)
DECLARE @.maxfrag DECIMAL
DECLARE @.CmdType VARCHAR (255)
-- Decide on the maximum fragmentation to allow
SELECT @.maxfrag = 5
-- Declare 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) NULL,
ObjectId INT NULL,
IndexName CHAR (255) NULL,
IndexId INT NULL,
Lvl INT NULL,
CountPages INT NULL,
CountRows INT NULL,
MinRecSize INT NULL,
MaxRecSize INT NULL,
AvgRecSize INT NULL,
ForRecCount INT NULL,
Extents INT NULL,
ExtentSwitches INT NULL,
AvgFreeBytes INT NULL,
AvgPageDensity INT NULL,
ScanDensity DECIMAL NULL,
BestCount INT NULL,
ActualCount INT NULL,
LogicalFrag DECIMAL NULL,
ExtentFrag DECIMAL NULL)
-- 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 cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT #fraglist. ObjectName
, #fraglist. ObjectId
, #fraglist. IndexId
, #fraglist. LogicalFrag
, #fraglist. IndexName
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
ORDER BY LogicalFrag desc, ObjectName asc
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Executing Check on (0, ' + RTRIM(@.tablename) + ', ' + RTRIM(@.IndexName) + ') - fragmentation
currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
if @.frag > 75
select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM(@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
else
if @.frag between 5 AND 75
SELECT @.cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ', ' + RTRIM(@.indexid) + ')'
-- EXEC (@.cmdtype)
print @.cmdtype
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <anonymous@.discussions.microsoft.com> wrote in message news:1927901c44d03$1b2cad90$a601280a@.phx.gbl...
> I modified the DBCC INDEXDEFRAG script from BOL with the
> DBCC DBREINDEX. If the LogicalFragmentation is greater
> than 75 then DBCC REINDEX the index. If the
> LogicalFragmentation is between 5 and 75 then used the
> DBCC INDEXDEFRAG.
> I applied the script listed below to a database that has
> 1500 indexes that is heavy fragmented. The script on
> found approximately 75 indexes to reindex or defrag.
> Please help me with the missing indexes that need to be
> reindexed or defragmented.
> Thanks,
> Dan
> DECLARE @.tablename VARCHAR (128)
> DECLARE @.execstr VARCHAR (255)
> DECLARE @.objectid INT
> DECLARE @.indexid INT
> DECLARE @.frag DECIMAL
> DECLARE @.IndexName VARCHAR (255)
> DECLARE @.maxfrag DECIMAL
> DECLARE @.CmdType VARCHAR (255)
> -- Decide on the maximum fragmentation to allow
> SELECT @.maxfrag = 5
> -- Declare 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) NULL,
> ObjectId INT NULL,
> IndexName CHAR (255) NULL,
> IndexId INT NULL,
> Lvl INT NULL,
> CountPages INT NULL,
> CountRows INT NULL,
> MinRecSize INT NULL,
> MaxRecSize INT NULL,
> AvgRecSize INT NULL,
> ForRecCount INT NULL,
> Extents INT NULL,
> ExtentSwitches INT NULL,
> AvgFreeBytes INT NULL,
> AvgPageDensity INT NULL,
> ScanDensity DECIMAL NULL,
> BestCount INT NULL,
> ActualCount INT NULL,
> LogicalFrag DECIMAL NULL,
> ExtentFrag DECIMAL NULL)
> -- 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 cursor for list of indexes to be defragged
> DECLARE indexes CURSOR FOR
> SELECT #fraglist. ObjectName
> , #fraglist. ObjectId
> , #fraglist. IndexId
> , #fraglist. LogicalFrag
> , #fraglist. IndexName
> FROM #fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId,
> IndexName, 'IndexDepth') > 0
> ORDER BY LogicalFrag desc, ObjectName asc
> -- Open the cursor
> OPEN indexes
> -- loop through the indexes
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Executing Check on (0, ' + RTRIM(@.tablename)
> + ', ' + RTRIM(@.IndexName) + ') - fragmentation
> currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
> if @.frag > 75
> select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM
> (@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
> else
> if @.frag between 5 AND 75
> SELECT @.cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM
> (@.objectid) + ', ' + RTRIM(@.indexid) + ')'
> EXEC (@.execstr)
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag,
> @.IndexName
> END
> -- Close and deallocate the cursor
> CLOSE indexes
> DEALLOCATE indexes
> -- Delete the temporary table
> DROP TABLE #fraglist
> GO
>
|||Dan
Run DBCC SHOWCONTIG to identify fragmentation.
For more details please refer to the BOL.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:1927901c44d03$1b2cad90$a601280a@.phx.gbl...
> I modified the DBCC INDEXDEFRAG script from BOL with the
> DBCC DBREINDEX. If the LogicalFragmentation is greater
> than 75 then DBCC REINDEX the index. If the
> LogicalFragmentation is between 5 and 75 then used the
> DBCC INDEXDEFRAG.
> I applied the script listed below to a database that has
> 1500 indexes that is heavy fragmented. The script on
> found approximately 75 indexes to reindex or defrag.
> Please help me with the missing indexes that need to be
> reindexed or defragmented.
> Thanks,
> Dan
> DECLARE @.tablename VARCHAR (128)
> DECLARE @.execstr VARCHAR (255)
> DECLARE @.objectid INT
> DECLARE @.indexid INT
> DECLARE @.frag DECIMAL
> DECLARE @.IndexName VARCHAR (255)
> DECLARE @.maxfrag DECIMAL
> DECLARE @.CmdType VARCHAR (255)
> -- Decide on the maximum fragmentation to allow
> SELECT @.maxfrag = 5
> -- Declare 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) NULL,
> ObjectId INT NULL,
> IndexName CHAR (255) NULL,
> IndexId INT NULL,
> Lvl INT NULL,
> CountPages INT NULL,
> CountRows INT NULL,
> MinRecSize INT NULL,
> MaxRecSize INT NULL,
> AvgRecSize INT NULL,
> ForRecCount INT NULL,
> Extents INT NULL,
> ExtentSwitches INT NULL,
> AvgFreeBytes INT NULL,
> AvgPageDensity INT NULL,
> ScanDensity DECIMAL NULL,
> BestCount INT NULL,
> ActualCount INT NULL,
> LogicalFrag DECIMAL NULL,
> ExtentFrag DECIMAL NULL)
> -- 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 cursor for list of indexes to be defragged
> DECLARE indexes CURSOR FOR
> SELECT #fraglist. ObjectName
> , #fraglist. ObjectId
> , #fraglist. IndexId
> , #fraglist. LogicalFrag
> , #fraglist. IndexName
> FROM #fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId,
> IndexName, 'IndexDepth') > 0
> ORDER BY LogicalFrag desc, ObjectName asc
> -- Open the cursor
> OPEN indexes
> -- loop through the indexes
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Executing Check on (0, ' + RTRIM(@.tablename)
> + ', ' + RTRIM(@.IndexName) + ') - fragmentation
> currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
> if @.frag > 75
> select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM
> (@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
> else
> if @.frag between 5 AND 75
> SELECT @.cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM
> (@.objectid) + ', ' + RTRIM(@.indexid) + ')'
> EXEC (@.execstr)
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag,
> @.IndexName
> END
> -- Close and deallocate the cursor
> CLOSE indexes
> DEALLOCATE indexes
> -- Delete the temporary table
> DROP TABLE #fraglist
> GO
>

DBCC DBREINDEX\INDEXDEFRAG

I modified the DBCC INDEXDEFRAG script from BOL with the
DBCC DBREINDEX. If the LogicalFragmentation is greater
than 75 then DBCC REINDEX the index. If the
LogicalFragmentation is between 5 and 75 then used the
DBCC INDEXDEFRAG.
I applied the script listed below to a database that has
1500 indexes that is heavy fragmented. The script on
found approximately 75 indexes to reindex or defrag.
Please help me with the missing indexes that need to be
reindexed or defragmented.
Thanks,
Dan
DECLARE @.tablename VARCHAR (128)
DECLARE @.execstr VARCHAR (255)
DECLARE @.objectid INT
DECLARE @.indexid INT
DECLARE @.frag DECIMAL
DECLARE @.IndexName VARCHAR (255)
DECLARE @.maxfrag DECIMAL
DECLARE @.CmdType VARCHAR (255)
-- Decide on the maximum fragmentation to allow
SELECT @.maxfrag = 5
-- Declare 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) NULL,
ObjectId INT NULL,
IndexName CHAR (255) NULL,
IndexId INT NULL,
Lvl INT NULL,
CountPages INT NULL,
CountRows INT NULL,
MinRecSize INT NULL,
MaxRecSize INT NULL,
AvgRecSize INT NULL,
ForRecCount INT NULL,
Extents INT NULL,
ExtentSwitches INT NULL,
AvgFreeBytes INT NULL,
AvgPageDensity INT NULL,
ScanDensity DECIMAL NULL,
BestCount INT NULL,
ActualCount INT NULL,
LogicalFrag DECIMAL NULL,
ExtentFrag DECIMAL NULL)
-- 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 cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT #fraglist. ObjectName
, #fraglist. ObjectId
, #fraglist. IndexId
, #fraglist. LogicalFrag
, #fraglist. IndexName
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId,
IndexName, 'IndexDepth') > 0
ORDER BY LogicalFrag desc, ObjectName asc
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Executing Check on (0, ' + RTRIM(@.tablename)
+ ', ' + RTRIM(@.IndexName) + ') - fragmentation
currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
if @.frag > 75
select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM
(@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
else
if @.frag between 5 AND 75
SELECT @.cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM
(@.objectid) + ', ' + RTRIM(@.indexid) + ')'
EXEC (@.execstr)
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag,
@.IndexName
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GODan,
You were executing the wrong variable (@.execstr), a variable you never assigned a value to. You should execute
the @.CmdType variable instead. You can use below code, but you need to comment out my print and remove the
comment for your EXEC:
DECLARE @.tablename VARCHAR (128)
DECLARE @.execstr VARCHAR (255)
DECLARE @.objectid INT
DECLARE @.indexid INT
DECLARE @.frag DECIMAL
DECLARE @.IndexName VARCHAR (255)
DECLARE @.maxfrag DECIMAL
DECLARE @.CmdType VARCHAR (255)
-- Decide on the maximum fragmentation to allow
SELECT @.maxfrag = 5
-- Declare 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) NULL,
ObjectId INT NULL,
IndexName CHAR (255) NULL,
IndexId INT NULL,
Lvl INT NULL,
CountPages INT NULL,
CountRows INT NULL,
MinRecSize INT NULL,
MaxRecSize INT NULL,
AvgRecSize INT NULL,
ForRecCount INT NULL,
Extents INT NULL,
ExtentSwitches INT NULL,
AvgFreeBytes INT NULL,
AvgPageDensity INT NULL,
ScanDensity DECIMAL NULL,
BestCount INT NULL,
ActualCount INT NULL,
LogicalFrag DECIMAL NULL,
ExtentFrag DECIMAL NULL)
-- 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 cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT #fraglist. ObjectName
, #fraglist. ObjectId
, #fraglist. IndexId
, #fraglist. LogicalFrag
, #fraglist. IndexName
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
ORDER BY LogicalFrag desc, ObjectName asc
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Executing Check on (0, ' + RTRIM(@.tablename) + ', ' + RTRIM(@.IndexName) + ') - fragmentation
currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
if @.frag > 75
select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM(@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
else
if @.frag between 5 AND 75
SELECT @.cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ', ' + RTRIM(@.indexid) + ')'
-- EXEC (@.cmdtype)
print @.cmdtype
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <anonymous@.discussions.microsoft.com> wrote in message news:1927901c44d03$1b2cad90$a601280a@.phx.gbl...
> I modified the DBCC INDEXDEFRAG script from BOL with the
> DBCC DBREINDEX. If the LogicalFragmentation is greater
> than 75 then DBCC REINDEX the index. If the
> LogicalFragmentation is between 5 and 75 then used the
> DBCC INDEXDEFRAG.
> I applied the script listed below to a database that has
> 1500 indexes that is heavy fragmented. The script on
> found approximately 75 indexes to reindex or defrag.
> Please help me with the missing indexes that need to be
> reindexed or defragmented.
> Thanks,
> Dan
> DECLARE @.tablename VARCHAR (128)
> DECLARE @.execstr VARCHAR (255)
> DECLARE @.objectid INT
> DECLARE @.indexid INT
> DECLARE @.frag DECIMAL
> DECLARE @.IndexName VARCHAR (255)
> DECLARE @.maxfrag DECIMAL
> DECLARE @.CmdType VARCHAR (255)
> -- Decide on the maximum fragmentation to allow
> SELECT @.maxfrag = 5
> -- Declare 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) NULL,
> ObjectId INT NULL,
> IndexName CHAR (255) NULL,
> IndexId INT NULL,
> Lvl INT NULL,
> CountPages INT NULL,
> CountRows INT NULL,
> MinRecSize INT NULL,
> MaxRecSize INT NULL,
> AvgRecSize INT NULL,
> ForRecCount INT NULL,
> Extents INT NULL,
> ExtentSwitches INT NULL,
> AvgFreeBytes INT NULL,
> AvgPageDensity INT NULL,
> ScanDensity DECIMAL NULL,
> BestCount INT NULL,
> ActualCount INT NULL,
> LogicalFrag DECIMAL NULL,
> ExtentFrag DECIMAL NULL)
> -- 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 cursor for list of indexes to be defragged
> DECLARE indexes CURSOR FOR
> SELECT #fraglist. ObjectName
> , #fraglist. ObjectId
> , #fraglist. IndexId
> , #fraglist. LogicalFrag
> , #fraglist. IndexName
> FROM #fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId,
> IndexName, 'IndexDepth') > 0
> ORDER BY LogicalFrag desc, ObjectName asc
> -- Open the cursor
> OPEN indexes
> -- loop through the indexes
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Executing Check on (0, ' + RTRIM(@.tablename)
> + ', ' + RTRIM(@.IndexName) + ') - fragmentation
> currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
> if @.frag > 75
> select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM
> (@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
> else
> if @.frag between 5 AND 75
> SELECT @.cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM
> (@.objectid) + ', ' + RTRIM(@.indexid) + ')'
> EXEC (@.execstr)
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag,
> @.IndexName
> END
> -- Close and deallocate the cursor
> CLOSE indexes
> DEALLOCATE indexes
> -- Delete the temporary table
> DROP TABLE #fraglist
> GO
>|||Dan
Run DBCC SHOWCONTIG to identify fragmentation.
For more details please refer to the BOL.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:1927901c44d03$1b2cad90$a601280a@.phx.gbl...
> I modified the DBCC INDEXDEFRAG script from BOL with the
> DBCC DBREINDEX. If the LogicalFragmentation is greater
> than 75 then DBCC REINDEX the index. If the
> LogicalFragmentation is between 5 and 75 then used the
> DBCC INDEXDEFRAG.
> I applied the script listed below to a database that has
> 1500 indexes that is heavy fragmented. The script on
> found approximately 75 indexes to reindex or defrag.
> Please help me with the missing indexes that need to be
> reindexed or defragmented.
> Thanks,
> Dan
> DECLARE @.tablename VARCHAR (128)
> DECLARE @.execstr VARCHAR (255)
> DECLARE @.objectid INT
> DECLARE @.indexid INT
> DECLARE @.frag DECIMAL
> DECLARE @.IndexName VARCHAR (255)
> DECLARE @.maxfrag DECIMAL
> DECLARE @.CmdType VARCHAR (255)
> -- Decide on the maximum fragmentation to allow
> SELECT @.maxfrag = 5
> -- Declare 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) NULL,
> ObjectId INT NULL,
> IndexName CHAR (255) NULL,
> IndexId INT NULL,
> Lvl INT NULL,
> CountPages INT NULL,
> CountRows INT NULL,
> MinRecSize INT NULL,
> MaxRecSize INT NULL,
> AvgRecSize INT NULL,
> ForRecCount INT NULL,
> Extents INT NULL,
> ExtentSwitches INT NULL,
> AvgFreeBytes INT NULL,
> AvgPageDensity INT NULL,
> ScanDensity DECIMAL NULL,
> BestCount INT NULL,
> ActualCount INT NULL,
> LogicalFrag DECIMAL NULL,
> ExtentFrag DECIMAL NULL)
> -- 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 cursor for list of indexes to be defragged
> DECLARE indexes CURSOR FOR
> SELECT #fraglist. ObjectName
> , #fraglist. ObjectId
> , #fraglist. IndexId
> , #fraglist. LogicalFrag
> , #fraglist. IndexName
> FROM #fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId,
> IndexName, 'IndexDepth') > 0
> ORDER BY LogicalFrag desc, ObjectName asc
> -- Open the cursor
> OPEN indexes
> -- loop through the indexes
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Executing Check on (0, ' + RTRIM(@.tablename)
> + ', ' + RTRIM(@.IndexName) + ') - fragmentation
> currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
> if @.frag > 75
> select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM
> (@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
> else
> if @.frag between 5 AND 75
> SELECT @.cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM
> (@.objectid) + ', ' + RTRIM(@.indexid) + ')'
> EXEC (@.execstr)
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag,
> @.IndexName
> END
> -- Close and deallocate the cursor
> CLOSE indexes
> DEALLOCATE indexes
> -- Delete the temporary table
> DROP TABLE #fraglist
> GO
>

DBCC DBREINDEX\INDEXDEFRAG

I modified the DBCC INDEXDEFRAG script from BOL with the
DBCC DBREINDEX. If the LogicalFragmentation is greater
than 75 then DBCC REINDEX the index. If the
LogicalFragmentation is between 5 and 75 then used the
DBCC INDEXDEFRAG.
I applied the script listed below to a database that has
1500 indexes that is heavy fragmented. The script on
found approximately 75 indexes to reindex or defrag.
Please help me with the missing indexes that need to be
reindexed or defragmented.
Thanks,
Dan
DECLARE @.tablename VARCHAR (128)
DECLARE @.execstr VARCHAR (255)
DECLARE @.objectid INT
DECLARE @.indexid INT
DECLARE @.frag DECIMAL
DECLARE @.IndexName VARCHAR (255)
DECLARE @.maxfrag DECIMAL
DECLARE @.CmdType VARCHAR (255)
-- Decide on the maximum fragmentation to allow
SELECT @.maxfrag = 5
-- Declare 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) NULL,
ObjectId INT NULL,
IndexName CHAR (255) NULL,
IndexId INT NULL,
Lvl INT NULL,
CountPages INT NULL,
CountRows INT NULL,
MinRecSize INT NULL,
MaxRecSize INT NULL,
AvgRecSize INT NULL,
ForRecCount INT NULL,
Extents INT NULL,
ExtentSwitches INT NULL,
AvgFreeBytes INT NULL,
AvgPageDensity INT NULL,
ScanDensity DECIMAL NULL,
BestCount INT NULL,
ActualCount INT NULL,
LogicalFrag DECIMAL NULL,
ExtentFrag DECIMAL NULL)
-- 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 cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT #fraglist. ObjectName
, #fraglist. ObjectId
, #fraglist. IndexId
, #fraglist. LogicalFrag
, #fraglist. IndexName
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId,
IndexName, 'IndexDepth') > 0
ORDER BY LogicalFrag desc, ObjectName asc
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Executing Check on (0, ' + RTRIM(@.tablename)
+ ', ' + RTRIM(@.IndexName) + ') - fragmentation
currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
if @.frag > 75
select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM
(@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
else
if @.frag between 5 AND 75
SELECT @.cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM
(@.objectid) + ', ' + RTRIM(@.indexid) + ')'
EXEC (@.execstr)
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag,
@.IndexName
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GODan,
You were executing the wrong variable (@.execstr), a variable you never assig
ned a value to. You should execute
the @.CmdType variable instead. You can use below code, but you need to comme
nt out my print and remove the
comment for your EXEC:
DECLARE @.tablename VARCHAR (128)
DECLARE @.execstr VARCHAR (255)
DECLARE @.objectid INT
DECLARE @.indexid INT
DECLARE @.frag DECIMAL
DECLARE @.IndexName VARCHAR (255)
DECLARE @.maxfrag DECIMAL
DECLARE @.CmdType VARCHAR (255)
-- Decide on the maximum fragmentation to allow
SELECT @.maxfrag = 5
-- Declare 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) NULL,
ObjectId INT NULL,
IndexName CHAR (255) NULL,
IndexId INT NULL,
Lvl INT NULL,
CountPages INT NULL,
CountRows INT NULL,
MinRecSize INT NULL,
MaxRecSize INT NULL,
AvgRecSize INT NULL,
ForRecCount INT NULL,
Extents INT NULL,
ExtentSwitches INT NULL,
AvgFreeBytes INT NULL,
AvgPageDensity INT NULL,
ScanDensity DECIMAL NULL,
BestCount INT NULL,
ActualCount INT NULL,
LogicalFrag DECIMAL NULL,
ExtentFrag DECIMAL NULL)
-- 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 cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT #fraglist. ObjectName
, #fraglist. ObjectId
, #fraglist. IndexId
, #fraglist. LogicalFrag
, #fraglist. IndexName
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
ORDER BY LogicalFrag desc, ObjectName asc
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Executing Check on (0, ' + RTRIM(@.tablename) + ', ' + RTRIM(@.IndexNam
e) + ') - fragmentation
currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
if @.frag > 75
select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM(@.tablename) + ',' + rtrim(
@.IndexName) + ', 0)'
else
if @.frag between 5 AND 75
SELECT @.cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ', ' + RTRIM(
@.indexid) + ')'
-- EXEC (@.cmdtype)
print @.cmdtype
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <anonymous@.discussions.microsoft.com> wrote in message news:1927901c44d03$1b2cad90$a60
1280a@.phx.gbl...
> I modified the DBCC INDEXDEFRAG script from BOL with the
> DBCC DBREINDEX. If the LogicalFragmentation is greater
> than 75 then DBCC REINDEX the index. If the
> LogicalFragmentation is between 5 and 75 then used the
> DBCC INDEXDEFRAG.
> I applied the script listed below to a database that has
> 1500 indexes that is heavy fragmented. The script on
> found approximately 75 indexes to reindex or defrag.
> Please help me with the missing indexes that need to be
> reindexed or defragmented.
> Thanks,
> Dan
> DECLARE @.tablename VARCHAR (128)
> DECLARE @.execstr VARCHAR (255)
> DECLARE @.objectid INT
> DECLARE @.indexid INT
> DECLARE @.frag DECIMAL
> DECLARE @.IndexName VARCHAR (255)
> DECLARE @.maxfrag DECIMAL
> DECLARE @.CmdType VARCHAR (255)
> -- Decide on the maximum fragmentation to allow
> SELECT @.maxfrag = 5
> -- Declare 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) NULL,
> ObjectId INT NULL,
> IndexName CHAR (255) NULL,
> IndexId INT NULL,
> Lvl INT NULL,
> CountPages INT NULL,
> CountRows INT NULL,
> MinRecSize INT NULL,
> MaxRecSize INT NULL,
> AvgRecSize INT NULL,
> ForRecCount INT NULL,
> Extents INT NULL,
> ExtentSwitches INT NULL,
> AvgFreeBytes INT NULL,
> AvgPageDensity INT NULL,
> ScanDensity DECIMAL NULL,
> BestCount INT NULL,
> ActualCount INT NULL,
> LogicalFrag DECIMAL NULL,
> ExtentFrag DECIMAL NULL)
> -- 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 cursor for list of indexes to be defragged
> DECLARE indexes CURSOR FOR
> SELECT #fraglist. ObjectName
> , #fraglist. ObjectId
> , #fraglist. IndexId
> , #fraglist. LogicalFrag
> , #fraglist. IndexName
> FROM #fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId,
> IndexName, 'IndexDepth') > 0
> ORDER BY LogicalFrag desc, ObjectName asc
> -- Open the cursor
> OPEN indexes
> -- loop through the indexes
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Executing Check on (0, ' + RTRIM(@.tablename)
> + ', ' + RTRIM(@.IndexName) + ') - fragmentation
> currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
> if @.frag > 75
> select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM
> (@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
> else
> if @.frag between 5 AND 75
> SELECT @.cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM
> (@.objectid) + ', ' + RTRIM(@.indexid) + ')'
> EXEC (@.execstr)
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag,
> @.IndexName
> END
> -- Close and deallocate the cursor
> CLOSE indexes
> DEALLOCATE indexes
> -- Delete the temporary table
> DROP TABLE #fraglist
> GO
>|||Dan
Run DBCC SHOWCONTIG to identify fragmentation.
For more details please refer to the BOL.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:1927901c44d03$1b2cad90$a601280a@.phx
.gbl...
> I modified the DBCC INDEXDEFRAG script from BOL with the
> DBCC DBREINDEX. If the LogicalFragmentation is greater
> than 75 then DBCC REINDEX the index. If the
> LogicalFragmentation is between 5 and 75 then used the
> DBCC INDEXDEFRAG.
> I applied the script listed below to a database that has
> 1500 indexes that is heavy fragmented. The script on
> found approximately 75 indexes to reindex or defrag.
> Please help me with the missing indexes that need to be
> reindexed or defragmented.
> Thanks,
> Dan
> DECLARE @.tablename VARCHAR (128)
> DECLARE @.execstr VARCHAR (255)
> DECLARE @.objectid INT
> DECLARE @.indexid INT
> DECLARE @.frag DECIMAL
> DECLARE @.IndexName VARCHAR (255)
> DECLARE @.maxfrag DECIMAL
> DECLARE @.CmdType VARCHAR (255)
> -- Decide on the maximum fragmentation to allow
> SELECT @.maxfrag = 5
> -- Declare 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) NULL,
> ObjectId INT NULL,
> IndexName CHAR (255) NULL,
> IndexId INT NULL,
> Lvl INT NULL,
> CountPages INT NULL,
> CountRows INT NULL,
> MinRecSize INT NULL,
> MaxRecSize INT NULL,
> AvgRecSize INT NULL,
> ForRecCount INT NULL,
> Extents INT NULL,
> ExtentSwitches INT NULL,
> AvgFreeBytes INT NULL,
> AvgPageDensity INT NULL,
> ScanDensity DECIMAL NULL,
> BestCount INT NULL,
> ActualCount INT NULL,
> LogicalFrag DECIMAL NULL,
> ExtentFrag DECIMAL NULL)
> -- 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 cursor for list of indexes to be defragged
> DECLARE indexes CURSOR FOR
> SELECT #fraglist. ObjectName
> , #fraglist. ObjectId
> , #fraglist. IndexId
> , #fraglist. LogicalFrag
> , #fraglist. IndexName
> FROM #fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId,
> IndexName, 'IndexDepth') > 0
> ORDER BY LogicalFrag desc, ObjectName asc
> -- Open the cursor
> OPEN indexes
> -- loop through the indexes
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Executing Check on (0, ' + RTRIM(@.tablename)
> + ', ' + RTRIM(@.IndexName) + ') - fragmentation
> currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
> if @.frag > 75
> select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM
> (@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
> else
> if @.frag between 5 AND 75
> SELECT @.cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM
> (@.objectid) + ', ' + RTRIM(@.indexid) + ')'
> EXEC (@.execstr)
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag,
> @.IndexName
> END
> -- Close and deallocate the cursor
> CLOSE indexes
> DEALLOCATE indexes
> -- Delete the temporary table
> DROP TABLE #fraglist
> GO
>

Wednesday, March 7, 2012

DBCC DBREINDEX vs DBCC INDEXDEFRAG

I am a little confused of which on does what by this Microsoft statement:
Unlike DBCC DBREINDEX or any general index build, DBCC INDEXDEFRAG is an
online operation, so it does not hold long-term locks that can block running
queries or updates. Depending on the amount of fragmentation, DBCC
INDEXDEFRAG can be considerably faster than running DBCC DBREINDEX because a
relatively unfragmented index can be defragmented much faster than a new
index can be built. Another advantage is that with DBCC INDEXDEFRAG, the
index is always available, unlike DBREINDEX. A large amount of fragmentation
can cause DBCC INDEXDEFRAG to run considerably longer than DBCC DBREINDEX,
which may or may not outweigh the benefit of the command's online
capabilities.
Which one is safe to use when users are logged in?
Thx,
DaveIn addition to Russel's excellent post, have a look at
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/Optimize/SS2KIDBP.asp
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dave" <captan@.hotmail.com> wrote in message
news:%23GFGvXvSDHA.1920@.TK2MSFTNGP11.phx.gbl...
I am a little confused of which on does what by this Microsoft statement:
Unlike DBCC DBREINDEX or any general index build, DBCC INDEXDEFRAG is an
online operation, so it does not hold long-term locks that can block running
queries or updates. Depending on the amount of fragmentation, DBCC
INDEXDEFRAG can be considerably faster than running DBCC DBREINDEX because a
relatively unfragmented index can be defragmented much faster than a new
index can be built. Another advantage is that with DBCC INDEXDEFRAG, the
index is always available, unlike DBREINDEX. A large amount of fragmentation
can cause DBCC INDEXDEFRAG to run considerably longer than DBCC DBREINDEX,
which may or may not outweigh the benefit of the command's online
capabilities.
Which one is safe to use when users are logged in?
Thx,
Dave

DBCC DBREINDEX unexpected results

Normally, after I use DBCC DBREINDEX, I can be sure that Scan Density on a clustered or non-clustered index is very good - eg. 99% or 100%. However, I have one database where there are a number of indexes that are not showing any improvement in Scan Density after running DBCC DBREINDEX. In on case, a clustered index, I run it on two days in succession and Scan Density actually go worse! Can anyone give me a reason for this? Can anyone suggest how to fix it?

CliveI think I found the problem. Look like a newly modified maintenance proc wasn't doing what it should have been.

Clive

dbcc dbreindex question

Is this command, which I know can be executed on a per-table basis,
the same as the Rebuild Index Task in the SQL Server Management Studio
for maintenance tasks??

The documentation I have mentions the reindex.sql script, is this the
script that is executed by the Rebuild Index Task??

Thank you, Tomtlyczko (tlyczko@.gmail.com) writes:

Quote:

Originally Posted by

Is this command, which I know can be executed on a per-table basis,
the same as the Rebuild Index Task in the SQL Server Management Studio
for maintenance tasks??

I would guess the maintenance task uses ALTER INDEX REBUILD which is a
more modern version of DBCC DBREBUILD, at least in terms of syntax.

I have not worked much with maintenance plans, but since they SSIS
packages, you should be able to look what's on the inside with help
of the Business Intelligence Development Studio.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

dbcc dbreindex problems

sql2k sp3
When running:
dbcc dbreindex ('dev.dbo.transdtl')
I get:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 1. Most significant primary key is '8216076'.
The statement has been terminated.
Now this doesn't make sense. TransDTLKey is the PK column for this table. No
other unique constraints are defined. So I:
select transdtlkey
from transdtl
group by transdtlkey
having count(transdtlkey) > 1
and get back 0 records.
Ideas?
TIA, ChrisRSmells like data corruption to me. Did you DBCC CHECKTABLE? Also, I vaguely recall this (I might be
have been dreaming, though), I'd search the newsgroups archive as well...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:2C94CEF3-9E9C-4C11-8EE6-B302CEC8ABBD@.microsoft.com...
> sql2k sp3
> When running:
> dbcc dbreindex ('dev.dbo.transdtl')
> I get:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 1. Most significant primary key is '8216076'.
> The statement has been terminated.
> Now this doesn't make sense. TransDTLKey is the PK column for this table. No
> other unique constraints are defined. So I:
> select transdtlkey
> from transdtl
> group by transdtlkey
> having count(transdtlkey) > 1
> and get back 0 records.
>
> Ideas?
> TIA, ChrisR|||You are correct:
Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 2511: [Microsoft][ODBC SQL
Server Driver][SQL Server]Table error: Object ID 1945773989, Index ID 0. Keys
out of order on page (1:817776), slots 57 and 58.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
1945773989, Index ID 16. Keys out of order on page (1:637458), slots 128 and
129.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 2 consistency errors in table 'TransDtl' (object ID 1945773989).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 2 consistency errors in database 'DEV'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_rebuild is the minimum
repair level for the errors found by DBCC CHECKDB (DEV ).
The problem is though that I ran dbcc checkdb with repair_rebuild and it
errors out because of this issue. So the Checkdb cant fix it and neither can
the Reindex. Any other ideas? A few years back at another job we had to:
Put the data into new tables.
Drop the old table.
Rename the new table.
to get rid of other strange behavior and it seemed to do the trick. I may
try this again here.
"Tibor Karaszi" wrote:
> Smells like data corruption to me. Did you DBCC CHECKTABLE? Also, I vaguely recall this (I might be
> have been dreaming, though), I'd search the newsgroups archive as well...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:2C94CEF3-9E9C-4C11-8EE6-B302CEC8ABBD@.microsoft.com...
> > sql2k sp3
> >
> > When running:
> >
> > dbcc dbreindex ('dev.dbo.transdtl')
> >
> > I get:
> >
> > Server: Msg 1505, Level 16, State 1, Line 1
> > CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> > ID 1. Most significant primary key is '8216076'.
> > The statement has been terminated.
> >
> > Now this doesn't make sense. TransDTLKey is the PK column for this table. No
> > other unique constraints are defined. So I:
> >
> > select transdtlkey
> > from transdtl
> > group by transdtlkey
> > having count(transdtlkey) > 1
> >
> > and get back 0 records.
> >
> >
> > Ideas?
> >
> > TIA, ChrisR
>
>|||ChrisR wrote:
> You are correct:
> Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 2511:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
> 1945773989, Index ID 0. Keys out of order on page (1:817776), slots
> 57 and 58. [Microsoft][ODBC SQL Server Driver][SQL Server]Table
> error: Object ID 1945773989, Index ID 16. Keys out of order on page
> (1:637458), slots 128 and 129.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0
> allocation errors and 2 consistency errors in table 'TransDtl'
> (object ID 1945773989). [Microsoft][ODBC SQL Server Driver][SQL
> Server]CHECKDB found 0 allocation errors and 2 consistency errors in
> database 'DEV'. [Microsoft][ODBC SQL Server Driver][SQL
> Server]repair_rebuild is the minimum repair level for the errors
> found by DBCC CHECKDB (DEV ).
>
> The problem is though that I ran dbcc checkdb with repair_rebuild and
> it errors out because of this issue. So the Checkdb cant fix it and
> neither can the Reindex. Any other ideas? A few years back at another
> job we had to:
> Put the data into new tables.
> Drop the old table.
> Rename the new table.
> to get rid of other strange behavior and it seemed to do the trick. I
> may try this again here.
>
If you script out the table and data, can you try using the
REPAIR_ALLOW_DATA_LOSS option with dbcc checkdb. You can do this under a
user transaction to examine the differences (or data loss) when compared
to the copy of the table. If you find the repairs sufficient, you can
update rows from the table copy and then insert missing rows from the
table copy to get the tables in sync.
You obviously want to script out this whole procedure before you commit
to tying up production data for too long. OTOH, if your production table
is corrupt, the sooner you fix it, the better.
--
David Gugick
Imceda Software
www.imceda.com|||Im lucky in that this is a development db.
"David Gugick" wrote:
> ChrisR wrote:
> > You are correct:
> >
> > Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 2511:
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
> > 1945773989, Index ID 0. Keys out of order on page (1:817776), slots
> > 57 and 58. [Microsoft][ODBC SQL Server Driver][SQL Server]Table
> > error: Object ID 1945773989, Index ID 16. Keys out of order on page
> > (1:637458), slots 128 and 129.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0
> > allocation errors and 2 consistency errors in table 'TransDtl'
> > (object ID 1945773989). [Microsoft][ODBC SQL Server Driver][SQL
> > Server]CHECKDB found 0 allocation errors and 2 consistency errors in
> > database 'DEV'. [Microsoft][ODBC SQL Server Driver][SQL
> > Server]repair_rebuild is the minimum repair level for the errors
> > found by DBCC CHECKDB (DEV ).
> >
> >
> > The problem is though that I ran dbcc checkdb with repair_rebuild and
> > it errors out because of this issue. So the Checkdb cant fix it and
> > neither can the Reindex. Any other ideas? A few years back at another
> > job we had to:
> >
> > Put the data into new tables.
> > Drop the old table.
> > Rename the new table.
> >
> > to get rid of other strange behavior and it seemed to do the trick. I
> > may try this again here.
> >
> >
> If you script out the table and data, can you try using the
> REPAIR_ALLOW_DATA_LOSS option with dbcc checkdb. You can do this under a
> user transaction to examine the differences (or data loss) when compared
> to the copy of the table. If you find the repairs sufficient, you can
> update rows from the table copy and then insert missing rows from the
> table copy to get the tables in sync.
> You obviously want to script out this whole procedure before you commit
> to tying up production data for too long. OTOH, if your production table
> is corrupt, the sooner you fix it, the better.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||1. Read about the specific error numbers in the updated Books Online. The update includes detailed
explanations for all corruption types of errors, including suggested actions. Execute the DBCC CHECK
command from QA so you get all error numbers back (Maint Wiz only return error number from first
error that a command generates).
2. Generic recommendations:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:BE9354B5-AB7D-407E-ABCB-60E8A46233F2@.microsoft.com...
> You are correct:
> Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 2511: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Table error: Object ID 1945773989, Index ID 0. Keys
> out of order on page (1:817776), slots 57 and 58.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
> 1945773989, Index ID 16. Keys out of order on page (1:637458), slots 128 and
> 129.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
> errors and 2 consistency errors in table 'TransDtl' (object ID 1945773989).
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
> errors and 2 consistency errors in database 'DEV'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_rebuild is the minimum
> repair level for the errors found by DBCC CHECKDB (DEV ).
>
> The problem is though that I ran dbcc checkdb with repair_rebuild and it
> errors out because of this issue. So the Checkdb cant fix it and neither can
> the Reindex. Any other ideas? A few years back at another job we had to:
> Put the data into new tables.
> Drop the old table.
> Rename the new table.
> to get rid of other strange behavior and it seemed to do the trick. I may
> try this again here.
>
> "Tibor Karaszi" wrote:
>> Smells like data corruption to me. Did you DBCC CHECKTABLE? Also, I vaguely recall this (I might
>> be
>> have been dreaming, though), I'd search the newsgroups archive as well...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> news:2C94CEF3-9E9C-4C11-8EE6-B302CEC8ABBD@.microsoft.com...
>> > sql2k sp3
>> >
>> > When running:
>> >
>> > dbcc dbreindex ('dev.dbo.transdtl')
>> >
>> > I get:
>> >
>> > Server: Msg 1505, Level 16, State 1, Line 1
>> > CREATE UNIQUE INDEX terminated because a duplicate key was found for index
>> > ID 1. Most significant primary key is '8216076'.
>> > The statement has been terminated.
>> >
>> > Now this doesn't make sense. TransDTLKey is the PK column for this table. No
>> > other unique constraints are defined. So I:
>> >
>> > select transdtlkey
>> > from transdtl
>> > group by transdtlkey
>> > having count(transdtlkey) > 1
>> >
>> > and get back 0 records.
>> >
>> >
>> > Ideas?
>> >
>> > TIA, ChrisR
>>

Tuesday, March 27, 2012

DBCC SHRINKDATABASE

Sunday, March 25, 2012

dbcc showcontig with tablersults

DBCC SHOWCONTIG Performances

DBCC SHOWCONTIG - Please Help

Thursday, March 22, 2012

Dbcc Showcontig

DBCC SHOWCONTIG

DBCC SHOWCONTIG

Sunday, March 11, 2012

DBCC INDEXDEFRAG

Thursday, March 8, 2012

DBCC INDEXDEFRAG

DBCC INDEXDEFRAG

dbcc error 2511

DBCC DBREINDEX\INDEXDEFRAG

DBCC DBREINDEX\INDEXDEFRAG

DBCC DBREINDEX\INDEXDEFRAG

Wednesday, March 7, 2012

DBCC DBREINDEX vs DBCC INDEXDEFRAG

DBCC DBREINDEX unexpected results

dbcc dbreindex question

dbcc dbreindex problems

DBfile usage

Blog Archive

About Me