Your description of TF 1204 is not correct. Please revise the article. Also, I am not sure why you would want two trace flags on that both record deadlock information....

Also look up Glenn Berry SQL Server Diagnostic Scripts and pick the one for your version of SQL Server. Numerous queries in there to find the top worst offending queries...

Good choice. There are other reasons, but I would go with this one as well.

Does the table have a clustered index on it? If not it is a HEAP table. Deleting records from those (without TABLOCK) will NOT deallocate empty pages. I have seen...

Data value skew is the most likely cause. Or at least statistics that reflect that. Your direct query is giving the optimizer the exact value it needs to optimize for...

GilaMonster (12/12/2016)

---

It doesn't generate deadlocks, just facilitates them.

If your workload currently is IO-bottlenecked (quite common), then the IO latencies are slowing things down. Remove those,...

1) You know what I do when I get called in to a new client that is using an ORM? I go KAAAACCCCHHHIIIIINNNGGGG! 😀 I know there is going to...

It would REALLY help if you would give us a create table statement with sample data INSERT statements and sproc calls and their actual output and what you think the...

I'm with Wryan138 here: CASE statement (my favorite TSQL 4-letter word!!) #FTW

All-better-fixed! 🙂

Very slick! Sadly I seem to be unable to get to the SQLSharp.com website via multiple browsers. 🙁

Indianrock (12/9/2016)

---

Comment from a co-worker dba. We probably shouldn't have turned on forced parameterization years ago, since all application sql is parmameterized and only prod support add hoc work...

Yep.

An ELSE may be needed to complete the needs of the OP.

Note that I ALWAYS urge clients to...

Jacob Wilkins (12/9/2016)

---

Just in case anyone else happens upon this and is temporarily confused as I was, the accepted solutions do include a row for CustomerKey=4 in their results, which...

We need to see some code to help you. Doesn't have to be full-blown sproc, but must cover your issues completely.