Original question: Does a full backup and restore of a database rebuild table indexes and update statistics? We were seeing some slow query response times for a query running on a production server compared to the test server. We ran profiler trace, looked at execution plans and dbcc showcontig between the two databases and everything is pretty much the same except the same query in production was over 10 times slower than the one ran in the test environment with the same hardware. So in a rather desperate measure we did a full backup and then restore of the production database and now the same query is running about the same time as the test server. Hence my question about table indexes and statistics?

To answer the first part of the question, nothing is updated with a database restore; the database backup saves the current database as an as-is image and the restore restores that image. If you had out-of-date statistics before the backup, you’ll still have them afterwards.

With the information provided, it would appear that your backup/restore operations fixed a physical file (extent) fragmentation problem (see a previous post on fragmentation in SQL Server). Assuming your testing environment was created from a production database backup, you wouldn’t have had the physical fragmentation to contend with in that environment because the extents would have been restored to the file system in physical order.

You’re going to have to create a plan to deal with physical file fragmentation in your production environment. In addition, index maintenance should be planned to deal with logical fragmentation. Microsoft SQL Server 2000 Index Defragmentation Best Practices is a very worthwhile article to read on the topic. Your best bet will be to either run DBCC SHOWCONTIG, or used the information in sys.dm_db_index_physical_stats to determine the extent of logical / extent fragmentation.

One more topic of note. The SQL Server Storage Engine Blog ran a great series on fragmentation explaining SQL Server structures that’s worth a read. Gaining an understanding of these structures will go a long way in better understanding what fragmentation is and how it can affect the various structures in SQL Server.