How many of this 12 000 connections is active (actually executing something, not just sitting there opened)?

How many NUMA nodes that machine have? Did you messed with CPU affinity mask...

That diagnostic tool you use seems dangerous. I've seen people clogged-up their servers with third-party diagnostic tools, and also with SQL Profiler. Use lighter methods instead, like dmv's and highly...

Check if actual execution plans are different. Are those virtual machines?

Use Array function in the Value filed of the condition.

Eg:

Array("master", "model", "msdb")

That works for me.

If you make one result as persistent calculated column, and other as non-persistent calculated column, than maybe.

If you want to be sure, instead of calculated fields use trigger to calculate...

Only two reasons come...

I must admit, JohnA, You scared me a little :w00t:

My average score in exams that led me to MCITP DBA 2008 and MCITP Developer for SQL 2008 was 941 (i...

I live in Croatia. London is 1.5h of flight away, and there are acceptable flight prices.

I just started to learn for MCM certificate. Videos are really inspiring, packed with information and insights, really refreshing.

4 videos watched, 1000 to go 🙂

I hope to be ready in...

Are you sure you have no open transactions behind (DBCC OPENTRAN)? What is log_reuse_desc from sys.databases? Not sure about the names, I'm writing from the head.

These are really huge log...

I missed your truncate because the indentation misled me 🙂

Try with CHECKPOINT command every several batches, because log cannot be truncated if checkpoint did not occur (also happens automatically, but...

#tempSample is never cleared. So you throw out the same rows over and over in cross apply. Is that intentional?

You also don't need uix_tmpSampleIndex index.

Log of what db has grown? what recovery model it uses?

did you measure which statement cumulatively takes most time?

Try to avoid cross apply, use @table variable instead of #temp table...

You can do it in a single command:

CREATE PROCEDURE dbo.GetExpirationValue

@UserEmail VARCHAR(50)

AS

SELECT a.email, a.membershipexpiry,

IsExpired = CASE WHEN a.membershipexpiry < GETDATE() THEN 1 ELSE 0 END

FROM dbo.tbl_a...

Of course you backup location is on physically separated storage, not the one db is located on ? If it is on the same storage, think the worst scenario if...