SQL Server – Odd behaviour with cached temp tables, a linked server and MSDTC (failing)

I recently encountered some strange behaviour occurring in a production system, whereby a procedure called from an application failed due to the error “unable to enlist in a distributed transaction”. ¬†However, running the procedure from SSMS worked fine,¬†and led to the procedure working again without problem from the application.


Continue reading


When to create Indexes on Temp Tables

Creating indexes on Temp Tables sometimes appear to be a contentious issue. Amongst a number of things, they prevent Temporary Table caching, as described by Paul White in his blog post on Temp Table Caching Explained.

However, if we have a Temp Table with a large volume of data, and are then going to be manipulating subsets of this data, indexes are going to be beneficial in the same way as a “normal” table: they allow SQL Server to perform seek operations instead of just table scans, which can drastically affect the performance of your queries.
Continue reading