Today I found out that it is now possible to enable the setting optimize for ad-hoc workloads at the database level when using Azure SQL Database. Traditionally this was always set at the server level for locally based SQL Servers.
A very quick post for today, recently I have been working on some code to gather metrics around SQL Server memory, more specifically, how much memory is on your server, your total / target memory and PLE. (If you want to know more about total vs target see this link: https://blobeater.blog/2017/03/01/sql-server-target-vs-total-memory/)
Hopefully you know the relevance and importance of setting a correct value for max memory on your SQL Server. By default it will be the value 2147483647 which is not a random number but the 8th Mersenne prime! In a computing sense it is the maximum positive value for a 32-bit signed binary integer and that is big.
For this blog post I want to discuss the meaning behind SQL Server: Memory Manager\Target Server Memory (KB) and SQL Server: Memory Manager \Total memory (KB) Perfmon counters. I will mention how under different situations and configuration settings their behaviour changes.
I remember asking a question at a recent training event, the outcome? I now don’t use task manager to try and understand how much memory SQL Server is consuming. Why? Well it has the ability to lie, and as you will see, it can lie really well. (Depending on settings)
Let’s look at my environment, SQL Server is running under Process ID 5548.
Right, so I am using 58% of my server memory and looking at SQL Server it is consuming approx. 105MB? Well, it’s not. It doesn’t make any sense.
Use resource monitor to really know what is going on.
Resource monitor shows:
Why this is so inaccurate (for my case) is because of the use of Lock Pages in Memory – Task manager does not track memory that is allocated using the AllocateUserPhysicalPages() API which is what this setting does.