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 |
|
andris_sh
Starting Member
3 Posts |
 Posted - 2011-09-30 : 08:09:12
|
| Hello.SQL server 2008, Standard Edition, 64-bytes.We have SSIS process which run several cycles of data load and in the end of each cycle launches certain query, which checks data integrity.Query contains about 12 UNION statements and uses tempdb heavily. It can be rewritten and it is not really an issue.In most cases query performs just fine. But every now and then it simply does not start. Activity monitor shows high CPU usage by the session. Usually query performs in about a minute, but after 10-15 cycles it suddenly hangs and works for 8-9 hours.There is plenty of space in tempdb log (query usually takes about 500 MB of tempdb log space, unused tempdb log space is about 3,5 GB). Activity monitor does not show any locks in Processes section. Disk activity is very low. Session shows CPU activity, but that is all. When we kill process and restart SSIS load, query performs fine (in a minute) for 10-15 iterations, then hangs again.As I said, query can be rewritten and issue solved this way, but I am more interested in query monitoring issue. With what tool and for what exactly I should look in case of such non-performing query?Taanks! |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-30 : 09:40:10
|
| Check for blocking.High CPU is usually scans. Check execution plan.12 UNION statements means a lot of sorting (checking for distinct) so that can be a problem, depending on how much data. |
 |
|
|
|
|
|
Query suddenly hangs