Ola Pessoal,

Acho que a maioria dos DBA’s conhece essa opção do Auto Close, e também sabem o quanto nós não gostamos dessa função.

De acordo com o BOL o Auto Close faz com que após o ultimo usuario se desconecte da base de dados ele libera todos os locks presentes na mesmo, ou seja, agora você consegue pegar o arquivo .mdf e fazer o que voce quizer seja copiar, mover ou até mesmo excluir.

Mas o que essa opção influencia no Data Cache? Bom meu amigo, ela tem total interferencia, pois sendo que após o ultimo usuario se desconectar todos os locks são liberados e com isso todo vestigio de presença desse banco de dados no Buffer Cache do SQL Server é eliminado, então todas as paginas de dados que estavam presentes lá no Data Cache serão escritas novamente no disco. Agora você imagina isso em um banco altamente transacional recebendo milhares de requisições por segundo, quando o ultimo carinha se desconectar a morte começa a pairar sobre o seu banco de dados.

Vou mostrar então o efeito dessa opção no SQL Server.

Temos aqui uma base de dados normal chamada MCITP

use MCITP
go

DBCC DROPCLEANBUFFERS — Limpando o Data Cache
go

set statistics io on
select * from dbo.Produtos

Vamos analisar agora o numero de paginas lidas.

Como vocês podem ver todas as paginas de dados foram lidas e jogadas para o Data Cache. Vamos conferir se as paginas de dados realmente estão lá?

select * from sys.dm_os_buffer_descriptors
where database_id = DB_ID(‘MCITP’)
and file_id <> 2

Como vocês podem ver, as paginas de dados estao realmente no Data Cache do SQL Server.

Agora vamos acabar com nosso banco de dados? Vamos habilitar a opção AUTO CLOSE.

alter database MCITP set AUTO_CLOSE ON
go
select * from sys.dm_tran_locks

temos a seguinte visualização.

No Management Studio ficou duas abas abertas, uma apontando para a base Master e outra para a base MCITP, por isso ainda temos apenas um unico lock na base. Vamos fechar a aba que esta a base MCITP e ver como o Data Cache se comporta.

Executamos novamente a consulta:

select * from sys.dm_os_buffer_descriptors
where database_id = DB_ID(‘MCITP’)
and file_id <> 2

É meus amigos como vocês podem ver todas as paginas de dados que estavam no Data Cache foram varridas, o que aconteceu? Foram todas para disco novamente, esperando que na proxima leitura essas paginas sejam levadas para o Data Cache novamente.

Pessoal, por hoje é isso espero que vocês tenham gostado desse post, e que principalmente não habilitem nunca essa opção em seu ambiente de produção.

Marcos Freccia
@SQLFreccia
MCTS SQL Server 2008