Ian Scarlett (2/24/2011)

---

OMG, that makes me feel...

onixsoft (2/24/2011)

---

So now I have 2 choices: change Prod database to compatible mode SQL2005 and Parameterization = Forced or replace most used queries to use Stored Procedures instead dynamic queries...

glennhill (2/24/2011)

---

I started to put a spreadsheet together with my analysis - and in the process of doing that discovered some results I had forgotten that might help me. I...

Right.

SELECT A.NameA, C.NameC

FROM #TempA AS A

CROSS

APPLY (

SELECT TOP (1)

...

Code to find sessions/requests using SERIALIZABLE:

-- Sessions

SELECT *

FROM sys.dm_exec_sessions AS S

WHERE S.transaction_isolation_level = 4

;

-- Active requests

SELECT *

FROM sys.dm_exec_requests AS R

WHERE...

Alex Webber (2/24/2011)

---

Just to add, I can't find anywhere that we're using SERIALIZABLE isolation level (we have no TransactionScope usage either) but after googling around there appear to be one...

Excellent. I love it when I am wrong about that sort of thing. :laugh:

I did a term using Turbo Pascal at University too.

Well, this is what I see:

USE Sandpit

ALTER DATABASE Sandpit

SET PARAMETERIZATION SIMPLE

;

SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON

;

SET ...

The logic is easier to express in loop form, so if you need speed and elegance, I would transfer the logic to your favourite .NET language (I would use C#)...

Might be wrong about SP2, consider moving up to a fully supported version like SP4.

Odd about the database thing - have you run a CHECKDB recently?

I noticed that, but assumed it was an error in your test data.

TableA value 1 matches...

I think I have a handle on this now, but I just want to be sure you see a successful auto-param in all cases (index, no index) for this form...

I don't think you were doing it deliberately,...

Demo:

SET STATISTICS XML OFF;

SET NOCOUNT ON;

CREATE TABLE S (A INT IDENTITY PRIMARY KEY);

GO

INSERT S DEFAULT VALUES

GO 1000

BEGIN TRANSACTION

SELECT * FROM S WITH (SERIALIZABLE) WHERE A BETWEEN 501 AND 502;

SELECT ...