May 18, 2015 at 3:44 pm
I would like to put a Clustered Index on a date column in a current heap, but one question/concern.
This heap every month has thousands of rows deleted and even more added later.
How much of an issue will this cause the Clustered Index as far as page splits? I was thinking Fill Factor of 70%.
I would normally just test and still will on Dev box, but my Dev box is much smaller than production as far as power.
Thank you!
May 19, 2015 at 1:18 am
Delete doesn't cause page splits, so you don't have to worry about the delete statements regarding page splits. Inserts might cause page split. It depends on the value that you insert. If the value is always increasing (for example, it is always the value of the function getdate()), then inserting new records will not cause page split, but if new records can have dates that were already passed in the table, then you can have page splits. Unfortunately no one can give you a magic number for the fill factor. It depends on to many factors that we can't know – Rate of inserts, Size of records, range of values in the date column, etc'. You'll just have to test it. Also the fact that your dev box has different specification then the production box isn't relevant to number of page splits. Page splits have nothing to do with CPU power or memory.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 19, 2015 at 4:55 am
You'll have to test it, monitor it, and adjust as needed. That's the normal approach to any of this. I'm not even sure that I would mess with the fill factor out of the gate unless you're sure, through testing and monitoring, that you will need it to be adjusted from the default.
"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
May 19, 2015 at 7:34 am
Thank you and I will do just that. I was just curious about page splits since deleting and adding data will be common in this instance.
I'm curious about two things as far as best practice on Indexes.
1. Is it common to create a Clustered Index on a temp table?
2. Would you put Indexes on separate disk or does this really matter?
Thank you again!
May 19, 2015 at 8:15 am
butcherking13 (5/19/2015)
Thank you and I will do just that. I was just curious about page splits since deleting and adding data will be common in this instance.I'm curious about two things as far as best practice on Indexes.
1. Is it common to create a Clustered Index on a temp table?
I wouldn't say I put one on all temp tables. But it's a common practice if I'm going to be doing filtering or joining (especially joining) with that temp table. Probably best to approach it case by case with no hard and fast rule.
2. Would you put Indexes on separate disk or does this really matter?
Thank you again!
That is a tough call. If you put all your heaps and clustered indexes on one disk and your nonclustered indexes on another, does that help performance? Maybe. It really depends. I would only suggest approaching splitting storage as you identify the need for splitting storage. Also, be cautious about saying "indexes" on a separate disk because the clustered index is the data, so I've seen people very carefully splitting storage from indexes only to include the clustered index on the index storage which utterly defeats the purpose.
"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
May 19, 2015 at 8:42 am
butcherking13 (5/19/2015)
Thank you and I will do just that. I was just curious about page splits since deleting and adding data will be common in this instance.I'm curious about two things as far as best practice on Indexes.
1. Is it common to create a Clustered Index on a temp table?
2. Would you put Indexes on separate disk or does this really matter?
Thank you again!
1. If it results in a net benefit, yes. If it results in a net loss, no. Measure the full lifecycle with and without using SQL:BatchCompleted in Profiler, with the Reads, Writes, CPU, and Duration values having whatever weighting is appropriate to your environment.
1a. For optimal results, you can also vary when in the table lifecycle the index is added, and how ORDER BY statements on your INSERTs affects the net result.
2. Depends; are they truly dedicated disks at the physical device/spindle/flash chip level? If so... why do you still have an empty one sitting around? If not, I wouldn't bother.
May 19, 2015 at 9:12 am
That is what I was looking for as temp tables are joining.
Appreciate your response! Thank you!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply