How to choose optimal spark environment settings?

Hi Srisakthi ,

I have a job developed that will go through each lakehouse table and optimize, vacuum, and delta log commit each table, but haven't started running it yet. I'll give this a try and see if it makes a difference on performance.

Re: Enable vorder and optimizewrite and bin size value as 1GB to minimize no of files and optimize for read performance - almost all of the SQL runs against our bronze layer lakehouse, where the tables were created via pipeline copy activities. Is it possible to set vordering and otpmizewrite on those - I thought that was the default? And re: the 1 GB file sizes - I'm completely unsure how to set that when a copy activity is doing the loads. I definitely see output from the copy activities that says things like 8 GB copied but created 100+ files, meaning they're too small. Just not sure how to stop this from happening. p.s. we also use dynamic partitioning on the copy activity to improve performance, I suspect this also causes the files to be split up "too small" but not sure.

Re: Make sure you are using nodes required for processing your data. Adjust spark setting like the cores, executors to be used - that's the crux of my question. Is there a way to make an intelligent guess and whether I need more cores with less memory vs. less cores with more memory?

And we are using the 1.3 runtime.

I really appreciate the help - sorry for the ongoing questions. 

Scott