【案例讨论】从案例引发的对SQL Server性能调优的思考

hwtong

yanyangtian4502 发表于 2012-5-17 08:29
版主太客气了啊，那咱们今天就继续

继续继续.
多吸引点人气到MSSQL板块来.

yanyangtian4502

咱们来谈谈内存管理吧

说到内存，确实让我比较纠结，有一下几个原因：
1.内存管理涉及到的知识很多，例如windows本身的内存管理机制，sql server的内存管理机制。每一个都是要深入的学习的。就我所知的，很多的朋友做DBA的一个原因就是认为这个事情轻松，不要掌握太多的技术，不会像开发人员那样累死累活的。这种想法是不对的，充其量也顶多是一个能力一般般的，并且还只能称之为“知道什么是数据库”的人。其实，我个人也不是DBA，也是开发人员，之所以要狠心的搞数据库是是因为在性能中牵涉到了这一块（性能包：程序的性能，服务器硬件的性能，网络性能，数据库性能等等）。有一点可以说的就是：不管搞什么，都要搞精。
2.内存管理的分析涉及到很多的东西，例如DMV，性能计数器等。而这些工具产生的数据又不能相互独立的分析，需要关联起来，所以分析的方法安全是看个人的思维和知识的掌握程度。

yanyangtian4502

好吧，说了这么多，我们还是言归正传，谈谈内存方面的问题。
很多的朋友一谈到内存，第一反应和建议就是：将32位的操作系统和数据库换为64位，或者使用AWE等命令充分的使用内存，这是一个方面，确实让数据库可用的内存变多了，还有一个问题就是：数据库是否合理的使用了这些内存呢？

首先送大家两个脚本，当是体验一把：

SET TRAN ISOLATION LEVEL READ UNCOMMITTED

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

上面的脚本用来查询每一个数据库占用的内存。

我们再来看下一个，查询出表或者索引占用的内存，如下：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT

OBJECT_NAME(p.[object_id]) AS [TableName]

, (COUNT(*) * 8) / 1024 AS [Buffer size(MB)]

, ISNULL(i.name, '-- HEAP --') AS ObjectName

, COUNT(*) AS NumberOf8KPages

FROM sys.allocation_units AS a

INNER JOIN sys.dm_os_buffer_descriptors AS b

ON a.allocation_unit_id = b.allocation_unit_id

INNER JOIN sys.partitions AS p

INNER JOIN sys.indexes i ON p.index_id = i.index_id

AND p.[object_id] = i.[object_id]

ON a.container_id = p.hobt_id

WHERE b.database_id = DB_ID()

AND p.[object_id] > 100

GROUP BY p.[object_id], i.name

ORDER BY NumberOf8KPages DESC

arron刘

yanyangtian4502 发表于 2012-5-17 08:29
版主太客气了啊，那咱们今天就继续

汪洋大大好早啊。早上好啊

yanyangtian4502

arron刘 发表于 2012-5-17 08:56
汪洋大大好早啊。早上好啊

早啊 呵呵呵

hwtong

yanyangtian4502 发表于 2012-5-17 08:53
好吧，说了这么多，我们还是言归正传，谈谈内存方面的问题。
很多的朋友一谈到内存，第一反应和建议就是： ...

你这里表或者索引占用的内存是指?
因为一般只有query到一个表 才会分配一定的内存的吧.

yanyangtian4502

在这里，我就不讲基本的概念，这个任务就留给大家了。
我这里主要讲讲常常遇到的一些与内存相关的问题。
1.关于32位如何使用更多的内存问题，这个问题被讨论的很多了，这里不赘述。

下面，我介绍简要的提一下常常使用的计数器和DMV，，具体的含义，大家一定要自己去查（自学的能力就是决定了一个人的能力和以后的高度和深度），其中有些在“传说”中到现在为止已经不适用了，我会告诉大家！
计数器：
SQL Server:Buffer Manager\Buffer Cache Hit Ratio
SQL Server:Buffer Manager\Page Life Expectancy
SQL Server:Buffer Manager\Free Pages
SQL Server:Buffer Manager\Free List Stalls/sec
SQL Server:Buffer Manager\Lazy Writes/sec
SQL Server:Memory Manager\Target Server Memory (KB)
SQL Server:Memory Manager\Total Server Memory (KB)
SQL Server:Memory Manager\Memory Grants Outstanding
SQL Server:Memory Manager\Memory Grants Pending

在上面的计数器中，我要纠正一个传言：Page Life Expectancy一般不要超过300.
300这个值是微软在很多年前的文档中给出的，现在的很多的书籍和资料，也是这样写的。但是这是不对的。因为在很多年之前，也就是sql server2000的时代，4G的物理内存就认为是非常的大了，而且sql server可用的数据缓冲池最大也只有1.6GB。但是，现在，服务器以及数据库的可用的内存已经发生了很大的变换，SQL Server使用非常多的内存。所以这个数据300也要发生变换，现在这个值计算公式为：（服务器的物理内存总数/4）*300，如果一个32GB的服务器，那么这个值就是2400

yanyangtian4502

hwtong 发表于 2012-5-17 09:10
你这里表或者索引占用的内存是指?
因为一般只有query到一个表 才会分配一定的内存的吧.

一个表的数据占用的内存

yanyangtian4502

关于DMV呢，我这里也讲几个，基本都是见名知意的：
sys.dm_exec_query_memory_grants：可以用来找出哪些查询在等待着分配内存。
sys.dm_os_memory_cache_counters：给出现在使用内存的一个快照。
sys.dm_os_sys_memory：给出了现在操作系统使用内存的情况
sys.dm_os_memory_clerks：给出了与SQL Server中每个组件使用的内存的情况。


下面我们来看看“内存分页”的问题以及解决办法。
自从SQL Server 2005 SP2开始，SQL Server就会发生工作区裁剪的问题，就是说：SQL Server使用的内存在操作系统发出了内存紧急命令之后，SQL Server占用的会被一下子收回去一部分。不用说，这对SQL Server性能影响是非常的大的。

同时，也会在Window Log中也会记录“a significant part of SQL Server process memory has been paged out”。

我们可以查看得出这个问题。

发生这个问题的原因主要如下：
1. 不正确的设置：max server memory 这个选项。
2. Lock Pages in Memory没有被使用。
3.大量的操作系统缓存被用来非缓冲的I/O操作，例如，在操作系统上面copy文件之类的。
4.硬件驱动问题。

解决这个问题最快的办法就是：强制SQL Server锁住它使用的内存，这通过设置Lock Pages in Memory为true来搞定。

大家先消化一下吧，我先休息会！

hwtong

yanyangtian4502 发表于 2012-5-17 09:18
在这里，我就不讲基本的概念，这个任务就留给大家了。
我这里主要讲讲常常遇到的一些与内存相关的问题。
...

呵呵 那个值 我一直也以为阈值为300