tempdb limits

[alex]

Hi,

we want to execute store procedure in paralel up to 250 times

procedure called from diffrent connections and create 4 temp tables during execution

so in total me might have 1000 temp tables with 5000 rows in each table.

I can place tempdb on separate drive and increase to any size.

Would 1000 temp tables be problem?

Thank you

Alex

[MAK]

Database objects include all tables, views, stored procedures, extended stored procedures, triggers, rules, defaults, and constraints. The sum of the number of all these objects in a database cannot exceed 2,147,483,647

1000 temp tables are ok. Make sure you are not doing Select * into a temptable. If you do, you will see a lot of blocking and timeouts.

Create the temptable first and then insert data to it.

For capacity specification, check this linke
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp

[skhanal]

Theoritically it should be ok, but I think you will run into a lot of blocking issues because of contention on sysobjects table in tempdb.

One way to minimize contention is to create multiple tempdb data files and use service pack 4. Refer to MS KB article

http://support.microsoft.com/default...b;en-us;328551

for details.