Slow reaction time of tempdb in MSSQL2000 - URGENT!
Posted on 2004-09-30
We have an MSSQL2K database which uses the tempdb for temporary query result-tables (which are created by a stored procedure, filled in with data, queried and then dumped).
We noticed that as the size of data increases (and we are not talking about hugh quantities, about a few thousands of records), the reaction time of the tempdb got slower and slower.
When testing the stored procedure, and breaking it into stages, we saw it took 8 seconds to do the CREATE of the temporary table, another 8 seconds to the the INSERT (of about 1000 records) and then another 8 seconds to do a select (which is a very simple select that returns the top 100 records of that table).
Upon further examination, we found out that the tempdb had grown to 32GB (!), out of which only 1.5MB were being used.
We shrunk the tempdb using the dbcc shrinkdatabase so it is now down to 8MB, we also restarted the DB itself, hoping that would resolve the problem.
Unfortunatley, even though the tempdb is now in normal size, the SP still takes 24 seconds to perform.
We tested doing exactly the same procedure but on ready-made empty tables on the regular DB, and then the insert took about 1 second and the select less than that, so obviously there is something very wrong with the tempdb.
We tested it on other MSSQL2K database machiens we have with more or less the same result, so this is not a one-time occurence but rather a behaviour of the tempdb.
Can anyone tell me how I can resolve this? What kind of settings do I need to do to make it respond in a reasonible amount of time? What could have caused a tempdb of a 100MB live database grow to 32GB? and how can I prevent it from happening again?
The top priority is to speed up the creation, inserting and selection from the tempdb. The SP itself is optimized and the problem is defintley the response time of the tempdb itself.
This is quite urgent as this is a query which works at the heart of our business' web system, which is now getting time-outs due to the long response time.
Any help would be appreciated, thank you in advance.