patternsqlMinor
Slow temp table drops in sql 2005
Viewed 0 times
sqltempslow2005dropstable
Problem
I've run into a problem on our production sql server where temp table objects take a long time to drop (obvious when small and synchronous drop is used). I can't reproduce this on other sql servers (similarly spec'd with same numbers of spindles serving tempdb data files (split in same number of files (1 per physical core)).
On SQL 2005 Enterprise (SP2 - 3042).
Update: one more factor - which is looking the most likely. this server has >500 databases on it. Another server with > 800 also runs these drops slow. That's the only other server I've got with a lot of dbs on it.
Second Update: restart of the problem servers will allow the create and drop statements to execute instantaneously. Performance of the test degrades over the next few hours (while application is running) until it hits (what appears to be) a plateau. I've got a job running in the background that is testing this every 30 mins. I'll see what results are after a few days and see if execution times are the same. I think they will be.
Third Update: While none of the executing statements have shown latch waits on CPU resources, using sp_whoisactive I see that during a delta_interval = 30 (seconds) run, running query the CPU_delta reports roughly 30,000 (milliseconds?) and when I watch perf during execution there appears to be one core worth of cpu spike during the execution time. these are on 16 cpu boxes so it can be a bit tough to see via perfmon when other traffic is occurring, but it appears to be spiking a cpu worth during execution of drop statements.
Create and destruction of 20 tiny temp tables with unique names (one column, no rows) takes less than 20ms on most servers I test it on. On one server it takes > 5 seconds.
The vast majority (>95%) of the time is spent on the drop statements.
During execution there are no explicit waits, and no blocking being reported, and perfmon doesn't show any load on the I/O subsystem for either data or log files.
I've looked at peak and low usage tim
On SQL 2005 Enterprise (SP2 - 3042).
Update: one more factor - which is looking the most likely. this server has >500 databases on it. Another server with > 800 also runs these drops slow. That's the only other server I've got with a lot of dbs on it.
Second Update: restart of the problem servers will allow the create and drop statements to execute instantaneously. Performance of the test degrades over the next few hours (while application is running) until it hits (what appears to be) a plateau. I've got a job running in the background that is testing this every 30 mins. I'll see what results are after a few days and see if execution times are the same. I think they will be.
Third Update: While none of the executing statements have shown latch waits on CPU resources, using sp_whoisactive I see that during a delta_interval = 30 (seconds) run, running query the CPU_delta reports roughly 30,000 (milliseconds?) and when I watch perf during execution there appears to be one core worth of cpu spike during the execution time. these are on 16 cpu boxes so it can be a bit tough to see via perfmon when other traffic is occurring, but it appears to be spiking a cpu worth during execution of drop statements.
Create and destruction of 20 tiny temp tables with unique names (one column, no rows) takes less than 20ms on most servers I test it on. On one server it takes > 5 seconds.
The vast majority (>95%) of the time is spent on the drop statements.
During execution there are no explicit waits, and no blocking being reported, and perfmon doesn't show any load on the I/O subsystem for either data or log files.
I've looked at peak and low usage tim
Solution
You didn't mention the IOPS used by the tempdb LUN. Are you seeing any IO waits on the LUN device? Is anything else on that LUN? Is another system using the disk group/pool that the LUN is carved from?
Ditto master db -- same q's.
Also, are you using local temp tables (#tablename) or global temp tables (##tablename)? If global could another SPID be keeping the shared lock on the global temp tables when you're trying to delete them?
Ditto master db -- same q's.
Also, are you using local temp tables (#tablename) or global temp tables (##tablename)? If global could another SPID be keeping the shared lock on the global temp tables when you're trying to delete them?
Context
StackExchange Database Administrators Q#7753, answer score: 8
Revisions (0)
No revisions yet.