July 26, 2018 at 11:48 am
Hi,
As I understand - how much memory is required by my Query depends on factors such as how large are my source tables are and whether-or-not those tables are Indexed on the Columns being JOINed (merge vs hash join).
However, is there a way to ESTIMATE the same query's runtime memory requirements (Pages and Physical memory) when it goes live? Can the development environment (very small volumes of test data) be multiplied with a factor to estimate Live/Prod memory requirements? If so, how close can we get to real runtime memory usage with this math ?
thank you
July 26, 2018 at 12:40 pm
Getting exact measures is going to be very hard because, at the root, is the query run more than once with a unique data set each time, or a mixed set? I ask because a unique set each and every time will need more memory than the possible shared data between queries. Frankly measures like this are very hard. Let's say another query uses some of the data from this query, then that data is already in memory and won't have to be loaded again. On and on.
So, how do you make a best guess? You do what you said. Measure the behavior in dev, better in a test process or processes that more accurately mirrors what real behavior will look like (dev is frequently weird, for obvious reasons). Then, with those measures you can extrapolate behaviors. Same thing as you release to production. Observe behavior of the system, watch the growth and extrapolate as needed.
Using stuff like extended events you can measure how much memory a given instance of a query uses, but change the parameters, update the statistics, and everything changes. So usually it's a matter of measuring the broad behavior, not an individual call.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply