asked
Temp tables and Temp DB
Team, I have a stored procedure which uses temporary tables (local). The procedure runs at non-office hours. worst part of this process is that it takes 5 hours to complete! A recent trace shows that it takes 3 hours just to load a temp table. Kindly help me resolve this. When we use a Temp table, does it lock the temp db while loading the data into temp table ? If it does, what would happen if any other process on the same server tries to create another temp table ?
On SQL Server 6.0 Using INSERT INTO #temp query creates lock on tempDB but it was fixed on SQL Server 7 and Later. You may be facing bottlenecks on your tempdb configurations, check out the following- Make sure that your tempdb files are on high performance separate disk preferably RAID1, because sql server often uses tempdb to create hash tables, sorting, version stores etc. Adequately set your tempdb size by setting the file size to a value large enough to accommodate the high workload. This prevents tempdb from expanding too frequently, which can affect performance. As an additional step Analyze your index and statistics structure on your query that you use to load data into temp table.
What sort of data is going into the temp table? loading 100 million rows into a temp table would take a very long time - whereas loading 10 thousand rows should be much faster. Without any sort of idea of what your procedure is doing, it's difficult to say. One thing to definitely avoid is inserting the data into the temp table row-by-row - that will get progressively slower over time, and won't be a pretty picture. If you are loading huge numbers of rows in one statement, you can sometimes get better results by breaking that up into smaller batches.
Everyone else has given great advice, but let me directly answer your immediate question, no, loading data into a temp table doesn't lock the tempdb database. You're likely hitting contention on resources, not a specific blocking issue. Follow @Cyborg's and @Matt's advice. Yes, you need to optimize how tempdb is configured, but the bigger question is, what does your code look like, what is it doing, why is it moving data into tempdb, how is it moving data into tempdb, and finally, is there a better way to arrive at the same answer?
