1

We are trying to analyze the growth of tempdb on one of our SQL server 2005 Ent SP4.

I know tempdb would need the space depending upon the transactions, and how queries are designed to use it.

We had an issue for disk space getting full , where we analyzed how tempdb grew that big over a week.

After a reboot, via monitoring we came across 4-5 queries, which were holding up version store for more than 20 minutes, grew tempdb same day from its initial size of 2 GB ( 4 data files) to over 100 GB within a spam of 6-8 hours.

For example

query A holds the version store for 10 minutes where current version generation rate was 23.81 KB/sec and the cleanup rate was 0.00 KB/sec. The version store size was 17 MB and tempdb file size was 10332 MB.

Later after an hour the query B ran, the version store for 16 minutes where current version generation rate was 33.81 KB/sec and the cleanup rate was 0.00 KB/sec. The version store size was 45 MB and tempdb file size was 45332MB.

Similarly a query C ran after 3-4 hours, the version store for 15 minutes where current version generation rate was 33.81 KB/sec and the cleanup rate was 0.00 KB/sec. The version store size was 19 MB and tempdb file size was 85332MB.

and so on on. during our monitoring for that day.

Now same queries run next day and day after,

We see that tempdb allocations and dellocations value running to somewhat similar during the very first day, but tempdb did not grew that fast.

same happens for upcoming 7-8 days where growth is approx 8-10 GB compared to first day where it grew to 100 GB for the similar queries running each day.

Also , the current output of select count(*) from sys.dm_tran_version_store

is 196470 and its been 2 days since a reboot.

May be i am unaware of some process here, but please help me understand what could be the reason apart from those identified queries as we are trying to improve if they are the one causing tempdb to grow that much.

Note* tempdb size is 110 GB and Free space it shows is almost 109 GB.

  • The version store numbers are very small (e.g. 45 MB). Why do you think the version store would have anything to do with this? – usr Jun 23 '15 at 23:30
1

There can be many reasons for that.

  • You're queries run and generate a version, during another query use the same tables. During this phase version 1 will be hold back. If your transaction isn't commited at the end, it may occur that version 1 will last for a long time in your tempdb. Make sure that all transactions are probably commited and closed.
  • Make sure that all information is stored on the disc. It may occur that your information is still stored in your memory. This could cause the hold of a version in your tempdb. You can force the latch process by using CHECKPOINT after your commit.

Hopefully this will help you.

  • thanks for the suggestion. For point 1, yes we are using commit in our queries, even still we see a hold back. Also, we used checkpoint as a test for one of them, no luck. still will test with remaining queries. – KASQLDBA Jun 25 '15 at 11:15
1

Version store basically cause from long running transaction or high transaction throughput. You need to look for the queries which are causing this. Other than that version store is used in following conditions:

  • Row version which are generated by data modification transactions for features (MARS, online indexes, AFTER trigger etc)
  • Row version which are generated by data modification transaction for databases (Database is using snapshot or read committed isolation)

Remember, TempDb check point has lower priority than other database checkpoint. I have written an article specifying recommendation for TempDb which would provide you more insight about it. If you have high version store then you need to look into above points which I have mentioned.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.