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
Either way you can restart SQL Server services also 
It is one of the effective command by which we can evaluate of stored procedure performance.
Enjoy !!
Thanks
Rajat Jaiswal
No comments:
Post a Comment