There's also a lot of good information here:

http://www.brentozar.com/sql/sql-server-performance-tuning/

Without knowing a lot more about how your system is configured, it would be very difficult to make suggestions. For example:

- What RAID levels are you using and how many...

Yes, but it can be messy. Depending on how far you want to take it, here are some links:

http://msdn.microsoft.com/en-us/library/cc917694.aspx

http://technet.microsoft.com/en-us/library/ms189854.aspx

The first statement creates the foreign key in an untrusted state. It does this so that the key is guaranteed to be created, even if there is bad data, like...

We see this kind of thing all the time. In our case, it's not a dramatic as in yours, but we'll have procedures that take 1min to run the first...

I agree about the uniqueidentifier probably not being the best choice, but leaving that alone, here's something that may work for you.

This doesn't read things row by row but I'm...

Could you post the table and index DDL? It would probably help give you a better answer.

Something that jumps out is that you have an OR in your select...

I was about to type nearly the same thing venoym, but you beat me to it.

One thing I would also check is whether or not it is possible to make...

Please forgive my earlier posts where I was attempting to kill a mouse with a tank. This is much simpler. I don't know why I didn't think of it before:

SELECT

...

this isn't much better, but it gets rid of the DISTINCT in the SELECT

WITH CTE

AS (

SELECT

1 AS...

A couple things:

- It's ugly

- If you have more than 32 rows in your base table, you will have to use the MAXRECURSION query hint

- If you have more than...

Normally I do too, but they were in the original example. I was more interested in people's thoughts on using CROSS APPLY instead of a CTE.

for the basic example, why not just do something like

SELECT usercount, Period, monthval

FROM (

SELECT MONTH( UserCreationDate ) AS monthval,

...