January 17, 2011 at 4:20 am
I have a situation like below and would like to get your expert opinion on the same.
We have a table TableA. It has a identity column Col_Ident but has clustered index defined on some other column Col_CLI because of access pattern. Now, we understand that page splits will occur in the clustered index on Col_CLI. But we are not sure whether Col_Ident(non-clustered, primary key) will ever undergo a page split.
I’m of the opinion, that there will be no page splits in the non-clustered index on Col_Ident since it is a monotonically increasing column. But, one of my friend says that since the non-clustered index will be based on the clustering key, there could be page splits.
I was cross checking DBCC IND and DBCC PAGE commands where i gone mad. Please help me out with the same.
January 17, 2011 at 5:19 am
It shouldn't page split due to inserts. As you say, it's an increasing column and new rows will always be at the 'end'. The clustering key will be present, but as an include column (since that's a unique nonclustered index). It might be able to split due to updates through. What's the data type of the clustered index key, can it ever change?
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
January 17, 2011 at 5:54 am
Hi Gail, Thanks for your comments.
Still, am not clear on certain point:
The data type is BIGINT for both columns. The cluster index data will undergo many operations like insert,delete and update.
Could you please explain a scenario to understand the situation in better way plz...
" It might be able to split due to updates through." .
January 17, 2011 at 6:16 am
You can only get page splits due to updates if you're increasing the data stored in a variable-length column or updating a column from null to not null.
Can the clustered index key (that bigint) be null at all? If so, can it be updated from null to a not-null value?
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
January 17, 2011 at 6:54 am
Clustered index would not be null at all in my case. However, one point in my mind is that at the intermediate level due to page splits on clustered index (I read that the intermediate level would be a clustered Key rather then the case of heap), would it lead to a page split on non-clustered index even its a sequential one.
I can see the NextPageID of DBCC IND is far way from the CurrentPageID.However, its sequential.Can we take this scenario as a page split? Once I defragment, all Pages are getting closer and sequential.
Also I can see a small percentage of fragmentation in this case. Is this fragmentation due to any kind of Page split?If I could add a FILLFACTOR for the non-clustered index would it be beneficial to reduce the fragmentation?
January 17, 2011 at 7:02 am
With a unique nonclustered index (which you have because it's the primary key) the clustering key won't be present at the intermediate level at all. It'll only be at the leaf level.
Why are you so worried about page splits?
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
January 17, 2011 at 7:32 am
Hi Gail,
Querying DMV, sys.dm db index operational stats, I can see some leaf allocation count and non leaf allocation count for these primary keys. In most of site, it refers a page split.
Answering your question"Why do you bother about page splits"
1. I would like to know the reason for the above count to understand the DMV. If the above are no way related to page splits, I would be able to take this DMV from my list to check the Page splits.
2. Even its not big; not claiming my db is perfect :), as long as these primary keys are no way in use of read operations, changing the fillfactor would benefit? I am curious to know the fact on the same.
3. Again, its small, if i could give some improvements in updates, if at al there any page splits, I would feel that I learnt something...
Thanks for your effort making me understand the things so far....
January 17, 2011 at 8:29 am
Can I look at the DMV for Page splits. Plz?
January 17, 2011 at 11:44 am
Appreciate any thoughts!!!
January 17, 2011 at 7:39 pm
Am I missing something in my question? Any thoughts?
January 17, 2011 at 9:39 pm
Non-clustered index with clustered index, table will be sorted based on indexed column and non-clustered indexes will point to the index key of clustered index and finally the data page. Here any page split in clustered index will need a modification on non-clustered index as well because it needs to point to the new indexkey of clustered index.
Ref:http://sql-server-performance.com/Community/forums/p/29598/156379.aspx
As above, would there be a chance of page splits in non-clustered index???
Once again, Appreciate your thoughts.
January 17, 2011 at 9:46 pm
The DM you're using will not help you find page splits. sys.dm_db_index_physical_stats will show you the affect that page and extent splits have had in the form of fragmentation and fragments.
DBCC Show_Contig used to do a better job, IMHO.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 17, 2011 at 9:54 pm
Hi Jeff, Thanks for your comments to look at the different angle of the DM.
Yes, I can see fragmentation and fragments also in the scenario. So can we take a conclusion that we will have page splits at non clustered level even its a sequentially ordered?
Can we consider a page allocation as a page split in case of index?
January 17, 2011 at 10:23 pm
sqlchanakya (1/17/2011)
So can we take a conclusion that we will have page splits at non clustered level even its a sequentially ordered?
No. Not necessarily but you certainly could if the "sequential" ordering was on something other than an IDENTITY column.
Can we consider a page allocation as a page split in case of index?
No. A page allocation can be the simple growth of an index or it could be a page split.
From the questions you're asking, I recommend you spend some time in Books Online (the help system that comes with SQL Server) on the area of index architecture and the like.
As a side bar, you need to be a little more patient with your posts. You "bumped" your own post 4 times yesterday. Today was a holiday for a lot of the folks in the U.S. and the rest of the people in the world were working. They don't always have the time to drop everything to answer a question on a volunteer site.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply