Finance | Project Operations, Human Resources, ...

SQL Database DTU Size for BYOD - (Timeout issue with BI reporting)

(0) Share

Report

Posted on by YOG

414

Hi All,

We have our Azure SQL on S3 DTU selected, around 30 data entities (includes Customised Data entity for Sales Invoice Line Margin) export from dynamicsF&O. 95% of entities is being export once in a day with incremental push.

We are now fall under situation where 100% DTU is getting used and database perfomance is very poor. Our key reports are timing out and data entity export (especially custom data entity) is taking 9-10hours to export.

SalesInvoiceLineMargin entity is the biggest table with 2.5M rows.

MSFT recommendation

MSFT recommends minimum P2 DTU and our data entity export works ok with this too.

Requirement

To save money spending behind Azure SQL. Current S3 is £139 where as P2 is £866. Huge difference.

Questions

1. Is minimum P2 mandatory and required?

2. Do we have to increase DTU from P2 to P3 and so on.. when data increases in future (more Legal Entities go live)?

3. is there any workaround to save money due to this?

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

A.Prasanna 8,223 on at

Like (0)

Report

RE: SQL Database DTU Size for BYOD - (Timeout issue with BI reporting)

you can dynamically scale up, scale down based on demand. By scaling down the lowest possible based on your usage pattern you can save some money.
Refer https://demiliani.com/2020/06/16/auto-scaling-your-azure-sql-database-with-logic-apps/

https://www.mssqltips.com/sqlservertip/6543/auto-scale-azure-sql-db-using-azure-logic-apps/

https://channel9.msdn.com/Blogs/Azure/Azure-SQL-Database-dynamically-scale-up-or-scale-down for more information.

Thanks & Regards,

Amith

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

RE: SQL Database DTU Size for BYOD - (Timeout issue with BI reporting)

1. No it's not. As you have noticed yourself, it's working with S3. I think the Premium recommendation is there because originally only Premium tier supported Clustered Columnstore Indexes. But nowadays also standard tier from S3 supports it.

2. With higher volumes you might need higher DTU.

3. One thing that you should do is to make sure that you don't export same data over and over again. Is the incremental push working correctly for you? The total nr of records should not matter much in the export performance, instead the nr of records that are actually exported matters.

Was this reply helpful? Yes No
Suggested answer

Sergei Minozhenko 23,093 on at

Like (0)

Report

RE: SQL Database DTU Size for BYOD - (Timeout issue with BI reporting)

Hi YOG,

Premium tier has better IOPS per DTU, so it works better if you need to write (export from F&O) or read (power BI) a big amount of data. It's not mandatory, but it depends on your DB loads. If you want to save money, I would start with analyzing queries you are running in DB and trying to tune performance by new indexes first.

Was this reply helpful? Yes No