January 13, 2010 at 11:30 am
Hello,
I'm currently facing an issue with tempDB when using a simple delete DML statement:
DELETE SchemaName.TableName
No triggers on the table and tempDB grow disproportionally when run.
The table can contain millions of rows and the more rows it delete, more space on the disk tempDB will grab. I understand for the log file but for tempDB? What could make that happen? No other transaction are running and no long running transactions are running either (everything's clear using DBCC opentran)
I've reproduced this issue where nothing was running except the DELETE statement so nothing else can conflict with it.
I've added the trace flags 1140 (http://support.microsoft.com/kb/2000471/en-US) because it was an imported SQL Server 2005 DB onto SQL Server 2008 but it didn't help much (about a few percent was saved only)
Truncate would be marvelous but unfortunately, it cannot be used because the table is used in a transactional replication (http://msdn.microsoft.com/en-us/library/ms177570.aspx)
Shrinking the tempDB file on the fly does not always work as intended either (a patch solution).
And of course restarting SQL to set back default tempDB size upon SQL service start is out of question.
I'm totally clueless why tempDB is being used when using a simple delete statement.
Any idea would be greatly appreciate.
Thank you
January 13, 2010 at 11:43 am
You may want to read this article, http://www.sqlservercentral.com/articles/T-SQL/67898/. It is meant to manage the growth of a databases log file, but it may also help you with the growth of tempdb as well.
January 13, 2010 at 12:27 pm
Thank you for your post Lynn
However, my issue is not with the log file (this is handle separately), it is the tempDB mdf file size.
If I'm deleting half the data, the tempDB size will have grow only half the size of the issue.
If I'm deleting a quarter of the data, the tempDB size will have grow only a quarter of the size of the issue.
It grows proportionally on how many rows are deleted and never reuse the space it took.
So doing them in batch is a good thing for the transaction log and for concurrency but for my issue it does not help quite much (I've give it a try though, (batch deleting) in case of it could work). TempDB does not reuse the space left immediately, it look like it's random when SQL reuse tempDB space.
Using select * from sys.dm_db_file_space_usage, version_store_reserved_page_count count still grow by the delete. Other stats are always pretty low. Some time sql clean up something and the version store count switch to 0 and fill (release pages). Unallocated extend page shows this.
I've also tried mixing the trace flag 1140 and batch deleting, didn't do much either 🙁
Good post however
January 13, 2010 at 12:30 pm
I've look up to SP1 CU5 for fixes and nothing about tempDB or DELETE dml statements
January 13, 2010 at 12:38 pm
Megistal (1/13/2010)
Thank you for your post LynnHowever, my issue is not with the log file (this is handle separately), it is the tempDB mdf file size.
If I'm deleting half the data, the tempDB size will have grow only half the size of the issue.
If I'm deleting a quarter of the data, the tempDB size will have grow only a quarter of the size of the issue.
It grows proportionally on how many rows are deleted and never reuse the space it took.
So doing them in batch is a good thing for the transaction log and for concurrency but for my issue it does not help quite much (I've give it a try though, (batch deleting) in case of it could work). TempDB does not reuse the space left immediately, it look like it's random when SQL reuse tempDB space.
Using select * from sys.dm_db_file_space_usage, version_store_reserved_page_count count still grow by the delete. Other stats are always pretty low. Some time sql clean up something and the version store count switch to 0 and fill (release pages). Unallocated extend page shows this.
I've also tried mixing the trace flag 1140 and batch deleting, didn't do much either 🙁
Good post however
I understand that your issue is not the LOG file but TEMPDB. I was suggesting that the same method used to manage the growth of the log file MAY also help you with the issue you are having with tempdb growth.
It is worth trying.
January 13, 2010 at 12:43 pm
What is the isolation level you are using?
Can you show me the code you used for batch deletes?
January 13, 2010 at 12:48 pm
Read committed is used when deleting
Some transaction used snapshot sometimes but none of them are running when testing this (because they will use tempDB for the versioning) otherwise it will not.
This could be a test however switching off the snapshot feature and test it again.
The quick an dirty way I tested that out:
set rowcount 1000
WHILE 1 = 1
BEGIN
DELETE SchemaName.TableName
IF @@ROWCOUNT = 0
BREAK
END
January 13, 2010 at 1:02 pm
I've switch off the isolation level, to test that out.
It seems that it has only slightly something to do with it because tempDB seems to not using as much space as before but still it's taking too much space (from 1 megs to several gigs).
The behavior I'm noticing is at some point SQL seems to flush data (similar like a checkpoint but for tempDB) once that done, extend are free and SQL reuse them to process ongoing delete statements until none are free.
January 14, 2010 at 9:51 am
Finally yes it is the snapshot isolation that's causing the issue. I must have did something wrong when testing yesterday.
Thank you for your help, the isolation level had slip off my mind at first.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply