Хранимые процедуры с префиксом sp_ и производительность

Хранимые процедуры с префиксом sp_ и производительностьСуществует множество мнений относительно использования/не использования префикса sp_ для хранимых процедур. В пользу не использования обычно приводят аргумент, что sp_ это префикс для системных хранимых процедур и может возникнуть путаница. А в пользу использования, что путаница не возникнет, т.к. какие могут быть системные хранимые процедуры в пользовательской базе данных. Заблуждение и первых, и вторых в том, что использование/не использование sp_, это не вопрос стиля именования. Это вопрос производительности.
Главная проблема в том, что использование префикса sp_ в базах отличных от master приводит к следующей проблеме. Если оптимизатор встречает хранимую процедуру с префиксом sp_ от сначала проверяет, а нет ли такой системной, и если ее находит, то игнорирует вашу, а берет системную. Это приводит к двум проблемам:

  • Лишняя проверка требует дополнительных затрат
  • Существует довольно большая вероятность, что ваша план для такой хранимой процедуры не будет кэшироваться.

Рассмотрим более подробно:

Типы системных объектов

Системные объекты поставляться с установкой SQL Server, располагаются в системных базах данных, но могут быть доступны из любых баз данных. В текущих версиях все системные обыекты помечены как поставляемые Microsoft, но в будущих версиях все может измениться.
Их всего 4:

  • System-Stored-Procedure — имя начинается на  sp_ и может быть как обычной хранимой процедурой, так и расширенной (исполняемой вне SQL Server) хранимой процедурой
  • System-Extended-Stored-Procedure — имя начинается на xp_, могут быть как обычными, так и расширенными хранимыми процедурами. Доступны только в базе данных master и/или схеме sys  в других базах.
  • System View — системные представления, живут в схемах INFORMATION_SCHEMA и sys
  • System Function — системные функции (скалярные или табличные), которые видимы в любой базе данных, рекомендовано (странный комментарий для системного объекта) называть их с префиксом fn_

Метод распознавания

И так, почему нужен специальный метод распознавания.

Основная причина в том, что системные объекты должны быть видимыми из пользовательской базы данных, но при этом физически хранятся в системной базе, и не копируются в пользовательскую базу при ее создании. Достаточно логичный подход, т.к. в противном случае, не возможно контролировать эти самые системные объекты. То есть нельзя защитить их от пользователя, а также возникают проблемы с обновлением сервера баз данных, которые иногда затрагивают и системные объекты.

Стоит также упомянуть, что полное имя объекта базы данных состоит из 4-х частей:

[server.] [database.] [schema.] entity

При указании не полного имени, осуществляется распознавание нужного объекта. Самое простое и понятное правило состоит в том, что сервер и база данных всегда берутся текущими, а схема выбирается, как схема по умолчанию для пользователя. В SQL Sever 2000, имя такой схемы совпадает с именем пользователя, но если объект не найдет, то дополнительно идет проверка в схеме dbo. В 2005 имя схемы по умолчанию может быть любым и задается при помощи DEFAULT SCHEMA при создании или обновлении пользователя, если DEFAULT SCHEMA не была задана, то она равна dbo (а не имени пользователя, как было в предыдущих версиях).

Но у каждого правила есть исключения, т.к. не хорошо заставлять писать пользователя master.dbo.sp_who, а не просто sp_who.

Для распознования системных объектов используются следующие правила.

  1. Anydb.dbo.sp_* распознается, как master.dbo.sp_, но выполняется в контексте anydb.

    То есть, если мы выполним следующий код:

    use master
    go
    create procedure sp_my_test
    as
        PRINT 'Execute in master'
    go
    
    use demo
    go
    exec sp_my_test
    go

    То получим ответ «Execute in master», а не ошибку, что хранимой процедуры sp_my_test в базе данных demo нет. Тоже самое будет, если я напишу явно:

    use demo
    go
    exec demo.dbo.sp_my_test
    go

    Но, что будет, если имена будет конфликтовать, то есть, я создам одну и ту же хранимую процедуру в разных базах данных:

    use master
    go 
    create procedure sp_my_test
    as
        PRINT 'Execute in master'
    go 
    
    use demo 
    go
    create procedure sp_my_test
    as
        PRINT 'Execute in demo'
    go
    
    use demo
    go
    exec sp_my_test
    go

    То получу результат: Execute in demo. То есть SQL Server, взял хранимаю процедуру из правильной (текущей баз данных). Но так можно доиграться, и до случайной перегрузки системных процедур.

    use demo
    go
    create procedure sp_who
    as
        PRINT 'Fake sp_who'
    go
    
    exec sp_who
    
    exec demo.dbo.sp_who

    В результате, мы получит список работающих в данный момент пользователей в двух экзмеплярах. То есть, если имя вашей sp_ хранимой процедуру совпало с системной, то ее вызвать не получиться.

    Системные табличные функции нужно вызывать с префиксом ::, а скалярные без указания схемы.

  2. Из этого в частности следуют, что пользовательскую функцию нужно вызвать с указание схемы.
    create function my_fn() returns int
    begin
        RETURN 1
    end
    
    go
    select my_fn()

    Результат:

    Msg 195, Level 15, State 10, Line 1

    ‘my_fn’ is not a recognized built-in function name.

  3. Системные представления из INFORMATION_SCHEMA или sys нужно вызывать с указаним схемы.

    При этом они размещаются в базе данных master, но компиляции выполняется в каждой конкретной базе данных.

  4. На все остальные системные объекты, поставляемые компанией Microsoft, распространяются обычные правила распознавание имен.
Запись опубликована в рубрике В помощь администратору с метками , . Добавьте в закладки постоянную ссылку.

Добавить комментарий

Войти с помощью: