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-19 : 07:32:06
|
| 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 " |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-05-19 : 14:48:34
|
| Wouldn't try to use enterprise manager for things like this.try select * from master..sysaltfilesselect * from tempdb..sysfilesTo find the location.Sounds like the system is creating a lot of temp tables and so blocking.Think about changing it to use table variables for small tablesMaybe it is creating reports and using large temp tables for sorting - think about a separate reporting server.Load http://www.mindsdoor.net/SQLAdmin/sp_nrInfo.htmland run it to detect blocking.When you look at tables in tempdb do you see lots? Look at the create date and also rows in sysindexes for them.When trying to view things like this always useset transaction isolation level read uncommitted==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-05-19 : 20:47:37
|
| Also, look at performance monitor. What do the queue lengths look like for the disks that tempdb sets on? Specifically, look at the physical disk write and read queue lengths.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
tempdb response time