August 3, 2014 at 11:31 pm
Hi,
How do we calculate how much memory is required by SQL Server instance? Right now we have 16 GB of memory on the Server and if we check the Task Manager it shows almost all the memory is used by SQL Server. I know we cannot decide based on this. Is there any tool to check the same.
August 4, 2014 at 12:16 am
There is no tool that I know of but only references on what general guide to set your max server memory to. Glenn Berry has blog post here[/url] that includes a chart as a general guide.
As long as you are on a dedicated server for SQL Server you will generally see 1GB-3GB of RAM left for the OS, enough where it does not starve for memory and end up trying to take it from SQL Server. As well do not forget to set the minimum server memory.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
August 4, 2014 at 12:27 am
Hi,
If your SQL Server is configured to use 16GB of memory by setting the Max Server Memory configuration option to 16000 (or by leaving it to default value), it's very likely going to use all that for the buffer cache.
To determine if that is too much or too little, you need to have some sort of baseline. For this purpose look at the following SQL Server: Buffer Manager objects in Performance Monitor:
- Page Life Expectancy (PLE) to see how long pages are held in the cache.
- Page Reads/sec to see how many physical database reads you're doing.
Also have a look at the following Physical Disk object:
- Disk Reads/sec to see the amount of read I/O happening on your storage.
After you've collected this data you need to figure out what is a good PLE for your system, it's not usually the outdated recommendation of >300 that's still floating around the Net.
One approach to figure out a correct memory setting is to look at the PLE to see if it keeps growing despite the workload. If it does, then you probably have more memory configured than you'll need and everything ends up being cached. A "correct" value would probably be one where your PLE is in acceptable level and you're storage can easily keep up with the I/O.
August 4, 2014 at 2:53 am
This was removed by the editor as SPAM
August 4, 2014 at 4:45 am
As has been stated, SQL Server will use all the memory you make available to it. In order to understand if you have enough memory, I'd suggest using some of the techniques I wrote about in this article[/url].
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply