Identifying orphaned tables in Microsoft SQL APS / PDW

Question

We are facing severe performance issues on a Microsoft APS / PDW environment. Beside other tasks such as maintaining CCIs and statistics it was suggested to check for orphaned tables. A search on Google resulted in a query, which should help identifying these table with the DMV sys.dm_db_index_usage_stats. The idea is to identify all tables, which don't have any index scans since the last reboot, which can be evaluated by sys.dm_os_sys_info. However, seemingly none of these DMVs is supported on the APS - are there any other possibilities to identify orphaned tables?

Answer 1

How long is your business cycle?

If you touch most of your tables every batch, I'd use a query like this:

select  s.name,t.name
from    sys.tables t
        join sys.schemas s on s.schema_id = t.schema_id
where not exists (
        select  1
        from    sys.dm_pdw_exec_requests r
        where   r.command like ('%' + s.name + '.' + t.name + '%')
    )
order by s.name,t.name

It won't work in the following circumstances:

• your queries exceed 4000 characters in length, you may miss a table name in the longer form of the query.
• you are in the habit of omitting the schema name and just using the table name. I this case omit the 's.name' from the sub-query.
• you run a high volume of queries, and your DMV's don't store enough workload. In this case periodically sweep and aggregate your DMV rows to a new table, excluding REQUEST_IDs that already exist.

I'll see if I can get a better (more reliable) answer, but this is the way I've done it in the past.