SQL Query to calculate size of each schemas in Azure data warehouse and to find last access time?

Question

Hi,

Is there any SQL query that could help me get the size of each schemas present in our Azure Data Warehouse service and also to find when was it last accessed?

I checked and found one query below which can help me find the size of each table in schema but considering I have 15K+ tables its not feasible.

-- Check space used by a specific table
DBCC PDW_SHOWSPACEUSED('Demo.Analysis');

Answer 1

Hello Suraj Pardeshi,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

Regarding your explanation and questions, you are in need of SQL Query to calculate size of each Schemas and last access time in Azure data warehouse.

In Azure Synapse Analytics (formerly known as Azure SQL Data Warehouse), there's no single built-in SQL command that directly provides the schema size and last accessed time together, but you can achieve your goals by querying system views and DMVs (Dynamic Management Views) to get the size of each schema and the last access time for an Azure Synapse Analytics environment.

Try the following sample SQL snippet:

SELECT
    s.name AS SchemaName,
    SUM(p.used_page_count * 8) / 1024 AS SizeMB
FROM
    sys.dm_pdw_nodes_db_partition_stats p
    JOIN sys.objects o ON p.object_id = o.object_id
    JOIN sys.schemas s ON o.schema_id = s.schema_id
GROUP BY
    s.name
ORDER BY
    SizeMB DESC;

The above query will calculate the size of each schema based on the sum of the sizes of the tables within each schema. https://learn.microsoft.com/en-us/sql/t-sql/views/sys-dm-pdw-nodes-db-partition-stats-views?view=sql-server-ver15 and https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-objects-transact-sql?view=sql-server-ver15 and https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-schemas-transact-sql?view=sql-server-ver15

SELECT
    s.name AS SchemaName,
    MAX(q.creation_time) AS LastAccessedTime
FROM
    sys.dm_pdw_exec_requests r
    JOIN sys.dm_pdw_sql_requests q ON r.request_id = q.request_id
    JOIN sys.objects o ON o.object_id = q.object_id
    JOIN sys.schemas s ON o.schema_id = s.schema_id
GROUP BY
    s.name
ORDER BY
    LastAccessedTime DESC;

The above will give you an idea of when a schema was last accessed by infer recent access patterns through query history. https://learn.microsoft.com/en-us/sql/t-sql/views/sys-dm-pdw-exec-requests-views?view=sql-server-ver15 and https://learn.microsoft.com/en-us/sql/t-sql/views/sys-dm-pdw-sql-requests-views?view=sql-server-ver15

NOTE: If you come across error 404 in viewing the above links, it's because of multiple topics and updates, check the appropriate topic or second to the last directory from the links. Also, you can combine the results as needed and if you need a consolidated view for reporting purposes, you will need to merge the results programmatically or using a reporting tool.

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

** Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful ** so that others in the community facing similar issues can easily find the solution.

Best Regards,

Sina Salam