Evitando a
recompilação de SPs – Luciano Leston – Dez 2004
d) Geração do plano de execução
Recompilação em tempo de execução:
f) Mudanças no status do ambiente
g) Uso do caractere especial *
Algumas formas de diminuir a recompilação de SPs:
b) Uso de DDL e DML separadamente
c) Cuidados com tabelas temporárias
h) Sub-SPs e SPs particionadas:
Introdução:
Uma das maiores estratégias de
performance de um SGBD é a capacidade de reaproveitar processos de execução
anteriores, gravados fisicamente ou armazenados em cache de memória. No SQL
Server, o “Query Optimizer” é o responsável por isso. Ele seleciona a melhor
estratégia de acesso aos dados (resultados mais rápidos e eficientes) e
armazena esse processo para reutilização, de forma que, quando solicitado, o
melhor plano de execução esteja sempre disponível. Esse evento é chamado de
“compilação”. A utilização de "objetos" compilados garante um aumento
significativo de performance na maior parte das situações, uma vez que gerar um
plano de execução tem um custo bastante elevado.
Quando uma
“Stored Procedure” é chamada pela primeira vez, um plano de execução é criado
para ela e armazenado para posterior aproveitamento. A “compilação" de uma
SP não é a compilação de código que ocorre em algumas linguagens como VB e
JAVA, mas na verdade é um processo intermediário, onde o SQL analisa a
estrutura de dados e de fluxo de execução, acelerando os resultados das querys
internas. Na compilação de uma SP temos: verificação de sintaxe, localização
dos objetos, escolha dos índices e análise das constraints, definição das
operações a serem executadas em paralelo, estimativa de custo para a execução
de cada instrução e alocação da quantidade de memória (Procedure Cache) a ser
utilizada. Basicamente, o ganho de performance em se usar uma SP reside no
aproveitamento do plano que está em cache a partir da segunda execução. Se esse
plano não puder ser reaproveitado ou se uma SP recompilar muitas vezes é
possível que ela tenha um custo maior do que a execução de suas partes
isoladamente.
O plano de execução da SP armazenado é utilizado a cada nova chamada, até que uma ação ocorra para invalidar esse plano, forçando a uma nova compilação deste. Algumas ações geram automaticamente a recompilação da SP, como por exemplo, as alterações de objetos referenciados por ela (tabelas, índices, constranints, triggers, etc). Ações como essas causam a recompilação da SP no momento em que esta é chamada. Existem também (e são mais freqüentes) as recompilações em tempo de execução. Essas é que causam a maior parte da perda de performance na execução de SPs, aumentando significativamente o seu custo, podendo até inviabilizar o seu uso (principalmente em SPs muito extensas). Uma recompilação afeta todo o plano de execução e não apenas a parte das instruções que originaram o processo.
Compilação:
O processo de compilação se divide em quatro
etapas: Parse e Normalize, compilação das instruções T-SQL, compilação das
instruções SQL e a geração do plano de execução.
a)
Parse e Normalize – Parse é a dissecação das instruções
do SQL, transformando-as em estrutura de dados que possam ser processadas pelo
computador. Também inclui a validação da sintaxe. Normalize é a validação das
referências dentro do código, validação dos objetos e suas características e a
validação da semântica das instruções, verificando se a instrução solicitada
tem sentido como, por exemplo, a tentativa de executar uma tabela.
b) Compilação T-SQL – Análise das estruturas de código e das construções procedurais como IF, WHILE, declaração de variáveis, etc...
c) Compilação SQL – Tradução das requisições não procedurais para instruções "set-based" que possam ser utilizadas de forma eficiente (otimizada). São as instruções SELECT, INSERT, UPDATE, etc...
d) Geração do plano de execução – É a fase final, onde o plano criado é gravado no cache.
Plano de execução:
O otimizador do SQL executa uma operação chamada de “constant folding”, que é a analise das expressões no momento da compilação, evitando desperdícios na geração do plano de execução. Por exemplo, quando passamos para avaliação uma expressão como “WHERE a > 2*2 and a < 1*3” o otimizador do sql traduz a operação para “a>4 and a<3” e determina que a expressão não é valida (como resultado) e elimina a expressão do plano de execução (gera uma expressão no plano marcando seu retorno como 0).
Na arquitetura da SP, o plano de execução é dividido em duas estruturas chamadas “Compiled Plan” e “Executable Plan”:
a) Compiled Plan – Esta estrutura, também chamada de “query plan”, é totalmente re-entrante, ou seja, todos os usuários podem compartilhar o plano de execução compilado. Nenhuma informação contextual (como valor de variáveis ou parâmetros passados) é armazenada, apenas a estrutura básica da SP, a fim de poder ser re-utilizada por todos os usuários concomitantemente. Até duas cópias do plano de execução são mantidas em cache: uma cópia da versão serial e outra cópia da versão com execução em paralelo (se for o caso), de acordo com o grau de paralelismo apropriado.
b) Executable Plan – Nesta estrutura, cada usuário executando a query tem uma cópia deste plano com os dados manipulados durante a execução da sp armazenados junto ao plano. Esta estrutura também é chamada de “execution context”. Este plano pode ser re-utilizado, mas não é re-entrante e cada usuário tem uma cópia com seus próprios dados.
Compile LOCK:
Quando uma SP é
compilada, automaticamente é gerado um “compile lock”, que afeta não só a SP em
questão, mas todos os planos desta SP que estiverem sendo compartilhados (em
execução). Todas as execuções são suspensas até que a compilação esteja
concluída. A recompilação é a maior causa da ocorrência de "compile
locks" em SPs e triggers.
Outros motivos para
que os "compile locks" ocorram são:
·
O
usuário que executa a SP não é o seu "owner";
·
O nome
da SP não está qualificado com o "owner" no momento da execução;
·
Os
objetos referenciados dentro da SP não estão qualificados com o seu
"owner".
Exemplo de como a
falta de identificação do "owner" gera o compile lock:
Sendo o usuário
"dbo" o "owner" da SP "usp_sem_owner" e sendo
esta executada pelo usuário "paulo" com a instrução "exec
usp_sem_owner", a busca inicial pelo objeto em cache falha. Antes do
"optimizer" se preparar para execução da SP ele verifica se o nome do
"owner" está devidamente qualificado e quando não está (como no nosso
caso) é atribuído o nome do usuário executor como sendo o "owner" do
objeto. Neste caso obtemos o nome "paulo.usp_sem_owner". Uma
vez que esse objeto não existe ocorre um evento de cache chamado SP:CacheMiss.
Como o SQL não pode determinar se o plano que está em cache (dbo.usp_sem_owner)
é o referido, um lock exclusivo é colocado na SP e o otimizador se prepara para
recompilar. Nesse momento o otimizador localiza o nome do objeto e obtém o seu
"ID". Antes de compilar o plano de execução, o SQL usa o objectID
para realizar uma nova busca no cache. Se for localizado um plano anterior no
cache, ele será executado e o "compíle lock" cancelado, caso
contrário uma compilação terá inicio. Quando especificamos o "owner"
todo esse processo é realizado em uma única etapa e não causa nenhum
"compile lock".
Uma vez
que o plano está pronto para ser executado, o otimizador do sql utiliza o mesmo
algoritmo anterior, buscando desta vez pelos objetos referenciados dentro da SP
(um a um). Se eles não estiverem qualificados com o "owner", teremos
um "compile lock" para toda a SP até que ela tenha sido completamente
analisada. Caso seja necessário uma recompilação, um "compile lock"
será colocado em todos os planos compilados compartilhados, suspendendo todas
as execuções concorrentes até que a nova compilação esteja concluída.
O prefixo "sp_":
Stored procedures com o nome
iniciando por "sp_" são consideradas pelo SQL Server como sendo do
sistema e por sua vez possuem regras específicas de manipulação. Para começar,
elas estão localizadas, por default, no banco "master". Usar esse prefixo para o nome de SPs que não
se localizem no banco "master" fará com que o SQL obtenha um evento
"SP:CacheMiss", ou seja, ele nunca encontrará a SP em cache (como
descrito acima) e a pesquisa para a localização da SP terá sempre um custo mais
elevado (não se trata mais de pesquisar dentro do banco e sim entre o master e
o banco atual). Além disso, não adianta qualificar a SP com o nome do banco,
pois como regra, ele irá sempre no "master" para depois ir ao banco
atual (ou qualificado).
Processo de execução:
Após o plano ser colocado em cache,
o SQL se prepara para a execução, verificando se não houve nenhuma alteração e
se o plano não precisa ser recompilado. Embora poucos microssegundos separem a
compilação da execução, podemos ainda ter processamento caso existam instruções
DDL a serem processadas (CREATE, ALTER, DROP, etc) no código, teremos uma nova
recompilação no momento em que seus objetos forem referenciados.
Nenhum processo de recompilação será
disparado para mudanças no valor dos parâmetros (a não ser que eles mudem a
estrutura do plano de execução a ponto de invalidá-lo), no espaço de memória,
nos recursos do sistema ou necessidade de acessar dados em cache.
Quando uma SP for realmente ser
executada, um processo chamado "memory grant scheduler" é chamado.
Esse processo controla o uso de memória usado pelo plano de execução. Ele é
responsável por algumas otimizações na execução como, por exemplo, quando
processamos uma instrução "select top 10" e nosso resultset for de
100 registros, o "memory grant scheduler" aloca memória apenas para o
resultado final e instrui a finalização da query antes do previsto (antes de
obter os 100 registros). Com isso garante-se que somente serão processados
tantos registros quanto necessários para atender a query e que não haverá
desperdício de memória e recursos. Quando o "scheduler" retorna ok
para a alocação da memória requisitada, o plano é aberto e a execução começa,
prosseguindo assim até sua conclusão.
Como o SQL interpreta uma chamada
de execução para uma SP (ou batch):
Recompilação em tempo de execução:
Embora todo o processo de compilação já esteja processado e o devido plano sendo executado, ainda podem ocorrer recompilações devido a processos que, durante o fluxo da SP, alterem a estrutura do que o SQL Optimizer tenha projetado (plano de execução). Existem vários eventos que podem causar isso e varias formas de evitá-los (ou minimizá-los). Eis alguns dos principais eventos que podem causar a recompilação em tempo de execução:
a) Alterações nos objetos referenciados na SP (schema, permissões, índices, constraints) – O SQL detecta a mudança na tabela e invalida o plano de execução previamente gerado para a SP. Um novo plano tem que ser gerado na próxima instrução que referencie os objetos alterados. Algumas dessas situações que causam a invalidação do plano são:
·
Mudanças nas tabelas ou views (ALTER TABLE ou ALTER VIEW);
·
Criação ou atualização das estatísticas (manual ou
automático);
·
A exclusão ou alteração de um índice usado no plano de
execução;
·
Um grande número de mudanças nos registros (ver ítem “b”).
b) Alteração de registros: Existe um contador interno do SQL (rowmodctr na tabela sysindexes) que determina ações a serem executadas quando um certo número de alterações nos registros ocorrer. No caso de SPs a mudança destes registros causam a recompilação da mesma, dependendo do tipo de tabela:
|
Tipo de Tabela |
Resultando em uma tabela vazia |
Iniciando a partir de uma tabela
vazia |
A partir de uma tabela que
contem registros |
|
Permanente |
< 500 registros |
>= 500 registros |
>= 500 registros + 20% do
total de registros |
|
Temporária |
< 6 registros |
>= 6 registros |
>=6 registros + 20% do total
de registros |
c) Chamada a SP sem usar o nome qualificado – a SP não é localizada no cache e o otimizador do sql não consegue re-utilizar nenhum plano para ela, criando um novo.
d) Operações de DDL (Data Definition Language) e DML (Data Manipulation Language) dentro da mesma SP:
·
Ex. de DDL: CREATE, ALTER, DROP, TRUNCATE
·
Ex. de DML: Select, Insert,
Update, Declare, Set
e) Algumas operações em tabelas temporárias – o uso de tabelas temporárias pode causar a recompilação da SP.
f) Mudanças no status do ambiente: Algumas alterações de ambiente (usando instruções SET) causam a recompilação da SP.
g) Uso do caractere especial *. O uso desse caractere facilita a programação, mas pode causar um problema de recompilação. Quando o otimizador compila a SP pela primeira vez ele substitui * (internamente) por todas as colunas da tabela referenciada, mas quando executamos a SP ele tem que verificar quais são as colunas atuais e comparar com as existentes anteriormente, para verificar se houveram alterações (nome, tipo, precisão ou uma nova coluna). Se alguma alteração tiver ocorrido, uma nova compilação poderá ser necessária (caso afete o plano de execução). Isso se repete a cada execução, causando aumento no tempo de pesquisa e a possível recompilação da SP.
Algumas formas de diminuir a recompilação de SPs:
a) Use sub-procedures para executar instruções que estejam causando recompilações – neste caso ainda teremos uma recompilação mas, ao invés da recompilação de toda a SP, teremos a recompilação apenas da sub-procedure;
b) Uso de DDL e DML separadamente – Quando instruções DDL são colocadas dentro de SPs (ou querys) preparam o otimizador para uma recompilação tão logo ele encontrar a primeira ocorrência dos objetos referenciados em uma instrução DML. Ex:
create procedure dbo.usp_exemplo
as
create
table dbo.Tabela1 (campo1 int) --- DDL
select
campo1 from dbo.Tabela1 --- DML=>recompila
create index IX_Tb1 on dbo.Tabela1(campo1) --- DDL
Insert
into dbo.Tabela1 Values (123) --- DML=>recompila
select campo1 from dbo.Tabela1 --- DML
create table dbo.Tabela2 (campo2
char(5)) ---
DDL
select campo2 from dbo.Tabela2 ---
DML=>recompila
Neste
caso a SP será recompilada três vezes quando for executada. No processo de
criação do plano de execução pelo otimizador do SQL ele inicialmente compila
toda a SP. Ocorre que nesse momento as tabelas “Tabela1” e “Tabela2” ainda não
existem. Por causa disso, nenhum plano de execução para as referências destas
tabelas é gerado. Eles somente serão gerados durante a execução. Quando a SP for executada pela
primeira vez, o primeiro passo é criar a tabela “Tabela1”. Em seguida, é
executado um “Select” na Tabela1,a qual não tem nenhum plano de execução. Nesse
momento a SP é recompilada para gerar o plano de execução da instrução “Select”
e de todas as outras instruções que referenciam esta tabela, no nosso caso um
“insert” e um “select” após a criação do índice. Nenhum plano é gerado para o
“select” na tabela “Tabela2” pois ela não existe ainda. O próximo passo é criar
o índice para a tabela “Tabela1”. Em seguida é executado um novo “Select” (e
agora já existe um plano para ele). Entretanto, devido à mudança no “schema” da
Tabela1, uma nova recompilação será processada e o plano de execução será
atualizado (gerado novamente). A seguir, a tabela “Tabela2” será criada e uma
instrução “Select” será executada. Devido a não existir nenhum plano de
execução a SP é recompilada novamente. Estas recompilações ocorrerão em cada
execução da SP. Para reduzirmos isso, devemos modificar a SP para que todos as
instruções DDL sejam colocadas juntas, realizando uma única recompilação. Ex:
create procedure dbo.usp_exemplo
as
-- Todas as instruções DDL primeiro
create table dbo.Tabela1 (campo1 int)
create index IX_Tabela1 on dbo.Tabela1
(campo1)
create table dbo.Tabela2 (campo2 char(5))
-- Seguida pelas
instruções DML
select campo1 from dbo.Tabela1 --- recompila uma
única vez
Insert into dbo.Tabela1 Values (123)
select campo1 from dbo.Tabela1
select
campo2 from dbo.Tabela2
Desta forma o otimizador irá compilar da seguinte maneira: Inicialmente (como no caso anterior) não existem planos para as tabelas “Tabela1” e “Tabela2” pois elas não existem. No primeiro passo todas as instruções DDL são executadas (criamos “Tabela1” e seu índice e “Tabela2”). Na primeira execução do “Select”, como ainda não existe um plano de execução, a SP será recompilada. Como todos os objetos já existem, são gerados planos de execução para todos os planos que estão faltando durante a mesma compilação. Como não existem mais alterações nos objetos referenciados, nenhuma outra compilação será necessária. É bom lembrar que o restante da execução usará o mesmo plano de execução e cache criados anteriormente.
c) O uso de tabelas temporárias pode causar a recompilação da SP em cada execução. Para evitar isso algumas situações devem ser observadas:
·
Todas as instruções que se referenciam a uma tabela
temporária devem ser da tabela criada na mesma SP e não a uma SP que chamou
esta ou de uma SP a ser chamada, ou ainda a uma string executada via EXEC ou
“sp_executesql”
·
Não devem existir cursores apontando para uma tabela
temporária
·
Instruções DROP TABLE na tabela temporária não podem
aparecer antes do fim da SP. Não existe nenhum motivo para a execução de DROP
TABLE em tabelas temporárias, uma vez que elas são excluídas imediatamente após
o término da execução da SP.
·
Nunca usar instruções CREATE TABLE ou SELECT INTO em tabelas
dentro do controle de IF...ELSE ou WHILE
d) O uso da opção KEEP PLAN: Tabelas temporárias utilizadas dentro de SPs geram uma certa complexidade ao otimizador do SQL. O “row count” e as informações de estatísticas podem variar dramaticamente durante a execução de uma SP. Para garantir que o otimizador use o melhor plano de execução em todos os casos, um algoritmo especial foi desenvolvido para ser mais agressivo com as recompilações. O algoritmo define que se uma tabela temporária que for criada numa SP for modificada mais de seis vezes, a SP será recompilada quando o a próxima instrução se referenciar a ela. Esse processo é útil nos casos em que o volume dos dados podem mudar radicalmente, afetando o plano de execução que havia sido escolhido. Entretanto, no caso de grandes SPs que modificam varias vezes as tabelas temporárias mas que não as afetam de forma significativa, esse algoritmo de recompilação pode ser um grande gargalo de performance. Para resolver esses casos existe a opção KEEP PLAN nas instruções “select”. Essa opção elimina a recompilação causada por mais de 6 alterações na tabela temporária e usa o algoritmo de recompilação padrão (500 alterações). O KEEP PLAN não elimina a recompilação, ele simplesmente altera o tipo de algoritmo a ser usado. Ex:
select campo1 from
dbo.Tabela1
option (KEEP PLAN)
e) Uso da opção KEEPFIXED PLAN: Esta opção força o query optimizer a utilizar sempre o mesmo plano de execução para a query corrente. Isso representa não mais recompilar a query devido a mudanças nas estatísticas ou alterações nos índices da tabela. A única forma de recriar este plano será nos casos de alteração na própria SP ou a exclusão e recriação de algum objeto usado na query. Por isso, é importante a avaliação anterior do processo e a certeza de que nenhuma alteração nas tabelas envolvidas iria afetá-lo, caso contrário, ao invés de um benefício teremos sim um gargalo de processamento maior do que a recompilação. Exemplo de uso:
CREATE
PROCEDURE dbo.usp_ModificaRegistros
AS
-- vamos considerar que exista uma tabela TestePlan com
-- a mesma definição de #T e com mais de 1000 registros
CREATE TABLE
#T (a int, b varchar(15), c decimal(9,2), d int)
INSERT INTO #T
SELECT a,b,c,d FROM dbo.TestePlan
SELECT count(1) FROM #T WHERE a = 37
Na segunda
execução da SP "usp_ModificaRegistros" o código " select
count(1) from #t where a = 37" irá
causar uma recompilação (devido ao número de registros que foram acrescentados a tabela temporária).
Alterando a SP para incluir a opção "keepfixed plan":
ALTER
PROCEDURE dbo.usp_ModificaRegistros
AS
...
SELECT count(1) FROM #T WHERE a = 37
OPTION (KEEPFIXED PLAN)
A partir
da segunda execução nenhuma recompilação irá ocorrer (será preservado o plano
escolhido na compilação inicial). Isso pode ser interpretado como uma situação
favorável, mas não podemos esquecer que, quaisquer que sejam as alterações na
tabela, não haverá uma recriação do plano de execução, ou seja, não será utilizado nenhum novo índice e
nenhuma atualização de estatística.
f) Uso de instruções SET: Alguns SETs alteram o ambiente de execução de tal forma que é necessário uma nova recompilação a cada alteração. Usar essa opção dentro da SP vai causar uma recompilação a cada vez que ela for executada, tantas vezes quanto os SETs encontrados. A recompilação ocorrerá no momento em que a alteração de ambiente entrar em execução, ou seja, no momento em que uma instrução usar a alteração solicitada. Poderemos evitar o excesso de recompilações colocando todas as instruções sets no inicio da sp, obtendo-se, desta forma, uma única recompilação. Não são todas as instruções SET que causam alterações do ambiente (ou no resultado retornado pela query) e, portanto, a recompilação da SP. Se for realmente necessário alterar o ambiente use as instruções SET fora do escopo da SP. Ex:
SET CONCAT_NULL_YIELDS_NULL
ON
Exec dbo.USP_NaoAlterarAmbiente
No exemplo acima também poderíamos substituir o uso do SET usando dentro
da SP a função ISNULL() durante as concatenações de string.
Alguns SETs não tem efeito quando colocados dentro da SP e não causam
recompilação, como no caso do SET NOCOUNT, que altera apenas a exibição no
cliente, não envolvendo nenhum processo interno do SQL ou dos planos de
execução. Existem outros casos que são as declarações implícitas de ambiente
colocadas na SP como, por default, SET ANSI_NULLS ON e SET QUOTED_IDENTIFIER
ON. Esses SETs são armazenados na coluna status da tabela de sistema sysobjetcs
e somente poderão ser alteradas se a SP for excluída e recriada com outras
definições.
Abaixo uma lista com as instruções SET e seu efeitos sobre a SP:
