Xjyuk

How to avoid making self and former employee look bad when reporting on fixing former employee's work?

Is there a need for better software for writers?

Why use steam instead of just hot air?

Do Monks gain the 9th level Unarmored Movement benefit when wearing armor or using a shield?

Rusty Chain and back cassette – Replace or Repair?

Are on’yomi words loanwords?

How do carbureted and fuel injected engines compare in high altitude?

Why valarray so slow on VS2015?

Why is it wrong to *implement* myself a known, published, widely believed to be secure crypto algorithm?

Are double contractions formal? Eg: "couldn't've" for "could not have"

Is there an idiom that means "revealing a secret unintentionally"?

Names of the Six Tastes

if i accidentally leaked my schools ip address and someone d doses my school am i at fault

What's the "magic similar to the Knock spell" referenced in the Dungeon of the Mad Mage adventure?

resoldering copper waste pipe

How did Captain Marvel know where to find these characters?

What is the status of the three crises in the history of mathematics?

How do I minimise waste on a flight?

Passport stamps art, can it be done?

What's an appropriate age to involve kids in life changing decisions?

Why did they wait for Quill to arrive?

How can it be that ssh somename works, while nslookup somename does not?

How can Sam Wilson fulfill his future role?

Examples where existence is harder than evaluation

Does STATISTICS IO output include Version Store reads?

Every query plan statistic says my query should be faster, but it is notQuery plan reads vs statistics io readsCan I Improve Performance on Bloated System Tables?Clustered Index Scan when using greater than in WHERE clauseOPTION (RECOMPILE) is Always Faster; Why?Helpful nonclustered index improved the query but raised logical readsA Query in the Query Store shows high Logical ReadsQuestions about the output of “statistics IO”IO Statistics for Index Seeklogical reads on global temp table, but not on session-level temp table

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

4

SQL Server has an option SET STATISTICS IO ON that shows the number of logical and physical page reads for a query. Do these stats include reads of the version store for SNAPSHOT and RCSI queries?

sql-server

share|improve this question

asked 1 hour ago

ForrestForrest

2,7151822

add a comment | 

4

SQL Server has an option SET STATISTICS IO ON that shows the number of logical and physical page reads for a query. Do these stats include reads of the version store for SNAPSHOT and RCSI queries?

sql-server

share|improve this question

asked 1 hour ago

ForrestForrest

2,7151822

add a comment | 

SQL Server has an option SET STATISTICS IO ON that shows the number of logical and physical page reads for a query. Do these stats include reads of the version store for SNAPSHOT and RCSI queries?

sql-server

share|improve this question

asked 1 hour ago

ForrestForrest

2,7151822

share|improve this question

asked 1 hour ago

ForrestForrest

2,7151822

share|improve this question

asked 1 hour ago

ForrestForrest

2,7151822

asked 1 hour ago

ForrestForrest

2,7151822

asked 1 hour ago

ForrestForrest

2,7151822

1 Answer
1

active

oldest

votes

4

STATISTICS IO does not include Version Store reads, at least for the version store in tempdb.

Here's a demo for proof:

--setup script
USE master
GO

CREATE DATABASE TestDB
GO

ALTER DATABASE TestDB
SET ALLOW_SNAPSHOT_ISOLATION ON
GO

USE TestDB
GO

DROP TABLE IF EXISTS dbo.Test
GO

CREATE TABLE dbo.Test (ID int identity PRIMARY KEY, junk int)

INSERT dbo.Test
SELECT TOP (100000) 1
FROM master.dbo.spt_values a
CROSS JOIN master.dbo.spt_values b

Start a 30s update loop in one SSMS tab

--UPDATE loop
SET NOCOUNT ON
DECLARE @stop datetime = DATEADD(SECOND, 30, GETDATE())

WHILE GETDATE() < @stop
BEGIN
 BEGIN TRAN

 UPDATE dbo.Test
 SET junk += 1

 COMMIT
END

UPDATE dbo.Test
SET junk = 1

And while the loop is going, run two identical queries in SNAPSHOT with STATISTICS IO ON, separated by 15s to allow versions to accumulate.

USE TestDB
SET STATISTICS IO ON
GO

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

SELECT MAX(junk)
FROM dbo.Test

WAITFOR DELAY '00:00:15'

SELECT MAX(junk)
FROM dbo.Test

COMMIT

The IO stats show identical reads:

But the actual execution plan shows the scan for the second query taking far more time, due to reading the version store.

share|improve this answer

answered 1 hour ago

ForrestForrest

2,7151822

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  +1; an interesting takeaway is the "cost relative to batch" is the same at 50% each. Just one more reason to not put too much trust in that number,
  
  – Max Vernon
  30 mins ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Consider adding a way to view version store reads for that query execution (if it is reported anywhere).
  
  – Joe Obbish
  3 mins ago
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f237704%2fdoes-statistics-io-output-include-version-store-reads%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

4

STATISTICS IO does not include Version Store reads, at least for the version store in tempdb.

Here's a demo for proof:

--setup script
USE master
GO

CREATE DATABASE TestDB
GO

ALTER DATABASE TestDB
SET ALLOW_SNAPSHOT_ISOLATION ON
GO

USE TestDB
GO

DROP TABLE IF EXISTS dbo.Test
GO

CREATE TABLE dbo.Test (ID int identity PRIMARY KEY, junk int)

INSERT dbo.Test
SELECT TOP (100000) 1
FROM master.dbo.spt_values a
CROSS JOIN master.dbo.spt_values b

Start a 30s update loop in one SSMS tab

--UPDATE loop
SET NOCOUNT ON
DECLARE @stop datetime = DATEADD(SECOND, 30, GETDATE())

WHILE GETDATE() < @stop
BEGIN
 BEGIN TRAN

 UPDATE dbo.Test
 SET junk += 1

 COMMIT
END

UPDATE dbo.Test
SET junk = 1

And while the loop is going, run two identical queries in SNAPSHOT with STATISTICS IO ON, separated by 15s to allow versions to accumulate.

USE TestDB
SET STATISTICS IO ON
GO

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

SELECT MAX(junk)
FROM dbo.Test

WAITFOR DELAY '00:00:15'

SELECT MAX(junk)
FROM dbo.Test

COMMIT

The IO stats show identical reads:

But the actual execution plan shows the scan for the second query taking far more time, due to reading the version store.

share|improve this answer

answered 1 hour ago

ForrestForrest

2,7151822

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  +1; an interesting takeaway is the "cost relative to batch" is the same at 50% each. Just one more reason to not put too much trust in that number,
  
  – Max Vernon
  30 mins ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Consider adding a way to view version store reads for that query execution (if it is reported anywhere).
  
  – Joe Obbish
  3 mins ago
  

  
  
  
  

add a comment | 

4

STATISTICS IO does not include Version Store reads, at least for the version store in tempdb.

Here's a demo for proof:

--setup script
USE master
GO

CREATE DATABASE TestDB
GO

ALTER DATABASE TestDB
SET ALLOW_SNAPSHOT_ISOLATION ON
GO

USE TestDB
GO

DROP TABLE IF EXISTS dbo.Test
GO

CREATE TABLE dbo.Test (ID int identity PRIMARY KEY, junk int)

INSERT dbo.Test
SELECT TOP (100000) 1
FROM master.dbo.spt_values a
CROSS JOIN master.dbo.spt_values b

Start a 30s update loop in one SSMS tab

--UPDATE loop
SET NOCOUNT ON
DECLARE @stop datetime = DATEADD(SECOND, 30, GETDATE())

WHILE GETDATE() < @stop
BEGIN
 BEGIN TRAN

 UPDATE dbo.Test
 SET junk += 1

 COMMIT
END

UPDATE dbo.Test
SET junk = 1

And while the loop is going, run two identical queries in SNAPSHOT with STATISTICS IO ON, separated by 15s to allow versions to accumulate.

USE TestDB
SET STATISTICS IO ON
GO

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

SELECT MAX(junk)
FROM dbo.Test

WAITFOR DELAY '00:00:15'

SELECT MAX(junk)
FROM dbo.Test

COMMIT

The IO stats show identical reads:

But the actual execution plan shows the scan for the second query taking far more time, due to reading the version store.

share|improve this answer

answered 1 hour ago

ForrestForrest

2,7151822

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  +1; an interesting takeaway is the "cost relative to batch" is the same at 50% each. Just one more reason to not put too much trust in that number,
  
  – Max Vernon
  30 mins ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Consider adding a way to view version store reads for that query execution (if it is reported anywhere).
  
  – Joe Obbish
  3 mins ago
  

  
  
  
  

add a comment | 

STATISTICS IO does not include Version Store reads, at least for the version store in tempdb.

Here's a demo for proof:

--setup script
USE master
GO

CREATE DATABASE TestDB
GO

ALTER DATABASE TestDB
SET ALLOW_SNAPSHOT_ISOLATION ON
GO

USE TestDB
GO

DROP TABLE IF EXISTS dbo.Test
GO

CREATE TABLE dbo.Test (ID int identity PRIMARY KEY, junk int)

INSERT dbo.Test
SELECT TOP (100000) 1
FROM master.dbo.spt_values a
CROSS JOIN master.dbo.spt_values b

Start a 30s update loop in one SSMS tab

--UPDATE loop
SET NOCOUNT ON
DECLARE @stop datetime = DATEADD(SECOND, 30, GETDATE())

WHILE GETDATE() < @stop
BEGIN
 BEGIN TRAN

 UPDATE dbo.Test
 SET junk += 1

 COMMIT
END

UPDATE dbo.Test
SET junk = 1

And while the loop is going, run two identical queries in SNAPSHOT with STATISTICS IO ON, separated by 15s to allow versions to accumulate.

USE TestDB
SET STATISTICS IO ON
GO

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

SELECT MAX(junk)
FROM dbo.Test

WAITFOR DELAY '00:00:15'

SELECT MAX(junk)
FROM dbo.Test

COMMIT

The IO stats show identical reads:

But the actual execution plan shows the scan for the second query taking far more time, due to reading the version store.

share|improve this answer

answered 1 hour ago

ForrestForrest

2,7151822

--setup script
USE master
GO

CREATE DATABASE TestDB
GO

ALTER DATABASE TestDB
SET ALLOW_SNAPSHOT_ISOLATION ON
GO

USE TestDB
GO

DROP TABLE IF EXISTS dbo.Test
GO

CREATE TABLE dbo.Test (ID int identity PRIMARY KEY, junk int)

INSERT dbo.Test
SELECT TOP (100000) 1
FROM master.dbo.spt_values a
CROSS JOIN master.dbo.spt_values b

--UPDATE loop
SET NOCOUNT ON
DECLARE @stop datetime = DATEADD(SECOND, 30, GETDATE())

WHILE GETDATE() < @stop
BEGIN
 BEGIN TRAN

 UPDATE dbo.Test
 SET junk += 1

 COMMIT
END

UPDATE dbo.Test
SET junk = 1

USE TestDB
SET STATISTICS IO ON
GO

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

SELECT MAX(junk)
FROM dbo.Test

WAITFOR DELAY '00:00:15'

SELECT MAX(junk)
FROM dbo.Test

COMMIT

share|improve this answer

answered 1 hour ago

ForrestForrest

2,7151822

answered 1 hour ago

ForrestForrest

2,7151822

answered 1 hour ago

ForrestForrest

2,7151822