/*
**	File:		Stats IO reads part 3.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 5 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 a forwarded record
update dbo.reads
set data = replicate( 'b', 500 )
where i = 2;
go

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

select *
from dbo.reads;
go
-- 3 reads as expected.

-- :: PART 4 - lets forward another record off the page but onto the same forwarding page
update dbo.reads
set data = replicate( 'c', 500 )
where i = 4;
go

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

select *
from dbo.reads;
go
-- 4 reads? But last time we got 5 reads. So it looks
-- like when we use the same forwarding page then we
-- get fewer reads. Lets try again

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

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

select *
from dbo.reads;
go
-- ok that is weird now we get 5 reads from the same
-- 3 pages as before. What is going on here? We don't
-- count the forwarded page each time when it's the
-- same page. So whereas before every forwarded
-- record had it's own page now it doesn't. We still
-- count the main data page whenever we read it, but
-- we don't count the forwarding page if it is the
-- same. Wow, so misleading!