To start...

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Can you post the deadlock graph? (you can get it out of the system health extended events session)

Also, https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/

Not sure about the exists, but it doesn't look like a loop is needed at all...

CREATE TABLE #RawData_Import

(ID int identity (1,1),

Code nvarchar(20),

TrackingReference bigint)

CREATE TABLE #UniqueCodes

(Code nvarchar(20),

TrackingReference bigint)

INSERT INTO #RawData_Import VALUES...

The first thing would be what the point of the partitioning is

CREATE TABLE #Table1 (col1 VARCHAR(10))

IF NOT EXISTS

(SELECT 1 FROM #Table1 WHERE col1 = 'Test')

BEGIN

INSERT INTO #Table1 (col1) VALUES ('Test')

END

Looks fine, works as...

You have one or more databases on the instance with a - in their name?

Michael Valentine Jones (7/3/2014)

---

GilaMonster (7/3/2014)

---

Do note that using functions on the columns like that prevents any index seek for that predicate.

If that column is a date, then:

SELECT

dbclaimcnt, dbclaimentered

FROM

EPSClaimsUOA

WHERE

dbclaimentered...

Just to put things in perspective and explain where that myth comes from...

During the development of SQL Server 7, a particular piece of reference hardware in the MS lab was...

If that's in the Accidental DBA book, I'll have to have words with the technical editor (oh wait, that was me). That's an error

No, they're not. The costs are not...

Identify the query being run, run it in a representative test environment, identify the slow parts. Tune the query to be faster.

Could be any number of reasons why it times...

Neither cost threshold nor subtree costs are measured in minutes. They're a unit-less number that only makes sense when compared to another cost.

A query of cost 14 should be faster...

Well firstly start by fixing that silly threshold value. 300 is insanely low for a sustained value for PLE, unless you still have a server with < 4GB memory

Chapter 4:...

Add User1 as a database user on database B and grant them update rights on the table in question, then just UPDATE DatabaseB.dbo.TableB ....

Do note that using functions on the columns like that prevents any index seek for that predicate.

If that column is a date, then:

SELECT

dbclaimcnt, dbclaimentered

FROM

EPSClaimsUOA

WHERE

dbclaimentered >= '2014-01-01' and dbclaimentered...

The WAITFOR wait type means that the session is running the WAITFOR statement. Hence, if you're seeing that wait, the app is running that statement. Look through that app's code...

I'd use a filtered unique index to enforce that.

CREATE TABLE SomeTable (

Version INT

IsActive BIT NOT NULL

)

CREATE UNIQUE INDEX idx_SomeTableVersion ON SomeTable (Version)

WHERE (IsActive = 1)