February 22, 2011 at 10:23 pm
Hello Friends, Can anyone help me with the strategy to make indexes on OLTP tables ?
February 23, 2011 at 6:12 am
sql_butterfly (2/22/2011)
Hello Friends, Can anyone help me with the strategy to make indexes on OLTP tables ?
That is a gigantic topic and really completely depends on the tables in questions and the queries being run against them. There are entire books written on how to best tune your system (for example, look at my signature line). The one thing I can say, as far as a strategy for indexing goes, plan on having a clustered index on every table, especially in an OLTP system. The clustered index should be on the column or columns that is both most selective and most frequently used to access the data. This is because the data itself is stored with the clustered index, so making the choice of which index is the cluster becomes extremely important. After that, it really depends on the data, the queries, the data structure's, frequency of access, etc.
"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
February 23, 2011 at 10:58 pm
Thanks Grant. What should be the generalized fillfactor for indexes on OLTP tables ?
February 24, 2011 at 2:10 am
sql_butterfly (2/23/2011)
Thanks Grant. What should be the generalized fillfactor for indexes on OLTP tables ?
I would say it depends. You shall monitor the number of reads and writes on a specific table and based on the results you shall decide.
For instance you would use a high fill factor (100%) where there are a very few writes and would use a lower fill factor (60-70%) if there are numerous write operations, otherwise pick a value somewhere between the two eg. 85%.
This is just me, I'd love to hear what others say.
__________________________
Allzu viel ist ungesund...
February 24, 2011 at 6:24 am
sql_butterfly (2/23/2011)
Thanks Grant. What should be the generalized fillfactor for indexes on OLTP tables ?
I agree with Mr. Holio, or can I call you Bung? It depends. In general, I accept the defaults until and unless I know I've got a reason to change them. It works well enough in most cases, but then you'll find situations where you'll want to radically reduce it because you've got lots of wildly scattered inserts and you're getting hit by page splits so you sacrifice space to reduce that problem, or you've got very precise data with few inserts so you can afford to try to compress more data onto a page to reduce space & increase speed a bit... It depends. In general, I'd leave it on the defaults until I hit a problem.
"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
February 24, 2011 at 6:30 am
Grant Fritchey (2/24/2011)
sql_butterfly (2/23/2011)
Thanks Grant. What should be the generalized fillfactor for indexes on OLTP tables ?I agree with Mr. Holio, or can I call you Bung? It depends. In general, I accept the defaults until and unless I know I've got a reason to change them. It works well enough in most cases, but then you'll find situations where you'll want to radically reduce it because you've got lots of wildly scattered inserts and you're getting hit by page splits so you sacrifice space to reduce that problem, or you've got very precise data with few inserts so you can afford to try to compress more data onto a page to reduce space & increase speed a bit... It depends. In general, I'd leave it on the defaults until I hit a problem.
And what is your preference when you notice that an index gets fragmented from 0% to say 90% after 24hrs? Do you reorganize it on a daily basis or set the fillfactor to a lower value? Or both?
__________________________
Allzu viel ist ungesund...
February 24, 2011 at 7:00 am
Bungholio (2/24/2011)
Grant Fritchey (2/24/2011)
sql_butterfly (2/23/2011)
Thanks Grant. What should be the generalized fillfactor for indexes on OLTP tables ?I agree with Mr. Holio, or can I call you Bung? It depends. In general, I accept the defaults until and unless I know I've got a reason to change them. It works well enough in most cases, but then you'll find situations where you'll want to radically reduce it because you've got lots of wildly scattered inserts and you're getting hit by page splits so you sacrifice space to reduce that problem, or you've got very precise data with few inserts so you can afford to try to compress more data onto a page to reduce space & increase speed a bit... It depends. In general, I'd leave it on the defaults until I hit a problem.
And what is your preference when you notice that an index gets fragmented from 0% to say 90% after 24hrs? Do you reorganize it on a daily basis or set the fillfactor to a lower value? Or both?
Probably both. It becomes such a dance at that point.
"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
February 24, 2011 at 7:13 am
Probably both. It becomes such a dance at that point.
Appreciate your response.
Yours,
Mr.Holio
__________________________
Allzu viel ist ungesund...
February 25, 2011 at 8:54 am
And what is your preference when you notice that an index gets fragmented from 0% to say 90% after 24hrs? Do you reorganize it on a daily basis or set the fillfactor to a lower value? Or both?
I would say to stop using a GUID as the index value?? 😀
Seriously if you are asking the question you initially asked by FAR your best option is to hire a professional to come in and mentor you while actually DOING an indexing strategy session with your database and application. This is a HUGE topic and there are MANY ways you can go wrong.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 25, 2011 at 11:04 am
TheSQLGuru (2/25/2011)
And what is your preference when you notice that an index gets fragmented from 0% to say 90% after 24hrs? Do you reorganize it on a daily basis or set the fillfactor to a lower value? Or both?
I would say to stop using a GUID as the index value?? 😀
Seriously if you are asking the question you initially asked by FAR your best option is to hire a professional to come in and mentor you while actually DOING an indexing strategy session with your database and application. This is a HUGE topic and there are MANY ways you can go wrong.
Why would I need to call in a professional if all I need is a supporting nonclustered index with an (expected) increasing fragmentation level during the day?
Just think of the Production.WorkOrder table in AdventureWorks: What would be a valid reason not to have a nonclustered index on ProductID? Just because it might get fragmented during the day?
If we would talk about a clustered index, I'd agree. But index in general (including nonclustered), I don't.
February 25, 2011 at 11:09 am
TheSQLGuru (2/25/2011)
And what is your preference when you notice that an index gets fragmented from 0% to say 90% after 24hrs? Do you reorganize it on a daily basis or set the fillfactor to a lower value? Or both?
I would say to stop using a GUID as the index value?? 😀
Seriously if you are asking the question you initially asked by FAR your best option is to hire a professional to come in and mentor you while actually DOING an indexing strategy session with your database and application. This is a HUGE topic and there are MANY ways you can go wrong.
I believe the confusion here is the original poster and the person you quoted are not the same person.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 25, 2011 at 11:26 am
Craig Farrell (2/25/2011)
TheSQLGuru (2/25/2011)
And what is your preference when you notice that an index gets fragmented from 0% to say 90% after 24hrs? Do you reorganize it on a daily basis or set the fillfactor to a lower value? Or both?
I would say to stop using a GUID as the index value?? 😀
Seriously if you are asking the question you initially asked by FAR your best option is to hire a professional to come in and mentor you while actually DOING an indexing strategy session with your database and application. This is a HUGE topic and there are MANY ways you can go wrong.
I believe the confusion here is the original poster and the person you quoted are not the same person.
Yep, that was the issue. But my statements still apply individually!! 😛
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply