External Temp Tables And Plan Cache Bloat

Sebastian Meine warns us about using external temporary tables in stored procedures:

When a stored procedure is compiled that is accessing an external temp table, SQL Server has no guarantee that the next time this stored procedure is called it is called from the same connection. However, if it is called from a different connection, the accessed temp table might contain significantly more (or less) data making a different execution plan preferable.

A simple way to deal with this situation is to force a recompilation every time a procedure that works with external temporary tables is executed. SQL Server is not going that route. Instead, SQL Server caches the procedure once for each connection. That can safe a significant amount of CPU resources when the procedure in question is called within a loop.

Try to avoid using external temp tables.  There are some cases in which it’s a very useful construct, but

Related Posts

Finding The Slow Query In A Procedure

Erin Stellato shows us how we can find the slowest query within a stored procedure: Figuring out exactly what causes slow performance for a stored procedure can sometimes feel like trying to unravel a ball of Clark Griswold’s Christmas lights.  It’s not uncommon to see procedures with hundreds, even thousands of lines of code.  You […]

Read More

Signing Certificates For Multi-Database Access

David Fowler shows you how to create a signed certificate which allows for cross-database access: Recently Manish Kumar asked an interesting question, what do you do if your proc accesses multiple or even all the databases on the server? So, instead of giving him a fuzzy answer in reply, I thought I’d write up exactly […]

Read More

Categories

January 2016
MTWTFSS
« Dec Feb »
 123
45678910
11121314151617
18192021222324
25262728293031