Any Advantage or Disadvantage to size of BatchExecutionSizeInBytes

[justinkneff]

I'm curious if any testing has been done to what value BatchExecutionSizeInBytes being set to 100000 provides. I didn't see any documentation about this specific setting? Is there a max size SQL Server can accept?

[kennethchoe]

When you run the recorded sql statement, it will be chunked by the BatchExecutionSizeInBytes. Say, if your "recorded sql" is 1 MB, it will be executed in ~10 chunks so that it doesn't get too much waste on sql communication session management overhead, but also it doesn't try to digest too big of a sql at once.

I don't think there is much need to find exactly sweet spot on this value. Most optimal value wouldn't be much faster than the initial value, while the 100000 will be much faster than not using the library. But please let me know if you find it different.