Problem in avg_page_space_used_in_percent value in fragmentation

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Problem in avg_page_space_used_in_percent...

Problem in avg_page_space_used_in_percent value in fragmentation

Rate Topic

Display Mode

Topic Options

Author

Message

dsachu

Posted Monday, February 18, 2013 4:54 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, May 13, 2013 3:45 AM
Points: 90, Visits: 335

Hi,

I have some confusion so i required some help.

I am using below statement in sql server to reduce fragmentation

alter index PK_AccountCodeMaster_1 on AccountCodeMaster rebuild

but in output I always getting the value of "avg_page_space_used_in_percent" is less than 10

can please suggest......how i will increase the value and would request you please guide me .....why sql is not it's increase it's value more than 10 automatically during rebuild.

Is there any problem in my concept ?
-----------------------------------------------------------

Table_Name : CanvasClaimsRuralMarkup

Index_Name : PK_CanvasClaimsRuralMarkup

avg_fragmentation_in_percent : 0

avg_page_space_used_in_percent : 7.042253521

Regards,
Sachin.

Post #1421138

GilaMonster

Posted Monday, February 18, 2013 5:36 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:01 PM
Points: 37,669, Visits: 29,923

How big is the table? How many pages?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1421154

dsachu

Posted Monday, February 18, 2013 6:55 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, May 13, 2013 3:45 AM
Points: 90, Visits: 335

Hi Gail,

By mistake I have given you wrong table

I am using below statement in sql server to reduce fragmentation

alter index PK_JG_UserLogin on UserLogins rebuild

but in output I always Getting after fragmentation

Table_Name : UserLogins
Index_Name : PK_JG_UserLogin
avg_fragmentation_in_percent : 80
avg_page_space_used_in_percent : 84.20311342

Table_Name : UserLogins
Index_Name : PK_JG_UserLogin
avg_fragmentation_in_percent : 0
avg_page_space_used_in_percent : 1.198418582

As per my knowledge... After fragmentation, I required Value like

Table_Name : UserLogins
Index_Name : PK_JG_UserLogin
avg_fragmentation_in_percent : 0
avg_page_space_used_in_percent : 98.20311342

Table_Name : UserLogins
Index_Name : PK_JG_UserLogin
avg_fragmentation_in_percent : 0
avg_page_space_used_in_percent : 98.198418582

DBCC SHOWCONTIG (UserLogins);
------------------------*/
DBCC SHOWCONTIG scanning 'UserLogins' table...
Table: 'UserLogins' (515532920); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 30
- Extents Scanned..............................: 7
- Extent Switches..............................: 6
- Avg. Pages per Extent........................: 4.3
- Scan Density [Best Count:Actual Count].......: 57.14% [4:7]
- Extent Scan Fragmentation ...................: 71.43%
- Avg. Bytes Free per Page.....................: 1762.1
- Avg. Page Density (full).....................: 78.23%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

----------------------------------------------------------------
ObjectID : 515532920
IndexID : 2
PercentFragment : 0
TotalFrags : 1
PagesPerFrag : 1
NumPages : 1

So, would request you please suggest me why sql is not fragment as per my understanding........because I have read some article.

As per article when value of "avg_fragmentation_in_percent is less" is less than 10 then it will be better and when avg_page_space_used_in_percent is greater than 95 then it will be better.

So if any concept problem please guide me.

Regards,
Sachin.

Post #1421195

GilaMonster

Posted Monday, February 18, 2013 7:26 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:01 PM
Points: 37,669, Visits: 29,923

dsachu (2/18/2013)

So, would request you please suggest me why sql is not fragment as per my understanding........because I have read some article.

Because the table's far too small for you to care about fragmentation much. The index in question is a single page.

ObjectID : 515532920
IndexID : 2
PercentFragment : 0
TotalFrags : 1
PagesPerFrag : 1
NumPages : 1

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1421213

dsachu

Posted Monday, February 18, 2013 7:42 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, May 13, 2013 3:45 AM
Points: 90, Visits: 335

It means when number of pages will be small.........fragmentation not follow the rule. right ?

Regards,
Sachin.

Post #1421222

GilaMonster

Posted Monday, February 18, 2013 7:56 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:01 PM
Points: 37,669, Visits: 29,923

When the number of pages is low, you probably don't want to worry about fragmentation. Besides, you can't defragment a 1 page index.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1421234

« Prev Topic | Next Topic »

Permissions