SQL Server Mentalist

Hi all,

After long time I have started sharing,analyzing scripts again..

I just believe on sharing knowledge rather than keeping it to myself folks..

I will also suggest you to do same and see the difference between world and you !!

Lets get back to my article , In this post I am giving all script Related to Memory usage details for SQL Server instance

In this article we are covering scripts for

1. Memory Configuration on the Server visible to Operating System
2. Buffer Pool Usage at Given Moment
3. Total Memory used by SQL Server instance from Performance Mon
4. Memory needed as per current Workload for SQL Server instance
5. Total amount of dynamic memory the server is using for maintaining connections
6. Total amount of dynamic memory the server is using for locks
7. Total amount of dynamic memory the server is using for the dynamic SQL cache
8. Total amount of dynamic memory the server is using for query optimization
9. Total amount of dynamic memory used for hash, sort and create index operations
10. Total Amount of memory consumed by cursors
11. Number of pages in the buffer pool (includes database, free, and stolen
12. Number of Data pages in the buffer pool
13. Number of Free pages in the buffer pool
14. Number of Reserved pages in the buffer pool
15. Number of Stolen pages in the buffer pool
16. Number of Plan Cache pages in the buffer pool

Don’t get into fear with this large list just enjoy script and believe me its very simple ..Use it as it is !!

Following all query script and self explanatory

DECLARE @pg_size INT, @Instancename varchar(50)

SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'

SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) 
FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'

PRINT '---------------------------------------------------------------------------
        -------------------------'

PRINT 'Memory usage details for SQL Server instance ' + @@SERVERNAME  + ' (' 
    + CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - ' +  
      SUBSTRING(@@VERSION, CHARINDEX('X',@@VERSION),4)  + ' - ' + 
      CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'

PRINT '----------------------------------------------------------------------------
------------------------'

SELECT 'Memory Configuration on the Server visible to Operating System'

SELECT physical_memory_in_bytes/1048576.0 as [Physical Memory_MB], 
        physical_memory_in_bytes/1073741824.0 as [Physical Memory_GB], 
        virtual_memory_in_bytes/1048576.0 as [Virtual Memory MB] 
FROM sys.dm_os_sys_info

SELECT 'Buffer Pool Usage at the Moment'

SELECT (bpool_committed*8)/1024.0 as BPool_Committed_MB, 
        (bpool_commit_target*8)/1024.0 as BPool_Commit_Tgt_MB,
        (bpool_visible*8)/1024.0 as BPool_Visible_MB  
FROM sys.dm_os_sys_info

SELECT 'Total Memory used by SQL Server instance from Performance  Mon '

SELECT cntr_value as Mem_KB, 
        cntr_value/1024.0 as Mem_MB, 
        (cntr_value/1048576.0) as Mem_GB 
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Total Server Memory (KB)'

SELECT 'Memory needed as per current Workload for SQL Server instance'

SELECT cntr_value as Mem_KB, 
        cntr_value/1024.0 as Mem_MB, 
        (cntr_value/1048576.0) as Mem_GB 
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Target Server Memory (KB)'

SELECT 
'Total amount of dynamic memory the server is using for maintaining connections'

SELECT cntr_value as Mem_KB, 
    cntr_value/1024.0 as Mem_MB, 
    (cntr_value/1048576.0) as Mem_GB 
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Connection Memory (KB)'

SELECT 'Total amount of dynamic memory the server is using for locks'

SELECT cntr_value as Mem_KB, 
    cntr_value/1024.0 as Mem_MB, 
    (cntr_value/1048576.0) as Mem_GB 
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Lock Memory (KB)'

SELECT 
'Total amount of dynamic memory the server is using for the dynamic SQL cache'

SELECT cntr_value as Mem_KB, 
    cntr_value/1024.0 as Mem_MB, 
    (cntr_value/1048576.0) as Mem_GB 
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'SQL Cache Memory (KB)'

SELECT 
'Total amount of dynamic memory the server is using for query optimization'

SELECT cntr_value as Mem_KB, 
    cntr_value/1024.0 as Mem_MB, 
    (cntr_value/1048576.0) as Mem_GB 
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Optimizer Memory (KB) '

SELECT 
'Total amount of dynamic memory used for hash, sort and create index operations’

SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB,
         (cntr_value/1048576.0) as Mem_GB 
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Granted Workspace Memory (KB) '

SELECT 'Total Amount of memory consumed by cursors'

SELECT cntr_value as Mem_KB, 
        cntr_value/1024.0 as Mem_MB,
         (cntr_value/1048576.0) as Mem_GB 
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Cursor memory usage' and instance_name = '_Total'

SELECT 
'Number of pages in the buffer pool (includes database, free, and stolen).'

SELECT cntr_value as [8KB_Pages], 
        (cntr_value*@pg_size)/1024.0 as Pages_in_KB, 
        (cntr_value*@pg_size)/1048576.0 as Pages_in_MB 
FROM sys.dm_os_performance_counters 
WHERE object_name= @Instancename+'Buffer Manager' 
    and counter_name = 'Total pages' 

SELECT 'Number of Data pages in the buffer pool'

SELECT cntr_value as [8KB_Pages], 
        (cntr_value*@pg_size)/1024.0 as Pages_in_KB, 
        (cntr_value*@pg_size)/1048576.0 as Pages_in_MB 
FROM sys.dm_os_performance_counters 
WHERE object_name=@Instancename+'Buffer Manager' 
    and counter_name = 'Database pages' 

SELECT 'Number of Free pages in the buffer pool'

SELECT cntr_value as [8KB_Pages], 
        (cntr_value*@pg_size)/1024.0 as Pages_in_KB, 
        (cntr_value*@pg_size)/1048576.0 as Pages_in_MB 
FROM sys.dm_os_performance_counters 
WHERE object_name=@Instancename+'Buffer Manager' 
    and counter_name = 'Free pages'

SELECT 'Number of Reserved pages in the buffer pool'

SELECT cntr_value as [8KB_Pages], 
        (cntr_value*@pg_size)/1024.0 as Pages_in_KB, 
        (cntr_value*@pg_size)/1048576.0 as Pages_in_MB 
FROM sys.dm_os_performance_counters 
WHERE object_name=@Instancename+'Buffer Manager' 
    and counter_name = 'Reserved pages'

SELECT 'Number of Stolen pages in the buffer pool'

SELECT cntr_value as [8KB_Pages], 
        (cntr_value*@pg_size)/1024.0 as Pages_in_KB, 
        (cntr_value*@pg_size)/1048576.0 as Pages_in_MB 
FROM sys.dm_os_performance_counters 
WHERE object_name=@Instancename+'Buffer Manager' 
    and counter_name = 'Stolen pages'

SELECT 'Number of Plan Cache pages in the buffer pool'

SELECT cntr_value as [8KB_Pages], 
    (cntr_value*@pg_size)/1024.0 as Pages_in_KB, 
    (cntr_value*@pg_size)/1048576.0 as Pages_in_MB 
FROM sys.dm_os_performance_counters 
WHERE object_name=@Instancename+'Plan Cache' 
    and counter_name = 'Cache Pages'  and instance_name = '_Total'

Output of above query

And after clicking or enabling following option

—————————————————————————————————-

Memory usage details for SQL Server instance OM-PC (10.50.1617.0 – X64) – Developer Edition (64-bit))

—————————————————————————————————-

————————————————————–

Memory Configuration on the Server visible to Operating System

(1 row(s) affected)

Physical Memory_MB                      Physical Memory_GB                      Virtual Memory MB

————————————— ————————————— —————————————

3887.429687500                          3.796318054199                          8388607.875000000

(1 row(s) affected)

——————————-

Buffer Pool Usage at the Moment

(1 row(s) affected)

BPool_Committed_MB                      BPool_Commit_Tgt_MB                     BPool_Visible_MB

————————————— ————————————— —————————————

84.156250                               2112.515625                             2112.515625

(1 row(s) affected)

—————————————————————

Total Memory used by SQL Server instance from Performance  Mon

(1 row(s) affected)

Mem_KB               Mem_MB                                  Mem_GB

——————– ————————————— —————————————

86176                84.156250                               0.082183837

(1 row(s) affected)

————————————————————-

Memory needed as per current Workload for SQL Server instance

(1 row(s) affected)

Mem_KB               Mem_MB                                  Mem_GB

——————– ————————————— —————————————

2163216              2112.515625                             2.063003540

(1 row(s) affected)

——————————————————————————

Total amount of dynamic memory the server is using for maintaining connections

(1 row(s) affected)

Mem_KB               Mem_MB                                  Mem_GB

——————– ————————————— —————————————

480                  0.468750                                0.000457763

(1 row(s) affected)

————————————————————

Total amount of dynamic memory the server is using for locks

(1 row(s) affected)

Mem_KB               Mem_MB                                  Mem_GB

——————– ————————————— —————————————

768                  0.750000                                0.000732421

(1 row(s) affected)

—————————————————————————-

Total amount of dynamic memory the server is using for the dynamic SQL cache

(1 row(s) affected)

Mem_KB               Mem_MB                                  Mem_GB

——————– ————————————— —————————————

1728                 1.687500                                0.001647949

(1 row(s) affected)

————————————————————————-

Total amount of dynamic memory the server is using for query optimization

(1 row(s) affected)

Mem_KB               Mem_MB                                  Mem_GB

——————– ————————————— —————————————

992                  0.968750                                0.000946044

(1 row(s) affected)

——————————————————————————-

Total amount of dynamic memory used for hash, sort and create index operations.

(1 row(s) affected)

Mem_KB               Mem_MB                                  Mem_GB

——————– ————————————— —————————————

0                    0.000000                                0.000000000

(1 row(s) affected)

——————————————

Total Amount of memory consumed by cursors

(1 row(s) affected)

Mem_KB               Mem_MB                                  Mem_GB

——————– ————————————— —————————————

0                    0.000000                                0.000000000

(1 row(s) affected)

————————————————————————-

Number of pages in the buffer pool (includes database, free, and stolen).

(1 row(s) affected)

8KB_Pages            Pages_in_KB                             Pages_in_MB

——————– ————————————— —————————————

10772                86176.000000                            84.156250000

(1 row(s) affected)

—————————————

Number of Data pages in the buffer pool

(1 row(s) affected)

8KB_Pages            Pages_in_KB                             Pages_in_MB

——————– ————————————— —————————————

4648                 37184.000000                            36.312500000

(1 row(s) affected)

—————————————

Number of Free pages in the buffer pool

(1 row(s) affected)

8KB_Pages            Pages_in_KB                             Pages_in_MB

——————– ————————————— —————————————

1168                 9344.000000                             9.125000000

(1 row(s) affected)

——————————————-

Number of Reserved pages in the buffer pool

(1 row(s) affected)

8KB_Pages            Pages_in_KB                             Pages_in_MB

——————– ————————————— —————————————

0                    0.000000                                0.000000000

(1 row(s) affected)

—————————————–

Number of Stolen pages in the buffer pool

(1 row(s) affected)

8KB_Pages            Pages_in_KB                             Pages_in_MB

——————– ————————————— —————————————

4956                 39648.000000                            38.718750000

(1 row(s) affected)

———————————————

Number of Plan Cache pages in the buffer pool

(1 row(s) affected)

8KB_Pages            Pages_in_KB                             Pages_in_MB

——————– ————————————— —————————————

1898                 15184.000000                            14.828125000

(1 row(s) affected)

Hope this Helps !!!!

Thanks for visiting my blog !!

Hope you will like this script on How To find list of SQL Server Instances Installed on a machine By Query

If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog .

If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog

Where todays links are

Link Resource Website