Steve, I suppose you noticed that my previous "ouch" had a link to another thread...

Heh, Mr. Coffee, virtual machines are horribly slower than physical machines.

There's a virtualization layer between your system and the physical resources: CPU, disks and memory must be accesses through...

Ouch!!

SQL Server 2008 R2 RTM is out since May 3rd.

I installed it on my laptop and in SSMS about dialog I get:

Microsoft SQL Server Management Studio10.50.1600.1

Hope this helps

Gianluca

Great explanation, Jeff! That's one thing I wanted to ask.

It's strange to see that partition by seems to be faster than distinct. I would have expected to be equally...

GilaMonster (5/12/2010)

Views do not have cached exec...

I would code as a stored procedure, as it can cache the query plan.

It's a quite vague question, without any other requirement this is all I can suggest.

Hope this helps

Gianluca

I get:

19 seconds for the first run

16 seconds for the second run

Don't run the select * from #myhead statement, as it involves presenting results in SSMS.

Me too. That's why I hate tuning queries...

I've never done that, but, according to BOL's description of copy only backup, I think it can be done.

Great code, Jeff!!

The issue with the RECOMPILE hint is the one described here by Erland Sommarskog. Maybe I misunderstood what he meant, but I still believe there's something wrong with it.

The only downside I see in using hints is that they have statement scope, not procedure scope. If I remember right, there's an issue still not fixed in the RECOMPILE...

It makes sense. Everything I can suggest is trying to convert the code into dynamic SQL. It's fast and it doesn't cache bad plans.

You could use the ability of...

It can surely be converted in SQL2000 code.

Just get rid of CTEs and PIVOT and it will look like this:

SELECT [Art],

Avg_Days_in = SUM(CASE [Status] WHEN 'In' THEN AVG_DIFF...