我试图提出一种理智的方法来了解max server memory (mb)的设置是否合适(应该降低还是升高,或者保持原样)。我知道max server memory (mb)应该始终足够低,以便为操作系统本身等留出空间。

我正在寻找的环境有几百台服务器。我需要一个可靠的公式来确定缓冲池的当前大小是否合适,因为RAM是按分配给每个服务器的每GB的成本来计算的。整个环境是虚拟化的,分配给VM的“物理” RAM可以轻松上下调。

我正在查看一个特定的SQL Server实例,其PLE为1,100,052秒,相当于12.7天(服务器启动的时间)。该服务器的最大服务器内存设置为2560MB(2.5GB),其中实际仅提交了1380MB(1.3GB)。

我已经阅读了几篇文章,其中一篇由乔纳森·凯希耶斯(Jonathan Keheyias)(帖子),另一篇由保罗·兰达(Paul Randal)(帖子),还有其他几篇。 Jonathan提倡监视每4GB缓冲池中的PLE低于300。对于上述SQL Server实例,300 * (2.5 / 4) = 187导致目标PLE确实非常低,低于300。该实例具有290GB的SQL Server数据(不包括日志文件),仅用于集成测试。假设最近12天代表该服务器的典型使用情况,那么我可以说降低max server memory (mb)设置。

另一方面,我还有另一台集成测试服务器,其中PLE为294,其max server memory (mb)设置仅为1GB。该服务器仅具有224MB的SQL Server数据(不包括日志),并且正在运行某些BizFlow数据库。该服务器可能会受益于更高的max server memory (mb)设置。

我认为对于分配了过多内存的目标来说,一个好的起点可能包括:

SELECT 
    RamMB = physical_memory_in_bytes / 1048576
    , BufferPoolCommittedMB = bpool_committed * 8192E0 / 1048576
    , BufferPoolCommitTargetMB = bpool_commit_target * 8192E0 / 1048576
    , PercentOfDesiredSizeMB = CONVERT(INT,(CONVERT(DECIMAL(18,2),bpool_committed) 
                            / bpool_commit_target) * 100)
FROM sys.dm_os_sys_info;


如果BufferPoolCommitTargetMB / BufferPoolCommittedMB大于1,则服务器未使用整个缓冲池。如果有问题的机器的PLE也大于“ x”,则它可能是减少max server memory (mb)的一个很好的选择。

由于Buffer Manager:Lazy writes/sec性能计数器跟踪SQLOS写入页面的次数。由于内存压力而在检查点之间移出磁盘,这可能是另一件事。

DECLARE @WaitTime DATETIME;
SET @WaitTime = '00:00:15';
DECLARE @NumSeconds INT;
SET @NumSeconds = DATEDIFF(SECOND, 0, @WaitTime);
DECLARE @LazyWrites1 BIGINT;
DECLARE @LazyWrites2 BIGINT;

SELECT @LazyWrites1 = cntr_value 
FROM sys.dm_os_performance_counters dopc
WHERE (
        dopc.counter_name LIKE 'Lazy writes/sec%' COLLATE SQL_Latin1_General_CP1_CI_AS
    )
    AND dopc.object_name = 'MSSQL$' + CONVERT(VARCHAR(255),
               SERVERPROPERTY('InstanceName')) + ':Buffer Manager';

WAITFOR DELAY @WaitTime;

SELECT @LazyWrites2 = cntr_value 
FROM sys.dm_os_performance_counters dopc
WHERE (
        dopc.counter_name LIKE 'Lazy writes/sec%' COLLATE SQL_Latin1_General_CP1_CI_AS
    )
    AND dopc.object_name = 'MSSQL$' + CONVERT(VARCHAR(255),
               SERVERPROPERTY('InstanceName')) + ':Buffer Manager';

SELECT LazyWritesPerSecond = (@LazyWrites2 - @LazyWrites1) / @NumSeconds;


上面的代码假定服务器在15秒内处于负载状态它需要运行,否则它将报告0;否则,它将报告0。

我还应该看看PAGELATCHIO_*等待状态或某种其他等待类型作为内存压力的指标吗?

我的问题是,如何可靠地确定PLE和max server memory (mb)的“良好”目标值?

#1 楼

如您所知,没有通用的公式可以计算服务器的最大内存,您可以进行一些快速数学运算并得出一个值,但最终您仍然需要Perfmon计数器的帮助来监视内存使用情况并相应地进行更改。我知道下面的通用公式,我也使用它。我从此链接中学到了这个公式。对于SQL Server 2005到2008 R2,请注意:从SQL Server 2005到2008 R2,最大服务器内存只能控制缓冲池。因此,最大服务器内存配置在此有点乏味,并且只需很少的计算


就可以为Windows OS保留2 G内存。
当然,系统将运行防病毒软件。请保留1.5G的防病毒功能。请注意,Mcafee和SQL Server不能并存,因此请确保留有足够的空间。
您还可以检查性能监视器Perfmon Process-> Private bytes and Working Set,以监视AV和SQL Server框中运行的其他小型应用程序的内存使用情况。 >



请考虑驱动程序/固件的内存要求。您必须根据系统上安装的驱动程序的内存要求派生它。 RAMMAP工具可以帮助

考虑SQL Server的NonbPool(aka MTL或MTR)内存需求。

select  sum(multi_pages_kb)/1024 as multi_pages_mb from  sys.dm_os_memory_clerks


+最大工作线程数* 2MB

+在大多数情况下,用于直接Windows分配的内存大约为0到300 MB,但如果在SQL Server进程中加载​​了许多3方组件(包括链接服务器dll的第3个),则可能必须增加内存方备份dll等。)

+如果您正在使用CLR,请为CLR广泛添加一些额外的内存。

请考虑将在服务器上运行的作业(包括复制代理,日志传送等)和程序包的内存要求。根据正在运行的作业数量,它可以从MB到GB。对于中型服务器,可以将其作为250 MB。

请确保有足够的可用空间用于操作系统。

大约(每GB到4G为止为100 MB)+(每GB到12GB为止为50 MB)+(到RAM大小为止,每GB为25 MB)


其他内存要求。

如果您的环境有其他内存需求。

最大服务器内存=物理内存总量–(1 + 2 + 3 + 4 + 5 + 6 + 7 )

我还没有包括SSIS.SSRS,SSAS的内存配置,您还需要从总物理服务器内存中减去这些服务所需的内存。

配置完成后上面您需要监视以下计数器





SQLServer:Buffer Manager--页面预期寿命(PLE):
SQLServer:Buffer Manager-- CheckpointPages / sec:
SQLServer:Memory Manager-待批内存:
SQLServer:memory Manager-目标服务器内存:
SQLServer:memory Manager-总服务器内存



对于SQL Server 2012/2014。

From SQL Server 2012 onwards设置最大服务器内存h变得容易。因为现在最大服务器内存几乎解决了所有内存消耗问题。服务器的最大内存控制SQL Server的内存分配,包括缓冲池,编译内存,所有缓存,qe内存授予,锁管理器内存和CLR内存(基本上是dm_os_memory_clerks中的任何“职员”)。线程堆栈,堆,除SQL Server以外的链接服务器提供程序的内存或由“非SQL Server” DLL分配的任何内存不受最大服务器内存的控制。

您可以分配75-80%到SQL Server,然后使用perfmon计数器监视内存使用情况。在SQL Server 2012中,很少使用perfmon计数器。不建议使用缓冲区管理器计数器,必须使用内存管理器计数器



SQL Server:内存管理器-目标服务器内存(KB)
SQL Server:内存管理器- -总服务器内存(KB)
SQL Server:内存管理器-可用内存(KB)
SQL Server:内存管理器-数据库高速缓存内存(KB)


>
关于PLE的价值,我使用了Joanthan的公式,幸运的是它对我有用。

#2 楼

这里的挑战是数字不考虑最终用户的体验。

很好的例子:我有数据库服务器用来跟踪公司员工访问的每个网站。我不在乎它是否在峰值负载期间无法跟上插入,因为前端应用程序会定期分批处理插入,并且缓慢的插入不会对用户造成问题。用户仍然可以浏览网页,而不会被缓慢的插入阻止。

在SELECT时,人力资源部门会在询问给定员工的可疑浏览历史记录时才发出报告,但他们不在乎报告要花多长时间-他们只是打开报告然后关闭做其他事情。

性能需要从一个问题开始:用户对性能满意吗?如果是这样,请将系统留在原处。

评论


即使您使用的内存超出了您的需要?

–詹姆斯·安德森(James Anderson)
2014年12月11日12:18

詹姆斯(James)-一般来说,我不想进行会引起用户抱怨的更改。如果您确实想这样做,则可以逐步降低每个服务器的内存量,直到用户开始抱怨为止,但是当我已经工作过度时,我通常没有时间采取这些步骤。我必须专注于使不满意的用户感到满意的任务-而不是试图使不满意的用户感到不满意。 ;-)

–布伦特·奥扎(Brent Ozar)
2014年12月11日15:22

好点,布伦特。我被问到是否有些服务器超额配置,因为我们每年为每GB的内存付费。我正在查看的许多实例在最大服务器内存(mb)中都具有很少的RAM,因此,我非常不希望缩小它们的大小。但是,其他一些实例具有1,000,000 + PLE,因此很明显可能是RAM下降的候选对象。显然,降低RAM会导致IOps的增加,但我不确定这样做的成本是多少。

– Max Vernon♦
2014年12月11日在22:17

同样,将PLE与最大服务器内存设置进行比较也很麻烦。最大服务器内存设置越低,最小“可接受” PLE将越低,因此我可能会陷入不断下降的漩涡。正如您所提到的,我确定用户性能一定会受到影响。

– Max Vernon♦
2014年12月11日22:19在

PLE计数器是您应该避免从2012年开始使用的计数器,或者当您拥有NUMA系统时,每个节点都充当自己的小内存分配器。如果要为每个未完成的NUMA节点查找PLE,则可能会得到不正确的值

– Shanky
2014年12月13日在23:27

#3 楼

我用来评估PLE与max server memory的当前T-SQL是:

/*
    Purpose:            Returns a resultset describing various server level stats including PLE
                        Max and Min Server Memory, etc.
    By:                 Max Vernon
    Date:               2014-12-01
*/
SET NOCOUNT ON;

/*
    wait stats for PAGELATCH_IO
*/
DECLARE @Debug BIT;
SET @Debug = 0;
DECLARE @HTMLOutput BIT;
SET @HTMLOutput = 1;
DECLARE @WaitTime DATETIME;
SET @WaitTime = '00:00:15';
DECLARE @NumSeconds INT;
SET @NumSeconds = DATEDIFF(SECOND, 0, @WaitTime);
DECLARE @InstanceName NVARCHAR(255);
SET @InstanceName = CONVERT(NVARCHAR(255), SERVERPROPERTY('InstanceName'));
DECLARE @Version NVARCHAR(255);
DECLARE @VersionINT INT;
SET @Version = CONVERT(NVARCHAR(255),SERVERPROPERTY('ProductVersion'));
SET @VersionINT = CONVERT(INT, SUBSTRING(@Version,1 ,CHARINDEX('.',@Version)-1));
DECLARE @cmd NVARCHAR(MAX);
SET @cmd = '';
DECLARE @TaskCount INT;
DECLARE @TasksPerSecondAvg INT;
DECLARE @AvgWaitTimeInMSPerTask DECIMAL(10,2);
DECLARE @AvgWaitTimeInMSPerSecond DECIMAL(10,2);
DECLARE @TotalWaitTimeInMSOverall DECIMAL(10,2);
DECLARE @LazyWrites1 BIGINT;
DECLARE @LazyWrites2 BIGINT;
DECLARE @FreeListStallsSec1 BIGINT;
DECLARE @FreeListStallsSec2 BIGINT;
DECLARE @BatchReq1 BIGINT;
DECLARE @BatchReq2 BIGINT;
DECLARE @ws TABLE
(
    RunNum INT
    , wait_type SYSNAME
    , waiting_tasks_count BIGINT
    , wait_time_ms BIGINT
    , max_wait_time_ms BIGINT
    , signal_wait_time_ms BIGINT
);
INSERT INTO @ws
SELECT 1, dows.*
FROM sys.dm_os_wait_stats dows
WHERE dows.wait_type LIKE 'PAGEIOLATCH_%'
ORDER BY dows.waiting_tasks_count DESC;

SELECT @LazyWrites1 = cntr_value 
FROM sys.dm_os_performance_counters dopc
WHERE (
        dopc.counter_name LIKE N'Lazy writes/sec%' COLLATE SQL_Latin1_General_CP1_CI_AS
    )
    AND dopc.object_name = N'MSSQL$' + @InstanceName + N':Buffer Manager';

SELECT @FreeListStallsSec1 = cntr_value 
FROM sys.dm_os_performance_counters dopc
WHERE (
        dopc.counter_name LIKE N'Free list stalls/sec%' COLLATE SQL_Latin1_General_CP1_CI_AS
    )
    AND dopc.object_name = N'MSSQL$' + @InstanceName + N':Buffer Manager';

SELECT @BatchReq1 = cntr_value
FROM sys.dm_os_performance_counters dopc
WHERE dopc.counter_name LIKE N'Batch Requests/sec%' COLLATE SQL_Latin1_General_CP1_CI_AS
    AND dopc.object_name = N'MSSQL$' + @InstanceName + N':SQL Statistics';

WAITFOR DELAY @WaitTime;

INSERT INTO @ws
SELECT 2, dows.*
FROM sys.dm_os_wait_stats dows
WHERE dows.wait_type LIKE N'PAGEIOLATCH_%'
ORDER BY dows.waiting_tasks_count DESC;

SELECT @LazyWrites2 = cntr_value 
FROM sys.dm_os_performance_counters dopc
WHERE (
        dopc.counter_name LIKE N'Lazy writes/sec%' COLLATE SQL_Latin1_General_CP1_CI_AS
    )
    AND dopc.object_name = N'MSSQL$' + @InstanceName + N':Buffer Manager';

SELECT @FreeListStallsSec2 = cntr_value 
FROM sys.dm_os_performance_counters dopc
WHERE (
        dopc.counter_name LIKE N'Free list stalls/sec%' COLLATE SQL_Latin1_General_CP1_CI_AS
    )
    AND dopc.object_name = N'MSSQL$' + @InstanceName + N':Buffer Manager';

SELECT @TaskCount = SUM(w2.waiting_tasks_count - w1.waiting_tasks_count)
    , @TasksPerSecondAvg = CONVERT(DECIMAL(10,2), (SUM(w2.waiting_tasks_count) - SUM(w1.waiting_tasks_count))) / @NumSeconds
    , @AvgWaitTimeInMSPerTask = CONVERT(DECIMAL(10,2),(SUM(w2.wait_time_ms) - SUM(w1.wait_time_ms))) / CONVERT(DECIMAL(10,2),(SUM(w2.waiting_tasks_count) - SUM(w1.waiting_tasks_count)))
    , @AvgWaitTimeInMSPerSecond = (CONVERT(DECIMAL(10,2), (SUM(w2.waiting_tasks_count) - SUM(w1.waiting_tasks_count))) / @NumSeconds) * (CONVERT(DECIMAL(10,2),(SUM(w2.wait_time_ms) - SUM(w1.wait_time_ms))) / CONVERT(DECIMAL(10,2),(SUM(w2.waiting_tasks_count) - SUM(w1.waiting_tasks_count))))
    , @TotalWaitTimeInMSOverall = SUM(w2.wait_time_ms) - SUM(w1.wait_time_ms)
FROM (SELECT * FROM @ws ws1 WHERE ws1.RunNum = 1) w1
    INNER JOIN (SELECT * FROM @ws ws2 WHERE ws2.RunNum = 2) w2 ON w1.wait_type = w2.wait_type
WHERE (w2.waiting_tasks_count - w1.waiting_tasks_count) > 0;

SELECT @BatchReq2 = cntr_value
FROM sys.dm_os_performance_counters dopc
WHERE dopc.counter_name LIKE N'Batch Requests/sec%' COLLATE SQL_Latin1_General_CP1_CI_AS
    AND dopc.object_name = N'MSSQL$' + @InstanceName + N':SQL Statistics';

/*
    configured values for max server memory and min server memory, etc
*/
DECLARE @MaxServerMemory BIGINT;
DECLARE @MaxServerMemoryPages BIGINT;
DECLARE @MinServerMemory BIGINT;
DECLARE @MinPLE BIGINT;
DECLARE @RamMB BIGINT;
DECLARE @BufferPoolCommittedMB BIGINT;
DECLARE @BufferPoolCommitTargetMB BIGINT;
DECLARE @PercentOfDesiredSizeMB INT;
DECLARE @TargetPageLifeExpectancyPer4GB BIGINT;
SET @TargetPageLifeExpectancyPer4GB = 60 * 120; /* 120 minutes */
/*DECLARE @VMType VARCHAR(255);*/
DECLARE @PLESeconds BIGINT;

SELECT @MaxServerMemory = CONVERT(BIGINT,c.value)
FROM sys.configurations c
WHERE c.name = N'max server memory (mb)'

SET @MaxServerMemoryPages = @MaxServerMemory / 128; /* 8KB pages */

SELECT @MinServerMemory = CONVERT(BIGINT,c.value)
FROM sys.configurations c
WHERE c.name = N'min server memory (mb)'

SET @MinPLE = @MaxServerMemory / 4096E0 * @TargetPageLifeExpectancyPer4GB;

IF @VersionINT < 11
BEGIN
    SET @cmd = 'SELECT 
    @RamMB = dosi.physical_memory_in_bytes / 1048576
    , @BufferPoolCommittedMB = dosi.bpool_committed * 8192E0 / 1048576
    , @BufferPoolCommitTargetMB = dosi.bpool_commit_target * 8192E0 / 1048576
    , @PercentOfDesiredSizeMB = CONVERT(INT,(CONVERT(DECIMAL(18,2),dosi.bpool_committed) / dosi.bpool_commit_target) * 100)
FROM sys.dm_os_sys_info dosi;
';
END
ELSE 
BEGIN 
SET @cmd = 'SELECT 
    @RamMB = dosi.physical_memory_kb / 1024
    , @BufferPoolCommittedMB = dosi.committed_kb / 1024
    , @BufferPoolCommitTargetMB = dosi.committed_target_kb / 1024
    , @PercentOfDesiredSizeMB = CONVERT(INT,(CONVERT(DECIMAL(18,2),dosi.committed_kb) / dosi.committed_target_kb) * 100)
FROM sys.dm_os_sys_info dosi;';
END
EXEC sp_executesql @cmd
    , N'@RamMB BIGINT OUTPUT, @BufferPoolCommittedMB BIGINT OUTPUT, @BufferPoolCommitTargetMB BIGINT OUTPUT, @PercentOfDesiredSizeMB INT OUTPUT' 
    , @RamMB = @RamMB OUT
    , @BufferPoolCommittedMB = @BufferPoolCommittedMB OUT
    , @BufferPoolCommitTargetMB = @BufferPoolCommitTargetMB OUT
    , @PercentOfDesiredSizeMB = @PercentOfDesiredSizeMB OUT;

/*
    Page Life Expectancy for all memory nodes
*/
SELECT @PLESeconds = CONVERT(BIGINT, cntr_value) 
FROM sys.dm_os_performance_counters dopc
WHERE dopc.counter_name LIKE N'Page Life Expectancy%' COLLATE SQL_Latin1_General_CP1_CI_AS
    AND dopc.object_name = N'MSSQL$' + @InstanceName + N':Buffer Manager';

/*
    Total data in all user-databases.
*/
DECLARE @TotalDBSpaceUsed TABLE
(
    TotalSpaceUsedInMB BIGINT
);
DECLARE @SpaceUsedInMB BIGINT;
SET @cmd = '';
SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE '
UNION ALL
' END + 
'
SELECT DatabaseName = ''' + d.name + ''' 
    , AllocType = au.type_desc
    , TotalPagesInMB = SUM(au.total_pages) * 8192E0 / 1048576
FROM ' + QUOTENAME(d.name) + '.sys.allocation_units au
WHERE au.type > 0
GROUP BY au.type_desc
'
FROM master.sys.databases d
WHERE d.database_id > 4;
SET @cmd = 'SELECT SUM(TotalPagesInMB)
FROM (
' + @cmd + '
) t;'; 
INSERT INTO @TotalDBSpaceUsed (TotalSpaceUsedInMB)
EXEC sp_executesql @cmd;
SELECT @SpaceUsedInMB = TDSU.TotalSpaceUsedInMB
FROM @TotalDBSpaceUsed TDSU;

IF @Debug = 1
BEGIN
    SELECT ServerName = @@SERVERNAME
        , InstanceName = @InstanceName
        , DatabaseSpaceUsedMB = @SpaceUsedInMB
        , PLEinSeconds = @PLESeconds
        , MinAcceptablePLE = @MinPLE
        , MinServerMemoryMB = @MinServerMemory
        , MaxServerMemoryMB = @MaxServerMemory
        , TotalServerRAMinMB = @RamMB
        , BufferPoolCommittedMB = @BufferPoolCommittedMB
        , BufferPoolCommitTargetMB = @BufferPoolCommitTargetMB
        , PercentBufferPoolCommitted = @PercentOfDesiredSizeMB
        , BatchReqPerSecond = (@BatchReq2 - @BatchReq1) / @NumSeconds
        , LazyWritesPerSecond = (@LazyWrites2 - @LazyWrites1) / @NumSeconds
        , FreeListStallsPerSecond = (@FreeListStallsSec2 - @FreeListStallsSec2) / @NumSeconds
        /*, VMType = @VMType*/
        , IOTaskCount = @TaskCount 
        , TaskPerSecondAvg = @TasksPerSecondAvg 
        , AvgWaitTimeInMSPerTask = @AvgWaitTimeInMSPerTask 
        , AvgWaitTimeInMSPerSecond = @AvgWaitTimeInMSPerSecond 
        , TotalWaitTimeInMSOverall  = @TotalWaitTimeInMSOverall
        , SamplePeriodinSec = @NumSeconds;

    SELECT MaxServerMemorySuggested = 
            CASE WHEN @BufferPoolCommittedMB < @BufferPoolCommitTargetMB 
            THEN @BufferPoolCommittedMB 
            ELSE ((CONVERT(DECIMAL(18,4), @MinPLE) / @PLESeconds) * @MaxServerMemory) 
                    + (((@LazyWrites2 - @LazyWrites1) / @NumSeconds) * 64) 
                    + ((@FreeListStallsSec2 - @FreeListStallsSec2) / @NumSeconds) * 64 
            END
        , Reason = CASE WHEN @BufferPoolCommittedMB < @BufferPoolCommitTargetMB THEN N'Committed MB less than current Max Server Memory'
            ELSE N'Calculated based on PLE, Lazy Writes / second and List Stalls / second' END
        , LazyWritesX64 = (((@LazyWrites2 - @LazyWrites1) / @NumSeconds) * 64)
        , ListStallsX64 = ((@FreeListStallsSec2 - @FreeListStallsSec2) / @NumSeconds) * 64;
END

DECLARE @Out TABLE
(
    KeyID INT IDENTITY(1,1)
    , ItemDesc NVARCHAR(255)
    , ItemValue SQL_VARIANT
    , IsDebug BIT DEFAULT(0)
);

INSERT INTO @Out (ItemDesc, ItemValue, IsDebug)
VALUES (N'Server Name', CONVERT(NVARCHAR(255),@@SERVERNAME), 1);

INSERT INTO @Out (ItemDesc, ItemValue)
VALUES (N'Data Space Used (MB)', @SpaceUsedInMB);

INSERT INTO @Out (ItemDesc, ItemValue)
VALUES (N'Page Life Expectancy (sec)', @PLESeconds);

INSERT INTO @Out (ItemDesc, ItemValue)
VALUES (N'Minimum Acceptable Page Life Expectancy (sec)', @MinPLE);

INSERT INTO @Out (ItemDesc, ItemValue)
VALUES (N'Minimum Server Memory (MB)', @MinServerMemory);

INSERT INTO @Out (ItemDesc, ItemValue)
VALUES (N'Maximum Server Memory (MB)', @MaxServerMemory);

INSERT INTO @Out (ItemDesc, ItemValue)
VALUES (N'Total Server RAM in MB', @RamMB);

INSERT INTO @Out (ItemDesc, ItemValue)
VALUES (N'Buffer Pool Committed MB', @BufferPoolCommittedMB);

INSERT INTO @Out (ItemDesc, ItemValue)
VALUES (N'Buffer Pool Commit Target MB', @BufferPoolCommitTargetMB);

INSERT INTO @Out (ItemDesc, ItemValue)
VALUES (N'Percent of Buffer Pool Committed', @PercentOfDesiredSizeMB);

INSERT INTO @Out (ItemDesc, ItemValue)
VALUES (N'Batch Requests Per Second', (@BatchReq2 - @BatchReq1) / @NumSeconds);

INSERT INTO @Out (ItemDesc, ItemValue)
VALUES (N'Lazy Writes Per Second', (@LazyWrites2 - @LazyWrites1) / @NumSeconds);

INSERT INTO @Out (ItemDesc, ItemValue)
VALUES (N'Free List Stalls Per Second', (@FreeListStallsSec2 - @FreeListStallsSec2) / @NumSeconds);

INSERT INTO @Out (ItemDesc, ItemValue)
VALUES (N'IO Task Count', @TaskCount);

INSERT INTO @Out (ItemDesc, ItemValue)
VALUES (N'Task Per Second Avg', @TasksPerSecondAvg);

INSERT INTO @Out (ItemDesc, ItemValue)
VALUES (N'Avg Wait Time In MS Per Task', @AvgWaitTimeInMSPerTask);

INSERT INTO @Out (ItemDesc, ItemValue)
VALUES (N'Avg Wait Time In MS Per Second', @AvgWaitTimeInMSPerSecond);

INSERT INTO @Out (ItemDesc, ItemValue)
VALUES (N'Total Wait Time In MS Overall', @TotalWaitTimeInMSOverall);

INSERT INTO @Out (ItemDesc, ItemValue)
VALUES (N'Sample Period in Seconds', @NumSeconds);

INSERT INTO @Out (ItemDesc, ItemValue)
VALUES (N'Lazy Writes per Second', ((@LazyWrites2 - @LazyWrites1) / @NumSeconds));

INSERT INTO @Out (ItemDesc, ItemValue)
VALUES (N'List Stalls per Second', ((@FreeListStallsSec2 - @FreeListStallsSec2) / @NumSeconds));

INSERT INTO @Out (ItemDesc, ItemValue)
VALUES (N'Recommended Max Memory (MB)', N'');

INSERT INTO @Out (ItemDesc, ItemValue)
VALUES (N'Recommended Max Memory Reason', N'');

INSERT INTO @Out (ItemDesc, ItemValue, IsDebug)
VALUES (N'Recommended Max Memory Signal', 0, 1);

/*
    Add memory if Lazy Writes occurred
    Add 64MB per Lazy Write (just for fun)
*/
DECLARE @LazyWritesMB INT;
SET @LazyWritesMB = (((@LazyWrites2 - @LazyWrites1) / @NumSeconds) * 64);

/*
    Add memory if Free List Stalls occurred
    Add 128MB per Free List Stall
*/
DECLARE @FreeListStallMB INT;
SET @FreeListStallMB = (((@FreeListStallsSec2 - @FreeListStallsSec2) / @NumSeconds) * 128);

/*
    Add the Additional memory requirements to the Recommended Max Memory row
*/
DECLARE @AdditionalMemory INT;
SET @AdditionalMemory = 
    @LazyWritesMB
    + @FreeListStallMB;

IF (@MaxServerMemory + @AdditionalMemory < 1024) AND (@PLESeconds >= @MinPLE)
BEGIN
    UPDATE @Out 
    SET ItemValue = @MaxServerMemory
    WHERE ItemDesc = N'Recommended Max Memory (MB)';

    UPDATE @Out 
    SET ItemValue = 'Max Server Memory is low, however PLE is acceptable'
    WHERE ItemDesc = N'Recommended Max Memory Reason';

    UPDATE @Out 
    SET ItemValue = 1
    WHERE ItemDesc = N'Recommended Max Memory Signal';
END

IF ((@BufferPoolCommittedMB + @AdditionalMemory) < @BufferPoolCommitTargetMB) AND (@PLESeconds >= @MinPLE)
BEGIN
    UPDATE @Out 
    SET ItemValue = @BufferPoolCommittedMB + @AdditionalMemory
    WHERE ItemDesc = N'Recommended Max Memory (MB)';

    UPDATE @Out 
    SET ItemValue = 'Buffer pool committed is less than Max Server Memory, and PLE is acceptable.'
    WHERE ItemDesc = N'Recommended Max Memory Reason';

    UPDATE @Out 
    SET ItemValue = 2
    WHERE ItemDesc = N'Recommended Max Memory Signal';
END

DECLARE @PLEMultiplier DECIMAL(10,2);
SET @PLEMultiplier = (CONVERT(DECIMAL(10,2),@MinPLE) / CONVERT(DECIMAL(10,2), @PLESeconds));
IF @PLEMultiplier < 0.90 SET @PLEMultiplier = 0.90;
IF @PLEMultiplier > 1.10 SET @PLEMultiplier = 1.10;

INSERT INTO @Out (ItemDesc, ItemValue, IsDebug)
VALUES (N'PLE Multiplier', @PLEMultiplier, 1);

IF /*(@MaxServerMemory + @AdditionalMemory >= 1024) AND*/ (@PLESeconds <= @MinPLE)
BEGIN
    UPDATE @Out 
    SET ItemValue = 
        (SELECT TOP(1) Inc
        FROM (
            SELECT Inc = t.RowNum * 256
            FROM (
                SELECT RowNum = CONVERT(BIGINT,ROW_NUMBER() OVER (ORDER BY o.object_id))
                FROM sys.objects o, sys.objects o1
                ) t
            WHERE (t.RowNum * 256) <  CONVERT(BIGINT,POWER(2,30))
            ) t1
        WHERE t1.Inc > CONVERT(INT, (@MaxServerMemory * @PLEMultiplier))
        ORDER BY t1.Inc)
    WHERE ItemDesc = N'Recommended Max Memory (MB)';

    UPDATE @Out 
    SET ItemValue = 'Low PLE indicates Max Server Memory should be adjusted upwards.'
    WHERE ItemDesc = N'Recommended Max Memory Reason';

    UPDATE @Out 
    SET ItemValue = 3
    WHERE ItemDesc = N'Recommended Max Memory Signal';
END

IF (@MaxServerMemory + @AdditionalMemory >= 1024) AND (@PLESeconds > @MinPLE)
BEGIN
    UPDATE @Out 
    SET ItemValue = 
        (SELECT TOP(1) Inc
        FROM (
            SELECT Inc = t.RowNum * 256
            FROM (
                SELECT RowNum = CONVERT(BIGINT,ROW_NUMBER() OVER (ORDER BY o.object_id))
                FROM sys.objects o, sys.objects o1
                ) t
            WHERE (t.RowNum * 256) <  CONVERT(BIGINT,POWER(2,30))
            ) t1
        WHERE t1.Inc <= CONVERT(INT, (@MaxServerMemory * @PLEMultiplier))
        ORDER BY t1.Inc DESC)
    WHERE ItemDesc = N'Recommended Max Memory (MB)';

    UPDATE @Out 
    SET ItemValue = 'High PLE indicates Max Server Memory could be adjusted downwards.'
    WHERE ItemDesc = N'Recommended Max Memory Reason';

    UPDATE @Out 
    SET ItemValue = 4
    WHERE ItemDesc = N'Recommended Max Memory Signal';
END

DECLARE @RecommendedMaxServerMemory INT;
SELECT  @RecommendedMaxServerMemory = CONVERT(INT,ItemValue)
FROM @Out o 
WHERE o.ItemDesc = N'Recommended Max Memory (MB)';

IF @RecommendedMaxServerMemory > (@MaxServerMemory * 0.96)
    AND @RecommendedMaxServerMemory < (@MaxServerMemory * 1.04)
BEGIN
    UPDATE @Out
    SET ItemValue = @MaxServerMemory
    WHERE ItemDesc = N'Recommended Max Memory (MB)';
    UPDATE @Out
    SET ItemValue = 'No changed recommended'
    WHERE ItemDesc = N'Recommended Max Memory Reason';
    UPDATE @Out 
    SET ItemValue = 0
    WHERE ItemDesc = N'Recommended Max Memory Signal';
END 

IF (@HTMLOutput = 1)
BEGIN
    SELECT ItemValue
        , HTMLOutput = '<table>' + 
            (
                SELECT 'td' = ItemDesc
                    , ''
                    , 'td' = ItemValue
                    , ''
                FROM @Out o
                WHERE CASE WHEN @Debug = 0 THEN o.IsDebug ELSE 0 END = 0
                ORDER BY o.KeyID
                FOR XML PATH('tr')
            ) +
            '</table>'
    FROM @Out o
    WHERE o.ItemDesc = N'Recommended Max Memory Signal';
END
ELSE
BEGIN
    SELECT *
    FROM @Out o
    WHERE CASE WHEN @Debug = 0 THEN o.IsDebug ELSE 0 END = 0
    ORDER BY o.KeyID;
END


该代码将PLE与最小“可接受” PLE进行比较,得出max server memory的数量系统已配置。如果PLE明显高于可接受的数字,则建议最大降低13%。如果PLE低于可接受的PLE,则建议最多增加10%max server memory

如果已提交的缓冲池的实际数量小于目标缓冲池的大小,则建议将max server memory降低到该数量,并为线程,延迟写入等添加一些额外的内存。

代码还会查看各种性能计数器,例如“懒惰写入” /“秒”,“空闲列表停顿”和“批处理请求”。

代码并不完美,我在这里共享以获取输入,并为将来的SO用户带来好处。

评论


从SQL Server 2012缓冲池目标开始并已提交的Max Mind没有任何意义,并且不赞成使用这些计数器。相反,您必须使用“内存管理器目标已提交(KB)”和“当前已提交”。如果您想了解更多,为什么它会赋予错误的价值?social.technet.microsoft.com/wiki/contents/articles/…

– Shanky
2014-12-13 23:25