Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

High count of version-store pages in tempdb Expand / Collapse
Author
Message
Posted Saturday, January 3, 2009 4:08 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:34 PM
Points: 1,865, Visits: 3,615
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 2
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
Posted Saturday, January 3, 2009 5:00 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 9:52 AM
Points: 2,006, Visits: 6,078
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
Posted Saturday, January 3, 2009 5:05 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!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
Posted Saturday, January 3, 2009 9:03 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:34 PM
Points: 1,865, Visits: 3,615
Thank you both, I'll have a look at your suggestions.

__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
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
Posted Saturday, January 3, 2009 9:13 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:34 PM
Points: 1,865, Visits: 3,615
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 2
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
Posted Sunday, January 4, 2009 6:59 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!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
Posted Sunday, January 4, 2009 7:33 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:04 PM
Points: 724, Visits: 1,002
Do you have a snapshot of any database as this will do the version control in tempdb.
Post #629452
Posted Sunday, January 4, 2009 8:04 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:34 PM
Points: 1,865, Visits: 3,615
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 2
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
Posted Tuesday, January 6, 2009 8:02 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:34 PM
Points: 1,865, Visits: 3,615
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 2
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 »

Add to briefcase

Permissions Expand / Collapse