Monitoring the Version-Store Size
Because tempdb is used for the version store, applications that make heavy use of triggers in SQL Server 2000 need to be aware of possible increased demands on this database after you upgrade to SQL Server 2005.Thus, you need to appropriately monitor and manage disk space for the version store.
The version store consists of append-only store units, which I'll explain shortly.You can look at the following SQL Server Performance Monitor counters in the SQL Server:Transactions object to see how many store units you have:
- version store unit count: count of version store units
- version store unit creation: total number of version store units created to store row versions since the instance was started
- version store unit truncation: total number of version store units removed since the instance was started
Although the SQL Server Books Online (BOL) page that describes the counters in the SQL Server:Transactions Object describes the store units as allocation units, they're nothing like the allocation units used to store regular table data. (For more information, see "Managing Data Space," June 2006, InstantDoc ID 50009.) The append-only store units don't belong to particular objects, as regular allocation units do, and are highly optimized for sequential inserts and random lookups. Each store unit can store many row versions from many different objects. If versions are to be stored either from snapshot isolation or from triggers, SQL Server creates a new store unit about every minute. Once started, a transaction will store all its versions in the same store unit no matter how long the transaction is active.
You can examine the values from Performance Monitor programmatically, by using the Dynamic Management View (DMV) sys.dm_os_performance_counters. The query in Listing 2 shows you the values for the three counters mentioned earlier.The value of version store unit count, which is shown in the query results as the current number of store units, is the difference of the other two values; that is, the total number of store units created minus the total number removed.
The size of a specific store unit depends on the number of versions generated by the transactions writing to that store unit.(Keep in mind that queries running under snapshot isolation might actually read versions from any store unit because a query might need to retrieve data modified by many different transactions.)
A background thread periodically removes store units if none of the versions they contain are needed any longer. Individual rows aren't removed, only entire units. This deallocation occurs in the order the store units were created, so that a store unit created at time T110 can't be removed prior to one created at time T100, even if no versions in the T110 store unit are needed.
In addition to the three counters I mentioned, you should also monitor these additional counters in the SQL Server: Transactions object: free Space in tempdb (KB) and version store size (KB). You can also retrieve the version store size by selecting from the sys.dm_db_file_space_usage DMV and looking at the column called version_store_reserved_page_count. This value (in pages) can be multiplied by eight to get comparable values to those displayed in the Performance Monitor, which returns values in KB.The value for version store size (KB) should in fact be very close to the value of version_store_reserved_page_count times eight.Thus, you can use either tool to monitor the space that the version store uses.
Keep an Eye on RLV
Even if you never plan to use one of the snapshot-based isolation levels, you aren't off the hook as far as row versioning is concerned. If you're upgrading a database that has any triggers on it, row versioning will be used in that process. SQL Server uses the version store in tempdb to keep track of the triggers' inserted and deleted values, and you must monitor your tempdb growth as if you were using snapshot isolation. I've showed you some of the basic counters to monitor that will help you detect the space requirements of tempdb when using RLV. Next month, I'll provide more details about monitoring the version store and demonstrate its use with triggers when snapshot isolation is enabled.
End of Article
Prev. page
1
[2]
next page -->
