Scenario: DBA found that tempdDBdatabase usage is getting high and most of the size is consumed by row versioning. DBA raised the issue with application team running that query. Once Application team close the session, tempDB usage comes normal.
Question: Application tea raise concern “How can a select statement on table consume huge amount of resources in tempDB?”
Answer:
As per BOL (https://technet.microsoft.com/en-us/library/ms175492%28v=sql.105%29.aspx) : Row versioning-based isolation levels (RCSI – Read Committed Snapshot Isolation) reduce the number of locks acquired by transaction by eliminating the use of shared locks on read operations. This increases system performance by reducing the resources used to manage locks. Performance is also increased by reducing the number of times a transaction is blocked by locks acquired by other transactions. Row versioning-based isolation levels increase the resources needed by data modifications.
While updating the row in a table or index, the new row is marked with a value called the transaction sequence number (XSN), XSN is unique number and keeps updating with upcoming new command \ transactions. When updating a row, New version is generated. If DB is already having previous version of this row, previous version of the row is stored in the version store and the new version of the row contains a pointer to the old version of the row in the version store. New row version stores corresponding XSN value for reflecting the time the row was modified.
All version stores are linked in a chain. SQL Server keep running clean up task to remove old versions which are not in use. As long as a transaction is open, all versions of rows that have been modified by that transaction must be kept in the version store. This is the reason due to which long running open transaction can cause huge tempDB row versioning space.
Here, Row X is getting updated by multiple transactions (Tv,Tx,Ty,Tz) and each time new version store is created. When DB engine tries to clear old unused version stores, it found open transaction using version X. DB engine not able to clear any row version from version store after version X. Due to which TempDB size keeps increasing due to row versioning.
–Check Size of tempDB consumed by Version Store
SELECT SUM(VERSION_STORE_RESERVED_PAGE_COUNT) AS [VERSION STORE PAGES USED],
(SUM(VERSION_STORE_RESERVED_PAGE_COUNT)*1.0/128) AS [VERSION STORE SPACE IN MB],
SUM(INTERNAL_OBJECT_RESERVED_PAGE_COUNT) AS [INTERNAL OBJECT PAGES USED],
(SUM(INTERNAL_OBJECT_RESERVED_PAGE_COUNT)*1.0/128) AS [INTERNAL OBJECT SPACE IN MB],
SUM(USER_OBJECT_RESERVED_PAGE_COUNT) AS [USER OBJECT PAGES USED],
(SUM(USER_OBJECT_RESERVED_PAGE_COUNT)*1.0/128) AS [USER OBJECT SPACE IN MB],
SUM(UNALLOCATED_EXTENT_PAGE_COUNT) AS [FREE PAGES],
(SUM(UNALLOCATED_EXTENT_PAGE_COUNT)*1.0/128) AS [FREE SPACE IN MB]
FROM SYS.DM_DB_FILE_SPACE_USAGE;
–Find session using version store
SELECT A.*,B.KPID,B.BLOCKED,B.LASTWAITTYPE,B.WAITRESOURCE,B.DBID,B.CPU,B.PHYSICAL_IO,B.MEMUSAGE,B.LOGIN_TIME,B.LAST_BATCH,
B.OPEN_TRAN,B.STATUS,B.HOSTNAME,B.PROGRAM_NAME,B.CMD,B.LOGINAME,REQUEST_ID
FROM SYS.DM_TRAN_ACTIVE_SNAPSHOT_DATABASE_TRANSACTIONS A
INNER JOIN SYS.SYSPROCESSES B
ON A.SESSION_ID = B.SPID
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
http://www.facebook.com/mssqlfun
Other Linked Profiles :-
http://www.sqlservercentral.com/blogs/mssqlfun/
http://social.msdn.microsoft.com/Profile/rohitgarg
http://www.toadworld.com/members/rohit-garg/blogs/default.aspx
