1) different hardware?

2) different memory allocation, server settings?

3) fragmented indexes/data/os files?

4) out of date statistics?

5) bad query plan from parameter sniffing?

1) with 20GB RAM, do not use /3GB, just /PAE and enable AWE. Also consider upgrading to 64 bit sql server.

2) Colin, can't sql 2005 std use more than...

Looks like my definition (is broken now) is too restrictive - this from dictionary.com:

Said of a program or feature that is considered obsolescent and in the process of being phased...

is this 32 bit or 64 bit?

0 and 4GB would be workable for each of the 5 instances. Avoid setting min and max equal to each other. You...

In addition to what others said:

1) Can't you use JOINS instead of IN clauses? Could be more efficient.

2) Are all necessary fields (IN fields for example) indexed to provide...

1) It is a common mistake to think that join clause conditionals and where clause conditionals will equate. But there is a vast difference in where the restriction is...

Agreed on all Bob. I should have been more specific about my low-risk index stuff. I consider that to be nothing more than adding/including a column for example....

1) Appropriate testing covers the "don't fix something that is slow because you may break it".

2) Besides, many performance fixes are either adding/altering indexes, defragmentation of same, updating...

I think 6 should be removed from the list. Performance improvements are always welcome IMHO. Preventing user complaints should be a goal of all companies!

Nachi I definitely agree with the others in that we can't help you without the table structures (including indexes/keys) and sample queries. Also some information on the spread of...

One situation that 'could' cause the observed behavior is the original query was (attempting ) doing one or more larger-scale locks that kept getting delayed due to heavy activity on...

The biggest thing for me on this query is that 1690 page reads for the query ran to 27 seconds to get the output. That is just way out...

1) you can never know FOR CERTAIN that a database won't be used EVER again without searching every piece of code you have in existence - and even then you...

1) lock pages in memory has no effect on other than enterprise edition of sql server.

2) watch pages/sec counter in perf mon to see if stuff is being flushed to...

Performance analysis is a VERY deep and complex subject. The odds of you getting satisfaction for a wide-spread issue like you have is very small. I STRONGLY recommend...