SQL Server 2005内存压力和tempdb写入问题
我们的生产 SQL Server 遇到一些问题。
服务器：双四核至强 8GB 内存 单个 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。
所以，这是我的问题 -
什么会导致 tempdb 上的所有这些写入？ 我不确定我们是否一直都有那么多活动，但似乎过多了，而且这些问题是最近才出现的。
我应该向服务器添加更多内存吗？
在高负载服务器上，tempdb 和 db 文件是否应该位于单独的阵列上？
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-
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.
Should I just add more memory to the server?
On high load servers, should tempdb and db files be located on separate arrays?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
如果您有 SAN 或 NAS，磁盘队列长度高并不意味着存在 I/O 瓶颈，您可能需要查看其他附加计数器。 查看讨论的 SQL Server Urban Legends 了解更多详情。
1：以下操作大量使用 tempdb
这些 SQL Server 2005 功能也大量使用 tempdb：
正如其他 SO 答案中提到的，请阅读 这篇文章介绍了提高 tempdb 性能的最佳实践。
2：查看服务器上的可用 RAM 量，即查看 WMI 计数器 Memory->Available Mbytes 没有帮助，因为 SQL Server 将在 RAM 中缓存数据页，因此任何运行足够长的数据库服务器将几乎没有可用空间内存。
您应该查看的计数器更有意义，可以告诉您向服务器添加 RAM 是否有帮助：
SQL Server 实例：缓冲区管理器 -> 页面预期寿命（以秒为单位）
低于 300-400 秒的值意味着页面在内存中停留的时间不会很长，并且会不断从磁盘读取数据。 页面预期寿命较短的服务器将受益于额外的 RAM。
和
SQL Server实例：缓冲区管理器->缓冲区缓存命中率
这告诉您从 RAM 读取但不必从磁盘读取的页面的百分比，缓存命中率低于 85 将意味着服务器将受益于额外的 RAM
3：是的，这里不会出错。 建议将 tempdb 放在一组单独的磁盘上。 请参阅标题为“移动 tempdb 数据库”的此知识库文章，了解如何执行此操作。
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
These SQL Server 2005 features also use tempdb heavily:
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.
是的，高负载服务器上的建议是将 TempDB 放在与用户数据库不同的一组驱动器上：
SQL Server 2005 联机丛书：优化 tempdb 性能
Yes, the recommendation on high load servers is to put TempDB on a separate set of drives from the user databases:
SQL Server 2005 Books Online: Optimizing tempdb Performance
不能直接回答您的问题，但这可能是一个很好的提示：重新启动 SQL Server 实例将清除 tempdb，在调查在 tempdb 上执行的操作时，这可能是一个好的开始。
Not directly an answer on your question but this might be a good tip: Restarting your SQL Server instance will clear the tempdb, this might be a good start when investigating the actions which are done on the tempdb.
很好的问题，+1
tempdb 在 SQL 2005+ 中使用得更加频繁。
至少：快照隔离级别、在线索引重建、在触发器中读取 INSERTED/DELETED（用于读取日志文件！）
除了通常的 order by 子句、临时表等之外，
您可能最好将日志拆分为数据文件（也用于可恢复性）。
更多内存总是好的，但请参阅这个 64 位特定内容，脾气暴躁的老 DBA 下面。
最后，也许最重要的是，您可能会在 tempdb 中出现空间分配争用：
来自Linchi Shea 和 SQL Server 存储团队
后期编辑：
Paul Randall 添加了一个条目“综合 tempdb 博客文章系列" 提供了很好的链接
Excellent question, +1
tempdb is used far more heavily in SQL 2005+.
At least: Snapshot isolation levels, online index rebuild, reading INSERTED/DELETED in triggers(used to read the log file!)
This in addition to the usual order by clauses, temp tables etc.
You'd probably be better splitting your log and data files (also for recoverability).
More memory is always good but see this 64 bit specific stuff, Grumpy Old DBA below.
Finally, and maybe most important probably, you can have contention of space allocation in tempdb:
Explanations from Linchi Shea and SQL Server storage team
Late edit:
Paul Randall added an entry "Comprehensive tempdb blog post series" which offers good links
对 tempdb 的写入可以是任何内容。 内部哈希表、临时表、表变量、存储过程调用等。
如果您只有 250 Megs 的可用 RAM，那么更多的 RAM 会更好。
始终建议您将 tempdb 和用户数据库拆分到不同的磁盘。
对 tempdb 的所有写入大小均为 64k，因为这是每个数据库范围的大小。
Writes to the tempdb can be anything. Internal hash tables, temp tables, table variable, stored procedure calls, etc.
If you only have 250 Megs of free RAM, then yes more RAM would be good.
It is always recommended that you split tempdb and user databases to different disks.
All writes to the tempdb will be 64k in size as that's the size of each database extent.