Utilities

A collection of SQL utilities for a variety of metadata and administration tasks.

• Identify External Table Metadata
• Cost Management

Identify External Table Metadata

Identify an External Table’s metadata such as data sources and data formats.

USE <specific_database>;
GO

SELECT et.[name] AS TableName,
et.[location] AS TableLocation,
ef.[name] AS FileFormatName,
ef.[format_type] AS FileFormatType,
es.[name] AS DataSourceName,
es.[location] AS DataSourceLocation
FROM sys.external_tables et
INNER JOIN sys.external_file_formats ef ON ef.file_format_id = et.file_format_id
INNER JOIN sys.external_data_sources es ON es.data_source_id = et.data_source_id

Expected Output

Cost Management

Serverless SQL can be configured to limit the amount of data processed by day, week and month. This helps budget control.

Check current settings:

SELECT [name] AS DataLimitWindow,
value AS TBValue,
value_in_use AS value_in_use
FROM sys.configurations
WHERE [name] LIKE 'Data processed %';

The following syntax modified the data processed limits. In this example, up to 2TB can be processed per day, 4TB per week, and 12TB per month.

sp_set_data_processed_limit @type = N'daily', @limit_tb = 2

sp_set_data_processed_limit
 @type= N'weekly', @limit_tb = 4

sp_set_data_processed_limit @type= N'monthly',@limit_tb = 12

Check current usage versus limits:

;WITH DataUsage
AS
(
    SELECT [type] AS DataUsageWindow,
    data_processed_mb AS DataProcessedMB,
    CAST(data_processed_mb AS DECIMAL(10,3)) / 1000 AS DataProcessedGB,
    (CAST(data_processed_mb AS DECIMAL(10,3)) / 1000) / 1000 AS DataProcessedTB
    FROM sys.dm_external_data_processed
),
DataLimit
AS
(
    SELECT [name] AS DataLimitWindow,
    CASE 
        WHEN [name] LIKE '%daily%' THEN 'daily'
        WHEN [name] LIKE '%weekly%' THEN 'weekly'
        WHEN [name] LIKE '%monthly%' THEN 'monthly'
    END AS DataUsageWindow,
    value AS TBValue,
    CAST(value_in_use AS INT) AS TBValueInUse
    FROM sys.configurations
    WHERE [name] LIKE 'Data processed %'
)
SELECT DL.DataUsageWindow,
    DL.TBValueInUse,
    DU.DataProcessedMB,
    DU.DataProcessedGB,
    DU.DataProcessedTB,
    (100 / DL.TBValueInUse) * DU.DataProcessedTB AS PercentTBUsed
FROM DataLimit DL
INNER JOIN DataUsage DU ON DL.DataUsageWindow = DU.DataUsageWindow