question on indexes

Question

question on indexes

Dhruva_51

SSChampion

Points: 10521
More actions
July 13, 2016 at 11:35 am

#311706

If we have a fill factor of 100 and a table is hit with heavy inserts and deletes. How the new data gets inserted? Does the data removed from the pages which might be half filled get used by the new inserts or new inserts goes into new pages at the end.

Viewing 8 posts - 1 through 7 (of 7 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Does this table have a clustered index or not? Can you provide the full create table script for the table (including all indexes)?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Dhruva_51 SSChampion Points: 10521 More actions · Answer 2

Table has cluster index with default setting fill factor. The table has around 15 indexes including clustered. I just went through all indexes and some are set to fill factor 85 and some to 90.

Phil Parkin SSC Guru Points: 246957 More actions · Answer 3

muth_51 (7/13/2016)
Table has cluster index with default setting fill factor. The table has around 15 indexes including clustered. I just went through all indexes and some are set to fill factor 85 and some to 90.

We cannot answer this question without seeing the DDL for the table.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Phil Parkin (7/13/2016)
muth_51 (7/13/2016)
Table has cluster index with default setting fill factor. The table has around 15 indexes including clustered. I just went through all indexes and some are set to fill factor 85 and some to 90.
We cannot answer this question without seeing the DDL for the table.

As well as the values that are being inserted.

That seems like a lot of indexes. Did you check for duplicate/overlapping ones?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 5

muth_51 (7/13/2016)
If we have a fill factor of 100 and a table is hit with heavy inserts and deletes. How the new data gets inserted? Does the data removed from the pages which might be half filled get used by the new inserts or new inserts goes into new pages at the end.

Both, but I'd strongly suggest you deal with all the questions and concerns the others have brought up.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 6

Each new row will go to the page in each index where it belongs, based on its new place in the index. If there happens to be enough room on that page for the row, it will be written there.

If the new row's value(s) for a given index place it at the end of the index (such as an index on an identity value), then the row is placed on the last page of the index. If the last page in the index doesn't have room for that row then a new page is allocated and added at the end of the index for it. Side note:Even though that page is added to the end of the index, adding that page still raises the 'page split occurred' event for XEvents and some other monitoring endpoints.

If the new row's key value(s) for an index place it anywhere within the index(such as an index on a name, where new values can go anywhere in the index), then the row will be placed on the page in the index (ordered by those keys) where it belongs. If that page is full, then trigger a split, and send half the page's contents - the half closer to the end of the index - to the new page. The new row would land on one of those two pages, based on its order within the rows on them.

This process happens for each insert for each of the fifteen indexes you have on this table. Do you see lots of IO_COMPLETION waits?

-Eddie

Eddie Wuerch
MCM: SQL

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

Side note:Even though that page is added to the end of the index, adding that page still raises the 'page split occurred' event for XEvents and some other monitoring endpoints.

I sure wish the SQL Server product team would change that one, adding a separate flag or event that says it was just a new allocation at the very end. Those two things are incredibly different IMNSHO.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service