Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem in avg_page_space_used_in_percent value in fragmentation Expand / Collapse
Author
Message
Posted Monday, February 18, 2013 4:54 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 10, 2013 10:49 PM
Points: 92, Visits: 340
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
Posted Monday, February 18, 2013 5:36 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 3:43 PM
Points: 39,866, Visits: 36,206
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
Posted Monday, February 18, 2013 6:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 10, 2013 10:49 PM
Points: 92, Visits: 340
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
Posted Monday, February 18, 2013 7:26 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 3:43 PM
Points: 39,866, Visits: 36,206
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
Posted Monday, February 18, 2013 7:42 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 10, 2013 10:49 PM
Points: 92, Visits: 340
It means when number of pages will be small.........fragmentation not follow the rule. right ?

Regards,
Sachin.
Post #1421222
Posted Monday, February 18, 2013 7:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 3:43 PM
Points: 39,866, Visits: 36,206
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 »

Add to briefcase

Permissions Expand / Collapse