how to use all the pages of extent for the same table

Question

Post reply

how to use all the pages of extent for the same table

Ganesh Kamal

SSChasing Mays

Points: 612
More actions
December 28, 2012 at 1:13 am

#272391

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

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;-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ganesh Kamal SSChasing Mays Points: 612 More actions · Answer 3

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.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

gkganeshbe (12/28/2012)
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.

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;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

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;-)