August 13, 2015 at 11:37 am
Hello friend,
I Created index on table that contain 55% fragmantion so I REBUIT it. But still it has 29% Fragmantion so How I can remove that fragmentation?
I had also try to drop index and recreate it.
Thanks
August 13, 2015 at 11:58 am
Can you post the table and index creation code including the fill factor?
August 13, 2015 at 12:02 pm
If the index is on a small table, some amount of fragmentation can't be avoided because of the storage of pages in extants. If it's a large table, something doesn't seem right.
August 13, 2015 at 12:10 pm
CREATE NONCLUSTERED INDEX [INX_NONCLUSTER_State]
ON STATE_COUNTY_CODES(State)
with (FILLFACTOR=80)
CREATE TABLE STATE_COUNTY_CODES(
[County_Name] [varchar](150) NULL,
[State] [varchar](2) NULL,
[State_County] [varchar](5) NULL,
[County_Type] [varchar](15) NULL,
[State_ID] [int] IDENTITY(1,1) NOT NULL,
PRIMARY KEY CLUSTERED
August 13, 2015 at 12:11 pm
table has 3500 records
August 13, 2015 at 12:39 pm
meerack11 (8/13/2015)
table has 3500 records
This is the issue, which doesn't represent a problem since it's a small table. Check Ron's comment.
August 13, 2015 at 12:44 pm
If you're not experiencing slowdowns when accessing a table, I wouldn't worry about fragmentation percentages too much. Use it when you're investigating performance issues. Don't chase the number for it's own sake.
August 13, 2015 at 12:50 pm
so not worry right?
August 13, 2015 at 12:55 pm
Bottom line: No, there is no need to worry.
But... if you are experiencing performance issues accessing data from this table, then you should be concerned. But you don't mention any and given the small size of the table there would surely be another cause.
If you don't expect to add many or any records to this table, set the fill factor to 100%. You use a fill factor less than 100% when you expect to have a large number of inserts and you don't want the indexes to start fragmenting right away.
August 13, 2015 at 1:16 pm
Its Development so less record but in future when it deploy in production then it has large amount data going to insert. so what is suitable fill factor for it ?
and currently no need to worry abt fragmentation but what is other matter affect that fragmentation ?
August 13, 2015 at 1:23 pm
Fill factors are not an exact science. But given the situation you describe, I would start with 80 and see how it goes.
August 13, 2015 at 1:34 pm
Fill factor is not really defined by the amount of data in the table but on the rate of data modifications.
To generate some tests, I did the following:
IF OBJECT_ID( 'STATE_COUNTY_CODES') IS NOT NULL DROP TABLE STATE_COUNTY_CODES
CREATE TABLE STATE_COUNTY_CODES(
[County_Name] [varchar](150) NULL,
[State] [varchar](2) NULL,
[State_County] [varchar](5) NULL,
[County_Type] [varchar](15) NULL,
[State_ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
)
CREATE NONCLUSTERED INDEX [INX_NONCLUSTER_State]
ON STATE_COUNTY_CODES(State)
with (FILLFACTOR=100)
-- Used the TOP and ORDER BY to simulate your table
-- Commented it to increase the volume of the data
INSERT INTO STATE_COUNTY_CODES(
[County_Name],
[State],
[State_County],
[County_Type])
SELECT
--TOP (3500)
c.name,
StateProvinceCode,
LEFT( c.name, 3) + s.StateProvinceCode,
LEFT( NEWID(), 15)
FROM AdventureWorks2012.Person.StateProvince s
CROSS
JOIN sys.all_columns c
WHERE CountryRegionCode = 'US'
--ORDER BY NEWID()
--Review the fragmentation
SELECT OBJECT_NAME(i.object_id),i.name, s.*
FROM sys.indexes i
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), i.object_id, i.index_id, NULL, 'DETAILED') s
WHERE i.name = 'INX_NONCLUSTER_State'
ORDER BY avg_fragmentation_in_percent DESC
--Rebuilt the index playing with different fill factors
ALTER INDEX INX_NONCLUSTER_State
ON dbo.STATE_COUNTY_CODES
REBUILD --with (FILLFACTOR=0)
With an adequate volume of data, the fragmentation would likely be zero after a rebuilt. As this table shouldn't change much (according to the name), the fragmentation shouldn't be an issue and index maintenance should keep it in optimum conditions.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply