powershell - Get CPU used (Max) and CPU used (Avg) in Azure SQL Elastic Pool

Question

I want to get get "CPU used (Max)" and "CPU used (Avg)" from the Portal using a SQL Query or Powershell script. I used "sys.elastic_pool_resource_stats", but did not mentioned details.

Is there any way to do this, using SQL Query.

Below is the sample screenshot. From the below screenshot I want to get CPU used (Max): 1.94 and CPU used (Avg): 0.07. This data is for Last 1 month.

question from:https://stackoverflow.com/questions/65904922/get-cpu-used-max-and-cpu-used-avg-in-azure-sql-elastic-pool

Answer 1

There are many third-party tools or community scripts (that use T-SQL or PowerShell) that enable you to monitor performance of your database. Most of them use CPU utilization as one of the fundamental metrics. As an example, you can use Get-DbaCpuUsage command from PsDba tools PowerShell library to monitor CPU usage using the following PowerShell:

Get-DbaCpuUsage -SqlInstance sql2017

You can also use custom scripts and libraries like the Query Performance Insights library that enables you to query CPU usage:

select *
from qpi.cpu_usage;

This library also enables you to get the information about historical CPU usage per Query Store intervals (available only in SQL server 2016+ and Azure SQL):

select start_time, execution_type_desc,
    tps =  sum(count_executions)/ min(interval_mi) /60,
    [cpu %] = ROUND(100 * sum(count_executions*cpu_time_s)/ min(interval_mi) /60 /(SELECT top 1 cpu_count FROM sys.os_sys_info)/*cores*/,1)
from qpi.db_query_plan_exec_stats_history
group by start_time, execution_type_desc
order by start_time desc

In addition this library enables you to easily find top queries or query plans that consumed most of the CPU time:

select top 10 *
from qpi.db_query_exec_stats_history
order by cpu_time_ms desc;

select top 10 *
from qpi.db_query_plan_exec_stats_history
order by cpu_time_ms desc

Ref this Microsoft blog: Monitor CPU usage on SQL Server and Azure SQL.

HTH.