SQL Server 2012 wolniejszy niż 2008

Przeprowadziłem migrację dużej witryny i bazy danych ze starszego serwera (Windows 2008 / SQL Server 2008/16 GB RAM / 2 x 2,5 GHz Quad Core / SAS) na nowszy, znacznie lepszy serwer (Windows 2008 R2 / SQL Server 2012 SP1 / 64 GB RAM / 2 x 2,1 GHz 16 rdzeni / dysków SSD).

Odłączyłem pliki bazy danych na starym serwerze, skopiowałem je i załączyłem na nowym serwerze. Wszystko poszło bardzo dobrze.

Następnie zmieniłem poziom zgodności na 110, zaktualizowałem statystyki, odbudowałem indeksy.

Ku mojemu ogromnemu rozczarowaniu zauważyłem, że większość zapytań SQL jest znacznie wolniejsza (2-3-4 razy wolniejsza) na nowym serwerze SQL 2012 niż na starym serwerze SQL 2008.

Na przykład w tabeli z około 700 000 rekordów na starym serwerze zapytanie dotyczące indeksu trwało około 100 ms. Na nowym serwerze to samo zapytanie zajmuje około 350 ms.

To samo dzieje się w przypadku wszystkich zapytań.

Byłbym wdzięczny za pomoc tutaj. Daj mi znać, co sprawdzić / zweryfikować. Ponieważ trudno mi uwierzyć, że na lepszym serwerze z nowszym programem SQL Server wydajność jest gorsza.

Więcej szczegółów:

Pamięć jest ustawiona na maks.

Mam tę tabelę i indeks:

CREATE TABLE [dbo].[Answer_Details_23](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserID] [int] NOT NULL,
    [SurveyID] [int] NOT NULL,
    [CustomerID] [int] NOT NULL default 0,
    [SummaryID] [int] NOT NULL,
    [QuestionID] [int] NOT NULL,
    [RowID] [int] NOT NULL default 0,
    [OptionID] [int] NOT NULL default 0,
    [EnteredText] [ntext] NULL,
 CONSTRAINT [Answer_Details_23_PK] PRIMARY KEY NONCLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_Answer_Details_23_SummaryID_QuestionID] ON [dbo].[Answer_Details_23]
(
    [SummaryID] ASC,
    [QuestionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Wykonałem to zapytanie:

set statistics time on;
select summaryid, count(summaryid) from Answer_Details_23 group by summaryid order by count(summaryid) desc;
set statistics time off;

STARY SERWER - Czasy wykonania programu SQL Server: czas procesora = 419 ms, czas, który upłynął = 695 ms.

NOWY SERWER - Czasy wykonania programu SQL Server: czas procesora = 1340 ms, czas, który upłynął = 1636 ms.

PLANY WYKONANIA przesłane tutaj: http://we.tl/ARbPuvf9t8

Późniejsza aktualizacja:

• 16-rdzeniowe procesory AMD 2.1 GHz Opteron wyglądają znacznie gorzej niż czterordzeniowe procesory Intel 2,5 GHz
• Ogromna poprawa zmieniająca opcje zasilania systemu Windows z wyważonego na wysoką
• Dalsza poprawa zmieniająca maksymalny stopień równoległości na 8 i próg kosztów na 4

Teraz czasy wykonania programu SQL Server: czas procesora = 550 ms, czas, który upłynął = 828 ms.

Wciąż jest gorszy niż stary serwer, ale nie jest taki zły. Jeśli masz jakieś inne sugestie (inne niż lokalne optymalizacje zapytań), prosimy o komentarz.

Mam podobne problemy z SQL Server, możliwe, że Twój serwer nie jest optymalnie skonfigurowany. Nowsze Xeony są wyposażone w TurboBoost, HT itp., Które mogą znacząco wpłynąć na wydajność serwera.

Na przykład odnieśliśmy sukces; Konfiguracja o niskim opóźnieniu dla serwerów Dell

Ustawienia będą miały zastosowanie do serwerów innych niż Dell, mogą mieć tylko inne nazwy.

Poprawiliśmy także wydajność, ustawiając profil zarządzania energią systemu Windows na wysoką wydajność, od Balanced. Ostatnim krokiem jest zalecenie zarezerwowania do 8 GB pamięci dla systemu operacyjnego na serwerach x64, domyślna instalacja SQL zajmuje całą pamięć. Możesz spróbować rezerwacji 4/8 GB, ustawiając maksymalną konfigurację pamięci SQL Server na 4/8 GB mniej niż całkowita pamięć.

Jeśli to możliwe, zalecam przywrócenie starego serwera. Jeśli nie masz dostępnych skryptów regresji / automatyzacji / ładowania, najlepszym, co możesz zrobić, jest rejestrowanie aktywności systemu przez 1-4 godziny w okresie wysokiej aktywności. Następnie skonfiguruj serwer WWW taki sam jak produkcyjny i komputer kliencki, aby uruchomić skrypt. Uruchom tę samą aktywność na nowym serwerze, zmień konfigurację i ponownie uruchom tę samą aktywność. Naprawdę chciałbyś zrobić o wiele więcej, ale nie wydaje się, że byłoby to wykonalne i jest poza zakresem tego pytania.

Daj mi znać, co sprawdzić / zweryfikować

Masz problem z wydajnością. Postępuj zgodnie z metodologią rozwiązywania problemów dotyczących wydajności, taką jak oczekiwania i kolejki, aby zidentyfikować wąskie gardło. Połączona metodologia pokazuje, co i jak mierzyć. Opublikuj tutaj wyniki, a my możemy pomóc z konkretnymi poradami na podstawie twoich rzeczywistych pomiarów. Ponieważ jest zbyt otwarty i nikt się nie domyśla. Zawężenie go do konkretnego problemu wyeliminuje zgadywanie.

Po aktualizacji

Plany są zupełnie inne. Stary plan miał niski agregat strumienia na stosie, który faktycznie ma złą szacunkową liczność (141k vs. 108k), a matematyka haszowa dalej niepoprawnie prognozuje, w drugą stronę (35k vs. 108k). Nowy plan nie zawiera agregacji strumienia i zawiera dokładne szacunki aż do samej góry. Oczywiście nie wyjaśnia to, dlaczego stary plan był wykonywany szybciej .

Dolne skany mają nieco inną liczbę wierszy (nieistotną), ale dość różne koszty: stare to 2.49884 (IO 2.28979 CPU 0.20905) w porównaniu z nowym 1.59109 (IO 1.53868 CPU 0.0524084). Ponownie wskazałby na lepsze wykonanie w 2012 r. (Przebudowa indeksu prawdopodobnie zmniejszyła fragmentację?).

Bardzo różna jest liczba wątków: 32 w nowych (każdy uzyskuje ~ 23 tys. Wierszy) i 8 w starych (każdy uzyskuje ~ 95 tys. Wierszy). Stół jest dość wąski. To mogło być to, że duża liczba wątków jest rzeczywiście boli wydajności ze względu na o wiele częstsze unieważnień pamięci podręcznej . Spróbowałbym:

1. wyeliminuj HyperThreading w nowej konfiguracji serwera (jeśli istnieje) i / lub
2. wypróbuj zapytanie z DOP 8.

Zauważyłem twój komentarz:

Dodano plan wykonania z maxdop 8 Zapytanie jest w ten sposób szybsze

Prawdopodobnie są to po prostu procesory nadepnące na siebie. Po zainstalowaniu dysków SSD IO jest prawie niczym, a stół jest zdecydowanie zbyt mały, aby zagwarantować 32 skanery. Ta zamiana wymiany prawdopodobnie unieważnia L1 / L2.

W przypadku większości współczesnych systemów wielordzeniowych, a szczególnie systemów wielordzeniowych, architektura sprzętowa jest taka, że ​​pewne części pamięci są dalekie od niektórych rdzeni / procesorów, a niektóre części pamięci są zbliżone do niektórych rdzeni / procesorów. Jest to określane jako architektura pamięci niejednorodnej lub w skrócie NUMA. Chcesz, aby ustawienie MAXDOP było zgodne z liczbą rdzeni na węzeł NUMA, aby zminimalizować liczbę przypadków, w których dany węzeł numa musi wyjść poza swoją pamięć na dane.

Możesz użyć poniższych opcji, aby sprawdzić konfigurację nowego komputera i upewnić się, że MAXDOP jest ustawiony na najlepsze ustawienie, pod względem sprzętowym :

DECLARE @CPUs int;
DECLARE @NumaNodes int;
DECLARE @ServerRAMInMB int;

SET @ServerRAMinMB = (SELECT (i.physical_memory_kb / 1024) AS ServerMemory 
    FROM sys.dm_os_sys_info i);
SET @CPUs = (SELECT i.cpu_count from sys.dm_os_sys_info i);
SET @NumaNodes = (SELECT MAX(c.memory_node_id) + 1 FROM sys.dm_os_memory_clerks c 
    WHERE memory_node_id < 64);

SELECT @ServerRamInMB, @CPUs, @NumaNodes;

IF @CPUs > 4 /* this would be 4 cores, not 4 CPUs */
BEGIN
    DECLARE @MaxDOP int;
    SET @MaxDOP = @CPUs * 0.75;
    IF @MaxDOP > (@CPUs / @NumaNodes) SET @MaxDOP = (@CPUs / @NumaNodes);
    EXEC sp_configure 'max degree of parallelism', @MaxDOP;
    EXEC sp_configure 'cost threshold for parallelism', 4; 
END

I obejmował @ServerRamInMBparametru tutaj ponieważ używam go do instalacji na Max Server Memoryi Min Server Memoryopcji konfiguracyjnych z wartościami, które są odpowiednie dla danego serwera.

W jakim trybie edycji i licencjonowania jesteś? Prawdopodobnie nie używasz wszystkich rdzeni. Zobacz notatkę na tej stronie - http://msdn.microsoft.com/en-us/library/ms143760.aspx

„Licencja Enterprise Edition z licencją Server + Client Access License (CAL) jest ograniczona do maksymalnie 20 rdzeni na instancję programu SQL Server”.

Miałem ten sam problem, co opisany na tej stronie: zmiana ustawień mocy z „zbalansowanej” na „wysoką wydajność” zrobiła ogromną różnicę - ponad dwukrotnie wydłużając czasy reakcji. Teraz, gdy korzystamy z dysków SSD, nie sądzę, aby problem mógł być związany z zużyciem energii.

Przeszedłem również ten problem przez co najmniej 2 tygodnie bez solidnego rozwiązania, zamiast mylić jeden problem z drugim.

Wreszcie rezolucja jest następująca:

1. Zresetowałem kompatybilność z 010 na 011

2. Zresetuj także zgodność głównej bazy danych. Domyślnie sql zachowa stare ustawienie zgodności. Musimy to zmienić ręcznie.

Wszystkiego najlepszego