Nadmierne blokowanie kompilacji na sp_procedure_params_90

Ponowne pojawienie się tego pytania MSDN: Raport o zablokowanym procesie: co to jest zasób oczekiwania „OBJECT: 32767: 124607697: 0 [COMPILE]”

Złapałem te stwierdzenia w programie Profiler. Wszystkie mają czas trwania powyżej 3 sekund. Niektóre powyżej 10+. Działanie blokujące jest takie samo jak łącze z MSDN .

Wszystkie połączenia wykorzystują nazewnictwo 3 części. Wszystkie określają inny proc w formie, które wyglądają następująco:

exec [db1].[sys].sp_procedure_params_90_rowset N'proc1', 1, NULL, NULL
exec [db2].[sys].sp_procedure_params_90_rowset N'proc2', 1, NULL, NULL
exec [db3].[sys].sp_procedure_params_90_rowset N'proc3', 1, NULL, NULL
exec [db4].[sys].sp_procedure_params_90_rowset N'proc4', 1, NULL, NULL

Co mogę zrobić, aby zmniejszyć ten poziom blokowania?

(edytuj) Teraz widzę to samo dla:

exec [db1].[sys].sp_primary_keys_rowset N'view1', N'dbo'
exec [db2].[sys].sp_primary_keys_rowset N'view1', N'dbo'
exec [db3].[sys].sp_primary_keys_rowset N'view1', N'dbo'
exec [db4].[sys].sp_primary_keys_rowset N'view1', N'dbo'

Dzieje się coś systemowego, ale nie wiem, co jeszcze zrobić. dzwoniącym jest VB6 przez ADO. ADO wykonuje te połączenia.

Przykładowy raport o zablokowanym procesie znajduje się poniżej

 <blocked-process-report>
    <blocked-process>
        <process
            id="process5bc1288"
            taskpriority="0"
            logused="0"
            waitresource="OBJECT: 32767:124607697:0 [COMPILE]"
            waittime="28887"
            ownerId="11638114050"
            transactionname="sqlsource_transform">
            <executionStack>
                <frame
                    line="1"
                    sqlhandle="0x000000000000000000000000000000000000000000000000">
                    <sqltext>EXEC [dbo].[spAlertDetectByPoll] ':V:^RMAlert^:Z:^&amp;N&amp;#RMAlert#&amp;S&amp;#L#&amp;UID&amp;#19#&amp;AGN&amp;#1#&amp;DFC&amp;#103#^', 1</sqltext>
                </frame>
            </executionStack>
            <inputbuf>
SET NO_BROWSETABLE OFF   </inputbuf>
        </process>
    </blocked-process>
    <blocking-process>
        <process
            status="suspended"
            waitresource="OBJECT: 32767:124607697:0 [COMPILE]"
            waittime="35693"
            spid="1121"
            sbid="0"
            ecid="0"
            priority="0"
            trancount="0"
            lastbatchstarted="2013-12-16T14:45:48.960">
            <executionStack>
                <frame
                    line="1"
                    sqlhandle="0x000000000000000000000000000000000000000000000000" />
            </executionStack>
            <inputbuf>
SET NO_BROWSETABLE OFF   </inputbuf>
        </process>
    </blocking-process>
</blocked-process-report>

sql-server-2008-r2 blocking

— Dan Holmes
źródło

Czy masz najnowszy dodatek Service Pack i aktualizacje zbiorcze dla programu SQL Server 2008 R2?

— Max Vernon,

SP2 CU4 Microsoft SQL Server 2008 R2 (SP2) - 10.50.4270.0 (X64)

— dan holmes

Kiedy to się zaczęło? Czy ostatnio zastosowałeś dodatek Service Pack lub aktualizację zbiorczą? Wspieram również VB6 / ADO i przypominam sobie, że te procesy systemowe pojawiały się raz lub dwa razy, ale nie sądzę, żeby wystąpił problem z blokowaniem. Myślę, że wymyślili, bo są tak często dzwonieni. Modlę się, żeby to nie było związane z SP / CU, ponieważ wciąż jesteśmy na 10.50.2500, i byłoby śmiercią, gdyby te rzeczy zaczęły zajmować 3-10 sekund każda.

— Jon Seigel,

umieścił jedną z wielu w pastbin pastebin.com/4wUgzby9 . trwa to około 2 lub 3 tygodni. Dawno nie stosowaliśmy CU. Stało się to na początku 2012 r. Po raz pierwszy według daty MSDN.

— dan holmes,

to może być tylko objaw. Regularnie czekam na RESOURCE_SEMAPHORE_QUERY_COMPILE. Oto najlepsze podejście do tego typu oczekiwania, jakie znalazłem: blogs.msdn.com/b/support_sql_france/archive/2012/02/07/...

— dan holmes

Jest doskonały post na blogu http://blogs.msdn.com/b/support_sql_france/archive/2012/02/07/sql-server-compilation-gateways-and-resource-semaphore-query-compile.aspx, który wyjaśnia, co jest wydarzenie.

SQL Server pozwala na ustalenie liczby kompilacji na podstawie ich złożoności. Grupuje je w małe, średnie i duże. W przypadku dużych kompilacji może istnieć tylko jedna kompilacja na raz, więc powiedzmy, że wszystkie Twoje procesy są uważane za duże, a następnie każda musi być kompilowana szeregowo. To może tłumaczyć blokowanie.
Myślę, że może istnieć kilka podejść do problemu - rozważ więcej zasobów (więcej procesorów pozwoli na jednoczesne współdziałanie większej liczby małych i średnich zapytań lub może przekroczyć próg dla tego, co jest uważane za średnie). Ponadto więcej pamięci może rozwiązać problem.

Jeśli jesteś podobny do większości z nas, może to nie być możliwe. Inną opcją może być przejrzenie połączeń ADO i sprawdzenie, czy liczba połączeń może zostać zmniejszona lub rozłożona, aby nie wszystkie połączenia odbywały się jednocześnie. Zmniejszenie liczby w dowolnym momencie powinno skrócić czas oczekiwania.

Jeśli to nie zadziała, zastanów się nad ustaleniem „kompatybilności” przechowywanych procesów. Może rozbić je na mniejsze części, co może zredukować je do małych lub średnich segmentów i umożliwić więcej równoległych kompilacji. Lub określ, dlaczego procy muszą być za każdym razem rekompilowane. Sprawdź, czy można je przepisać tak, że nie trzeba ich ponownie kompilować. Na koniec rozważę skorzystanie z Przewodników po planach. Umożliwi to prekompilację procesorów i może zaoszczędzić trochę czasu.

Mam nadzieję, że to pomaga

— paulbarbin
źródło

Nadmierne blokowanie kompilacji na sp_procedure_params_90_rowset