分配更多的CPU和RAM后，SQL Server的性能降低

EXEC dbo.sp_BlitzFirst @SinceStartup = 1;

#1 楼

这里发生了很多事情，其中​​大多数是相当广泛和模糊的。


2008R2 RTM于2010年4月21日问世。它完全不受支持。您需要优先考虑大约3年前才发布的最新Service Pack。这样一来，如果您遇到奇怪的错误或其他问题，您将受到保护。
由于添加了vCPU（从1到4）并且没有更改任何设置，因此查询可以并行进行。我知道这听起来好像会更快，但是请稍等！
您可能已经添加了RAM，但可能没有更改最大服务器内存，因此服务器可以利用它。
您的服务器正在等待什么。我正在研究的一个开源项目提供了免费的脚本来帮助您评估SQL Server。如果您想尝试一下，请直接在这里。

您想获取sp_BlitzFirst来查看服务器的等待状态。您可以通过几种方式运行它。

这将向您显示服务器自启动以来一直在等待什么。

EXEC dbo.sp_BlitzFirst @SinceStartup = 1;

这将向您显示正在等待什么查询现在，在30秒的窗口中。

EXEC dbo.sp_BlitzFirst @Seconds = 30, @ExpertMode = 1;

一旦弄清正在等待什么查询（那里有很多关于等待统计的信息），您可以开始进行更改以使事情得到控制。如果您达到此目标，则可能要考虑将“并行性”的“成本阈值”提高到50，并可能将MAXD​​OP降低到2。 sp_BlitzWho（后者在早期的GitHub存储库中）开始捕获查询计划。除了等待统计信息外，它们是您可以用来找出问题所在的最重要内容之一。

您可能还想查看Jonathan Kehayias的有关VMWare计数器的这篇文章，以了解有关SQL Server的信息。 。 CPU肯定有问题。您的服务器通常无聊，但是当事情变热时，事情就会变糟。我会尝试轻松解决这个问题。


您遇到了一个名为CXPACKET的毒药等待。您没有很多，但这是有道理的，因为您的服务器不是非常活跃。我将在稍后说明原因。您平均对THREADPOOL和SOS_SCHEDULER_YIELD的等待时间非常长。您使用的是VM，因此要确保SQL Server有保留，或者该框没有被严重超额使用。一个吵闹的邻居真的会毁了你在这里的日子。您还将要确保服务器/ VM guest虚拟机/ VM主机未在“平衡电源”模式下运行。这会使您的CPU降到不必要的低速运行，并且不会立即恢复到全速运行。
它们如何配合？使用4个CPU，您有512个工作线程。请记住，单个CPU的使用量是相同的，但是现在您的查询可以并行进行，因此它们可以消耗更多的工作线程。在您的情况下，并行查询的每个并行分支有4个线程。

并行的是什么？最有可能的一切。并行性的默认“成本阈值”为5。该数字在90年代后期的某个类似桌面上工作时被设为缺省值。比大多数笔记本电脑都要小，但您仍然领先于那台笔记本电脑。

当大量并行查询开始时，您将用完这些工作线程。发生这种情况时，查询只是在等待线程开始。这也是CXPACKET出现的地方。查询正在退出CPU，并且很长时间没有恢复正常。我看不到任何阻塞等待，因此您很可能只是将所有内容都塞在查询内并行等待中。

您可以做什么？ >确保平衡电源模式下没有任何问题
将MAXD​​OP更改为2
将并行性的成本阈值更改为50
按照上面的Jon K.文章验证VM运行状况
使用脚本称为SOS_SCHEDULER_YIELD来查找任何缺少的索引请求。

要进行更彻底的故障排除，请查看我为Google写的关于云计算硬件大小的白皮书。

希望这会有所帮助！

#2 楼

是!我在服务器场中的SQL Server虚拟机上遇到了这种情况。查看虚拟机的主机CPU准备时间和内存气球驱动程序计数器。 ..

#3 楼

我没有指出的一件事是，由于调度，将vCPU添加到VM经常会减慢它的速度。必须等待4个物理内核可用，以便可以调度所有vCPU，即使其中3个处于空闲状态。

如果主机中没有很多内核，并且您的其他工作负载很忙，这可能会导致额外的等待，并导致性能显着下降。 >这是其中一例，其中提供了有关此情况及其诊断的真实示例。

另外，vCPU的配置（vSocket与vCore）实际上会影响某些应用程序，例如SQL Server。这是因为SQL Server本身知道NUMA（以避免相同的NUMA跨度性能下降），并且因为VMware可能以不同的方式显示虚拟NUMA节点。站点。


话虽如此，我很高兴您在Erik的帮助下解决了这些问题，但是您可能还希望研究并考虑这些问题。

#4 楼

只是一点点帮助（不能将其发布为评论）继续@sp_BlitzErik的答案，我对Pinal和Max Vernon（无法在哪里记起）提出了一些疑问，说明应该使用多少MAXDOP： />

/*************************************************************************
Author          :   Kin Shah
Purpose         :   Recommend MaxDop settings for the server instance
Tested RDBMS    :   SQL Server 2008R2

**************************************************************************/
declare @hyperthreadingRatio bit
declare @logicalCPUs int
declare @HTEnabled int
declare @physicalCPU int
declare @SOCKET int
declare @logicalCPUPerNuma int
declare @NoOfNUMA int

select @logicalCPUs = cpu_count -- [Logical CPU Count]
    ,@hyperthreadingRatio = hyperthread_ratio --  [Hyperthread Ratio]
    ,@physicalCPU = cpu_count / hyperthread_ratio -- [Physical CPU Count]
    ,@HTEnabled = case 
        when cpu_count > hyperthread_ratio
            then 1
        else 0
        end -- HTEnabled
from sys.dm_os_sys_info
option (recompile);

select @logicalCPUPerNuma = COUNT(parent_node_id) -- [NumberOfLogicalProcessorsPerNuma]
from sys.dm_os_schedulers
where [status] = 'VISIBLE ONLINE'
    and parent_node_id < 64
group by parent_node_id
option (recompile);

select @NoOfNUMA = count(distinct parent_node_id)
from sys.dm_os_schedulers -- find NO OF NUMA Nodes 
where [status] = 'VISIBLE ONLINE'
    and parent_node_id < 64

-- Report the recommendations ....
select
    --- 8 or less processors and NO HT enabled
    case 
        when @logicalCPUs < 8
            and @HTEnabled = 0
            then 'MAXDOP setting should be : ' + CAST(@logicalCPUs as varchar(3))
                --- 8 or more processors and NO HT enabled
        when @logicalCPUs >= 8
            and @HTEnabled = 0
            then 'MAXDOP setting should be : 8'
                --- 8 or more processors and HT enabled and NO NUMA
        when @logicalCPUs >= 8
            and @HTEnabled = 1
            and @NoofNUMA = 1
            then 'MaxDop setting should be : ' + CAST(@logicalCPUPerNuma / @physicalCPU as varchar(3))
                --- 8 or more processors and HT enabled and NUMA
        when @logicalCPUs >= 8
            and @HTEnabled = 1
            and @NoofNUMA > 1
            then 'MaxDop setting should be : ' + CAST(@logicalCPUPerNuma / @physicalCPU as varchar(3))
        else ''
        end as Recommendations

--------------------------------------- ------------------

--MAX VERNON 

/* 
   This will recommend a MAXDOP setting appropriate for your machine's NUMA memory
   configuration.  You will need to evaluate this setting in a non-production 
   environment before moving it to production.

   MAXDOP can be configured using:  
   EXEC sp_configure 'max degree of parallelism',X;
   RECONFIGURE

   If this instance is hosting a Sharepoint database, you MUST specify MAXDOP=1 
   (URL wrapped for readability)
   http://blogs.msdn.com/b/rcormier/archive/2012/10/25/
   you-shall-configure-your-maxdop-when-using-sharepoint-2013.aspx

   Biztalk (all versions, including 2010): 
   MAXDOP = 1 is only required on the BizTalk Message Box
   database server(s), and must not be changed; all other servers hosting other 
   BizTalk Server databases may return this value to 0 if set.
   http://support.microsoft.com/kb/899000
*/
SET NOCOUNT ON;

DECLARE @CoreCount int;
SET @CoreCount = 0;
DECLARE @NumaNodes int;

/*  see if xp_cmdshell is enabled, so we can try to use 
    PowerShell to determine the real core count
*/
DECLARE @T TABLE (
    name varchar(255)
    , minimum int
    , maximum int
    , config_value int
    , run_value int
);
INSERT INTO @T 
EXEC sp_configure 'xp_cmdshell';
DECLARE @cmdshellEnabled BIT;
SET @cmdshellEnabled = 0;
SELECT @cmdshellEnabled = 1 
FROM @T
WHERE run_value = 1;
IF @cmdshellEnabled = 1
BEGIN
    CREATE TABLE #cmdshell
    (
        txt VARCHAR(255)
    );
    INSERT INTO #cmdshell (txt)
    EXEC xp_cmdshell 'powershell -OutputFormat Text -NoLogo -Command "& {Get-WmiObject -namespace "root\CIMV2" -class Win32_Processor -Property NumberOfCores} | select NumberOfCores"';
    SELECT @CoreCount = CONVERT(INT, LTRIM(RTRIM(txt)))
    FROM #cmdshell
    WHERE ISNUMERIC(LTRIM(RTRIM(txt)))=1;
    DROP TABLE #cmdshell;
END
IF @CoreCount = 0 
BEGIN
    /* 
        Could not use PowerShell to get the corecount, use SQL Server's 
        unreliable number.  For machines with hyperthreading enabled
        this number is (typically) twice the physical core count.
    */
    SET @CoreCount = (SELECT i.cpu_count from sys.dm_os_sys_info i); 
END

SET @NumaNodes = (
    SELECT MAX(c.memory_node_id) + 1 
    FROM sys.dm_os_memory_clerks c 
    WHERE memory_node_id < 64
    );

DECLARE @MaxDOP int;

/* 3/4 of Total Cores in Machine */
SET @MaxDOP = @CoreCount * 0.75; 

/* if @MaxDOP is greater than the per NUMA node
    Core Count, set @MaxDOP = per NUMA node core count
*/
IF @MaxDOP > (@CoreCount / @NumaNodes) 
    SET @MaxDOP = (@CoreCount / @NumaNodes) * 0.75;

/*
    Reduce @MaxDOP to an even number 
*/
SET @MaxDOP = @MaxDOP - (@MaxDOP % 2);

/* Cap MAXDOP at 8, according to Microsoft */
IF @MaxDOP > 8 SET @MaxDOP = 8;

PRINT 'Suggested MAXDOP = ' + CAST(@MaxDOP as varchar(max));