I am trying to write a script to allocate SQL MAX memory, where 10% of memory to OS and remaining to SQL. line 6 ((LEN(@Total_Mem_Mb) - 3) * (-1)) - 24000) makes that calculation, but I couldn't get that working based on percentage.
Any help is much appreciated!
DECLARE @Total_Mem_Mb DECIMAL;
SELECT @Total_Mem_Mb = (physical_memory_kb / 1024)
FROM sys.dm_os_sys_info;
WHEN @Total_Mem_Mb
BETWEEN 625000 AND 725000 THEN
ROUND(@Total_Mem_Mb, (LEN(@Total_Mem_Mb) - 3) * (-1)) - 24000
Hi @Saravanaraj-6475，
Welcome to Microsoft Q&A!
Could you please let us know what does 24000 refer to in your query?
It could be better to provide the value of @Total_Mem_Mb and your expected output.
Please refer below and check whether it is helpful.
DECLARE @Total_Mem_Mb DECIMAL;
SELECT @Total_Mem_Mb = (physical_memory_kb / 1024)
FROM sys.dm_os_sys_info;
IF @Total_Mem_Mb>=625000 AND @Total_Mem_Mb<=725000
SELECT ROUND(@Total_Mem_Mb, (LEN(@Total_Mem_Mb) - 3) * (-1)) - ROUND(@Total_Mem_Mb*0.1, (LEN(@Total_Mem_Mb*0.1) - 3) * (-1))
Or using one of below to replace above:
SELECT @Total_Mem_Mb- ROUND(@Total_Mem_Mb*0.1, (LEN(@Total_Mem_Mb*0.1) - 3) * (-1))
SELECT ROUND(@Total_Mem_Mb, (LEN(@Total_Mem_Mb) - 3) * (-1)) - @Total_Mem_Mb*0.1
Best regards,
Melissa
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
To get 10%, try this script:
declare @Total_Mem_Mb decimal
select @Total_Mem_Mb = physical_memory_kb / 1024
from sys.dm_os_sys_info
declare @to_os_Mb decimal
declare @to_sql_Mb decimal
set @to_os_Mb = ceiling(@Total_Mem_Mb * 10.0/100)
set @to_sql_Mb = @Total_Mem_Mb - @to_os_Mb
select @Total_Mem_Mb as Total_Mem_Mb, @to_os_Mb as to_os_Mb, @to_sql_Mb as to_sql_Mb
13 people are following this question.
