PDW Useful Queries: Check for existence of user temporary tables

User temporary tables in PDW/APS are treated slightly different than temporary tables in SQL Server.  They are still only viewable in the context of the session which created them, however the existence can have other impact to other sessions.  The existence of a temporary table in PDW is tracked as a distributed transaction across the appliance.  This can have appliance wide impact.  It is a best practice to explicitly drop the temporary table once it is no longer needed.  However it is not uncommon for users to leave this tables after they are done, and not disconnect their session which would also trigger a drop.  This could leave the temporary table around for days or weeks and have unintended consequences.

While the actual table can only be viewed form the context of the creating session, there is a workaround for viewing these tables.  Underneath PDW, the tables are not temporary tables but rather tables stored in the internal PDWtempdb database and also exist in tempdb on the CTL node.  Because of this, we can use the following query to check for existence of PDW user temporary tables.

SELECT * FROM   tempdb.sys.tables WHERE  NAME LIKE '%___________________%' 

This is possible because we use a common internal name with the same prefix which includes a large number of underscores.  You will see the actual table name at the end of this table name.  There is not much more data you can get from this output however.  The easiest workaround is to disconnect idle user sesisons which connected before the creation time of the temp table in the result set.  If a more prescribed solution is required, you will need to contact Microsoft support to further identify what session owns the temp table and how to disconnect it.

Enjoy!