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 (5)

  1. Subramanian kaushik Gurumoorthy says:

    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

    1. That is correct. At the CMP level the PDW user temporary tables are permanent objects so all execution rules apply.

      1. Subramanian kaushik Gurumoorthy says:

        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

      2. Subramanian kaushik Gurumoorthy says:

        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

        1. 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-sql

          although 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.

Skip to main content