PATINDEX is a function, whereas LIKE is a predicate.

The DELETE FROM syntax is pretty odd, and I'm not sure of all of the side effects. Can you use the ANSI Standard DELETE syntax instead:

Delete tblMMStanTestScores

Where TS.TestShortName =...

Whigger,

I have an idea, this may be similar to an OSQL problem I've seen before; can you see if output from DBCC USEROPTIONS is the same in QA as it...

Russell,

sp_executesql does not wrap an additional transaction, AFAIK:

exec sp_executesql N'select @@trancount'

-- returns: 0

Jane,

Unfortunately, the answer is: It depends

I recommend you read this article by Erland Sommarskog, which covers this topic and related issues in...

There are a few things you can do.

A) I highly recommend indexing the table! It is imperative to query performance. You just need to be careful...

Quite the contrary, Frank. I really appreciate it. Thank you!

Niall,

When I run load tests, I usually start the monitor for Average Disk Queue Length, Total Processor Time, Total Memory Utilization, Page Faults, and then sometimes add a few others...

Whigger,

Does this include sp_executesql from QA?

This really makes no sense...

Are the execution plans the same?

FYI, I posted my own test results here, along with an invitation for someone to show me how to prove that PATINDEX will, indeed, outperform LIKE:

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/08/4993.aspx

Or...

UPDATE YourTable

SET YourColumn = STUFF(YourColumn, CHARINDEX(YourColumn, 'mkpdc'), 5, 'SRV01')

UPDATE YourTable

SET YourColumn = STUFF(YourColumn, 3, 5, 'SRV01')

WHERE YourColumn LIKE '\\mkpdc%'

I run load tests all the time (it's a major part of my job) and durations are generally fairly consistent -- with some decay as simultaneous connections increase -- until...

Niall,

Are you looking at other performance counters, e.g. processor time and disk IO, during your profiling? Perhaps you're maxing something out?

Actually, both are cached. Look in syscacheobjects and you'll see both executable and compiled plans after either sp_executesql or ad hoc SQL.

Whigger, can you post some code showing us...