Frequent Index Fragmentation

Question

Frequent Index Fragmentation

Jai-SQL DBA

Ten Centuries

Points: 1330
More actions
September 15, 2014 at 7:21 am

#311503

Hi guys,
I am facing an issue with index fragmentation.I have a DB in which few of the tables have started getting fragmented very frequently .As I rebuild the index & lowers the fragmentation to 10 % it crosses 90% with in an hour or so.Nothing from the application perspective has been changed.
Can you please help understanding what could be the reason and what solution can be implemented to sort this out.Please understand that the server in concerns is a prod server and cant be played with :(.
Thanks in advance

Viewing 10 posts - 1 through 9 (of 9 total)

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

Sean Lange SSC Guru Points: 286562 More actions · Answer 1

Jai-SQL DBA (9/15/2014)
Hi guys,
I am facing an issue with index fragmentation.I have a DB in which few of the tables have started getting fragmented very frequently .As I rebuild the index & lowers the fragmentation to 10 % it crosses 90% with in an hour or so.Nothing from the application perspective has been changed.
Can you please help understanding what could be the reason and what solution can be implemented to sort this out.Please understand that the server in concerns is a prod server and cant be played with :(.
Thanks in advance

Not much in the way of details here. What do the table structures and indexes look like? Sounds like you have a pretty busy table. I am going to take a shot in the dark and guess you have a clustered index on a guid?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jai-SQL DBA Ten Centuries Points: 1330 More actions · Answer 2

CREATE TABLE [dbo].[TEST](

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[NIV] [varchar](17) NULL,

[SLT] [smallint] NULL,

[ORD] [nvarchar](12) NOT NULL,

[FULLSLT] [int] NULL,

[Status] [nvarchar](10) NULL,

CONSTRAINT [PK_dbo.TEST] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]

) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_TEST] ON [dbo].[TEST]

(

[NIV] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]

GO

This is My table looks like and INDEX [IX_TEST] keeps on fragmenting

Sean Lange SSC Guru Points: 286562 More actions · Answer 3

Jai-SQL DBA (9/15/2014)
CREATE TABLE [dbo].[TEST](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[NIV] [varchar](17) NULL,
[SLT] [smallint] NULL,
[ORD] [nvarchar](12) NOT NULL,
[FULLSLT] [int] NULL,
[Status] [nvarchar](10) NULL,
CONSTRAINT [PK_dbo.TEST] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_TEST] ON [dbo].[TEST]
(
[NIV] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
GO
This is My table looks like and INDEX [IX_TEST] keeps on fragmenting

Not surprising you are seeing massive fragmentation. Your fill factor is set to 99. Why is it so high?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jai-SQL DBA Ten Centuries Points: 1330 More actions · Answer 4

it was always the same ...can u suggest the number what should be in there ?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

Depends. If you see frequent fragmentation, you can drop it down slightly (not a lot, bit by bit). There's no value which is always correct for all cases.

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

Maintainer Old Hand Points: 318 More actions · Answer 6

I would try 10% as a guideline. But that may not be appropriate for your situation.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

Maintainer (9/17/2014)
I would try 10% as a guideline. But that may not be appropriate for your situation.

Just to be clear to the OP... that's not the FILL FACTOR... that's how much space to leave open on the first or second try. To leave 10% free space, you would set the FILL FACTOR to 90%.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jai-SQL DBA Ten Centuries Points: 1330 More actions · Answer 8

yes I understood that :)) thanks ...I have changed it to 90 and as I see the fragmentation rate has lower down,it is not that frequent as it used to be,I need to play with the values carefully and analyse further,thanks everyone for their support and I would come back ,if required.

Much Appreciated

Sean Lange SSC Guru Points: 286562 More actions · Answer 9

Jai-SQL DBA (9/18/2014)
yes I understood that :)) thanks ...I have changed it to 90 and as I see the fragmentation rate has lower down,it is not that frequent as it used to be,I need to play with the values carefully and analyse further,thanks everyone for their support and I would come back ,if required.
Much Appreciated

You will probably need to monitor that over the next few days/weeks and tweak the fill factor ever so slightly until you find the tipping point.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/