July 13, 2016 at 11:35 am
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.
July 13, 2016 at 12:08 pm
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
July 13, 2016 at 12:11 pm
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.
July 13, 2016 at 12:36 pm
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 13, 2016 at 1:35 pm
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
July 14, 2016 at 7:01 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 14, 2016 at 8:15 pm
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
July 15, 2016 at 7:41 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply