SQL Repository
  • Home
  • Articles
    • MS SQL DBA
    • SSIS
    • SSRS
    • T-SQL
  • Code Snippets
    • MS SQL DBA
    • SSIS
    • SSRS
    • T-SQL
  • Interview Questions
    • MS SQL DBA
    • SSIS
    • SSRS
    • T-SQL
  • How To
    • MS SQL DBA
    • SSIS
    • SSRS
    • T-SQL
  • Contact





Total Server Memory and Target Server Memory

On 01 Oct, 2015
MS SQL DBA
By : Charith Silva
No Comments
Views : 2561

Total server memory specifies the amount of memory the server has committed using the memory manager.  Target Server Memory Indicates the ideal amount of memory the server can consume.

Total Server Memory divided by Target Server Memory ratio should be close to 1. If the Total Server Memory value is significantly lower than the Target Server Memory value, it can mean that there’s memory pressure on the server.

Upon staring SQL Server its total memory will be low and it will grow throughout the warm-up period while SQL Server is bringing pages into its buffer pool and until it reaches a steady state. When this steady state is reached the total server memory will ideally be around the value of the target memory.

Note : Total Server Memory Performance Counter is also not the total memory used by SQL Server because it shows only the memory allocation through Buffer Pool.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE @Total as Float
DECLARE @Target as Float
DECLARE @Ratio as Float
DECLARE @Min as Float
DECLARE @Max as Float
 
SET @Total=(SELECT ROUND(((CAST([cntr_value] AS float )/1024)/1024),2)FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Memory Manager%' AND [counter_name] = 'Total Server Memory (KB)')
SET @Target=(SELECT ROUND(((CAST([cntr_value] AS float )/1024)/1024),2) FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Memory Manager%' AND [counter_name] ='Target Server Memory (KB)')
SET @Ratio=(SELECT ROUND(100.0 * ( SELECT CAST([cntr_value] AS FLOAT) FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Memory Manager%' AND [counter_name] = 'Total Server Memory (KB)' ) / ( SELECT CAST([cntr_value] AS FLOAT) FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Memory Manager%' AND [counter_name] = 'Target Server Memory (KB)') , 2))
SET @Min =(SELECT CAST(value AS float )/1024 FROM sys.configurations WHERE name ='min server memory (MB)')
SET @Max =(SELECT CAST(value AS float )/1024 FROM sys.configurations WHERE name ='max server memory (MB)')
 
SELECT @Min AS [Min server memory (GB)], @Max AS [Max server memory (GB)], @Total AS [Total Server Memory (GB) in use],@Target AS [Target Server Memory(GB) in use],ROUND(@Total/@Target,4)*100 AS [Ratio (Total/Target)]

 

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Google+ (Opens in new window)

Related



Previous Post Next Post 

About The Author

Charith Silva

Charith Silva is a Microsoft certified SQL Server developer and database administrator who was graduated at Buckinghamshire New university in the UK. His career was started in 1998, primarily into Web application development, and later diversified into database development. He has got a vast experience in SQL Server database development, Database administration and Business Intelligence development. He believes that sharing the knowledge is key to the success.


Number of Posts : 87
All Posts by : Charith Silva

Leave a Comment

Click here to cancel reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">





  • Popular
  • Recent
  • Database stuck in “Restoring” state

    13949 views
  • Find the modified date of SQL Server Agents Jobs

    5097 views
  • Script to Check TempDB Speed

    3671 views
  • Log shipping Alerts failing to send emails

    2966 views
  • PING all the Linked Servers and get a status report

    2965 views
  • Moving the tempdb database

    27 Jan, 2016
  • Script to Check TempDB Speed

    14 Jan, 2016
  • SQL Server buffer pool

    05 Nov, 2015
  • Log shipping Alerts failing to send emails

    04 Nov, 2015
  • View queries waiting for memory grant

    21 Oct, 2015

Useful links

  • Books Online for SQL Server 2012
  • Developer Reference for SQL Server 2014
  • Download SQL Server
  • Installation for SQL Server 2012
  • Microsoft Virtual Academy
  • SQL Server Online Training
  • Transact-SQL Reference
  • Tutorials for SQL Server 2012

Tags

.CSV 70-461 AdventureWorks 2012 ALL ANY CAST Chinook Database Code Snippet CONVERT CTE dataset datasource Dates DATETIME divide by zero Duplicates Exam EXCEPT expressions FORMAT IF Import Indexes INTERSECT Jobs NULLIF REBUILD Recursive CTE REORGANIZE ROW_NUMBER() Schedules Sequence SOME sp_stop_job SQL Server 2012 SQL Server Agent SSIS SSRS T-SQL Tally Table T_SQL UAC Permissions Error UNION UNION ALL

Recent Comments

  • Rudnei Silva on Log shipping Alerts failing to send emails
  • johnson Welch on Database stuck in “Restoring” state
  • Neil on Database stuck in “Restoring” state
  • Mark Gribler on MS SQL Database Administrator Interview Questions – Part 4

Google Analytics Stats

Latest Tweets:

  • 4 years ago Attended @SQLSatMcr yesterday - it was amazing! Roll on @sqlsatcambs! Won some Beats Headphones courtesy of @SQLDBApros - thanks guys! :)
  • 4 years ago Looking forward to attending @SQLSatMcr - its too far off though!!!
  • 4 years ago Simple Post: WhoIsActive SPROC: http://t.co/LZvQUaeapK
  • 5 years ago POST: Index REBUILD or Index REORGANIZE: http://t.co/h3L0N37vw4
  • 5 years ago How to Ping all Linked Servers: http://t.co/Q2QxusrKjO
  • 5 years ago For beginners - T-SQL Divide by Zero Error: http://t.co/BBhgoH5hK9

© Copyright 2015 SQL Repository. All Rights Reserved by SQL Repository