DBfile usage: all

Showing posts with label all_indexes. Show all posts

Sunday, March 25, 2012

DBCC SHOWCONTIG and Extent Switches

All,
I executed the following statement at my production server.
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
which provided me the following resultset. Besides "AveragePageDensity" and
"LogicalFragmentation", I paid special attention to "Extent Swithces" which
is quite high.
Is there any performance gain if I reduce th Extent Switces? How can I
lessen the value of Extent Switches.
TIA
Kay
ObjectName ObjectId IndexName
IndexId
Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
89.321 84.615 66 78 1.898 18.056
package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
99.541 92.857 39 42 1.307 16.667
package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
99.772 96.078 49 51 0.258 13.725
package_description 1450240717 PK_package_description 1 0 3226 169022
44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
package_description 1450240717 package_description73 2 0 296 169022 12
12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
package_description 1450240717 idx_package_state 4 0 341 169022 12 12
12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
package_description 1450240717 idx_package_available 7 0 298 169022 12
12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
package_description 1450240717 tpackage_description 255 0 114597
340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
99.999 17.153
pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
100.000 1 1 0.000 0.000Kay
http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
"Kay" <CallDBA@.hotmail.com> wrote in message
news:OmnmxiiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity"
> and "LogicalFragmentation", I paid special attention to "Extent Swithces"
> which is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes
> AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation
> ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022
> 12 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022
> 12 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>|||Based on the result set provided and viewing the clustered index, your extent
switches are fine. Extent switches is the number of times the DBCC statement
moved off an extent while it was scanning the pages in the extent. You would
expect an extent switch to happen after the whole extent had been scanned.
The most useful line of output would be the ScanDensity BestCount
ActualCount. This is your measure of fragmentation. The best count is the
ideal number of extents, where as the actual count is the actual number of
extents use to hold the data pages.
I noticed the AveragePageDensity is around 89.321, which indicates a
fillfactor of 90 set for the clustered index. In a clustered index, since the
leaf level contains the data, you can use FILLFACTOR to control how much
space to leave in the table itself. By reserving free space, you can avoid
splitting pages to make room for a new entry. NOTE that FILLFACTOR is not
maintained; it only indicates how much space is reserved with the existing
data at the time the index is built. If you need to, you can use the DBCC
DBREINDEX command to rebuild the index and reestablish the original
FILLFACTOR specified, also reducing fragmentation.
"Kay" wrote:
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity" and
> "LogicalFragmentation", I paid special attention to "Extent Swithces" which
> is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
> ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022 12
> 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022 12
> 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>
>

DBCC SHOWCONTIG & DB Performance

Hi,
If I run DBCC SHOWCONTIG WITH FAST, TABLERESULTS, ALL_INDEXES on our DB I
get info back our indexes. As I understand it, an indexid = 0 is the index
representing the heap of a table with no clustered index, so we don't need t
o
worry about the scandensity or fragmentation of these for db performance.
Indexid's 1-254 are actual indexes & may need looking at if low scandensity
or high fragmentation.
What does an index with an indexid = 255 represent though, and do they have
an impact on performance?
TIASteve,
Indid = 255 is the entry for tables with text / image columns, for example,
if you create table t(colA text), then you will have two entries in table
sysindexes for this table idnid = 0 and indid = 255. I do not know if we
should defrag indid = 255.
AMB
"Steve" wrote:

> Hi,
> If I run DBCC SHOWCONTIG WITH FAST, TABLERESULTS, ALL_INDEXES on our DB I
> get info back our indexes. As I understand it, an indexid = 0 is the index
> representing the heap of a table with no clustered index, so we don't need
to
> worry about the scandensity or fragmentation of these for db performance.
> Indexid's 1-254 are actual indexes & may need looking at if low scandensit
y
> or high fragmentation.
> What does an index with an indexid = 255 represent though, and do they hav
e
> an impact on performance?
> TIAsql

Thursday, March 22, 2012

DBCC SHOWCONTIG

I've been running above procedure using the "with all_indexes, fast;" option
on a table in my database. This table has 18 indexes. The results in this
procedure refer to index 1, 2, ..., 18. I don't relate to ID number as my
indexes all have english names (e.g. Account, LastName, etc.). I'm not sure
which index ID is referring to what named index. Are these ID numbers simply
the order in which they appear when I see the list in Enterprise Manager,
Manage Indexes option? Is there a way to have the DBCC SHOWCONTIG procedure
reflect the actual index names I had assigned?
Thanks, Jim
Hi Jim
What version are you using? In SQL 2005 there is a replacement for DBCC
SHOWCONTIG that allows you to filter the results and add more information to
the output. The output is much more readable, too. DBCC is not a procedure,
so there is very little you can do to modify how it works.
You cannot assume the index numbers are the same as the order the indexes
come back in the EM list. You can run this query to see what number goes
with what index: (substitute your own table name, of course)
SELECT indid, name
FROM sysindexes
WHERE id = object_id('your-table-name')
AND indexproperty(id,name, 'isStatistics') = 0
AND indexproperty(id,name, 'isHypothetical') = 0
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jim B" <JB@.lightning.com> wrote in message
news:1BE95CD5-1977-47C9-8778-57DBA4A9F183@.microsoft.com...
> I've been running above procedure using the "with all_indexes, fast;"
> option
> on a table in my database. This table has 18 indexes. The results in
> this
> procedure refer to index 1, 2, ..., 18. I don't relate to ID number as my
> indexes all have english names (e.g. Account, LastName, etc.). I'm not
> sure
> which index ID is referring to what named index. Are these ID numbers
> simply
> the order in which they appear when I see the list in Enterprise Manager,
> Manage Indexes option? Is there a way to have the DBCC SHOWCONTIG
> procedure
> reflect the actual index names I had assigned?
> --
> Thanks, Jim
|||Kalen. Thanks for the quick reply. I have to to support sites that use
either SQL2000 or SQL2005. What would be the better option to DBCC
SHOWCONTIG that you mentioned for SQL2005. The query you gave me worked
great in both 2000 and 2005. Thanks again for help.
Thanks, Jim
"Kalen Delaney" wrote:

> Hi Jim
> What version are you using? In SQL 2005 there is a replacement for DBCC
> SHOWCONTIG that allows you to filter the results and add more information to
> the output. The output is much more readable, too. DBCC is not a procedure,
> so there is very little you can do to modify how it works.
> You cannot assume the index numbers are the same as the order the indexes
> come back in the EM list. You can run this query to see what number goes
> with what index: (substitute your own table name, of course)
> SELECT indid, name
> FROM sysindexes
> WHERE id = object_id('your-table-name')
> AND indexproperty(id,name, 'isStatistics') = 0
> AND indexproperty(id,name, 'isHypothetical') = 0
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Jim B" <JB@.lightning.com> wrote in message
> news:1BE95CD5-1977-47C9-8778-57DBA4A9F183@.microsoft.com...
>
>
|||Jim
In SQL 2005 there is a table valued function called
sys.dm_db_index_physical_stats. It returns a LOT of information, but you can
filter it in any way you like, and since it returns table results, you can
join it to the sys.indexes to get the index name, and you can restrict the
columns coming back to just what you need.
You should read about it in BOL first, and if you have a SQL Server Magazine
subscription, you can find a couple of articles I wrote about it on their
site.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jim B" <JB@.lightning.com> wrote in message
news:1E20DDE1-6435-4ED1-A4F4-E7402C3B05B2@.microsoft.com...[vbcol=seagreen]
> Kalen. Thanks for the quick reply. I have to to support sites that use
> either SQL2000 or SQL2005. What would be the better option to DBCC
> SHOWCONTIG that you mentioned for SQL2005. The query you gave me worked
> great in both 2000 and 2005. Thanks again for help.
> --
> Thanks, Jim
>
> "Kalen Delaney" wrote:

dbcc showcontig

When I run command: dbcc showcontig with all_indexes, I got a lot of indexes w/ high logical fragmentation
So I ran dbcc dbreindex on all the indexes
When I re-ran showcontig to verify each table individually, it reported 0% frag per index
For one last time, I ran a full report of the entire database, the logicalfrag quoted the original numbers of 100%.
Why would dbcc showcontig all_indexes and dbcc showcontig (tblname) all_indexes have different results for logicalfrag100% fragmentation is only possible with tiny indexes (i.e. several pages).
Can you give some examples of the output you're comparing?
You don't need to fix fragmentation on all indexes - it depends on your
access patterns whether fragmentation will affect your performance. Read the
whitepaper below for full details:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Achieng O" <snow.barb@.verizon.net> wrote in message
news:AEA21E32-7345-4600-B116-434F1D3E398D@.microsoft.com...
> When I run command: dbcc showcontig with all_indexes, I got a lot of
indexes w/ high logical fragmentation.
> So I ran dbcc dbreindex on all the indexes.
> When I re-ran showcontig to verify each table individually, it reported 0%
frag per index.
> For one last time, I ran a full report of the entire database, the
logicalfrag quoted the original numbers of 100%.
> Why would dbcc showcontig all_indexes and dbcc showcontig (tblname)
all_indexes have different results for logicalfrag?
>|||***Here's the result w/ dbcc showcontig with all_indexes****
DBCC SHOWCONTIG scanning 'DEDUCTRULE' table...
Table: 'DEDUCTRULE' (158623608); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6797.0
- Avg. Page Density (full)................: 16.02%
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 5335.0
- Avg. Page Density (full)................: 34.09%
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7207.0
- Avg. Page Density (full)................: 10.96%
****BUT WHEN I RUN dbcc showcontig (ACCESSPROFILE) with all_indexes****
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 5335.0
- Avg. Page Density (full)................: 34.09%
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7207.0
- Avg. Page Density (full)................: 10.96%
****HOW COME THE STATS FOR LOGICALFRAG CHANGED?*****
Paul S Randal [MS] wrote:
> 100% fragmentation is only possible with tiny indexes (i.e. several pages).
> Can you give some examples of the output you're comparing?
> You don't need to fix fragmentation on all indexes - it depends on your
> access patterns whether fragmentation will affect your performance. Read the
> whitepaper below for full details:
> http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> Regards.
>|||Interesting - there must be a corner case in one of the algorithms for
single-page indexes, but that's not something I'd consider fixing in an SP
as you shouldn't even bother about fragmentation with an index under say
1000 pages - it's just not worth it.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Achieng O" <snow.barb@.verizon.net> wrote in message
news:#gC0eCO7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> ***Here's the result w/ dbcc showcontig with all_indexes****
> DBCC SHOWCONTIG scanning 'DEDUCTRULE' table...
> Table: 'DEDUCTRULE' (158623608); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 6797.0
> - Avg. Page Density (full)................: 16.02%
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 100.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 5335.0
> - Avg. Page Density (full)................: 34.09%
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7207.0
> - Avg. Page Density (full)................: 10.96%
> ****BUT WHEN I RUN dbcc showcontig (ACCESSPROFILE) with all_indexes****
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 5335.0
> - Avg. Page Density (full)................: 34.09%
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7207.0
> - Avg. Page Density (full)................: 10.96%
> ****HOW COME THE STATS FOR LOGICALFRAG CHANGED?*****
> Paul S Randal [MS] wrote:
> > 100% fragmentation is only possible with tiny indexes (i.e. several
pages).
> > Can you give some examples of the output you're comparing?
> >
> > You don't need to fix fragmentation on all indexes - it depends on your
> > access patterns whether fragmentation will affect your performance. Read
the
> > whitepaper below for full details:
> >
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> >
> > Regards.
> >
>

dbcc showcontig

When I run command: dbcc showcontig with all_indexes, I got a lot of indexe
s w/ high logical fragmentation.
So I ran dbcc dbreindex on all the indexes.
When I re-ran showcontig to verify each table individually, it reported 0% f
rag per index.
For one last time, I ran a full report of the entire database, the logicalfr
ag quoted the original numbers of 100%.
Why would dbcc showcontig all_indexes and dbcc showcontig (tblname) all_inde
xes have different results for logicalfrag?100% fragmentation is only possible with tiny indexes (i.e. several pages).
Can you give some examples of the output you're comparing?
You don't need to fix fragmentation on all indexes - it depends on your
access patterns whether fragmentation will affect your performance. Read the
whitepaper below for full details:
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Achieng O" <snow.barb@.verizon.net> wrote in message
news:AEA21E32-7345-4600-B116-434F1D3E398D@.microsoft.com...
> When I run command: dbcc showcontig with all_indexes, I got a lot of
indexes w/ high logical fragmentation.
> So I ran dbcc dbreindex on all the indexes.
> When I re-ran showcontig to verify each table individually, it reported 0%
frag per index.
> For one last time, I ran a full report of the entire database, the
logicalfrag quoted the original numbers of 100%.
> Why would dbcc showcontig all_indexes and dbcc showcontig (tblname)
all_indexes have different results for logicalfrag?
>|||***Here's the result w/ dbcc showcontig with all_indexes****
DBCC SHOWCONTIG scanning 'DEDUCTRULE' table...
Table: 'DEDUCTRULE' (158623608); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6797.0
- Avg. Page Density (full)................: 16.02%
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 5335.0
- Avg. Page Density (full)................: 34.09%
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7207.0
- Avg. Page Density (full)................: 10.96%
****BUT WHEN I RUN dbcc showcontig (ACCESSPROFILE) with all_indexes****
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 5335.0
- Avg. Page Density (full)................: 34.09%
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7207.0
- Avg. Page Density (full)................: 10.96%
****HOW COME THE STATS FOR LOGICALFRAG CHANGED?*****
Paul S Randal [MS] wrote:
> 100% fragmentation is only possible with tiny indexes (i.e. several pages)
.
> Can you give some examples of the output you're comparing?
> You don't need to fix fragmentation on all indexes - it depends on your
> access patterns whether fragmentation will affect your performance. Read t
he
> whitepaper below for full details:
> http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
> Regards.
>|||Interesting - there must be a corner case in one of the algorithms for
single-page indexes, but that's not something I'd consider fixing in an SP
as you shouldn't even bother about fragmentation with an index under say
1000 pages - it's just not worth it.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Achieng O" <snow.barb@.verizon.net> wrote in message
news:#gC0eCO7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> ***Here's the result w/ dbcc showcontig with all_indexes****
> DBCC SHOWCONTIG scanning 'DEDUCTRULE' table...
> Table: 'DEDUCTRULE' (158623608); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 6797.0
> - Avg. Page Density (full)................: 16.02%
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 100.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 5335.0
> - Avg. Page Density (full)................: 34.09%
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7207.0
> - Avg. Page Density (full)................: 10.96%
> ****BUT WHEN I RUN dbcc showcontig (ACCESSPROFILE) with all_indexes****
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 5335.0
> - Avg. Page Density (full)................: 34.09%
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7207.0
> - Avg. Page Density (full)................: 10.96%
> ****HOW COME THE STATS FOR LOGICALFRAG CHANGED?*****
> Paul S Randal [MS] wrote:
pages).
the
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
>

Saturday, February 25, 2012

DBCC DBREINDEX

When I run DBCC SHOWCONTIG (tablename) WITH ALL_INDEXES the first index shows
Table: 'tablename' (437576597); index ID: 0, database ID: 8
TABLE level scan performed.
- Pages Scanned........................: 15721
- Extents Scanned.......................: 2027
- Extent Switches.......................: 2026
- Avg. Pages per Extent..................: 7.8
- Scan Density [Best Count:Actual Count]......: 96.99% [1966:2027]
- Extent Scan Fragmentation ...............: 43.96%
- Avg. Bytes Free per Page................: 202.3
- Avg. Page Density (full)................: 97.
When I run DBCC DBREINDEX (tablename,'',0) it cleans up the other indexes
but the first on remains unchanged. Any ideas? TIA PS: All the indexes are
nonclusteredThat is because index 0 is not an index at all. Index ID of 0 indicates
this is a HEAP. Heaps can not be reindexed. You should add a clustered
index to the table and that will change the ID from 0 to 1 and allow you to
defragment and reindex the table.
--
Andrew J. Kelly SQL MVP
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:A5312425-B102-4CA3-8E78-8BD00B246EFB@.microsoft.com...
> When I run DBCC SHOWCONTIG (tablename) WITH ALL_INDEXES the first index
> shows
> Table: 'tablename' (437576597); index ID: 0, database ID: 8
> TABLE level scan performed.
> - Pages Scanned........................: 15721
> - Extents Scanned.......................: 2027
> - Extent Switches.......................: 2026
> - Avg. Pages per Extent..................: 7.8
> - Scan Density [Best Count:Actual Count]......: 96.99% [1966:2027]
> - Extent Scan Fragmentation ...............: 43.96%
> - Avg. Bytes Free per Page................: 202.3
> - Avg. Page Density (full)................: 97.
> When I run DBCC DBREINDEX (tablename,'',0) it cleans up the other indexes
> but the first on remains unchanged. Any ideas? TIA PS: All the indexes
> are
> nonclustered|||Thanks for your response. A follow up question. Does a heap eventually get so
fragmented that it gets corrupt? Is there a way to defrag or reindex a heap
or do we need to go to clustered indexes. TIA
"Andrew J. Kelly" wrote:
> That is because index 0 is not an index at all. Index ID of 0 indicates
> this is a HEAP. Heaps can not be reindexed. You should add a clustered
> index to the table and that will change the ID from 0 to 1 and allow you to
> defragment and reindex the table.
> --
> Andrew J. Kelly SQL MVP
>
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:A5312425-B102-4CA3-8E78-8BD00B246EFB@.microsoft.com...
> > When I run DBCC SHOWCONTIG (tablename) WITH ALL_INDEXES the first index
> > shows
> >
> > Table: 'tablename' (437576597); index ID: 0, database ID: 8
> > TABLE level scan performed.
> > - Pages Scanned........................: 15721
> > - Extents Scanned.......................: 2027
> > - Extent Switches.......................: 2026
> > - Avg. Pages per Extent..................: 7.8
> > - Scan Density [Best Count:Actual Count]......: 96.99% [1966:2027]
> > - Extent Scan Fragmentation ...............: 43.96%
> > - Avg. Bytes Free per Page................: 202.3
> > - Avg. Page Density (full)................: 97.
> >
> > When I run DBCC DBREINDEX (tablename,'',0) it cleans up the other indexes
> > but the first on remains unchanged. Any ideas? TIA PS: All the indexes
> > are
> > nonclustered
>
>|||Fragmentation does not cause corruption but it can affect performance. The
only way to defrag a HEAP is to export all the data, truncate the table and
import it back in again. Or you can create a clustered index and then drop
it. But it will immediately start to get fragmented again with more
activity. Bottom line is that the recommendation is to have a clustered
index on each and every table. Have a look at this:
http://www.sql-server-performance.com/gv_clustered_indexes.asp
Andrew J. Kelly SQL MVP
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:710E38A1-BE1E-4753-A1D5-D371F9416E7B@.microsoft.com...
> Thanks for your response. A follow up question. Does a heap eventually get
> so
> fragmented that it gets corrupt? Is there a way to defrag or reindex a
> heap
> or do we need to go to clustered indexes. TIA
> "Andrew J. Kelly" wrote:
>> That is because index 0 is not an index at all. Index ID of 0 indicates
>> this is a HEAP. Heaps can not be reindexed. You should add a clustered
>> index to the table and that will change the ID from 0 to 1 and allow you
>> to
>> defragment and reindex the table.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Greg" <Greg@.discussions.microsoft.com> wrote in message
>> news:A5312425-B102-4CA3-8E78-8BD00B246EFB@.microsoft.com...
>> > When I run DBCC SHOWCONTIG (tablename) WITH ALL_INDEXES the first index
>> > shows
>> >
>> > Table: 'tablename' (437576597); index ID: 0, database ID: 8
>> > TABLE level scan performed.
>> > - Pages Scanned........................: 15721
>> > - Extents Scanned.......................: 2027
>> > - Extent Switches.......................: 2026
>> > - Avg. Pages per Extent..................: 7.8
>> > - Scan Density [Best Count:Actual Count]......: 96.99% [1966:2027]
>> > - Extent Scan Fragmentation ...............: 43.96%
>> > - Avg. Bytes Free per Page................: 202.3
>> > - Avg. Page Density (full)................: 97.
>> >
>> > When I run DBCC DBREINDEX (tablename,'',0) it cleans up the other
>> > indexes
>> > but the first on remains unchanged. Any ideas? TIA PS: All the indexes
>> > are
>> > nonclustered
>>|||Also check out the whitepaper below which explains the options for removing
fragmentation and when it actually makes sense to remove it.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Thanks
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u5KKBAYEGHA.524@.TK2MSFTNGP09.phx.gbl...
> Fragmentation does not cause corruption but it can affect performance. The
> only way to defrag a HEAP is to export all the data, truncate the table
> and import it back in again. Or you can create a clustered index and then
> drop it. But it will immediately start to get fragmented again with more
> activity. Bottom line is that the recommendation is to have a clustered
> index on each and every table. Have a look at this:
> http://www.sql-server-performance.com/gv_clustered_indexes.asp
>
> --
> Andrew J. Kelly SQL MVP
>
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:710E38A1-BE1E-4753-A1D5-D371F9416E7B@.microsoft.com...
>> Thanks for your response. A follow up question. Does a heap eventually
>> get so
>> fragmented that it gets corrupt? Is there a way to defrag or reindex a
>> heap
>> or do we need to go to clustered indexes. TIA
>> "Andrew J. Kelly" wrote:
>> That is because index 0 is not an index at all. Index ID of 0 indicates
>> this is a HEAP. Heaps can not be reindexed. You should add a clustered
>> index to the table and that will change the ID from 0 to 1 and allow you
>> to
>> defragment and reindex the table.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Greg" <Greg@.discussions.microsoft.com> wrote in message
>> news:A5312425-B102-4CA3-8E78-8BD00B246EFB@.microsoft.com...
>> > When I run DBCC SHOWCONTIG (tablename) WITH ALL_INDEXES the first
>> > index
>> > shows
>> >
>> > Table: 'tablename' (437576597); index ID: 0, database ID: 8
>> > TABLE level scan performed.
>> > - Pages Scanned........................: 15721
>> > - Extents Scanned.......................: 2027
>> > - Extent Switches.......................: 2026
>> > - Avg. Pages per Extent..................: 7.8
>> > - Scan Density [Best Count:Actual Count]......: 96.99% [1966:2027]
>> > - Extent Scan Fragmentation ...............: 43.96%
>> > - Avg. Bytes Free per Page................: 202.3
>> > - Avg. Page Density (full)................: 97.
>> >
>> > When I run DBCC DBREINDEX (tablename,'',0) it cleans up the other
>> > indexes
>> > but the first on remains unchanged. Any ideas? TIA PS: All the
>> > indexes
>> > are
>> > nonclustered
>>
>|||Thanks for your response and I will check out the article.
"Paul S Randal [MS]" wrote:
> Also check out the whitepaper below which explains the options for removing
> fragmentation and when it actually makes sense to remove it.
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> Thanks
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u5KKBAYEGHA.524@.TK2MSFTNGP09.phx.gbl...
> > Fragmentation does not cause corruption but it can affect performance. The
> > only way to defrag a HEAP is to export all the data, truncate the table
> > and import it back in again. Or you can create a clustered index and then
> > drop it. But it will immediately start to get fragmented again with more
> > activity. Bottom line is that the recommendation is to have a clustered
> > index on each and every table. Have a look at this:
> >
> > http://www.sql-server-performance.com/gv_clustered_indexes.asp
> >
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Greg" <Greg@.discussions.microsoft.com> wrote in message
> > news:710E38A1-BE1E-4753-A1D5-D371F9416E7B@.microsoft.com...
> >> Thanks for your response. A follow up question. Does a heap eventually
> >> get so
> >> fragmented that it gets corrupt? Is there a way to defrag or reindex a
> >> heap
> >> or do we need to go to clustered indexes. TIA
> >>
> >> "Andrew J. Kelly" wrote:
> >>
> >> That is because index 0 is not an index at all. Index ID of 0 indicates
> >> this is a HEAP. Heaps can not be reindexed. You should add a clustered
> >> index to the table and that will change the ID from 0 to 1 and allow you
> >> to
> >> defragment and reindex the table.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> >> news:A5312425-B102-4CA3-8E78-8BD00B246EFB@.microsoft.com...
> >> > When I run DBCC SHOWCONTIG (tablename) WITH ALL_INDEXES the first
> >> > index
> >> > shows
> >> >
> >> > Table: 'tablename' (437576597); index ID: 0, database ID: 8
> >> > TABLE level scan performed.
> >> > - Pages Scanned........................: 15721
> >> > - Extents Scanned.......................: 2027
> >> > - Extent Switches.......................: 2026
> >> > - Avg. Pages per Extent..................: 7.8
> >> > - Scan Density [Best Count:Actual Count]......: 96.99% [1966:2027]
> >> > - Extent Scan Fragmentation ...............: 43.96%
> >> > - Avg. Bytes Free per Page................: 202.3
> >> > - Avg. Page Density (full)................: 97.
> >> >
> >> > When I run DBCC DBREINDEX (tablename,'',0) it cleans up the other
> >> > indexes
> >> > but the first on remains unchanged. Any ideas? TIA PS: All the
> >> > indexes
> >> > are
> >> > nonclustered
> >>
> >>
> >>
> >
> >
>
>|||Thanks for your response and I will check out the article.
"Andrew J. Kelly" wrote:
> Fragmentation does not cause corruption but it can affect performance. The
> only way to defrag a HEAP is to export all the data, truncate the table and
> import it back in again. Or you can create a clustered index and then drop
> it. But it will immediately start to get fragmented again with more
> activity. Bottom line is that the recommendation is to have a clustered
> index on each and every table. Have a look at this:
> http://www.sql-server-performance.com/gv_clustered_indexes.asp
>
> --
> Andrew J. Kelly SQL MVP
>
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:710E38A1-BE1E-4753-A1D5-D371F9416E7B@.microsoft.com...
> > Thanks for your response. A follow up question. Does a heap eventually get
> > so
> > fragmented that it gets corrupt? Is there a way to defrag or reindex a
> > heap
> > or do we need to go to clustered indexes. TIA
> >
> > "Andrew J. Kelly" wrote:
> >
> >> That is because index 0 is not an index at all. Index ID of 0 indicates
> >> this is a HEAP. Heaps can not be reindexed. You should add a clustered
> >> index to the table and that will change the ID from 0 to 1 and allow you
> >> to
> >> defragment and reindex the table.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> >> news:A5312425-B102-4CA3-8E78-8BD00B246EFB@.microsoft.com...
> >> > When I run DBCC SHOWCONTIG (tablename) WITH ALL_INDEXES the first index
> >> > shows
> >> >
> >> > Table: 'tablename' (437576597); index ID: 0, database ID: 8
> >> > TABLE level scan performed.
> >> > - Pages Scanned........................: 15721
> >> > - Extents Scanned.......................: 2027
> >> > - Extent Switches.......................: 2026
> >> > - Avg. Pages per Extent..................: 7.8
> >> > - Scan Density [Best Count:Actual Count]......: 96.99% [1966:2027]
> >> > - Extent Scan Fragmentation ...............: 43.96%
> >> > - Avg. Bytes Free per Page................: 202.3
> >> > - Avg. Page Density (full)................: 97.
> >> >
> >> > When I run DBCC DBREINDEX (tablename,'',0) it cleans up the other
> >> > indexes
> >> > but the first on remains unchanged. Any ideas? TIA PS: All the indexes
> >> > are
> >> > nonclustered
> >>
> >>
> >>
>
>

Sunday, March 25, 2012

DBCC SHOWCONTIG and Extent Switches

DBCC SHOWCONTIG & DB Performance

Thursday, March 22, 2012

DBCC SHOWCONTIG

dbcc showcontig

dbcc showcontig

Saturday, February 25, 2012

DBCC DBREINDEX

DBCC DBREINDEX

Friday, February 24, 2012

DBCC DBREINDEX

DBfile usage

Blog Archive

About Me