This is a remote server (MSSQL 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 benifit 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? Is DROPCLEANBUFFERS clear index cache also?

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?