Hi

Good posts, especially with a 'ToDo' list (even it is not complete)

But the problem is that if one can understand that type of list and what to do in each...

Results can not be cached as underlying data changes

Regarding data (buffer cache) you can not control what is cached (the minor exception is PINTABLE)

try this http://www.sqlsolutions.com/products/sql-deadlock-detector/index.html

(NOLOCK) hints are the sign of despair, usually they come from SQL 2000/7

On SQL 2005 you can use new isolation modes, like SNAPSHOT isolation

>there are a lot of full scans going on

Then why are you surprised that CPU is so high?

There is no silver bullet or settings like

SET CPU_UTILISATION LOW

SET SQL_SERVER FAST

🙂

You need...

Compare execution plans on both servers.

Then, execution time - is it for the 'cold' execution or a 'hot' execution (2nd time execution)?

I dont know what is NMS

I monitor nCPU, IO and locks using this: http://www.sqlsolutions.com/products/IO%20Activity%20Monitor/index.html

And I hate SANs

Take the script I provide and execute it several times on any database. Take the best result (it returns the elapsed time in ms)

On my old laptop...

It is useless to compare queries like this.

Duration may vary up to 100 times and more between 'hot' and 'cold' execution. Cold execution is an execution after DBCC DROPCLEANBUFFERS or...

Execution time of the same query may vary 100 times and even more. Like 1 second when data is in cache ("hot" execution) and 100 seconds in a "cold" mode,...

If it is SQL 2005, may be you should use DDL triggers/alerts, to be NOTIFIED about logins, intead of constantly monitoring them (with a probability of losing a short-lived login...

the memory you allocated is used for the buffer cache

Check other parameters of sp_configure

Save your trace into a table and filter it there. I dont trust SQl profiler filters at all.

Or filter it in SQL Trace Analyzer (google it)

In fact, using SELECT * might be dangerous in SOME cases.

But it is like GOTO in conventional languares.

Too many GOTOs are bad, but prohibiting it completely IS WRONG.

The same for...

I dont agree with you

1. Just imagine, that to browse data instead of SELECT * you have to type 50 long column names... I dont believe that Microsoft will depreciate...