Calculating DTU's for Azure SQL Database

Question

Calculating DTU's for Azure SQL Database

gana20m

SSCrazy

Points: 2448
More actions
February 13, 2018 at 9:20 pm

#403113

Comments posted to this topic are about the item Calculating DTU's for Azure SQL Database
Regards,Ganapathi varma, MCSAEmail: gana20m@gmail.com

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

DrMDodd Valued Member Points: 57 More actions · Answer 1

gana20m - Tuesday, February 13, 2018 9:20 PM
Comments posted to this topic are about the item Calculating DTU's for Azure SQL Database

Good morning, I have an existing Azure Database on the S0 Tiered plan, however I believe that this database is not correctly spec'ed. Is there any way I can get the measurements you talk about in this blog from Azure itself. Regards,

Martin

Johan Bijnens SSC Guru Points: 134959 More actions · Answer 2

I liked the article until I read the content of the DTU calculation webpage.
At least the first 2 paragraphs are yours.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

BLOB EATER SSChampion Points: 14604 More actions · Answer 3

I am sure you have the up time SLA wrong, should it 99.99% ?

BLOB EATER SSChampion Points: 14604 More actions · Answer 4

DrMDodd - Wednesday, February 14, 2018 2:42 PM
gana20m - Tuesday, February 13, 2018 9:20 PM
Comments posted to this topic are about the item Calculating DTU's for Azure SQL Database
Good morning, I have an existing Azure Database on the S0 Tiered plan, however I believe that this database is not correctly spec'ed. Is there any way I can get the measurements you talk about in this blog from Azure itself. Regards,
Martin

Dr Dodd - those perfmon counters are from a local SQL Server. S0 is quite low powered ( IMO ) you probably will need to scale up. If this database has come from a local SQL Server then run the tool against that with a representative workload.

francesco.mantovani SSC Eights! Points: 839 More actions · Answer 5

Is the website http://dtucalculator.azurewebsites.net/ still a valid reference of Azure DTU calculation?

I mean the website was created in 2015 and we are now in 2021.

Also, I've created a query to use in order to withdraw information about the DTU per database:

WITH DB_CPU_Stats
AS
( SELECT
DatabaseID
,ISNULL(DB_NAME(DatabaseID), CASE DatabaseID
WHEN 32767 THEN 'Internal ResourceDB'
ELSE CONVERT(VARCHAR(255), DatabaseID)
END) AS [DatabaseName]
,SUM(total_worker_time) AS [CPU Time Ms]
,SUM(total_logical_reads) AS [Logical Reads]
,SUM(total_logical_writes) AS [Logical Writes]
,SUM(total_logical_reads + total_logical_writes) AS [Logical IO]
,SUM(total_physical_reads) AS [Physical Reads]
,SUM(total_elapsed_time) AS [Duration MicroSec]
,SUM(total_clr_time) AS [CLR Time MicroSec]
,SUM(total_rows) AS [Rows Returned]
,SUM(execution_count) AS [Execution Count]
,COUNT(*) 'Plan Count'

FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (SELECT
CONVERT(INT, value) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid') AS F_DB
GROUP BY DatabaseID)
SELECT
ROW_NUMBER() OVER (ORDER BY [CPU Time Ms] DESC) AS [Rank CPU]
,DatabaseName
,[Logical Reads]
,[Logical Writes]
,[CPU Time Ms]
,[CPU Time Ms] / 1000 [CPU Time Sec]
,[Duration MicroSec]
,[Duration MicroSec] / 1000000 [Duration Sec]
FROM DB_CPU_Stats
ORDER BY [Rank CPU]
OPTION (RECOMPILE);

But I'm uncertain about the Processor – % Processor Time: is that calculate in seconds or milliseconds?