question

lok_taurian avatar image
lok_taurian 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 ?
temporary-table
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
If possible, post the query and table structures along with sample data and we can help you out. Another possibility would be to supply the execution plan so we can see what is happening in the server.
0 Likes 0 ·
Cyborg avatar image
Cyborg answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
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?
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image Cyborg commented ·
+1, I missed out coding analysis.
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
@Cyborg I didn't think you missed anything.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.