x

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 ?

more ▼

asked Jan 03, 2011 at 08:43 PM in Default

lok_taurian gravatar image

lok_taurian
31 3 3 3

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.
Jan 04, 2011 at 12:01 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.
more ▼

answered Jan 04, 2011 at 12:50 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

(comments are locked)
10|1200 characters needed characters left
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?
more ▼

answered Jan 04, 2011 at 03:08 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

+1, I missed out coding analysis.
Jan 04, 2011 at 03:10 AM Cyborg
@Cyborg I didn't think you missed anything.
Jan 04, 2011 at 03:12 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jan 03, 2011 at 09:11 PM

Cyborg gravatar image

Cyborg
10.6k 36 40 45

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x34

asked: Jan 03, 2011 at 08:43 PM

Seen: 1063 times

Last Updated: Jan 03, 2011 at 08:43 PM