Tempdb Growth & profromance | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Tempdb Growth & profromance

Hi all,
After restarting the production server, our stored proc. seems to executing at the normal speed what we are see in Dev & UAT servers. After couple of days performance of the stored proc. seems to be slow, for instance stored proc. which was excuting at 3 sec goes up to 6 sec & then 10 secs. What we noticed is creation of temp tables in Tempdb & size of it. After week we have around 400 temp tables & size of tempdb is around 1gb. We have seperate drive for tempdb which is around 8gb space. According to BOL, temp tables in tempdb gets dropped after the session or stored proc. completes . How come there is 400 temp tables in tempdb. Is tables in tempdb can slow down the proformanance of the system?
Thanks
emamuthu
Just like in most any programming language, it is a good habit to manually remove what you don’t need after you use it, such as temp tables. In theory, they should be deleted after a connection is dropped, which makes me think that perhaps connections are not being dropped as you thing they are. You will need to check this out. But, as I said before, if you get in the habit of manually removing objects you don’t longer need, instead of leaving it up to the system, you will have better overall performance. ————————————————————–
Brad M. McGehee, SQL Server MVP
Technical Editor/Moderator www.SQL-Server-Performance.Com
Director of DBA Education for www.Red-Gate.Com
www.sqlbrad.com
www.sqlHawaii.com
There are many factors to consider for TEMPDB issues in SQL 2005, http://sqlserver-qa.net/blogs/tools…tempdb-is-full-in-sql-2000-2005-versions.aspx &http://sqlserver-qa.net/blogs/tools…server-index-optimization-best-practices.aspx fyi to take consider. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
thanks for the reply…but my tempdb isn’t full at all..i still have 7 gb of free space…As of now my tempdb size is just 800MB & has around 400+ temp tables…still the performance seems to be getting bad as the temp tables being created in tempdb. I checked all the stored procedure in my prodution server & we drop all the temp tables at the end of stored proc. But still the temp tables are being created in tempdb & creating proformance problem. thanks
emamuthu

Then this (performance) may not be related to TEMPDB or temp tables creation, the queries are culprit and you have to normalize them to attain the performance. check what are the slow running queries using PROFILER in this case, take help of DTA too. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>