SQL Server Requirements For Better Direct Query Performance (Data of 1M Rows normalized tables)

[BalaKrish]

Dear All,

I have set up powerBI environment as follows

DB configuration:

SQL server express edition (AWS RDS)  - db.t3.xlarge

RAM - 16gb

Auto scalable

Datasize : 1Million rows in the normalized tables

I have the reporting needs which needs all these 1Million rows into the PowerBI report.

Now , the report is **bleep** slow. What is the issue?

 Is it the db configuration ? Do I have to change it to any other SQL server DB instances?

What is the minimum configuration thats is needed for direct query to handle 1Million rows with the refresh latency of 3secs?

[v-diye-msft]

Hi @BalaKrish 

I'd like to suggest you to optimize from the following aspects.

1. Optimize data source performance

2. Optimize model design

3. Optimize report designs

4. Convert to a Composite Model

For further information, you may refer to the article .

[BalaKrish]

Thanks for the answer !

But I am still in the phase of setting up environment and fine tuning it..

Lets say I am setting up everything perfectly from PowerBI side..In addition , I have set up all the data source necessities for the direct query performance.

My very question is : 

Can I achieve the better performance just by having the 8gb ram of AWS RDS SQL server express edition?

if not ,what is the minimum requirement of SQL server configuration for handling 1Million rows (Direct query Mode).??

[Anonymous]

Hi @BalaKrish,

Can I achieve the better performance just by having the 8gb ram of AWS RDS SQL server express edition?

if not ,what is the minimum requirement of SQL server configuration for handling 1Million rows (Direct query Mode).??

Throwing more money at it will keep working to a certain level. Either the budget runs out, or the gains don't keep adding up.

Why is DirectQuery a hard requirement for your report? This is something that I'd really like to understand better.

Especially for the set of rows you're talking about, Import should not be a real issue. If (near) real-time is an issue, then look into refreshing a data set by API, for a latency of 30(ish) minutes.

DirectQuery will typically place a heavy load on your DB, because all the single visuals will trigger at least one query to be sent to your DB. As @v-diye-msft mentioned, Composite Models could be something for you to look into as well. Keep older data in import, and then layer the recent, real-time data on top of it. 

Also, Guy in a Cube made an excellent video with the legend that is Bob Ward. They go into some of the typical issues with DQ, and say a bit more about handling it. First step should be opening up Profiler, to check what comes in from queries. If they are handled properly with indexes, partitioning, ..., then you can take the next step in figuring it out.

[BalaKrish]

Hi Bennie,

Thanks for the reply !

Direct Query mode is what needed for my requirement. My data may grow exponentialy in some months from 1M to 10M or 100M.

So , there is no way of moving to import considering the amount data to be used for analytics.

Since , I am testing with minimum resource , I want to understand what is the real deal with the SQL server expess behaviour on the directstorage mode for the PowerBI reporting.

I need strong usecase to find out why it is slower here.

I have done some analysis on the SQL server editions. looks like SQL server express/standard doesnt support DirectQuery Storage mode for corporate business DB.

Link : https://www.microsoft.com/en-us/sql-server/sql-server-2017-editions

I still need to troubleshoot and find out the the effective SQL server configuration to handle the scalable data over the period.

As one of my requirement is to slowly move to higher configurations based on the data flow.