/*
**	File:		Stats IO reads part 1.sql
**
**	Summary:	Run in a job to run indirect checkpoints prior to SQL 2012
**				set the percentage limit before running.
**
**	SQL Server Versions:	2005 onwards
**	----------------------------------------------------------------------------
**	Written by Rainer Unwin (c) 2015
**
**	For more scripts check out http://www.rmuniwn.com
**
**	You may alter this code for your own non-commercial purposes (e.g. in a
**	for-sale commercial tool).
**	You may republish altered code as long as you include this copyright and
**	give due credit, however you must obtain my prior permission before blogging
**	this code.
**
**	this code and information are provided "as is" without warranty of
**	any kind, either expressed or implied, including but not limited
**	to the implied warranties of merchantability and/or fitness for a
**	particular purpose. You use the script at your own risk and should
**	always test in a pre-production or test environment before running
**	in production.
*/
-- :: PART 1 - setup
set nocount on;
go

use tempdb;
go

-- create an object to play with
if object_id( N'dbo.reads', 'U' ) is not null drop table dbo.reads;
go

create table dbo.reads( i int, data char( 5000 ) );
go

-- :: PART 2 - check with a single page

-- insert a single record and check the structure of the table
-- then confirm that we get a single read (The data page only)
insert dbo.reads( i, data ) values( 1, 'a' );
go

select *
from dbo.reads_pages;
go

set statistics io on;
go

select *
from dbo.reads;
go
-- just 1 read (The data page). The IAM page is not read.
-- lets try again with a second page of data.

-- :: PART 3 - Now try with 2 data pages

insert dbo.reads( i, data ) values( 2, 'b' );
go

select *
from dbo.reads_pages;
go

select *
from dbo.reads;
go
-- again we see the number of reads of data pages only.
-- we need to prove this is the case when we exceed the
-- extent boundary as well so lets add another 10 pages

-- :: PART 4 - lets use 12 pages which exceeds the extent boundary and conclusion

insert into dbo.reads( i, data )
	values( 3, 'c' )
		, ( 4, 'd' )
		, ( 5, 'e' )
		, ( 6, 'f' )
		, ( 7, 'g' )
		, ( 8, 'h' )
		, ( 9, 'i' )
		, ( 10, 'j' )
		, ( 11, 'k' )
		, ( 12, 'l' );
go

select *
from dbo.reads_pages;
go

select *
from dbo.reads;
go
-- If you have multiple files, as I do, you may also
-- find that you now have multiple IAM pages. In my
-- case I had 12 data pages and 2 IAM page. reads
-- from the select were 12 matching the number of
-- data pages