DBCC MemoryStatus e a cache de procedimentos




Going a little deep…


 


Nada melhor do que conseguir mais informações sobre o SQL Server para entender a mágica que ele faz com os objetos na memória e em sua cache de procedimentos.


Além do mais podemos cruzar o que já lemos com a saída dos comandos. Exemplo: O DBCC MemoryStatus possui um contador de buffers chamado hashed, que equivale ao contador de performance “Buffer manager – database pages”. Mas porque usar o termo “hashed”? Se olharmos o Inside SQL Server 2000 (pág. 92) podemos notar que as páginas de dados são colocadas na memória e referenciadas através de um código hash da combinação “dbid-fileno-pageno”, daí o termo utilizado pelo MemoryStatus.


 


No script abaixo eu utilizo o DBCC MemoryStatus e alguns contadores de performance para ver a utilização do buffer pool. O script parece grande mais não é, a grande maioria é compsta de comentários. Ele está em anexo no final do post. Enjoy!


 


/******************************************************************************


 


  Autor: Luciano Caixeta Moreira


  Data: 03/03/2006


  Descrição: Analisa informações de memória relacionada com a cache de procedimentos.       


 


******************************************************************************/


use MASTER


GO


 


/*


          Antes de iniciar os testes, colocar os seguintes contadores no performance monitor:


 


          – Buffer Manager:Free pages


          – Buffer Manager:Procedure cache pages


          – Cache Manager: Cache Object Counts: AdHoc Sql Plans


          – Cache Manager: Cache Object Counts: Prepared Sql Plans


          – Cache Manager: Cache Object Counts: Procedure Plans


*/


 


DBCC MEMORYSTATUS


GO


 


/*


          Verifique como está a informação relacionada com a procedure cache.


          Depois de executar o próximo comando todos os valores devem estar zerados.


*/


 


DBCC FREEPROCCACHE


GO


 


— Gera uma entrada para a cache, evitando que esse valor possa mascarar um resutado no futuro.


select * from syscacheobjects


GO


 


DBCC MEMORYSTATUS


GO


 


/*


          Resutado do DBCC MEMORYSTATUS e dos contadores de performance:


 


Buffer Distribution            Buffers    


—————————— ———–


Free                           500


 


Procedure Cache                Value      


—————————— ———–


TotalProcs                     1


TotalPages                     2


InUsePages                     0


 


 


          Buffer Manager: Free Pages = 504


          Buffer Manager: Procedure cache Pages = 2


          Cache Manager: Cache Object Counts: AdHoc Sql Plans = 0


          Cache Manager: Cache Object Counts: Prepared Sql Plans = 1


          Cache Manager: Cache Object Counts: Procedure Plans = 0


*/


 


EXEC SP_WHO


GO


 


DBCC MEMORYSTATUS


GO


 


/*


          Resutado do DBCC MEMORYSTATUS e dos contadores de performance:


 


Buffer Distribution            Buffers    


—————————— ———–


Free                           494


 


Procedure Cache                Value      


—————————— ———–


TotalProcs                     3


TotalPages                     8


InUsePages                     5


 


          Buffer Manager: Free Pages = 498


          Buffer Manager: Procedure cache Pages = 8


          Cache Manager: Cache Object Counts: AdHoc Sql Plans = 0


          Cache Manager: Cache Object Counts: Prepared Sql Plans = 1


          Cache Manager: Cache Object Counts: Procedure Plans = 2


 


          ** As informações indicam que existem mais duas procs compiladas, olhando os contadores de performance


          Cache Manager: cache object counts: (todas instâncias) podemos ver que os planos são:


 


          – Procedure plan (2 objetos). Analisando a (pseudo) tabela syscacheobjects temos:


 


          CacheObjectType               ObjectType    Sql


          ——————————————————————————


          Executable Plan                  Proc             sp_who


          Compiled Plan           Proc             sp_who


 


*/


 


EXEC SP_WHO


GO


 


DBCC MEMORYSTATUS


GO


 


/*


          Como era de se esperar o resultado ficou idêntico ao anterior, pois o procedimento


          estava em cache.


*/


 


SELECT CategoryID, CategoryName


FROM Northwind..Categories


GO


 


DBCC MEMORYSTATUS


GO


 


/*


          A execução da consulta levou a uma queda no número de páginas livres do buffer, pois informações


são carregadas do disco para a memória.


          (Particularmente, eu esperava um número bem menor do que essa alocação de 35 páginas (602 – 567).)


 


 


          Resutado do DBCC MEMORYSTATUS e dos contadores de performance:


 


Buffer Distribution            Buffers    


—————————— ———–


Free                           492


 


Procedure Cache                Value      


—————————— ———–


TotalProcs                     5


TotalPages                     10


InUsePages                     6


 


 


          Buffer Manager: Free Pages = 496


          Buffer Manager: Procedure cache Pages = 10


          Cache Manager: Cache Object Counts: AdHoc Sql Plans = 0


          Cache Manager: Cache Object Counts: Prepared Sql Plans = 3


          Cache Manager: Cache Object Counts: Procedure Plans = 2


 


          ** Foram adicionadas 2 procs na procecure cache! Exatamento como esperado: 2 planos de execução a mais, um


para o compiled plan e um para o executable plan. Analisando os contadores de performance (cache object counts),


notei as seguintes alterações:


 


          – Mais (2) Prepared Sql Plans


 


          Resultado da consulta na syscacheobjects:


 


          CacheObjectType               ObjectType    Sql


          ——————————————————————————


          Executable Plan                  Prepared        ()SELECT [CategoryID]=[CategoryID],[CategoryName]=[CategoryName] FROM [Northwind]..[Categories]


          Compiled Plan           Prepared        ()SELECT [CategoryID]=[CategoryID],[CategoryName]=[CategoryName] FROM [Northwind]..[Categories]


 


          Este exemplo demonstra que a procedure cache não armazena somente planos de SPs, e sim todos


os planos gerados por uma instância do SQL Server, como diz o BOL:


          “Procedure cache:


          This is a pool of pages containing the execution plans for all Transact-SQL statements currently


          executing in the instance.”


 


          Se for executado novamente a consulta, nenhum procedimento é adicionado à cache de procedimentos.


*/


 


SELECT * FROM MASTER..SYSPROCESSES


GO


 


DBCC MEMORYSTATUS


GO


 


/*


          Resutado do DBCC MEMORYSTATUS e dos contadores de performance:


 


Buffer Distribution            Buffers    


—————————— ———–


Free                           490


 


Procedure Cache                Value      


—————————— ———–


TotalProcs                     6


TotalPages                     12


InUsePages                     6


 


 


          Buffer Manager: Free Pages = 494


          Buffer Manager: Procedure cache Pages = 12


          Cache Manager: Cache Object Counts: AdHoc Sql Plans = 0


          Cache Manager: Cache Object Counts: Prepared Sql Plans = 4


          Cache Manager: Cache Object Counts: Procedure Plans = 2


 


          Somente foi adicionado 1 plano de execução à procedure cache, detalhado pela syscacheobjects:


 


          CacheObjectType               ObjectType    Sql


          ——————————————————————————


          Compiled Plan           Prepared        ()SELECT * FROM [MASTER]..[SYSPROCESSES]


*/


 


/*


          Tenta verificar se o SQL Server consegue parametrizar a consulta e reutilizar o


          plano de execução.


*/


 


SELECT CategoryID, CategoryName


FROM Northwind..Categories


WHERE CategoryID = 5


GO


 


DBCC MEMORYSTATUS


GO


 


/*


          Resutado do DBCC MEMORYSTATUS e dos contadores de performance:


 


Buffer Distribution            Buffers    


—————————— ———–


Free                           488


 


Procedure Cache                Value      


—————————— ———–


TotalProcs                     8


TotalPages                     14


InUsePages                     7


 


 


          Buffer Manager: Free Pages = 492


          Buffer Manager: Procedure cache Pages = 14


          Cache Manager: Cache Object Counts: AdHoc Sql Plans = 0


          Cache Manager: Cache Object Counts: Prepared Sql Plans = 6


          Cache Manager: Cache Object Counts: Procedure Plans = 2      


 


          CacheObjectType               ObjectType    Sql


          ——————————————————————————


          Compiled Plan           Prepared        (@1 tinyint)SELECT [CategoryID]=[CategoryID],[CategoryName]=[CategoryName] FROM [Northwind]..[Categories] WHERE [CategoryID]=@1


          Executable Plan                  Prepared        (@1 tinyint)SELECT [CategoryID]=[CategoryID],[CategoryName]=[CategoryName] FROM [Northwind]..[Categories] WHERE [CategoryID]=@1


 


          Com o resultado acima, ficou claro que a consulta pode ser parametrizada, pois foi criada uma variável @1.


 


*/


 


SELECT CategoryID, CategoryName


FROM Northwind..Categories


WHERE CategoryID = 3


GO


 


DBCC MEMORYSTATUS


GO


 


/*


          Resutado do DBCC MEMORYSTATUS e dos contadores de performance:


 


Buffer Distribution            Buffers    


—————————— ———–


Free                           488


 


Procedure Cache                Value      


—————————— ———–


TotalProcs                     8


TotalPages                     14


InUsePages                     7


 


 


          Buffer Manager: Free Pages = 492


          Buffer Manager: Procedure cache Pages = 14


          Cache Manager: Cache Object Counts: AdHoc Sql Plans = 0


          Cache Manager: Cache Object Counts: Prepared Sql Plans = 6


          Cache Manager: Cache Object Counts: Procedure Plans = 2      


 


          Mudando somente o código da categoria podermos verificar que o SQL havia conseguido parametrizar a


          consulta, pois ele reutilizou o plano de execução já que nenhum número foi alterado.


*/


 


SELECT CategoryID, CategoryName


FROM Northwind..Categories


WHERE CategoryID = CAST(3 AS Integer)


GO


 


DBCC MEMORYSTATUS


GO


 


/*


          Resutado do DBCC MEMORYSTATUS e dos contadores de performance:


 


Buffer Distribution            Buffers    


—————————— ———–


Free                           486


 


Procedure Cache                Value      


—————————— ———–


TotalProcs                     10


TotalPages                     16


InUsePages                     8


 


 


          Buffer Manager: Free Pages = 490


          Buffer Manager: Procedure cache Pages = 16


          Cache Manager: Cache Object Counts: AdHoc Sql Plans = 2


          Cache Manager: Cache Object Counts: Prepared Sql Plans = 6


          Cache Manager: Cache Object Counts: Procedure Plans = 2      


 


          CacheObjectType               ObjectType    Sql


          ——————————————————————————


          Compiled Plan           Prepared        SELECT CategoryID, CategoryName  FROM Northwind..Categories  WHERE CategoryID = CAST(3 AS Integer)


          Executable Plan                  Prepared        SELECT CategoryID, CategoryName  FROM Northwind..Categories  WHERE CategoryID = CAST(3 AS Integer)


 


          Demonstra que mesmo a consulta possuindo uma semântica igual a anterior, um novo plano é criado e não pode


          ser parametrizado devido à utilização do CAST.


*/


 


SELECT CategoryID, CategoryName


FROM Northwind..Categories


WHERE CategoryID = CAST(4 AS Integer)


GO


 


DBCC MEMORYSTATUS


GO


 


/*


          Resutado do DBCC MEMORYSTATUS e dos contadores de performance:


 


Buffer Distribution            Buffers    


—————————— ———–


Free                           484


 


Procedure Cache                Value      


—————————— ———–


TotalProcs                     12


TotalPages                     18


InUsePages                     9


 


 


          Buffer Manager: Free Pages = 488


          Buffer Manager: Procedure cache Pages = 18


          Cache Manager: Cache Object Counts: AdHoc Sql Plans = 4


          Cache Manager: Cache Object Counts: Prepared Sql Plans = 6


          Cache Manager: Cache Object Counts: Procedure Plans = 2      


 


          CacheObjectType               ObjectType    Sql


          ——————————————————————————


          Compiled Plan           Prepared        SELECT CategoryID, CategoryName  FROM Northwind..Categories  WHERE CategoryID = CAST(4 AS Integer)


          Executable Plan                  Prepared        SELECT CategoryID, CategoryName  FROM Northwind..Categories  WHERE CategoryID = CAST(4 AS Integer)


 


          A alteração do código da categoria gera um novo plano de execução que somente poderá ser reutilizado se for executada


          uma consulta exatamente com o mesmo texto.


*/


 


/*


          Algumas comparações interessantes…


 


          DBCC MEMORYSTATUS                                     x                  Performance counters


 


          Buffer counts – commited              =                 Buffer manager – Total pages


          Procedure cache – TotalPages        =                 Buffer manager – procedure cache pages


          Buffer distribution – Free      (aprox.)        Buffer manager – free pages (sempre 4 a menos – não sei porque!)


          Buffer counts – hashed                           =                 Buffer manager – database pages


*/


 


Até a próxima.


[]s


 


Luti


 

ProcedureCache.sql

Skip to main content