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!

Comments

• Anonymous
  January 11, 2017
  As per my understanding if the memory provided for any requests is not enough , would the extra processing would be performed on the TEMPDB database making the processing of the complete query slower
  • Anonymous
    January 18, 2017
    The comment has been removed
    • Anonymous
      January 24, 2017
      Hi Tim,Thanks.What does CXPACKET wait type means , can it be improved in PDW , I tried with various sites didnt get much information related to the same for PDW ?regards,Subramanian kaushik Gurumoorthy
    • Anonymous
      January 24, 2017
      Hi Tim,Thanks.What does CXPACKET wait type means , can it be improved in PDW , I tried with various sites didnt get much information related to the same for PDW ?regards,Subramanian kaushik Gurumoorthy
      • Anonymous
        May 17, 2017
        Hi, You can find CXPACKET wait type description here:https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sqlalthough description applies to PDW, the only possible resolution for PDW would be to tune the query. Changing cost threshold or modifying degree parallelism is not a supported option.