/*
**	File:		Stats IO reads part 2.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 varchar( 8000 ) );
go

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

-- insert 7 records 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, replicate( 'a', 7677 ) ) -- fill the page
		, ( 2, replicate( 'b', 50 ) )
		, ( 3, replicate( 'c', 50 ) )
		, ( 4, replicate( 'd', 50 ) )
		, ( 5, replicate( 'e', 50 ) )
		, ( 6, replicate( 'f', 50 ) )
		, ( 7, replicate( 'g', 50 ) );
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.

-- :: PART 3 - Now generate 1 forwarded record
update dbo.reads
set data = replicate( 'b', 5000 )
where i = 2
go

-- 3 pages: 1 IAM, 1 data & 1 for the forwarded record
select *
from dbo.reads_pages;
go

select *
from dbo.reads;
go
-- now we see 3 page reads. This could be because we
-- have read the IAM page to see where we should read
-- the forwarded page or it could be that we jumped
-- to the forwarded page and then back again

-- :: PART 4 - lets forward another record off the main data page
update dbo.reads
set data = replicate( 'd', 5000 )
where i = 4;
go

-- 4 pages: 1 IAM , 1 data, 2 for forwarded records
select *
from dbo.reads_pages;
go

select *
from dbo.reads;
go
-- 5 reads but how did we get there? if we read the IAM
-- page then we would read just 4 pages. The IAM, the
-- data page and the 2 forwarded pages. No we must have
-- read the data page, followed the pointer, then back
-- to the data page and repeat for the other forward
-- pointer. Lets confirm with 1 more.

-- :: PART 5 - lets forward another record off the main data page
update dbo.reads
set data = replicate( 'f', 5000 )
where i = 3;
go

-- 5 pages: 1 IAM , 1 data, 3 for forwarded records
select *
from dbo.reads_pages;
go

select *
from dbo.reads;
go
-- yes the pattern holds. Each new forwarded record adds
-- two reads. One to go the the forwarded page and one
-- to go back to the data page. In the next section you
-- will see another interesting effect