/*
Microsoft SQL Server 2017 (RTM-CU9-GDR) (KB4293805) - 14.0.3035.2 (X64)   Jul  6 2018 18:24:36   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 17134: ) (Hypervisor) 
*/

use tempdb;
go

CREATE TABLE testindex (a INT, b varchar(30), dummy char(100))
GO
;WITH xx AS (SELECT * FROM 
	(VALUES (5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A')) AS x(a,b)
) 
INSERT INTO testindex(a,b,dummy) SELECT x1.*, 'a'
FROM xx AS x1,xx AS x2,xx AS x3,xx AS x4,xx AS x5,xx AS x6--,xx AS x7;
GO
INSERT INTO testindex(a,b,dummy) VALUES (5,'X','a')
GO

CREATE INDEX IX_testindex ON testindex(a,b)
GO

set statistics profile on;
go
set statistics io on; SELECT a, b FROM testindex WHERE a = 5 and b = 'X' option (recompile); set statistics io off;
go
set statistics profile off;
go

use master;
create database DBTest001;
alter database DBTest001 set auto_create_statistics off;
go

declare @sql nvarchar(max) = N'create event session DBTest001_TrackLatches on server 
add event sqlserver.latch_acquired
(
  action (sqlserver.session_id, sqlserver.sql_text)
  where ([database_id] = {dbid} and session_id = {spid})
)
add target package0.ring_buffer (set max_memory = 1000)
with (event_retention_mode=allow_single_event_loss, max_dispatch_latency = 3 seconds, track_causality=on, startup_state=off);';

if exists(select 1 from sys.server_event_sessions where name = N'DBTest001_TrackLatches')
 drop event session DBTest001_TrackLatches on server;

select
 @sql = replace(replace(@sql, N'{dbid}', cast(db_id('DBTest001') as nvarchar(10))), N'{spid}', cast(@@spid as nvarchar(10)));

exec(@sql);
go

use DBTest001;
go

CREATE TABLE testindex (a INT, b CHAR(1))
GO
;WITH xx AS (SELECT * FROM 
	(VALUES (5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A')) AS x(a,b)
) 
INSERT INTO testindex(a,b) SELECT x1.*
FROM xx AS x1,xx AS x2,xx AS x3,xx AS x4,xx AS x5,xx AS x6,xx AS x7;
GO
INSERT INTO testindex(a,b) VALUES (5,'X')
GO

create statistics ST_testindex__a on testindex (a) --with fullscan;
create statistics ST_testindex__b on testindex (b) --with fullscan;
go

create index IX_testindex on testindex (a, b);
go

create procedure spGetPagesRead
 @UpdateStatisticsMode varchar(10) = null

as
begin
 set nocount on;

 if @UpdateStatisticsMode = 'fullscan'
  begin
   update statistics testindex(ST_testindex__a) with fullscan;
   update statistics testindex(ST_testindex__b) with fullscan;
  end
 else
  begin
   update statistics testindex(ST_testindex__a);
   update statistics testindex(ST_testindex__b);
  end;

 alter event session DBTest001_TrackLatches on server state = start;
 set statistics io on; SELECT a, b FROM testindex WHERE a = 5 and b = 'X'  option (recompile); set statistics io off;

 declare @x xml;

 select
  @x = cast(t.target_data as xml)
 from
  sys.dm_xe_sessions s join
  sys.dm_xe_session_targets t on t.event_session_address = s.address
 where
  s.name = N'DBTest001_TrackLatches';

 alter event session DBTest001_TrackLatches on server state = stop;

 select distinct
  object_name(pa.object_id) as object, pi.file_id, pi.page_id, pa.index_id, pa.page_type_desc
 from
  @x.nodes('/RingBufferTarget/event[@name = "latch_acquired"][data[@name = "mode"][text = "SH"]]') t(n) cross apply
  (select t.n.value('(data[@name = "file_id"]/value)[1]', 'smallint'), t.n.value('(data[@name = "page_id"]/value)[1]', 'int')) pi(file_id, page_id) join
  sys.dm_db_database_page_allocations(db_id(), null, null, null, 'detailed') pa on pa.allocated_page_file_id = pi.file_id and pa.allocated_page_page_id = pi.page_id

end;
go

exec dbo.spGetPagesRead 'fullscan';
exec dbo.spGetPagesRead;
go

drop event session DBTest001_TrackLatches on server;
go

use master;
drop database DBTest001;
go

use master;
create database DBTest001;
alter database DBTest001 set auto_create_statistics off;
go

declare @sql nvarchar(max) = N'create event session DBTest001_TrackLatches on server 
add event sqlserver.latch_acquired
(
  action (sqlserver.session_id, sqlserver.sql_text)
  where ([database_id] = {dbid} and session_id = {spid})
)
add target package0.ring_buffer (set max_memory = 1000)
with (event_retention_mode=allow_single_event_loss, max_dispatch_latency = 3 seconds, track_causality=on, startup_state=off);';

if exists(select 1 from sys.server_event_sessions where name = N'DBTest001_TrackLatches')
 drop event session DBTest001_TrackLatches on server;

select
 @sql = replace(replace(@sql, N'{dbid}', cast(db_id('DBTest001') as nvarchar(10))), N'{spid}', cast(@@spid as nvarchar(10)));

exec(@sql);
go

use DBTest001;
go

CREATE TABLE testindex (a INT, b CHAR(1))
GO
;WITH xx AS (SELECT * FROM 
	(VALUES (5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A')) AS x(a,b)
) 
INSERT INTO testindex(a,b) SELECT x1.*
FROM xx AS x1,xx AS x2,xx AS x3,xx AS x4,xx AS x5,xx AS x6,xx AS x7;
GO
INSERT INTO testindex(a,b) VALUES (5,'X')
GO

create statistics ST_testindex__a on testindex (a) --with fullscan;
create statistics ST_testindex__b on testindex (b) --with fullscan;
go

create index IX_testindex on testindex (a, b);
go

create procedure spGetPagesRead
 @UpdateStatisticsMode varchar(10) = null

as
begin
 set nocount on;

 if @UpdateStatisticsMode = 'fullscan'
  begin
   update statistics testindex(ST_testindex__a) with fullscan;
   update statistics testindex(ST_testindex__b) with fullscan;
  end
 else
  begin
   update statistics testindex(ST_testindex__a);
   update statistics testindex(ST_testindex__b);
  end;

 alter event session DBTest001_TrackLatches on server state = start;
 set statistics io on; SELECT a, b FROM testindex WHERE a = 5 and b = 'X'  option (recompile); set statistics io off;

 declare @x xml;

 select
  @x = cast(t.target_data as xml)
 from
  sys.dm_xe_sessions s join
  sys.dm_xe_session_targets t on t.event_session_address = s.address
 where
  s.name = N'DBTest001_TrackLatches';

 alter event session DBTest001_TrackLatches on server state = stop;

 select
  pi.file_id, pi.page_id, pa.index_id, pa.page_type_desc
 from
  @x.nodes('/RingBufferTarget/event[@name = "latch_acquired"][data[@name = "mode"][text = "SH"]]') t(n) cross apply
  (select t.n.value('(data[@name = "file_id"]/value)[1]', 'smallint'), t.n.value('(data[@name = "page_id"]/value)[1]', 'int')) pi(file_id, page_id) join
  sys.dm_db_database_page_allocations(db_id(), null, null, null, 'detailed') pa on pa.allocated_page_file_id = pi.file_id and pa.allocated_page_page_id = pi.page_id
 where
  pa.object_id = object_id('testindex');

end;
go

exec dbo.spGetPagesRead 'fullscan';
exec dbo.spGetPagesRead;
go

drop event session DBTest001_TrackLatches on server;
go

use master;
drop database DBTest001;
go