#12 – Para que serve

Boa tarde, boa tarde…. Olá pessoal, tudo bem?

Mais uma semana começando, para alguns volta as aulas (kkkkk)…. é a mamata esta acabando e o futuro deste país tem que voltar para sua realidade, no mundo capitalista que estamos vivendo, sem o mínimo de educação civica e moral não somos nada.

Deixando de lado este pequeno pensamento, seguindo em frente este é o post de número 12 dedicado exclusivamente a sessão Para que serve, que lentamente esta atraindo novos seguidores ao meu Blog.

Como você já deve ter percebido os posts relacionados a esta sessão tem o objetivo de apresentar ou demonstrar como  códigos de exemplo, aplicativos, utilitários, enfim recursos relacionados diretamente á banco de dados ou gerenciadores de bancos de dados podem ser utilizados como uma possível solução de problemas, bem como, orientar na sua forma de utilização.

Após esta tradicional saudação, chegou a hora de falar sobre o #12 – Para que serve de hoje, tenho a certeza que você vai gostar.

No post de hoje, vou a destacar uma alteração que a Microsoft introduziu no novo SQL Server 2016, que a partir desta versão alterar de maneira direta o comportamento padrão existente atualmente para alocação de dados e autocrescimento para os bancos de dados de usuário ou para o system database TEMPDB.

Em contra partida, neste post vou destacar um pouco sobre a relação das Trace Flag 1117 e 1118 para com estes dois recursos que compõem o SQL Server, sabendo que durante anos ambas foram recomendadas pelas equipes de engenheiros da Microsoft como técnicas para alterar este comportamento padrão, que a partir da versão 2016 poderá ser realizado de uma maneira bem diferente ou até mesmo de forma automática.

Vamos lá….começa aqui o #12 – Para que serve – Alterando o comportamento padrão para alocação de dados e autocrescimento no Microsoft SQL Server 2016 –

Introdução

Até a versão 2014 o Microsoft SQL Server apresentava o mesmo padrão definido desde a versão 2000 para alocação de dados e autocrescimento de banco de dados, comportamento que poderia ser alterado através do uso de recursos externos entre deles as tão conhecidas e temidas trace flags.

Para um melhor entendimento, vou abordar brevemente os dois conceitos, visando esclarecer um pouco o papel de cada um deles, começando por:

Alocação de Dados: Quando se referimos a alocação de dados em uma instância ou servidor SQL Server, estamos fazendo referência a dois recursos de extrema importância que forma o SQLOS, me refiro ao Database Engine e Storage Engine, sendo estes responsáveis em possibilitar o armazenamento, contenção e consumo de dados manipulados pelo SQL Server.

Como destacado anteriormente o Microsoft SQL Server até a versão 2014 não apresentava a capacidade de criar páginas de dados iniciais ou as primeiras oito páginas de dados conhecidas como extended (extensão) identificadas internamente como páginas ou extensões mista, no qual as primeiras páginas ou extended deveriam se iguais, uniforme, do mesmo tipo e apresentar a mesma estrutura contendo somente informações relacionadas a tabelas ou índices.

Você pode estar se perguntando, mas isso não era possível de ter alterado nas versões mais antigas? A resposta simples e direta é SIM, e para tal finadade eramos obrigados a utilizar a Trace Flags 1118 (se quiser saber mais sobre ela acesse: https://www.brentozar.com/archive/2014/06/trace-flags-1117-1118-tempdb-configuration/)

Mas isso na versão 2016 não é mais necessário, para oferecer e permitir esta mudança de comportamento o time de engenheiros da Microsoft dedicados ao SQL Server aplicaram uma pequena mudança na estrutura da Dynamic Management View: sys.databases existente desde as primeiras versões do produto, na qual foi adicionada uma nova coluna chamada is_mixed_page_allocation_on, que pode ser utilizada através do comando ALTER DATABASE. Falarei um pouco mais sobre esta nova coluna posteriormente.

Dando continuidade, vamos conhecer um pouco sobre AutoGrow (Autocrescimento):

Autocrescimento: Opção aplicada aos bancos de dados que define qual deverá ser a fator e forma de crescimento de um banco de dados, também sofreu algumas mudanças.

A partir do SQL Server 2016 todo processo de autocrescimento e alocação de dados será realizado de forma automática, no qual o Database Engine e parceria com o Storage Engine serão autosuficientes capazes de identificar a necessidade de mudar a forma de alocação e autocrescimento do banco de dados, sem recorrer a necessidade de fazer uso da trace flag 1117.

Desta forma, de acordo com a distribuição dos dados alocados em seus respectivos arquivos de dados ou filegroups permitirá que quando um arquivo de dados crescer todos os demais arquivos relacionados ao banco de dados ou filegroup deverão crescer ao mesmo tempo, sendo este o novo comportamento adotado para este banco de dados, algo revolucionará se levarmos em consideração do processo desempenho pelo Storage Engine para alocar e contar os dados.

Esta mudança de comportamento pode ser considerada uma peça chave para o SQL Server no que se relaciona a performance, pois de maneira simultânea teremos arquivos alocados ao mesmo tempo na mesma transação, oferecendo uma redução no tempo estimado para contenção de alocação de dados, o que no final das contas provacará uma sensível diminuição para o Storage Engine controlar o número de pontos de marcação de dados relacionado ao que está alocado para uso.

MIXED_PAGE_ALLOCATION

Nova opção adicionada ao comando ALTER DATABASE que permite aplicar aos bancos de dados de usuário e system database TEMPDB a nova forma de alocação de dados adotada para a versão 2016 do SQL Server, denominada Mixed Page Allocation ou Alocação de Páginas Mistas, na qual destacado anteriormente será possível alocar para toda estrutura de um banco de dados o uso de páginas ou extended mistas, aplicada de implícita para tabelas e índices.

A coluna is_mixed_page_allocation_on apresenta dois valores, sendo eles:

  • 0 = A estrutura de tabelas e índices será alocada de forma uniforme e não permitirá que as primeras páginas de dados ou a primeira extended possa ser formada por uma estrutura mista.
  • 1 = A estrutura de tabelas e índices poderá ser alocada de forma mista permitindo que as primeras páginas de dados ou a primeira extended possa ser formada por uma estrutura mista.

Vale ressaltar que ao realizar uma simples consulta na DMV sys.databases, valor padrão apresentado na coluna is_mixed_page_allocation_on para os bancos de dados de usuário é 0(zero), e para os bancos de dados de sistema: Master, Model e MSDB é 1(Hum) sendo que para este bancos de dados não é permitido alterar a forma de alocação.

Perguntas e respostas

Muito bem, você pode estar coçando a sua cabeça e ainda contendo algumas dúvidas sobre ese possível novo comportamento entre outros conceitos aqui apresentados, no intuito de tentar ajudar, elaborei algumas perguntas:

1. Afinal esta nova opção Mixed_Page_Allocations possui alguma relação com as trace flags 1117 e 1118?
Respondendo de bate pronto: SIM possuem total relação.

2. Esta nova opção substituio uso de ambas as traces flags?
Sim, tem este finalidade mas aplicada somente a partir da versão 2016.

3. Após alterar a forma de alocação para Mixed Page Allocation posso voltar ao formato anterior?
Sim, sem nenhum tipo de risco ou impedimento.

Além das questões a Tabela 1 apresentada abaixo poderá lhe ajudar a entender em qual cenário você poderá fazer uso da mixed_page_allocation ou das trace flags 1117 e 1118:

Database TF 1117 TF 1118
tempdb Não requerida (default) Não requerida (default)
user databases Por padrão será realizada o autocrescimento de forma simples, ou seja, de um único arquivo por vez. Use ALTER DATABASE <dbname> MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES para habilitar o crescimento para todos os arquivos de forma simultânea Não requerida. Use ALTER DATABASE <dbname> SET MIXED_PAGE_ALLOCATION  ON para voltar a utilizar alocação mista.
Other system databases (master, model, msdb) -NA- Alocação de páginas em modo mista não pode ser alterada para estes bancos de dados.

Tabela 1 – Cenários para uso da alocação mista ou mudança no autocrescimento.

Referências

https://technet.microsoft.com/pt-br/library/ms190969(v=sql.105).aspx

https://msdn.microsoft.com/en-US/library/bb522682.aspx

https://support.microsoft.com/en-us/kb/2964518

https://msdn.microsoft.com/en-us/library/ms178534.aspx

https://msdn.microsoft.com/en-us/library/bb522469.aspx

https://msdn.microsoft.com/en-us/library/ms187782.aspx

Links

Caso você ainda não tenha acessado os posts anteriores desta sessão, fique tranquilo é fácil e rápido, basta selecionar um dos links apresentados a seguir:

https://pedrogalvaojunior.wordpress.com/2016/12/16/11-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2016/11/15/10-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2016/10/08/09-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2016/08/06/07-para-que-serve/

Conclusão

Cuidar da vida de nossos dados é algo muito importante, mas saber como e de que forma estes podem ser armazenados esta bem acima de qualquer outra preocupação, pensando nisso a Microsoft permitiu a partir da versão 2016 alterar de forma simples, rápida e segura a maneira com nossos bancos de dados podem crescer no decorrer do tempo, bem como, as estruturas internas podem ser criadas e alocadas, capacidade que nos permite melhrorar de maneira sensível atividades relacionadas a como nossos dados podem estar alocados para consulta, possibilitando ganhos de processamento de dados.
Neste post você pode mais uma vez observar que o Microsoft SQL Server esta em constante evolução, um dos produtos mais prestigiados pela Microsoft, buscando sempre trazer melhorais e inovações, algo de extrema importância para qualquer profissional que trabalha com esta tecnologia.

Agradecimentos

Mais uma vez obrigado por sua visita, agradeço sua atenção, fique a vontade para enviar suas críticas, sugestões, observações e comentários.

Nos encontramos em breve, até lá…..