Hi,

Recently I was asked to reindex on one large database tables - there
was a performance issue and we hope rebuild the indexes will help. I
ran the DBCC DBREINDEX command for each tables in this database, I
also ran EXEC EXEC sp_updatestats to update statistics.
We use SQL Server 2005 Standard Edition.

After I reindexed the tables, I was asked to test if performance is
better than before.

I would like to know, if there is some simple query/tool available to
help me to test, or confirm that after database table index rebuild,
the performance is better or acceptable.

I understand a truly sql performance test requires carefully designed
test cases, however I believe in my case, my manager only need a rough
test result. It would be great if there is a “quick recipe” exists and
I can use.

Much appreciate for your suggestions/help, and thank you in advance.

Bob

Bob (ausgoodman (AT) hotmail (DOT) com) writes:
That was a mistake. When you rebuild indexes, statistics are updated. And
since SQL Server needs to read all rows to rebuild the index, it might as
well use that information to create the index. But when you run
sp_updatestats to update statistcs, SQL Server only samples the data,
which means that the statistics becomes less accurate.

You need a benchmark. Take some queries that are critical for your
application and run them before and after the rebuild. In both cases, the
server should be idle, so that the result is not disturbed by concurrent
activity.

Far from all queries benefit from an index rebuild. For instance a query
that select a single row in a table based on the primary key is not likely
too see any improvement, if the table was badly fragmented.

On the other hand, a query that scans a large table, can see drastic
improvments.

It can also happen that queries perform better, not because the index was
rebuilt as such, but because the statistics were updated, and the optimizer
therefore found a better plan. (And for that matter, the changed statistics
could unfortunately also lure the optimizer to select a worse plan.)

Finally, you may also see an overall performance improvement due to better
cache utilization. If you have a table with 1GB data which has a page
utilisation of 33% because of fragmentation, and this table is entirely in
cache, it takes up 3GB in the cache. If you defragment it, this means that
there is now 2GB more cache available. If this reduces the number of
physical reads, this can give significant improvement. This latter effect
should be possible to detect by use of some performance counters, for
instance Cache Hit Rate.

Normally, you should monitor fragmentation and reindex when the
fragmentation is over a certain level. Many sites have jobs to do this
automatically. Other just rebuilds everything once a week. Which is OK, as
long as you have the maintenance window.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx