guyinacube

Azure Synapse Serverless Cost Control query

May 9th, 2023
328
1
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 3.00 KB | None | 1 0
  1. /*how much data was processed during the current day, week, or month*/
  2. SELECT *
  3. FROM sys.dm_external_data_processed
  4.  
  5. /*Configure cost control for serverless SQL pool in T-SQL*/
  6. sp_set_data_processed_limit
  7.     @type = N'daily',
  8.     @limit_tb = 1
  9.  
  10. sp_set_data_processed_limit
  11.     @type= N'weekly',
  12.     @limit_tb = 7
  13.  
  14. sp_set_data_processed_limit
  15.     @type= N'monthly',
  16.     @limit_tb = 350
  17.  
  18. /*To see the current configuration execute the following T-SQL statement*/
  19. SELECT * FROM sys.configurations
  20. WHERE name like 'Data processed %';
  21.  
  22. /*https://github.com/microsoft/Azure_Synapse_Toolbox/blob/master/TSQL_Queries/ServerlessSql/CostPerQuery.sql
  23. Author:  Nick Salch*/
  24. SELECT
  25.     ERH.query_text as [command],
  26.     ERH.status as [status],
  27.     ERH.login_name as [login_name],
  28.     ERH.start_time as [start_time],
  29.     ERH.end_time as [end_time],
  30.     ERH.total_elapsed_time_ms as [duration_ms],
  31.     /* Data processed =  data scanned + data moved + data written */
  32.     ERH.data_processed_mb as [data_processed_MB],
  33.     /* Cost management for serverless SQL pool
  34.     The amount of data processed is rounded up to the nearest MB per query.
  35.     Each query has a minimum of 10 MB of data processed. */
  36.     CASE WHEN ERH.data_processed_mb < 10 THEN 10 ELSE ERH.data_processed_mb END as [data_pricing_MB],
  37.     cast(ERH.total_elapsed_time_ms/1000.0 as decimal(12,2)) as [duration_sec],
  38.     /*$5 per 1TB scan, minimum 10M */
  39.     cast((CASE WHEN ERH.data_processed_mb < 10 THEN 10 ELSE ERH.data_processed_mb END)*5/100000.0 as decimal(19,7))  as cost_in_$,
  40.     ERH.command as [statement]
  41. FROM sys.dm_exec_requests_history ERH
  42. ORDER BY ERH.start_time desc
  43. --order by cast((CASE WHEN ERH.data_processed_mb < 10 THEN 10 ELSE ERH.data_processed_mb END)*5/100000.0 as decimal(19,7)) desc
  44.  
  45. SELECT
  46.     CAST([end_time] AS DATE) RunTime,
  47.     COUNT(*) Queries,
  48.     AVG([data_processed_MB]) AvgDataProcessedMB,
  49.     SUM([data_processed_MB]) TotalDataProcossedMB,
  50.     SUM([cost_in_$]) TotaCostIn$,
  51.     AVG([cost_in_$]) AvgCostIn$
  52. FROM
  53. (
  54. SELECT
  55.     ERH.query_text as [command],
  56.     ERH.status as [status],
  57.     ERH.login_name as [login_name],
  58.     ERH.start_time as [start_time],
  59.     ERH.end_time as [end_time],
  60.     ERH.total_elapsed_time_ms as [duration_ms],
  61.     /* Data processed =  data scanned + data moved + data written */
  62.     ERH.data_processed_mb as [data_processed_MB],
  63.     /* Cost management for serverless SQL pool
  64.     The amount of data processed is rounded up to the nearest MB per query.
  65.     Each query has a minimum of 10 MB of data processed. */
  66.     CASE WHEN ERH.data_processed_mb < 10 THEN 10 ELSE ERH.data_processed_mb END as [data_pricing_MB],
  67.     cast(ERH.total_elapsed_time_ms/1000.0 as decimal(12,2)) as [duration_sec],
  68.     /*$5 per 1TB scan, minimum 10M */
  69.     cast((CASE WHEN ERH.data_processed_mb < 10 THEN 10 ELSE ERH.data_processed_mb END)*5/100000.0 as decimal(19,7))  as cost_in_$,
  70.     ERH.command as [statement]
  71. FROM sys.dm_exec_requests_history ERH
  72. ) X
  73. GROUP BY
  74.     CAST([end_time] AS DATE)
  75. ORDER BY    1 DESC
Tags: sql
Add Comment
Please, Sign In to add comment