InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 21:13:35 GMT20http://www.sqlservercentral.com/Forums/Topic1421138-146-1.aspxWhen the number of pages is low, you probably don't want to worry about fragmentation. Besides, you can't defragment a 1 page index.Mon, 18 Feb 2013 07:56:25 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1421138-146-1.aspxIt means when number of pages will be small.........fragmentation not follow the rule. right ?Regards,Sachin.Mon, 18 Feb 2013 07:42:18 GMTdsachuhttp://www.sqlservercentral.com/Forums/Topic1421138-146-1.aspx[quote][b]dsachu (2/18/2013)[/b][hr]So, would request you please suggest me why sql is not fragment as per my understanding........because I have read some article.[/quote]Because the table's far too small for you to care about fragmentation much. The index in question is a single page.[quote]ObjectID : 515532920IndexID : 2PercentFragment : 0TotalFrags : 1PagesPerFrag : 1[b]NumPages : 1[/b][/quote]Mon, 18 Feb 2013 07:26:19 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1421138-146-1.aspxHi 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 [b] Getting after fragmentation[u][/u][/b]Table_Name : UserLogins Index_Name : PK_JG_UserLoginavg_fragmentation_in_percent : 80avg_page_space_used_in_percent : 84.20311342Table_Name : UserLogins Index_Name : PK_JG_UserLoginavg_fragmentation_in_percent : 0avg_page_space_used_in_percent : 1.198418582As per my knowledge... After fragmentation, I required Value likeTable_Name : UserLogins Index_Name : PK_JG_UserLoginavg_fragmentation_in_percent : 0avg_page_space_used_in_percent : 98.20311342Table_Name : UserLogins Index_Name : PK_JG_UserLoginavg_fragmentation_in_percent : 0avg_page_space_used_in_percent : 98.198418582DBCC SHOWCONTIG (UserLogins);------------------------*/DBCC SHOWCONTIG scanning 'UserLogins' table...Table: 'UserLogins' (515532920); index ID: 0, database ID: 5TABLE 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 : 515532920IndexID : 2 PercentFragment : 0TotalFrags : 1PagesPerFrag : 1 NumPages : 1So, 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.Mon, 18 Feb 2013 06:55:52 GMTdsachuhttp://www.sqlservercentral.com/Forums/Topic1421138-146-1.aspxHow big is the table? How many pages?Mon, 18 Feb 2013 05:36:35 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1421138-146-1.aspxHi,I have some confusion so i required some help.I am using below statement in sql server to reduce fragmentation [b]alter index PK_AccountCodeMaster_1 on AccountCodeMaster rebuild [/b]but in output I always getting the value of "avg_page_space_used_in_percent" is less than 10can 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_CanvasClaimsRuralMarkupavg_fragmentation_in_percent : 0avg_page_space_used_in_percent : 7.042253521Regards,Sachin.Mon, 18 Feb 2013 04:54:08 GMTdsachu