Hmmm, it's a covering index? Change the order of the columns then. You mentioned earlier that the statistics were up to date and the index wasn't fragmented, right?

You're getting a...

Stupid question, I realize, but being the stupid guy in the stack, it's my job.

What service pack are you currently running?

I just checked, squealservercentral.com is available. Steve?

Or maybe we should set up a parody site. We could use Brandy's pirate themes...

It sounds like you've covered everything from a DBA standpoint. It's starting to sound like some type of hardware issue, drivers, the OS, something along those lines. Sorry this hasn't...

I'd say no. The only thing you can do is eliminate the lookup by including or adding columns to the nonclustered index. Are you sure you can't do an include?...

Hmmm, no performance counters is an issue, although probably not a symptom of the performance problem. That makes it more difficult to know what's going on. Have you run Profiler?...

Bummer. Then you'll need to create a compound index, at least based on my tests. The query is basically OK (since you fixed the implicit data conversion, those things will...

Start here. Then try here. Then go here. You could also do a search of articles here at SQL Server Central.

I'd check to see if you've got open connections and possibly uncommitted transactions. For it to just degrade linearly over time means that resources are being consumed and not released....

Absolutely. If you can't find a local copy of books online, you can refer to them at MSDN.

No, based on the data I tested with, 1000 rows not 7 million, the date column alone didn't supply a selective enough non-clustered index for the optimizer to choose it...

I thought I had seen that 10% number too. Here's where it's referenced.

You've effectively got a cursor going on with that query running in the SELECT statement. I'd try it like this:

SELECT a.OrderID

,a.UniqueID

...

I don't know about the DBCC thing. Sorry.

It sounds like you've got a CREATE statement inside your procedure definition. That's a problem. You'll need to remove that.

It really depends on the shop. I've worked where I was the System Administrator, DBA and Developer. Now I work for a largish company where we have 14 DBA's. I...