I just want to add some more stuff:

1) For SQL Server 2005 you should use sys.sql_modules. It returns just one row for each SP (not like syscomments), which makes the...

Great news that SQL Server is moving forward. I have not heard about R2.

IMHO, because of the global financial cricis, enterprises are reducing expences, so there is no rush for...

The code is nice, but it actually shows only the records from the last trace file (SQL Srv 2005 Ent , SP2 and SP3).

select * from sys.traces

- returns...

Clustered indexes causes page splits, and columns from it are included into any other index, so it adds overhead.

Maybe use all of this:

1) checkpoint

2) dbcc dropcleanbuffers

3) dbcc freeproccache

4) dbcc freesystemcache('all')

(The last one N4 probably is the same as N2 + N3).

Using tests for the whole TB database...

What about reducing total memory size available to Windows by changing boot.ini:

"MAXMEM=

Limits Windows to ignore (not use) physical memory beyond the amount indicated. The number is interpreted in megabytes. Example:...

If you want speed, try this. It minimises the number of comparissons - it should be quicker than the others.

SELECT @boo =

( CASE...