主页 / dba / 问题 / 84574
Accepted
Fandango68
Asked: 2014-12-08 23:09:24 +0800 CST

一种自动清除/重新启动虚拟内存蠕变的方法？

  • 772

我知道这样做不是很好的做法，但我的公司负担不起我试图为我们的问题找到合适的解决方案。我读过这篇文章，我可以对 SQL Server 2005 做同样的事情，但我希望有更好（更快？）的方法。

问题如下：

我们有一个在 Windows Server 2003 上运行的 ASP.net 网站 (ASP2)，它还托管我们的 SQL Server 2005 数据库并进行安装。

最近我们注意到我们的 SQL Server 2005 实例的 VM 已提交内存空间增长了，几乎达到了我们设置为 1.7 GB 的 VM 保留空间（嗯，太多了？）。

运行我们的 SQL Server 的 PC 服务器具有 4 GB 的 RAM 和高达 3.5 GB 的可用磁盘空间，用于安装 SQL Server 2005。数据库本身位于 350 GB 的共享空间上（为此有两个分区：分别是 C 和 D 驱动器）。

因此，就虚拟机空间而言，我认为我们有足够的空间并将我们的虚拟机限制设置为 1.7 GB（理想情况下为 2 GB）就足够了。

数据库本身的大小小于 500 MB。

当我们的 VM Committed 值接近 VM Reserved 值时，服务会大大减慢。我知道解决这个问题的唯一方法是停止所有 SQL 服务服务并重新启动它们。但是在 24 小时内，VM Committed 仍然在爬升！

有没有一种方法可以让我试图找出问题的原因，而只需在一夜之间自动重新启动所有服务？或者我真的应该试图找出我们问题的原因吗？

我做了一些调查（但正如我所说，我们现在时间很差），并找到了这个 SQL 查询（见下文），它告诉我什么实际查询似乎最占用 CPU（不确定这是否有什么需要做我上面原来的VM问题）。

它显示了一个查询恰好在一夜之间运行，并且可能是该查询只是占用内存而不将其释放回池中？

有没有办法在 SQL Server 中“关闭”线程/内存分配等，以便释放内存？

这是 CPU “hog search” 查询（由this提供）

select  
    highest_cpu_queries.plan_handle,  
    highest_cpu_queries.total_worker_time, 
    q.dbid, 
    q.objectid, 
    q.number, 
    q.encrypted, 
    q.[text] 
from  
    (select top 50  
        qs.plan_handle,  
        qs.total_worker_time 
    from  
        sys.dm_exec_query_stats qs 
    order by qs.total_worker_time desc) as highest_cpu_queries 
    cross apply sys.dm_exec_sql_text(plan_handle) as q 
order by highest_cpu_queries.total_worker_time desc

编辑，回答 Pathum 的问题：

运行查询的结果：

---------------------------------------------------------------------------------------------------- 
Memory usage details for SQL Server instance SERVER9 (9.00.4053.00 - X86) - Standard Edition)
----------------------------------------------------------------------------------------------------

Memory visible to the Operating System
Physical Memory_MB|Physical Memory_GB|Virtual Memory GB 4091|4|2
Buffer Pool Usage at the Moment
BPool_Committed_MB|BPool_Commit_Tgt_MB|BPool_Visible_MB
519.250000|1599.875000|1599.875000

Total Memory used by SQL Server Buffer Pool as reported by Perfmon counters
Mem_KB|Mem_MB|Mem_GB 531712|519.250000|0.507080078

Memory needed as per current Workload for SQL Server instance    
Mem_KB|Mem_MB|Mem_GB 1638272|1599.875000|1.562377929

Total amount of dynamic memory the server is using for maintaining connections
Mem_KB|Mem_MB|Mem_GB 560|0.546875|0.000534057

Total amount of dynamic memory the server is using for locks
Mem_KB|Mem_MB|Mem_GB 968|0.945312|0.000923156

Total amount of dynamic memory the server is using for the dynamic SQL cache
Mem_KB|Mem_MB|Mem_GB 1120|1.093750|0.001068115

Total amount of dynamic memory the server is using for query optimization
Mem_KB|Mem_MB|Mem_GB 552|0.539062|0.000526428

Total amount of dynamic memory used for hash, sort and create index operations.
Mem_KB|Mem_MB|Mem_GB 0|0.000000|0.000000000

Total Amount of memory consumed by cursors
Mem_KB|Mem_MB|Mem_GB 480|0.468750|0.000457763

Number of pages in the buffer pool (includes database, free, and stolen).
8KB_Pages|Pages_in_KB|Pages_in_MB 66464|531712.000000|519.250000000

Number of Data pages in the buffer pool
8KB_Pages|Pages_in_KB|Pages_in_MB 50135|401080.000000|391.679687500

Number of Free pages in the buffer pool
8KB_Pages|Pages_in_KB|Pages_in_MB 895|7160.000000|6.992187500

Number of Reserved pages in the buffer pool
8KB_Pages|Pages_in_KB|Pages_in_MB 0|0.000000|0.000000000

Number of Stolen pages in the buffer pool
8KB_Pages|Pages_in_KB|Pages_in_MB 15434|123472.000000|120.578125000

Number of Plan Cache pages in the buffer pool
8KB_Pages|Pages_in_KB|Pages_in_MB 11953|95624.000000|93.382812500

Page Life Expectancy - Number of seconds a page will stay in the buffer pool without references
Page Life in seconds|PLE Status 47011|PLE is Healthy

Number of requests per second that had to wait for a free page
Free list stalls/sec 0


Number of pages flushed to disk/sec by a checkpoint or other operation that require all dirty pages to be flushed
Checkpoint pages/sec 572

Number of buffers written per second by the buffer manager"s lazy writer
Lazy writes/sec 0

Total number of processes waiting for a workspace memory grant
Memory Grants Pending 0

Total number of processes that have successfully acquired a workspace memory grant
Memory Grants Outstanding 0

第二次查询，检查内存压力：

SELECT object_name, cntr_value 
FROM sys.dm_os_performance_counters 
WHERE counter_name IN ('Total Server Memory (KB)', 'Target Server Memory (KB)');

结果：

object_name|cntr_value
SQLServer:Memory Manager        |1638272
SQLServer:Memory Manager        |531712
(2 row(s) affected)

  • 我的服务器是 32 位的。我们不会出于任何原因迁移到 64 位。

  • sp_configur无法提供来自电子信息的值，这是保密的。:)

sql-server sql-server-2005

3 个回答

  1. Best Answer

    首先，每当您发布与 SQL Server 相关的问题时，请发布“SQL Server 的版本和版本”，您不会相信答案可能会随着版本的不同而完全改变。

    最近我们注意到我们的 SQL Server 2005 实例的 VM 已提交内存空间增长了，几乎达到了我们设置为 1.7 GB 的 VM 保留空间（嗯，太多了？）。

    你为什么看VM committedand VM reserved，你真的知道它在说什么吗？您想从 VM 提交/保留中推断出什么。VM 保留是 SQL Server 保留的虚拟地址空间 (VAS) 总量。SQL Server 可以根据其 VAS 限制保留 VAS，即 64 位为 8 TB，32 位默认为 2 G。所以当虚拟机保留高时没有问题。这是正常行为。保留的内存实际上是 SQL Server“认为”它将来可能使用的内存。而且由于保留的 VM 是 1.7 GI，所以不认为这有问题

    VM 已提交意味着虚拟地址空间 (VAS) 或 SQL Server 已提交的虚拟内存或physical RAM系统上支持的内存的总量。已提交的内存具有与之关联的物理内存。已提交表示 SQL Server 当前正在使用的总虚拟内存。

    当我们的 VM Committed 值接近 VM Reserved 值时，服务会大大减慢。我知道解决这个问题的唯一方法是停止所有 SQL 服务服务并重新启动它们。但是在 24 小时内，VM Committed 仍然在爬升！

    在 SQL Server 上，提交的虚拟机可以等于保留的虚拟机，这不一定是问题。请不要养成重新启动 SQL Server IMO 的习惯，这是你能做的最糟糕的事情。现在我认为您在 SQL Server 中面临性能问题，您需要首先找出导致服务器缓慢的实际原因。要开始使用，您可以参考如何分析 SQL Server 性能

    这实际上可以帮助您找出问题所在。相信我，从您发布的内容来看，在我看来，它似乎不是内存问题，您再次提供了有关 SQL Server 版本的有限信息，因此我的回答是有限的。

    请在您的问题中添加以下输出

    Select @@Version

    您的 SQL Server 是否已修补到 SQL Server 2005 SP4。肯定是。您可以使用以下命令阅读错误日志并将其内容发布在某个共享位置并附加有问题的链接吗？我想看看错误日志中有什么。

    sp_readerrorlog

    你说你只有 4 G 的 SQL Server 你为 SQL Server 设置了最大服务器内存吗？

    Windows Server 2003 是否已修补到最新的 Service Pack。确保 Windows Server 中存在过度修剪 SQL Server 内存的错误。

    编辑：

    .Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) 2009 年 5 月 26 日 14:24:20 版权所有 (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2（Build 3790：Service Pack 2）

    您的 SQL Server 是 32 位，而 Windows Server 是标准版 32 位。无论如何，您不能强制 Windows 服务器使用超过 4 G 的 RAM，因此在 SQL Server 上启用 AWE 是没有意义的，因为它可能允许数据和索引页面查看超出 2 G 的 VAS 限制的内存。我只能建议您将 SQL Server 和 Windows 操作系统都升级到 64 位。我会推荐带有 SP2 的 SQL Server 2012 和 windows Server 2012 或 windows server 2008 r2，但要确保两者都是 64 位的。

    如果这是不可能的，您可以将 Windows Server 2003 从标准升级到企业，那么您可以启用 PAE 并且窗口服务器可以看到超过 4 G。之后启用 AWE 以允许 SQL Server 数据和索引页面看到大于 2 G 的内存。到目前为止，无论如何，SQL Server VAS 都被限制为 2G，因此它无法访问超过 2G 的内存

    如果您想了解有关 32 位内存配置的信息，请阅读本文

    • 5

  2. 首先检查最大内存。此处的说明：http ://technet.microsoft.com/en-us/library/ms191144(v=sql.90).aspx

    您应该将最大值设置为足够低，以便为操作系统和任何其他应用程序留出空间。在您的情况下，这将非常低。也许500mb-1gb

    • 1

  3. 您可以从查询中发布以下结果吗？我在下面提供的这些要点可以帮助您诊断问题

    用于报告 SQL Server 实例的内存使用详细信息的脚本 原始作者 Sakthivel Chidambaram 此脚本将帮助您诊断问题

                /*============================================================================
               Script to report Memory usage details of a SQL Server instance
               Author: Sakthivel Chidambaram, Microsoft http://blogs.msdn.com/b/sqlsakthi 

               Date: June 2012
               Version: V2

               V1: Initial Release
               V2: Added PLE, Memory grants pending, Checkpoint, Lazy write,Free list counters

               THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
               ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
               TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
               PARTICULAR PURPOSE. 

            ============================================================================*/ 
            -- We don't need the row count
            SET NOCOUNT ON

            -- Get size of SQL Server Page in bytes
            DECLARE @pg_size INT, @Instancename varchar(50) 
            SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E' 

            -- Extract perfmon counters to a temporary table
            IF OBJECT_ID('tempdb..#perfmon_counters') is not null DROP TABLE #perfmon_counters
            SELECT * INTO #perfmon_counters FROM sys.dm_os_performance_counters

            -- Get SQL Server instance name
            SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM #perfmon_counters WHERE counter_name = 'Buffer cache hit ratio' 

            -- Print Memory usage details
            PRINT '----------------------------------------------------------------------------------------------------' 
            PRINT 'Memory usage details for SQL Server instance ' + @@SERVERNAME + ' (' + CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - ' + SUBSTRING(@@VERSION, CHARINDEX('X',@@VERSION),4) + ' - ' + CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')' 
            PRINT '----------------------------------------------------------------------------------------------------' 
            SELECT 'Memory visible to the Operating System' 
            SELECT CEILING(physical_memory_in_bytes/1048576.0) as [Physical Memory_MB], CEILING(physical_memory_in_bytes/1073741824.0) as [Physical Memory_GB], CEILING(virtual_memory_in_bytes/1073741824.0) as [Virtual Memory GB] FROM sys.dm_os_sys_info 
            SELECT 'Buffer Pool Usage at the Moment' 
            SELECT (bpool_committed*8)/1024.0 as BPool_Committed_MB, (bpool_commit_target*8)/1024.0 as BPool_Commit_Tgt_MB,(bpool_visible*8)/1024.0 as BPool_Visible_MB FROM sys.dm_os_sys_info 
            SELECT 'Total Memory used by SQL Server Buffer Pool as reported by Perfmon counters' 
            SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Total Server Memory (KB)' 
            SELECT 'Memory needed as per current Workload for SQL Server instance' 
            SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Target Server Memory (KB)' 
            SELECT 'Total amount of dynamic memory the server is using for maintaining connections' 
            SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Connection Memory (KB)' 
            SELECT 'Total amount of dynamic memory the server is using for locks' 
            SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Lock Memory (KB)' 
            SELECT 'Total amount of dynamic memory the server is using for the dynamic SQL cache' 
            SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'SQL Cache Memory (KB)' 
            SELECT 'Total amount of dynamic memory the server is using for query optimization' 
            SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Optimizer Memory (KB) ' 
            SELECT 'Total amount of dynamic memory used for hash, sort and create index operations.' 
            SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Granted Workspace Memory (KB) ' 
            SELECT 'Total Amount of memory consumed by cursors' 
            SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Cursor memory usage' and instance_name = '_Total' 
            SELECT 'Number of pages in the buffer pool (includes database, free, and stolen).' 
            SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name= @Instancename+'Buffer Manager' and counter_name = 'Total pages' 
            SELECT 'Number of Data pages in the buffer pool' 
            SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Database pages' 
            SELECT 'Number of Free pages in the buffer pool' 
            SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Free pages' 
            SELECT 'Number of Reserved pages in the buffer pool' 
            SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Reserved pages' 
            SELECT 'Number of Stolen pages in the buffer pool' 
            SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Stolen pages' 
            SELECT 'Number of Plan Cache pages in the buffer pool' 
            SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name=@Instancename+'Plan Cache' and counter_name = 'Cache Pages' and instance_name = '_Total'
            SELECT 'Page Life Expectancy - Number of seconds a page will stay in the buffer pool without references' 
            SELECT cntr_value as [Page Life in seconds],CASE WHEN (cntr_value > 300) THEN 'PLE is Healthy' ELSE 'PLE is not Healthy' END as 'PLE Status'  FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Page life expectancy'
            SELECT 'Number of requests per second that had to wait for a free page' 
            SELECT cntr_value as [Free list stalls/sec] FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Free list stalls/sec'
            SELECT 'Number of pages flushed to disk/sec by a checkpoint or other operation that require all dirty pages to be flushed' 
            SELECT cntr_value as [Checkpoint pages/sec] FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Checkpoint pages/sec'
            SELECT 'Number of buffers written per second by the buffer manager"s lazy writer'
            SELECT cntr_value as [Lazy writes/sec] FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Lazy writes/sec'
            SELECT 'Total number of processes waiting for a workspace memory grant'
            SELECT cntr_value as [Memory Grants Pending] FROM #perfmon_counters WHERE object_name=@Instancename+'Memory Manager' and counter_name = 'Memory Grants Pending'
            SELECT 'Total number of processes that have successfully acquired a workspace memory grant'
            SELECT cntr_value as [Memory Grants Outstanding] FROM #perfmon_counters WHERE object_name=@Instancename+'Memory Manager' and counter_name = 'Memory Grants Outstanding'

    • 使用它可以检查内存压力

      SELECT object_name, cntr_value FROM sys.dm_os_performance_counters WHERE counter_name IN ('Total Server Memory (KB)', 'Target Server Memory (KB)');

    • 如果您的 SQL Server 部署在 32 位操作系统上。考虑将其移至 64 位操作系统。
    • 如果您可以提供 sp_configure 信息中的值，也更好

    希望我的观点对你有帮助:)

    2014 年 12 月 10 日更新

    @Fernando68 嗨，您是否在 SQL Server 中启用了 AWE（地址窗口扩展）？并注意这在 64 位机器上不是必需的，因为操作系统的内存限制（一个 64 位内存地址），如果启用，SQL Server 将始终尝试使用 AWE，并且 SQL Server 可以动态管理其 AWE 内存用法。启动时，它将分配启动工作负载所需的内存量。

    你可以参考下面的文章

    工作原理：SQL Server 32 位 PAE/AWE on（SQL 2005、2008 和 2008 R2）——没有使用预期的那么多 RAM！

    • 0

相关问题