You can easily rebuild a heap table starting from SQL Server 2008.

ALTER TABLE [TableName] REBUILD

What is better a car or a boat? 🙂

Seriously?

For...

Try to save result of "SELECT count(*) FROM @StateAbbrTable" into true/false variable and then use it in the query. Also add OPTION(RECOMPILE). This will help to eliminate ambiguity of OR...

For option 2, MiddleName is not a key column in the index, it's only on a leaf level, therefore a server cannot perform an index seek on that column but...

Shrink the data files before rebuilding the indexes doesn't sound like a good advice

The 2nd query is wrong:

select count(*) FROM [DW_Enterprise]..E_SIP_CLAIM_PAYMENT A

INNER JOIN [SiriusPink]..CLAIM C

ON A.claim_id = C.claim_id

where A.base_claim_payment_id NOT in (

...

Default Trace doesn't capture all the event required to perform the task. If the app connects to the server and runs some very simple queries you may not see anything...

If you have source code of application you can search for connection string parameters.

From DB side you can run a SQL Profiler to see against what databases your app is...

GUID is an ID! So if you say that you need another ID please share your idea of what that ID should look like.

These 2 parameters can be particularly useful in your case:

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

---

TimeLimit

Set the time, in seconds, after which no commands are executed. By default, the time is not limited.

LockTimeout

Set the time, in...

SP returns output parameter, but you don't read it. You have to use OUTPUT clause when you run the procedure.

Put comma where? Didn't work how? Could you please provide the scripts of what you are trying to do?

Please start from getting a deadlock graph using one of the techniques described in the above links.

abright (12/4/2015)

---

Besides changing code, is there...

Blocking is not the same as deadlock. And despite the fact that one can lead to another it would...

There are tons and tons of information (including the official documentation) available online on how to detect deadlocks. To name a few:

Detecting and Ending Deadlocks

Finding SQL Server Deadlocks Using Trace...