Prev: Steps for copying databases?
Next: Maintenance scripts
From: cbrichards via SQLMonster.com on 3 Jul 2010 08:57
We have Read Committed Isolation enabled and I was interested in more closely
monitoring TempDB usage. We do NOT have Snapshot Isolation enabled, only Read
Committed Isolation.

In looking at sys.dm_db_file_space_usage, the column
version_store_reserved_page_count specifically, does that relate to the usage
of the version store when Read Committed Isolation is enabled, or only when
Snapshot Isolation is enabled, or both?

Second question. It appears that according to sys.dm_db_file_space_usage, the
column version_store_reserved_page_count specifically, that the page counts
are active (changing) on a server even when none of the databases on the
server have either Snapshot Isolation or Read Committed Isolation enabled.
Why is that?

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201007/1

From: Erland Sommarskog on 3 Jul 2010 16:59
cbrichards via SQLMonster.com (u3288(a)uwe) writes:
> We have Read Committed Isolation enabled and I was interested in more
> closely monitoring TempDB usage. We do NOT have Snapshot Isolation
> enabled, only Read Committed Isolation.
>
> In looking at sys.dm_db_file_space_usage, the column
> version_store_reserved_page_count specifically, does that relate to the
> usage of the version store when Read Committed Isolation is enabled, or
> only when Snapshot Isolation is enabled, or both?

To both, and everything else that uses the version store. Find an
SQL Server instance to play with an restart it, make sure that no one
else is around. Then run this:

select * from sys.dm_db_file_space_usage
go
create database rcsi
alter database rcsi set read_committed_snapshot on
go
use rcsi
go
create table klump(a int NOT NULL)
go
insert klump (a)
select object_id + column_id * 10000
from sys.columns
go
begin transaction
delete klump
go
select * from sys.dm_db_file_space_usage
go
rollback transaction
go
use master
go
drop database rcsi
go
select * from sys.dm_db_file_space_usage

You will see that the version store is used.

> Second question. It appears that according to
> sys.dm_db_file_space_usage, the column version_store_reserved_page_count
> specifically, that the page counts are active (changing) on a server
> even when none of the databases on the server have either Snapshot
> Isolation or Read Committed Isolation enabled.

The version store is used for many things. On the top of my head
I recall triggers and MARS, but I believe there is more. Again,
restart SQL Server and run this:

select * from sys.dm_db_file_space_usage
go
create database rc
go
use rc
go
create table klump(a int NOT NULL)
go
create trigger klump_del_tri on klump for delete as
select count(*) from deleted
select * from sys.dm_db_file_space_usage
go
insert klump (a)
select object_id + column_id * 10000
from sys.columns
go
delete klump
go
use master
go
drop database rc
go
select * from sys.dm_db_file_space_usage



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Steps for copying databases?
Next: Maintenance scripts