sql - Why didn't rebuilding my index substantially improve query performance?

Question

At my work I'm spearheading a project to improve the performance of our databases. I've decided I want to start by establishing a baseline of query performance and seeing how much it improves after rebuilding the index of a table on our testing environment. A lot of our tables are heavily fragmented and have a high page count. So I ran a trace of a few statements, including three SELECTS, one INSERT, one UPDATE, and one DELETE. I then rebuilt the indices and traced the statements again.

I did not see a noticeable improvement in performance after rebuilding the indices. In fact, the only statement that had a substantial reduction in time was SELECTing a range of values (from 3080 ms down to 2112 ms). SELECTing a single row, the INSERT, the UPDATE, and the DELETE all had worse performance after rebuilding the index (from 73 ms to 107 ms) after the rebuild. The number of pages only seems to have decreased by a few hundred, so the size of each table was reduced by only a 1-3 MB.

Should my performance increase be larger? Am I underestimating the value of this small increase? It's only one statement in one table in a whole DB out of hundreds of databases, so maybe the small increase in performance quickly adds up. But

question from:https://stackoverflow.com/questions/65905049/why-didnt-rebuilding-my-index-substantially-improve-query-performance

Answer 1

Waitting for answers