Introdução ao cache no SQL Server

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 &lt;a href=&quot;http://technet.microsoft.com/en-us/library/ms188754.aspx&quot;&gt;DMV&lt;/a&gt; é 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