Freigeben über

DBCC MemoryStatus e a cache de procedimentos

  • Artikel

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

Comments