0

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?

Tyron78
  • 4,117
  • 2
  • 17
  • 32

1 Answers1

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.

Ron Dunn
  • 2,971
  • 20
  • 27
  • Thanks for the quick reply - I will give it a try. The length of our queries varies dramatically from quite short CTAS statements to very long queries with subqueries, CTEs and so on. Regulary we are using schema names, so this should be OK. – Tyron78 Feb 04 '21 at 09:02
  • 1
    One more thought, if you're a person who likes to wrap your objects in [], you should probably add some code to handle it. Either strip the brackets from the COMMAND using REPLACE(), or add one or more OR conditions to the sub query to cater for your bracket usage. – Ron Dunn Feb 04 '21 at 09:09
  • The environmet grew over the past 5 or 6 years with various developers, so I guess some used brackets and others didn't - the replace would be the better choice I guess. But thanks for the hint. – Tyron78 Feb 04 '21 at 09:14
  • Seemingly, the orphaned tables are named quite cryptically. According to MS, these tables can be remainings of drop operations and so on... seemingly they represent copies of the original table in each node - so I doubt that I can find them in the query log. However, I will check if I can find a way to identify these tables via query. – Tyron78 Feb 19 '21 at 11:51