January 3, 2009 at 4:08 pm
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.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 3, 2009 at 5:00 pm
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
January 3, 2009 at 5:05 pm
Have you tried to select from sys.dm_tran_version_store?
Regards
Piotr
...and your only reply is slàinte mhath
January 3, 2009 at 9:03 pm
Thank you both, I'll have a look at your suggestions.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 3, 2009 at 9:13 pm
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.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 4, 2009 at 6:59 am
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
January 4, 2009 at 7:33 am
Do you have a snapshot of any database as this will do the version control in tempdb.
January 4, 2009 at 8:04 am
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?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 6, 2009 at 8:02 am
Using the method in my previous posting, I found the culprit: it is an UPDATE trigger on a large table.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply