Thanks for your suggestion. Here's what I see if I check Max Memory:

PhysicalMemoryMBMaxServerMemoryMBIsAweEnabled

8181.80 6144 ...

Excellent! Thanks so much for sharing the benefit of your experience. I will plan to do some tinkering this weekend and set the snapshot option.

Oooo! Nice feature (ad hoc workloads). I hadn't read up on that one for 2008 before. Thanks!

Thanks very much, guys!

Interesting idea on the Read_Committed_Shapshot. The documentation says that 'most' applications can work with it without changes... Dun't, dun't duh... That and the issue that it uses...

Oh, and apparently I have no manners this morning. Thanks all for the suggestions!

Next time it's hung I will try dbcc freeproccache just to see if that resolves the problem. Seems like an easy way to see if that is the issue.

The relationship...

Interesting. One of my caveats here is that the sql server is at a co-lo. Not sure if I'd be allowed to install Ignite on that server but it could...

OK, well where to start with the replies... Maybe this check list...

Lavanyasri (8/7/2012)

1. Check for missing indexes

2. Do you running rebuild index\reorg ,update statistics on weekend?

3.Try to find...

Is it the ssms process or is it SQL server that keeps doing that?

=> Definitely SSMS. The SQL Server instance is pretty constant at 170 mb.

32 bit or 64...

Just figured out the page 2 thing.

No other dbs that can be removed from this server.

The server is hosted at ViaWest which would dearly like the client to...

So far I have not caught SGAM contention--so I'm holding off on that flag. I was looking at that as well but it seems to have some risks and I...

It is a machine with 2, 4-core processors so I already have tempdb files = cores. If I'm following along correctly then that would be a 'no' for adding more...

Yep, PageLatch and PFS indicated as the issue. I can certainly try creating more files. Should be fairly easy to see if there is improvement. Anyone want to take a...

Interesting. What is apparent is that no one here has the understanding to create a read-only proxy account with limited domain permissions. Your point is well taken that once there...

Thanks for the code to shred the XML. That will come in handy.

However, from my experience, the best high-volume ETL solution I've used was and an end-to-end TSQL solution, fully...