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