Snapshot Isolation and DMVs

Question

Snapshot Isolation and DMVs

chris.o.smith

SSCommitted

Points: 1674
More actions
December 6, 2019 at 3:47 pm
Go to Answer
#3705451
I recently noticed that DMVs, or at least sys.dm_db_partition_stats, does not respect snapshot isolation. I couldn't find anything describing this behavior in books online, so I was wondering if this is expected/desired behavior? Here are steps to follow if you'd like to repeat the behavior:
1. Create a dummy table but don't add any values to it yet:
```
IF OBJECT_ID('dbo.SnapshotIsolationTest') IS NOT NULL
DROP TABLE SnapshotIsolationTest;


CREATE TABLE SnapshotIsolationTest
(
col1 INT
);

GO
```
2. In a 2nd query window, start a transaction with snapshot isolation level snapshot that counts the number of records in the table. Don't commit the transaction. Running this will show 0 records, as expected:
```
--Set isolation level to snapshot so that the max row version is consistent with the data selected
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;

SELECT
 COUNT(*)
FROM dbo.SnapshotIsolationTest;
```
3. In a 3rd query window, set transaction isolation level to snapshot and then get a row count using sys.dm_db_partition_stats. Don't commit the transaction yet. Running this will show 0 records, as expected:
```
--Set isolation level to snapshot so that the max row version is consistent with the data selected
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;

SELECT
  SUM(row_count)
FROM  sys.dm_db_partition_stats 
WHERE object_id = OBJECT_ID('SnapshotIsolationTest')
  AND (
  index_id = 0
  OR index_id = 1
  );
```
4. Now add 2 records to the table created in the first query window:
```
INSERT INTO dbo.SnapshotIsolationTest (
  col1
  )
VALUES (0),(1);
```
5. Go back to the 2nd query window and rerun the SELECT COUNT(*) - remember that this is still an open transaction with snapshot isolation which started before the values were inserted into the table. It still shows 0 records, as expected.
6. In the 3rd query window, rerun the sys.dm_db_partition_stats query - remember that this is also still an open transaction with snapshot isolation which started before the values were inserted into the table. It shows 2 records, not respecting the transaction isolation level.
Jacob Wilkins

One Orange Chip

Points: 27976
More actions
December 10, 2019 at 9:24 pm
Answer
#3706699
Yeah, this has been known for a while.
See here, here, and here (this last is the wayback machine's copy of the connect item referenced by the second article).
From the first link:
(You can get the same information from sys.dm_db_partition_stats, but in that case change p.rows to p.row_count (yay consistency!). In fact, this is the same view that sp_spaceused uses to derive the count – and while it is much easier to type than the above query, I recommend against using it just to derive a count because of all of the extra calculations it does – unless you want that information, too. Also note that it uses metadata functions that do not obey your outer isolation level, so you could end up waiting on blocking when you call this procedure.)
(my emphasis)
Cheers!
- This reply was modified 4 years, 11 months ago by Jacob Wilkins.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101090 More actions · Answer 1

I would expect that behavior honestly. I don't see how SQL could realistically maintain a potentially almost unlimited number of intermediate row counts for different snapshot points for every table in the db. That would be extremely complex, for very little real payback.

On the other hand, I would expect SQL to make sure that a COUNT(*) was accurate, no matter how many snapshot points there were in the table.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Mike Good SSCertifiable Points: 7415 More actions · Answer 2

I am surprised to see this. Thank you for posting it.