Yikes. That doesn't sound good.

First, don't use Profiler on the production server. Instead, set up a server side trace. Second, use Steve's advice and put a filter on the trace...

It's 10 levels in a CASE statement (here's the page in the local Books Online). I don't know of a limit on IF statements, but I think it's reasonably safe...

Any chance of tuning the procedure or adding indexes to help it out?

Since your server is stressed, you'll want to be judicious about using this, but I'd suggest placing a trace on the server to capture the queries that are being called....

Simplest way I can think of is to capture the trace output from your production box and the use the Profiler's ability to replay traces against the test machine. Capture...

Operations Manager is a completely seperate product sold by Microsoft. It's intention is for large scale enterprise level monitoring, reporting and alerting. Here's a place to learn a bit more...

Or, depending on how you're growing (by percent is the default and the worst choice), you can run out of space before it finishes growing.

Auto-growth is a nice safety valve,...

How about Selectivity AND Usage.

Gail sums up all the reasons better than I will. You need to take both into account when designing your indexes. Simple usage is not enough...

Excellent. Well done. I'll have to see if we can use this one on our servers.

The processor issue could be that parallel execution plans are running slower. Slower transactions can lead to more deadlocks since locks are held longer and lock escalations are delayed. If...

That is something I was curious about but never pursued. Thanks for the info.

So then, would you say, there's pretty much no reason to ever use a table variable (except,...

Whoop!

Hold the phone. Did you say it goes against another database? Is that other database only performing reads or does it also have inserts & updates? And now that we're...

I know of a .22 revolver that holds 10 or 12 shots, but since most of these guys are toting Police Specials, etc., I don't think so.

I agree with the last post. You must be doing inserts, updates, deletes, somewhere in order to get deadlocks.

Are you possibly creating global temporary tables as part of the querying...

If you fixed it, post the solution so that anyone else reading the post knows how to solve it too.

Did you multiply by 60.0?