July 7, 2015 at 6:04 pm
We have an issue with the Version Store growing constantly. According to sys.dm_os_performance_counters, "Version Generation rate (KB/s)" is growing, but "Version Cleanup rate (KB/s)" isn't. We use read-committed snapshot isolation
While dbcc opentran and sys.dm_exec_requests don't show any long running transactions, I wrote a query looking at sys.dm_tran_active_snapshot_database_transactions. This shows a number of long running transactions but, according to sys.dm_exec_sessions, they are all sleeping. The transactions that are running come and go very quickly, as I would expect.
Could these sleeping transactions be responsible for preventing the version store from cleaning up?
July 9, 2015 at 12:21 am
Further to the above query someone here has pointed out that sp_spaceused shows the majority of space in tempdb is unallocated and is now trying to tell me there is no issue.
If I look at the properties in SQL Server Management Studio it shows tempdb as mostly available (Space Available is over 90% of Size). Running sp_spaceused shows a similar ratio between database_size and unallocated_space.
If tempdb, and therefore the Version Store, was really empty, I would have no explanation of why tempdb is growing, or why Version Cleanup rate (KB/s) doesn't seem to be changing.
Something is using tempdb, or it wouldn't need to keep growing as it is.
Is it possible that sp_spaceused and the UI are not accurate for tempdb? I've tried to do a search online, but I can't work out why these values are inconsistent.
July 9, 2015 at 5:31 am
tempdb will always grow and shrink depending on what operations are accessing your different databases. This database keeps all the variables, temp tables (including table variables), and "in memory" calculations for queries. So unless no one is using your databases, it should be growing.
The question is, is tempdb growing uncontrollably or is it a consistent rate of growth?
Do you have a profiler trace open or an activity monitor window constantly open to watch the databases? That might be affecting your numbers. Remember, even monitoring takes up memory, cpu, and tempdb storage.
When you look at sleeping connections, are you just looking at connections to tempdb or are you looking at connections all across the board?
There is so much information missing from this post, I couldn't begin to tell you if you really have a problem or if this is normal. If no one at your organization can help you with this, you might consider hiring a consultant for a week or two to help you out with some baselines.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply