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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s