SQL Server 2005 内存压力和 tempdb 写入问题 [英] SQL Server 2005 Memory Pressure and tempdb writes problem

问题描述

我们的生产 SQL Server 出现了一些问题.

服务器:双四核至强8 GB 内存单个 RAID 10 阵列Windows 2003 服务器 64 位SQL Server 2005 标准 64 位

机器上现在有大约 250MB 的可用 RAM.SQL Server 有大约 6GB 的 RAM，我们的监控软件显示，实际使用了 SQL Server 分配的 RAM 的一半.

我们的主数据库大约为 20GB，其中大约 12GB 以任何频率使用.我们的 tempdb 大小为 700MB.两者都位于同一个物理磁盘阵列上.

此外，使用 Filemon，我能够看到 tempdb 文件有 100 或 1000 次长度为 65536 的写入.磁盘队列长度在近 80% 的时间超过 100.

那么，这是我的问题-

1. 什么会导致所有这些写入临时数据库?我不确定我们是否一直有那么多活动，但似乎过度了，而且这些问题是最近才出现的.

2. 我应该向服务器添加更多内存吗?

3. 在高负载服务器上，tempdb 和 db 文件应该位于不同的阵列上吗?

解决方案

如果您有 SAN 或 NAS，高磁盘队列长度并不意味着您有 I/O 瓶颈，您可能需要查看其他额外的计数器.查看 SQL Server Urban Legends 讨论 了解更多详情.

1:以下操作大量使用 tempdb

• 重复创建和删除临时表(本地或全局)
• 使用 tempdb 进行存储的表变量
• 与 CURSORS 关联的工作表
• 与 ORDER BY 子句关联的工作表
• 与 GROUP BY 子句关联的工作表
• 与哈希计划相关的工作文件

这些 SQL Server 2005 功能也大量使用 tempdb:

• 行级版本控制(快照隔离)
• 在线索引重建

如其他 SO 答案中所述，请阅读 这篇文章提高 tempdb 性能的最佳实践.

2:查看服务器上的可用 RAM 量，即查看 WMI 计数器 Memory->Available Mbytes 无济于事，因为 SQL Server 会将数据页缓存在 RAM 中，因此任何运行时间足够长的数据库服务器都几乎没有可用 RAM.
在告诉您向服务器添加 RAM 是否有帮助时，您应该查看的更有意义的计数器是:
SQL Server 实例:缓冲区管理器->页面预期寿命(以秒为单位)低于 300-400 秒的值意味着页面在内存中的时间不会很长，并且数据正在不断地从磁盘读入.页面预期寿命较短的服务器将受益于额外的 RAM.
和
SQL Server 实例:缓冲区管理器->缓冲区缓存命中率这会告诉您从 RAM 读取的页面不必从磁盘读取的百分比，低于 85 的缓存命中率将意味着服务器将受益于额外的 RAM

3:是的，这里不会出错.建议将 tempdb 放在一组单独的磁盘上.在标题下查看这篇知识库文章:关于如何执行此操作的移动 tempdb 数据库.>

We are having some issues with our production SQL Server.

Server: Dual Quad Core Xeon 8 GB RAM Single RAID 10 Array Windows 2003 Server 64-bit SQL Server 2005 Standard 64-Bit

There is about 250MB of free RAM on the machine right now. SQL Server has around 6GB of RAM, and our monitoring software says that only half of the SQL Server allocated RAM is actually being used.

Our main database is approximately 20GB, with about 12GB being used with any frequency. Our tempdb is at 700MB. Both are located on the same physical disk array.

Additionally, using Filemon, I was able to see that the tempdb file had 100's or 1000's of writes of length 65536. Disk queue length was over 100 nearly 80% of the time.

So, here are my questions-

1. What would cause all those writes on the tempdb? I'm not sure if we have always had that much activity, but it seems excessive and these problems are recent.

2. Should I just add more memory to the server?

3. On high load servers, should tempdb and db files be located on separate arrays?

解决方案

A high disk queue length does not mean you have an I/O bottleneck if you have a SAN or NAS, you may want to look at other additional counters. Check out SQL Server Urban Legends discussed for more details.

1: The following operations heavily utilize tempdb

• Repeated create and drop of temporary tables (local or global)
• Table variables that use tempdb for storage purposes
• Work tables associated with CURSORS
• Work tables associated with an ORDER BY clause
• Work tables associated with an GROUP BY clause
• Work files associated with HASH PLANS

These SQL Server 2005 features also use tempdb heavily:

• row level versioning (snapshotisolation)
• online index re-building

As mentioned in other SO answers read this article on best practice for increasing tempdb performance.

2: Looking at the amount of free RAM on the server i.e. looking at the WMI counter Memory->Available Mbytes doesn't help as SQL Server will cache data pages in RAM, so any db server that's running long enough will have little free RAM.
The counters you should look at that are more meaningful in telling you if adding RAM to the server will help are:
SQL Server Instance:Buffer Manager->Page Life Expectancy (in seconds) A value below 300-400 seconds will mean that Pages are not in memory very long and data continually is being read in from disks. Servers that have a low page life expectancy will benefit from additional RAM.
and
SQL Server Instance:Buffer Manager->Buffer Cache hit Ratio This tells you the percentage of pages that were read from RAM that didn't have to incur a read from disk, a cache hit ratio lower then 85 will mean that the server will benefit from additional RAM

3: Yes, can't go wrong here. Having tempdb on a separate set of disks is recommended. Look at this KB article under the heading: Moving the tempdb database on how to do this.

这篇关于SQL Server 2005 内存压力和 tempdb 写入问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！