Plan Cache – Proc Cache – Como funciona…

A maneira que o SQL Server chega ao dado solicitado, depende de um plano de execução, criado e executado pelo DataBase Engine, os passos tomados por uma query, antes da sua efetiva execução são:
– Analise Lexica (Analise os tokens da linguagem, como por exemplo a analise de um select e um from…)
– Analise sintatica (Analise a ordem dos tokens, ou seja, não tem um from antes do select, um order by ser a ultima
instrução…)
– Analise semantica (Analise da existencia das tabelas, colunas, e possiveis conversões)
– O DbEngine ira criar varios planos de execução e escolher o melhor deles
– Este plano então é executado, trazendo o retorno da sua query

Veja que todos os passos tem um mesmo objetivo, que é gerar com a garantia de execução com sucesso, um plano de execução ideal para buscar o seu dado. Este plano é criado apenas no ultimo passo, sera mesmo necessario, que para todas as querys, todos os passos sejam executados? Felizmente não!!!

O SQL Server trabalha com um espaço na memoria denominado Procedure Cache, ou Plan Cache, esta area especifica da memoria é responsavel por armazenar os planos de execuções de querys que são executadas, fazendo com que, caso alguma query seja executada repetidas vezes com uma alta frequencia, não seja necessario realizar todos os passos pré criação do plano de execução.

Quando executamos uma query e seu plano de execução vai para o Plan Cache, ele possui um valor identico ao seu custo de execução, que com o tempo vai sendo decrementado até chegar a 0, quando existe pressão externa (Windows) em relação a memoria, o SQL Server começara a tirar da memoria todos os planos de execução que possuem valor 0. Caso não exista pressão externa na memoria, o SQL Server, mesmo que encontre planos de execução com valor 0, não ira remove-los da memoria, se não ha pressão para que remover?…

O valor de um plano de execução volta a seu original caso o mesmo seja reutilizado, mantendo-o assim por mais tempo em memoria.

Uma caracteristica importante é que os planos de execução independem do SPID de conexão, ou seja, uma conexão pode utilizar o plano de execução criado por outra conexão.

Vale lembrar que escritas diferentes da mesma query, não utilizarão o mesmo plano, pois são, teoricamente querys diferentes.

Abaixo uma query para verificar as querys que estão no Plan Cache


USE master
GO
SELECT UseCounts,RefCounts, Cacheobjtype, Objtype, 
ISNULL(DB_NAME(dbid),'ResourceDB') AS DatabaseName, TEXT AS SQL 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
ORDER BY dbid,usecounts DESC;
GO


Lembrando que devido a utilização de DMV, a mesma só sera executada em SQL Server´s iguais ou superiores ao SQL Server 2005.

A maior importancia de manter um plano de execução armazenados em memoria é a performance, imagine em um ambiente OLTP de CallCenter, aonde as mesmas querys são executadas milhares de vezes repetidas vezes, se não fosse possivel manter em memoria, seria necessario a cada nova execução, gerar um novo plano para aquela query, o que causaria impacto para a operação.

Caso voce queira remover um plano de execução do Plan Cache, voce pode utilizar o comando DBCC freeproccache, se voce executa-lo assim:

DBCC FREEPROCCACHE


Simplismente todos os planos serão removidos da memoria, como um teste, antes de tira-los da memoria, execute a primeira query deste post para visualisar todos os planos em memoria, em seguinda, execute o DBCC, e depois re-execute a query de verificação, voce vera que todo o retorno sera da query utilizada por voce, para essa visualisação.

Ao executar um simples select em qualquer tabela, o mesmo deve ir para o Plan Cache normalmente.

Anúncios
Post a comment or leave a trackback: Trackback URL.

Trackbacks

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: