Hi folks!
We're using Dedicated SQL pool (formerly SQL DW) for reporting system, mainly for Power BI reports with direct querying for datasource views.
Our current tier is the minimum serverless level - Gen2: DW100c and during the active time of workday it's usually loaded for 50-100% because of frequent read queries and inserts.
1st problem: performance issues because of "heavy" read queries.
There are some reports that can't use aggregated data because data analysts need to see all the raw records (e.g. full stats for a certain client). So each of these reports slows down the whole server for 5-10 minutes. At that time, the volume of data is not so big, but there are many joins and biz logic in views.
The easiest workaround is a read only replica of DWH and that theoretically should solve this issue, but I can't find such built-in option or any info about how to setup it.
2nd problem: costs for using.
We're closing to $1000/month and it seems too high for 100 DWUs (it's 2x-3x comparing to other production RDBS).
There is a lot of regular and frequent spikes of 100% resource/DWU usage and such high resource utilization leads to high costs for vCore-based pricing model.
Actually we aren't using any Synapse or Analysis Services features, because all the incoming data is already structured relational data (either from other relational DBs or from back-end services). Another thing is that we have only 5-7 DWH users which build their reports by importing data in Power BI. So there is no need for Synapse pool, big data handling/transforming and so on.
May be for our scenario it's better to switch to some provisioned tier ?