Dedicated DW SQL Pool Historic Storage Growth

Question

Hi,

We have a dedicated SQL pool data warehouse database. I want to find out how much it is growing each day historically. Is there a way to see the size on disk historically? At the moment I can only find the current size through SSMS of 131072.00 MB. I want to know what it is daily though, but it seems there is nothing in azure to see this.

Kind Regards

Glyn

Answer 1

Hi Glyn Thomas,

Thanks for reaching out to Microsoft Q&A.

It sounds like you're looking for a way to track the historical growth of your dedicated SQL pool data warehouse database in Azure1. While Azure doesn't provide a built-in feature specifically for historical size tracking, you can use a combination of Azure Monitor and Log Analytics to achieve this.

Here's a high-level approach:

1. Enable Diagnostic Settings: Navigate to your dedicated SQL pool in the Azure Portal, select the Diagnostic settings link, and create a new diagnostic setting1. Choose the categories Exec Requests and Request Steps.
2. Send Data to Log Analytics: Select the Log Analytics workspace you want to send the data to and save the settings.
3. Deploy Azure Monitor Workbook: Use the Azure Synapse Toolbox to deploy a workbook template that includes historical monitoring1. This will give you a graphical, interactive set of reports to help you track the size growth over time.

Alternate solution would be as below:

1. You can create an automated SQL script to run daily, querying the current size of the SQL Pool. This can be achieved by querying sys.dm_pdw_nodes_db_partition_stats for actual data space usage or sys.database_files for allocated space

   -- Query for allocated size SELECT SUM(size * 8.0 / 1024) AS Size_MB FROM sys.master_files WHERE type_desc = 'ROWS';

   Store Results in an Audit Table
   • Create a table in your SQL Pool to store daily results, capturing the date, size, and relevant metadata.

   CREATE TABLE dbo.DailyStorageUsage ( RecordDate DATE, Size_MB FLOAT ); -- Insert daily storage usage INSERT INTO dbo.DailyStorageUsage (RecordDate, Size_MB) VALUES (GETDATE(), (SELECT SUM(size * 8.0 / 1024) FROM sys.master_files WHERE type_desc = 'ROWS'));

Set Up an Automation Runbook or Logic App

• Use Azure Automation or an Azure Logic App to run this query daily. The results will be logged to your audit table automatically.

Log Analytics Integration

• To view this information outside the SQL Pool, consider logging it to Log Analytics using a PowerShell script or Azure Monitor Custom Logs. This allows easy querying and visualization with Kusto Query Language (KQL) for trend analysis.

Trend Visualization in Log Analytics or Power BI

• Once the data is in Log Analytics, you can use KQL to analyze trends or integrate it into Power BI for graphical trend analysis.

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.