T-SQL percentage calulation

Sara 401 Reputation points

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!

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

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points

    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,

    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.

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Viorel 112.9K Reputation points

    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
    0 comments No comments