May 14, 2013 at 3:41 am
Hi All,
There is a set of stored procedures that is executed as a separate module. At the time this module executes nothing else is working on the database.
The last three days the client complained for a huge TempDB growth (1TB) which was not a case so far. Additionally it's been 25 days since we moved the Sql Server 2008 r2 database from Windows server 2008 r2 to Windows server 2012.
In the above mentioned set, temp tables are used. After a check in some SPs the temp tables were not dropped. We plan to do this.
In one SP there is a global temp table which is dropped under a condition defined in the application running the sp set. We will check this too and ensure the global temp table is dropped for sure.
Can anyone suggest any other tips or hints. Can it be something with the Windows server version?
Please you're welcome to share...
Best regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
May 14, 2013 at 4:51 am
Tempdb growth has to be because of tempdb use. There's nothing in the OS that's going to affect tempdb. It's not just temp tables. It's also any sort operations, index updates, hash join operations, snapshot isolation and more that all affect the tempdb. But, usually, the number one issue is code. It sounds like yours is using a lot of tempdb for it's operations. I'd suggest looking to see where and how you can eliminate that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 14, 2013 at 5:17 am
Grant Fritchey (5/14/2013)
Tempdb growth has to be because of tempdb use. There's nothing in the OS that's going to affect tempdb. It's not just temp tables. It's also any sort operations, index updates, hash join operations, snapshot isolation and more that all affect the tempdb. But, usually, the number one issue is code. It sounds like yours is using a lot of tempdb for it's operations. I'd suggest looking to see where and how you can eliminate that.
Yes Grant, I consider the other operations that I didn't mentioned. We're exactly looking into the code.
It's interesting that the same code didn't show performance issues before, and now on the new server it did.
Thank you for your reply!
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply