Niższa wydajność programu SQL Server po przydzieleniu większej ilości procesora i pamięci RAM

Mamy SQL Server 2008 R2 (10.50.1600) działający na wirtualnym serwerze Windows 2008 R2. Po aktualizacji procesora z 1 rdzenia na 4 i pamięci RAM z 4 gb na 10 gb zauważyliśmy, że wydajność jest gorsza.

Niektóre obserwacje, które widzę:

1. Uruchomienie zapytania, które zajęło <5 sekund, zajmuje teraz> 200 sekund.
2. Procesor jest przypisany do wartości 100, a przyczyną jest sqlservr.exe.
3. Wybrana liczba (*) w tabeli z 4,6 mln wierszy zajęła ponad 90 sekund.
4. Procesy uruchomione na serwerze nie uległy zmianie. Jedyną zmianą było zwiększenie procesora i pamięci RAM.
5. Inne serwery SQL mają statyczny plik stronicowania, w którym ten serwer jest skonfigurowany do samodzielnego zarządzania nim.

Czy ktoś wcześniej napotkał ten problem?

Na sp_BlitzErik pobiegłem

EXEC dbo.sp_BlitzFirst @SinceStartup = 1;

Dając mi te wyniki.

Dużo się tu dzieje, a większość z nich jest dość szeroka i niejasna.

1. 2008R2 RTM ukazało się 21 kwietnia 2010 r. Całkowicie nie ma wsparcia. Priorytetem będzie skorzystanie z najnowszego dodatku Service Pack, który ukazał się około 3 lata temu. W ten sposób będziesz chroniony, jeśli trafisz na dziwny błąd lub coś. Udaj się tutaj, aby dowiedzieć się, co musisz pobrać.

2. Ponieważ dodałeś vCPU (od 1 do 4) i nie zmieniłeś żadnych ustawień, twoje zapytania mogą teraz przebiegać równolegle. Wiem, że to brzmi, jakby wszyscy byli szybsi, ale poczekaj!

3. Być może dodałeś pamięć RAM, ale nie mogłeś zmienić Max Server Memory, aby Twój serwer mógł z niej skorzystać.

4. Dowiedz się, na co czeka Twój serwer. Projekt open source, nad którym pracuję, zapewnia bezpłatne skrypty, które pomogą Ci zmierzyć SQL Server. Udaj się tutaj, jeśli chcesz spróbować.

Będziesz chciał chwycić sp_BlitzFirst, aby sprawdzić statystyki czekania serwera. Możesz uruchomić go na kilka sposobów.

To pokaże ci, na co czekał Twój serwer od momentu uruchomienia.

EXEC dbo.sp_BlitzFirst @SinceStartup = 1;

To pokaże, na jakie zapytania czekają teraz, podczas 30-sekundowego okna.

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

Gdy zorientujesz się, na jakie zapytania czekają (jest tam mnóstwo rzeczy o statystykach oczekiwania), możesz zacząć wprowadzać zmiany, aby uzyskać kontrolę.

Jeśli widzisz, że czekają CXPACKET, oznacza to, że twoje zapytania idą równolegle i być może nadepną. Jeśli trafisz w to, prawdopodobnie zechcesz rozważyć podwyższenie progu kosztu równoległości do 50, a może obniżenie MAXDOP do 2.

Po tym kroku jest to, gdy chcesz użyć czegoś takiego jak sp_WhoIsActive lub sp_BlitzWho (ten ostatni znajduje się w repozytorium GitHub wcześniej), aby rozpocząć przechwytywanie planów zapytań. Oprócz statystyk czekania są jedną z najważniejszych rzeczy, na które możesz patrzeć, aby dowiedzieć się, co się dzieje.

Możesz także przeczytać ten artykuł Jonathana Kehayiasa na temat liczników VMWare, aby sprawdzić w związku z programem SQL Server.

Aktualizacja

Sprawdzanie statystyk oczekiwania i chłopca są dziwne. Z pewnością jest coś nie tak z procesorami. Twój serwer w większości siedzi znudzony, ale kiedy się nagrzewa, robi się źle. Spróbuję to łatwo zepsuć.

1. Jesteś uderzenie czekać trucizny o nazwie THREADPOOL. Nie masz jej mnóstwo, ale ma to sens, ponieważ twój serwer nie jest strasznie aktywny. Wyjaśnię dlaczego za minutę.

2. Masz naprawdę długie średnie oczekiwania na SOS_SCHEDULER_YIELDi CXPACKET. Jesteś na maszynie wirtualnej, więc musisz upewnić się, że SQL Server ma zastrzeżenia lub że pole nie jest strasznie nadmiernie subskrybowane. Hałaśliwy sąsiad może naprawdę zepsuć ci dzień tutaj. Będziesz także chciał się upewnić, że serwer / gość VM / host VM nie działa w trybie zrównoważonej energii. Powoduje to, że procesory obracają się do niepotrzebnie niskich prędkości i nie od razu wracają do pełnej prędkości.

3. Jak się wiążą? Przy 4 procesorach masz 512 wątków roboczych. Pamiętaj, że masz tę samą ilość z jednym procesorem, ale teraz, gdy twoje zapytania mogą być równoległe, mogą zużywać o wiele więcej wątków roboczych. W twoim przypadku 4 wątki na równoległą gałąź zapytania równoległego.

Co dzieje się równolegle? Najprawdopodobniej wszystko. Domyślny próg kosztu dla równoległości wynosi 5. Liczba ta została ustalona jako domyślna pod koniec lat 90. XX wieku podczas pracy na komputerze wyglądającym tak .

To prawda, że ​​Twój sprzęt jest mniejszy niż większość laptopów, ale wciąż jesteś o krok przed tym.

Kiedy pojawia się wiele równoległych zapytań, wyczerpują się wątki robocze. Kiedy tak się dzieje, zapytania po prostu czekają na wątki. Tam też SOS_SCHEDULER_YIELDpojawia się pytanie. Zapytania wychodzą z procesorów i nie wracają przez długi czas. Nie widzę żadnych blokujących oczekiwań, więc najprawdopodobniej po prostu wszyscy jesteście wypchani czekaniami na paralelizm wewnątrz zapytania.

Co możesz zrobić?

1. Upewnij się, że nic nie jest w trybie zrównoważonej mocy
2. Zmień MAXDOP na 2
3. Zmień próg kosztów dla równoległości na 50
4. Postępuj zgodnie z powyższym artykułem Jona K., aby sprawdzić poprawność kondycji VM
5. Użyj wywoływanego skryptu, sp_BlitzIndexaby wyszukać brakujące żądania indeksu.

Aby uzyskać dokładniejsze rozwiązywanie problemów, zapoznaj się z oficjalnym dokumentem, który napisałem dla Google na temat doboru sprzętu w chmurze.

Mam nadzieję że to pomoże!

Tak! Tego typu sytuacja wystąpiła w programie SQL Server vms w naszej farmie serwerów. Sprawdź czas gotowości procesora hosta VM i liczniki sterownika balonu pamięci. CZAS GOTOWOŚCI PROCESORA - BLOG CZĘŚĆ I i zrozumienie balonowania VMware Praca z moim sysadminem była kluczowa, ale nie była łatwa ...

Jedną z rzeczy, których nie zauważyłem, jest to, że dodanie vCPU do maszyny wirtualnej bardzo często może spowolnić ją z powodu planowania.

Podstawową ideą jest to, że jeśli maszyna wirtualna ma 4 jednostki vCPU, hiperwizor musi poczekać na dostępność 4 rdzeni fizycznych, aby zaplanować wszystkie jednostki vCPU, nawet jeśli 3 z nich są bezczynne.

Jeśli nie masz dużo rdzeni na hoście, a inne obciążenia są zajęte, może to spowodować dodatkowe oczekiwanie i znaczny spadek wydajności.

W VMware ESXi można to zobaczyć na zaawansowanych wykresach za pośrednictwem CPU Ready.

Oto jeden z wielu artykułów z prawdziwym przykładem tego, jak to się dzieje i jak zostało zdiagnozowane .

Dodanie większej ilości pamięci RAM może również spowodować nagły spadek wydajności, jeśli przydział pamięci RAM maszyny wirtualnej jest większy niż węzeł NUMA.

Ponadto konfiguracja procesorów vCPU (vSockets vs. vCores) może faktycznie wpływać na niektóre aplikacje, takie jak serwer SQL. Wynika to z faktu, że sam SQL Server jest świadomy NUMA (aby uniknąć tego samego rodzaju spadku wydajności obejmującego NUMA) i dlatego, że VMware może prezentować wirtualne węzły NUMA w różny sposób.

Jest to opisane w poście na blogu na własnej stronie VMware .

Biorąc to pod uwagę, cieszę się, że rozwiązałeś problemy z pomocą Erika, ale możesz chcieć spojrzeć i rozważyć te rzeczy.

Tylko mała pomoc (nie mogę tego dodać jako komentarza) kontynuując odpowiedź @ sp_BlitzErik, otrzymałem kilka zapytań z Pinalem i Maxem Vernonem (nie pamiętam gdzie), które mówią, ile MAXDOP powinieneś użyć:

/*************************************************************************
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));