SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 At what point do I need to drop/create indexes?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rkeith27
Starting Member

5 Posts

Posted - 02/18/2014 :  13:19:34  Show Profile  Visit rkeith27's Homepage  Reply with Quote
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

USA
36996 Posts

Posted - 02/18/2014 :  13:58:32  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 02/18/2014 :  15:09:00  Show Profile  Visit rkeith27's Homepage  Reply with Quote
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

USA
36996 Posts

Posted - 02/18/2014 :  15:10:58  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Constraint Violating Yak Guru

USA
383 Posts

Posted - 02/18/2014 :  17:35:52  Show Profile  Reply with Quote
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 - 02/19/2014 :  07:34:51  Show Profile  Visit rkeith27's Homepage  Reply with Quote
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
Constraint Violating Yak Guru

USA
383 Posts

Posted - 02/19/2014 :  10:25:52  Show Profile  Reply with Quote
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

USA
36996 Posts

Posted - 02/19/2014 :  13:27:17  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Constraint Violating Yak Guru

USA
383 Posts

Posted - 02/19/2014 :  13:44:21  Show Profile  Reply with Quote
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

USA
36996 Posts

Posted - 02/19/2014 :  14:22:43  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000