Azure SQL Database DTU Calculator per database not per server

Question

I found an amazing script online that will allow everyone of us to determine how much juice do we need on Azure before the migration:

Set-ExecutionPolicy -Scope Process -ExecutionPolicy Unrestricted -Force

$ErrorActionPreference = "Stop"
$VerbosePreference = "Continue"

cls

Write-Output "Collecting counters..."
Write-Output "Press Ctrl+C to exit."

$counters = @("\Processor(_Total)\% Processor Time", 
"\LogicalDisk(_Total)\Disk Reads/sec", 
"\LogicalDisk(_Total)\Disk Writes/sec", 
"\SQLServer:Databases(_Total)\Log Bytes Flushed/sec") 

Get-Counter -Counter $counters -SampleInterval 1 -MaxSamples 3600 | 
    Export-Counter -FileFormat csv -Path "C:\sql-perfmon-log.csv" -Force

The problem is that this script is targeting the whole server, not a specific database.

Is it possible through PowerShell or to run a T-SQL query through PowerShell and retrieve these 4 metrics each second:

Processor - % Processor Time
Logical Disk - Disk Reads/sec
Logical Disk - Disk Writes/sec
Database - Log Bytes Flushed/sec

The goal is to collect those 4 metrics for 1 database only.

David Browne - Microsoft David Browne - Microsoft · Accepted Answer · 2021-03-10T15:27:41

Those are system-wide metrics, and there is no way to scope them down to a single database.

You could use RML Utilities to capture a SQL Trace of all the traffic on the server and replay it against an instance on which you have restored only the target database.

Or you could sample the resource utilization for the sessions connected to the target database

select SYSDATETIME() sample_time,
       sum(cpu_time) cpu_time, 
       sum(total_elapsed_time) total_elapsed_time,
       sum(reads) reads,
       sum(writes) writes
from sys.dm_exec_sessions
where database_id = db_id('AdventureWorks2017')

Or create an XEvent session to record this data every time a session ends:

CREATE EVENT SESSION [aw_logout] ON SERVER 
ADD EVENT sqlserver.logout(
    WHERE ([sqlserver].[database_name]=N'AdventureWorks2017'))
ADD TARGET package0.event_file(SET filename=N'aw_session_logout')

Azure SQL Database DTU Calculator per database not per server

1 Answers