| Author |
Topic |
|
rkeith27
Starting Member
5 Posts |
 Posted - 2014-02-18 : 13:19:34
|
| I have a table with a couple thousand records that are constantly inserted, updated, and deleted. I have a non-clustered index on a column in this table and it has a total of 9 columns.With a table this size...1) Should I be dropping/creating indexes on new record inserts?2) Does it matter on a table this size? If so, at what point should I be dropping/creating indexes? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-02-18 : 13:58:32
|
| 1. Absolutely not.2. No. Never. I don't understand why you think you'd need to drop/create indexes based upon DML statements.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
rkeith27
Starting Member
5 Posts |
Posted - 2014-02-18 : 15:09:00
|
| Reading articles about indexing and them discussing dropping the index, inserting the record, and then re-creating the index. It (the article) talked about the performance issue of not doing this but it never addressed the size of the records or the number of records in the table. Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-02-18 : 15:10:58
|
| Please post the article. It was likely regarding a massive import/data warehouse, which does not sound like is your case.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-02-18 : 17:35:52
|
| Instead of a nonclustered index with 9 columns, you need the correct clustered index with only the specific number of columns you need in the table's key.If you need all 9 columns in the lookup key (extraordinarily unlikely), then cluster the table by those 9 columns. |
|
|
|
rkeith27
Starting Member
5 Posts |
Posted - 2014-02-19 : 07:34:51
|
| I couldn't find the article right away but I did find a blog that said it was for data warehouse operations. Thanks.Only one column has a non-clustered index, not 9 columns. The table just has 9 columns total. One clustered and one non-clustered index. |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-02-19 : 10:25:52
|
| 2) Yes. When any index gets too badly fragmented, rebuild it. For nonclus indexes, you can wait for 35% fragmentation before rebuilding. For a clus index, 15%-20% is more typical. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-02-19 : 13:27:17
|
| You may not even need to rebuild them based upon fragmentation. Try disabling the rebuild job for a few weeks and compare performance before/after. We disabled our rebuild job on our most critical system a couple of years ago at the recommendation of Microsoft. The only downside that we've seen is storage. The pain is too much while the rebuild job is running, and the performance impact of fragmentation is very minimal.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-02-19 : 13:44:21
|
quote:
Originally posted by tkizer
You may not even need to rebuild them based upon fragmentation. Try disabling the rebuild job for a few weeks and compare performance before/after. We disabled our rebuild job on our most critical system a couple of years ago at the recommendation of Microsoft. The only downside that we've seen is storage. The pain is too much while the rebuild job is running, and the performance impact of fragmentation is very minimal.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
That depends on the type of fragmentation and other table details.An online rebuild shouldn't cause any issue at all.In fact, I can't imagine that an offline rebuild on a table that small would either. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-02-19 : 14:22:43
|
quote:
Originally posted by ScottPletcher
That depends on the type of fragmentation and other table details.
Agreed, that's why you have to see what happens in your environment.quote:
Originally posted by ScottPletcher
An online rebuild shouldn't cause any issue at all.
With rkeith27's specific table, yes that would likely be the case. But on high volume systems with large tables, an online rebuild can pose many challenges. My reply was in regards to this: "When any index gets too badly fragmented, rebuild it."quote:
Originally posted by ScottPletcher
In fact, I can't imagine that an offline rebuild on a table that small would either.
Right for this specific table an offline rebuild would be fast, though there'll likely still be issues (blocking) while it's running. A table this small might not even have enough pages to get rid of fragmentation anyway. Often rebuild jobs ignore tables of this size.Just to make it clear, my "No. Never." answer was in regards to drop/create question and not to a rebuild question.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
At what point do I need to drop/create indexes?