Post reply

SQL Server Memory Settings

  • April 11, 2016 at 11:46 pm

    #326333

    For SQL Server 2005 and above :

    SELECT @@servername as Servername,name, value

    FROM sys.configurations

    where name='min server memory (MB)'

    or name='max server memory (MB)'

    Result:

    ServernameNameValue

    ABCDEFmin server memory (MB)0

    ABCDEFmax server memory (MB)2147483647

    How can I get the results in this order:

    Servername min server memory (MB) max server memory (MB)

    ABCDEF 02147483647

    Thanks

  • April 12, 2016 at 6:48 am

    #1871364

    How about something like this:

    SELECT TOP 1 @@servername AS Servername

    ,[min server memory (MB)] = (select value from sys.configurations where name = 'min server memory (MB)')

    ,[max server memory (MB)] = (select value from sys.configurations where name = 'max server memory (MB)')

    FROM sys.configurations

    Twitter: @SQL_JGood
    Blog: sqljgood.wordpress.com/[/url]

  • April 12, 2016 at 7:04 am

    #1871370

    J Good (4/12/2016)

    How about something like this:

    SELECT TOP 1 @@servername AS Servername

    ,[min server memory (MB)] = (select value from sys.configurations where name = 'min server memory (MB)')

    ,[max server memory (MB)] = (select value from sys.configurations where name = 'max server memory (MB)')

    FROM sys.configurations

    Or just

    SELECT @@servername AS Servername

    ,[min server memory (MB)] = (select value from sys.configurations where name = 'min server memory (MB)')

    ,[max server memory (MB)] = (select value from sys.configurations where name = 'max server memory (MB)')

    The FROM in the outer query isn't needed.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 12, 2016 at 7:08 am

    #1871371

    Doh! Good point. 😎

    Twitter: @SQL_JGood
    Blog: sqljgood.wordpress.com/[/url]

  • April 12, 2016 at 7:10 am

    #1871372

    Or without subqueries:

    SELECT TOP 1 @@servername AS Servername

    ,[min server memory (MB)] = MAX(CASE WHEN name = 'min server memory (MB)' THEN value END )

    ,[max server memory (MB)] = MAX(CASE WHEN name = 'max server memory (MB)' THEN value END )

    FROM sys.configurations

    WHERE name IN('min server memory (MB)', 'max server memory (MB)')

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply