T-SQL percentage calulation

Question

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

Answer 1

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.

Answer 2

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