2

We have a production server with 500GB RAM. max server memory is set to 450GB. Whenever we checked the memory usage it's showing 450GB, but maybe SQL Server reached 450GB for some time? The memory is not released and it will be used whenever it is needed in future. So we have a free space for utilizing by SQL Server when it needs.

I need to configure an alert when SQL Server really uses 450GB of memory. If it's having FREE space among 450GB then how much it is and if the free space is used beyond 90% of available memory then I need an alert.

I can say in DB level even the SQL Server occupies DB files more than it wants and didn't release it. But I can tell to anyone like we would have unused space and it could be used by SQL Server at any time without increasing the file size.

Like that I am looking for script in Memory Level as well. I hope you are understanding my problem. My SQL Server is fully dedicated and no other applications or no other services (ssis,ssrs etc) are running. Find the below example DB space related script.

select
NAME = left(a.NAME,15),
[FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)),
[FILE_MAXSIZE_MB] = convert(decimal(12,2),round(a.maxsize/128.000,2)),[FILE_GROWTH] = convert(decimal(12,2),round(a.growth/128.000,2))
from
dbo.sysfiles a

closed as unclear what you're asking by Tom V, RolandoMySQLDBA, Michael Green, James Anderson, Philᵀᴹ May 22 '16 at 16:57

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

2

As JamesZ mentioned, the memory is intended for the SQL Server to use, particularly for the data cache. Therefore SQL Server will attempt to use everything that has been made available to the SQL Server process. (Plus some operating overhead.)

If you are concerned about reserving memory for Windows Operating System and any other processes that you run on the server you can adjust your maxservermemory to a lower number.

I have had good success with Jonathan Kehayias's "formula" that he posted here:

How Much Memory Does My SQL Server Actually Need?

The first paragraph has a good outline of how Kehayias would by default configure the Maximum Memory. Of course, this is not absolute and you will need to observe your SQL Server and determine whether more or less memory needs to be reserved.

Brent Ozar has a sanity check post at: Memory Dangerously Low or Max Memory Too High

So, you should not have a script to tell you that there is too little memory available. You should configure appropriately for what your system needs.

Brent Ozar's post for a 512 GB Server suggests:

EXEC sys.sp_configure ‘max server memory (MB)’, ‘471859’; 
RECONFIGURE;
2

Let me start by saying this is all very normal what you are seeing.

I need to configure an alert when SQL Server really uses 450GB of memory. If it's having FREE space among 450GB then how much it is and if the free space is used beyond 90% of available memory then I need an alert.

I don't see any logic in putting alert but If you want to just get information about SQL Server memory utilization you have DMV sys.dm_os_process_memory. This DMV was introduced in SQL Server 2008 so you cannot use this if you have SQL Server 2005. You can put this in SQL Server agent with condition like it If Phys_Memory_usedby_Sqlserver_MB > 450 G you call sp_send_dbmail stored proc and send mail to who ever you want. I am sure you can create that simple query. If you cannot let me know.

select
(physical_memory_in_use_kb/1024) Phys_Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Mem_allocated_By_Locked_pagesAPI_MB,
(virtual_address_space_committed_kb/1024 )Total_Memory_Used_in_MB,--RAM+Page File
process_physical_memory_low,
process_virtual_memory_low
from sys.dm_os_process_memory

Reason I say I don't see any logic is because SQL Server memory code is designed to cache as much objects and data as possible to avoid any physical I/O. This actually helps in SQL Server performing better. If you set max server memory to 450 G SQL Server can immediately use all that memory, any why not you have asked it to use it.

Also note that there are certain components, memory to which, are allocated by buffer pool but if allocated that will also be counted under SQL Server memory utilization.

  • Hi I got an output as Phys_Memory_usedby_Sqlserver_MB》maxservermemory(450339》450000) and no memory pressure as well. So I think through given script its not good to put an alert in our environment. Any as you said its a normal behaviour as sql occupies all the memory what ever we allocate to it. – Narendra May 20 '16 at 23:55
  • The script is ONLY to tell you memory utilization of SQL Server. Whether you treat it as alert or whatever its up to you. Script is correct and as I said SQL Server memory can utilize more than what it is set in max server memory. The moral is don't go for alert its how SQL Server memory behave – Shanky May 21 '16 at 5:21
  • I understood Shanky, I got something from Idera monitoring tool which we are using and it's showing that the available memory among the sqlserver used memory. But I fully committed your words that sqlserver used all the memory and it's good for I/O fast as well. finally thanks for your valueble time as well. If I get any script related to the same will post here and it will be useful to everyone who ever seeking the same. – Narendra May 21 '16 at 6:40

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