Arquivo da tag: Storage Engine

@03 – O que Acontece?

Bom dia!

Feliz 2021, estamos abrindo os trabalhos deste ano em meu blog, como mais um novo post da sessão O que acontece.

Seja bem-vindo ao @03 – O que Acontece?

Seguindo a tradição, antes de apresentar o terceiro post, quero destacar alguns pontos sobre esta sessão, em especial o tipo de conteúdo que você leitor vai encontrar em cada post relacionado a ela:

1 – Os posts publicados nesta sessão envolvem um pouco do Micrososft SQL Server, na verdade o objetivo dela é tentar mostrar como ele SQL Server, o qual aqui eu vou denominar como caixa, pensa exclusivamente fora dela, ou seja, como este grandioso SGBD (Sistema Gerenciador de Banco de Dados) faz para que tenhamos nossos dados armazenados e apresentados em tela, em adicional como podemos de uma forma simples aprender, conhecer, desvendar os comportamentos realizados por ele além do uso das habituais ferramentas de administração: SQL Server Management Studio ou Azure Data Studio.

2 – Os posts, não apresentam o objetivo de demonstrar recursos, comandos, funcionalidades ou ensinar algo novo, na verdade eu quero tentar mostrar o que acontece muitas vezes quando por exemplo você esta processando um simples comando Select buscando milhões de dados em uma tabela, e o SQL Server Management Studio vai apresentando aos poucos os registros. Serão exclusivamente estes cenários, comportamentos e formas de atuação envolvendo o SQL Server e o ambiente que ele se encontra;

3 – Os posts, não terão uma estrutura padrão, na verdade, O que Acontece, foi idealizado da mesma forma que inicialmente começamos a pensar. Vamos reunindo conhecimentos, ideias, possibilidades, hipóteses, analisando alternativas, mensurando teorias, até tentar construir algo mais concreto;

4 – Não será estabelecido um calendário de publicação, ao contrário, sempre que algum pensamento fora da caixa pairar sobre a minha cabeça, ou coisas do meu dia-a-dia relacionados as minhas experiências profissionais ou acadêmicas, novos posts serão publicados, bem como, os atuais atualizados e corrigidos;

5 – Não vou me ater ao certo ou errado, melhor ou pior, tecnicamente perfeito ou melhor tecnicamente, o que eu quero é tentar como eu já destaquei ilustrar o que acontece do lado de fora do SQL Server, o que ele muitas vezes esta realizando e não temos ideia do que está acontecendo; e

6 – Vou tentar em cada post trazer uma ferramenta, aplicativo ou utilitário existente no próprio sistema operacional que possa nos ajudar a observar e entender o que está acontecendo de preferência em tempo real, em adicional, se possível utilizarei vídeos para elucidar de forma mais didática o objetivo do post.

Continuar lendo →

#26 – Para que serve

Olá, pessoal, bom dia.

Como vocês estão? Já fazem alguns meses que não nos encontramos nesta sessão em meu blog, fico feliz em te encontrar novamente. Seja bem-vindo a mais um post da sessão Para que Serve, post de número 26, em mais um dia de muitas atividades, afazeres e compromissos profissionais, domésticos (sim, eu adoro ajudar a minha esposa, cuidar da nossa casa…..) e claro acadêmicos.

Neste post quero destacar uma parte de um dos recursos mais importantes, impactantes e tradicionais do Microsoft SQL Server existente deste sua primeira versão, o qual esta totalmente relacionamento com o comportamento de nossas transações, querys e processamentos que possam estar sendo realizados neste momento em nossos servidores ou instâncias.

Sendo direto e reto no assunto, você que esta neste momento lendo este post e trabalhando com seus dados, tabelas e bancos no SQL Server esta fazendo uso dele sem talvez saber que ele exista, me refiro ao tradicionais Níveis de Isolamento de Transações ou Transaction Isolation Levels.

Você se lembra da existência deste recurso e o quanto ele é importante? Pois bem, caso não se lembra, a partir deste post e provavelmente os próximos 2 ou 3 futuros serão dedicados nesta sessão a apresentar de forma simples, prática e muito didática como podemos fazer uso deste recurso em nossas transações, seus comportamentos, vantagens e desvantagens (isso se elas existirem) e principalmente os riscos ao fazer uso talvez de uma forma não muito indicada.

Sendo assim, chegou a hora de conhecer um pouco mais sobre o post de número 25 da sessão Para que serve. Mas uma vez, bem vindo ao #26 – Para que serve – Apresentando os nível de isolamento Read Uncommitted.

Espero que você esteja animado para conhecer um pouco mais sobre este recurso, caso já conheça, continue lendo este post, sempre podemos aprender algo novo….

Continuar lendo →

#25 – Para que serve

Bom dia, bom dia, bom dia, hoje estou tão feliz (kkkkk)….

Isso parece música, parece não, na verdade é…, aquela musiquinha cantadas todos os dias na entrega da escola, quem nunca cantou esta musiquinha.

Seja bem-vindo a mais um post da sessão Para que Serve, sendo este o de número 25, em mais um dia de muitas atividades, afazeres e compromissos profissionais, domésticos (isso eu não posso comentar aqui….kkkkk) e acadêmicos.

No post de hoje, vou compartilhar com você que esta acessando meu blog, um dos mais tradicionais recursos existentes no Microsoft SQL Server introduzido desde as versões iniciais do produto, mantendo-se sempre atualizada, úteis e principalmente apresentando um pouco perigoso ao ser utilizada quando não se conhece.

Da mesma maneira que realizei no post anterior desta sessão, serei mais direto no assunto, sem qualquer tipo de mistério, suspense ou criar qualquer clima de expectativa sobre este tema, na verdade estou me referindo ao uso das Table Hints (dicas de tabelas), com certeza você já deve ter ouvido falar ou já utilizou pelo menos uma delas.

Ainda não? Não se lembra? Fique tranquilo(a), vou tentar te ajudar a conhecer pouco destas pequenas mas grandiosas funcionalidades existentes no Microsoft SQL Server.

Sendo assim, chegou a hora de conhecer um pouco mais sobre o post de número 25 da sessão Para que serve. Mas uma vez, bem vindo ao #25 – Para que serve – Table Hint – Serializable.

Espero que você esteja animado para conhecer um pouco mais sobre esta funcionalidade, caso já conheça, continue lendo este post, sempre podemos aprender algo novo….

Introdução

As dicas de tabela tem um papel muito importante a partir do momento que decidimos fazer uso delas, alterar o comportamento padrão do otimizador de consulta durante a instrução DML (linguagem de manipulação de dados) ao especificar um método de bloqueio, um ou mais índices, uma operação de processamento de consulta, como uma verificação de tabela ou busca de índice, ou outras opções. As dicas da tabela são especificadas na cláusula FROM da instrução DML e afetam apenas a tabela ou exibição referenciada nessa cláusula.

Mesmo sabendo que o otimizador de consulta do SQL Server normalmente seleciona o melhor plano de execução para uma consulta, muitos desenvolvedores e administradores de banco de dados tomam a decisão de fazer uso deste recurso em diversos cenários de forma indiscriminada.

Estas dicas ou table hints, podem ao longo de sua utilização impactar de forma direta na maneira que o otimizador de consultas existente no Microsoft SQL Server trabalha desde o momento da identificação da análise da query e criação do seu plano de execução, mas principalmente no momento da própria execução.

Vale ressaltar que as mesmas serão ignoradas se a tabela não for acessada pelo plano de consulta, isso pode ser provocado porque o query optimizer opta por não acessar a tabela ou porque uma exibição indexada é acessada, mas este cenário também pode ser contornado, quem sabe em outro momento.

Vamos em frente, sabendo que estas funcionalidades precisam ser um pouco mais conhecidas e melhor entendidas, tomei a decisão de elaborar este post, tentando detalhar um pouco mais sobre a table hint Serializable.

Desta forma, vou apresentar uma breve descrição sobre esta dica de tabela, logo na sequência utilizarei um simples cenário prático para demonstrar como podemos fazer da mesma.

Como diria Chapolin Colorado “Siga-me os bons…..”

Table Hint – SERIALIZABLE

A dica de tabela SERIALIZABLE está na outra extremidade do espectro da dica de tabela NoLock (eu costume dizer bem diretamente que ela faz o papel inverso ao NoLock).

Se comparada ao NoLock, a Serializable, apresenta uma consistência extremamente alta, não permite leituras sujas, leituras não repetíveis e leituras fantasmas de dados, o que fortalece ainda mais a aplicação dos pilares ACID: (Atomicidade, Consistência, Isolamento e Durabilidade).

Quando você usa a dica de tabela SERIALIZABLE (também conhecida como HOLDLOCK), ela garante que nenhuma outra transação pode modificar ou ler dados não confirmados na transação atual. Em alguns casos, podemos dizer que ela se torna semelhante ao HOLDLOCK, porém aplica aos locks restrições maiores ao longo da transação.

Em outras palavras, as transações devem aguardar que outras transações sejam concluídas antes de concluir seu trabalho. Isso limita drasticamente a simultaneidade de banco de dados e coloca um prêmio na consistência do banco de dados. Outro ponto importante quando se referimos a table hint Serializable, se relaciona e existência de um nível de isolamento similar a seu comportamento conhecido Isolation Level Serializable.

Para muitos profissionais de SQL Server, ela é uma variação mais completa da table hint Repeatable Read, bloqueando qualquer modificação de dados nas colunas que são consultadas até que sejam concluídas, independente da operação ser um Update ou Insert, esse comportamento fornece uma alta consistência, mas ao custo de uma baixa concorrência.

Agora que conhecemos um pouco mais sobre esta table hint, vamos avançar, conhecer o seu funcionamento de forma prática, para tal, utilizaremos como base o Bloco de Código 1, que realizará a criação do seguinte ambiente:

· Databases: DatabaseTableHints;

· Table: SerializableTable; e

· Transações: TST e TSTII.

— Bloco de Código 1 —

— Criando o Banco de Dados – DatabaseTableHints —
Create Database DatabaseTableHints
Go

— Acessando o Banco de Dados – DatabaseTableHints —
Use DatabaseTableHints
Go

— Criando a Tabela – SerializableTable —
Create Table SerializableTable
(Codigo Int Identity(1,1) Not Null Primary Key Clustered,
Valores Varchar(30))
Go

Até aqui nada de especial, nosso ambiente já está criado e pronto para receber alguns dados, vamos utilizar o Bloco de Código 2, responsável em realizar o insert e consulta dos dados, conforme apresento a seguir:

— Bloco de Código 2 —
— Inserindo uma pequena porção de dados —
Insert Into SerializableTable
Values (‘Pedro’), (‘Antonio’), (‘Galvão’), (‘Junior’),
(‘MVP’), (‘MCC’), (‘MSTC’), (‘MIE’), (‘SQL Server’),
(‘Banco de Dados’),(‘Table Hint SerializableTable’)
Go

— Consultando os dados —
Select Codigo, Valores From SerializableTable
Order By Valores Desc
Go

A executarmos o comando Select declarado acima, deveremos ter um result set semelhante ao apresentado na Figura 1 a seguir:

Figura 1 – Processamento do comando Select apresentando os dados inseridos na Table SerializableTable.

Avançando mais um pouco, neste momento temos uma porção de dados armazenados em nossa table SerializabelTable, o que nos permite realizar uma pequena simulação de seu comportamento e entender como a ela realiza o bloqueio em nossa query.

Vamos então realizar a execução do Bloco de Código 3, sendo este o ponto chave que vai nortear nosso entendimento, observe que estamos abrindo um bloco transacional denominado TST (conforme já destacado anteriormente).

— Bloco de Código 3 —
— Iniciando um novo Bloco de Transações denominada TST —
Begin Transaction TST

Observe que estou fazendo uso do comando Begin Transaction abrindo nosso bloco de transação denominado TST, o qual consiste na execução do comando Select em conjunto com table hint Serializable.

Neste instante o Microsoft SQL Server em conjunto com alguns de seus mais importantes componentes: Database Engine e Storage Engine, estabelecem uma nova transação que deverá ser processada dentro da sessão que estamos trabalhando mais que a mesma não recebeu até o presente momento nenhuma instrução Commit ou Rollback.

Select * From SerializableTable With (Serializable) — Especificando a Table Hint Serializable —
Go

— Forçando um Delay de 10 segundos para gerar bloqueio no nível de leitura compartilhadas —

WaitFor Delay ’00:00:10′
Go

Importante: O uso do comando WaitFor, vai nos ajudar a ter tempo para abrir uma nova query e executar os próximos passos que deveremos realizar, note que abaixo já estou declarando o comando Commit para confirmar a execução da nossa transação, que na verdade consiste na busca dos linhas de dados existentes na tabela SerializableTable e apresentadas em tela.

Commit Transaction TST
Go

A Figura 2 apresentada abaixo, ilustra o processamento do Bloco de Código 3, o que nos permite identificar a ocorrência do Delay de 10 segundos:

Figura 2 – Processamento do Bloco de Código 3.

Muito bem, agora é a hora da verdade, em paralelo a execução do Bloco de Código 3, vamos executar vamos executar o Bloco de Código 4, o qual vai nos exigir abrir uma nova query dentro da ferramenta que você se encontra acessando o Microsoft SQL Server no momento, eu como de costume estou utilizando o bom e velho Management Studio.

— Bloco de Código 4 —
— Abrir nova query e executar o Select abaixo, após 10 segundos os dados serão apresentados —
Begin Transaction TSTII
Select ‘Aguardando…’ As ‘Passo 1…’
Go

Select GetDate() As ‘Passo 2 – Update Realizado…’

Update SerializableTable
Set Valores = ‘SerializableTable’
Where Codigo = 11
Go

Select GetDate() As ‘Passo 3 – Apresentar dados…’
Go

Select Codigo, Valores From SerializableTable
Where Codigo = 11

Commit Transaction TSTII
Go

Select GetDate() As ‘Transações confirmadas…’
Go

Posso dizer que a execução do Bloco de Código 4, aparentemente foi super tranquilo e simples, mas tenho a certeza que após sua execução você conseguiu entender o funcionamento da table hint Serializable.

A Figura 3 a seguir, apresenta o resultados obtidos através do processamento deste bloco de código:

Figura 3 – Realização do bloco de transação TSTII em paralelo ao TST.

Você pode observar que a partir do momento que nossa primeira transação TST estava sendo processada, a segunda transação TSTII teve seu processamento iniciado, mas ficou aguardando a confirmação de conclusão da TST.

Isso não é fantástico, com certeza é, realmente o uso das table hints em situações ou necessidades específicas pode ser de grande avalia em nossas atividades, mas calma, muita calma neste hora, pois não devemos utilizar nenhum recurso ou funcionalidade sem conhecer melhor seu comportamento, este foi justamente o objetivo principal do post de hoje.

Sem mais delongas, chegamos ao final, foi muito legal poder apresentar um pouco sobre os níveis de isolamento existentes no Microsoft SQL Server.

Claro de que um pouco de trabalho este post, mas já estou acostumado, mesmo assim sempre vale a pena poder compartilhar um pouco de conhecimento e experiências adquiridas ao longo dos anos de trabalho como DBA e Professor.

Espero que você tenha gostado, eu posso dizer que sim, mas sua opinião é muito importante.

Referências

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-2017

https://sqlperformance.com/2014/04/t-sql-queries/the-serializable-isolation-level

https://www.techrepublic.com/article/using-advanced-table-hints-in-sql-server/

https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table

https://pedrogalvaojunior.wordpress.com/2019/02/23/24-para-que-serve/

http://www.verycomputer.com/156_c5e201f3b1bce12f_1.htm

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/2019/02/23/24-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2018/12/21/23-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2018/08/22/22-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2018/05/28/21-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2018/04/12/20-para-que-serve/

Conclusão

Mais uma vez podemos observar como o Microsoft SQL Server apresenta um conjunto grandioso de funcionalidades, utilizadas de maneiras diferentes e específicas para cada cenário.

Neste post podemos conhecer, aprender e visualizar como as table hint Serializabel pode ser utilizada, seus comportamento, além disso, entender como uma consulta usa o nível de isolamento SERIALIZABLE ou a table hint HOLDLOCK.

Demonstrei como o Microsfot SQL Server pode aplicar bloqueios compartilhados até que uma transação seja concluída, papel fundamente para qualquer SGBD de alto nível, ainda mais o SQL Server, que possui a capacidade de compreender e permitir que bloqueios ocorram e possam ser feitos durante o processamento de diversas querys ou transações.

Este é o fantástico Microsoft SQL Server, que desde suas primeiras versões nos apresenta inúmeras possibilidades de se aprender, possibilitando usar o passado como fonte de inspiração para construção de um futuro melhor, por isso que a cada dia eu me apaixono ainda mais por este produto…

Vai SQL Server, Vai SQL Server….

Agradecimentos

Mais uma vez obrigado por sua ilustre visita, sinto-me honrado com sua presença, espero que este conteúdo possa lhe ajudar e ser útil em suas atividades profissionais e acadêmicas.

Um forte abraço, até o próximo post da sessão Para que serve a ser publicado no mês agosto de 2019.

Um grande abraço e ótimo final de semana.

Valeu.

#24 – Para que serve

Olá, bom dia, que bom te ver por aqui, neste sabadão, estamos entrando na semana que antecede a maior festa do Brasil.

Seja bem-vindo a mais um post da sessão Para que Serve, sendo este o de número 24, mais um dia de muito trabalhado, repleto de atividades e compromissos.

De uma forma bem diferente e mais direta, não vou realizar suspense ou criar qualquer clima de expectativa sobre este tema, na verdade estou me referindo ao uso das Table Hints (dicas de tabelas), com certeza você já deve ter ouvido falar ou já utilizou pelo menos uma delas, adianto que neste post não vou falar da mais conhecida a NoLock e sim de outras duas que também são importantes.

Sendo assim, chegou a hora de conhecer um pouco mais sobre o post de número 24 da sessão Para que serve. Mas uma vez, bem vindo ao #24 – Para que serve – Table Hint – UpdLock e TabLock.

Espero que você esteja animado para conhecer um pouco mais sobre estas funcionalidades, caso já conheça, continue lendo este post, sempre podemos aprender algo novo….

Introdução

As dicas de tabelas podem ao longo de sua utilização impactar de forma direta na maneira que o otimizador de consultas existente no Microsoft SQL Server trabalha desde o momento da identificação da análise da query e criação do seu plano de execução, mas principalmente no momento da própria execução.

Vale ressaltar que as table hints serão ignoradas se a tabela não for acessada pelo plano de consulta, isso pode ser provocado porque o query optimizer opta por não acessar a tabela ou porque uma exibição indexada é acessada, mas este cenário também pode ser contornado, quem sabe em outro momento.

Vamos em frente, sabendo que estas funcionalidades precisam ser um pouco mais conhecidas e melhor entendidas, tomei a decisão de elaborar este post, destacando as duas dicas de tabela que atualmente acabei me deparando em cenários de consultoria, me refiro a:

· UpdLock; e

· Tablock.

Desta forma, vou apresentar uma breve descrição destas duas dicas de tabelas, e posteriormente utilizar um simples cenário prático para demonstrar como podemos fazer uso de ambas, começando pela UpdLock.

Table Hint – UpdLock

Sendo está uma das mais antigas dicas de tabela existente no Microsoft SQL Server, reconhecida como uma table hint exclusiva para seu uso no comando update, o que na verdade não se aplicada, mesmo sendo este o cenário mais comum de uso podemos em determinados momentos aplicar a instruções Select e Insert.

Seu papel é especificar que bloqueios de atualização serão usados e mantidos até que a transação seja concluída. UpdLock utiliza bloqueios de atualização apenas em operações de leitura no nível de linha ou de página.

Caso o UpdLock venha a combinado com TabLock, ou se um bloqueio em nível de tabela for usado por outro motivo, um bloqueio exclusivo reconhecido pela sigla (x) será usado.

Importante destacar que sempre que um UpdLock é especificado, as dicas em nível de isolamento ReadCommitted e ReadCommittedLock são ignoradas, sendo assim, podemos em alguns cenários se deparar com a ocorrência de dados fantasmas.

Agora que conhecemos um pouco mais sobre esta table hint, vamos avançar, conhecer seu funcionamento de forma prática, para tal, utilizaremos como base o Bloco de Código 1, que realizará a criação do seguinte ambiente:

· Databases: TesteDatabaseTableHintUpdLock;

· Tables: UpdLockTable; e

· Transações: TUPD e TUPDII.

— Bloco de Código 1 —
— Criando o Banco de Dados – TesteDatabaseTableHintUpdLock —
Create Database TesteDatabaseTableHintUpdLock
Go

— Acessando o Banco de Dados – TesteDatabaseTableHintUpdLock —
Use TesteDatabaseTableHintUpdLock
Go

— Criando a Tabela – UpdLockTable —
Create Table UpdLockTable
(Codigo Int Identity(1,1) Not Null Primary Key Clustered,
Valores Varchar(20))
Go

Até aqui nada de especial, nosso ambiente já está criado e pronto para receber alguns dados, para tal, utilizaremos o Bloco de Código 2, responsável em realizar o insert e consulta dos dados, conforme apresento a seguir:

— Bloco de Código 2 —
— Inserindo uma pequena porção de dados —
Insert Into UpdLockTable
Values (‘Pedro’), (‘Antonio’), (‘Galvão’), (‘Junior’),
(‘MVP’), (‘MCC’), (‘MSTC’), (‘MIE’), (‘SQL Server’),
(‘Banco de Dados’),(‘Table Hint UpdLock’)
Go— Consultando os dados —
Select Codigo, Valores From UpdLockTable
Order By Valores Desc
Go

A partir deste momento temos uma porção de dados armazenados em nossa table UpdLockTable, o que nos permite realizar uma pequena simulação de seu comportamento e entender como a ela realiza o bloqueio em nossa query.

— Bloco de Código 3 —
— Iniciando um novo Bloco de Transações denominada TUPD —
Begin Transaction TUPD

— Realizando o Update —

Update UpdLockTable
Set Valores = ‘BD’
Where Codigo = 10
Go

No próximo passo a ser realizado aparentemente pode parecer bastante simples, mas após sua execução você vai entender que nem sempre é assim que as coisas podem ser interpretadas.

Seguindo em frente, vamos executar o Bloco de Código 4, o qual vai nos exigir abrir uma nova query dentro da ferramenta que você se encontra acessando o Microsoft SQL Server no momento, eu como de costume estou utilizando o bom e velho Management Studio.

— Bloco de Código 4 —

Select Codigo, Valores From UpdLockTable
Order By Valores Desc
Go

Acredito que você deva ter feito a execução deste simples bloco de código e até o presente momento o Microsoft SQL Server ainda não te retornou nenhum dado. Não é verdade?

A Figura 1 pode te ajudar a comprovar e responder este meu questionamento:

Figura 1 – Execução do bloco de código 4 sendo realizada e nenhum dado retornado em tela.

Viu, nem tudo que parece ser fácil realmente é! Mas o que pode ter acontecido?

Na verdade, o SQL Server está trabalhando da forma correta, realizamos o processamento de um comando Update dentro de um bloco de transações que ainda não foi encerrado e internamente o bloqueio da tabela foi aplicada de forma geral o que impede que quaisquer outras transações que venha a acessar a tabela como um todo consiga.

Mas se você observou em nosso Bloco de Código 3 não fizemos uso da table hint UpdLock, é justamente este o ponto chave, a partir do momento em que fazemos uso desta dica de tabela teremos a capacidade de acessar nossos dados mesmo que estes se encontrem dentro de outra transação ou bloqueio.

Para confirmar esta mudança de comportamento, vamos executar agora o Bloco de Código 5 e logo na sequência abrir uma nova query e observar o resultado apresentado na Figura 2:

— Bloco de Código 5 —

RollBack Transaction TUPD — Encerrando a transação anterior

— Realizar novamente o Update agora com UpdLock e Abrir nova Sessão —
Begin Transaction TUPDII

Update UpdLockTable With (UpdLock)
Set Valores = ‘Forçando UpdLock’
Where Codigo = 11
Go

Figura 2 – Select realizado em outra query mesmo com a transação TUPDII ainda não encerrada.

Show, não é verdade, neste momento o Microsoft SQL Server acaba de retornar nossos dados manipulados na tabela UpdLock a qual se encontra pertencente a um bloqueio de atualizações de dados que pode estar sendo aplicado no nível de linha ou página de dados (mas este comportamento e estudo vou deixar para outro momento).

Com isso, conhecemos a table hint UpdLock, ainda não terminamos nossa caminhada, podemos evoluir mais um pouco nesta jornada, conhecendo agora a dica de tabela TabLock na sequência.

Table Hint – TabLock

Também uma das mais tradicionais dicas de tabela, e durante minha pesquisa e entendimento sobre esta table hint, cheguei a conclusão que a ela é muito misteriosa, devido ao seu comportamento.

Aonde a mesma tem a função de especificar que o bloqueio adquirido seja aplicado no nível de tabela, o tipo de bloqueio que é adquirido depende da instrução que está sendo executada.

Por exemplo:

1. Uma instrução Select pode adquirir um bloqueio compartilhado. Ao especificar TABLOCK, o bloqueio compartilhado é aplicado à tabela inteira, e não no nível de linha ou página. Se HoldLock também for especificado, o bloqueio de tabela será mantido até o final da transação; e

2. Ou então quando usado com o provedor de conjuntos de linhas em massa através do uso de uma instrução OPENROWSET para importar dados em uma tabela, TabLock permite que vários clientes carreguem dados simultaneamente na tabela de destino com o registro em log e o bloqueio otimizados.

Estamos preparados para seguir em frente, conhecemos um pouco mais sobre esta table hint, chegou a hora de conhecer o seu funcionamento de forma prática, para tal, utilizaremos como base o Bloco de Código 6, que realizará a criação do seguinte ambiente:

· Databases: TesteDatabaseTableHintTabLock;

· Tables: TabLockTable; e

· Transações: TTBL e TTBLII.

— Bloco de Código 6 —
— Criando o Banco de Dados – TesteDatabaseTableHintTabLock —
Create Database TesteDatabaseTableHintTabLock
Go

— Acessando o Banco de Dados – TesteDatabaseTableHintTabLock —
Use TesteDatabaseTableHintTabLock
Go

— Criando a Tabela – TabLockTable —
Create Table TabLockTable
(Codigo Int Identity(1,1) Not Null Primary Key Clustered,
Valores Varchar(20))
Go

Até aqui nada de especial, nosso ambiente foi criado, preparado para receber alguns dados, para tal, utilizaremos o Bloco de Código 7, responsável em realizar o insert e consulta dos dados, conforme apresento a seguir:

— Bloco de Código 7 —

— Inserindo uma pequena porção de dados —
Insert Into UpdLockTable
Values (‘Pedro’), (‘Antonio’), (‘Galvão’), (‘Junior’),
(‘MVP’), (‘MCC’), (‘MSTC’), (‘MIE’), (‘SQL Server’),
(‘Banco de Dados’),(‘Table Hint TabLock’)
Go

— Consultando os dados —
Select Codigo, Valores From UpdLockTable
Order By Valores Desc

Nossa porção de dados encontra-se armazenados em nossa table TabLockTable, o que nos permite realizar uma pequena simulação de seu comportamento e entender como ela realiza o bloqueio em nossa query.

Vamos então realizar a execução do Bloco de Código 8, sendo este o ponto chave que vai nortear nosso entendimento, observe que estamos abrindo um bloco transacional denominado TTBL (conforme já destacado anteriormente).

— Bloco de Código 8 —

— Iniciando um novo Bloco de Transações denominada TTBL —

Begin Transaction TTBL

— Utilizando o TabLock —
Begin Transaction TTBL

Declare @Contador TinyInt = 1

Select ‘Início…’

While @Contador < 255
BeginUpdate TabLockTable
Set Valores = @Contador
Where Codigo = 10

Select Valores From TabLockTable
Where Codigo = 10

Set @Contador += 1
End

Select ‘Fim…’

Observação: Você deve ter notado um pouco de lentidão na execução de bloco de código, bem como, o uso de alguns comandos e técnicas que podem ser melhoradas ou até mesmo substituídas, ressalto que o mesmo não possui o objetivo de ser executado no menor tempo possível ou até mesmo ser entendimento como uma técnica para inserção de dados. Este bloco de código foi criado e elaborado somente para esta prática, sem qualquer finalidade de uso em cenários reais.

Dando continuidade, enquanto nosso Bloco de Código 8 é processado, vamos abrir uma nossa query e executar o Bloco de Código 9 e observar se algo de errado ou diferente acontece:

— Bloco de Código 9 —
— Inserindo uma nova linha de registró lógico na TableTableLock durante o processamento do bloco de código 8 —

Insert Into TabLockTable Values (‘Teste TabLock’)
Go

Tenho a certeza que você imaginava que o SQL Server iria demorar para processar nossa query ou simplesmente não iria retornar nada em tela, da mesma forma que a UpdLock.

Não!

Ele mais uma vez está trabalhando de forma certa, da mesma maneira que fizemos anteriormente não utilizamos de forma proposital a table hint TabLock em nosso bloco de código, algo que vamos realizar agora utilizando os blocos de código 10 e 11, declarados a seguir:

— Bloco de Código 10 —

Rollback Transaction TTBL — Encerrando a transação anterior —

— Adicionar TabLock —
Begin Transaction TTBLII

Declare @Contador TinyInt = 1

Select ‘Início…’

While @Contador < 255
BeginUpdate TabLockTable With (TabLock)
Set Valores = @Contador
Where Codigo = 10

Select Valores From TabLockTable
Where Codigo = 10

Set @Contador += 1
End

Select ‘Fim…’
Go

— Bloco de Código 11 —

— Abrir nova query e realizar Insert vai ocorrer bloqueio —
Insert Into TabLockTable Values (‘Teste TabLock II’)
Go

Para nossa surpresa, o Bloco de Código 10 já deve ter sido processado, mas o 11 ainda se encontra em processamento, a Figura 3 apresentada abaixo, comprova este comportamento:

Figura 3 – Bloco de código 11 ainda em execução, aguardando algum retorno do bloco de código 10, mesmo após sua execução.

Você pode estar questionamento a si mesmo, o porquê deste comportamento por parte do nosso Microsoft SQL Server. Não precisa ficar preocupado, por padrão o Database Engine em conjunto com o Storage Engine estão aplicando em tempo de execução um bloqueio em nossa tabela TabLockTable, ou seja, a mesma ficou bloqueada pela operação de Update que nos disparamos no bloco de código 10 em conjunto table hint TabLock.

Para que possamos concluir a execução do Bloco de Código 11, precisamos encerrar nossa transação aberta anteriormente denominada TTBLII, para tal vamos executar o Bloco de Código 12 dentro da mesma query utilizada para executar o Bloco de Código 10:

— Bloco de Código 12 —

— Confirmando e encerrando a transação TTBLII dentro da mesma sessão do bloco de código 10 —

Commit Transaction TTBLII
Go

E como um passe de mágica, o Bloco de Código 11 foi encerrado simultaneamente após o processamento do Bloco de Código 12.

Vou deixar para que você mesmo comprove este resultado.

Não é algo realmente surpreendente, como a mesma funcionalidade utilizada de formas similares ou parecidas possa ser comportar de maneiras tão diferentes.

Eu sou suspeito a dizer, mas não podemos dizer que isso é um passe de mágicas ou truque, ao contrário, existe um conjunto de propriedades relacionadas ao controle de transacional e bloqueios que os Sistemas Gerenciadores de Bancos de Dados utilizam para garantir que tudo funcione da melhor forma possível chamado ACID:

Atomicidade;
Consistência;
Isolamento; e
Durabilidade.

Quem sabe em um outro post eu aborde este tema, por hoje, vou ficando por aqui.

Com isso, e sem mais delongas, chegamos ao final. Ufa deu um pouco de trabalho este post, como de costume, mesmo assim sempre vale a pena poder compartilhar um pouco do conhecimento e experiências adquiridas ao longo dos anos de trabalho como DBA e Professor.

Espero que você tenha gostado, eu posso dizer que sim, mas sua opinião é muito importante.

Referências

https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table

http://sqlissuessa.blogspot.com/2016/07/table-hints-transact-sql.html#!

https://sqlhelp.wordpress.com/2009/01/13/table-hints-transact-sql/

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d2e78faa-9c79-4faf-8232-5ae08a5dd23e/sql-table-hints-not-working-updlock-holdlock

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a7536fc4-ed1e-44aa-a16b-d4dc9ca6cd70/what-is-the-difference-between-holdlock-and-updlock-in-sql-server

https://www.sqlservercentral.com/Forums/Topic1227171-392-1.aspx

http://www.sql-server-performance.com/2004/2000-table-hints/

https://pedrogalvaojunior.wordpress.com/2018/03/01/script-challenge-13-a-resposta/

https://pedrogalvaojunior.wordpress.com/tag/trace-flag/

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/2018/12/21/23-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2018/08/22/22-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2018/05/28/21-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2018/04/12/20-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2018/01/02/19-para-que-serve/

Conclusão

Mais uma vez podemos observar como o Microsoft SQL Server apresenta um conjunto grandioso de funcionalidades, aplicando formas diferentes e especificas para cenários simulares em suas estruturas de código, mas que durante sua execução pode ser entendida de uma maneira muito particular.

Neste post podemos conhecer, aprender e visualizar como as table hints UpdLock e TabLock são utilizadas, seus comportamentos, e principalmente como os bloqueios realizados por ambas podem ser aplicar para um mesmo cenário no caso o uso do comando Update dentro de um bloco de transações.

Vai SQL Server, Vai SQL Server….

Agradecimentos

Mais uma vez obrigado por sua ilustre visita, sinto-me honrado com sua presença, espero que este conteúdo possa lhe ajudar e ser útil em suas atividades profissionais e acadêmicas.

Um forte abraço, até o próximo post da sessão Para que serve a ser publicado no mês maio de 2019.

Um grande abraço e ótimo final de semana.

Valeu.

#23 – Para que serve

Olá, bom dia, tudo bem? E ai preparado para festividades de final de Ano?

Seja bem-vindo a mais um post da sessão Para que Serve, sendo este o de número 23, mais um dia de muito começando, repleto de atividades e compromissos, ainda mais hoje sexta – feira e muito próximo ao Natal.

Mesmo no ritmo de fim de ano, tenho mantido a minha rotina, acordar bem cedo, para poder aproveitar da melhor maneira possível meu precioso tempo livre, colocando em prática algo que adoro fazer, publicar um post novo em meu blog mantendo a tradição de querer renovar e compartilhar as experiências e aprendizados adquiridos em minhas atividades profissionais e acadêmicas.

No post de hoje, vou compartilhar com você que esta acessando meu blog, uma nova opção adicionada diretamente aos bancos de dados que criamos a partir do Microsoft SQL Server 2016 que nos permite que seja utilizada de forma exclusiva no nível de banco de dados ao invés de aplicar diretamente no nível de instância.

Talvez você já possa ter utilizada esta nova opção, mas tenho a certeza que muitos dos profissionais que ainda não migraram seus ambientes para versões mais novas talvez não a conheçam, estou me referindo a opção AUTOGROW_ALL_FILES, que basicamente define no nível de banco de dados (Database Level) ao contrário do que acontecia nas versões anteriores que tínhamos a necessidade de ativar a Trace Flag T1117 que aplicava esta alteração de comportamento padrão no nível de instância (Server Level).

No decorrer deste post será realizado um pequeno comparativo entre as opções AUTOGROW_ALL_FILES e AUTOGROW_SINGLE_FILE, analisando seus comportamentos padrões, tendo como base um simples bloco de código inserindo 500.000 (Quinhentas mil linhas de registros lógicos) com dados fixos.

Adianto que esta análise comparativa em nenhum momento leva em consideração características de Hardware, versão de Sistema Operacional ou uso de uma aplicação específica. Na verdade o objetivo desta simples análise é elucidar que o uso destas opções podem influenciar na maneira que o Microsoft SQL Server utiliza um ou mais arquivos de dados de forma proporcional ou simultânea.

Sendo assim, chegou a hora de conhecer um pouco mais sobre o post de número 23 da sessão Para que serve. Mas uma vez, bem vindo ao #23 – Para que serve – Opções de Bancos de Dados – AUTOGROW_ALL_FILES versus AUTOGROW_SINGLE_FILE.

Espero que você esteja animado para conhecer um pouco mais sobre esta propriedade, caso já conheça, continue lendo este post, sempre podemos aprender algo novo….

Introdução

Todo o banco de dados SQL Server tem, no mínimo, dois arquivos de sistema operacional: um arquivo de dados e um arquivo de log. Os arquivos de dados contêm dados e objetos como tabelas, índices, procedimentos armazenados e exibições.

Os arquivos de log contêm as informações necessárias para recuperar todas as transações no banco de dados, ao contrário dos arquivos de dados que podem ser agrupados em grupos de arquivos para propósitos de alocação e administração.

Os bancos de dados SQL Server possuem três tipos de arquivos, como mostrado na Tabela 1 a seguir:

Arquivo	Descrição
Primário	O arquivo de dados primário contém as informações de inicialização do banco de dados e aponta para os outros arquivos no banco de dados.
Primário	Dados do usuário e objetos podem ser armazenados neste arquivo ou em arquivos de dados secundários. Todo banco de dados possui um arquivo de dados primário. A extensão de nome de arquivo indicada para arquivos de dados primários é .mdf.
Secundário	Os arquivos de dados secundários são opcionais, definidos pelo usuário, e armazenam dados do usuário.
	Arquivos secundários podem ser usados para distribuir os dados entre os diversos discos, colocando cada arquivo em uma unidade de disco diferente. Além disso, caso um banco de dados exceda o tamanho máximo em um único arquivo Windows, será possível usar arquivos de dados secundários, assim, o banco de dados continuará a crescer.
	A extensão de nome de arquivo indicada para arquivos de dados secundários é .ndf.
Log de transações	Os arquivos de log de transações armazenam as informações de log usadas para recuperar o banco de dados. Deve haver, no mínimo, um arquivo de log para cada banco de dados.
Log de transações	A extensão de nome de arquivo indicada para arquivos de transação é .ldf.

Tabela 1 – Tipos de Arquivos que formam um banco de dados criado no Microsoft SQL Server.

Agora que já conhecemos os tipos de arquivos que podem compor um banco de dados, vamos conhecer um pouco sobre um outro importante recurso que esta diretamente relacionado a este post, os denominados Filegroups.

Filegroups

Quando objetos são criados no banco de dados sem especificar a qual grupo de arquivos eles pertencem, os objetos são atribuídos ao grupo de arquivos padrão. A qualquer hora, um grupo de arquivos é designado como o grupo de arquivos padrão.

Os arquivos no grupo de arquivos padrão devem ser grandes o suficientes para armazenar qualquer objeto novo alocado a outros grupos de arquivo.

O grupo de arquivos PRIMÁRIO é o grupo de arquivos padrão, a menos que seja alterado usando a instrução ALTER DATABASE. A alocação para os objetos de sistema e de tabelas permanece no grupo de arquivos PRIMÁRIO, e não no novo grupo de arquivos padrão. O SQL Server mapeia um banco de dados de um conjunto de arquivos do sistema operacional.

As informações de log e dados nunca ficam misturadas no mesmo arquivo, e os arquivos individuais são usados apenas por um banco de dados, os grupos de arquivos são conhecidos como coleções de arquivos e são usados para simplificar o posicionamento de dados e em tarefas administrativas, como operações de backup e restauração.

Crescimento de Arquivos de Dados

Ao criar um banco de dados, estamos estabelecendo o uso de uma ou mais áreas em disco rígido para alocar nossos arquivos de dados. Dentre as diversas opções e propriedades que podemos configurar no momento da criação de um novo banco de dados ou em sua alteração, nos deparamos com a propriedade FileGrowth, sendo esta responsável em estabelecer a forma ou método de crescimento que toda estrutura de arquivos de dados que formam nossos bancos deverá aplicar, escolhendo a método de rodízio (Round-Robin) na qual os arquivos vão sendo preenchidos de forma aleatória de acordo com a necessidade ou através do método Preenchimento Proporcional (Proportional Fill).

Chegou a hora de colocar a mão nos teclados, como de costume teremos um ambiente de testes a ser criado, o que será utilizado como cenário de estudos.

Criando o Ambiente

Em meu ambiente de estudos estou utilizando o Microsoft SQL Server 2017 Enterprise Edition – Cumulative Update 9 e Sistema Operacional Windows 10, fique a vontade para utilizar o melhor cenário possível dentro das suas necessidades, a partir da versão 2016 do Microsoft SQL Server.

Para realizar nossa simples prática, começaremos pela execução do Bloco de Código 1, responsável por criar a seguinte estrutura:

Databases: TesteDatabaseAUTOGROWSINGLEFILE; e TesteDatabaseAUTOGROWALLFILES;
Tables: TabelaGrowSingleFile; e TabelaGrowAllFile.

Importante: Destaco que os caminhos informados para criação dos referidos bancos e seus arquivos, estão apresentados de acordo com a configuração do meu ambiente, fique a vontade para alterar de acordo com suas necessidades e configurações.

— Bloco de Código 1 —
— Criando os respectivos bancos de dados —

— Criando o Banco de Dados TesteDatabaseAUTOGROWSINGLEFILE —
Create Database TesteDatabaseAUTOGROWSINGLEFILE
On Primary
(Name= ‘TesteDatabaseAUTOGROWSINGLEFILE_Data’,
FileName= ‘S:\MSSQL-2017\Data\TesteDatabaseAUTOGROWSINGLEFILE_Data.mdf’,
Size=10 MB,
MaxSize=4096 MB,
FileGrowth=100 MB),
(Name= ‘TesteDatabaseAUTOGROWSINGLEFILE_Data1’,
FileName= ‘S:\MSSQL-2017\Data\TesteDatabaseAUTOGROWSINGLEFILE_Data1.ndf’,
Size=10 MB,
MaxSize=4096 MB,
FileGrowth=100 MB),
(Name= ‘TesteDatabaseAUTOGROWSINGLEFILE_Data2’,
FileName= ‘S:\MSSQL-2017\Data\TesteDatabaseAUTOGROWSINGLEFILE_Data2.ndf’,
Size=10 MB,
MaxSize=4096 MB,
FileGrowth=100 MB)
Log On
(Name= ‘TesteDatabaseAUTOGROWSINGLEFILE_Log’,
FileName= ‘S:\MSSQL-2017\Log\TesteDatabaseAUTOGROWSINGLEFILE_Log.Ldf’,
Size=20 MB,
MaxSize=8192 MB,
FileGrowth=200 MB)
Go

— Criando o Banco de Dados TesteDatabaseAUTOGROWALLFILES —
Create Database TesteDatabaseAUTOGROWALLFILES
On Primary
(Name= ‘TesteDatabaseAUTOGROWALLFILES_Data’,
FileName= ‘S:\MSSQL-2017\Data\TesteDatabaseAUTOGROWALLFILES_Data.mdf’,
Size=10 MB,
MaxSize=4096 MB,
FileGrowth=100 MB),
(Name= ‘TesteDatabaseAUTOGROWALLFILES_Data1’,
FileName= ‘S:\MSSQL-2017\Data\TesteDatabaseAUTOGROWALLFILES_Data1.ndf’,
Size=10 MB,
MaxSize=4096 MB,
FileGrowth=100 MB),
(Name= ‘TesteDatabaseAUTOGROWALLFILES_Data2’,
FileName= ‘S:\MSSQL-2017\Data\TesteDatabaseAUTOGROWALLFILES_Data2.ndf’,
Size=10 MB,
MaxSize=4096 MB,
FileGrowth=100 MB)
Log On
(Name= ‘TesteDatabaseAUTOGROWALLFILES_Log’,
FileName= ‘S:\MSSQL-2017\Log\TesteDatabaseAUTOGROWALLFILE_Log.Ldf’,
Size=20 MB,
MaxSize=8192 MB,
FileGrowth=200 MB)
Go

Nota: Vale ressaltar que ambos os bancos de dados estão estruturados com a mesma quantidade de arquivos de dados e log, respectivamente 3(três) arquivos de dados e somente um único arquivo de log.

Muito bem, neste momento nossos bancos de dados estão criados e ambos contendo a mesma configuração para as propriedades: Size, MaxSize e FileGrowth, como também, configurados para que o crescimento ocorra de forma aleatória para os arquivos de dados.

Vamos validar nossa estrutura através do Bloco de Código 2 apresentado abaixo, o qual vai nos permitir identificar justamente as configurações que aplicamos no momento da criação dos referidos bancos de dados:

— Bloco de Código 2 —
Select DB_NAME() AS [DatabaseName], Name, file_id, physical_name,
(size * 8.0/1024) as Size,
((size * 8.0/1024) – (FILEPROPERTY(name, ‘SpaceUsed’) * 8.0/1024)) As FreeSpace
From sys.database_files
Go

Após executar o Bloco de Código 2, o Management Studio deverá apresentar o resultado similar a ilustrado na Figura 1 para ambos os bancos de dados:
Figura 1 – Informações sobre os bancos de dados seus arquivos, tamanhos e espaço livre ocupado.

Nosso próximo passo será forçar o crescimento dos nossos arquivos para o Banco de Dados TesteDatabaseAUTOGROWSINGLEFILE, observando o comportamento que o SQL Server vai utilizar, ressalto que estaremos fazendo a criação da tabela que utilizaremos como base para inserir os dados, para tal cenário vamos utilizar o Bloco de Código 3 apresentado a seguir:

— Bloco de Código 3 —
Use TesteDatabaseAUTOGROWSINGLEFILE
Go

— Criando a Tabela TabelaGrowSingleFile —
Create Table TabelaGrowSingleFile
(Codigo Int Identity(1,1) Not Null Primary Key,
Texto VarChar(100) Default ‘Grow Single File’,
Quantidade SmallInt Default 2018,
ValoresNumericos Numeric(8,2) Default ‘2018.12’,
DataAtual Date Default GetDate()+Rand()*30)
Go

— Inserindo a massa de dados —
Insert Into TabelaGrowSingleFile Default Values
Go 500000

A partir do momento que o Bloco de Código 3 é executado, o Microsoft SQL Server em conjunto com o SQL OS, Database Engine e Storage Engine, começam a fazer uso dos arquivos de dados, distribuindo em tempo real as páginas de dados conforme o método ou técnica escolhida para preenchimento dos arquivos de dados.

Para confirmarmos a alocação e distribuição destas páginas, vamos executar o Bloco de Código 4 apresentado abaixo em uma Nova Query e observar o resultado apresentado:

— Bloco de Código 4 —
Use TesteDatabaseAUTOGROWSINGLEFILE
Go

DBCC ShowFileStats — Comando que vai apresentar a distribuição de páginas de dados entre os arquivos
Go

Observação: Execute o comando DBCC ShowFileStats durante a execução do Bloco de Código 3, para que você possa obter um resultado similar ao apresentado na Figura 2 abaixo:

Figura 2 – Resultado apresentado pelo comando DBCC ShowFileStats.

No decorrer do processamento do Bloco de Código 3, podemos novamente o Bloco de Código 2 para identificar que neste momento o Microsoft SQL Server esta fazendo uso método de preenchimento dos arquivos de dados Round-Robin, no qual ele identifica qual seria o melhor arquivo para alocar a página, para confirmar este cenário a Figura 3 ilustra o resultado obtido de mais uma execução do Bloco de Código 2:
Figura 3 – Alocação dos arquivos de dados.

Observe a mudança de valores nas colunas Size e FreeSpace, como também, suas diferenças de valores em relação ao File_ID=1 para com os File_ID = 2 e 3, são justamente estas diferenças que nos orientam a entender que o Round-Robin esta sendo utilizado.

Estamos indo bem, falta um pouco para chegarmos ao final….

Nosso próximo passo se destina a alterar a forma de preenchimento e utilização dos arquivos de dados definidos para o Banco de Dados TesteDatabaseAUTOGROWALLFILES, e em seguida forçar o uso de cada arquivos e a distribuição de páginas. Vamos então executar o Bloco de Código 5 e sua sequência de passos:

— Bloco de Código 5 —
— Alterando a definição de crescimento dos arquivos de Dados para o FileGroup Primary —
Use Master
Go

Alter Database TesteDatabaseAUTOGROWALLFILES
Modify FileGroup [Primary] AUTOGROW_ALL_FILES — Definindo o crescimento proporcional para todos os arquivos de dados —
Go

— Acessando o Banco de Dados —
Use TesteDatabaseAUTOGROWALLFILES
Go

— Criando a Tabela TabelaGrowSingleFile —
Create Table TabelaGrowAllFile
(Codigo Int Identity(1,1) Not Null Primary Key,
Texto VarChar(100) Default ‘Grow All File’,
Quantidade SmallInt Default 2018,
ValoresNumericos Numeric(8,2) Default ‘2018.12’,
DataAtual Date Default GetDate()+Rand()*30)
Go

— Inserindo a massa de dados —
Insert Into TabelaGrowAllFile Default Values
Go 500000

Neste momento temos o banco de dados TesteDatabaseAUTOGROWALLFILES sendo utilizando, no qual sua estrutura de arquivos esta definida para ser utilizada de forma conjunto e proporcional, ou seja, conforme a necessidade de crescimento dos arquivos de dados, ao invés de um único arquivo ser invocado e ter seu valor de crescimento definido, todos os arquivos serão envolvidos e afetados, conforme a Figura 4 a seguir ilustra:
Figura 4 – Preenchimento de todos os arquivos de dados sendo realizado com base no uso da opção Grow_All_Files.

Estamos quase lá, para finalizar nosso estudos, vamos executar o Bloco de Código 6, o qual tem a finalidade de repetir a inserção das 500.000 mil linhas de registros em cada banco de dados e posteriormente forçar um crescimento para os arquivos de dados:

— Bloco de Código 6 —
— Acessando o Banco de Dados —
Use TesteDatabaseAUTOGROWSINGLEFILE
Go

— Inserindo a massa de dados —
Insert Into TabelaGrowSingleFile Default Values
Go 500000

— Acessando o Banco de Dados —
Use TesteDatabaseAUTOGROWALLFILES
Go

— Inserindo a massa de dados —
Insert Into TabelaGrowAllFile Default Values
Go 500000

Note que para o Banco de Dados TesteDatabaseAUTOGROWSINGLEFILE o crescimento vai ocorrer no primeiro arquivo de dados e para no Banco de Dados TesteDatabaseAUTOGROWALLFILES este crescimento será aplicado a todos os arquivos, conforme apresenta a Figura 5 abaixo:
Figura 5 – Espaço ocupado pelos arquivos de dados após o crescimento ser ocorrido.

Para finalizar, compartilho a Figura 6 que apresenta a utilização dos arquivos de dados por parte do Storage Engine de acordo com o método de alocação e preenchimento dos arquivos de dados, respeitando a configuração dos bancos de dados aqui utilizados:

Figura 6 – Comparativo entre AutoGrowSingleFile versus AutoGrowAllFiles.

Importante: Observe que todos os arquivos de dados definidos para uso no banco de Dados TesteDatabaseAutoGrowAllFiles apresentam os mesmos valores para colunas Size e FreeSpace, cenário totalmente diferente para o banco de dados TesteDatabaseAutoGrowSingleFile, que ilustra a utilização de forma diferente dos arquivos de dados, no qual os arquivos File_ID=2 e 3 estão neste momento sem espaço livre, o que indica que o crescimento foi aplicado ao primeiro arquivo de dados.

Espero que você tenha gostado, eu posso dizer que sim, mas sua opinião é muito importante.

Referências

https://pedrogalvaojunior.wordpress.com/2017/01/23/12-para-que-serve/

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-file-and-filegroup-options?view=sql-server-2017

https://blogs.msdn.microsoft.com/psssql/2016/03/15/sql-2016-it-just-runs-faster-t1117-and-t1118-changes-for-tempdb-and-user-databases/

https://www.brentozar.com/archive/2014/06/trace-flags-1117-1118-tempdb-configuration/

https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-2017

https://www.sqlshack.com/understanding-sql-server-proportional-fill-algorithm/

http://www.sqlservercentral.com/scripts/Maintenance+and+Management/30218/

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/2018/08/22/22-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2018/05/28/21-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2018/04/12/20-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2018/01/02/19-para-que-serve/

Conclusão

Conhecer a cada nova versão as mudanças e novidades aplicadas ao Microsoft SQL Server não é uma tarefa fácil, mas deixar de fazer uso delas pode em algum momento parecer falta de interesse ou até mesmo desconhecimento do potencial existente no produto.

Neste post, podemos conhecer esta nova opção Auto_Grow_All_Files, que nos permite aplicar uma nova maneira de orientar o SQL Server no uso, alocação e principalmente crescimento de nossos arquivos de dados, o que pode ou não impactar de forma direta na performance, contenção ou distribuição de recursos relacionados a disco rígido.

Em momento algum, o cenário aqui utilizado, muito menos a análise feita, teve o objetivo de comprovar qual forma de alocação e uso dos arquivos de dados é melhor, isso deve ser analisado para cada necessidade e ambiente.

Espero que o conteúdo aqui apresentado possa lhe ajudar a conhecer um pouco sobre como os arquivos de dados são importantes e úteis para nossos bancos, além disso, a importância de se utilizar mais de um arquivo de dados ou filegroups.

Vai SQL Server, Vai SQL Server….

Agradecimentos

Mais uma vez obrigado por sua ilustre visita, sinto-me honrado com sua presença, espero que este conteúdo possa lhe ajudar e ser útil em suas atividades profissionais e acadêmicas.

Um forte abraço, até o próximo post da sessão Para que serve a ser publicado no mês fevereiro de 2019.

Um grande abraço e ótima semana.

Valeu.

Dica do Mês – Comando Restore Database Page – Restaurando páginas de dados de uma tabela no Microsoft SQL Server

Olá boa tarde, que surpresa te encontrar mais uma vez no meu blog, caso esta seja a sua primeira vez, fico mais feliz ainda, seja muito bem vindo.

Este é mais um post da sessão Dica do Mês, sessão dedicada a compartilhar bimestralmente dicas, novidades, curiosidades e demais assuntos, conteúdos e informações relacionadas ao Microsoft SQL Server, Banco de Dados e Tecnologias de Banco de Dados.

No post de hoje, quero compartilhar com vocês uma das funcionalidades adicionadas ao Microsoft SQL Server a partir da versão 2016 e que recentemente acabei conhecendo com um pouco mais.

Funcionalidade que trouxe um grande salto de qualidade ao produto, ainda mais se levarmos em consideração sua praticidade e simplicidade de uso.

Como você já pode notar no título deste post, estou me referindo a nova capacidade de recuperação de dados através do comando Restore Database em conjunto com a opção Page.

Pois bem, sem mais delongas, vamos em frente, vou tentar mitigar a sua curiosidade e ao mesmo também satisfazer os meus objetivos. Sendo assim, seja bem vindo ao post – Dica do Mês – Comando Restore Database Page – Restaurando páginas de dados de uma tabela no Microsoft SQL Server.

Introdução

Umas das tarefas mais ingratas para qualquer profissional de tecnologia, principalmente aqueles que estão diretamente relacionadas as tarefas de administração, retenção e armazenamento de dados se relaciona ao momento em que nossos ambientes começam apresentam comportamentos fora do comum ou até mesmo instabilidades.

Quem nunca se deparou com este tipo de situação! Eu por diversas vezes passei por isso nesta minha longa estrada da vida na área de tecnologia da informação.

Mas não somente isso é importante, algo muito maior e mais preocupante podemos enfrentar, o tão temido momento de restauração de um banco de dados o chamado Restore Database, imagina então você ter que recuperar uma parte específica de uma tabela ou índice que de uma hora para outra começou a apresentar falhas e simplesmente tornou-se inacessível.

Foi justamente com base neste tipo de cenário, que o time de engenheiros da Microsoft dedicados no desenvolvimento do Microsoft SQL Server adicionaram no comando Restore Database e também no interface gráfica do Management Studio a capacidade de verificar a integridade física e lógica de uma ou mais páginas de dados, como também, a possibilidade de realizar sua restauração.

Até aqui tranquilo, nada de novidade, vamos então seguir em frente e conhecer a opção Page existente no comando Restore Database.

Tabelas e Índices

As tabelas são o coração do Microsoft SQL Server e do modelo relacional em geral, pois é onde o dado é armazenado. Cada instância de um dado na tabela representa uma entidade simples ou registro (formalmente chamado de tupla). A maioria das tabelas serão relacionadas entre si. Por exemplo: A tabela Clientes possuí um identificador único CodigoCliente que é usado como chave estrangeira no relacionamento com a tabela Pedido.

As tabelas devem ser modeladas de acordo com a teoria de banco de dados relacionais, respeitando as formas normais.

Ao criarmos nossas tabelas e índices, estamos criando internamente estrutura responsáveis em armazenar em tempo real nossos dados em áreas físicas das unidades de armazenamento de dados.

Não vou me aprofundar nos conceitos relacionados a páginas de dados, pois este não é objetivo deste post, mas sim de destacar como a Restore Database Page é importante, sua finalidade e forma de uso.

Restore Database Page

Seu objetivo é possibilitar a restauração de uma página de dados danificada sem restaurar todo o banco de dados, muito menos provocar qualquer tipo de impacto ou instabilidade no acesso aos dados após sua resturaçao.

Normalmente, as páginas que são candidatos para restauração foram marcadas como “suspeita” devido a um erro que é encontrado ao acessar a página.

As páginas suspeitas são identificadas na tabela suspect_pages no banco de dados msdb.

Avançando mais um pouco, neste momento, já temos uma noção dos elementos básicos: Tabelas e Índices, sabemos também da estrutura que as compõem chamada de páginas de dados e de que forma estas estruturas são controladas e gerenciadas, agora vamos construir nosso cenário de testes que justamente vai nos permitir ter a visão completa de toda esta estrutura e como poderemos realizar os procedimentos de sobrescrever uma página de dados e posteriormente realizar sua restauração.

Nosso ambiente

Como de costume vamos utilizar um ambiente isolado dos demais bancos de dados que você possa conter, desta maneira nosso cenário será constituído dos seguintes elementos:

Banco de Dados: RestoreDatabasePage;
Database Recovery Model: Full;
Database Page_Verify: CheckSum;
Tabela: TabelaCorrompida; e
Índice Clusterizado: Ind_TabelaCorrompida_Codigo.

Criando o ambiente

Através do Bloco de Código 1 apresentado abaixo, vamos realizar a criação dos respectivos elementos destacados anteriormente:

— Bloco de Código 1 – Criação do Ambiente —

— Criando o Banco de Dados —
Create Database RestoreDatabasePage
Go

— Acessando —
Use RestoreDatabasePage
Go

— Criando a TabelaCorrompida —
Create Table TabelaCorrompida
(Codigo Int Identity(0,2),
ValorGUID UniqueIdentifier,
ValorRandomico BigInt,
ColunaGrande Char(100) Default ‘TC’)
Go

— Criando o Índice Clusterizado na TabelaCorrompida —
Create Clustered Index Ind_TabelaCorrompida_Codigo On TabelaCorrompida(Codigo)
Go

Como nossa estrutura base pronta, chegou a hora de popular nossa tabela realizando o processo de inserção de uma aleatória massa de dados em nossa tabela, para tal, vamos utilizar o Bloco de Código 2 apresentado a seguir:

— Bloco de Código 2 – Populando a TabelaCorrompida —
— Desabilitando a contagem de linhas processadas —
Set NoCount On
Go

— Declarando a variável de controle @Contador —
Declare @Contador Int = 0

— Abrindo bloco de transação Trans1 —
Begin Transaction Trans1

While @Contador <= 132768
Begin

Insert Into TabelaCorrompida(ValorGUID, ValorRandomico)
Values (NewId(), ABS(CHECKSUM(Rand()* 200000000)))

Set @Contador += 2
End

— Confirmando e encerrando o bloco de transação Trans1 —
Commit Transaction Trans1
Go

Observação: Note que estou fazendo uso dos comandos Begin Transaction e Commit Transaction, como forma de controle e adoção de transações explícita, sendo assim, estou informando o Microsoft SQL Server quando a transação começa e deverá ser obrigatoriamente encerrada, além disso, estou evitando e isolando o processo de inserção de dados de qualquer possibilidade de bloqueio.

Neste momento, nossa tabela já esta populada “abastecida de dados”, com um total fixo de 66385 linhas de dados, denominados tecnicamente como registros lógicos.

Vamos caminhar mais um pouco, antes de realizarmos o processo de consultar a estrutura de nossas páginas de dados e posteriormente forçar sua reescrita, vamos realizar um procedimento de backup database de nosso banco de dados, procedimento importante para garantir e possibilitar a restauração das páginas, para tal utilizaremos o Bloco de Código 3 apresentado abaixo:

— Bloco de Código 3 – Backup Database —
Backup Database RestoreDatabasePage
To Disk = ‘S:\MSSQL-2017\Backup\RestoreDatabasePage-Backup-Full.bak’ — Troque para sua unidade de disco
With Compression,
NoFormat,
Init,
Stats=10
Go

Pronto, nosso backup já esta realizado, estamos prontos e preparados para começar a brincadeira, nosso próximo passo será obter a relação das páginas de dados que forma nossa TabelaCorrompida, para isso, vamos utilizar a não documentada function sys.fn_PhysLocFormatter, solicitando ao Microsoft SQL Server a apresentação das 100 primeiras páginas de dados da nossa tabela, conforme apresenta o Bloco de Código 4:

— Bloco de Código 4 – Obtenção a relação das páginas de dados da TabelaCorrompida —
Select TOP 100 sys.fn_PhysLocFormatter(%%physloc%%) PageId, *
FROM TabelaCorrompida
Go

A Figura 1 apresentada a seguir ilustra o resultado obtido após a execução do Bloco de Código 4:
Figura 1 – Relação das páginas de dados e seus respectivos dados.

Legal, esta ficando interessante esta brincadeira, por enquanto sem nenhum perigo!

Para que possamos realizar o processo de reescrita de uma ou mais páginas de dados, vou selecionar duas páginas (256 e 258) e seus valores para utilizar em nosso cenário, conforme a Tabela 1 apresentada abaixo:

PageID	Codigo	ValorGuid
(1:256:10)	20	6460AAB3-AD12-47BB-B179-8C1930B1A287
(1:258:1)	120	AEF17F9D-D838-4FEF-B723-CA3658D03319

Tabela 1 – Relação de páginas de dados e valores que iremos utilizar.

Já sabemos com quais estruturas vamos fazer o processo de reescrever suas estruturas, devemos então preparar nosso banco de dados para que nos possibilite a realização desta tarefa, desta forma, utilizaremos o Bloco de Código 5, apresentado abaixo:

— Bloco de Código 5 — Alterando a forma de acesso do banco de dados RestoreDatabasePage —

— Preparando-se para corromper a estrutura de páginas —
Use Master
Go

— Limitando a conexão do Banco de Dados para Single_User —
Alter Database RestoreDatabasePage
Set Single_User
With Rollback Immediate
Go

Ótimo, acabamos de limitar o acesso físico e lógico do nossa banco de dados para Single_User, desta forma, nenhuma outra conexão ou solicitação de acesso será permitida ao mesmo, neste momento temos acesso único e exclusivo.

O passo seguinte, consiste na consulta da estrutura da página de dados 256 e posteriormente na procura do valor 6460AAB3-AD12-47BB-B179-8C1930B1A287 armazenado no Slot 10, vamos então executar o Bloco de Código 6, apresentado abaixo:

— Bloco de Código 6 — Obtendo as informações sobre a página de dados 256 e pesquisando valor 6460AAB3-AD12-47BB-B179-8C1930B1A287 —

Para que possamos obter as informações de retorno apresentadas pelos comandos DBCC – Database Command Console, precisamos fazer uso do comando Dbcc TraceOn ativando a Trace Flag 3604 que orienta e informa ao Microsoft SQL Server que o mesmo deverá apresentar logo após a execução dos comandos DBCCs seus respectivos resultados.

— Obtendo informações sobre os slots de alocação de dados —
Dbcc TraceOn (3604)
Go

Seguindo nossa caminhada, vamos utilizar o comando DBCC Page, comando que vai nos possibilitar obter o conjunto de informações internas que formam a estrutura da nossa tabela, neste caso, vamos buscar toda estrutura da página de dados de número 256.

— Procurando valor 6460AAB3-AD12-47BB-B179-8C1930B1A287 e guardar slots —
Dbcc Page (‘RestoreDatabasePage’, 1, 256, 3);
Go

A Figura 2 apresentada abaixo, ilustra uma parte da estrutura interna da página de dados 256, apresentando sua área de buffer e page hearder:
Figura 2 – Estrutura interna da página de dados 256.

Pois bem, precisamos agora procurar o valor 6460AAB3-AD12-47BB-B179-8C1930B1A287 dentro da área de dados desta mesma página, afim de encontramos o refiro Slot 10 que armazena este dado.

Para que possamos encontrar o referido valor clique na guia de mensagens do Management Studio e preciso posteriormente a tecla de atalho CTRL + F, informando o valor na campo de busca.

A Figura 3 ilustra o 6460AAB3-AD12-47BB-B179-8C1930B1A287 localizado na estrutura interna da página de dados 256:
Figura 3 – Valor 6460AAB3-AD12-47BB-B179-8C1930B1A287 localizado.

O mesmo procedimento deverá ser feito para página 258 referente ao código 120 e ValorGuid AEF17F9D-D838-4FEF-B723-CA3658D03319.

Além disso, recomendo que você anote as informações referente OffSet e Length de dados valor pesquisado em sua referida página, pois ambos serão utilizado no procedimento de reescrita, mas como eu sou bonzinho, a Tabela 2 apresentada abaixo destaca estes valores:

Collumn	Offset	Length	ValorGuid
2	0x8	16	6460aab3-ad12-47bb-b179-8c1930b1a287
2	0x8	16	AEF17F9D-D838-4FEF-B723-CA3658D03319

Tabela 2 – Informações sobre Offset e Length dos respectivos ValorGuid.

Agora chegou a tão esperada hora de suar o barraco (kkkk), não é bem assim, mas chegou o momento de reescrevermos a estrutura das páginas de dados: 256 e 258, através do comando DBCC WritePage declarado no Bloco de Código 7 apresentado na abaixo:

— Reescrevendo a página de dados 256 no OffSet 0x8 —
Dbcc WritePage (‘RestoreDatabasePage’, 1, 256, 8, 16, 0x00000000000000000000000000000001, 1)
Go

— Reescrevendo a página de dados 256 no OffSet 0x8 —
Dbcc WritePage (‘RestoreDatabasePage’, 1, 258, 8, 16, 0x00000000000000000000000000000001, 1)
Go

Se você conseguiu realizar o processamento destes dois comandos DBCC WritePage, isso significa que neste momento as páginas de dados 256 e 258 estão apresentando inconsistência em suas estruturas, algo que podemos comprovar através da execução do Bloco de Código 8, apresentado abaixo:

— Bloco de Código 8 – Verificando a Integridade da TabelaCorrompida —
— Alterando o acesso ao Banco de Dados para Multi_User —
Alter Database RestoreDatabasePage
Set Multi_User
Go

— Realizar testes de integridade consultando dados na TabelaCorrompida —
Use RestoreDatabasePage
Go

Select Count(Codigo) From TabelaCorrompida
Go

Ao realizarmos o comando Select Count() para tentarmos contar a quantidade de linhas de registros existentes na TabelaCorrompida, o Management Studio nos retorna a seguinte mensagem de erro:
Msg 824, Level 24, State 2, Line 162
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x4bd220eb; actual: 0xcb53a034). It occurred during a read of page (1:256) in database ID 11 at offset 0x00000000200000 in file ‘S:\MSSQL-2017\Data\RestoreDatabasePage.mdf’. Additional messages in the SQL Server error log or operating system error log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Vamos avançar mais ainda, estamos nos aproximando do final deste post, agora que nosso ambiente esta danificado podemos fazer uso da opção Page existente no comando Restore Database que vai nos permitir restaurar a estrutura física e lógica da nossa tabela, sendo assim, vamos utilizar o Bloco de Código 9, apresentado abaixo:

— Bloco de Código 9 – Iniciando o processo de restauração e recuperação das páginas de dados —
— Realizando a Restauração das Páginas de Dados —
Use Master
Go

— Restore Database Page —
Restore Database RestoreDatabasePage
PAGE=’1:256, 1:258′ — Informando os números de páginas
From Disk = N’S:\MSSQL-2017\Backup\RestoreDatabasePage-Backup-Full.bak’
With File = 1, — Especificando o arquivo de dados
NoRecovery, — Não liberando o banco para acesso
Stats = 10
Go

Perfeito, realizamos o procedimento se restauração das páginas de dados 256 e 258 sem restaurar toda estrutura do nosso banco, agora podemos realizar um novo teste e verificar se a a estrutura da nossa TabelaCorrompida encontra-se funcional, conforme apresenta o Bloco de Código 10 a seguir:

— Bloco de Código 10 — Realizando um novo teste de integridade consultando dados na TabelaCorrompida —
Use RestoreDatabasePage
Go

Select Count(Codigo) From TabelaCorrompida
Where Codigo Not Between 20 And 120
Go

E para nossa surpresa o Management Studio retornou mais uma vez outra mensagem de erro:
Msg 829, Level 21, State 1, Line 186
Database ID 11, Page (1:256) is marked RestorePending, which may indicate disk corruption. To recover from this state, perform a restore.

Esta mensagem nos informa que não podemos realizar o acesso a TabelaCorrompida pois neste momento a página 256 esta marcado como pendente de restauração, este é um comportamento normal apresentado pelo SQL Server, pois o mesmo depende da realização de um backup de log e posteriormente da restauração (conhecido como Tail Log) para realizar a limpeza e desmarcar esta página de dados como pendente.

Para tal procedimento, utilizaremos o Bloco de Código 11, apresentado abaixo:

— Bloco de Código 11 — Realizando Backup Log e Restore Log (Tail Log) —
— Backupear o Log e Restaura para Liberar páginas marcadas como pendentes —
Use Master
Go

Backup Log RestoreDatabasePage
To Disk = ‘S:\MSSQL-2017\Backup\RestoreDatabasePage-Backup-Log.bak’
With NoFormat,
Init,
Name = N’RestoreDatabasePage-Backup-Log’,
Stats=10
Go

— Restaurar Log —
Restore Log RestoreDatabasePage
From Disk = ‘S:\MSSQL-2017\Backup\RestoreDatabasePage-Backup-Log.bak’
With Recovery,
Replace,
Stats = 10
Go

Acredito que o procedimento de Backup Log e Restore Log tenha ocorrido normalmente, basta agora realizar o último teste de acesso a TabelaCorrompida para poder consultar todos os dados armazenados na mesma, conforme apresenta o Bloco de Código 12:

— Bloco de Código 12 — Realizar último teste de integridade consultando dados na TabelaCorrompida —
Use RestoreDatabasePage
Go

A Figura 4 apresentada abaixo ilustra a massa de dados existente na TabelaCorrompida, após o procedimento de restauração e recuperação das páginas de dados: 256 e 258.
Figura 4 – Relação de dados existentes na TabelaCorrompida, recuperados após o procedimento de Restore Database Page.

— Obtendo a quantidade de registros armazenados na TabelaCorrompida —
Select Parcial=(Select Count(Codigo) From TabelaCorrompida Where Codigo Not In (20,120)),
Geral=(Select Count(Codigo) From TabelaCorrompida)
Go

Show de bola, muito bom, conseguimos, seguimos todos os passos desde a criação do nosso ambiente, inserção de dados, identificação das páginas e suas estrutura, reescrita na estrutura das páginas e o tão esperado procedimento de restauração.

Com isso chegamos ao final de mais um post da sessão Dica do Mês, antes de encerrarmos, gostaria de contar com a sua participação neste post, respondendo a enquete abaixo:

Referências

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-pages-sql-server?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql

https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql

https://www.mssqltips.com/sqlservertip/1925/how-to-use-the-sql-server-sysfnphyslocformatter-undocumented-function/

https://blogs.msdn.microsoft.com/fcatae/2016/04/12/dbcc-page/

https://docs.microsoft.com/pt-br/sql/t-sql/database-console-commands/dbcc-transact-sql

http://www.sqlskills.com/BLOGS/PAUL/post/SQL-Server-2008-New-%28undocumented%29-physical-row-locator-function.aspx

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2006/12/13/more-undocumented-fun-dbcc-ind-dbcc-page-and-off-row-columns/

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql

Posts Anteriores

https://pedrogalvaojunior.wordpress.com/2018/07/26/dica-do-mes-ocultando-uma-instancia-em-execucao-do-microsoft-sql-server/

https://pedrogalvaojunior.wordpress.com/2018/04/25/dica-do-mes-sql-operations-studio-view-as-chart/

https://pedrogalvaojunior.wordpress.com/2018/03/14/dica-do-mes-microsoft-sql-server-2017-sql-graph-databases/

https://pedrogalvaojunior.wordpress.com/2018/01/24/dicadomes-sqlservertoolsuiteintroduction/

Conclusão

Como já destaquei em outros posts, a cada nova versão, atualização e correção a Microsoft transforma o SQL Server em um produto surpreende, ainda mais na sua capacidade e versatilidade de permitir aos profissionais de tecnologia, administradores de bancos de dados, programadores, entre outros, utilizar recursos nativo e também os não documentados oficialmente como um elemento capaz de se superar e sobreviver a inúmeras falhas ou situações de perdas de dados.

No post de hoje, mais uma vez este foi constatado, a possibilidade através do comando DBCC Page de se obter informações sobre as páginas de dados, o comando DBCC WritePage (muito cuidado com ele) sensacional na sua funcionalidade em permitir uma reescrita de dados na estrutura das páginas que formam uma tabela, e principalmente a não documentada function sys.fn_physLocFormatter que de forma simples, fácil e confiável nos apresenta a distribuição de páginas de dados que compõem nossas tabelas em conjunto com os respectivos slots que armazenam nosso dados.

Acredito que você tenha conseguido entender e observar como consultamos a estrutura de páginas, a forma que alteramos seu conteúdo forçando uma reescrita de dados e depois como conseguimos através do comando Restore Database Page recuperar estas áreas.

Este é o fantástico Microsoft SQL Server, produto tão fascinante que a cada dia eu não consigo deixar de querer estudar e conhecer mais ainda.

Agradecimentos

Agradeço a você por sua atenção e visita ao meu blog. Fique a vontade para enviar suas críticas, sugestões, observações e comentários.

Nos encontramos no próximo post da sessão Dica do Mês a ser publicado no mês de dezembro.

Um forte abraço, sucesso, até mais…

Microsoft SQL Server 2017 – Cumulative Update 10

A Microsoft disponibilizou para download nesta semana a Atualização Cumulativa 10 (Cumulative Update 10) para SQL Server 2017.

Através do artigo KB4342123 publicado no site de suporte da Microsoft, esta nova atualização cumulativa disponível do SQL Server 2017 apresenta todas as correções existentes desde o lançamento da atual versão do produto, além disso, possui correções para problemas encontrados após o lançamento das atualizações cumulativas anteriores existentes.

Caso você necessite realizar o download de alguma atualização cumulativa anterior, utilize os links listados abaixo:

A relação completa de bugs corrigidos por esta atualização inclui um que causa vazamento de memória no arquivo sqlwepxxx.dll, outro que proporciona a degradação de performance quando a função HASHBYTES é utilizada durante uma consulta e um que faz com que a sincronização de banco de dados demore mais do que o esperado.

Ao aplicar esta atualização o número do build será atualizado para (build 14.0.3037.1), caso deseje realizar o download, basta clicar na imagem abaixo:

Fontes e Direitos Autorais: Microsoft Support – https://support.microsoft.com/en-us/help/4342123/cumulative-update-10-for-sql-server-2017

#22 – Para que serve

Oi, bom dia, tudo bem?

Seja bem-vindo a mais um post da sessão Para que Serve, sendo este o de número 22, mais um dia de muito trabalho começando, repleto de atividades, compromissos, reuniões, enfim tudo aquilo que normalmente estamos acostumados a nos deparar em nossos convívio diário.

E eu (parece música), estou aqui, como faço normalmente, acordando bem cedinho, para poder aproveitar da melhor maneira possível meu precioso tempo livre, colocando em prática algo que adoro fazer, publicar um post novo em meu blog mantendo a tradição de querer renovar e compartilhar as experiências e aprendizados adquiridos em minhas atividades profissionais e acadêmicas.

No post de hoje, não estarei compartilhando um conteúdo que pode ser considerado novo, ao contrário, quero destacar uma das mais antigas propriedades existentes em bancos de dados criados no Microsoft SQL Server desde suas versões mais antigas, mais especificamente falando da versão 2000 (é muito importante entender o passado, aprender com ele, para podemos melhorar e ofertar um futuro melhor) em diante.

Estou se referindo a propriedade denominada Page_Verify, a qual tem um papel de extrema importância no comportamento de um banco de dados de usuários, algo que pode impactar na forma de armazenamento, leitura e escrita das páginas de dados que formam a estrutura física e lógica de nossas tabelas e por consequência de nossos bancos de dados.

No decorrer deste post serão destacadas as respectivas opções existentes para esta propriedade, como também, será realizado uma análise comparativa no que diz respeito ao tempo de processamento de dados tendo como parâmetro um simples bloco de código inserindo 100.000 (Cem mil linhas de registros lógicos) com dados aleatórios.

Destaco antecipadamente que esta análise comparativa em nenhum momento leva em consideração características de Hardware, versão de Sistema Operacional ou uso de uma aplicação específica. Na verdade o objetivo desta simples análise é elucidar o uso desta propriedade, suas opções e respectivos algoritmos de processamento podem influenciar diretamente nos processos de escrita e leitura de dados.

Sendo assim, chegou a hora de conhecer um pouco mais sobre o post de número 22 da sessão Para que serve. Mas uma vez, bem vindo ao #22 – Para que serve – Propriedades de Bancos de Dados – Page_Verify e suas opções.

Espero que você esta animado para conhecer um pouco mais sobre esta propriedade, caso já conheça, continue lendo este post, sempre podemos aprender algo novo….

Introdução

Criar uma nova tabela para alocar dados em qualquer banco de dados existente no Microsoft SQL Server, pode ser considerada uma tarefa ou procedimento de fácil compreensão, muito simples de ser realizada tanto por linha de comando como também por interface gráfica, mas por trás “dos panos” diversos procedimentos internos são realizados pelo Sistema Gerenciador de Banco de Dados e seus diversos recursos e componentes internos.

Imagine então a tarefa de criação de um novo banco de dados, não parece também ser algo muito, mas muito simples! Teoricamente sim, mas se começarmos a analisar, entender e de preferência estudar cada uma das diversas propriedades que formam e orientam o Mecanismo de Bancos de Dados na execução da instrução de criação de um novo banco, teremos a total certeza que isso não tão simples como pensamos.

Pensando justamente neste cenário, decidi então identificar uma propriedade que por muitas vezes me passou totalmente despercebida e nos últimos dias acabou sendo motivo de estudos, como já destacado anteriormente estou me referindo a propriedade Page_Verify, sendo esta um das mais antigas propriedades existentes em bancos de dados desde a versão 2000 ou nível de compatibilidade 80, inicialmente apresentava como conjunto de opções os valores: None e Torn_Page_Detection e a partir da versão 2005 do Microsoft SQL Server ganhou uma nova opção conhecida como CheckSum e por consequência um novo algoritmo.

Vamos então conhecer um pouco mais sobre esta propriedade.

Propriedade de Banco de Dados Page_Verify

Propriedade existente em bancos de dados criados no Microsoft SQL Server que possui basicamente a função de permitir ao Sistema Gerenciador de Banco de Dados identificar e informar as chamadas Páginas de Dados Incompletas (Incomplete Pages) ou Páginas de Dados Suspeitas (Suspect Pages), através de dados internos catalogados e coletados na tabela suspect_pages existente dentro do banco de dados de sistemas MSDB.

Outra característica muito importante apresentada pela Page_Verify, relaciona-se ao método de verificação que garante ao Microsoft SQL Server que as páginas de dados que compõem um determinada tabela podem ser consideradas integras e completas, esta capacidade esta totalmente relacionada ao uso de um algoritmo pertencente a esta propriedade de acordo com o tipo de verificação de página que deve ser aplicada.

Esta tão importante característica, deve ser levada em consideração, mesmo tendo diversas funcionalidades que nos permitem identificar se a estrutura física e lógica de um banco de dados pode ser considerado integra. Em inúmeros cenários um banco de dados pode aparentemente estar apresentando falhas na estrutura interna que define uma respectiva página de dados sem necessariamente existir ou que tenha ocorrida uma falha física nas unidades de disco que armazenam tal página de dados.

A identificação se uma página de dados pode ou não ser considerada incompleta, suspeita ou quebrada é feita através da identificação de bits armazenados diretamente no cabeçalho de dados existente na respectiva página, é justamente por isso que a propriedade Page_Verify utiliza alguns algoritmos de acordo com a opção escolhida nas configurações e propriedades de um banco de dados, como recurso para garantir em tempo real o menor número de ocorrências ou possíveis falhas que venham a ocasionar uma perda de dados.

Vamos agora conhecer as formas de verificação de páginas de dados e seus respectivos algoritmos.

Formas de Verificação: None, Torn_Page_Detection e CheckSum

Até a versão 2005 SP2 do Microsoft SQL Server tínhamos somente as formas de verificação de páginas de dados None e Torn_Page_Detection, mas este cenário muito e uma nova opção denominada CheckSum foi adicionada, sendo esta então valor padrão para propriedade Page_Verify para bancos de dados com nível de compatibilidade 90 ou superior.

None: Ao escolher esta opção, o Microsoft SQL Server recebe a orientação para que não seja realizado os procedimentos de verificação e correção das páginas lidas durante os atividades de armazenamento de dados.

Logicamente esta é uma opção que não deverá ser utilizada em ambientes que não podemos correr o menor risco possível de aceitar falhas ou perdas de dados, mesmo confiando nas tecnologias de Hardware, estratégias de armazenamento, contingência e retenção de dados, a partir do momento que esta opção for escolhida você estará assumindo o risco.

Torn_Page_Detection: Opção padrão existente na versão 2000 do Microsoft SQL Server. Ao escolher esta opção, estaremos definindo o uso do algoritmo que armazenará em tempo real no cabeçalho de cada página de dados bits que identificam a operação de gravação.

No momento em que esta mesma página é lida de um disco durante a operação de leitura, os bits do cabeçalho da página são comparados àqueles no corpo da página. Caso alguma discrepância venha a ser detectada, esta específica página de dados será em tempo real considerada quebrada (incompleta ou suspeita) e o problema é relatado para um usuário.

Vale destacar que este algoritmo no momento em que uma página de dados esta sendo verificada, realiza o procedimento de duplicação de pequenas porções de bits de dados contidos em seu cabeçalho, algo que não podemos necessariamente relatar como um consumo maior de espaço em disco, na verdade é esta tarefa de duplicação de dados bits de dados utilizada para garantir a consistência da página. Por outro lado, dependendo do tipo de falha que poderá ocorrer alguns erros acabam não sendo detectados, se justamente o bit quebrado é um daqueles que não foram duplicados no cabeçalho da página, o dano não é detectado.

CheckSum: Opção padrão desde o SQL Server 2005 SP3, a partir do momento que esta opção é selecionado, o Microsoft SQL Server estará fazendo uso do algoritmo responsável durante operação de gravação, realizar uma soma de verificação (CheckSum) que calcula a partir da estrutura completa de uma página de dados um valor a ser armazenado diretamente em seu cabeçalho.

No momento em que a respectiva página de dados é lida a partir de um disco, CheckSum é calculado novamente e comparado com o valor no cabeçalho da página. A idéia é semelhante ao TORN_PAGE_DETECTION, podemos destacar que ambos algoritmos calculam um valor de verificação para uma página e armazenam o resultado no cabeçalho da página, mas CheckSum usa a página inteira para cálculo, ao contrário do TORN_PAGE_DETECTION que utiliza apenas um pequeno número de bits existente na página de dados.

Se compararmos a opção TORN_PAGE_DETECTION com a opção CheckSum, podemos afirmar que o CheckSum apresenta uma forma de análise e verificação de possíveis falhas e erros nas atividades de Input/Output de dados, mais confiável e inteligente, estabelecendo uma cobertura mais precisa que garante uma assertividade maior na identificação de possíveis páginas de dados incompletas, suspeitas ou quebradas.

Agora que conhecemos a propriedade Page_Verify e suas opções, vamos então conhecer de forma prática e entender o comportamento de cada uma delas, realizando assim a análise destacada no início deste post.

Chegou a hora de colocar a mão nos teclados, como de costume teremos um ambiente de testes a ser criado, o que será utilizado como cenário de estudos.

Criando o Ambiente

Como já destacado anteriormente esta análise ou possível cenário de estudo não consiste em afirmar qual é a mais rápida ou melhor forma de verificação de páginas de dados, mas sim demonstrar o quanto uma determinada opção poderá impactar ou influenciar nos custos de processamento de dados nas atividades de leitura e escrita, nesta prática mais especificamente nas atividades de gravação de dados.

Para realizar nossa simples prática, começaremos pela execução do Bloco de Código 1, responsável por criar a seguinte estrutura:

Database: TesteDatabasePageVerify;
Tables: TabelaPageVerify; e PageVerifyTempoDecorrido.

— Bloco de Código 1 —
— Criando o Banco de Dados TesteDatabasePageVerify —
Create Database TesteDatabasePageVerify
Go

— Acessando o Banco de Dados TesteDatabasePageVerify —
Use TesteDatabasePageVerify
Go

— Criando a Tabela TabelaPageVerify —
Create Table TabelaPageVerify
(Codigo Int Identity(1,1) Not Null Primary Key,
Texto VarChar(10) Not Null,
Quantidade SmallInt Not Null,
ValoresNumericos Numeric(18, 2) Not Null,
DataAtual Date Not Null)
Go

— Criando a Tabela PageVerifyTempoDecorrido —
Create Table PageVerifyTempoDecorrido
(NumeroDaAnalise SmallInt Identity(1,1) Not Null,
TipoDaAnaliseRealizada Varchar(22) Not Null,
HoraInicio Time Not Null,
HoraFim Time Not Null,
HoraDiferenca As (DateDiff(Second, HoraInicio, HoraFim)))
Go

A tabela TabelaPageVerify será utilizada como repositório dos dados que estaremos aleatoriamente gerando como fonte de análise, já a tabela PageVerifyTempoDecorrido terá um papel importante que consiste basicamente em armazenar os dados relacionada a análise realizada, dentre elas o Tipo da Análise, Horário de Início e Fim e a diferença em segundo entre os respectivos horários.

Avançando mais um pouco, vamos agora realizar propriamente a análise para cada uma das opções de verificação de páginas existentes, serão realizadas um total de 10 análises para cada opção e ao fim os dados armazenados na Tabela PageVerifyTempoDecorrido será apresentados como uma fonte de resultando da nossa análise.

Para realizar esta análise utilizaremos o Bloco de Código 2 apresentado abaixo:

— Bloco de Código 2 —
— Desativando a Contagem de Linhas —
Set NoCount On
Go

— Declarando as variáveis de controle —
Declare @Counter TinyInt = 0,
@Text Char(130),
@Position TinyInt,
@RowCount Int = 100000,
@HoraInicio Time = GetDate(),
@HoraFim Time

Set @Text = ‘0123456789@ABCDEFGHIJKLMNOPQRSTUVWXYZ\_abcdefghijklmnopqrstuvwxyzŽŸ¡ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåæçèéêëìíîïðñòóôõöùúûüýÿ.;^’ –There are 130 characters in this text–

While @Counter <10 — Definindo a quantidade máxima de testes —
Begin

— Alterando a Propriedade Page_Verify para None —
Alter Database TesteDatabasePageVerify
Set Page_Verify None

— Inserindo a massa de dados na tabela TabelaPageVerify —
While (@RowCount >=1)
Begin

Set @Position=Rand()*130

Insert Into TabelaPageVerify (Texto, Quantidade, ValoresNumericos, DataAtual)
Values(Concat(SubString(@Text,@Position+2,2),SubString(@Text,@Position-4,4),SubString(@Text,@Position+2,4)),
Rand()*1000,
Rand()*100+5,
DATEADD(d, 1000*Rand() ,GetDate()))

Set @RowCount = @RowCount – 1
End

Set @HoraFim=GetDate()

Insert Into PageVerifyTempoDecorrido (TipoDaAnaliseRealizada , HoraInicio, HoraFim)
Values (‘None’, @HoraInicio, @HoraFim)

— Alterando a Propriedade Page_Verify para Torn_Page_Detection —
Alter Database TesteDatabasePageVerify
Set Page_Verify Torn_Page_Detection

— Inserindo a massa de dados na tabela TabelaPageVerify —
Set @RowCount = 100000
Set @HoraInicio = GetDate()

While (@RowCount >=1)
Begin

Set @Position=Rand()*130

Set @RowCount = @RowCount – 1
End

Set @HoraFim=GetDate()

Insert Into PageVerifyTempoDecorrido (TipoDaAnaliseRealizada , HoraInicio, HoraFim)
Values (‘Torn_Page_Detection’, @HoraInicio, @HoraFim)

— Alterando a Propriedade Page_Verify para CheckSum —
Alter Database TesteDatabasePageVerify
Set Page_Verify CheckSum

— Inserindo a massa de dados na tabela TabelaPageVerify —
Set @RowCount = 100000
Set @HoraInicio = GetDate()

While (@RowCount >=1)
Begin

Set @Position=Rand()*130

Set @RowCount = @RowCount – 1
End

Set @HoraFim=GetDate()

Insert Into PageVerifyTempoDecorrido (TipoDaAnaliseRealizada , HoraInicio, HoraFim)
Values (‘CheckSum’, @HoraInicio, @HoraFim)

Set @Counter = @Counter + 1
End
Go

Ótimo, após o processamento de nossa análise, gerou a hora de obter o resumo de tempo decorrido de processamento para cada opção, basta executar o Bloco de Código 3 divido em duas partes: Resumo Detalhado e Resumo Sumarizado, conforme declaro abaixo:

— Bloco de Código 3 —
— Parte 1 – Consultando o resumo detalhado —
Select NumeroDaAnalise,
TipoDaAnaliseRealizada,
HoraInicio,
HoraFim,
HoraDiferenca As ‘Segundos’
From PageVerifyTempoDecorrido
Go

A Tabela 1 apresentada a seguir, ilustra o detalhamento de dados coletados durante o processamento do Bloco de Código 2 realizado anteriormente:

Número da Análise	Tipo da Análise	Hora Início	Hora Fim	Segundos
1	None	10:45:59.1966667	10:46:51.0566667	52
2	Torn_Page_Detection	10:46:51.0566667	10:47:31.9633333	40
3	CheckSum	10:47:31.9800000	10:48:09.3566667	38
4	None	10:47:31.9800000	10:48:09.3566667	38
5	Torn_Page_Detection	10:48:09.3700000	10:48:38.6800000	29
6	CheckSum	10:48:38.6966667	10:49:13.6800000	35
7	None	10:48:38.6966667	10:49:13.6933333	35
8	Torn_Page_Detection	10:49:13.7133333	10:49:47.5100000	34
9	CheckSum	10:49:47.5100000	10:50:17.3433333	30
10	None	10:49:47.5100000	10:50:17.3600000	30
11	Torn_Page_Detection	10:50:17.3766667	10:50:49.7066667	32
12	CheckSum	10:50:49.7233333	10:51:23.3566667	34
13	None	10:50:49.7233333	10:51:23.3733333	34
14	Torn_Page_Detection	10:51:23.3733333	10:51:54.4200000	31
15	CheckSum	10:51:54.4200000	10:52:29.0466667	35
16	None	10:51:54.4200000	10:52:29.0500000	35
17	Torn_Page_Detection	10:52:29.0666667	10:53:00.8300000	31
18	CheckSum	10:53:00.8466667	10:53:32.1433333	32
19	None	10:53:00.8466667	10:53:32.1600000	32
20	Torn_Page_Detection	10:53:32.1766667	10:54:06.6466667	34
21	CheckSum	10:54:06.6633333	10:54:36.9400000	30
22	None	10:54:06.6633333	10:54:36.9400000	30
23	Torn_Page_Detection	10:54:36.9566667	10:55:09.7533333	33
24	CheckSum	10:55:09.7700000	10:55:41.8866667	32
25	None	10:55:09.7700000	10:55:41.8866667	32
26	Torn_Page_Detection	10:55:41.9033333	10:56:15.0166667	34
27	CheckSum	10:56:15.0166667	10:56:45.6966667	30
28	None	10:56:15.0166667	10:56:45.7133333	30
29	Torn_Page_Detection	10:56:45.7133333	10:57:21.7900000	36
30	CheckSum	10:57:21.7933333	10:57:54.8433333	33

Tabela 1 – Detalhamento de dados coletados após o processamento do Bloco de Código 2.

— Parte 2 – Consultando o resumo sumarizado —
Select TipoDaAnaliseRealizada,
Avg(HoraDiferenca) As ‘Média em segundos’
From PageVerifyTempoDecorrido
Group By TipoDaAnaliseRealizada
Order By ‘Média em segundos’ Desc
Go

A Tabela 2 apresentada a seguir, ilustra o dados coletados durante o processamento do Bloco de Código 2 realizado anteriormente de forma sumarizada com o cálculo da média de segundos gastos para o processamento de cada opção de verificação de páginas:

Tipo da Análise	Média em segundos
None	34
Torn_Page_Detection	33
CheckSum	32

Tabela 2 – Dados sumarizados após o processamento do Bloco de Código 2.

Falta só um pouco, o último passo que iremos realizar, consiste na apresentação do Sumário de processamento em segundos demandado por cada algoritmo de verificação de páginas, através da execução do Bloco de Código 4, sendo que o mesmo apresentará os seguintes resultados:

Média de processamento em segundos;
Menor tempo de processamento em segundos;
Maior tempo de processamento em segundos; e
Somatória total do tempo de processamento.

— Bloco de Código 4 —
— Pivot —
Select ‘Média de processamento…..’ As ‘Sumário por segundos’, [None],[Torn_Page_Detection],[CheckSum]
From (Select TipoDaAnaliseRealizada,
HoraDiferenca
From PageVerifyTempoDecorrido
) As A
Pivot (Avg(HoraDiferenca) For TipoDaAnaliseRealizada In ([None],[Torn_Page_Detection],[CheckSum])) As Pvt
Union All
Select ‘Menor tempo de processamento…..’, [None],[Torn_Page_Detection],[CheckSum]
From (Select TipoDaAnaliseRealizada,
HoraDiferenca
From PageVerifyTempoDecorrido
) As A
Pivot (Min(HoraDiferenca) For TipoDaAnaliseRealizada In ([None],[Torn_Page_Detection],[CheckSum])) As Pvt
Union All
Select ‘Maior tempo de processamento…..’, [None],[Torn_Page_Detection],[CheckSum]
From (Select TipoDaAnaliseRealizada,
HoraDiferenca
From PageVerifyTempoDecorrido
) As A
Pivot (Max(HoraDiferenca) For TipoDaAnaliseRealizada In ([None],[Torn_Page_Detection],[CheckSum])) As Pvt
Union All
Select ‘Somatória do tempo de processamento…..’, [None],[Torn_Page_Detection],[CheckSum]
From (Select TipoDaAnaliseRealizada,
HoraDiferenca
From PageVerifyTempoDecorrido
) As A
Pivot (Sum(HoraDiferenca) For TipoDaAnaliseRealizada In ([None],[Torn_Page_Detection],[CheckSum])) As Pvt
Go

A Tabela 3 apresentada a seguir, ilustra o dados coletados durante o processamento do Bloco de Código 4, sumarizados e estruturados através do uso de Pivot:

Sumário por segundos	None	Torn_Page_Detection	CheckSum
Média de processamento…..	34	33	32
Menor tempo de processamento…..	30	29	30
Maior tempo de processamento…..	52	40	38
Somatória do tempo de processamento…..	348	334	329

Tabela 3 – Dados sumarizados e apresentados em formato de Pivot após o processamento do Bloco de Código 4.

Agora sim, chegamos ao final. Ufa deu um pouco de trabalho este post, como de costume, mesmo assim sempre vale a pena poder compartilhar um pouco do conhecimento e experiências adquiridas ao longo dos anos de trabalho como DBA e Professor.

Espero que você tenha gostado, eu posso dizer que sim, mas sua opinião é muito importante.

Referências

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-transact-sql?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/policy-based-management/set-the-page-verify-database-option-to-checksum?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-properties-options-page?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/manage-the-suspect-pages-table-sql-server?view=sql-server-2017

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/2018/05/28/21-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2018/04/12/20-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2018/01/02/19-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2017/12/15/18-para-que-serve/

Conclusão

Pensar na melhor forma para se armazenar um dado em uma respectiva tabela, vai muito além de escolher o tipo de dados mais indicado, temos também que procurar conhecer cada vez mais as propriedades de definem a estrutura e comportamento de um banco de dados, visando sempre garantir a melhor forma de armazenar, coletar e garantir que nossos servidores, aplicações e usuários possam fazer uso desta tão importante matéria prima utilizada pelas organizações, os dados que estão guardados em nossos bancos dados.

Neste post, você pode conhecer uma mais sobre a propriedade de banco de dados Page_Verify e suas opções: None, Torn_Page_Detection e CheckSum. Propriedade que desempenha um papel de extrema importância no que diz respeita a garantir que nossos dados estão armazenados de forma integra e confiável.

Realizamos uma simples análise para elucidar como estas opções podem influenciar no comportamento do Database Engine e também no Storage Engine, conhecemos também como cada opção desempenha o papel de verificar a estrutura física e lógica das páginas de dados que formam nossas tabelas, fazendo uso de algoritmos que gravam diretamente bits nos cabeçalhos destas mesma páginas, como fonte de identificação para tentar garantir que sua estrutura esta integra e confiável.

Mais uma vez destaco que este o estudo realizado no cenário apresentando neste post, não tem a finalidade de mostrar qual opção é mais rápida, mas sim demonstrar como em alguns momento a opção Torn_Page_Detection em comparação com a CheckSum pode ser mais vantajosa ou não, sabendo que a opção None não é recomendada para fiz corporativos.

Este é o fantástico Microsoft SQL Server, que desde suas primeiras versões nos apresenta inúmeras possibilidade de se aprender, possibilitando usar o passado como fonte de inspiração para construção de um futuro melhor, por isso que a cada dia eu me apaixono ainda mais por este produto…

Vai SQL Server, Vai SQL Server….

Agradecimentos

Mais uma vez obrigado por sua ilustre visita, sinto-me honrado com sua presença, espero que este conteúdo possa lhe ajudar e ser útil em suas atividades profissionais e acadêmicas.

Um forte abraço, até o próximo post da sessão Para que serve a ser publicado no mês novembro.

Um grande abraço e ótima semana.

Valeu.

Microsoft SQL Server 2017 Cumulative Update 9 disponível

A Microsoft informou ontem dia 19/07 no blog SQL Server Release Services a disponibilidade da Atualização Cumulativa(Cumulative Update) 9 para o Microsoft SQL Server 2017.

Atualizações Cumulativas disponíveis para o Microsoft SQL Server 2017:

O artigo KB4341265 publicado no site de suporte da Microsoft, esta nova atualização do SQL Server 2017 traz todas as correções disponibilizadas desde o lançamento do novo SQL Server, incluindo também correções para problemas encontrados após o lançamento das atualizações cumulativas anteriores.

Hotfixes que estão incluídos neste pacote de atualização cumulativa:

Número do bug VSTS	Número do artigo KB	Descrição	Área fixa	Plataforma
12144190	4340069	CORREÇÃO: SQL Server 2017 no Linux é desligado inesperadamente durante a recuperação de um banco de dados OLTP de memória	OLTP in-memory	Linux
12041154	4340134	CORREÇÃO: Erro quando uma função é definida com uma coluna restrita é usada para executar uma consulta drill-through no SSAS	Analysis Services	Windows
12128861	4340747	CORRIGIR: SQLDUMPER. Despejos EXE iniciada podem levar muito tempo para concluir o processo de geração de despejo para 2017 do SQL Server no Linux	Mecanismo SQL	Linux
12168709	4010460	CORREÇÃO: Um erro do.NET Framework ocorreu quando você atualiza a tabela de referência de uma transformação Fuzzy Lookup no SSIS	Integration Services	Windows
12138685	4339613	CORREÇÃO: “Unclosed aspas após a sequência de caracteres” erro ocorre no explorer MDS quando você tentar adicionar um novo membro para uma entidade no SQL Server	Data Quality Services (DQS)	Windows
12107546	4338890	CORREÇÃO: Uma instância do SQL Server pode parecer não responder e em seguida, pode ocorrer um erro de “não respondendo no Agendador” no SQL Server 2016	Mecanismo SQL	Windows
11922902	4316858	CORREÇÃO: “índice corrompido” mensagem e servidor desconexão quando uma consulta de estatísticas de atualização usa hash agregação no SQL Server	Desempenho do SQL	Todas
12149855	4341219	CORREÇÃO: Um cenário de cérebro divisão ocorre após um failover ao usar grupos de disponibilidade do AlwaysOn com a tecnologia de cluster externo no SQL Server 2017	Alta disponibilidade	Todas
12111717	4340837	CORREÇÃO: Erro 3906 quando for aplicado um hotfix em um SQL Server que possui um banco de dados em um banco de dados de inscrição de recepção de instantâneo	Mecanismo SQL	Windows
11983925	4133164	CORREÇÃO: Erro quando um trabalho do SQL Server Agent executa um comando PowerShell enumere permissões do banco de dados	Ferramentas de gerenciamento	Windows
12121216	4339664	CORREÇÃO: O erro de exceção ocorre quando você tenta atualizar dados de uma tabela dinâmica no Excel no SSAS 2017	Analysis Services	Windows
12123248	4340742	CORREÇÃO: Acesso ao SSAS usando HTTP falha no SQL Server	Analysis Services	Windows
12162067	4341264	Aperfeiçoamento: Permitir trabalhos do SQL Server Agent iniciar sem esperar que todos os bancos de dados obter recuperado no SQL Server 2017 no Linux	Mecanismo SQL	Linux
12186129	4101502	CORREÇÃO: Backup de banco de dados TDE habilitada com compactação causa corrupção de banco de dados no SQL Server	Mecanismo SQL	Todas
12129434	4134601	CORREÇÃO: “não foi possível carregar arquivo ou assembly ‘ Microsoft.AnalysisServices.AdomdClientUI” erro quando uma operação de “Processo total” é executada no SQL Server	Analysis Services	Windows
12162425	4341221	CORREÇÃO: Backup VSS Falha na réplica secundária de grupos básicos de disponibilidade no SQL Server 2016 e 2017	Mecanismo SQL	Windows
12108225	4339858	CORREÇÃO: Redo paralelo não funciona após você desativar 3459 de sinalizador de rastreamento em uma instância do SQL Server	Alta disponibilidade	Todas
12061383	4341253	CORREÇÃO: Sys.dm_db_log_info e sys.dm_db_log_stats DMVs podem retornar valores incorretos para o último banco de dados da instância do SQL Server 2016	Mecanismo SQL	Windows

Dentre os erros e falhas corrigidas neste cumulative update, as informações apresentadas no KB4341265 destacam uma correção relacionada comportamento apresentado por uma instância do SQL Server 2017 que aparentemente encontra-se travada e exibindo o erro “Non-yielding Scheduler“.

Outra correção destacada no artigo, se relaciona ao erro “Could not load file or assembly ‘Microsoft.AnalysisServices.AdomdClientUI”.

Vale ressaltar que além de correções relacionadas a erros apresentados por comportamentos apresentadas pelas instância SQL Server 2017, a CU9 também possui correções para os bugs relacionados as DMVs sys.dm_db_log_stats e sys.dm_db_log_info may retornem valores incorretos em determinados momentos de consulta de dados relacionados aos arquivos de log existentes em bancos de dados.

Vale ressaltar que após a atualização desta nova atualização cumulativa, o número do build utilizado pelo Microsoft SQL Server 2017 RTM será alterado para compilação: 14.0.3030.27.

Para realizar o download clique na imagem abaixo:

Fontes e Direitos Autorais: SQL Server Release Services – 19/07/2018.

Microsoft SQL Server 2016 SP2 – Atualização Cumulativa 1 disponível

A Microsoft disponibilizou para download hoje dia 30/05/2018 a Atualização Cumulativa 1 para SQL Server 2016 SP2.

As informações obtidas através do artigo KB4135048 publicado no site de suporte da Microsoft, esta atualização traz correções decorrentes dos problemas apresentados e identificados após o lançamento do SP2, sendo esta a primeira atualização cumulativa para este service pack.

Hotfixes que estão incluídos neste pacote de atualização cumulativa

Número de bug do VSTS	Número de artigo do KB	Descrição	Corrigir a área
11983391	4131193	Problemas de desempenho ocorrem sob a forma de PAGELATCH_EX e PAGELATCH_SH espera em TempDB quando você usar o SQL Server 2016	Serviço do SQL
11983323	4133164	CORREÇÃO: Erro quando um trabalho do SQL Server Agent executa um comando do PowerShell para enumerar as permissões do banco de dados	Ferramentas de gerenciamento
11516235	4087406	CORREÇÃO: Erro 9002 quando não houver nenhum espaço em disco suficiente para crescimento de crítica de log no SQL Server 2014, 2016 e 2017	Serviço do SQL
11695337	4092554	CORREÇÃO: “não é possível usar salvar transação dentro de uma transação distribuída” erro quando você executar um procedimento armazenado no SQL Server	Serviço do SQL
11983395	4086173	CORREÇÃO: Violação de acesso ocorre ao executar uma consulta DAX em um modelo tabular no SQL Server Analysis Services	Serviços de análise
11983390	4099472	PFS página melhoria de algoritmo round robin no SQL Server 2016	Serviço do SQL
11983376	3028216	CORREÇÃO: Ocorre uma falha quando o cache pró-ativo é desencadeada por uma dimensão no SSAS	Serviços de análise
11971819	4230516	CORREÇÃO: Uma falha de asserção de memória ocorre e o servidor é incapaz de fazer qualquer novas conexões no SQL Server	Serviço do SQL
11684529	4164562	CORREÇÃO: Nome de usuário errado aparece quando dois usuários acesse a MDS em momentos diferentes no SQL Server	Serviços de qualidade de dados (DQS)
11578522	4089718	Melhoria: Problema de desempenho ao atualizar o MDS do SQL Server 2012 para 2016	Serviços de qualidade de dados (DQS)
11983367	4134541	CORREÇÃO: Erro do MDS Add-in para o Excel quando você usar a versão alemã do Excel no SQL Server	Serviços de qualidade de dados (DQS)
11983360	4094858	CORREÇÃO: “ocorreu um erro inesperado” quando você usa o DAX medidas em visualizações de mesa poder BI no SQL Server	Serviços de análise
11983362	4094706	FIX: Um thread de trabalho parece ficar depois que outro thread de trabalho é abortado quando você executa uma consulta paralela no SQL Server	Serviço do SQL
11983392	4101502	CORREÇÃO: Backup de banco de dados habilitada a TDE com a compactação causa corrupção de banco de dados no SQL Server 2016	Serviço do SQL
11983382	4230306	CORREÇÃO: Restauração de um backup compactado TDE é vencida quando usando o cliente VDI	Serviço do SQL
11983383	4163087	CORREÇÃO: Desempenho é lento para um sempre na AG quando você processa uma leitura consulta no SQL Server	Serviço do SQL
11983373	4162814	CORREÇÃO: Ocorre uma violação de acesso de exceção interna e o servidor SSAS para de responder	Serviços de análise
11922532	4157948	CORREÇÃO: Erro de estouro de ponto flutuante ocorre quando você executar um módulo compilado nativamente aninhado que usa EXP funções no SQL Server	Na memória OLTP
11983358	4094893	CORREÇÃO: Banco de dados não pode ser descartado após seu armazenamento é desconectado e reconectado no SQL Server	Serviço do SQL
11983381	4058175	CORREÇÃO: Backup banco de dados habilitado para TDE e as operações de restauração são lentas quando a chave de criptografia é armazenada em um provedor de EKM no SQL Server	Segurança do SQL
11983369	4134175	FIX: Um cubo com várias partições de processamento gera muitas conexões de fonte de dados simultâneos no SSAS	Serviços de análise
11983357	4091245	CORREÇÃO: Violação de acesso ocorre quando você consulta uma tabela com uma coluna de inteiro em 2017 de SQL Server e SQL Server 2016	Desempenho de SQL
11983366	4101554	CORREÇÃO: Paralelo refazer em uma réplica do secundária de um grupo de disponibilidade que contém tabelas heap gera um despejo de declaração de tempo de execução ou o servidor de SQL falha com um erro de violação de acesso	Alta disponibilidade
11983379	4131960	CORREÇÃO: Um erro de declaração ocorre quando você executar uma consulta select aninhada contra um índice de columnstore no SQL Server	Desempenho de SQL
11983359	4132267	CORREÇÃO: Implantar um projeto SSAS em SSDT é frequentemente mal sucedido no SQL Server Analysis Services no modo Tabular	Serviços de análise
11057341	4052135	CORREÇÃO: Instrução RESTORE HEADERONLY para um TDE compactado backup leva muito tempo para concluir no SQL Server	Serviço do SQL
11750742	4098762	CORREÇÃO: Parâmetros ocultos são incluídos nos relatórios quando o papel do navegador é usado em 2016 SSRS	O Reporting Services
11983394	4163478	CORREÇÃO: Uma violação de acesso ocorre quando incrementais estatísticas são atualizadas automaticamente em uma tabela no SQL Server	Desempenho de SQL

Dentre os mais diversos bugs identificados e corrigidos destaco um relacionado ao bug de número: 11983332, artigo KB: 4133164, relacionada a um erro apresentado quando um trabalho do SQL Server Agent executa um comando do PowerShell para enumerar as permissões do banco de dados.

Vale ressaltar que após a atualização desta nova atualização cumulativa, o número do build utilizado pelo Microsoft SQL Server 2016 SP2 será alterado para compilação: 13.0.5149.0.

Para realizar o download clique na imagem abaixo:

Fontes e Direitos Autorais: Suporte da Microsoft -https://support.microsoft.com/pt-br/help/4135048/cumulative-update-1-for-sql-server-2016-sp2 – 30/05/2018.

Microsoft SQL Server 2016 SP1 – Atualização Cumulativa 9 disponível

A Microsoft disponibilizou para download hoje dia 30/05/2018 a Atualização Cumulativa 9 para SQL Server 2016 SP1.

As informações obtidas através do artigo KB4100997 publicado no site de suporte da Microsoft, esta atualização traz correções decorrentes dos problemas apresentados e identificados após o lançamento do SP1 e das atualizações cumulativas anteriores.

Relação de Atualizações Cumulativas disponíveis para o Microsoft SQL Server 2016 Service Pack 1:

SQL Server 2016 SP1 CU8

SQL Server 2016 SP1 CU7

SQL Server 2016 SP1 CU6

SQL Server 2016 SP1 CU5

SQL Server 2016 SP1 CU4

SQL Server 2016 SP1 CU3

SQL Server 2016 SP1 CU2

SQL Server 2016 SP1 CU1

SQL Server 2016 SP1

SQL Server 2016 all builds

Hotfixes que estão incluídos neste pacote de atualização cumulativa

Número de bug do VSTS	Número de artigo do KB	Descrição	Corrigir a área
11814294	4099472	PFS página melhoria de algoritmo round robin no SQL Server 2016	Serviço do SQL
11231756	4133164	CORREÇÃO: Erro quando um trabalho do SQL Server Agent executa um comando do PowerShell para enumerar as permissões do banco de dados	Ferramentas de gerenciamento
11701139	4086173	CORREÇÃO: Violação de acesso ocorre ao executar uma consulta DAX em um modelo tabular no SQL Server Analysis Services	Serviços de análise
11814333	4131193	Problemas de desempenho ocorrem sob a forma de PAGELATCH_EX e PAGELATCH_SH espera em TempDB quando você usar o SQL Server 2016	Serviço do SQL
11829791	3028216	CORREÇÃO: Ocorre uma falha quando o cache pró-ativo é desencadeada por uma dimensão no SSAS	Serviços de análise
11829056	4135113	CORREÇÃO: Registro de controle de alterações é inconsistente durante uma atualização em uma tabela que tem um índice cluster/exclusivo no SQL Server	Serviço do SQL
11918578	4293839	CORREÇÃO: Banco de dados TDE fica offline durante operações de descarga de log quando problemas de conectividade com o provedor EKM tornar-se inacessível no SQL Server	Segurança do SQL
11810404	4230730	CORREÇÃO: Uma condição de morto trava ocorre quando você executar uma reconstrução de índice online ou executa um comando de mesclagem no SQL Server	Serviço do SQL
11793118	4163478	CORREÇÃO: Uma violação de acesso ocorre quando incrementais estatísticas são atualizadas automaticamente em uma tabela no SQL Server	Desempenho de SQL
11923632	4230306	CORREÇÃO: Restauração de um backup compactado TDE é vencida quando usando o cliente VDI	Serviço do SQL
11924460	4163087	CORREÇÃO: Desempenho é lento para um sempre na AG quando você processa uma leitura consulta no SQL Server	Serviço do SQL
11684528	4164562	CORREÇÃO: Nome de usuário errado aparece quando dois usuários acesse a MDS em momentos diferentes no SQL Server	Serviços de qualidade de dados (DQS)
11634113	4094893	CORREÇÃO: Banco de dados não pode ser descartado após seu armazenamento é desconectado e reconectado no SQL Server	Serviço do SQL
11708639	4162814	CORREÇÃO: Ocorre uma violação de acesso de exceção interna e o servidor SSAS para de responder	Serviços de análise
11801446	4134541	CORREÇÃO: Erro do MDS Add-in para o Excel quando você usar a versão alemã do Excel no SQL Server	Serviços de qualidade de dados (DQS)
11637501	4132267	CORREÇÃO: Implantar um projeto SSAS em SSDT é frequentemente mal sucedido no SQL Server Analysis Services no modo Tabular	Serviços de análise
11797887	4101554	CORREÇÃO: Paralelo refazer em uma réplica do secundária de um grupo de disponibilidade que contém tabelas heap gera um despejo de declaração de tempo de execução ou o servidor de SQL falha com um erro de violação de acesso	Alta disponibilidade
11750742	4098762	CORREÇÃO: Parâmetros ocultos são incluídos nos relatórios quando o papel do navegador é usado em 2016 SSRS	O Reporting Services
11830380	4134175	FIX: Um cubo com várias partições de processamento gera muitas conexões de fonte de dados simultâneos no SSAS	Serviços de análise
11591371	4091245	CORREÇÃO: Violação de acesso ocorre quando você consulta uma tabela com uma coluna de inteiro em 2017 de SQL Server e SQL Server 2016	Desempenho de SQL
11714686	4094706	FIX: Um thread de trabalho parece ficar depois que outro thread de trabalho é abortado quando você executa uma consulta paralela no SQL Server	Serviço do SQL
11953725	4058175	CORREÇÃO: Backup banco de dados habilitado para TDE e as operações de restauração são lentas quando a chave de criptografia é armazenada em um provedor de EKM no SQL Server	Serviço do SQL
11833599	4131960	CORREÇÃO: Uma violação de acesso ocorre quando você executar uma consulta select aninhada contra um índice de columnstore no SQL Server	Mecanismo do SQL
11676935	4094858	CORREÇÃO: “ocorreu um erro inesperado” quando você usa o DAX medidas em visualizações de mesa poder BI no SQL Server	Serviços de análise
11791348	4101502	CORREÇÃO: Backup de banco de dados habilitada a TDE com a compactação causa corrupção de banco de dados no SQL Server 2016	Serviço do SQL

Dentre os mais diversos bugs identificados e corrigidos destaco um relacionado ao bug de número: 11923632, artigo KB: 4230306, que corresponde um erro apresentado durante a restauração de um backup de banco de dados compactado que utiliza criptografia transparente de dados TDE.

Vale ressaltar que após a atualização desta nova atualização cumulativa, o número do build utilizado pelo Microsoft SQL Server 2016 SP1 será alterado para compilação: 13.0.4502.0.

Para realizar o download clique na imagem abaixo:

Fontes e Direitos Autorais: Suporte da Microsoft – https://support.microsoft.com/pt-br/help/4100997/cumulative-update-9-for-sql-server-2016-sp1 – 30/05/2018.

Microsoft disponibiliza atualização cumulativa 7 para o Microsoft SQL Server 2017

A Microsoft disponibilizou ontem dia 24/05 uma nova Atualização Cumulativa para o Microsoft SQL Server 2017, denominada (Cumulative Update 7 – Build number: 14.0.3026.27), disponível para todas as edições existentes para Windows e Linux.

Com base no artigo KB4229789 publicado no site de suporte da Microsoft, a atualização apresenta correções para múltiplos bugs descobertos após o lançamento do SQL Server 2017 e atualizações cumulativas anteriores, dentre as quais ao final do artigo bloco de código que de ser realizado por aqueles que já tenham instalado as Cumulative Updates 2 ou 3 e necessitam trabalhar com o Query Store.

Outro bug corrigido destaca-se um bem conhecido relacionado ao Analysis Services após a execução de uma função DAX e um que faz com que o SQL Server não seja inicializado quando uma imagem de contêiner do Linux é usada no Docker para Windows.

A seguir destaco a lista de hotfixes incluídos nesta nova atualização:

VSTS bug number	KB article number	Description	Fix area	Platform
11701171	2932559	FIX: Totals are wrong after you filter on a pivot table item and remove the filter in SSAS	Analysis Services	Windows
11701194	4090032	FIX: Memory gets exhausted when you run Power BI report that executes DAX query on SSAS 2016 and 2017 Multidimensional mode	Analysis Services	Windows
11751257	4096258	FIX: An unexpected exception occurs and SSAS crashes when you run a particular DAX function in SQL Server 2017	Analysis Services	Windows
11919582	4090004	Improvement: Configure SESSION_TIMEOUT value for a Distributed Availability Group replica in SQL Server 2016 and 2017	High Availability	Windows
11919583	4099919	Transparent Data Encryption added for Log Shipping in SQL Server 2016 and 2017	High Availability	Windows
11708399	4135045	FIX: DROP_ASYMMETRIC_KEY causes an instance of SQL Server to crash if it’s rolled back from a trigger	SQL service	All
11953218	4212960	FIX: SQL Server will not start when you run a SQL Server 2017 Linux container image on Docker for Windows	SQL service	Linux
11701193	4074862	FIX: Unexpected error when you create a subcube in SQL Server 2016 and 2017 Analysis Services (Multidimensional model)	Analysis Services	Windows
11701196	4083949	FIX: SSAS may crash when you run a DAX query by using a non-admin Windows user in SQL Server 2016 and 2017	Analysis Services	Windows
11701201	4086136	FIX: SSAS stops responding when you run an MDX query in SQL Server 2016 and 2017 Analysis Services (Multidimensional model)	Analysis Services	Windows
11701140	4086173	FIX: Access violation occurs when executing a DAX query on a tabular model in SQL Server Analysis Services	Analysis Services	Windows
11701208	4089623	FIX: Out of memory occurs and query fails when you run MDX query with NON EMPTY option in SSAS	Analysis Services	Windows
11578523	4089718	Improvement: Performance issue when upgrading MDS from SQL Server 2012 to 2016	Data Quality Services (DQS)	Windows
11701179	4093226	FIX: “DirectQuery may not be used with this data source” error when you browse a Direct Query model in SQL Server	Analysis Services	Windows
11814297	4099472	PFS page round robin algorithm improvement in SQL Server 2016	SQL service	Windows
11870176	4136912	Multiple device VDI backup can hang on Linux	SQL service	Linux
11634330	4092554	FIX: “Cannot use SAVE TRANSACTION within a distributed transaction” error when you execute a stored procedure in SQL Server	SQL service	Windows
11922305	4157948	FIX: Floating point overflow error occurs when you execute a nested natively compiled module that uses EXP functions in SQL Server	In-Memory OLTP	All
11642085	4098732	FIX: A calculation error occurs when a secured measure is queried in SSAS 2017	Analysis Services	Windows
11923799	4230306	FIX: Restore of a TDE compressed backup is unsuccessful when using the VDI client	SQL service	All
11823305	4101502	FIX: TDE enabled database backup with compression causes database corruption in SQL Server 2016	SQL service	All
11971856	4230516	FIX: A memory assertion failure occurs and the server is unable to make any new connections in SQL Server	SQL service	Windows
11952929	4163087	FIX: Performance is slow for an Always On AG when you process a read query in SQL Server	SQL service	All
11951171	4230465	FIX: An unexpected communication link error occurs when using datetime2 fields accessing a database in SQL Server 2017	SQL service	All
11974640	4230542	FIX: Pacemaker promotes local replica to primary may fail when you use AlwaysOn AG in SQL Server 2017	High Availability	Linux
11974802	4293576	FIX: “Non-yielding” error when you try to recover an In-Memory database in SQL Server	In-Memory OLTP	All
11853504	4134601	FIX: “Could not load file or assembly ‘Microsoft.AnalysisServices.AdomdClientUI” error when a “Process Full” operation is run in SQL Server	Analysis Services	Windows
11967438	4230730	FIX: A dead latch condition occurs when you perform an online index rebuild or execute a merge command in SQL Server	SQL service	Windows

Para realizar o download clique na imagem abaixo:

Fontes e Direitos Autorais: Microsoft Support – 24/05/2018 – https://support.microsoft.com/en-us/help/4229789/cumulative-update-7-for-sql-server-2017

Script Challenge – 13 – A resposta….

Salve pessoal, bom dia.

Tudo bem? Seja mais uma vez muito bem vindo ao meu blog, mais especificamente ao post que apresenta a resposta para o Script Challenge, publicado em outubro de 2017 destacando o retorno da sessão Script Challenge (Script Desafiador ou Desafio do Script) como queiram traduzir.

Espero que você já tenha ouvido falar desta sessão ou acessado alguns dos posts publicados na mesma, caso ainda não tenha feito, fique tranquilo você vai encontrar no final deste post uma pequena relação contendo os últimos desafios lançados e seus respostas.

Vamos então falar um pouco mais sobre o último desafio, estou me referindo ao Script Challenge 13, sendo assim, seja bem vindo a mais um post da sessão Script Challenge.

Script Challenge 13

Falando do desafio de número 13, o mesmo foi publicado no mês de outubro de 2017, período de data que apresenta uma das comemorações mais importantes que ocorrem anualmente em quase todos os países do mundo, mas que especialmente no Estados Unidos da América.

E ai já matou a charada? Eu acredito que sim! Mas para te ajudar mais um pouco vou apresentar a Figura 1 que contem todo código Transact-SQL utilizado neste desafio, contendo trechos ou partes de código ocultas, procedimento que realizei no post que contempla o lançamento deste desafio como forma de aumentar o nível de dificuldade:

Figura 1 – Código Transact-SQL apresentado no Script Challenge 13.

Bom chegou a hora de revelar o que exatamente este pequeno bloco de código esta fazendo, chegou o momento de revelar e desvendar este desafio, a seguir apresento a resposta para o Script Challenge 13 e o trecho de código disponível para você utilizar em seus ambientes de trabalho ou estudos.

A resposta

Tanto no post de lançamento do desafio, bem como, neste post de apresenta a resposta para o mesmo, eu deixei algumas pequenas dicas para tentar ajudar a identificar a resposta, dentre as quais a relação do script com uma das datas comemorativas mais tradicionais dos Estados Unidos, neste caso o Halloween(conhecido tradicionalmente como dia das bruxas).

Mesmo assim você pode estar se perguntando, o que Script Challenge 13 tem haver com dia das bruxas, ué tudo haver, pois quando falamos de bruxas, temos também em mente a relação com magia, fantasias, medo, terror e propriamente a fantasmas, isso mesmos fantasmas, algo que também pode acontecer em nossas tabelas com o passar do tempo conforme vamos realizando as manipulações de dados, torna-se possível se deparar com a ocorrência de possíveis dados fantasmas.

Então a resposta para o Script Challenge 13 é justamente a possibilidade que o script apresenta em identificar uma possível ocorrência de dados fantasmas em nossas tabelas e bancos de dados.

Isso mesmo, esta é a resposta, e o script original que apresenta esta funcionalidade apresentado abaixo:

— Script Challenge 13 – A resposta – Identificando a ocorrência de dados fantasmas —

SELECT db_name(database_id),
object_name(object_id),
ghost_record_count,
version_ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(N’GhostDB’),
OBJECT_ID(N’GhostTable’),
NULL,
NULL ,
‘DETAILED’)
GO

Que coisa de louco isso Galvão! Sim realmente parece ser coisa de outro mundo, mas podem acontecer tranquilamente a qualquer momento, o complicado é imaginar, que nossos bancos de dados, podem ser vítimas deste tipo de situação.

Mas isso não é o fim do mundo, muito ao contrário, para este cenário temos uma grande vantagem. Você pode estar se perguntando qual, a resposta é simples, não precisamos chamar os Casas Fantasmas, nós mesmos podemos resolver facilmente isso. Foi pensando justamente nas possibilidades da ocorrência deste tipo de situação, que além da compartilhar a resposta para este desafio, vou deixar também um cenário de simulação de como é possível ocorrência a existência de dados fantasmas, como também a possibilidade de excluir estes “dados”, a seguir:

— Simulando a ocorrência de dados fantasmas —

— Criando o Banco de Dados – GhostDB —
Create Database GhostDB
Go

— Acessando o Banco de Dados —
Use GhostDB
Go

— Criando a Tabela GhostTable —
Create Table GhostTable
(GhostRecord Int)
Go

— Criando um índice clusterizado —
Create Clustered Index Ind_GhostTable_GhostRecord On GhostTable(GhostRecord)
Go

— Inserindo Dados na Tabela GhostTable —
Insert Into GhostTable
Select 100
Go

— Obtendo informações sobre as estatísticas de alocação de dados —
Select object_id,
index_id,
index_depth,
index_level
From sys.dm_db_index_physical_stats(db_id(),
object_id(‘GhostTable’),
object_id(‘Ind_GhostTable_GhostRecord’),
null,
null)
Go

— Obtendo informações sobre o Índice IND_GhostTable_GhostRecord —
Select id, name, root, first
from sys.sysindexes
where id=565577053 — Aqui você vai colocar o ID identificado do índice apresentado na sua máquina —
Go

— Identificando a página de dados que contem os dados inseridos na GhostTable —
SELECT first_page,
(convert(varchar(2), (convert(int, substring(first_page, 6, 1)) * power(2, 8)) +
(convert(int, substring(first_page, 5, 1)))) + ‘:’ + convert(varchar(11),
(convert(int, substring(first_page, 4, 1)) * power(2, 24)) +
(convert(int, substring(first_page, 3, 1)) * power(2, 16)) +
(convert(int, substring(first_page, 2, 1)) * power(2, 8)) +
(convert(int, substring(first_page, 1, 1))))) As Page
FROM SYS.SYSTEM_INTERNALS_ALLOCATION_UNITS
Where first_page = 0x180100000100 — Valor obtido no bloco de código anterior através da coluna root —
Go

— Habilitando a Trace Flag 3604 para apresentar informações sobre as páginas de dados —
DBCC TRACEON (3604)
GO

— Consultando informações sobre as páginas de dados relacionadas o índice Ind_GhostTable_GhostRecord —
DBCC PAGE(GhostDB,1,280,1)
Go

Após obter o resultado do DBCC Page procure pela coluna m_ghostRecCnt, neste momento ela deve esta apresentando o valor m_ghostRecCnt = 0.

— Excluíndo os registros em GhostTable —
Delete from GhostTable
Where GhostRecord=100
Go

— Consultando informações sobre as páginas de dados relacionadas o índice Ind_GhostTable_GhostRecord —
DBCC PAGE(GhostDB,1,280,3)
Go

Agora verifique novamente a coluna m_ghostRecCnt que neste momento deverá apresentar o valor igual á m_ghostRecCnt = 1, este é o indicador da ocorrência de um dado fantasma em nossa tabela.

— Confirmando a existência de um registro fantasmas —
SELECT db_name(database_id),
object_name(object_id),
ghost_record_count,
version_ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(N’GhostDB’), OBJECT_ID(N’GhostTable’), NULL, NULL , ‘DETAILED’)
GO

— Simulando a eliminação de dados fantasmas —
Alter Table GhostTable
Rebuild — Utilize este tipo de procedimento em uma tabela com poucos registros —
Go

— Confirmando a existência de um registros fantasmas —
SELECT db_name(database_id),
object_name(object_id),
ghost_record_count,
version_ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(N’GhostDB’),
OBJECT_ID(N’GhostTable’),
NULL,
NULL ,
‘DETAILED’)
GO

— Liberando espaço alocado anteriormente em disco pelos registros fantasmas —
Exec sp_clean_db_free_space @dbname=N’GhostDB’
Go

Meu deus, conseguimos, chegamos ao final, esta é a resposta para o Script Challenge 13, sinceramente falando achei que não iria conseguir compartilhar este conteúdo com vocês.

Espero que tenham gostado desta da volta desta sessão, como também, a nova maneira que pretendo apresentar os desafios e seus respostas.

Sua Participação

No post de lançamento deste desafio, contei com a participação através de uma enquete contendo algumas opções de respostas que poderiam estar relacionadas com o Script Challenge 13. A seguir apresento o resultado desta enquete:

A opção mais votada com 66,67% dos votos é justamente a resposta correta para este desafio, o qual retorna ao usuário informações relacionadas a identificação de páginas de dados com fragmentação.

Referências

Agradecimentos

Obrigado por sua visita, espero que o retorno desta sessão e o conteúdo aqui apresentado como um possível “desafio” possam ser úteis e ao mesmo tempo prover conhecimento, aprendizado ou mostrar recursos e problemas existentes no Microsoft SQL Server que as vezes parecem não ter uma resposta.

Um forte abraço nos encontramos em breve nas demais sessões e especialmente em junho de 2018 em mais um post da sessão Script Challenge.

Até a próxima…

Short Scripts – Fevereiro 2018 – Transaction Log

Olá, bom dia, mais uma semana começando….

E você já esta aqui acessando o meu blog, que alegria poder te encontrar em mais um post da sessão Short Scripts, uma das sessões mais recentes do meu blog que esta alçando a marca de 32 posts, sendo estes publicados trimestralmente.

Mantendo a tradição estou retornando com mais um conjunto de “pequenos” scripts catalogados e armazenados em minha biblioteca pessoal de códigos relacionados ao Microsoft SQL Server e sua fantástica linguagem de desenvolvimento Transact-SQL.

Mas como este é o primeiro post desta sessão em 2018, farei algo um pouco diferente, você terá uma pequena surpresa.

Desejo que o conteúdo aqui compartilhado possa lhe ser útil, como também sirvo de referência e sugestões para novas formar de resolução de problemas e aprendizado.

Vamos então conhecer um pouco mais sobre este novo post….

O post de hoje

Como já destacado no início do post, ao invés de compartilhar os últimos scripts adicionados a minha biblioteca, quero dividir com você um conteúdo dedicado especificamente a um assunto muito importante quando nos referimos ao Microsoft SQL Server, mais especificamente ao Transaction Log (Log de Transações), funcionalidade presente em todos os bancos de dados criados em qualquer versão e edição do SQL Server.

E ai que você achou desta surpresa, gostou? Eu gostei, não é fácil você conseguir encontrar em um único local um conteúdo focado exclusivamente a este assunto tão importante, que muitos profissionais que trabalham com banco de dados até hoje não conseguem entender o conceito e forma de atuação do Transaction-Log.

Seguindo em frente, a seguir apresento os códigos e exemplos selecionados para o Short Script – Fevereiro 2018 – Transaction Log. Vale ressaltar que todos os scripts publicados nesta sessão foram devidamente testados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, vale sim todo cuidado possível para evitar maiores problemas.

Short Scripts

Fique a vontade para compartilhar, comentar e melhorar cada um destes códigos:

— Short Script 1 – Log Record —

— Altera o Recovery Model para SIMPLE
ALTER DATABASE AdventureWorks2016
SET RECOVERY SIMPLE
Go

— Truncar o Transaction Log —
CHECKPOINT
Go

— Conteúdo do log – todas as colunas —
USE AdventureWorks2016
Select * from ::fn_dblog(null, null)
Go

–update
Begin Transaction

UPDATE dbo.Pessoa
SET nome = ‘XUXA’
Where ID=3

Rollback
Go

— Conteúdo armazenado no Log File —
Select [Current LSN],
Operation,
Context,
[Transaction ID],
[Log Record Length],
[Previous LSN],
AllocUnitName,
[Page ID],
[Slot ID],
[Checkpoint Begin],
[Checkpoint End],
[Minimum LSN],
SPID,
[Begin Time],
[Transaction Name],
[Parent Transaction ID],
[Lock Information],
Description,
[RowLog Contents 0],
[RowLog Contents 1],
[Log Record]
From ::fn_dblog(null, null)
Go

— DBCC SQLPERF —
DBCC SQLPERF(LOGSPACE)
Go

— Short Script 2 – CheckPoint —

— Criando a Base de Dados —
CREATE DATABASE DemoCheckpoint
ON PRIMARY
(NAME = ‘DemoCheckpoint_data’,
FILENAME = ‘D:\MSSQL\DemoCheckpoint_data.mdf’)
LOG ON
(Name = ‘DemoCheckpoint_Log’,
FILENAME = ‘D:\MSSQL\DemoCheckpoint_log.ldf’,
SIZE = 100MB,
FILEGROWTH = 10MB)
GO

— Alterando o Recovery Model —
ALTER DATABASE DemoCheckpoint
SET RECOVERY SIMPLE
Go

— Criando a Tabela Teste —
USE DemoCheckpoint
GO

CREATE TABLE Teste
(C1 varchar(50) NOT NULL,
C2 varchar(50) NOT NULL)
GO

— Forçando o Checkpoint —
CHECKPOINT
Go

— Abrir o Perfmon com os contadores

— em outra sessão
USE DemoCheckpoint
GO

WHILE 1=1
BEGIN

INSERT INTO dbo.teste
VALUES (‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’)
END

— Short Script 3 – Log Chain Simple —

— Iniciar nova sessão do Perfmon —
SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = ‘AdventureWorks2016’
Go

— Alterar Recovery Model para Simple —
ALTER DATABASE AdventureWorks2016
SET RECOVERY SIMPLE
Go

— Abrir nova Query —
USE AdventureWorks2016
Go

WHILE 1=1
BEGIN
INSERT INTO dbo.pessoa
VALUES (‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’)
END

— Alterar Recovery Model para Full —
ALTER DATABASE AdventureWorks2016
SET RECOVERY FULL
GO

— Realizar Backup Database —
BACKUP DATABASE AdventureWorks2016
TO DISK = ‘d:\backupcompress.bak’
WITH COMPRESSION,
DIFFERENTIAL
GO

— Short Script 4 – DBCC LogInfo —

— Criando uma nova base de dados —
CREATE DATABASE TestDB
ON PRIMARY
(NAME = ‘TestDB_data’,
FILENAME = ‘D:\MSSQL\TestDB_data.mdf’)
LOG ON
(Name = ‘TestDB_Log’,
FILENAME = ‘D:\MSSQL\TestDB_log.ldf’,
SIZE = 10MB,
FILEGROWTH = 10MB)
GO

— Obtendo informações sobre a base de dados —
DBCC LOGINFO(TestDB)
Go

–Forçando o crescimento do Transact-Log manualmente em 20MB —
ALTER DATABASE TestDB
MODIFY FILE
(NAME = ‘TestDB_Log’,
SIZE = 20MB);
GO

— Obtendo informações sobre a base de dados —
DBCC LOGINFO(TestDB)
Go

— Short Script 5 – Natureza Circular —

— Alterando Recovery Model FULL —
ALTER DATABASE TestDB
SET RECOVERY FULL;
Go

— Realizando Backup Database —
BACKUP DATABASE TestDB
TO DISK = ‘D:\TestDB.bak’
Go

— Forçando o encolhimento do Transaction – Log —
DBCC LOGINFO(TestDB)
Go

BACKUP LOG TestDB
TO DISK = ‘bkplogTestDB.trn’
Go

USE TestDB
Go

DBCC SHRINKFILE (TestDB_Log,1)
Go

DBCC LOGINFO(TestDB)
Go

— Criando uma nova Tabela —
USE TestDB
GO

CREATE TABLE dbo.pessoa
(ID int identity PRIMARY KEY NOT NULL,
Nome varchar(50) NOT NULL,
Sobrenome varchar(50) NOT NULL,
Nascimento date NOT NULL,
Cargo varchar(50))
GO

— Abrir nova query —
USE TestDB
GO

WHILE 1=1
BEGIN
INSERT INTO dbo.pessoa
VALUES (‘Junior’, ‘Galvão’, ‘19800428’, ‘Database Administrator’)
END

— Monitorar o crescimento do log em tempo de execução —
DBCC LOGINFO(TestDB)

CHECKPOINT

SELECT name,
Log_reuse_wait_desc
FROM sys.databases
WHERE name = ‘TestDB’
Go

— Realizar Backup do Arquivo de Log —
BACKUP LOG TestDB TO DISK = ‘d:\log.trn’
Go

— Alterando Recovery Model para Full
ALTER DATABASE AdventureWorks2016
SET RECOVERY FULL
Go

— Realizando novo Backup Database —
BACKUP DATABASE AdventureWorks2016
TO DISK = ‘d:\backup.bak’
WITH COMPRESSION
Go

— Short Script 6 – Backup and Transaction Log —

— Preparando a base – 1m10s se não preparada na demo 5
ALTER DATABASE AdventureWorks2016 SET RECOVERY FULL
GO
BACKUP DATABASE AdventureWorks2016 TO DISK = ‘d:\backup.bak’ WITH COMPRESSION
GO

— Realizando Backup do Arquivo de Log —
BACKUP LOG AdventureWorks2016
TO DISK = ‘bkplog.trn’
Go

— Obtendo informações sobre o Log —
DBCC LOGINFO(AdventureWorks2016)
Go

— Encolhendo o Transaction Log —
USE AdventureWorks2016
Go

DBCC SHRINKFILE (AdventureWorks2016_Log,1)
Go

— Obtendo informações sobre o Log —
DBCC LOGINFO(AdventureWorks2016)
Go

— Ajustando o tamanho do Transaction Log —
USE AdventureWorks2016
Go

— Encolhendo o Transaction Log —
DBCC SHRINKFILE (AdventureWorks2016_Log,1)
Go

— Obtendo informações sobre o Log —
DBCC LOGINFO(AdventureWorks2016)
Go

— Modificando o arquivo de Log —
ALTER DATABASE AdventureWorks2016
MODIFY FILE
(NAME = AdventureWorks2016_Log,
SIZE = 4MB)
Go

— Encolhendo o Transaction Log —
DBCC LOGINFO(AdventureWorks2016)

— Abrir nova query —
BACKUP DATABASE AdventureWorks2016
TO DISK = ‘d:\backup.bak’
Go

— Abrir nova query —
USE AdventureWorks2016
GO

— Forçando o Truncate do Log —
BACKUP LOG AdventureWorks2016
TO DISK = ‘bkplog.trn’
Go

— Obtedo informações do arquivo de log —
DBCC LOGINFO(AdventureWorks2016)
CHECKPOINT
SELECT name,
log_reuse_wait_desc
FROM sys.databases
WHERE name = ‘AdventureWorks2016’
Go

— Ajustando o tamanho do TLog
USE AdventureWorks2016
GO

DBCC SHRINKFILE (AdventureWorks2016_Log,1)
GO

DBCC LOGINFO(AdventureWorks2016)
Go

— Short Script 7 – File Growth —

— Habilitando Trace Flags para evidênciar mudanças no Log —
DBCC TRACEON (3004, 3605, -1);
Go

— Limpar o log do SQL Server —
sp_cycle_errorlog
Go

— Criar uma nova Base de Dados —
CREATE DATABASE TransactionLog
ON PRIMARY
(NAME = ‘TransactionLog_data’,
FILENAME = ‘D:\MSSQLSERVER\DATA\TransactionLog_data.mdf’,
SIZE = 10240MB)
LOG ON
(Name = ‘TransactionLog_Log’,
FILENAME = ‘D:\MSSQLSERVER\DATA\TransactionLog_log.ldf’,
SIZE = 1024MB,
FILEGROWTH = 1024MB)
GO

— Identificar o tempo decorrido para processamento relacionado somente ao Log —
xp_readerrorlog
Go

— Desabilitando Trace Flags —
DBCC TRACEOFF (3004, 3605, -1);
Go

— Short Script 8 – Open Transaction in Tracking —

— Identificando as Transações abertas e seu respectivo consumo do Transaction Log —
SELECT
[s_tst].[session_id],
[s_es].[login_name] AS [Login Name],
DB_NAME (s_tdt.database_id) AS [Database],
[s_tdt].[database_transaction_begin_time] AS [Begin Time],
[s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],
[s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],
[s_est].text AS [Last T-SQL Text],
[s_eqp].[query_plan] AS [Last Plan]
FROM sys.dm_tran_database_transactions [s_tdt] Inner JOIN sys.dm_tran_session_transactions [s_tst]
ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
Inner JOIN sys.[dm_exec_sessions] [s_es]
ON [s_es].[session_id] = [s_tst].[session_id]
Inner JOIN sys.dm_exec_connections [s_ec]
ON [s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN sys.dm_exec_requests [s_er]
ON [s_er].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
OUTER APPLY sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
ORDER BY [Begin Time] ASC;
Go

É isso ai, missão cumprida! Mais uma relação de short scripts acaba de ser compartilhada, mesmo sendo denominados short entre aspas “pequenos”, posso garantir que todos estes exemplos são de grande importância, apresentam um valor e conhecimento do mais alto nível.

Quero agradecer ao amigo Luiz Mercante que colaborou com a criação e uso destes scripts em algumas palestras realizadas nos últimos anos no evento SQL Saturday.

Chegamos ao final de mais um Short Scripts, espero que este material possa lhe ajudar, ilustrando o uso de alguns recursos e funcionalidades do Microsoft SQL Server.

Acredito que você tenha observado que estes códigos são conhecidos em meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server publicados no decorrer dos últimos anos.

Boa parte deste material é fruto de um trabalho dedicado exclusivamente a colaboração com a comunidade, visando sempre encontrar algo que possa ser a solução de um determinado problema, bem como, a demonstração de como se pode fazer uso de um determinado recurso.

Links

Caso você queira acessar os últimos posts desta sessão, não perca tempo acesse os links listados abaixo:

https://pedrogalvaojunior.wordpress.com/2017/12/09/short-scripts-dezembro-2017/

https://pedrogalvaojunior.wordpress.com/2017/09/16/short-scripts-setembro-2017/

https://pedrogalvaojunior.wordpress.com/2017/06/08/short-scripts-junho-2017/

https://pedrogalvaojunior.wordpress.com/2017/03/31/short-scripts-marco-2017/

Agradecimento

Obrigado mais uma vez por sua visita, fico honrado com sua ilustre presença ao meu blog, desejo e espero que você possa ter encontrado algo que lhe ajudou.

Volte sempre, nos encontraremos mais uma vez na sessão Short Scripts no post do mês de maio de 2018.

Sucesso….

#19 – Para que serve

Salve galera, boa tarde.

Feliz Ano Novo, Feliz 2018, o tempo passou e hoje dia 02/01 nos encontramos no primeiro dia útil para grande maioria da população mundial, mas que dureza pensar que temos mais 365 dias pela frente para superarmos, por outro lado que bom pensar assim.

Neste primeiro post de 2018 não vou trazer nenhuma novidade relacionada ao Microsoft SQL Server ou banco de dados, mas sim compartilhar como faço em alguns momentos, conceitos já conhecidos da grande maioria.

Hoje quero trazer para vocês um dos assuntos mais discutidos quando estamos trabalhando com nossos servidores de banco de dados, estou me referindo ao período de processamento do comando select dentro de um bloco de transação conhecido como Ciclo de Vida de Query através do comando Select.

Sendo assim, chegou a hora de conhecer um pouco mais sobre o primeiro post de 2018, post de número 19 da sessão Para que serve. Então seja bem vindo ao #19 – Para que serve – Ciclo de Vida de Query através do comando Select.

Introdução

Um dos comandos mais utilizados em qualquer Sistema Gerenciador de Banco de Dados ou propriamente um Banco de Dados é o comando Select, sendo este responsável em recuperar linhas do banco de dados e permite a seleção de uma ou várias linhas ou colunas de uma ou várias tabelas, no Microsoft SQL Server isso não é diferente.

Basicamente ao se executar um comando Select podemos estar trabalhando com uma simples query ou conjunto de querys que podem formar uma ou mais transações, é com base neste cenário que o comando Select composto por sua conjunto de argumentos e opções permite estabelecer um ciclo de vida dedicado exclusivamente ao seu período de compilação, execução e encerramento.

Desta forma, algumas perguntas podem surgir decorrentes do seu processo de processamento, dentre as quais destaco:

Quais são as etapas para o processamento de um select?
Onde inicia e onde termina cada processo?

De uma maneira bastante simples e direta vou tentar responder estas questões, iniciando pela organização da estrutura de componentes utilizadas pelo comando Select, conhecidos como:

Relation Engine;
Storage Engine; e
Buffer Pool.

Relational Engine é responsável pelos processos de Query Optmizer, Query Executor e Parse entre outros, avaliando toda a parte algébrica, sintaxe e plano de execução da Query.
Storage Engine é o cara do I/O, responsável pelo gerenciamento e requisições de disco, alocações, Access Methods Code, Buffer Manager e Transaction MGR.
Buffer Pool tem vários papeis, mas, um dos mais importantes é o gerenciamento de memória para o plano de execução e alocação de páginas no data cache.

A Figura 1 apresentada abaixo ilustra um modelo básico da estrutura de relação entre estes componentes:

Figura 1 – Estrutura dos componentes utilizados pelo comando select.

Logicamente, dentro de cada componente podemos encontrar diversos subcomponentes que formam sua estrutura, responsáveis por diversas ações e procedimentos, formando um ecossistema único para cada elemento, dentre eles destaco o Query Optimizer com suas diversas fases de otimização para gerar o plano de execução mais assertivo.

O Ciclo (Select)

O primeiro passo é estabelecer a conexão entre aplicação (ERP, CRM, Web, etc…) e o SQL Server. Para isso, é utilizado um protocolo chamado Network Interface (SNI). No fundo o SNI utiliza um outro protocolo, na verdade, podem existir vários protocolos e o mais conhecido é o famoso TCP/IP.

A Figura 2 abaixo ilustra o inicio do ciclo de vida do comando select através do acesso feito por uma aplicação:

Figura 2 – Representação do inicio do ciclo de vida do comando select.

Ao realizar a conexão através da comanda e do protocolo (TCP/IP), os pacotes TDS (Tabular Data Stream Endpoints) são encaminhados ao Protocolo Layer, que tem como papel “reconhecer e interpretar” o pacote e validar a informação, assim como sua origem (client). Após isso o conteúdo (SQL Command) do pacote é enviado ao Command Parse.

A Figura 3 apresenta o comportamento do Command Parse após o processo de reconhecimento e interpretação do pacote contendo o comando select ser realizado:

Figura 3 – Comportamento do Command Parse após o processo de reconhecimento dos pacotes.

Neste cenário o CMD Parser vai fazer o seu trabalho, primeiro validando o T-SQL, checando sintaxe, nomes de objetos, parâmetros, palavras chaves. A segunda parte é procurar no Buffer Pool se já existe um plano de execução compatível para está query, se sim, ele recupera este plano e executa (Query Executor), caso contrário, passa o result da análise (Query Tree) para o Query Optmizer que é o responsável por gerar o Execution Plan (plano de execução) que será usado na execução (próxima etapa) do ciclo.

Ao receber as instruções o Query Optimizer, identifica a query realizando diversas etapas (fases 0,1,2) de otimização, afim de encontrar o plano mais eficiente, com base no “cost-based” (I/O, CPU). Nesta etapa as estatísticas são utilizadas servindo como Input de informação para tomada de decisão do Query Optimizer. Após o termino desta etapa, o plano de execução está pronto, passando o bastão para o Query Executor

O Query Executor é quem executa a Query, na verdade ele executa o plano de execução, colocando os operadores para trabalhar. É neste ponto também que ocorre a interação com a Storage Engine via interface Access Methods (OLE DB).

Seguindo em frente, Access Methods passa a solicitação para o Buffer Manager recuperar a página de dados, se a página especifica estiver em memória, o Buffer Pool solicita ao Data Cache que recupere a pagina, e retorna ao Access Methods (leituras logicas). Ao contrário, os dados são recuperados do disco (leituras físicas), colocados em cache e devolvendo o controle para o Access Methods.

De posse dos dados, o Access Methods devolve a informação para o Relational Engine que será enviada ao Client que a solicitou, assim o resultando do comando select é apresentado na tela da aplicação exibindo assim os dados solicitados pelo usuário. Desta maneira, nosso ciclo ou melhor o ciclo de vida do comando select esta concluído, conforme a Figura 4 apresenta abaixo:

Figura 4 – Ciclo de vida do comando select concluído e dados apresentados para o usuário.

Com isso chegamos ao final do primeiro post de 2018 e post de número 19 da sessão Para que serve.

Referências

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql

https://technet.microsoft.com/en-us/library/ms189559(v=sql.90).aspx

http://www.sqlservergeeks.com/sql-server-architecture-part-2-the-relational-engine/

https://blogs.msdn.microsoft.com/tcaserta/2016/01/04/sql-server-fundamentos-storage-engine-parte-i/

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/2017/12/15/18-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2017/11/24/17-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2017/10/01/16-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2017/06/28/15-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2017/04/30/14-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2017/03/25/13-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2017/01/23/12-para-que-serve/

Conclusão

Como sempre a Microsoft e toda sua equipe nos surpreende com sua capacidade de trabalho, fortalecendo cada vez mais o Microsoft SQL Server não somente com um SGBD ou ferramenta de banco de dados, mas sim um ambiente completo para qualquer tipo de análise, desenvolvimento e administração que esteja relacionada com dados.

Este é o fantástico Microsoft SQL Server, surpreendente em todos os sentidos…

Agradecimentos

Mais uma vez obrigado por sua ilustre visita, sinto-me honrado com sua presença, espero que este conteúdo possa lhe ajudar e ser útil em suas atividades profissionais e acadêmicas.

Um forte abraço, até o próximo post da sessão Para que serve…..

Valeu.

Compartilhe isso:

Compartilhe isso:

Introdução

Table Hint – SERIALIZABLE

Referências

Links

Conclusão

Agradecimentos

Compartilhe isso:

Introdução

Table Hint – UpdLock

Table Hint – TabLock

Atomicidade;

Consistência;

Isolamento; e

Durabilidade.

Referências

Links

Conclusão

Agradecimentos

Compartilhe isso:

Referências

Links

Agradecimentos

Compartilhe isso:

Introdução

Tabelas e Índices

Nosso ambiente

Criando o ambiente

<a href="https://polldaddy.com/p/10145195" target="_blank" rel="noopener noreferrer">Responda nossa pesquisa</a>

Referências

Posts Anteriores

Conclusão

Agradecimentos

Compartilhe isso:

Compartilhe isso:

Referências

Links

Agradecimentos

Compartilhe isso:

Compartilhe isso:

Hotfixes que estão incluídos neste pacote de atualização cumulativa

Compartilhe isso:

Hotfixes que estão incluídos neste pacote de atualização cumulativa

Compartilhe isso:

Compartilhe isso:

Script Challenge 13

A resposta

Sua Participação

Referências

Agradecimentos

Compartilhe isso:

O post de hoje

Short Scripts

Links

Compartilhe isso:

Introdução

Links

Conclusão

Agradecimentos

Compartilhe isso: