DESAFIO DO GORDO – SOLUÇÃO – Qual a finalidade da propriedade AUTO CLOSE?

Galera,

Antes de mais nada quero agradecer a participação de todos no último DESAFIO DO GORDO e dizer que as respostas foram excelentes.

Para explicar o porque existe a recomendação de desabilitar a propriedade AutoClose é interessante descrever com um pouco mais de detalhes o que acontece quando o serviço do SQL Server é iniciado.

No momento em que o serviço do SQL Server é iniciado, ocorre a leitura dos registros do sistema operacional para encontrar a localização do banco de dados de sistema master e solicitar o bloqueio exclusivo dos arquivos de dados e de log do mesmo.

Depois disso o SQL Server realiza a leitura na view de sistema sys.master_files e localiza todos os arquivos de dados (.mdf, .ndf) e de transaction log (.ldf) de todos os bancos de dados armazenados na instância e também solicita o bloqueio exclusivo destes arquivos para inicializar cada um dos bancos de dados.

A primeira situação na qual a propriedade AutoClose pode influenciar na queda de performance é na aquisição deste bloqueio exclusivo nos arquivos de dados e log.

Se a propriedade estiver desligada esse bloqueio é mantido desde a inicialização do serviço até sua parada, porém caso a propriedade esteja habilitada, à partir do momento em que não houver mais nenhuma atividade no banco de dados, este bloqueio será liberado e os arquivos de dados e de log ficam disponíveis para qualquer outro processo.

Inicialmente essa situação pode parecer muito interessante, pois poderíamos manipular os arquivos de dados e de log e realizar algumas tarefas administrativas, como por exemplo, uma cópia de segurança.

Agora imagine que durante a cópia de segurança alguma aplicação necessite acessar o banco de dados, o que ocorreria?

Com certeza o SQL Server retornaria um erro alertando a impossibilidade de adquirir o bloqueio exclusivo nos arquivos e com isso a inicialização do banco de dados falharia.

Outro grande problema de performance decorrente da utilização da propriedade AutoClose está relacionado com a utilização das áreas de memória Buffer Cache e Plan Cache.

Sempre que uma consulta é realizada, é gerado um plano de execução que é mantido em uma área de memória chamada Plan Cache. Esta área de memória tem como finalidade armazenar os planos de execução gerados para que possam ser reutilizados se a consulta for executada novamente.

Após a geração do plano de execução, todas os operadores de pesquisa são executados e as páginas de dados selecionadas pela consulta são armazenadas em uma área de memória chamada Buffer Cache. Esta área de memória tem como finalidade armazenar as páginas de dados para que não seja necessário realizar novos acessos ao subsistema de disco e assim otimizar as próximas requisições de I/O.

Quando a propriedade AutoClose está habilitada e não houver mais conexões com o banco de dados, todas as páginas de dados e planos de execução que estiverem em memória serão excluídos, gerando assim uma grande queda de performance.

Vamos executar uma pequena demonstração para que fique bem claro esse comportamento.

Inicialmente habilitaremos a propriedade AutoClose no banco de dados AdventureWorks2012, conforme script abaixo:

USE master
GO
ALTER DATABASE [AdventureWorks2012] SET AUTO_CLOSE ON WITH NO_WAIT
GO

Em seguida vamos realizar alguma consultas no banco de dados AdventureWorks2012, conforme script abaixo:

USE AdventureWorks2012
GO
SELECT * FROM Person.Person
GO
SELECT * FROM Sales.SalesOrderDetail

Após a execução das consultas, é possível analisar, através da DMV sys.dm_os_buffer_descriptors, a quantidade de páginas de dados que foram alocadas em memória para o banco de dados AdventureWorks2012, conforme imagem abaixo:

DFG_1

Com as DMVs sys.dm_exec_cached_plans e sys.dm_exec_sql_text verificamos os planos de execução que foram armazenados em memória para as consultas executadas, conforme imagem abaixo:

DFG_2

Assim que todas as conexões com o banco de dados AdventureWorks2012 estiverem finalizadas, todas as áreas de memória serão limpas, conforme imagem abaixo:

--Quantidade de paginas de dados em memória
SELECT
COUNT(*) TotalPages,
DB_NAME(database_id) AS DBname
FROM
sys.dm_os_buffer_descriptors
GROUP BY
DB_NAME(database_id)
ORDER BY
1 DESC

--Quantidade de planos de execução em memória
SELECT
COUNT(*) AS TotalPlanos
FROM
SYS.dm_exec_cached_plans
CROSS APPLY
sys.dm_exec_sql_text(plan_handle)
WHERE
[dbid] = 7 and objtype='Adhoc'

DFG_3

Com essa demonstração fica extremamente simples concluir que a propriedade AutoClose está sempre desabilitada devido aos graves problemas de performance que pode trazer para um ambiente de banco de dados de alta performance.

Espero que tenham gostado e não deixem de inscrever-se no blog, no canal do youtube e no grupo de discussão SQLManiacs.

Grande abraço a todos.

Anúncios
  1. Nenhum comentário ainda.
  1. No trackbacks yet.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: