Performance tips- How to clean buffer to test performance TIP#28

 

Dear Friends,

To test the performance of a stored procedure we run the stored procedure several time.

You have observed when you run the stored procedure first time it took time but when you run next time it is faster. It just because the stored procedure /data is cached.

Now , due to this cache problem we got a wrong assumption that the stored procedure is fast but in actual it it taking cache data.

To assure that when we run the stored procedure it will run on new set of data not from buffer data SQL Server provided a option which is called “DROPCLEANBUFFER”

You can run this command as shown in below fig

DROP_CLAN_BUFFER

Either way you can restart SQL Server services also Smile .

It is one of the effective command by which we can evaluate of stored procedure performance.

Enjoy !!

Thanks

Rajat Jaiswal

Leave a Reply

Your email address will not be published. Required fields are marked *

*

code