Post reply

memory consumption by SQL server instance

  • December 29, 2015 at 1:17 am

    #306573

    Hi All

    Suppose i have 16gb total memory in my machine and allocate 14gb to SQL server instance which is not the recommended setting for sure as i should be allocating more for the OS . But lets say i kept this setting , in task manager it will always show that my SQL instance is consuming more memory which is 14gb but what i want to know exactly how much my SQL instance is taking during the run time as i am sure it is not taking full memory ? I want to fetch this information to calculate something .

    Thanks in advance

  • December 29, 2015 at 2:19 am

    #1848410

    If you've told SQL it's allowed to use 14GB of memory, there's a very good chance that it's using all 14GB.

    Why are you certain it's not using the full memory allocated?

    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

  • December 29, 2015 at 2:41 am

    #1848413

    Because its a new SQL instance i have setup with hardly any activity going on except for the backup of a database which is happening ...also i would like to know the exact memory consumption ... do you have a good query for it ?

  • December 29, 2015 at 2:57 am

    #1848414

    Doesn't matter. If there's activity, then SQL will be caching data. That's what uses the most memory.

    Check the Total Server Memory counter in perfmon, but chances are, if SQL's been told it can use 14GB of memory, it's using 14GB of memory.

    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

  • December 29, 2015 at 7:34 pm

    #1848668

    Thanks Gila but you still didn't answer my question 🙂 do we have a query to check exact usage other than task manager

  • December 29, 2015 at 8:38 pm

    #1848672

    sys.dm_os_sys_info is a good place to start.

    -Eddie

    Eddie Wuerch
    MCM: SQL

  • December 30, 2015 at 2:40 am

    #1848692

    muzikfreakster (12/29/2015)

    Thanks Gila but you still didn't answer my question 🙂

    Um...

    GilaMonster (12/29/2015)

    Check the Total Server Memory counter in perfmon

    Perfmon's easier than a query, but if you insist on a query you can use the sys.dm_os_performance_counters to read the perfmon counters from within SQL.

    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

  • December 30, 2015 at 10:00 am

    #1848785

    14GB is almost certainly too much. Keep in mind that the 'max memory' limit for SQL Server is for buffer space only. SQL will take additional RAM for many other things it needs, which can be quite a bit of RAM itself (CLR, XML, etc.).

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 8 posts - 1 through 7 (of 7 total)

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