This topic contains 1 reply, has 2 voices, and was last updated by 
-
Posts
-
As I sit here watching my PowerPivot data model refresh the thought came, “I wonder which DAX functions are more CPU intensive and which are more memory intensive?”
Now, we know that PowerPivot uses the same engine to power models as does AS tabular mode. Tabular is a memory-based cube with VertiPaq providing the compression algorithm. So, performance factors will be CPU speed, amount of memory, and memory speed.
As I watch my Windows Task Manager window for the past 9 minutes while the 1,100 measures calculate in the PowerPivot window I’ve seen CPU usage spike up to 100% multiple times (12-core, 3.2ghz) and memory usage hit up to 27gb. A little later both are back down to reasonably common levels. So, at some points DAX formulas were taxing the CPUs, while at other times the memory was getting consumed. Which functions draw which computer resources?
Searching online I haven’t found much other than DAX doesn’t do multi-threading particularly well. I know date calculations, like DATEADD() or DATESBETWEEN() aren’t processed in a multi-threaded manner. In fact, I think it might have been Marco Russo that said to separate thinking between the formula engine from the storage engine. The storage engine (VertiPaq) takes advantage of multiple cores, but the formula engine relies upon a single CPU.
Does anyone have some ideas about CPU vs memory usage with DAX functions?
Cheers,
Ron
Hello Ron,
Good to see you again!
Below are some links that discuss CPU and memory optimization (and include links to other sites that continue the discussion):
https://www.sqlbi.com/articles/checklist-for-memory-optimizations-in-powerpivot-and-tabular-models/
http://www.powerpivotblog.nl/what-is-eating-up-my-memory-powerpivot-excel-edition/
http://blog.crossjoin.co.uk/2011/02/23/querying-powerpivot-dmvs-from-excel/
http://www.ssas-info.com/VidasMatelisBlog/258_powerpivot-tips-and-tricks-for-it-pros
The topic ‘DAX Functions and CPU Usage’ is closed to new replies.