Nadal źle, aby uruchomić nazwę procedury składowanej przez użytkownika za pomocą sp_?

Jeden z moich współpracowników nazwał procedurę przechowywaną w naszej bazie danych SQL Server 2008 R2 sp_something. Kiedy to zobaczyłem, od razu pomyślałem: „To jest ŹLE!” i zacząłem przeszukiwać moje zakładki do tego artykułu online, który wyjaśnia, dlaczego jest on niepoprawny, aby móc wyjaśnić mojemu współpracownikowi.

W artykule (autorstwa Briana Morana ) wyjaśniono, że nadanie procedurze składowanej prefiksu sp_ powoduje, że SQL Server patrzy na główną bazę danych w poszukiwaniu skompilowanego planu. Ponieważ tam sp_sprocnie ma, SQL Server ponownie skompiluje procedurę (i potrzebuje do tego wyłącznej blokady kompilacji, powodując problemy z wydajnością).

Poniższy przykład podano w artykule, aby pokazać różnicę między dwiema procedurami:

USE tempdb;
GO

CREATE PROCEDURE dbo.Select1 AS SELECT 1;
GO

CREATE PROCEDURE dbo.sp_Select1 AS SELECT 1;
GO

EXEC dbo.sp_Select1;
GO

EXEC dbo.Select1;
GO

Uruchom to, następnie otwórz Profiler (dodaj SP:CacheMisszdarzenie Procedury składowane -> zdarzenie) i ponownie uruchom procedury składowane. Powinieneś zobaczyć różnicę między dwiema procedurami przechowywanymi: procedura sp_Select1przechowywana wygeneruje jeszcze jedno SP:CacheMisszdarzenie niż Select1procedura przechowywana (artykuł odwołuje się do SQL Server 7.0 i SQL Server 2000 ).

Po uruchomieniu przykładu w moim środowisku SQL Server 2008 R2 otrzymuję taką samą ilość SP:CacheMiss zdarzeń dla obu procedur (zarówno w tempdb, jak i w innej testowej bazie danych).

Zastanawiam się więc:

• Czy mogłem zrobić coś złego, wykonując przykład?
• Jest „nie nazywaj użytkownika sproc sp_something adagium „nie nazywaj ” jest nadal aktualne w nowszych wersjach SQL Server?
• Jeśli tak, to czy istnieje dobry przykład, który pokazuje jego ważność w SQL Server 2008 R2?

Wielkie dzięki za twoje przemyślenia na ten temat!

EDYTOWAĆ

Znalazłem tworzenie procedur przechowywanych ( aparat bazy danych) w msdn dla SQL Server 2008 R2, który odpowiada na moje drugie pytanie:

Zalecamy, aby nie tworzyć żadnych procedur przechowywanych, używając sp_ jako prefiksu. SQL Server używa przedrostka sp_ do oznaczenia procedur przechowywanych w systemie. Wybrana nazwa może być sprzeczna z niektórymi przyszłymi procedurami systemowymi. [...]

Nie wspomniano jednak o problemach z wydajnością spowodowanych użyciem sp_prefiksu. Chciałbym wiedzieć, czy nadal tak jest, czy naprawili to po SQL Server 2000.

Jest to dość łatwe do przetestowania. Stwórzmy dwie bardzo proste procedury:

CREATE PROCEDURE dbo.sp_mystuff
AS
  SELECT 'x';
GO
CREATE PROCEDURE dbo.mystuff
AS
  SELECT 'x';
GO

Teraz zbudujmy opakowanie, które wykonuje je wiele razy, z prefiksem schematu i bez niego:

CREATE PROCEDURE dbo.wrapper_sp1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    WHILE @i <= 1000
    BEGIN
      EXEC sp_mystuff;
      SET @i += 1;
    END
END
GO
CREATE PROCEDURE dbo.wrapper_1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    WHILE @i <= 1000
    BEGIN
      EXEC mystuff;
      SET @i += 1;
    END
END
GO
CREATE PROCEDURE dbo.wrapper_sp2
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    WHILE @i <= 1000
    BEGIN
      EXEC dbo.sp_mystuff;
      SET @i += 1;
    END
END
GO
CREATE PROCEDURE dbo.wrapper_2
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    WHILE @i <= 1000
    BEGIN
      EXEC dbo.mystuff;
      SET @i += 1;
    END
END
GO

Wyniki:

Wnioski:

• używanie przedrostka sp_ jest wolniejsze
• pomijanie prefiksu schematu jest wolniejsze

Ważniejsze pytanie: dlaczego chcesz używać przedrostka sp_? Co twoi współpracownicy spodziewać się przyrostu od tego? Nie powinno to oznaczać, że musisz udowodnić, że jest gorzej, powinno to być uzasadnienie dodania tego samego trzyliterowego prefiksu do każdej procedury przechowywanej w systemie. Nie widzę korzyści.

Przeprowadziłem też dość obszerne testy tego wzorca w następującym poście na blogu:

http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix

Zalecamy, aby nie tworzyć żadnych procedur przechowywanych, używając sp_ jako prefiksu. SQL Server używa przedrostka sp_ do oznaczenia procedur przechowywanych w systemie. Wybrana nazwa może być sprzeczna z niektórymi przyszłymi procedurami systemowymi. [...]

Nie wspomniano jednak o problemach z wydajnością spowodowanych użyciem przedrostka sp_. Chciałbym wiedzieć, czy nadal tak jest, czy naprawili to po SQL Server 2000.

Jak pokazuje prosty komentarz Martina Smitha - tak, jeśli masz procedurę składowaną z sp_prefiksem - SQL Server wykonawca zapytania zawsze najpierw sprawdza w masterbazie danych, czy istnieje procedura przechowywana (oznaczona jako systemowa procedura przechowywana) o tej nazwie.

A jeśli istnieje, ta procedura przechowywana w systemie z masterbazy danych zawsze ma pierwszeństwo i zostanie wykonana zamiast własnej.

Więc tak - to wciąż stoi: nie korzystać z sp_prefiksu.

Lepszym testem jest napisanie zapytania, które wymaga pełnej optymalizacji, ponieważ jest to prawdopodobnie lepsze odzwierciedlenie tego, co piszesz. Zawarłem następujące zapytanie w SP i powtórzyłem test i uzyskałem te same wyniki.

select * from Person.BusinessEntity b
inner join Person.BusinessEntityAddress ba on b.BusinessEntityID = ba.BusinessEntityID
inner join Person.Address a on ba.AddressID = a.AddressID

W obu przypadkach dostałem tę samą liczbę zdarzeń braku i trafienia w pamięci podręcznej, aw obu przypadkach plan został dodany do pamięci podręcznej. Uruchomiłem też oba procy kilka razy i nie było spójnej różnicy w czasie procesora ani w czasie, który upłynął, zgłaszany przez dm_exec_query_stats.

Innym problemem jest to, że ponieważ procy „sp_” mogą być wykonywane z poziomu master, możesz otrzymać kopię proc, która została uruchomiona w master zamiast z DB, w której pracujesz, ale szybki test pokaże, że tak nie jest. Jeśli jednak proces zostanie usunięty z bazy danych, nad którą pracujesz, a kopia istnieje w trybie głównym, zostanie wykonana, co może być problemem, jeśli jest to stara wersja. Jeśli jest to problem, nie użyłbym „sp_” do nazwania proc.

Uważam, że problem musi wystąpić, jeśli nie podasz w pełni kwalifikowanej nazwy obiektu. Zatem „EXEC sp_something” sprawdzi najpierw master, ale „EXEC dbname.dbo.sp_something” nigdy nie przejdzie do master pierwszy.

Lekcja, o ile pamiętam, polega na tym, aby zawsze używać w pełni kwalifikowanej nazwy.