Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-05-16 : 08:23:44
|
| barmalej writes "HiDuring performance problems investigation at client database I found rather strange thing and it is interesting to know if it can affect performance and what to do if it does. Trying to open tempdb subtree in EM leads to a delay in several minutes, other subtries (server own and production) open practically at once. Query of type select top 100 * from tempdb..sysobjects takes about 3 min or more. Trying to open tempdb properties to see its location (if same disc with main database) simply fails as it seems it hangs forever. Microsoft Windows server, Standard edition, SP1,Two 2 GHz processors, 1GB RAM. Hospital database, about 80-100 concurrent users.Thanks in advance " |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-05-16 : 08:26:55
|
| Look at performance monitor while this is happening. Do you see an increasing write or read disk queue length on the disk that contains the tempdb database while this is happening?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-05-16 : 14:55:31
|
If you have long running queries that do a SELECT INTO a temp table, you can have schema locks on tempdb that cause problems like this.select * into #temp from... The best solution is to always do a CREATE TABLE for temp tables, instead of doing a SELECT INTOCODO ERGO SUM |
|
|
|
|
|
|
tempdb response time