Jak zwiększyć wydajność dziewiczych zapytań w MS SQL Server?

Mam witrynę sieci Web ASP.NET, która wykonuje własne buforowanie danych, a dane nie zmieniają się przez długi czas, więc nie trzeba ponownie wysyłać zapytań do programu SQL Server za pomocą tego samego zapytania. Muszę poprawić wydajność pierwszych zapytań (dziewiczych), które trafiają do tego programu SQL Server. Niektóre zapytania przetwarzają tak dużo danych, że mogą powodować użycie programu SQL Server tempdb. Nie używam zmiennych tabeli tymczasowej ani tabel tymczasowych, więc SQL Server sam decyduje się na użycie tempdb, gdy tylko zajdzie taka potrzeba.

Mój rozmiar bazy danych wynosi 16 GB, mam 32 GB fizycznej pamięci RAM na moim serwerze.

Rozumiem, że strategia buforowania MS SQL Server próbuje utrzymać dane w pamięci RAM, aby przyspieszyć wydajność podobnych zapytań, jeśli potrzebują one ponownie załadować te same dane. Oprócz tego spróbuje użyć dostępnej pamięci RAM zamiast tempdb w celu przyspieszenia wydajności bez powodowania dostępu do dysku.

Podejrzewam, że gdy pojawia się zapytanie, które musi przechowywać coś w tempdb SQL Server i nie ma wystarczającej ilości pamięci RAM, SQL Server ma 2 możliwości:

1) aby zwolnić niektóre buforowane dane i użyć oszczędzonej pamięci RAM zamiast tempdb, aby uniknąć zapisywania na dysku

2) zachowuj buforowane dane dla przyszłych zapytań i zacznij używać tempdb, co powoduje, że zapis spowalnia dysk.

Nie wiem, jakiego wyboru dokona SQL Server w tej sytuacji, ale chciałbym, aby dokonał wyboru nr 1, ponieważ zależy mi tylko na wydajności pierwszych zapytań (dziewiczych), ponieważ nigdy więcej nie wysyłam tego samego zapytania do SQL Server (chociaż mogę wysłać podobne zapytanie).

Jaka jest strategia buforowania programu SQL Server dla tego scenariusza?

W jaki sposób równoważy użycie pamięci RAM między unikaniem tempdb dla pierwotnych zapytań a szybkością drugiego zapytania?

Czy można skonfigurować program SQL Server w taki sposób, aby dokonał wyboru nr 1? Jeśli tak to jak?

Jak inaczej mogę zwiększyć wydajność wszystkich dziewiczych zapytań SQL?

Ponieważ nie znam strategii buforowania programu SQL Server, chcę umieścić bazę danych na dysku RAM. Zapewni to, że każde pierwotne zapytanie ma wysoką prędkość ładowania niebuforowanych danych, nawet jeśli SQL Server zawsze dokonuje wyboru nr 1. Ryzyko polega na tym, że SQL Server może zacząć używać więcej tempdb z mniej dostępną pamięcią RAM (pozostało tylko 16 GB po użyciu 16 GB dla dysku RAM), jeśli nadal wybierze numer 2, co spowolni te dziewicze zapytania, które powodują rozlanie tempdb.

Interesuje mnie rozwiązanie dla SQL 2008 R2, ale chyba jest tak samo dla SQL 2008, SQL 2005 i może to być SQL 2000.

Wyjaśnienia:

Na tym urządzeniu nie działają żadne inne aplikacje, jest on dedykowany dla SQL Server . Strona internetowa działa na osobnym polu.

Jest to SQL Server 2008 R2 Standard Edition 64-bitowy w systemie Windows Server 2008 R2 Enterprise 64-bitowy.

Uruchamiam tylko zapytania tylko do odczytu, a baza danych jest ustawiona tylko do odczytu .

Załóżmy, że istnieją już dobre indeksy . To pytanie dotyczy SQL Server dokonującego wyboru nr 1 vs. wyboru nr 2, jak to zrobić, czy istnieje sposób, aby to kontrolować i czy Dysk RAM pomaga w dokonaniu właściwego wyboru w przypadku dziewiczych zapytań.

Twoje pytanie można w zasadzie przeformułować jako „Jak działa przyznawanie pamięci zapytań?”. Dobra lektura na ten temat to Zrozumienie przyznawania pamięci serwera SQL . Przed uruchomieniem zapytania może być konieczne przyznanie pamięci na sortowanie i mieszanie oraz inne operacje wymagające pamięci. Przydział pamięci jest wartością szacunkową . Na podstawie aktualnego stanu systemu (liczba uruchomionych i oczekujących żądań, dostępna pamięć itp.) System przyznaje kwerendie przydział pamięci do wymaganej ilości. Po przyznaniu pamięci kwerenda rozpoczyna wykonywanie (może potrzebować poczekać w przerażającej kolejce „semafor zasobów”, zanim otrzyma przydział). Przy wykonywaniu gwarantowane jest przyznanie pamięci przez system. Tę ilość pamięci można współdzielić ze stronami danych (ponieważ zawsze można je opróżnić na dysk), ale nigdy przy innym użyciu pamięci (tzn. Nie można jej „ukraść”). Kiedy więc zapytanie zacznie pytać o przyznaną pamięć od jego przyznania, silnik wdroży coś, co nazwiesz „strategią nr 1”: strony danych mogą zostać eksmitowane (opróżnione, jeśli są brudne), aby nadać zapytaniu obiecaną pamięć. Teraz, jeśli oszacowanie było prawidłowe, a przydział wynosił 100% żądanej pamięci, zapytanie nie powinno się „rozlać”. Ale jeśli oszacowanie było niepoprawne (sprowadza się do szacunków liczności, dlatego podlega nieaktualnym statystykom) lub jeśli zapytanie nie otrzymało całej kwoty, o którą prosiło, zapytanie zostanie „rozlane”. To właśnie wtedy pojawia się tempdb, a wydajność zwykle to czołgi.

Jedynym pokrętłem, które masz do dyspozycji, które kontroluje coś w tym procesie, jest gubernator zasobów . Ponieważ RG można użyć do określenia ustawienia MIN dla puli, można go użyć do zarezerwowania pamięci dla określonego obciążenia, tak aby faktycznie otrzymał przyznanie pamięci, o które prosi. Oczywiście po przeprowadzeniu właściwego dochodzenia, które pokazuje, że winowajcami są zmniejszone granty pamięci , i oczywiście po ocenie wpływu na inne obciążenia. I oczywiście przetestowane.

Teraz wróćmy do twojego pierwotnego pytania. Jeśli twoje dochodzenie jest prawidłowe (bardzo duże, jeśli), chciałbym wskazać dwa problemy:

• uruchamiasz w zapytaniach produkcyjnych, które wymagają przyznania pamięci dla strony internetowej . To wielkie nie-nie. Przydziały pamięci wskazują na zapytania analityczne, w których nie ma miejsca na obsługę żądań HTTP.
• Twoje zapytania prawdopodobnie nie powodują otrzymania żądanego przydziału pamięci. Ponownie, jeszcze więcej nie-nie dla obciążenia krytycznego dla opóźnień, jak strony internetowe.

To mówi mi, że masz podstawowy problem projektowy i architektoniczny. Witryny sieci Web są oparte na opóźnieniach i powinny tworzyć obciążenie podobne do OLTP, bez przydziałów pamięci i bez presji pamięci na zapytania. Nie wspominając o żadnych wyciekach. Zapytania analityczne powinny być uruchamiane w zadaniach offline i przechowywać wstępnie przetworzone wyniki w celu zapewnienia ich szybkiej dostępności, gdy żądają tego żądania HTTP.

Nie wspomniałeś o tym, jakie zapytania są uruchamiane względem bazy danych i czy istnieją odpowiednie indeksy, aby przyspieszyć wydajność twoich zapytań.

Musisz także upewnić się, czy na tym samym urządzeniu działają inne aplikacje. Mimo że pudełko ma 32 GB pamięci RAM, ustaw dowolne ustawienie maksymalnej pamięci na serwerze bazy danych, aby ustawić dowolny sztuczny limit. Jeśli na tym samym serwerze działają aplikacje, wówczas SQL i inne aplikacje mogą konkurować o zasoby i zauważ, że SQL jest bardzo wymagający pamięci.

SQL Server użyje tempdb do wewnętrznego sortowania lub łączenia skrótów / agregatów lub operatorów buforowania itp. I nie można kontrolować tego zachowania. Możesz ograniczyć ilość zwracanych danych.

Czy sprawdziłeś statystyki oczekiwania w tym polu? Za każdym razem, gdy SQL Server czeka na zasób, SQL Server śledzi zasób oczekiwania i analizowanie tych informacji pomaga.

Spójrz na zapytania diagnostyczne Glenn Berry, a to będzie dobry początek.

Zobacz także PARAMETERIZATION FORCED, jak wspomniano w http://weblogs.sqlteam.com/dang/archive/2009/06/27/Forced-Parameterization-A-Turbo-Button.aspx

To pytanie brzmi obecnie jak rozwiązanie szukające problemu. Zdecydowałeś, że dyskiem RAM jest rozwiązanie i chcesz, aby ktoś potwierdził ten wybór. Przepraszam, nie nastąpi.

Jeśli zmierzyłeś i zaobserwowałeś wyciek do tempdb, prawie na pewno będzie to spowodowane operacją sortowania lub mieszania i niedostatecznym przyznaniem pamięci zapytania. W zależności od ilości danych, które mają być przetwarzane, może to być nieuniknione, ale istnieje duże prawdopodobieństwo, że zapytanie i / lub indeksacja mogą zostać ulepszone, aby tego uniknąć.

Rzuć okiem na Zarządzanie buforami, aby lepiej zrozumieć, w jaki sposób SQL Server zarządza pamięcią, oraz SQL Server Zarządzanie pamięcią Objaśniono niektóre podstawowe narzędzia i zapytania DMV, aby zrozumieć, gdzie przydzielona jest pamięć.

Jak inaczej mogę zwiększyć wydajność wszystkich dziewiczych zapytań SQL?

To jest duży temat. Opublikuj zapytanie i zaplanuj, a otrzymasz ukierunkowaną informację zwrotną.