We have a customer database that is heavily fragmented - practically every table with more than 1000 pages has >95% fragmentation. Fill factors are set to sensible values, but page space usage is nowhere near to fill factor for most tables.

This is the result of no maintenance being performed on the database.

Rebuilding the indexes using Ola Hallengren's IndexOptimize reduces fragmentation as expected. On the existing production hardware, performance of the application improves as expected. All the metrics I normally use - client statistics on heavy queries, profiler durations, read/write stalls, application logs and user perception - indicate performance is improved.

However, a new database server backed with Intel PCIe SSDs is showing the opposite of what we expect. Highly fragmented, the application performs well. After rebuilding indexes, the application performs badly. Some operations that took ~90s now take ~6mins. However, none of the other metrics appear to indicate that the system is going slower.

Is this something anyone else has experienced?

  • 3
    Maybe try looking at your query plans. It's possible you're experiencing paramater sniffing. – Reaces Dec 30 '14 at 10:25
  • 2
    Agree with @Reaces. Rebuilding indexes will have updated stats and invalidated existing query plans. Maybe you were unlucky with the parameter values passed when the new plan was compiled. – Martin Smith Dec 30 '14 at 10:30
  • Ok - that's something I wasn't aware of. I guess I can test this using "DBCC FREEPROCCACHE" on the fragmented system and see how badly performance degrades given exactly the same operation. – Cybergibbons Dec 30 '14 at 10:36
  • 2
    And before doing that have a look at STATISTICS IO read ahead reads/physical reads to see if the 6 minutes query is even reading from disc at all. – Martin Smith Dec 30 '14 at 10:40
  • The balance of reads to writes is extremely high and the system has more than enough RAM to hold the entire DB in memory, so I would imagine that reads from disc should be low - which makes the behaviour odder. – Cybergibbons Dec 30 '14 at 11:57

Yes, rebuilding indexes (especially on SSD) can cause worse performance. Most high speed SSD prefer many, smaller block requests instead of fewer, larger requests. This is exactly the opposite pattern preferred by traditional, spinning rust.

Assume you have a highly fragmented B-tree. Because nothing is ordered on the disk, you will typically issue a lot of 8KB I/O requests to scan the tree. If you were to defragment the tree, then you can get up to 512KB in a single request. Those large requests will have higher latency on the SSD, because the SSD internally breaks it down to 8KB chunks (unlike a hard drive, which will issue a sequential I/O). For a great many cases: Higher disk latency = slower queries

All that being said, please do make sure you check that you are actually getting the same query plans that you were getting before the rebuild.

An finally: Unless you are low on space, why are you wasting your precious DBA time with index rebuilds when you run on SSD?

  • The rebuilds are happening for a few reasons - a consistent maintenance plan across all databases without worrying about underlying hardware, the perception that as long as the rebuild could happen it would have no penalty regardless of hardware, and to maintain sensible page space usage (I think this will cause problems regardless of the storage). – Cybergibbons Dec 30 '14 at 10:41
  • 1
    You might want to consider different rebuild strategies for databases residing on SSD. You might simply be burning CPU and SSD write cycles for no gain (or even a regression). – Thomas Kejser Dec 30 '14 at 10:50
  • How do you explain that the performance dropped from 90 seconds to 360 seconds by just rebuilding an index? The performance should not drop because the SSD does exactly the same as before. @Cybergibbons, could you verify if the execution plans changed? That sounds more like a possible problem. – o0x258 Dec 30 '14 at 10:59
  • As ora-600 says, your first port of call really is to verify that the plans have not changed. It is by far the most likely explanation.... If you have established that you are getting the same plans in the before/after scenario - then you can look into the special cases of slower SSD. Also, would be useful if you add the query to your question. – Thomas Kejser Dec 30 '14 at 11:04
  • 1
    @ora-600: The SSD does the same read, but the order those reads return in matters. Take the example of a simple loop join between two tables with a range scan and a low cache hit ratio. The outer table has to wait for the inner I/O to complete to make progress. If the inner scan returns often, in small chunks at a time, then the outer part of the loop makes progress faster, driving up parallelism. The total amount of I/O operations go up (though the bytes read are the same), but so does the speed of the plan – Thomas Kejser Dec 30 '14 at 12:02

Highly fragmented, the application performs well. After rebuilding indexes, the application performs badly.

A probable cause is that the changed (presumably reduced) size of the structures after rebuilding means the optimizer is choosing a different plan. One of the primary inputs to the optimizer's costing model is the number of pages each plan operator is expected to process.

Changing the number of pages in a structure can easily make the difference between the optimizer choosing a hash or merge join, versus a nested loops (with or without a spool) strategy. That's just one example; costing differences can affect all aspects of plan choice, including the decision to use parallelism or not.

To make the sort of performance differences you are observing (and also considering the lack of physical I/O), this seems like the most likely explanation (assuming you can eliminate 'bad' parameter sniffing).

All that said, without details (ideally plans and detailed metrics for a single instance of the problem before and after rebuilding) the current question is arguably very opinion-based, which would make it off-topic for this site.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.