Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 At what point do I need to drop/create indexes?

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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.
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://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.
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -