Sql-server – SQL Server – How to determine ideal memory for instance

You've got a couple of questions in here:

1) Any ideas on why the SQL Server process would not be following the limit set on the server?

Max server memory refers to the buffer pool, but not everything that the server uses memory for. For example, SSIS isn't part of the SQL Server engine - it's another application that just happens to come free in the box with SQL Server. (Same thing with SSAS and SSRS.) Max server memory has no impact on SSIS. This is one of the reasons you'll often hear recommendations that you should separate SSIS onto its own instance - especially if you're using SQL Server Enterprise in virtualization. At $7k USD per core, you're talking about $28k worth of licensing here, and you're working with something like $500 of RAM.

2) Does anyone know a good way of profiling memory usage within SQL server? We can see that page life expectancy is dropping and more memory is being allocated to the process, but is there any way to see what SQL is allocating the memory to (i.e. procedure cache, cached results, etc).

Yes, inside the SQL Server engine, you can use DMVs like sys.dm_exec_memory_clerks. If you're using the free Opserver tool from the StackExchange guys for SQL Server monitoring, look at the memory clerks listing. If not, you can start with the memory clerks query it uses.

However, outside of the SQL Server engine - like with your SSIS packages - SQL Server DMVs can't help because this is happening out of process. You'll need to use conventional systems administration tools to do process monitoring and watch which processes use RAM.

I must say this is normal and nothing to worry about. This blog tells how target server memory calculated. Please see the forumula below

Target1 = Current committed pages of SQL Server + ( Available Physical Memory - min (Total Physical Memory Pages / 20, Available Physical Memory Pages / 2))

ullAvailPageFile: The maximum amount of memory the current process can commit, in bytes. This value is equal to or smaller than the system-wide available commit value. To calculate the system-wide available commit value, call GetPerformanceInfo and subtract the value of CommitTotal from the value of CommitLimit.

If (Max Server Memory < ullAvailPageFile)

{

Target2= Max Server Memory

}

Else

{

Target2=Total Physical Memory

}

Target Server Memory (KB) =Minimum (Target1,Target2)

So if AvailablePhysicalMemory is very high (or) when MaxServermemory is low then Target Server Memory (KB) would give you the MaxServerMemory else value derived from above formula.

As per my findings using this formula even if target server memory is bit greater than total it is fine. And this is by virtue of how the value is calculated.

If target server memory is greater that total server memory it does not always signify a memory pressure. You must come out of this belief(if you have one). Like you mentioned other counters have to be taken into picture. SQl Server changes its target and total server memory values many a times and this depends on load on system. Consider a scenario where you run index rebuild for all big tables there would be flurry of activity and PLE would certainly drop increasing target memory but this does not means it is a memory pressure. SQL Server memory just adjusted itslef to current load.

In ideal world you should have RAM such that it can shold all databases in memory but this is many a times not possible.