Introdução ao cache no SQL Server
5 de setembro de 2013 Deixe um comentário
Segundo a Wikipédia
“[…] cache é um dispositivo de acesso rápido,
interno a um sistema, que serve de intermediário entre um operador de
um processo e o dispositivo de armazenamento ao qual esse operador acede.
A vantagem principal na utilização de um cache consiste
em evitar o acesso ao dispositivo de armazenamento o que pode ser
demorado […]”
O conceito de cache é aplicado a várias áreas da TI e com certeza você já deve ter ouvido falar nesse termo, por exemplo: no desenvolvimento de páginas WEB, Banco de Dados, e até ao acessar sites.
Na esfera SQL Server são dois os tipos de cache existentes:
Data Cache: Na primeira vez ele lê o disco e coloca os dados na memória,
Já nas próximas utilizações destes dados ele não precisa ir até o disco para recuperar os dados.
Procedure Cache: Diferente do primeiro tipo, ele armazena o código da procedure e os planos de execução na memória.
A título de conhecimento, estes mesmos caches existem no oracle (e em outros SGDBS), seriam eles Buffer Cache e o Shared Pool respectivamente.
contribuição: Wellington Mariusso 🙂
Quando criamos, por exemplo, uma stored procedure os seguintes passos são executados:
* Create Procedure
* Parse ( Faz a análise da sisntaxe, valida se os objetos existem etc)
* Gera o Plano de Execução para o comando, que são os ‘caminhos’ que o SQL vai seguir para realizar a sua solicitação e, tudo isso, baseado em uma série de variáveis, como por exemplo Seletividade, Densidade, Cardinalidade etc.
* Compila plano de execução: Depois que o plano já foi definido ele o pega e insere no cache para que as próximas execuções possam utilizar o mesmo plano, sem passar pelos mesmos passos.
Consultar os objetos em cache:
Através de DMVs (dynamic management Views) que foram introduzidas no SQL 2005, somos capazes de verificar, em tempo real, o que está acontecendo
no nosso servidor, no caso dos objetos que estão em cache a seguinte DMV poderá ser utilizada:
SELECT * FROM SYS.SYSCACHEOBJECTS where dbid = db_id([NomeSeuDB])
<p>Caso você deseje consultar somente os planos de execução que estão armazenados no cache a seguinte <a href="http://technet.microsoft.com/en-us/library/ms188754.aspx">DMV</a> é mais indicada</p>
<p>
select * from sys.dm_exec_cached_plans a cross apply sys.dm_exec_query_plan(a.plan_handle)
obs: fazemos uso do operador “cross apply” com a tablea sys.dm_exec_query_plan
para que possamos obter o plano de execução em formato XML ao invés de apenas o handle.
Limpando o cache
Muitas vezes estamos trabalhando com otimização de consultas e torna-se inviável reiniciar o servidor a cada novo teste para validar
se as nossas alterações foram efetivas, para isso o SQL Server possui a função:
dbcc freeproccache
Que vai limpar todos os objetos que estivem no cache; devemos tomar muito cuidado ao executar esse tipo de operação no nosso servidor
de produção, pois, as coisas tendem a ficar bem lentas por algum tempo. 🙂
Recompilação do plano de execução
Quando há alterações nos objetos refenciados em uma procedure, criação de indice, adição ou exclusão de campos (etc) o SQL precisa criar um novo plano de execução pois o antigo não reflete mais a realidade da base.
Vamos a Prática
Agora que já vimos um pouco sobre o cache do SQL Server vamos ver um pouco do assunto na prática.
primeiramente limparemos o cache
<p>DBCC FREEPROCCACHE
Agora faremos um select em uma tabela de sistema com um condicional
SELECT SQL,* FROM SYS.syscacheobjects WHERE DBID = DB_ID()
Execute novamente e note que nenhum outro objeto será adicionado ao cache; vamos
agora adicionar mais um condicional:
Agora iremos criar uma procedure, limpar o cache e por último executa-la.
create procedure sp_cacheTeste as select 1 as retorno dbcc freeproccache exec sp_cacheTeste
Podemos notar, após consultarmos a sys.syscacheobjects que os dados em cache mudaram.
Alteraremos a procedure para ver o que acontecerá com objeto no cache:
alter procedure sp_cacheTeste as select 1 as retorno, 0 as retorno2 exec sp_cacheTeste
Note as diferenças na coluna objectType da Sys.SysCacheObjects onde são descriminadas consultas AdHoc e procedures.
Ainda iremos explorar um pouco mais sobre o cache em um próximo post, pretendo mostrar, através do SQL Profiler e de alguns eventos específicos de captura de cache, como o SQL trabalha com dados/planos que ainda não estão em cache.
Espero que tenham gostado.
[]’s
Dhiego Piroto