For best performance there is no "it depends" - go with bigint over uniqueidentifier. Actually, in this case you could almost certainly get away with an int, which is...

I happen to know one of the best!...

1) Funny that you call this fairly simple. That is a beast of a query, with MANY joins, MANY CPU-burning operations (CASES, hash joins, aggregates,etc).

2) There is a CONVERT_IMPLICIT...

Increasing RAM from 4 to 16GB will only help if a) you have an edition of windows/SQL Server that can support that much and b) if 32 bit you configure...

I agree with Lynn - this is sounding more and more like a service broker opportunity.

sys.dm_exec_query_plan ( plan_handle ) has a dbid field.

SQLSACT (10/25/2012)

---

Grant Fritchey (10/23/2012)

---

There is a setting called 'Optimize for Ad Hoc Workload' that could help you out. Instead of storing a plan each time a query is run, the...

You cannot interchange UDFs and sprocs. They can be similar if they both output a variable, but you cannot "join" to the tabular output of a sproc.

1) I suppose the failure of some system action could cause SQL to barf (just a supposition here)

2) SQL 2005 SP3 is unsupported by Microsoft

What about autogrowths? Perhaps you are stalling out regularly due to those on server 1 but with server 2 having more free space in tlog you aren't autogrowing (which...

I will also note you are joining on mis-matched datatypes (or doing an unnecessary convert), which is also suboptimal.

Well, the mirror has to have writes to affect the changes brought over from the tlog on the primary so I would expect actual writes to be higher than the...

1) sp_whoisactive can help you see locking waits in real time (along with a TON of other useful features - and it is FREE)

2) the most common cause of lock...

BRIAN.ALLEN (10/23/2012)

---

Good thinking. Looking at the dumps, it appears every case has the ` character. If i run the sproc statements in SSMS it doesn't trigger the exception though. So,...

Can you check all the input buffers from the error log entries to see if there is some common thread amongst the sproc parameters? Maybe a funky character is...