7

I have a production box with 5 instances on.

Version: SQL Server 2014 SP3 Enterprise.

I discovered that even though the machine has ~400GB memory assigned, the combined MAX MEMORY settings on all 5 instances is less than 200GB.

The instances don't suffer from memory pressure, but as we already have that memory assigned, it's a waste not to use it.

I would like to increase it to higher value, leaving 10% for the OS (some instances will be assigned higher than others).

However, I've never done such a large increase of this setting before.

I know it’s a dynamic settings which doesn’t require a restart, however, I'm wondering on the following:

  • Should I make the increase in 2 phases (i.e increase half the amount, wait a week and then increase it again )?
  • Can something suddenly break (or make a performance degradation) if SQL server suddenly has so much more memory to play with ?

We don't have lock pages in memory setting enabled, and we don't have traceflag 834 on.

  • Your checkpoints (and backups) might take longer with larger data cache, if more pages were brought into memory. – dean May 17 at 12:31
1

I have a slightly different opinion.

If you are not experiencing memory pressure and your application is just working fine, why introduce an unknown variable (in your case - increasing sql server max memory) ? You have not tested this change, so its a risk.

If you have more bandwidth on your server, you can look into consolidating more instances on your server.

Can something suddenly break (or make a performance degradation) if SQL server suddenly has so much more memory to play with ?

Yes, and I faced a perf degradation issue with PROD having more memory and using TF 2335 fixed the issue.

you can read Paul's answer for more details but the gist is :

The potential size of the buffer pool also affects the optimizer's cost model for data access.

  • The server has 5 instance. Leaving 50 GB for OS leaves 150 GB which can be increased and can be divided into in 5 instances which gives 30 GB to each instance, do you think just 30 GB increase can cause optimizer to make crazy plans ?, I doubt it – Shanky May 18 at 6:06
  • I would not recommend setting the TF 2335, maybe only for one or two particular queries if there's no other practical solution, using QUERYTRACEON (and with a plan guide, it's easier to track this way). There is some difference between 2008 and 2014, after all. – dean May 18 at 8:09
  • @Shanky - what I said is that the size of buffer pool affects the optimizer's cost model which can lead to optimal or suboptimal plans. Now adding x amount will trigger that ? Neither you or me can tell that without testing as it depends on many factors. – Kin Shah May 18 at 17:23
  • @dean for a large buffer pool esp on 2014, we saw many query regressions. Traceflag can be used at server level or query level using query hints. Its upto OP to test and decide if there are fewer queries affected or not. So a good amount of testing is required on OP's part. In my case, I used it as query hint and when I created proper index, I did not have to use that trace flag. – Kin Shah May 18 at 17:25
  • @Kin Not many in my experience (and in my line of work, I deal with lot of enterprise customers). If the cardinality estimations are OK, and if there are proper indexes in place, this TF is (almost) never needed (in newer versions at least). I never had to set it. Some edge cases could always exist, of course. – dean May 18 at 18:41
0

You can change SQL Server max server memory anytime but when you do so SQL Server plan, data and other caches would be flushed. This means new queries hitting the SQL Server will go again though process of query plan creation which may be deciding factor some time. So if you really want to do it inform stakeholders that you want to change max server memory and do it when load is relatively very less. Please also inform people that due to cold cache few queries might take longer to execute during their first run.

Should I make the increase in 2 phases (i.e increase half the amount, wait a week and then increase it again )?

Just do it when you get approval and when load is relatively very less, there is no need to do it in phases.

Can something suddenly break (or make a performance degradation) if SQL server suddenly has so much more memory to play with ?

Things will not break but yes, since cache is flushed few queries may start taking more time to execute on their first run.

We don't have lock pages in memory setting enabled,

I suggest you do that specially when you have 5 instances running. Any instance, due to some rouge query/process, should not be allowed to hog all memory. it is better to set max limit for all SQL Server instances. Also note in some cases SQL Server can consume more than max limit. Adding LPIM requires "SQL Server restart"

Ideal situation: When you plan to add LPIM and that point change max server memory, since you are not facing any issue you have time.

  • Thanks for the fast response. The machine is virtual, so I constantly find different opinions regarding LPIM on a VM. Thanks for the note on the cache flash, it's indeed best to wait for our maintenance window and not just hit the setting change on any time. – Roni Vered May 17 at 8:12
  • For LPIM on VM, I believe if you have your VM host configured correctly leaving enough memory for various processes you should not worry about that. Please read similar answer given by me – Shanky May 17 at 8:18
  • Increasing max_server_memory will not flush neither data nor plan cache. Decreasing it will flush the plan cache, and only adjust data cache as/if needed. – dean May 17 at 12:29
  • @dean Are you sure ?. If you read This Answer it has link to MS doc which says if you use sp_configure option the caches will be flushed. – Shanky May 17 at 13:11
  • 1
    @dean You must test indeed, in mean time, please read DBCC FREEPROCCACHE (Transact-SQL) you would see changing max server memory does clear procedure cache. – Shanky May 17 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.