由于Server 内存占用很高本想看内存使用情况,但是发现下面几个计数器的值一直为0,我对比了别的Server的计数器的值都有值,难道需要开某个设定才能打开这几个计数器吗?
在SQLServer:Memory Manager 和 SQLServer:Buffer Manager 中不是所有的计数器都显示为0,但我们想看的这几个不应为0才对!请问各位有什么建议?!
Window 2008 R2
SQL Server 2008 (10.0.4000)SELECT TOP 100 * FROM sys.dm_os_performance_counters
where object_name ='SQLServer:Memory Manager'
and counter_name in ('Target Server Memory (KB)','Total Server Memory (KB)')
order by counter_nameSELECT TOP 100 * FROM sys.dm_os_performance_counters
where object_name ='SQLServer:Buffer Manager'
and counter_name in ('Target pages','Total pages','Database pages')
order by counter_name
在SQLServer:Memory Manager 和 SQLServer:Buffer Manager 中不是所有的计数器都显示为0,但我们想看的这几个不应为0才对!请问各位有什么建议?!
Window 2008 R2
SQL Server 2008 (10.0.4000)SELECT TOP 100 * FROM sys.dm_os_performance_counters
where object_name ='SQLServer:Memory Manager'
and counter_name in ('Target Server Memory (KB)','Total Server Memory (KB)')
order by counter_nameSELECT TOP 100 * FROM sys.dm_os_performance_counters
where object_name ='SQLServer:Buffer Manager'
and counter_name in ('Target pages','Total pages','Database pages')
order by counter_name
--我查都有记录哦
object_name counter_name instance_name cntr_value cntr_type
SQLServer:Memory Manager Target Server Memory (KB) 8547936 65792
SQLServer:Memory Manager Total Server Memory (KB) 2020344 65792object_name counter_name instance_name cntr_value cntr_type
SQLServer:Buffer Manager Database pages 15053 65792
SQLServer:Buffer Manager Target pages 1068492 65792
SQLServer:Buffer Manager Total pages 252543 65792
已将 VIEW SERVER STATE 权限授予 SQLService的启动账号,并用语句确认过,但是还是没值!SELECT sp.name
FROM sys.server_principals sp
WHERE EXISTS (SELECT *
FROM sys.server_permissions perm
WHERE perm.grantee_principal_id = sp.principal_id
AND permission_name = 'VIEW SERVER STATE')
我不是MSDN上说的SELECT COUNT(*) from sys.dm_os_performance_counters
无返结果的问题
在SQLServer:Memory Manager 中这些计数器值就不为0
Lock Blocks Allocated
Lock Owner Blocks Allocated
Lock Blocks
Lock Owner Blocks
这让我联想到了另一个问题 ,我有用WMI远程抓一些Server的性能数据,但因为Server的CPU processor数大于32个,在Windows Log中一直存在EventID:2006的报错(http://support.microsoft.com/kb/2279566)
不知道2个问题间是否存在联系?从Windows Perfrmon 和 SQL 来看 ,缺失'Target pages','Total pages','Database pages'等计数器的值,是否会对SQLOS 对内存管理有影响,因为用其他工具发现在SQL占用的内存在慢慢超过SQL中设置的最大内存值 !
你说的:“SQL占用的内存在慢慢超过SQL中设置的最大内存值” ,是完全有可能的。因为max server memory指的只是buffer pool中的内存,包含了数据库页、stolen pages中的单页,而不包含stolen pages中的multi-pages。所以如果是total pages(这个就是最大服务器内存) + multi-pages的话,总的大小就完全超过了max server memory了哈
http://support.microsoft.com/kb/2279566里面说到:在windows server 2008r2计算机上且超过了32个cpu时,32位程序不能查询“服务器工作队列”计数器。里面讲到要像解决,可以考虑把应用程序升级到64位的。但是很奇怪,难道你的系统是32位的吗?