I know that as the data file autogrows, we are subject to fragmentation
externally.
As an example, I created a data file starting at 1 GB and then have autogrow
set at 250 MB. . Say now its around 2 GB which means we may have 5 different
sets of contiguous allocations for this file. i.e one contiguous allocation
for the first time creation of 1 GB and then 4 250 MB allocations
Now say I detach this db and move the 2 GB file to another server.. Will it
copy this 2 GB file as one contiguous file or still have it broken down into
5 ? Trying to understand file level fragmentation.> Now say I detach this db and move the 2 GB file to another server.. Will
it
> copy this 2 GB file as one contiguous file or still have it broken down
into
> 5 ? Trying to understand file level fragmentation.
This depends. If you have on the target server enough contiguous space, the
new file should not be fragmented. Anyway, you can always check the
fragmentation and defragment files on disk with Disk Defragmenter.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
Showing posts with label fragmentation. Show all posts
Showing posts with label fragmentation. Show all posts
Monday, March 26, 2012
external file fragmentation
I know that as the data file autogrows, we are subject to fragmentation
externally.
As an example, I created a data file starting at 1 GB and then have autogrow
set at 250 MB. . Say now its around 2 GB which means we may have 5 different
sets of contiguous allocations for this file. i.e one contiguous allocation
for the first time creation of 1 GB and then 4 250 MB allocations
Now say I detach this db and move the 2 GB file to another server.. Will it
copy this 2 GB file as one contiguous file or still have it broken down into
5 ? Trying to understand file level fragmentation.> Now say I detach this db and move the 2 GB file to another server.. Will
it
> copy this 2 GB file as one contiguous file or still have it broken down
into
> 5 ? Trying to understand file level fragmentation.
This depends. If you have on the target server enough contiguous space, the
new file should not be fragmented. Anyway, you can always check the
fragmentation and defragment files on disk with Disk Defragmenter.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
externally.
As an example, I created a data file starting at 1 GB and then have autogrow
set at 250 MB. . Say now its around 2 GB which means we may have 5 different
sets of contiguous allocations for this file. i.e one contiguous allocation
for the first time creation of 1 GB and then 4 250 MB allocations
Now say I detach this db and move the 2 GB file to another server.. Will it
copy this 2 GB file as one contiguous file or still have it broken down into
5 ? Trying to understand file level fragmentation.> Now say I detach this db and move the 2 GB file to another server.. Will
it
> copy this 2 GB file as one contiguous file or still have it broken down
into
> 5 ? Trying to understand file level fragmentation.
This depends. If you have on the target server enough contiguous space, the
new file should not be fragmented. Anyway, you can always check the
fragmentation and defragment files on disk with Disk Defragmenter.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
external file fragmentation
I know that as the data file autogrows, we are subject to fragmentation
externally.
As an example, I created a data file starting at 1 GB and then have autogrow
set at 250 MB. . Say now its around 2 GB which means we may have 5 different
sets of contiguous allocations for this file. i.e one contiguous allocation
for the first time creation of 1 GB and then 4 250 MB allocations
Now say I detach this db and move the 2 GB file to another server.. Will it
copy this 2 GB file as one contiguous file or still have it broken down into
5 ? Trying to understand file level fragmentation.
> Now say I detach this db and move the 2 GB file to another server.. Will
it
> copy this 2 GB file as one contiguous file or still have it broken down
into
> 5 ? Trying to understand file level fragmentation.
This depends. If you have on the target server enough contiguous space, the
new file should not be fragmented. Anyway, you can always check the
fragmentation and defragment files on disk with Disk Defragmenter.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
externally.
As an example, I created a data file starting at 1 GB and then have autogrow
set at 250 MB. . Say now its around 2 GB which means we may have 5 different
sets of contiguous allocations for this file. i.e one contiguous allocation
for the first time creation of 1 GB and then 4 250 MB allocations
Now say I detach this db and move the 2 GB file to another server.. Will it
copy this 2 GB file as one contiguous file or still have it broken down into
5 ? Trying to understand file level fragmentation.
> Now say I detach this db and move the 2 GB file to another server.. Will
it
> copy this 2 GB file as one contiguous file or still have it broken down
into
> 5 ? Trying to understand file level fragmentation.
This depends. If you have on the target server enough contiguous space, the
new file should not be fragmented. Anyway, you can always check the
fragmentation and defragment files on disk with Disk Defragmenter.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
Friday, March 23, 2012
Extent fragmentation still high after index drop/create
hello everyone,
we dropped the clustered & nonclustered indeces on a table, then
rebuilt them. logical fragmentation is near zero, but extent
fragmentation is about 40%. how can this be if the indeces are brand
new?Here's a whitepaper you should read:
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
Also, what effect is the extent fragmentation having on the performance of
your workload? (i.e. do you have empricial evidence that the workload perf
is decreasing with increasing extent fragmentation - to justify the overhead
of these index operations?)
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
<gary@.dba723.net> wrote in message
news:fb0df63.0310090956.62c752a7@.posting.google.co m...
> hello everyone,
> we dropped the clustered & nonclustered indeces on a table, then
> rebuilt them. logical fragmentation is near zero, but extent
> fragmentation is about 40%. how can this be if the indeces are brand
> new?|||Thanks for the response, Paul!
A particular query on my test server with logical and extent frag near
zero ran in about 12 seconds (very complex query). The same query on
my reporting box took over a minute, with some of the largest tables
included at about 40% extent frag, near zero logical frag.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message news:<3f86d98b$1@.news.microsoft.com>...
> Here's a whitepaper you should read:
> http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
> Also, what effect is the extent fragmentation having on the performance of
> your workload? (i.e. do you have empricial evidence that the workload perf
> is decreasing with increasing extent fragmentation - to justify the overhead
> of these index operations?)
> Regards,
> Paul.
> --
> Paul Randal
> DBCC Technical Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> <gary@.dba723.net> wrote in message
> news:fb0df63.0310090956.62c752a7@.posting.google.co m...
> > hello everyone,
> > we dropped the clustered & nonclustered indeces on a table, then
> > rebuilt them. logical fragmentation is near zero, but extent
> > fragmentation is about 40%. how can this be if the indeces are brand
> > new?
Extent Fragmentation High Just After Reindexing
Hello. When reviewing the DBCC SHOWCONTIG immediately after reindexing all indexes on a database, I see the ExtentFragmentation has values like 50 to 70%... These are SQL 2005 tables with clustered PK's, no large varchars/blobs, and at least 100 pages in the index... The numbers related to PAGE fragmentation are ok after reindexing, but not the EXTENT fragmentation numbers.
I noticed the drive is in need of being defragged at the disk level. Is that a reason why reindexing doesn't fix the Extent frag numbers? ANy other ideas on this? I can try defragging the DISK over the weekend, bringing the database offline then, but any other thougths on why the Extents show these high %'s? Is there any command to reset them and maybe that isn't happening? Like must I do update usage to get valid Extent frag #'s?
If there were MANY autogrows on the files, is that a different level of fragmentation? and how could all those small pieces of files be pulled back together? Thanks, Bruce
Correct me if I'm wrong, but since its not a heap, I'm not sure the old extent fragmentation column is meaningful, in terms of SQL fragmentation. You're in 2005, use sys.dm_db_index_physical_stats instead. The avg_fragmentation_in_pct column shows logical fragmentation for indexes and extent fragmentation for heaps.
Code Snippet
use <database name here>
select * from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'<table name here>'),null, null,null)
This may show you a more application fragmentation percent.
Running your NTFS defrag tool in the OS won't hurt, but you are correct that will have to bring all apps to a halt including SQL.
Physical and logical fragmentation due to autogrow has a lot to do with existing fragmentation, available disk space and activity.
|||
W, thanks. Accoring to the SQL 2005 BOL snip below, ExtentFragmentation is not applicable to heaps and when the index spans multiple files. All of the indexes in question are NOT heaps and have a single MDF file. I'm doing other tests, like using the ALTER INDEX REORGANIZE command, and will try fully dropping and recreating the index, so I see a number closer to 0% Extent Fragmentation. I'd think ExtentFragmentation still matters, but if you'er saying to use the new functions, and the SHOWCONTIG is showing bogus data, hmmm, great, I'll try to find ExtentFragmentation in those functions too... Thanks, Bruce
ExtentFragmentation
Percentage of out-of-order extents in scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order extent is one for which the extent that contains the current page for an index is not physically the next extent after the extent that contains the previous page for an index.
ok, here is a specific example, with SQL I used and results... If anyone has any ideas on WHY the Extent Fragmentation numbers act like this, please let me know. Maybe they ARE just BOGUS completely and meaningless in SQL 2005? Here are the exact steps I did... all on SQL 2005 SP2, database has a single data file, and "MyTable" has a clustered PK and 2 other indexes.
Step 1: Ran a SHOWCONTIG to see the fragmentation level.
Code Snippet
print 'DBCC SHOWCONTIG ([tMyTable])'
DBCC SHOWCONTIG (tMyTable) WITH ALL_INDEXES
go
DBCC SHOWCONTIG ([tMyTable])
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 1, database ID: 21
TABLE level scan performed.
- Pages Scanned................................: 314
- Extents Scanned..............................: 46
- Extent Switches..............................: 189
- Avg. Pages per Extent........................: 6.8
- Scan Density [Best Count:Actual Count].......: 21.05% [40:190]
- Logical Scan Fragmentation ..................: 54.14%
- Extent Scan Fragmentation ...................: 45.65%
- Avg. Bytes Free per Page.....................: 2890.9
- Avg. Page Density (full).....................: 64.28%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 3, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 42
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 100.00% [6:6]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 135.0
- Avg. Page Density (full).....................: 98.33%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 6, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 31
- Extents Scanned..............................: 11
- Extent Switches..............................: 10
- Avg. Pages per Extent........................: 2.8
- Scan Density [Best Count:Actual Count].......: 36.36% [4:11]
- Logical Scan Fragmentation ..................: 25.81%
- Extent Scan Fragmentation ...................: 72.73%
- Avg. Bytes Free per Page.....................: 214.1
- Avg. Page Density (full).....................: 97.36%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note that 2 of teh indexes have 21% and 36% scan density values. So, this is my starting point, and I want to DEFRAG this tabloe, all 3 indexes.
Step 2: Ran an ALTER INDEX on all 3, with the REBUILD option.
Code Snippet
ALTER INDEX XPKtMyTable ON dbo.tMyTableREBUILD
GO
ALTER INDEX XIF2tMyTable ON dbo.tMyTable
REBUILD
GO
ALTER INDEX XIF5tMyTable ON dbo.tMyTable
REBUILD
GO
Step 3: Ran another SHOWCONTIG
Code Snippet
print 'DBCC SHOWCONTIG ([tMyTable])'
DBCC SHOWCONTIG (tMyTable) WITH ALL_INDEXES
go
DBCC SHOWCONTIG ([tMyTable])
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 1, database ID: 21
TABLE level scan performed.
- Pages Scanned................................: 205
- Extents Scanned..............................: 26
- Extent Switches..............................: 25
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 100.00% [26:26]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 46.15%
- Avg. Bytes Free per Page.....................: 123.2
- Avg. Page Density (full).....................: 98.48%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 3, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 42
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 100.00% [6:6]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 66.67%
- Avg. Bytes Free per Page.....................: 135.0
- Avg. Page Density (full).....................: 98.33%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 6, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 31
- Extents Scanned..............................: 11
- Extent Switches..............................: 10
- Avg. Pages per Extent........................: 2.8
- Scan Density [Best Count:Actual Count].......: 36.36% [4:11]
- Logical Scan Fragmentation ..................: 25.81%
- Extent Scan Fragmentation ...................: 72.73%
- Avg. Bytes Free per Page.....................: 214.1
- Avg. Page Density (full).....................: 97.36%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note that the Scan Density of teh first 2 indexes changed to 100%, but the 3rd didn't defrag for some reason?!? Also, note that the Extent Scan Fragmentation numbers are still high, up to 72% on the 3rd index.
Step 4: Dropped and recreated the 3 indexes
Code Snippet
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tMyTable]') AND name = N'XPKtMyTable')
ALTER TABLE [dbo].[tMyTable] DROP CONSTRAINT [XPKtMyTable]
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tMyTable]') AND name = N'XIF2tMyTable')
DROP INDEX [XIF2tMyTable] ON [dbo].[tMyTable] WITH ( ONLINE = OFF )
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tMyTable]') AND name = N'XIF5tMyTable')
DROP INDEX [XIF5tMyTable] ON [dbo].[tMyTable] WITH ( ONLINE = OFF )
ALTER TABLE [dbo].[tMyTable] ADD CONSTRAINT [XPKtMyTable] PRIMARY KEY CLUSTERED
( [MyTableID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [XIF2tMyTable] ON [dbo].[tMyTable]
( [coCode] ASC,
[DefID] ASC,
[GroupID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [XIF5tMyTable] ON [dbo].[tMyTable]
( [liabilityID] ASC,
[coCode] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Step 5: Ran the SHOWCONTIG again
Code Snippet
print 'DBCC SHOWCONTIG ([tMyTable])'
DBCC SHOWCONTIG (tMyTable) WITH ALL_INDEXES
go
DBCC SHOWCONTIG ([tMyTable])
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 1, database ID: 21
TABLE level scan performed.
- Pages Scanned................................: 205
- Extents Scanned..............................: 26
- Extent Switches..............................: 25
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 100.00% [26:26]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 38.46%
- Avg. Bytes Free per Page.....................: 123.2
- Avg. Page Density (full).....................: 98.48%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 3, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 42
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 100.00% [6:6]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 33.33%
- Avg. Bytes Free per Page.....................: 135.0
- Avg. Page Density (full).....................: 98.33%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 6, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 31
- Extents Scanned..............................: 11
- Extent Switches..............................: 10
- Avg. Pages per Extent........................: 2.8
- Scan Density [Best Count:Actual Count].......: 36.36% [4:11]
- Logical Scan Fragmentation ..................: 25.81%
- Extent Scan Fragmentation ...................: 45.45%
- Avg. Bytes Free per Page.....................: 214.1
- Avg. Page Density (full).....................: 97.36%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note the Extent Scan Fragmentation numbers are better, but not good, and that 3rd index is still at 36% Scan Density.
Step 6: Used the new sys.dm_db_index_physical_stats function to compare to the SHOWCONTIG values. (Broke it up into 4 queries for ease in reading here)
Code Snippet
select database_id, object_id, index_id, partition_number, index_type_desc
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)
select alloc_unit_type_desc, index_depth, index_level, avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)
select fragment_count, avg_fragment_size_in_pages, page_count, avg_page_space_used_in_percent, record_count
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)
select ghost_record_count, version_ghost_record_count, min_record_size_in_bytes, max_record_size_in_bytes, avg_record_size_in_bytes, forwarded_record_count
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)
database_id object_id index_id partition_number index_type_desc
-- -- -- -
21 1125175354 1 1 CLUSTERED INDEX
21 1125175354 3 1 NONCLUSTERED INDEX
21 1125175354 6 1 NONCLUSTERED INDEX
alloc_unit_type_desc index_depth index_level avg_fragmentation_in_percent
-- -- -
IN_ROW_DATA 2 0 0
IN_ROW_DATA 2 0 0
IN_ROW_DATA 2 0 25.8064516129032
fragment_count avg_fragment_size_in_pages page_count avg_page_space_used_in_percent record_count
-- -- -- --
11 18.6363636363636 205 NULL NULL
3 14 42 NULL NULL
9 3.44444444444444 31 NULL NULL
ghost_record_count version_ghost_record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes forwarded_record_count
-- -- -
NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL
That function doesn't appear to provide as much info as SHOWCONTIG, but maybe there are more functions to get the ExtentFrag % values...
ok, anyone have any ideas on this? Do I even care if Extent Frag numbers are high? and wondering why on some tables that have like 28,000 rows, they get very page-fragged after about only 200 row changes. I did this locally on a laptop also and defragged my drive in between and that had no effect on the SHOWCONTIG numbers. IF the autogrow was very small in the past and expanded many times, how would I see that now? In older versions of SQL Server, there was a table that maintained those "extents" (?), anything like that now, and is that a possible reason for fragmentation also?
Other ideas/comments are appreciated!! Thanks, Bruce
|||Dunno about why index 6 is showing that fragmentation, but your table not only looks pretty tight, but pretty small. 205 pages. Is fragmentation really a concern?I was also under the impression that fragmentation of the clustered index was the chief concern anyway, and that looks tight right now. Pretty sure the stated fragmentation of nonclustered indexes is even meaningful.
Bruce dBA wrote:
IF the autogrow was very small in the past and expanded many times, how would I see that now?"
You should be able to see "Autogrow of file" in the log if you're auditing that.|||
W, yes, I should have done that example on a larger table. The clustered index is the main importance, true, was just wondering why the other indexes get "fragged" so quickly with a small amount of updates (like 200 updates on a 28,000 row table)... The autogrow cauing fragmentation question, was more about before I started checking this out, the autogrow is set better now, but I don't know what it WAS before, and was just wondering if having a lot of small autogrowths in the past added to this, even though reindexing is done, aren't the MDF/NDF files fragged also possibly. In older releases of SQL Server, I remember that data was stored in catalog tables, each new "autogrowth" back then, not sure if you can historically see that, and how files fragging relates to index fragging.... Thanks, Bruce|||
Try using a fill factor of 90%. You are getting fragmentation because you are filling the pages at 100%.
Extent Fragmentation High Just After Reindexing
Hello. When reviewing the DBCC SHOWCONTIG immediately after reindexing all indexes on a database, I see the ExtentFragmentation has values like 50 to 70%... These are SQL 2005 tables with clustered PK's, no large varchars/blobs, and at least 100 pages in the index... The numbers related to PAGE fragmentation are ok after reindexing, but not the EXTENT fragmentation numbers.
I noticed the drive is in need of being defragged at the disk level. Is that a reason why reindexing doesn't fix the Extent frag numbers? ANy other ideas on this? I can try defragging the DISK over the weekend, bringing the database offline then, but any other thougths on why the Extents show these high %'s? Is there any command to reset them and maybe that isn't happening? Like must I do update usage to get valid Extent frag #'s?
If there were MANY autogrows on the files, is that a different level of fragmentation? and how could all those small pieces of files be pulled back together? Thanks, Bruce
Correct me if I'm wrong, but since its not a heap, I'm not sure the old extent fragmentation column is meaningful, in terms of SQL fragmentation. You're in 2005, use sys.dm_db_index_physical_stats instead. The avg_fragmentation_in_pct column shows logical fragmentation for indexes and extent fragmentation for heaps.
Code Snippet
use <database name here>
select * from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'<table name here>'),null, null,null)
This may show you a more application fragmentation percent.
Running your NTFS defrag tool in the OS won't hurt, but you are correct that will have to bring all apps to a halt including SQL.
Physical and logical fragmentation due to autogrow has a lot to do with existing fragmentation, available disk space and activity.
|||
W, thanks. Accoring to the SQL 2005 BOL snip below, ExtentFragmentation is not applicable to heaps and when the index spans multiple files. All of the indexes in question are NOT heaps and have a single MDF file. I'm doing other tests, like using the ALTER INDEX REORGANIZE command, and will try fully dropping and recreating the index, so I see a number closer to 0% Extent Fragmentation. I'd think ExtentFragmentation still matters, but if you'er saying to use the new functions, and the SHOWCONTIG is showing bogus data, hmmm, great, I'll try to find ExtentFragmentation in those functions too... Thanks, Bruce
ExtentFragmentation
Percentage of out-of-order extents in scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order extent is one for which the extent that contains the current page for an index is not physically the next extent after the extent that contains the previous page for an index.
ok, here is a specific example, with SQL I used and results... If anyone has any ideas on WHY the Extent Fragmentation numbers act like this, please let me know. Maybe they ARE just BOGUS completely and meaningless in SQL 2005? Here are the exact steps I did... all on SQL 2005 SP2, database has a single data file, and "MyTable" has a clustered PK and 2 other indexes.
Step 1: Ran a SHOWCONTIG to see the fragmentation level.
Code Snippet
print 'DBCC SHOWCONTIG ([tMyTable])'
DBCC SHOWCONTIG (tMyTable) WITH ALL_INDEXES
go
DBCC SHOWCONTIG ([tMyTable])
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 1, database ID: 21
TABLE level scan performed.
- Pages Scanned................................: 314
- Extents Scanned..............................: 46
- Extent Switches..............................: 189
- Avg. Pages per Extent........................: 6.8
- Scan Density [Best Count:Actual Count].......: 21.05% [40:190]
- Logical Scan Fragmentation ..................: 54.14%
- Extent Scan Fragmentation ...................: 45.65%
- Avg. Bytes Free per Page.....................: 2890.9
- Avg. Page Density (full).....................: 64.28%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 3, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 42
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 100.00% [6:6]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 135.0
- Avg. Page Density (full).....................: 98.33%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 6, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 31
- Extents Scanned..............................: 11
- Extent Switches..............................: 10
- Avg. Pages per Extent........................: 2.8
- Scan Density [Best Count:Actual Count].......: 36.36% [4:11]
- Logical Scan Fragmentation ..................: 25.81%
- Extent Scan Fragmentation ...................: 72.73%
- Avg. Bytes Free per Page.....................: 214.1
- Avg. Page Density (full).....................: 97.36%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note that 2 of teh indexes have 21% and 36% scan density values. So, this is my starting point, and I want to DEFRAG this tabloe, all 3 indexes.
Step 2: Ran an ALTER INDEX on all 3, with the REBUILD option.
Code Snippet
ALTER INDEX XPKtMyTable ON dbo.tMyTableREBUILD
GO
ALTER INDEX XIF2tMyTable ON dbo.tMyTable
REBUILD
GO
ALTER INDEX XIF5tMyTable ON dbo.tMyTable
REBUILD
GO
Step 3: Ran another SHOWCONTIG
Code Snippet
print 'DBCC SHOWCONTIG ([tMyTable])'
DBCC SHOWCONTIG (tMyTable) WITH ALL_INDEXES
go
DBCC SHOWCONTIG ([tMyTable])
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 1, database ID: 21
TABLE level scan performed.
- Pages Scanned................................: 205
- Extents Scanned..............................: 26
- Extent Switches..............................: 25
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 100.00% [26:26]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 46.15%
- Avg. Bytes Free per Page.....................: 123.2
- Avg. Page Density (full).....................: 98.48%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 3, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 42
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 100.00% [6:6]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 66.67%
- Avg. Bytes Free per Page.....................: 135.0
- Avg. Page Density (full).....................: 98.33%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 6, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 31
- Extents Scanned..............................: 11
- Extent Switches..............................: 10
- Avg. Pages per Extent........................: 2.8
- Scan Density [Best Count:Actual Count].......: 36.36% [4:11]
- Logical Scan Fragmentation ..................: 25.81%
- Extent Scan Fragmentation ...................: 72.73%
- Avg. Bytes Free per Page.....................: 214.1
- Avg. Page Density (full).....................: 97.36%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note that the Scan Density of teh first 2 indexes changed to 100%, but the 3rd didn't defrag for some reason?!? Also, note that the Extent Scan Fragmentation numbers are still high, up to 72% on the 3rd index.
Step 4: Dropped and recreated the 3 indexes
Code Snippet
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tMyTable]') AND name = N'XPKtMyTable')
ALTER TABLE [dbo].[tMyTable] DROP CONSTRAINT [XPKtMyTable]
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tMyTable]') AND name = N'XIF2tMyTable')
DROP INDEX [XIF2tMyTable] ON [dbo].[tMyTable] WITH ( ONLINE = OFF )
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tMyTable]') AND name = N'XIF5tMyTable')
DROP INDEX [XIF5tMyTable] ON [dbo].[tMyTable] WITH ( ONLINE = OFF )
ALTER TABLE [dbo].[tMyTable] ADD CONSTRAINT [XPKtMyTable] PRIMARY KEY CLUSTERED
( [MyTableID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [XIF2tMyTable] ON [dbo].[tMyTable]
( [coCode] ASC,
[DefID] ASC,
[GroupID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [XIF5tMyTable] ON [dbo].[tMyTable]
( [liabilityID] ASC,
[coCode] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Step 5: Ran the SHOWCONTIG again
Code Snippet
print 'DBCC SHOWCONTIG ([tMyTable])'
DBCC SHOWCONTIG (tMyTable) WITH ALL_INDEXES
go
DBCC SHOWCONTIG ([tMyTable])
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 1, database ID: 21
TABLE level scan performed.
- Pages Scanned................................: 205
- Extents Scanned..............................: 26
- Extent Switches..............................: 25
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 100.00% [26:26]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 38.46%
- Avg. Bytes Free per Page.....................: 123.2
- Avg. Page Density (full).....................: 98.48%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 3, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 42
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 100.00% [6:6]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 33.33%
- Avg. Bytes Free per Page.....................: 135.0
- Avg. Page Density (full).....................: 98.33%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 6, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 31
- Extents Scanned..............................: 11
- Extent Switches..............................: 10
- Avg. Pages per Extent........................: 2.8
- Scan Density [Best Count:Actual Count].......: 36.36% [4:11]
- Logical Scan Fragmentation ..................: 25.81%
- Extent Scan Fragmentation ...................: 45.45%
- Avg. Bytes Free per Page.....................: 214.1
- Avg. Page Density (full).....................: 97.36%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note the Extent Scan Fragmentation numbers are better, but not good, and that 3rd index is still at 36% Scan Density.
Step 6: Used the new sys.dm_db_index_physical_stats function to compare to the SHOWCONTIG values. (Broke it up into 4 queries for ease in reading here)
Code Snippet
select database_id, object_id, index_id, partition_number, index_type_desc
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)
select alloc_unit_type_desc, index_depth, index_level, avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)
select fragment_count, avg_fragment_size_in_pages, page_count, avg_page_space_used_in_percent, record_count
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)
select ghost_record_count, version_ghost_record_count, min_record_size_in_bytes, max_record_size_in_bytes, avg_record_size_in_bytes, forwarded_record_count
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)
database_id object_id index_id partition_number index_type_desc
-- -- -- -
21 1125175354 1 1 CLUSTERED INDEX
21 1125175354 3 1 NONCLUSTERED INDEX
21 1125175354 6 1 NONCLUSTERED INDEX
alloc_unit_type_desc index_depth index_level avg_fragmentation_in_percent
-- -- -
IN_ROW_DATA 2 0 0
IN_ROW_DATA 2 0 0
IN_ROW_DATA 2 0 25.8064516129032
fragment_count avg_fragment_size_in_pages page_count avg_page_space_used_in_percent record_count
-- -- -- --
11 18.6363636363636 205 NULL NULL
3 14 42 NULL NULL
9 3.44444444444444 31 NULL NULL
ghost_record_count version_ghost_record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes forwarded_record_count
-- -- -
NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL
That function doesn't appear to provide as much info as SHOWCONTIG, but maybe there are more functions to get the ExtentFrag % values...
ok, anyone have any ideas on this? Do I even care if Extent Frag numbers are high? and wondering why on some tables that have like 28,000 rows, they get very page-fragged after about only 200 row changes. I did this locally on a laptop also and defragged my drive in between and that had no effect on the SHOWCONTIG numbers. IF the autogrow was very small in the past and expanded many times, how would I see that now? In older versions of SQL Server, there was a table that maintained those "extents" (?), anything like that now, and is that a possible reason for fragmentation also?
Other ideas/comments are appreciated!! Thanks, Bruce
|||Dunno about why index 6 is showing that fragmentation, but your table not only looks pretty tight, but pretty small. 205 pages. Is fragmentation really a concern?I was also under the impression that fragmentation of the clustered index was the chief concern anyway, and that looks tight right now. Pretty sure the stated fragmentation of nonclustered indexes is even meaningful.
IF the autogrow was very small in the past and expanded many times, how would I see that now?"
You should be able to see "Autogrow of file" in the log if you're auditing that.|||
W, yes, I should have done that example on a larger table. The clustered index is the main importance, true, was just wondering why the other indexes get "fragged" so quickly with a small amount of updates (like 200 updates on a 28,000 row table)... The autogrow cauing fragmentation question, was more about before I started checking this out, the autogrow is set better now, but I don't know what it WAS before, and was just wondering if having a lot of small autogrowths in the past added to this, even though reindexing is done, aren't the MDF/NDF files fragged also possibly. In older releases of SQL Server, I remember that data was stored in catalog tables, each new "autogrowth" back then, not sure if you can historically see that, and how files fragging relates to index fragging.... Thanks, Bruce|||
Try using a fill factor of 90%. You are getting fragmentation because you are filling the pages at 100%.
Extent Fragmentation
I am currently trying to defrag a table and am unable to
reduce Extent fragmentation.
The structure of the table is as follows.
CREATE TABLE [dbo].[PortfolioMember] (
[PortfolioID] [int] NOT NULL ,
[ID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PortfolioMember] WITH NOCHECK ADD
CONSTRAINT [PK_PORTFOLIOMEMBER] PRIMARY KEY
CLUSTERED
(
[PortfolioID],
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[PortfolioMember] ADD
CONSTRAINT [AK_IDPFOL_PORTFOLI] UNIQUE
NONCLUSTERED
(
[ID],
[PortfolioID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [PortfolioMember_FK] ON [dbo].
[PortfolioMember]([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [has_members_FK] ON [dbo].[PortfolioMember]
([PortfolioID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[PortfolioMember] ADD
CONSTRAINT [FK_PORTFOLI_RELATION__PORTFOLI]
FOREIGN KEY
(
[PortfolioID]
) REFERENCES [dbo].[Portfolio] (
[PortfolioID]
)
GO
So it has a clustered index.
The Best I can get from DBCC SHOWCONTIG (PortfolioMember)
DBCC SHOWCONTIG scanning 'PortfolioMember' table...
Table: 'PortfolioMember' (1429580131); index ID: 1,
database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 26048
- Extents Scanned.......................: 3277
- Extent Switches.......................: 3283
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.15%
[3256:3284]
- Logical Scan Fragmentation ..............: 0.03%
- Extent Scan Fragmentation ...............: 90.51%
- Avg. Bytes Free per Page................: 802.9
- Avg. Page Density (full)................: 90.08%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
I have tried the following all with no success at
reducing Extent fragmentation:
DBCC DBREINDEX (portfoliomember, '', 90)
DBCC DBREINDEX (portfoliomember, 'PK_PORTFOLIOMEMBER', 90)
DBCC INDEXDEFRAG (CRS_BaseDev, portfoliomember,
PK_PORTFOLIOMEMBER)
I have executed sp_spaceused and there is plenty of room.
I am currently rebuilding the table but don't want to
have to do this to properly defrag the table.
Does anyone have any suggestions?
Thanks in advance.
Jamie
jamie.downs@.risk.sungard.comJamie,
A couple of questions first. Is this the entire table? I assume it has
more columns but were left out for simplicity sake. If so it is usually
best to shoe the actual DDL so there are no assumptions. If [ID] is unique
then why not just make it the PK and drop the PortfolofioID? Why do you
create duplicate indexes? You have a PK constraint and a Unique constraint
on the two columns. This will make an index already to enforce the
constraint. There is no need to add a second index to each one. The last
comment is that I assume you have multiple files in your file group and that
is what is giving you the Extent fragmentation. This value is basically
useless when you have multiple files.
--
Andrew J. Kelly
SQL Server MVP
"Jamie" <jamie.downs@.risk.sungard.com> wrote in message
news:0ee801c393e8$463c0460$a301280a@.phx.gbl...
> I am currently trying to defrag a table and am unable to
> reduce Extent fragmentation.
> The structure of the table is as follows.
> CREATE TABLE [dbo].[PortfolioMember] (
> [PortfolioID] [int] NOT NULL ,
> [ID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PortfolioMember] WITH NOCHECK ADD
> CONSTRAINT [PK_PORTFOLIOMEMBER] PRIMARY KEY
> CLUSTERED
> (
> [PortfolioID],
> [ID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PortfolioMember] ADD
> CONSTRAINT [AK_IDPFOL_PORTFOLI] UNIQUE
> NONCLUSTERED
> (
> [ID],
> [PortfolioID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [PortfolioMember_FK] ON [dbo].
> [PortfolioMember]([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [has_members_FK] ON [dbo].[PortfolioMember]
> ([PortfolioID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PortfolioMember] ADD
> CONSTRAINT [FK_PORTFOLI_RELATION__PORTFOLI]
> FOREIGN KEY
> (
> [PortfolioID]
> ) REFERENCES [dbo].[Portfolio] (
> [PortfolioID]
> )
> GO
> So it has a clustered index.
> The Best I can get from DBCC SHOWCONTIG (PortfolioMember)
> DBCC SHOWCONTIG scanning 'PortfolioMember' table...
> Table: 'PortfolioMember' (1429580131); index ID: 1,
> database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 26048
> - Extents Scanned.......................: 3277
> - Extent Switches.......................: 3283
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.15%
> [3256:3284]
> - Logical Scan Fragmentation ..............: 0.03%
> - Extent Scan Fragmentation ...............: 90.51%
> - Avg. Bytes Free per Page................: 802.9
> - Avg. Page Density (full)................: 90.08%
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> I have tried the following all with no success at
> reducing Extent fragmentation:
> DBCC DBREINDEX (portfoliomember, '', 90)
> DBCC DBREINDEX (portfoliomember, 'PK_PORTFOLIOMEMBER', 90)
> DBCC INDEXDEFRAG (CRS_BaseDev, portfoliomember,
> PK_PORTFOLIOMEMBER)
> I have executed sp_spaceused and there is plenty of room.
> I am currently rebuilding the table but don't want to
> have to do this to properly defrag the table.
> Does anyone have any suggestions?
> Thanks in advance.
> Jamie
> jamie.downs@.risk.sungard.com
>
>|||As a follow-on to what Andrew said, the extent fragmentation algorithm
inside DBCC SHOWCONTIG specifically does not work when an index/table spans
multiple files and this is stated in BooksOnLine.
You should read the whitepaper on fragmentation and how-to/whether-to
address it at:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, 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:O$44jy$kDHA.684@.TK2MSFTNGP09.phx.gbl...
> Jamie,
> A couple of questions first. Is this the entire table? I assume it has
> more columns but were left out for simplicity sake. If so it is usually
> best to shoe the actual DDL so there are no assumptions. If [ID] is
unique
> then why not just make it the PK and drop the PortfolofioID? Why do you
> create duplicate indexes? You have a PK constraint and a Unique
constraint
> on the two columns. This will make an index already to enforce the
> constraint. There is no need to add a second index to each one. The last
> comment is that I assume you have multiple files in your file group and
that
> is what is giving you the Extent fragmentation. This value is basically
> useless when you have multiple files.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Jamie" <jamie.downs@.risk.sungard.com> wrote in message
> news:0ee801c393e8$463c0460$a301280a@.phx.gbl...
> > I am currently trying to defrag a table and am unable to
> > reduce Extent fragmentation.
> >
> > The structure of the table is as follows.
> >
> > CREATE TABLE [dbo].[PortfolioMember] (
> > [PortfolioID] [int] NOT NULL ,
> > [ID] [int] NOT NULL
> > ) ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[PortfolioMember] WITH NOCHECK ADD
> > CONSTRAINT [PK_PORTFOLIOMEMBER] PRIMARY KEY
> > CLUSTERED
> > (
> > [PortfolioID],
> > [ID]
> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[PortfolioMember] ADD
> > CONSTRAINT [AK_IDPFOL_PORTFOLI] UNIQUE
> > NONCLUSTERED
> > (
> > [ID],
> > [PortfolioID]
> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [PortfolioMember_FK] ON [dbo].
> > [PortfolioMember]([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [has_members_FK] ON [dbo].[PortfolioMember]
> > ([PortfolioID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[PortfolioMember] ADD
> > CONSTRAINT [FK_PORTFOLI_RELATION__PORTFOLI]
> > FOREIGN KEY
> > (
> > [PortfolioID]
> > ) REFERENCES [dbo].[Portfolio] (
> > [PortfolioID]
> > )
> > GO
> >
> > So it has a clustered index.
> >
> > The Best I can get from DBCC SHOWCONTIG (PortfolioMember)
> >
> > DBCC SHOWCONTIG scanning 'PortfolioMember' table...
> > Table: 'PortfolioMember' (1429580131); index ID: 1,
> > database ID: 7
> > TABLE level scan performed.
> > - Pages Scanned........................: 26048
> > - Extents Scanned.......................: 3277
> > - Extent Switches.......................: 3283
> > - Avg. Pages per Extent..................: 7.9
> > - Scan Density [Best Count:Actual Count]......: 99.15%
> > [3256:3284]
> > - Logical Scan Fragmentation ..............: 0.03%
> > - Extent Scan Fragmentation ...............: 90.51%
> > - Avg. Bytes Free per Page................: 802.9
> > - Avg. Page Density (full)................: 90.08%
> > DBCC execution completed. If DBCC printed error messages,
> > contact your system administrator.
> >
> > I have tried the following all with no success at
> > reducing Extent fragmentation:
> >
> > DBCC DBREINDEX (portfoliomember, '', 90)
> >
> > DBCC DBREINDEX (portfoliomember, 'PK_PORTFOLIOMEMBER', 90)
> >
> > DBCC INDEXDEFRAG (CRS_BaseDev, portfoliomember,
> > PK_PORTFOLIOMEMBER)
> >
> > I have executed sp_spaceused and there is plenty of room.
> >
> > I am currently rebuilding the table but don't want to
> > have to do this to properly defrag the table.
> >
> > Does anyone have any suggestions?
> >
> > Thanks in advance.
> >
> > Jamie
> > jamie.downs@.risk.sungard.com
> >
> >
> >
> >
>sql
reduce Extent fragmentation.
The structure of the table is as follows.
CREATE TABLE [dbo].[PortfolioMember] (
[PortfolioID] [int] NOT NULL ,
[ID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PortfolioMember] WITH NOCHECK ADD
CONSTRAINT [PK_PORTFOLIOMEMBER] PRIMARY KEY
CLUSTERED
(
[PortfolioID],
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[PortfolioMember] ADD
CONSTRAINT [AK_IDPFOL_PORTFOLI] UNIQUE
NONCLUSTERED
(
[ID],
[PortfolioID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [PortfolioMember_FK] ON [dbo].
[PortfolioMember]([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [has_members_FK] ON [dbo].[PortfolioMember]
([PortfolioID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[PortfolioMember] ADD
CONSTRAINT [FK_PORTFOLI_RELATION__PORTFOLI]
FOREIGN KEY
(
[PortfolioID]
) REFERENCES [dbo].[Portfolio] (
[PortfolioID]
)
GO
So it has a clustered index.
The Best I can get from DBCC SHOWCONTIG (PortfolioMember)
DBCC SHOWCONTIG scanning 'PortfolioMember' table...
Table: 'PortfolioMember' (1429580131); index ID: 1,
database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 26048
- Extents Scanned.......................: 3277
- Extent Switches.......................: 3283
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.15%
[3256:3284]
- Logical Scan Fragmentation ..............: 0.03%
- Extent Scan Fragmentation ...............: 90.51%
- Avg. Bytes Free per Page................: 802.9
- Avg. Page Density (full)................: 90.08%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
I have tried the following all with no success at
reducing Extent fragmentation:
DBCC DBREINDEX (portfoliomember, '', 90)
DBCC DBREINDEX (portfoliomember, 'PK_PORTFOLIOMEMBER', 90)
DBCC INDEXDEFRAG (CRS_BaseDev, portfoliomember,
PK_PORTFOLIOMEMBER)
I have executed sp_spaceused and there is plenty of room.
I am currently rebuilding the table but don't want to
have to do this to properly defrag the table.
Does anyone have any suggestions?
Thanks in advance.
Jamie
jamie.downs@.risk.sungard.comJamie,
A couple of questions first. Is this the entire table? I assume it has
more columns but were left out for simplicity sake. If so it is usually
best to shoe the actual DDL so there are no assumptions. If [ID] is unique
then why not just make it the PK and drop the PortfolofioID? Why do you
create duplicate indexes? You have a PK constraint and a Unique constraint
on the two columns. This will make an index already to enforce the
constraint. There is no need to add a second index to each one. The last
comment is that I assume you have multiple files in your file group and that
is what is giving you the Extent fragmentation. This value is basically
useless when you have multiple files.
--
Andrew J. Kelly
SQL Server MVP
"Jamie" <jamie.downs@.risk.sungard.com> wrote in message
news:0ee801c393e8$463c0460$a301280a@.phx.gbl...
> I am currently trying to defrag a table and am unable to
> reduce Extent fragmentation.
> The structure of the table is as follows.
> CREATE TABLE [dbo].[PortfolioMember] (
> [PortfolioID] [int] NOT NULL ,
> [ID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PortfolioMember] WITH NOCHECK ADD
> CONSTRAINT [PK_PORTFOLIOMEMBER] PRIMARY KEY
> CLUSTERED
> (
> [PortfolioID],
> [ID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PortfolioMember] ADD
> CONSTRAINT [AK_IDPFOL_PORTFOLI] UNIQUE
> NONCLUSTERED
> (
> [ID],
> [PortfolioID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [PortfolioMember_FK] ON [dbo].
> [PortfolioMember]([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [has_members_FK] ON [dbo].[PortfolioMember]
> ([PortfolioID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PortfolioMember] ADD
> CONSTRAINT [FK_PORTFOLI_RELATION__PORTFOLI]
> FOREIGN KEY
> (
> [PortfolioID]
> ) REFERENCES [dbo].[Portfolio] (
> [PortfolioID]
> )
> GO
> So it has a clustered index.
> The Best I can get from DBCC SHOWCONTIG (PortfolioMember)
> DBCC SHOWCONTIG scanning 'PortfolioMember' table...
> Table: 'PortfolioMember' (1429580131); index ID: 1,
> database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 26048
> - Extents Scanned.......................: 3277
> - Extent Switches.......................: 3283
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.15%
> [3256:3284]
> - Logical Scan Fragmentation ..............: 0.03%
> - Extent Scan Fragmentation ...............: 90.51%
> - Avg. Bytes Free per Page................: 802.9
> - Avg. Page Density (full)................: 90.08%
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> I have tried the following all with no success at
> reducing Extent fragmentation:
> DBCC DBREINDEX (portfoliomember, '', 90)
> DBCC DBREINDEX (portfoliomember, 'PK_PORTFOLIOMEMBER', 90)
> DBCC INDEXDEFRAG (CRS_BaseDev, portfoliomember,
> PK_PORTFOLIOMEMBER)
> I have executed sp_spaceused and there is plenty of room.
> I am currently rebuilding the table but don't want to
> have to do this to properly defrag the table.
> Does anyone have any suggestions?
> Thanks in advance.
> Jamie
> jamie.downs@.risk.sungard.com
>
>|||As a follow-on to what Andrew said, the extent fragmentation algorithm
inside DBCC SHOWCONTIG specifically does not work when an index/table spans
multiple files and this is stated in BooksOnLine.
You should read the whitepaper on fragmentation and how-to/whether-to
address it at:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, 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:O$44jy$kDHA.684@.TK2MSFTNGP09.phx.gbl...
> Jamie,
> A couple of questions first. Is this the entire table? I assume it has
> more columns but were left out for simplicity sake. If so it is usually
> best to shoe the actual DDL so there are no assumptions. If [ID] is
unique
> then why not just make it the PK and drop the PortfolofioID? Why do you
> create duplicate indexes? You have a PK constraint and a Unique
constraint
> on the two columns. This will make an index already to enforce the
> constraint. There is no need to add a second index to each one. The last
> comment is that I assume you have multiple files in your file group and
that
> is what is giving you the Extent fragmentation. This value is basically
> useless when you have multiple files.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Jamie" <jamie.downs@.risk.sungard.com> wrote in message
> news:0ee801c393e8$463c0460$a301280a@.phx.gbl...
> > I am currently trying to defrag a table and am unable to
> > reduce Extent fragmentation.
> >
> > The structure of the table is as follows.
> >
> > CREATE TABLE [dbo].[PortfolioMember] (
> > [PortfolioID] [int] NOT NULL ,
> > [ID] [int] NOT NULL
> > ) ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[PortfolioMember] WITH NOCHECK ADD
> > CONSTRAINT [PK_PORTFOLIOMEMBER] PRIMARY KEY
> > CLUSTERED
> > (
> > [PortfolioID],
> > [ID]
> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[PortfolioMember] ADD
> > CONSTRAINT [AK_IDPFOL_PORTFOLI] UNIQUE
> > NONCLUSTERED
> > (
> > [ID],
> > [PortfolioID]
> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [PortfolioMember_FK] ON [dbo].
> > [PortfolioMember]([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [has_members_FK] ON [dbo].[PortfolioMember]
> > ([PortfolioID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[PortfolioMember] ADD
> > CONSTRAINT [FK_PORTFOLI_RELATION__PORTFOLI]
> > FOREIGN KEY
> > (
> > [PortfolioID]
> > ) REFERENCES [dbo].[Portfolio] (
> > [PortfolioID]
> > )
> > GO
> >
> > So it has a clustered index.
> >
> > The Best I can get from DBCC SHOWCONTIG (PortfolioMember)
> >
> > DBCC SHOWCONTIG scanning 'PortfolioMember' table...
> > Table: 'PortfolioMember' (1429580131); index ID: 1,
> > database ID: 7
> > TABLE level scan performed.
> > - Pages Scanned........................: 26048
> > - Extents Scanned.......................: 3277
> > - Extent Switches.......................: 3283
> > - Avg. Pages per Extent..................: 7.9
> > - Scan Density [Best Count:Actual Count]......: 99.15%
> > [3256:3284]
> > - Logical Scan Fragmentation ..............: 0.03%
> > - Extent Scan Fragmentation ...............: 90.51%
> > - Avg. Bytes Free per Page................: 802.9
> > - Avg. Page Density (full)................: 90.08%
> > DBCC execution completed. If DBCC printed error messages,
> > contact your system administrator.
> >
> > I have tried the following all with no success at
> > reducing Extent fragmentation:
> >
> > DBCC DBREINDEX (portfoliomember, '', 90)
> >
> > DBCC DBREINDEX (portfoliomember, 'PK_PORTFOLIOMEMBER', 90)
> >
> > DBCC INDEXDEFRAG (CRS_BaseDev, portfoliomember,
> > PK_PORTFOLIOMEMBER)
> >
> > I have executed sp_spaceused and there is plenty of room.
> >
> > I am currently rebuilding the table but don't want to
> > have to do this to properly defrag the table.
> >
> > Does anyone have any suggestions?
> >
> > Thanks in advance.
> >
> > Jamie
> > jamie.downs@.risk.sungard.com
> >
> >
> >
> >
>sql
Subscribe to:
Posts (Atom)