Etydhv

Gain of Non-Inverting Amplifier Does Not vary with Resistor Values

Which spells are in some way related to shadows or the Shadowfell?

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

Are there vaccine ingredients which may not be disclosed ("hidden", "trade secret", or similar)?

How likely are Coriolis-effect-based quirks to develop in starship crew members?

Ugin's Conjurant vs. un-preventable damage

What happens when the drag force exceeds the weight of an object falling into earth?

Is it a Munchausen Number?

I might have messed up in the 'Future Work' section of my thesis

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

Why did Missandei say this?

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

Pre-1993 comic in which Wolverine's claws were turned to rubber?

Can you turn a recording upside-down?

Not taking the bishop with the knight, why?

Two (probably) equal real numbers which are not proved to be equal?

Employee is self-centered and affects the team negatively

Lorentz invariance of Maxwell's equations in matter

Can I bring back Planetary Romance as a genre?

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

Narcissistic cube asks who are we?

Using wilcox.test() and t.test() in R yielding different p-values

My perfect evil overlord plan... or is it?

Integral with DiracDelta. Can Mathematica be made to solve this?

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;

3

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

ForrestForrest

2,7001822

add a comment | 

3

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

ForrestForrest

2,7001822

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

ForrestForrest

2,7001822

share|improve this question

asked 45 mins ago

ForrestForrest

2,7001822

share|improve this question

asked 45 mins ago

ForrestForrest

2,7001822

asked 45 mins ago

ForrestForrest

2,7001822

asked 45 mins ago

ForrestForrest

2,7001822

1 Answer
1

active

oldest

votes

3

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

ForrestForrest

2,7001822

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  +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
  15 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

3

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

ForrestForrest

2,7001822

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  +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
  15 mins ago
  

  
  
  
  

add a comment | 

3

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

ForrestForrest

2,7001822

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  +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
  15 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 45 mins ago

ForrestForrest

2,7001822

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

ForrestForrest

2,7001822

answered 45 mins ago

ForrestForrest

2,7001822

answered 45 mins ago

ForrestForrest

2,7001822