How Speed of disk refreshing too factors into refresh time

[TinyTimothy2]

I have not seen this discussed before. How can we quantify how much the disk we write to factors in speed during a refresh?
Seems like that could be a potential bottleneck.
It is not clear if there are disks, log files that need dba attention or automated jobs to keep clean

ie
SSAS
Power BI RS

[otykier]

There are no disk writes during refresh operations, as all data processing (compression, dictionary creation, etc.) happens in memory. As such, I don’t see how disk write speed could impact refresh performance.

As a general rule, when sizing hardware for SSAS, get the fastest CPU and the fastest RAM available for your budget. That’s all that matters for SSAS tabular: https://www.sqlbi.com/blog/marco/2013/02/12/hardware-sizing-guide-for-ssas-tabular/

[TinyTimothy2]

I agree CPU and RAM. However, the data is still written disk. In SSAS properties you can set where you want the data save too.
I originally thought no disk was used and everything was saved directly to memory.
If writes to disk it would seem it would seem disk has some effect on processing.
It wouldn't have an effect on utilizing the data, which resides in memory.

AI,,,,,
Yes, in SSAS Tabular, data is saved to disk during the refresh (processing) process. While Tabular is an in-memory database, it does not exclusively use memory; it simultaneously loads data into memory and stores it on disk, often using VertiPaq compression.
Here is how the process works and why it writes to disk:
Simultaneous Loading: During processing, data is read from the source, compressed, and written to disk files while being loaded into memory.
Purpose of Disk Storage: This storage on disk ensures that if the server restarts or the service is paused, the model can quickly reload the data from disk into memory without needing to re-query the source database.
Storage Location: The files are stored in the server's data directory, with a structure that includes folders for tables, columns, and dictionaries (IDF and tbl files).
Process Full vs. Data: While Process Full refreshes data and rebuilds structures, it still relies on writing this data to disk before finalizing. During this process, the model can consume up to twice its memory size because the old data is kept in memory while the new data is built.
SQLServerCentral
SQLServerCentral
+4
Key Considerations:
Restart Behavior: If a server restarts, SSAS will reload the data from the disk into memory.
Disk I/O: During large refreshes, disk I/O performance is still important, even though it is an in-memory technology, to handle the rapid writing of compressed data.

#######
Interesting that after server reboot (common task, say once a month), refreshes the memory from the disk
In our case, it takes ~ an hour after a reboot before SSAS is available to query again.
While individual refreshes can 20 mins - 5 hours.

[otykier]

Right, let me rephrase my original response: The disk writes that happen during data refresh should not be able to cause a noticeable slowdown of the refresh operation, unless your disks are very slow - like if you were using external storage or a SAN. That’s because the bottleneck of the refresh operation is the CPU, which has to find the optimal sort order and compress the data in the VertiPaq format. Sometimes, the bottleneck could also be the data source or the network, if reading large amounts of data, for example from a slow, remote SQL server.

Example: You could be reading 10 GB of uncompressed data from a remote server, but only around 1 GB of compressed data needs to be written to disk (if we assume a 10x compression rate). Writes happen during the refresh operation, not at the end of it. Which do you think is slower? Transferring 10 GB of data over the network, or writing 1 GB of data to disk?

That being said, you should try to confirm whether disk writes is indeed causing a bottleneck in your case. I’m not an expert in that area but I believe the it should be possible to track that using AS extended events, or perhaps even Process Monitoring (sys internals).