Home > SQL Server > SQL SERVER – SET STATISTICS IO – DROPCLEANBUFFERS- SET Statement

SQL SERVER – SET STATISTICS IO – DROPCLEANBUFFERS- SET Statement


SET STATISTICS IO : This set statement gives information about the disk IO activities of the T-SQL statements executed in the current session.

It displays disk information when it is set to ON and it hides the disk information when it is set to OFF.

Usage

SET STATISTICS IO ON

or

SET STATISTICS IO OFF

Disk activity information

pic1Sample output when STATISTICS IO is ON

pic2

DBCC DROPCLEANBUFFERS

It cleans all buffers from the buffer pool and it helps user to see the actual query performance without using cache data. It should be the first step before we start query performance tuning.

Note: We should not execute this DBCC command in the production server.Because, it will remove all the cached data from the buffer and make the query execution slower for the all new queries.

Example

When STATISTICS IO is ON and running a SQL query in the query windows

DBCC DROPCLEANBUFFERS
GO

set statistics io on
go

select * from [Person].[PersonPhone]

set statistics io off
go

pic4Statistics io data is very important while doing query performance tuning. I hope you have learned about STATISTICS IO set statement from this blog post. Please let me know if you have any questions.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: