Jak rozwiązywać problemy z blokowaniem spowodowane przez blokady kompilacji

Dotyczy: Microsoft SQL Server 2005 Standard EditionMicrosoft SQL Server 2005 Developer EditionMicrosoft SQL Server 2005 Enterprise Edition

Podsumowanie


W Microsoft SQL Server, tylko jedna kopia planu procedura składowana jest zazwyczaj w pamięci podręcznej w czasie. Wymuszanie to wymaga serializacji niektórych części procesu kompilacji, a synchronizacja jest realizowane w części za pomocą blokad kompilacji. Jeśli wiele połączeń jednocześnie uruchomione tej samej procedury przechowywanej i blokada kompilacji muszą być uzyskane dla tej procedury przechowywanej za każdym razem, gdy jest on uruchamiany, identyfikatory sesji (SPIDs) może rozpocząć się zablokować siebie, ponieważ każdy próbuje uzyskać blokadę wyłączności kompilacji na Obiektu.

Poniżej przedstawiono niektóre typowe cechy kompilacji blokowania, które można zaobserwować w danych wyjściowych blokowania:

  • waittype dla zablokowanych i (zwykle) blokowanie Spids sesji jest LCK_M_X (wyłączne) i waitresource ma postać "Object: DBID:object_id [[Compile]]," gdzie "object_id" jest identyfikatorem obiektu procedury przechowywanej.
  • Blokery mają waittype null, stan można runnable. Blockees mają waittype LCK_M_X (blokada wyłączności), stan uśpienia.
  • Mimo że czas trwania zdarzenia blokowania może być długa, nie ma żadnych pojedynczy identyfikator SPID, który blokuje inne identyfikatory SPIDs przez długi czas. Istnieje blokowanie toczenia. Jak tylko jedna kompilacja jest zakończona, inny identyfikator SPID przejmuje rolę Blocker głowy na kilka sekund lub mniej, i tak dalej.

Następujące informacje są z migawki sys. dm_exec_requests podczas tego rodzaju blokowania:

   session_id  blocking_session_id  wait_type  wait_time  waitresource   ----------  -------------------  ---------  ---------  ----------------------------      221          29                  LCK_M_X    2141       OBJECT: 6:834102 [[COMPILE]]   228          29                  LCK_M_X    2235       OBJECT: 6:834102 [[COMPILE]]    29         214                  LCK_M_X    3937       OBJECT: 6:834102 [[COMPILE]]    13         214                  LCK_M_X    1094       OBJECT: 6:834102 [[COMPILE]]    68         214                  LCK_M_X    1968       OBJECT: 6:834102 [[COMPILE]]   214           0                  LCK_M_X       0       OBJECT: 6:834102 [[COMPILE]] 

W kolumnie waitresource ("6:834102"), 6 jest identyfikatorem bazy danych i 834102 jest identyfikatorem obiektu. Należy pamiętać, że ten identyfikator obiektu należy do procedury przechowywanej, a nie do tabeli.

Więcej informacji


Procedura składowana ponownej kompilacji jest jedno wyjaśnienie dla blokad kompilacji na procedura składowana lub wyzwalacza. Rozwiązaniem w tym przypadku jest zmniejszenie lub wyeliminowanie rekompiluje. Wyjaśnienie najczęstszych powodów, że procedura składowana może być musi być ponownie kompilowana i kilka przydatnych informacji na temat zmniejszania częstotliwości rekompiluje, zobacz następujący artykuł z bazy wiedzy Microsoft Knowledge Base:

243586 Rozwiązywanie problemów z ponowną kompilacją procedury przechowywanej

Dodatkowe scenariusze, które prowadzą do kompilowania blokad:

  1. Procedura składowana jest wykonywana bez w pełni kwalifikowana nazwa
    • Użytkownik, który uruchamia procedura składowana nie jest właścicielem procedury.
    • Nazwa procedury przechowywanej nie jest w pełni kwalifikowana z nazwą właściciela obiektu.

    Na przykład, jeśli użytkownik "dbo" jest właścicielem obiektu dbo. mystoredproc i innego użytkownika, "Harry," uruchamia tę procedurę przechowywaną za pomocą polecenia "exec mystoredproc", wyszukiwanie początkowej pamięci podręcznej przez nazwę obiektu nie powiedzie się, ponieważ obiekt nie jest kwalifikowana właściciela. (Nie wiadomo jeszcze, czy inna procedura przechowywana o nazwie Harry. mystoredproc istnieje. W związku z tym SQL Server nie może być pewien, że buforowanego planu dla dbo. mystoredproc jest poprawne wykonać.) SQL Server następnie uzyskuje blokadę wyłączności kompilacji na procedurę i sprawia, że przygotowania do kompilowania procedury. Obejmuje to rozpoznawanie nazwy obiektu do identyfikatora obiektu. Przed SQL Server kompiluje plan, SQL Server używa tego identyfikatora obiektu do wykonywania bardziej precyzyjne przeszukiwanie pamięci podręcznej procedury i można zlokalizować wcześniej skompilowany plan, nawet bez kwalifikacji właściciela. Jeśli istniejący plan zostanie znaleziony, SQL Server ponownie używa buforowanego planu i faktycznie nie skompilować procedura składowana. Jednak brak właściciela kwalifikacji wymusza SQL Server do drugiego wyszukiwania pamięci podręcznej i uzyskać blokadę wyłączności skompilować przed program określa, że istniejący buforowanych wykonanie planu może być ponownie użyty. Uzyskiwanie blokady i wykonywania wyszukiwań i innych prac, które są potrzebne do osiągnięcia tego punktu można wprowadzić opóźnienie dla blokad kompilacji, która prowadzi do blokowania. Jest to szczególnie ważne, jeśli wielu użytkowników, którzy nie są właścicielem procedury przechowywanej jednocześnie uruchomić procedurę bez podawania nazwy właściciela. Należy pamiętać, że nawet jeśli nie widzisz identyfikatory SPID oczekiwania na kompilowanie blokad, brak kwalifikacji właściciela można wprowadzać opóźnienia w wykonywaniu procedura składowana i spowodować niepotrzebnie wysokie użycie procesora CPU. Następująca sekwencja zdarzeń będą rejestrowane w sesja rozszerzone zdarzenie programu SQL Server po wystąpieniu tego problemu.

    Nazwa zdarzenia Tekst
    rpc_starting mystoredproc
    sp_cache_miss mystoredproc
    sql_batch_starting mystoredproc
    sp_cache_hit mystoredproc
    ... ...

    sp_cache_miss występuje, gdy wyszukiwanie pamięci podręcznej przez nazwę nie powiedzie się, ale następnie pasujące buforowanych plan ostatecznie został znaleziony w pamięci podręcznej po nazwę obiektu niejednoznaczny został rozwiązany do identyfikatora obiektu i jest sp_cache_hit zdarzenia. Rozwiązanie tego problemu blokowania kompilacji jest upewnienie się, że odwołania do procedur przechowywanych są kwalifikowane przez właściciela. (Zamiast exec mystoredproc, użyj exec dbo. mystoredproc.) Podczas kwalifikacji właściciela jest ważne ze względu na wydajność, nie trzeba kwalifikować przechowywane proc z nazwą bazy danych, aby zapobiec dodatkowe wyszukiwania w pamięci podręcznej. Blokowanie, który jest spowodowany przez kompilowanie blokad można wykryć przy użyciu standardowych metod blokowania rozwiązywania problemów.

  2. Procedura składowana jest poprzedzony prefiksami "sp_"

    Jeśli nazwa procedury przechowywanej rozpoczyna się od prefiksu "sp_" i nie znajduje się w wzorca bazy danych, zobacz sp_cache_miss przed trafień w pamięci podręcznej dla każdego wykonywania, nawet jeśli właściciel-kwalifikują się procedura składowana. Jest to spowodowane sp_ prefiks informuje program SQL Server, że procedura składowana jest systemowa procedura składowana i procedury składowane w systemie mają reguły rozpoznawania nazw różnych. (Lokalizacja "preferowane" znajduje się w wzorca bazy danych.) Nazwy utworzonych przez użytkownika procedur przechowywanych nie należy rozpoczynać się od "sp_".

  3. Procedura składowana jest wywoływany przy użyciu innego przypadku (górny/dolny)

    Jeśli procedura kwalifikowana przez właściciela jest wykonywana przy użyciu innego przypadku (Górna lub dolna) z przypadku, który został użyty do utworzenia, procedura może wyzwolić zdarzenie CacheMiss lub zażądać blokady COMPILE. Ostatecznie procedura używa buforowanego planu i nie jest ponownie kompilowana. Ale żądanie blokady COMPILE czasami może spowodować sytuację "Blokowanie łańcucha", jeśli istnieje wiele identyfikatorów SPID, które próbują wykonać tę samą procedurę przy użyciu innego przypadku niż przypadek, który został użyty do jego utworzenia. Jest to prawdą bez względu na porządek sortowania lub sortowanie, które jest używane na serwerze lub w bazie danych. Powodem tego zachowania jest algorytm, który jest używany do znajdowania procedury w pamięci podręcznej jest oparta na wartości mieszania (dla wydajności), a wartości mieszania można zmienić, jeśli sprawa jest inna. Obejście problemu polega na upuszczeniu i utworzeniu procedury przy użyciu tej samej sprawy, która jest używana, gdy aplikacja wykonuje procedurę. Można również upewnić się, że procedura jest wykonywana ze wszystkich aplikacji przy użyciu poprawny przypadek (górny lub dolny).

  4. Procedura składowana jest wywoływana jako zdarzenie języka

    Jeśli użytkownik próbuje wykonać procedurę przechowywaną jako zdarzenie języka, a nie jako RPC, SQL Server należy przeanalizować i skompilować kwerendę zdarzenia języka, określić, że kwerenda próbuje wykonać określonej procedury, a następnie spróbuj znaleźć plan w pamięci podręcznej dla tej procedury. Aby uniknąć tej sytuacji, w którym program SQL Server musi przeanalizować i skompilować zdarzenie języka, upewnij się, że kwerenda jest wysyłana do SQL jako RPC.

    Aby uzyskać więcej informacji, zobacz sekcję "procedury przechowywane w systemie" w artykule Books Online "Tworzenie procedury przechowywanej."

Znane problemy

Oto kilka znanych problemów, które mogą uniemożliwić buforowanie planu:

  • Zmienne BLOB są używane jako parametr procedura składowana. Aby uzyskać więcej informacji, kliknij następujący numer artykułu w celu wyświetlenia tego artykułu z bazy wiedzy Microsoft Knowledge Base:
    2380435 Poprawka: plan kwerend dla procedury przechowywanej nie jest buforowany, jeśli procedura składowana używa zmiennej BLOB i zmienna jest używana w funkcji ciągu w Microsoft SQL Server 2008
  • Użyj Otwórz klucz symetryczny w partia procedura składowana/kwerendy. Aby uzyskać więcej informacji zobacz następujący wpis blogu MSDN: