High count of version-store pages in tempdb

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » High count of version-store pages in tempdb

High count of version-store pages in tempdb

Rate Topic

Display Mode

Topic Options

Author

Message

Marios Philippopoulos

Posted Saturday, January 03, 2009 4:08 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:10 AM
Points: 1,824, Visits: 3,477

I have scheduled the following query to run once a minute on my instance and store the results in a table for analysis:

SELECT SUM(version_store_reserved_page_count)
FROM sys.dm_db_file_space_usage;

Neither one of the row-versioning isolation levels is enabled on any of the databases.

At some point I get a utilization count of over 300,000 version-store pages recorded (close to 3 GB of data), but I don't know what query is causing this.

How can I find out which query is the culprit next time this happens?

The sys.dm_db_task_space_usage and sys.dm_db_session_space_usage DMVs do not provide any information on processes that trigger utilization of the version store.

__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation

Post #629370

Nicholas Cain

Posted Saturday, January 03, 2009 5:00 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 4:27 PM
Points: 2,007, Visits: 6,040

I know this doesn't describe exactly the problem that you are facing, however the concepts within this doc might help point you in the right direction

http://technet.microsoft.com/en-us/library/ms176029(SQL.90).aspx

Shamless self promotion - read my blog http://sirsql.net

Post #629379

Piotr.Rodak

Posted Saturday, January 03, 2009 5:05 PM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 10, 2012 9:49 AM
Points: 800, Visits: 1,759

Have you tried to select from sys.dm_tran_version_store?

Regards

Piotr

...and your only reply is slàinte mhath

Post #629382

Marios Philippopoulos

Posted Saturday, January 03, 2009 9:03 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:10 AM
Points: 1,824, Visits: 3,477

Thank you both, I'll have a look at your suggestions.

__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation

Post #629413

Marios Philippopoulos

Posted Saturday, January 03, 2009 9:13 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:10 AM
Points: 1,824, Visits: 3,477

Piotr Rodak (1/3/2009)

Have you tried to select from sys.dm_tran_version_store?

Regards

Piotr

I took a look at http://msdn.microsoft.com/en-us/library/ms186328(SQL.90).aspx. I need to get session and task-level info on the process that gives rise to the high number of version-store pages: login name, query-text, reads etc.

Not sure how to use sys.dm_tran_version_store to get this info.

__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation

Post #629415

Piotr.Rodak

Posted Sunday, January 04, 2009 6:59 AM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 10, 2012 9:49 AM
Points: 800, Visits: 1,759

I'd have a look what transactions and which databases the version store is generated for. It might give me clue regarding what process may be responsible for the issue.
But I never came across this issue myself, so it's pure speculation at this point.
It looks like sys.dm_tran_active_snapshot_database_transactions might contain the information you are looking for.
Regards

Piotr

...and your only reply is slàinte mhath

Post #629447

TRACEY-320982

Posted Sunday, January 04, 2009 7:33 AM

Right there with Babe

Group: General Forum Members
Last Login: Friday, May 03, 2013 9:01 AM
Points: 722, Visits: 996

Do you have a snapshot of any database as this will do the version control in tempdb.

Post #629452

Marios Philippopoulos

Posted Sunday, January 04, 2009 8:04 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:10 AM
Points: 1,824, Visits: 3,477

Thank you all for your input.

According to this link - http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx#E4CAC - the features generating version-store records in tempdb are:

• Snapshot isolation

• Read committed snapshot isolation (RCSI)

• Online index build

• Triggers

• MARS

Of all these the only one we use in our SQL instance is triggers to store information on DML operations in history tables for auditing purposes.

So I think my plan will be to poll sys.dm_exec_requests at the time during which this happens (seems to be a regularly occurring process) and use "OUTER APPLY sys.dm_exec_sql_text..." to get to the actual SQL running at the time. I will store this info in a table and look for the trigger operation and the table(s) involved.

Ultimately, my goal is to get at the actual SQL that is causing this and the login/session_ID under which it is running.

What do you guys think?

__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation

Post #629457

Marios Philippopoulos

Posted Tuesday, January 06, 2009 8:02 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:10 AM
Points: 1,824, Visits: 3,477

Using the method in my previous posting, I found the culprit: it is an UPDATE trigger on a large table.

__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation

Post #630599

« Prev Topic | Next Topic »

Permissions