数据库内存使用

Memory Manager：Total Server Memory（KB)：SQL Server缓冲区提交的内存（指定服务器已使用内存管理器提交的内存量。），基本等于SQL使用的内存。

Memory Manager：Target Server Memory (KB)：为服务器可供SQL Server使用的内存量。一般是由SQL Server能访问到的内存量和SQL Server的配置中的Max Server Memory值中的较小值算得（指定服务器能够使用的理想内存量）。

SELECT * ,
CAST(P.cntr_value*8.0/1024.0 AS INT) AS MemoryMB
FROM sys.sysperfinfo p
WHERE p.object_name LIKE '%SQLServer:Buffer Manager%'

在SQL 缓冲区中：

--Target pages 代表服务器能获得的最大内存
--Stolen pages 代表Buffer pool中被挪用的内存
--Free pages 代表可用的内存
--Total pages 代表buffer pool获得的内存

--Database pages代表数据库已经使用的内存

SQLServer Buffer Manager: Total pages的总大小（等于Database Pages+Free Pages+Stolen Pages）。该值乘以8KB，应该等于Memory Manager：Total Server Memory(KB)的值。

SQLServer Buffer Manager：Target pages总大小。该值乘以8KB，应该等于Memory Manager：Target Server Memory (KB)的值。

SELECT * ,
CAST(P.cntr_value*8.0/1024.0 AS INT) AS MemoryMB
FROM sys.sysperfinfo p
WHERE p.object_name LIKE '%SQLServer:Buffer Manager%'
AND (p.counter_name LIKE 'Total pages%'
OR p.counter_name LIKE 'Free pages%'
OR p.counter_name LIKE 'Target pages%'
OR p.counter_name LIKE 'Stolen pages%'
OR p.counter_name LIKE 'Database pages%')

来分析一个案例：

通过上图可以发现，

--Stolen pages（代表Bufferpool中被挪用的内存），占用1263M，使用正常

--Database pages（代表数据库已经使用的内存），占用721M，使用正常

--Free pages （代表可用的内存）则占用了11299M内存，占用较高。

Freepages的作用：

1、数据库肯定会增大，为了避免再次调整内存大小，导致数据库出现问题，所以先预留内存。

2、业务繁忙并发高的话，SQLOS需要给每个用户分配连接内存和查询内存，所以需要很多的内存准备，否则会因为内存不足而释放数据缓存而导致IO升高，最终影响查询速度

3、在数据库备份和还原的时候会提升性能。

这是SQL Server 缓冲池的预期行为。默认情况下，在启动 SQLServer之后，SQLServer会根据操作系统报告的物理内存数来动态增大或缩小高速缓冲存储器的容量。只要可用物理内存大小保持在4MB到10MB之间，SQLServer 缓冲池就会继续增大（保留可用物理内存在4MB到10MB之间是为了避免操作系统因为缺少内存而频繁地换页）。如果物理可用内存变得较少的时候，则SQLServer会将一些内存释放给操作系统。

　　为了使运行在服务器上的应用程序都能达到比较满意的效果，同时也为了能给其他应用程序分配足够的内存，可以通过最大内存限制来限制SQLServer 的内存使用量。

SQL 2012时：

SELECT * ,
CAST(P.cntr_value/1024.0 AS INT) AS MemoryMB
FROM sys.sysperfinfo p
WHERE p.object_name LIKE '%SQLServer:Memory Manager%'
AND (p.counter_name LIKE 'Free Memory%'
OR p.counter_name LIKE 'Stolen Server%'
or p.counter_name LIKE 'Total server%')
SELECT * ,
CAST(P.cntr_value*8.0/1024.0 AS INT) AS MemoryMB
FROM sys.sysperfinfo p
WHERE p.object_name LIKE '%SQLServer:Buffer Manager%'
AND (p.counter_name LIKE 'Target pages%'
OR p.counter_name LIKE 'Database pages%')

SQLServer:Buffer Manager只有此两个值：

SQLServer:Buffer Manager :Database pages     

SQLServer:Buffer Manager :Target pages  

没有SQLServer Buffer Manager:Total Pages/Stolen pages/Free pages值

Memory Manager：Total Server Memory（KB)的总大小等于SQLServer Buffer Manager :Database pages x 8KB +SQLServer Memory Manager: Free Memory (KB) +SQLServer Memory Manager:Stolen Server Memory (KB)。

SQL 2014时：

SELECT * ,
CAST(P.cntr_value/1024.0 AS INT) AS MemoryMB
FROM sys.sysperfinfo p
WHERE p.object_name LIKE '%SQLServer:Memory Manager%'
AND (p.counter_name LIKE 'Free Memory%'
OR p.counter_name LIKE 'Stolen Server%'
or p.counter_name LIKE 'Database Cache%'
or p.counter_name LIKE 'Target server%'
or p.counter_name LIKE 'Total server%')

----------------------------------------------------

每个数据库内存使用：

-- Note: querying sys.dm_os_buffer_descriptors
-- requires the VIEW_SERVER_STATE permission.

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value
   FROM sys.dm_os_performance_counters 
   WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
   AND counter_name = 'Total Pages';

;WITH src AS
(
   SELECT 
       database_id, db_buffer_pages = COUNT_BIG(*)
       FROM sys.dm_os_buffer_descriptors
       --WHERE database_id BETWEEN 5 AND 32766
       GROUP BY database_id
)
SELECT
   [db_name] = CASE [database_id] WHEN 32767 
       THEN 'Resource DB' 
       ELSE DB_NAME([database_id]) END,
   db_buffer_pages,
   db_buffer_MB = db_buffer_pages / 128,
   db_buffer_percent = CONVERT(DECIMAL(6,3), 
       db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;

实际上SQL Server所使用的物理内存，可以通过如下语句查看：

SELECT ISNULL(DB_NAME(database_id),'resourcedb')AS databasename,
CAST(COUNT(row_count)*8.0/(1024.0)AS DECIMAL(28,2))AS [size(mb)]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY databasename

SQL Server性能计数器

 SQLServer：Memory Manager：Total Server Memory（KB)：SQL Server缓冲区提交的内存。不是SQL Server总的使用内存，只是Buffer Pool中的大小。

 SQLServer：Memory Manager：Target Server Memory（KB）：服务器可供SQL Server使用的内存量。一般是由SQL Server能访问到的内存量和SQL Server的sp_Configure配置中的Max Server Memory值中的较小值算得。

 SQLServer：Memory Manger：Memory Grants Pending：等待内存授权的进程总数。如果该值不为0，说明当前有用户的内存申请由于内存压力被延迟，这意味着比较严重的内存瓶颈。

 SQLServer：Buffer Manager：Buffer Cache Hit Ratio：数据从缓冲区中找到而不需要从硬盘中去取的百分比。SQL Server在运行一段时间后，该比率的变化应该很小，而且都应该在98%以上，如果在95%以下，说明有内存不足的问题。 

 SQLServer：Buffer Manager：Lazy Writes/Sec：每秒钟被惰性编辑器（Lazy writer）写入的缓冲数。当SQL Server感觉到内存压力的时候，会将最久没有使用的数据页面和执行计划从缓冲池中清理掉，做这个动作的就是Lazy Writer。

 Page Life Expectancy：页面不被引用后，在缓冲池中停留的秒数。在内存没有压力的情况下，页面会一直待在缓冲池中，Page Life Expectancy会维持在一个比较高的值，如果有内存压力时，Page Life Expectancy会下降。所以如果Page Life Expectancy不能维持在一个值上，就代表SQLServer有内存瓶颈。

 SQLServer：Buffer Manager：Database Pages ：就是Database Cache的大小。

 SQLServer：Buffer Manager：Free Pages：SQL Server中空闲可用的大小。

 SQLServer：Buffer Manager：Stolen Pages：Buffer Pool中Stolen的大小。

 SQLServer：Buffer Manager：Total Pages：Buffer Pool的总大小（等于Database Pages+Free Pages+Stolen Pages）。该值乘以8KB，应该等于Memory Manager：Total Server Memory的值。

从上面这些计数器中我们就能了解SQL Server的内存使用情况，结合前面说的系统层的计数器大概能看出是否存在内存瓶颈。

转自：

http://www.cnblogs.com/CareySon/archive/2012/08/16/HowSQLServerManageMemory.html

https://www.mssqltips.com/sqlservertip/2393/determine-sql-server-memory-use-by-database-and-object/

http://www.cnblogs.com/caspnet/archive/2011/02/21/1959539.html

转载于:https://blog.51cto.com/dayday/1671756