SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index and Page Split


Index and Page Split

Author
Message
WhiteLotus
WhiteLotus
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2768 Visits: 971
Hi all,

I have some indexes which get fragmented very quickly ...

I set the fill factor starting from 70 and keep monitoring the result . Yesterday I set fill factor 30 but it still get fragmented easily even though it is a bit better compared with fill factor 70 ...

What should I do ?

Should I lower it down again , for example fill factor 20 ??

Moreover , I noticed the PAGE SPLIT/Sec is still high ..around 90 . How do i know which table that cause that page split ?

Many thanks....
SQL Guy 1
SQL Guy  1
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3610 Visits: 2598
What is data type, length, and number of columns?
How intensively it is used - from sys.dm_db_index_usage_stats ?
WhiteLotus
WhiteLotus
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2768 Visits: 971
SQL Guy 1 (7/31/2014)
What is data type, length, and number of columns?
How intensively it is used - from sys.dm_db_index_usage_stats ?


Thanks for your kind response . Appreciate it!
Here is the data for 2 different databases ( the indexes get fragmented in only a few hours after rebuild ):

on Database DB1

Index IX_P (Non clustered index)


Index key column :
P1(datetime) , size : 8 , Identity : No

included column :
ReaderId (Int)


fragmentation : 92.31%
pages : 806
fill factor 65%


Usage stat :

USER_SEEKS USER_SCANS USER_LOOKUPS USER_UPDATES
0 702 0 2826040

--------------------------------------------------------------------------------
on Database DB2


Index IX_A

Profile :

Index Key columns :
A1 (int)
A2 (TinyInt)
A3 (Bigint)

Fragmentation :81.44 %
Pagecount : 62593
Fill Factor : 70 %



Usage stat :

USER_SEEKS USER_SCANS USER_LOOKUPS USER_UPDATES
49476 0 0 0
0 0 65176 4753949
8247327 0 2551491 2873118
0 1 0 2
-----------------------------------------------------------------------------
Index B (nonclustered)

Profile :

Index Key columns:
B1 (Bigint)

Included column :
X1 (tinyint)
X2 (varbinary(max)
X3 (bit)
X4 (int)
X5 (tinyint)
X6 (datetime)
X7 (bigint)
X8 (uniqueidentifier)


Fragmentation : 97.24%
Pagecount : 63379
Fill Factor : 70%


Usage stat :

USER_SEEKS USER_SCANS USER_LOOKUPS USER_UPDATES
0 0 0 2873118

2 weeks ago :

USER_SEEKS USER_SCANS USER_LOOKUPS USER_UPDATES

0 4 0 15808493

758019 0 0 11550284

----------------------------------------------------------------------------
Index IX_C (nonclustered)

Profile :
Index key columns :
C1(bigint)


Fragmentation : 79.43%
Pagecount : 2163
Fillfactor : 70%

Usage stat:

USER_SEEKS USER_SCANS USER_LOOKUPS USER_UPDATES
2253136 6278 0 563608
1 0 0 0
28454 1119990 0 10

What do you think about it ?
Many thanks Smile
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214046 Visits: 41979
murnilim9 (7/31/2014)
SQL Guy 1 (7/31/2014)
What is data type, length, and number of columns?
How intensively it is used - from sys.dm_db_index_usage_stats ?


Thanks for your kind response . Appreciate it!
Here is the data for 2 different databases ( the indexes get fragmented in only a few hours after rebuild ):

on Database DB1

Index IX_P (Non clustered index)


Index key column :
P1(datetime) , size : 8 , Identity : No

included column :
ReaderId (Int)


fragmentation : 92.31%
pages : 806
fill factor 65%


Usage stat :

USER_SEEKS USER_SCANS USER_LOOKUPS USER_UPDATES
0 702 0 2826040

--------------------------------------------------------------------------------
on Database DB2


Index IX_A

Profile :

Index Key columns :
A1 (int)
A2 (TinyInt)
A3 (Bigint)

Fragmentation :81.44 %
Pagecount : 62593
Fill Factor : 70 %



Usage stat :

USER_SEEKS USER_SCANS USER_LOOKUPS USER_UPDATES
49476 0 0 0
0 0 65176 4753949
8247327 0 2551491 2873118
0 1 0 2
-----------------------------------------------------------------------------
Index B (nonclustered)

Profile :

Index Key columns:
B1 (Bigint)

Included column :
X1 (tinyint)
X2 (varbinary(max)
X3 (bit)
X4 (int)
X5 (tinyint)
X6 (datetime)
X7 (bigint)
X8 (uniqueidentifier)


Fragmentation : 97.24%
Pagecount : 63379
Fill Factor : 70%


Usage stat :

USER_SEEKS USER_SCANS USER_LOOKUPS USER_UPDATES
0 0 0 2873118

2 weeks ago :

USER_SEEKS USER_SCANS USER_LOOKUPS USER_UPDATES

0 4 0 15808493

758019 0 0 11550284

----------------------------------------------------------------------------
Index IX_C (nonclustered)

Profile :
Index key columns :
C1(bigint)


Fragmentation : 79.43%
Pagecount : 2163
Fillfactor : 70%

Usage stat:

USER_SEEKS USER_SCANS USER_LOOKUPS USER_UPDATES
2253136 6278 0 563608
1 0 0 0
28454 1119990 0 10

What do you think about it ?
Many thanks Smile


Are any of these causing performance issues?

Also, except for unique indexes (which may be part of constraints), I'd be tempted to simply drop any index that had a large number of User_Updates and very few or zero index seeks. User_Scans (a bit faster than heap or CI scans) can be useful indexes but, with 2,826,040, 0 seeks, and only 702 scans, Index IX_P (Non clustered index) looks pretty useless to me and probably drop that one as well, unless it's a unique index.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
WhiteLotus
WhiteLotus
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2768 Visits: 971
Jeff Moden (7/31/2014)
murnilim9 (7/31/2014)
SQL Guy 1 (7/31/2014)
What is data type, length, and number of columns?
How intensively it is used - from sys.dm_db_index_usage_stats ?


Thanks for your kind response . Appreciate it!
Here is the data for 2 different databases ( the indexes get fragmented in only a few hours after rebuild ):

on Database DB1

Index IX_P (Non clustered index)


Index key column :
P1(datetime) , size : 8 , Identity : No

included column :
ReaderId (Int)


fragmentation : 92.31%
pages : 806
fill factor 65%


Usage stat :

USER_SEEKS USER_SCANS USER_LOOKUPS USER_UPDATES
0 702 0 2826040

--------------------------------------------------------------------------------
on Database DB2


Index IX_A

Profile :

Index Key columns :
A1 (int)
A2 (TinyInt)
A3 (Bigint)

Fragmentation :81.44 %
Pagecount : 62593
Fill Factor : 70 %



Usage stat :

USER_SEEKS USER_SCANS USER_LOOKUPS USER_UPDATES
49476 0 0 0
0 0 65176 4753949
8247327 0 2551491 2873118
0 1 0 2
-----------------------------------------------------------------------------
Index B (nonclustered)

Profile :

Index Key columns:
B1 (Bigint)

Included column :
X1 (tinyint)
X2 (varbinary(max)
X3 (bit)
X4 (int)
X5 (tinyint)
X6 (datetime)
X7 (bigint)
X8 (uniqueidentifier)


Fragmentation : 97.24%
Pagecount : 63379
Fill Factor : 70%


Usage stat :

USER_SEEKS USER_SCANS USER_LOOKUPS USER_UPDATES
0 0 0 2873118

2 weeks ago :

USER_SEEKS USER_SCANS USER_LOOKUPS USER_UPDATES

0 4 0 15808493

758019 0 0 11550284

----------------------------------------------------------------------------
Index IX_C (nonclustered)

Profile :
Index key columns :
C1(bigint)


Fragmentation : 79.43%
Pagecount : 2163
Fillfactor : 70%

Usage stat:

USER_SEEKS USER_SCANS USER_LOOKUPS USER_UPDATES
2253136 6278 0 563608
1 0 0 0
28454 1119990 0 10

What do you think about it ?
Many thanks Smile


Are any of these causing performance issues?

Also, except for unique indexes (which may be part of constraints), I'd be tempted to simply drop any index that had a large number of User_Updates and very few or zero index seeks. User_Scans (a bit faster than heap or CI scans) can be useful indexes but, with 2,826,040, 0 seeks, and only 702 scans, Index IX_P (Non clustered index) looks pretty useless to me and probably drop that one as well, unless it's a unique index.


ohh thanks
All of them cause trouble
I have removed that index hohohohoh
but what i need to do with the rest ?

But I
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214046 Visits: 41979
You could disable them which drops all of the data from the index but keeps the meta data and see if peformance suffers. Of course, you need to do a test on whatever is using the indexes before you do that. If performance does suffer, rebuild the index and it will reform, data and all.

Of course, there are the normal warnings about doing this on high usage or large tables during business hours and that such action could cause some substantial logfile growth like any index reorg (no matter the recovery model) or index rebuild (in the FULL recovery model) can cause.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
WhiteLotus
WhiteLotus
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2768 Visits: 971
Jeff Moden (8/1/2014)
You could disable them which drops all of the data from the index but keeps the meta data and see if peformance suffers. Of course, you need to do a test on whatever is using the indexes before you do that. If performance does suffer, rebuild the index and it will reform, data and all.

Of course, there are the normal warnings about doing this on high usage or large tables during business hours and that such action could cause some substantial logfile growth like any index reorg (no matter the recovery model) or index rebuild (in the FULL recovery model) can cause.


Thanks for your response ..It is a good idea .. I could disable 1 index that has zero index seek but I highly hesitate to disable the others that has high number of index seek ...

I still don't know what to do with the indexes that have high number of index seek....
GilaMonster
GilaMonster
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223237 Visits: 46296
murnilim9 (7/30/2014)
Moreover , I noticed the PAGE SPLIT/Sec is still high ..around 90 . How do i know which table that cause that page split ?


That counter is misleading. It is not the number of mid-index page splits (which are the ones which cause fragmentation)

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


WhiteLotus
WhiteLotus
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2768 Visits: 971
GilaMonster (8/3/2014)
murnilim9 (7/30/2014)
Moreover , I noticed the PAGE SPLIT/Sec is still high ..around 90 . How do i know which table that cause that page split ?


That counter is misleading. It is not the number of mid-index page splits (which are the ones which cause fragmentation)


Hi..
Thx for your response ...I wonder what do you mean by mid-index page splits ?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214046 Visits: 41979
WhiteLotus (8/3/2014)
Jeff Moden (8/1/2014)
You could disable them which drops all of the data from the index but keeps the meta data and see if peformance suffers. Of course, you need to do a test on whatever is using the indexes before you do that. If performance does suffer, rebuild the index and it will reform, data and all.

Of course, there are the normal warnings about doing this on high usage or large tables during business hours and that such action could cause some substantial logfile growth like any index reorg (no matter the recovery model) or index rebuild (in the FULL recovery model) can cause.


Thanks for your response ..It is a good idea .. I could disable 1 index that has zero index seek but I highly hesitate to disable the others that has high number of index seek ...

I still don't know what to do with the indexes that have high number of index seek....


Apologies... I was relying on the flow of this thread to avoid having to include specific context...

Of course you wouldn't disable indexes with a large number of index seeks. You would only do such a thing on indexes that had little or no seeks compared to updates. Scans on indexes would definitely be a judgement call after some further investigation to see if the index could/should (not all should necessarily be) be modified to use seeks.

You would also avoid disabling UNIQUE indexes whether they have seeks or scans against them or not.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search