June 9, 2011 at 10:58 am
What would you recommend?
I have SQL Server 2008.
Every night at 2am, I run a stored procedures on numerous tables, each with anywhere from 4million to 7 million rows, and many columns. Each procedure:
a. disables the table indexes
b. truncates the table
c. bulk insert new data
d. rebuild table indexes
The indexes I have are non-clustered, and do not take long to rebuild.
Question I have, should I use clustered indexes (where I can), or would that create too much work for the server during the rebuild phase each night?
What would you recommend for optimal performance?
June 9, 2011 at 11:08 am
For that many records and if you are having performance issues, then yes add a Clustered Index where appropriate.
If performance is not an issue and the tables are bulk load nightly - then I would probably leave it as is.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 9, 2011 at 11:26 am
I would add that it also depends on how the data is being queried and how the NC indexes are defined.
Not knowing anything more than what you posted, it really comes down to "It depends."
June 9, 2011 at 11:27 am
Obviously the bulk insert would be faster without the clustered index unless the order of the insert follows the order of the clustered index. It all depends on the table usage and how you want to balance the bulk insert performance versus table query/modification performance.
-------------------------------------------------
Will C,
MCITP 2008 Database Admin, Developer
June 9, 2011 at 6:36 pm
My opinion is if the table is going to be queried then build a clustered index after loading. If it is just a landing zone for data on its way to a performance tuned and indexed schema then it depends, if the process that processes the data into its final location needs some help then build the indexes.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply