December 28, 2012 at 1:13 am
Created a table
Create table tmpsize(
tmpsizepk int identity,
Error_Msg varchar(8000)
)
Inserted 500 rows to the table
Declare @i as int
SET @i = 0
While @i<500
Begin
Insert into tmpsize values('Table used few pages of each extent. Extent Scan Fragmentation increased. How to use all the pages of a extent for this table')
SET @i = @i + 1
End
Checked the Extent and pages count using
DBCC SHOWCONTIG('tmpsize')
It showed like
DBCC SHOWCONTIG scanning 'tmpsize' table...
Table: 'tmpsize' (657085777); index ID: 0, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 10
- Extents Scanned..............................: 9
- Extent Switches..............................: 8
- Avg. Pages per Extent........................: 1.1
- Scan Density [Best Count:Actual Count].......: 22.22% [2:9]
- Extent Scan Fragmentation ...................: 44.44%
- Avg. Bytes Free per Page.....................: 996.0
- Avg. Page Density (full).....................: 87.69%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
It showed the pages count as 10. If we insert data for 8 pages it should use uniform extent. So only 2 extents should be used but it used 9 extents. Did i missed anything? How to use uniform extent instead of mixed extend. Please help me
December 28, 2012 at 2:01 am
Sql server internally handles the data storage.
See the quote
A new table or index is generally allocated pages from mixed extents. When the table or index grows to the point that it has eight pages, it then switches to use uniform extents for subsequent allocations. If you create an index on an existing table that has enough rows to generate eight pages in the index, all allocations to the index are in uniform extents.
from MSDN BOL
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 28, 2012 at 2:20 am
Don't fuss with the way SQL stores data. It's not your decision, it's up to the database engine (its one of the requirements of a relational database system)
With larger tables, once you do an index rebuild, the table will use all dedicated extents. For tiny tables like that, don't worry, it's not going to affect much, if anything.
p.s. DBCC ShowContig is deprecated, included only for backward compatibility with SQL 2000 and should not be used.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 28, 2012 at 3:20 am
Thanks Mr.Bhuvnesh. Now i have created an index
CREATE INDEX IX_tmpsize_tmpsizepk
ON tmpsize (tmpsizepk);
Again the count of Extent remains same as before.
December 28, 2012 at 4:03 am
gkganeshbe (12/28/2012)
Thanks Mr.Bhuvnesh. Now i have created an indexCREATE INDEX IX_tmpsize_tmpsizepk
ON tmpsize (tmpsizepk);
Again the count of Extent remains same as before.
thats what gail explained above.For tiny table you can't see this.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 28, 2012 at 5:00 am
gkganeshbe (12/28/2012)
It showed the pages count as 10. If we insert data for 8 pages it should use uniform extent. So only 2 extents should be used but it used 9 extents. Did i missed anything? How to use uniform extent instead of mixed extend. Please help me
now i have tested the same script for 55K records
and got the below results
Table: 'tmpsize' (935674381); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 1828
- Extents Scanned..............................: 232
- Extent Switches..............................: 231
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 98.71% [229:232]
- Extent Scan Fragmentation ...................: 3.45%
- Avg. Bytes Free per Page.....................: 289.1
- Avg. Page Density (full).....................: 96.43%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
NOw it can give you what you want to see (1828/232~ 8 )
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply