0

I have a production sql sever with 200GB ram.

Of this 150GB is allocated to sql server (by setting max memory settings in sql server properties)

The rest is used by ssis, os, anti-virus, apps.

I want to benchmark whether the 150GB memory is sufficient for sql server because there are around 20 databases ranging from 5GB to 500GB data file size. How can I capture the memory usage info?

3
  • Why did you let 50G RAM unused? 4G RAM is enough for OS if the server is dedicated to SQL Server and no app or IIS is located in the server. Set MAX Memory to 196G to improve SQL performance. Mar 8, 2022 at 8:53
  • I want to understand how to test the ideal figure. So that I can downgrade the memory if it's not required.
    – variable
    Mar 8, 2022 at 9:09
  • @MeyssamToluie He also mentioned he has SSIS and antivirus running on the same machine. I think that's a fair starting point he did. 196 GB all for SQL Server would likely not leave enough for those other applications. 50 GB might be a little excessive too but hard to say without knowing what SSIS is doing regularly, and I think a safe enough starting point while OP gauges their memory requirements.
    – J.D.
    Mar 8, 2022 at 12:41

3 Answers 3

5

Consumers

SQL Server has two main consumers of memory. There are many others, but under normal circumstances they don't consume enough additional memory to be notable.

  • The buffer pool
  • Query memory grants

Buffer Pool

Since SQL Server doesn't work with data on disk, queries need to bring the relevant pages into the buffer pool before queries can start working on them. This is true of both reads and writes. While queries wait for data to end up in memory, they wait on PAGEIOLATCH_%%.

Usually, the bigger the disparity between data and memory there is, the more of this wait type you'll see. Sometimes indexing comes into play here, both from the perspective that you have many useless indexes, and/or are missing useful indexes. You can use sp_BlitzIndex to diagnose indexes.

EXEC sp_BlitzIndex
    @DatabaseName = 'YourDatabase',
    @Mode = 0;

Memory Grants

Every query will ask for some memory to function, but there are certain query operators that ask for additional memory to use as scratch space:

  • Sorts
  • Hashes
  • Optimized Nested Loops

When queries are fighting with each other for memory grants, they'll wait on RESOURCE_SEMAPHORE. A lot of the time this happens because of a rather cruel default in SQL Server:

  • Any query can come along and ask for 25% of your server's max server memory setting, and
  • Any group of queries can steal ~75% of your server's max server memory setting in total

You can change the first one in Enterprise Edition by using Resource Governor to change the MAX_MEMORY_PERCENT setting to a number < 25.

ALTER RESOURCE POOL 
    [default]  
WITH
(   
     MAX_MEMORY_PERCENT = 10
);  

ALTER RESOURCE GOVERNOR RECONFIGURE;  

You can also control this at the individual query level with the MAX_GRANT_PERCENT query hint.

I have a video that shows you what different types of memory contention look like, and you can smash that like button here.

Do I Need More Memory?

There are a few free scripts you can use to see what waits and memory utilization look like on your server:

Example calls for those:

EXEC sp_BlitzFirst
    @SinceStartup = 1;

EXEC dbo.sp_PressureDetector
    @what_to_check = N'memory';

EXEC sp_WhoIsActive;

Analysis

Analysis of the output of those is beyond the scope of this answer, but in general you want to look for the waits I've mentioned

  • PAGEIOLATCH_%%
  • RESOURCE_SEMAPHORE

If either one make up a significant portion of your wait stats, it's likely that you need to:

  • Add more memory
  • Adjust memory grants
  • Tune queries and indexes

For better insight into your server's performance, a solid monitoring tool is your best bet. My current favorite is SQL Sentry, despite the fact that they were acquired by SolarWinds, a subsidiary of the Sheinhardt Wig Company.

0
0

Memory is about performance. Add more memory and you improve performance. Is your performance sufficient? If yes, then you have met your "memory requirements". If not, then adding memory might improve performance.

You might of course have allocated more memory than your workload will cause your SQL Server to benefit from. Lowering max server memory and see if things are getting slower can be one way to investigate this. Jonathan has some tips for doing that here: How much memory does my SQL Server actually need?

0

There is a tool called SQL Server Database Experimentation Assistant (DEA) tool.

The main purpose of this tool is to help you evaluate a targeted version of SQL Server for a specific workload.

Try to target the same version of SQL Server you are on but with more or less RAM.

2
  • This requires some replay service? What's that?
    – variable
    Mar 8, 2022 at 11:33
  • DEA Tool provides reply service Mar 8, 2022 at 12:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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