Mdthbs

What's the difference between "ricochet" and "bounce"?

Was Mohammed the most popular first name for boys born in Berlin in 2018?

What dice to use in a game that revolves around triangles?

How is Arya still alive?

Why does the electron wavefunction not collapse within atoms at room temperature in gas, liquids or solids due to decoherence?

Employee is self-centered and affects the team negatively

Does STATISTICS IO output include Version Store reads?

Has everyone forgotten about wildfire?

Was the Highlands Ranch shooting the 115th mass shooting in the US in 2019

Is every story set in the future "science fiction"?

Thawing Glaciers return to hand interaction

resoldering copper waste pipe

What can cause an unfrozen indoor copper drain pipe to crack?

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

Is it safe to keep the GPU on 100% utilization for a very long time?

Identity of a supposed anonymous referee revealed through "Description" of the report

Is there a need for better software for writers?

How do I minimise waste on a flight?

Does Thread.yield() do anything if we have enough processors to service all threads?

Lorentz invariance of Maxwell's equations in matter

Why are thrust reversers not used to slow down to taxi speeds?

Are wands in any sort of book going to be too much like Harry Potter?

Not taking the bishop with the knight, why?

What is the Ancient One's mistake?

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
  51 mins ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Consider adding a way to view version store reads for that query execution (if it is reported anywhere).
  
  – Joe Obbish
  24 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
  51 mins ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Consider adding a way to view version store reads for that query execution (if it is reported anywhere).
  
  – Joe Obbish
  24 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
  51 mins ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Consider adding a way to view version store reads for that query execution (if it is reported anywhere).
  
  – Joe Obbish
  24 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