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 12»»

Index and Page Split Expand / Collapse
Author
Message
Posted Wednesday, July 30, 2014 9:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 5:03 PM
Points: 122, Visits: 282
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....
Post #1598078
Posted Thursday, July 31, 2014 9:44 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:19 AM
Points: 457, Visits: 1,486
What is data type, length, and number of columns?
How intensively it is used - from sys.dm_db_index_usage_stats ?
Post #1598341
Posted Thursday, July 31, 2014 6:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 5:03 PM
Points: 122, Visits: 282
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 :)
Post #1598543
Posted Thursday, July 31, 2014 8:50 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 9:16 AM
Points: 35,400, Visits: 31,962
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 :)


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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1598555
Posted Thursday, July 31, 2014 10:04 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 5:03 PM
Points: 122, Visits: 282
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 :)


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
Post #1598559
Posted Friday, August 1, 2014 10:22 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 9:16 AM
Points: 35,400, Visits: 31,962
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1598762
Posted Sunday, August 3, 2014 5:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 5:03 PM
Points: 122, Visits: 282
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....
Post #1599025
Posted Sunday, August 3, 2014 5:56 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
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 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 #1599026
Posted Sunday, August 3, 2014 6:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 5:03 PM
Points: 122, Visits: 282
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 ?

Post #1599028
Posted Sunday, August 3, 2014 9:48 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 9:16 AM
Points: 35,400, Visits: 31,962
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1599054
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse