Rebuilding indexes on solid-state disks; is it worth it?

Question

Rebuilding indexes on solid-state disks; is it worth it?

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

THE-FHA (9/13/2011)
My question is how often should one reorganize indexes? daily, weekly or monthly or just on demand?

It largely depends on your environment. Baseline your system, check the info on a regular basis and build a plan to reorg or rebuild based on your findings.

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

Paul White SSC Guru Points: 150468 More actions · Answer 2

We use Fusion-io storage on extremely large sets of financial data. We are aware of the technical reasons to rebuild indexes (larger IOs, space usage, and so on) but in practice we never rebuild or reorganize indexes - it just didn't turn out to be worth it - not even close.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

SQL Kiwi (9/13/2011)
We use Fusion-io storage on extremely large sets of financial data. We are aware of the technical reasons to rebuild indexes (larger IOs, space usage, and so on) but in practice we never rebuild or reorganize indexes - it just didn't turn out to be worth it - not even close.

I think you hit on an important thing with the Fusion-IO. Fusion-IO are so much faster than most SSDs that it may be futile to rebuild/reorg indexes. Get a rack of those cards and that would be a real sweet SAN.

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

jwize1 SSC Journeyman Points: 87 More actions · Answer 4

We were facing massive slowdowns for some unknown reasons. Some of our queries were taking 45+ seconds complete. After opening ANTS Performance profiler we were able to see which query was causing the problem. I looked at the fragmentation of the unclustered indexs' properties that were being used. They were about 98% fragmented. We did a rebuild that took less than a second. There is zero noticeable lag where the slowdowns were.