Database state set to an earlier state before creating new indexes

Question

This is a remote server (SQL Server 2008).

We are trying to get benchmarking of some stored procedures. One stored procedure was taking avrg 44 seconds, we have checked this approx 20 times for a span of two weeks avrg was 44 seconds.

SQL Profiler suggested a missing index, we created the suggested index for checking the performance. The execution time reduced significantly after indexing.

We dropped the newly created index. Rebuild index was not performed, as we assumed drop index should rebuild the index for the table.

Client is also testing on same db. They also felt a performance gain in this particular operation. They are asking the cause for this gain, we have conveyed the above process, but index benefit can also be verified with older DB state, therefor we need to reset the data base to earlier state where it was taking 44 seconds.

Database Engine can not be restarted and Offline/Online options can also not be performed as "Alter Any Database" permission can not be granted.

We are looking on below command.

CHECKPOINT; 
GO 
DBCC DROPCLEANBUFFERS; 
GO

Permissions
Requires ALTER SERVER STATE permission on the server.

USE <<db Name>>
GO
GRANT ALTER TO your_user
GO

Need help to understand the possible cause behind this behaviour?

Does DROPCLEANBUFFERS also clear the index cache?

Answer 1

When you drop the index, the database is in the same state as before in terms of indexing. If you want to go back to a previous state then you'll need to get a backup for that time range. Depending on the requirements it's either just the FULL Backup or additional DIFFERENTIAL Backups or even a point-in-time recovery using the transaction logs.

But the question is: Why do you want to do it in the first place?

The benefit of any index should not be explained by runnig it against an older data set just to see that it would have been better in the past, too, but by explaining the effect itself. Either a Scan has changed into a Seek operation and/or the new index is more narrow than the one previously used and/or a bookmark lookup has been eliminated and/or the index allows for a better performing query plan.

Also, verify that the statistics are up to date before comparing old and new version to eliminate the side effect of a better performance due to better statistics.

The index comparison should be done using the same procedure against the same set of data (e.g. current data) and compare the actual execution plans.