内存动态管理视图(DMV):
从sys.dm_os_memory_clerks开始。
SELECT [type] ,
SUM(virtual_memory_reserved_kb) AS [VM Reserved] ,
SUM(virtual_memory_committed_kb) AS [VM Committed] ,
SUM(awe_allocated_kb) AS [AWE Allocated] ,
SUM(shared_memory_reserved_kb) AS [SM Reserved] ,
SUM(shared_memory_committed_kb) AS [SM Committed] ,
SUM(multi_pages_kb) AS [Multipage Allocator] ,
SUM(single_pages_kb) AS [SinlgePage Allocator],
SUM(virtual_memory_reserved_kb)/(CASE WHEN SUM(virtual_memory_committed_kb)=0 THEN 1 ELSE SUM(virtual_memory_committed_kb) END ) AS [Reserved/Commit],
SUM(single_pages_kb)+SUM(multi_pages_kb) AS Stolen,
SUM(virtual_memory_committed_kb)+SUM(single_pages_kb) AS [SinlgePage Allocator]
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY [type]
其中type为Memory Clerk的名称,可以知道内存的用途。
对于得出的数据:
Memoryclerk_sqlbufferpool:正常来说这个汇总值最大。
CACHESTORE_OBJECP:触发器、存储过程、函数的执行计划缓存。
CACHESTORE_SQLCP:动态T-SQL语句、预编译TSQL语句的执行计划缓存。
CACHESTORE_PHDR:缓存视图、用户自定义函数信息,帮助SQL更快生成执行计划。
CACHESTORE_XPROC:缓存扩展存储过程,sp_executesql,sp_cursor*,sp_Trace*等。
CACHESTORE_TEMPTABLES:缓存临时对象。local temp table 、global temp table 、table variable等。
CACHESTORE_CLRPROC:SQLCLR过程缓存。
CACHESTORE_EVENTS:存储Service Broker的时间和消息。
CACHESTORE_CURSORS:存储所有的游标,包括LocalTSQLcursors、Global TSQL cursor和API cursors等。
USERSTORE_TOKENPERM:保存所有用户的安全上下文及各种跟安全相关的令牌,这些缓存条目用于检查查询累积性的权限。
USERSTORE_SXC:暂时存放正在执行中的语句的PRC参数,如果参数过长,这部分内存的使用量会比较大。
内存中的数据页由哪些表格组成,各占多少?
sys.dm_os_buffer_descriptors
DECLARE @name NVARCHAR(100)
DECLARE @cmd NVARCHAR(1000)
DECLARE dbnames CURSOR
FOR
SELECT NAME
FROM master.dbo.sysdatabases
OPEN dbnames
FETCH NEXT FROM dbnames INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'select b.database_id,db=db_name(b.database_id),p.object_id,p.index_id,buffer_count=count(*) from '
+ @name + '.sys.allocation_units a, ' + @name
+ '.sys.dm_os_buffer_descriptors b, ' + @name
+ '.sys.partitions p where a.allocation_unit_id=b.allocation_unit_id and a.container_id=p.hobt_id and b.database_id=db_id('''
+ @name
+ ''')
group by b.database_id,p.object_id,p.index_id order by b.database_id,buffer_count desc '
EXEC (@cmd)
FETCH NEXT FROM dbnames INTO @name
END
CLOSE dbnames
DEALLOCATE dbnames
GO
会缓存执行计划的对象:
proc:存储过程
prepared:预定义语句
Adhoc:动态查询
ReplProc:复制筛选过程
Trigger:触发器
View:视图
Default:默认值
UsrTab:用户表
SysTab:系统表
Check:Check约束
Rule:规则
可以查看各种对象各占多少内存:
SELECT objtype ,
SUM(size_in_bytes) / 1024 AS sum_size_in_KB ,
COUNT(bucketid) AS cache_counts
FROM sys.dm_exec_cached_plans
GROUP BY objtype
分析具体存储哪些对象:
SELECT usecounts ,
refcounts ,
size_in_bytes ,
cacheobjtype ,
objtype ,
TEXT
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY objtype DESC ;
GO
--使用DMV分析SQL SERVER 启动以来做read最多的语句
--按照物理读的页面数排序,前50名
SELECT TOP 50
qs.total_physical_reads ,
qs.execution_count ,
qs.total_physical_reads / qs.execution_count AS [Avg IO] ,
SUBSTRING(qt.text, qs.statement_start_offset / 2,
( CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset ) / 2) AS query_text ,
qt.dbid ,
dbname = DB_NAME(qt.dbid) ,
qt.objectid ,
qs.sql_handle ,
qs.plan_handle
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_physical_reads DESC
--按照逻辑读的页面数排序,前50名
SELECT TOP 50
qs.total_logical_reads ,
qs.execution_count ,
qs.total_logical_reads / qs.execution_count AS [Avg IO] ,
SUBSTRING(qt.text, qs.statement_start_offset / 2,
( CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset ) / 2) AS query_text ,
qt.dbid ,
dbname = DB_NAME(qt.dbid) ,
qt.objectid ,
qs.sql_handle ,
qs.plan_handle
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_logical_reads DESC
--用DBCC强制释放部分SQL SERVER 内存缓存:
DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
--查看操作系统内存状况
SELECT total_physical_memory_kb / 1024 AS [物理内存(MB)] ,
available_physical_memory_kb / 1024 AS [可用物理内存(MB)] ,
system_cache_kb / 1024 AS [系统缓存内存总量(MB)] ,
( kernel_paged_pool_kb + kernel_nonpaged_pool_kb ) / 1024 AS [内核池内存总量(MB)] ,
total_page_file_kb / 1024 AS [操作系统报告的提交限制的大小(MB)] ,
available_page_file_kb / 1024 AS [未使用的页文件的总量(MB)] ,
system_memory_state_desc AS [内存状态说明]
FROM sys.dm_os_sys_memory
分享到:
相关推荐
SQL server从2005开始,引入了DMV和DMF来监控数据库的信息。可以用于监控服务器实例的运行状况、诊断问题和优化性能。 DMF和DMV有挺多,做为DBA应该掌握经常用到的语句。。
本文主要通过一下几个方面介绍:使用SQLDMV查找慢速查询、通过APM解决方案查询报告、SQLServer扩展事件、SQLAzure查询性能洞察等相关内容。本文来自博客园,由火龙果软件Anna编辑、推荐。SQLServer的一个重要功能是...
the bible of Sql server mointor and performance tunning
从2005以后引入了DMO(DMV+DMF)作为SQLServer管理工具,本书通过使用DMO,专注于SQLServer的性能优化。性能优化的手段有多种,但是DMO具有便捷的特性,使其在SQLServer的管理中具有不可替代并越来越重要的作用,...
Sql Server 数据库优化常用脚本,性能排查,sql server性能分析--查询死锁的sql语句,sql server性能分析--检测数据库阻塞语句
微软资深资料,SQLSERVER 性能 调优 优化 改进 微软
SQL Server内核的元数据:通过对内部的元数据的分析,我们快速而准确获取很多与SQL Server内部相关的信息,从而进行性能分析
2.SQL Server 2008 Query Performance Tuning Distilled能够帮助你完成SQLServer的性能诊断和调优,是这方面最好的书了。3.SQL Server DMVs in Action是DMV使用的最全的参考书,DBA必备。欢迎下载。
非常经典全面的数据库调优文档,不二之选
SQL Server DMVs in Action
这只是意味着一些T-SQL的语法,查询计划的行为以及一些其它方面和SQL Server 2000中行为一样(当然,如果你设置成90兼容模式则和SQL Server 2005中一样)。 在SQL Server 2008中,你可以使用ALTER DATABASE SET ...
当大家发现数据库查询性能很慢的时候,大家都会想到加索引来优化数据库查询性能,但是面对一个复杂的SQL语句,找到一个优化的索引组合对人脑来讲,真的不是一件很简单的事。 好在SQLSERVER提供了两种“自动”功能,...
从会话状态和等待事件的角度,了解数据库的运行情况,供DBA和... 等待事件是基于“事实”和“数据”的,而非“推测”,结合Oracle AWR、ASH(Sqlserver DMV、Windows计数器),我们可以把性能分析变为可能的方法论。
SQLServer的一个优点是它本身能回答几乎所有你的问题,因为SQLServer在各个DMV和DMF里存储了很多故障排除信息。另一方面这也是个缺点,因为你必须知道各个DMV/DMF,还有如何把它们解释和关联在一起。 至于你的...
sql2000,2005,2008DMV系统视图工具,性能分析的利器
SQL Monitor for Oracle,MySQL and DB2 是款免费的数据库跟踪工具,专门用来分析CPU使用率高的问题。 软件功能: 1. 监控SQL Server的进程和Job,查看当前执行的SQL/命令,并终止之。 2. 对象浏览器,跟 SQL Server...
SQL Server DMV queries are very useful to check current and historical information. They are widely used during troubleshooting and performance tuning.
IO 内存是sql server最重要的资源,数据从磁盘加载到内存,再从内存中缓存,输出到应用端,在sql server 内存初探中有介绍。在明白了sqlserver内存原理后,就能更好的分析I/O开销,从而提升数据库的整体性能。 在...