#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.

Com isso, e sem mais delongas, chegamos ao final. Foi muito legal poder mostrar um pouco mais sobre as table hints existentes no Microsoft SQL Server, claro de que um pouco de trabalho este post, mas já estou acostume, mesmo assim sempre vale a pena poder compartilhar um o 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.

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.

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 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.

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.

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 TUPD (conforme já destacado anteriormente).

— 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

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.

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

Go

 

 

— 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

Go

 

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…’

Go

 

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 —

Go

 

 

— 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.

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 maio de 2019.

Um grande abraço e ótimo final de 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 Management Studio 17.8 liberado


Em anuncio oficial, A Microsoft apresentou em seu blog a nova versão do Microsoft SQL Server Management Studio 17.8, conhecida por muitos profissionais de tecnologia pela sigla SSMS.

O SSMS combina um amplo grupo de ferramentas gráficas com vários editores de script avançados para fornecer acesso para desenvolvedores e administradores de todos os níveis de conhecimento ao SQL Server, além disso, através desta ferramenta você vai encontrar um ambiente totalmente integrado com os mais diversos serviços e recursos existentes no Microsoft SQL Server.

Vale ressaltar que desde a versão 2016 do Microsoft SQL Server, o time de engenheiros e desenvolvedores da Microsoft, decidiram superar a dependência do Management Studio da versão do SQL Server, sendo assim, deste então independente da versão do Microsoft SQL Server instalado é possível utilizar e instalar versões superiores ou não do Management Studio, tornando-se então um novo produto independente do SQL Server.

A partir da versão 17, o Microsoft SQL Server Management Studio apresenta um novo ícone que faz referência a esta ferramenta substituindo justamente o ícone utilizado pelas versões anteriores do Management Studio específicos de cada versão do Microsoft SQL Server.

A Figura 1 abaixo apresenta este novo ícone:

Figura 1 – Novo ícone utilizado pelo Microsoft SQL Server Management Studio a partir da versão 17.

Para realizar download do Microsoft SQL Server Management Studio 17.8, utilize um dos links apresentados abaixo:

Download SQL Server Management Studio 17.8

Download SQL Server Management Studio 17.8 Upgrade Package (upgrades 17.x to 17.8)

Após a instalação desta nova versão o Microsoft SQL Server Management Studio terá sua número de versão evoluído para: 17.8 e seu release build para: 14.0.17276.0. Dentre os bugs e melhorias implementadas nesta nova versão a Microsoft destaca correções específicas para editor de querys, nova interface para scripts e suporte a propriedade AUTOGROW_ALL_FILES, entre outras inovações.

Caso queira saber mais sobre esta versão, sua lista de correções e melhorias, acesse: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017

Esta nova versão do SSMS 17.8 esta disponível para as versões do Mirosoft SQL Server versão 2008 até 2017, é compatível com as versões 64 bits do Windows 7, Windows 8/8.1, Windows 10, Windows Server 2008 R2, Windows Server 2012, Windows Server 2012 R2 e Windows Server 2016 e também suporta o SQL Analysis Service PaaS.

Fontes e Direitos Autorais: Microsoft – Docs SQL Tools SQL Server Management Studio (SSMS) – 21/06/2018.

Material de Apoio – Abril 2018


Muito, mas, muito bom dia!

São exatamente 08:00 horas da manhã e você já esta dando uma passadinha por aqui, que legal, obrigado por mais esta visita.

E ai tudo bem com você? Espero que sim. Estou aqui mais uma vez procurando colaborar e compartilhar com a comunidade técnica em mais um post da sessão Material de Apoio dedicado exclusivamente ao meu blog.

Espero que você esteja gostando do conteúdo aqui disponibilizado, como também, possa me ajudar a cada vez mais melhorar ainda.

O post de hoje

Seja bem-vindo a mais um post da sessão Material de Apoio, sendo o segundo do ano de 2018 e de número 156 no total desta sessão.

Para aqueles que já acompanham o meu blog a um certo tempo, os posts dedicados a sessão Material de Apoio, possuem o objetivo de compartilhar o conhecimento de recursos, funcionalidades e procedimentos que podemos realizar no Microsoft SQL Server.

Hoje não será diferente, estou trazendo alguns dos mais recentes scripts  catalogados nos últimos meses, que atualmente estão compondo a minha galeria de códigos formada ao longo dos anos de trabalho como DBA e atualmente como Professor de Banco de Dados.

Neste post você vai encontrar arquivos relacionados com os seguintes temas:

  • @@TranCount;
  • Claúsula Where;
  • Comando Declare;
  • Comando Kill;
  • Comando Order By;
  • Comando Rollback;
  • Comando While;
  • Conversão implícita de dados;
  • Cursor;
  • Database Level Events;
  • Dynamic Management Function sys.dm_exec_query_plan;
  • Dynamic Management Function sys.dm_exec_sql_text;
  • Dynamic Management View sys.dm_exec_query_stats;
  • Dynamic Management View sys.dm_os_tasks;
  • Dynamic Management View sys.dm_os_threads;
  • Error Code 3609;
  • Função Cast;
  • Função EventData();
  • Função Raiserror();
  • Função Top();
  • Loop de processamento infinito;
  • Opção Recompile;
  • Operador Cross Apply;
  • OS Threads;
  • Processos em execução;
  • Sessions;
  • Set RowCount;
  • Stored Procedure Exec;
  • System Table sys.sysprocesses;
  • Tratamento de Erros;
  • Trigger DDL; e
  • Variáveis.

Espero que este conteúdo possa lhe ajudar em seus atividades profissionais e acadêmicas. Por questões de compatibilidade com a plataforma WordPress.com, todos os arquivos estão renomeados com a extensão .doc ao final do seu respectivo nome, sendo assim, após o download torna-se necessário remover esta extensão, mantendo somente a extensão padrão .sql.

Material de Apoio

A seguir apresento a relação de arquivos  selecionados:

1 – Material de Apoio – Abril 2018 – Encerramento processos que apresentam várias horas ou longo tempo de execução.sql

2 – Material de Apoio – Abril 2018 – Trigger DDL DATABASE LEVEL EVENTS – Bloqueando a criação de tabelas com um determinado nome.sql

3 – Material de Apoio – Abril 2018 – Identificando a SessionID e suas respectivas OS Threads.sql

4 – Material de Apoio – Abril 2018 – Relação de Querys – Apresentam conversão implícita de dados.sql

5 – Material de Apoio – Abril 2018 – Criando um loop infinito utilizando SET ROWCOUNT com Variable Table.sql

6 – Material de Apoio – Abril 2018 – Utilizando Rollback Transaction dentro de Trigger com tratamento de erro 3609.sql

7 – Material de Apoio – Abril 2018 – Utilizando Rollback Transaction dentro de Trigger decrementando o valor de @@Trancount.sql

Fique a vontade para copiar, editar, compartilhar e distribuir estes arquivos com seus contatos, aproveite se possível deixe seu comentário, críticas, sugestões e observações.

Nota: Todos os arquivos disponibilizados foram obtidos ou criados com autorização de seus autores, sendo estes, passíveis de direitos autorais.

Links

Caso você queira acessar os posts anteriores da sessão, não perca tempo utilize os links listados abaixo:

https://pedrogalvaojunior.wordpress.com/2018/02/13/material-de-apoio-fevereiro-2018/

https://pedrogalvaojunior.wordpress.com/2017/11/04/material-de-apoio-novembro-2017/

https://pedrogalvaojunior.wordpress.com/2017/08/08/material-de-apoio-agosto-2017/

https://pedrogalvaojunior.wordpress.com/2017/05/09/material-de-apoio-maio-2017/

Agradecimento

Quero agradecer imensamente a sua visita, sinto-me honrado e orgulhoso de contar com a sua presença.

Não deixe de acessar os outros posts das demais sessões, o próximo post desta sessão será publicado no mês de junho, até lá continue curtindo sua vida e compartilhando suas experiência.

Um forte abraço, muita saúde, sucesso e vamos em frente…

Material de Apoio – Fevereiro 2018


Boa tarde.

Tudo bem? E ai esta curtindo o carnaval?

Espero que sim, eu estou aqui mais uma vez procurando colaborar e compartilhar com a comunidade técnica em mais um post dedicado exclusivamente ao meu blog. Fico feliz em encontrar você aqui fazendo mais uma visita ao meu blog, neste feriado, espero que tenha gostado do conteúdo aqui encontrado.

O post de hoje

 

Seja bem-vindo a mais um post da sessão Material de Apoio, sendo o primeiro do ano de 2018 e de número 155 no total desta sessão.

Para aqueles que já acompanham o meu blog a um certo tempo, os posts dedicados a sessão Material de Apoio, possuem o objetivo de compartilhar o conhecimento de recursos, funcionalidades e procedimentos que podemos realizar no Microsoft SQL Server.

Hoje não será diferente, estou trazendo alguns dos mais recentes scripts  catalogados nos últimos meses, que atualmente estão compondo a minha galeria de códigos formada ao longo dos anos de trabalho como DBA e atualmente como Professor de Banco de Dados.

Neste post você vai encontrar arquivos relacionados com os seguintes temas:

  • Armazenamento de arquivos;
  • Cláusula Values;
  • Comando Select;
    Comando Top;
  • Endereço de e-mails;
  • Excel;
  • Grant All Permissions;
  • Impactos na Ordenação de dados;
  • Índices;
  • Ordenação de Colunas;
  • Passwords;
  • Performance;
  • Random Character;
  • Tabelas;
  • User Defined Function; e
  • Validação de dados.

Espero que este conteúdo possa lhe ajudar em seus atividades profissionais e acadêmicas. Por questões de compatibilidade com a plataforma WordPress.com, todos os arquivos estão renomeados com a extensão .doc ao final do seu respectivo nome, sendo assim, após o download torna-se necessário remover esta extensão, mantendo somente a extensão padrão .sql.

Material de Apoio

A seguir apresento a relação de arquivos  selecionados:

1 – Material de Apoio – Fevereiro 2018 – Realizando a validação de endereços de e-mail.sql

2 – Material de Apoio – Fevereiro 2018 – Impacto na Ordenação de Colunas em Índice – Comandos DML.sql

3 – Material de Apoio – Fevereiro 2018 – Generating A Password in SQL Server with T-SQL from Random Characters.sql

4 – Material de Apoio – Fevereiro 2018 – Simple SQL Server Function to Generate Random 8 Character Password.sql

5 – Material de Apoio – Fevereiro 2018 – Comando Select em conjunto com comando Top e cláusula Values.sql

6 – Material de Apoio – Fevereiro 2018 – Atribuindo Grant All para todas as tabelas.sql

7 – Material de Apoio – Fevereiro 2018 – Como armazenar arquivos do Excel diretamente no SQL Server.sql

Fique a vontade para copiar, editar, compartilhar e distribuir estes arquivos com seus contatos, aproveite se possível deixe seu comentário, críticas, sugestões e observações.

Nota: Todos os arquivos disponibilizados foram obtidos ou criados com autorização de seus autores, sendo estes, passíveis de direitos autorais.

Links

Caso você queira acessar os posts anteriores da sessão, não perca tempo utilize os links listados abaixo:

https://pedrogalvaojunior.wordpress.com/2017/11/04/material-de-apoio-novembro-2017/

https://pedrogalvaojunior.wordpress.com/2017/08/08/material-de-apoio-agosto-2017/

https://pedrogalvaojunior.wordpress.com/2017/05/09/material-de-apoio-maio-2017/

Agradecimento

Quero agradecer imensamente a sua visita, ainda mais hoje neste feriadão prolongado de carnaval.

Não deixe de acessar os outros posts das demais sessões, o próximo post desta sessão será publicado no mês de abril, até lá continue curtindo sua vida e compartilhando suas experiência.

Até a próxima, 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:

  1. Quais são as etapas para o processamento de um select? 
  2. 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.
  1. 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.
  2. 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.
  3. 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/

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.

#13 – Para que serve


Muito boa noite galera, tudo bem?

Noite de sábado, temperatura agradável, galera curtindo uma pizza, balada entre outras coisas e eu estou aqui para compartilhar com você mais um post da minha sessão Para que serve, hoje o post de número 13. Você esta pensando, post de número 13 não é nada muito “ospicioso” como diária um personagem de novela (kkkkk).

Que nada vamos em frente não se preocupe com este número, tenho a certeza que este post será muito legal e apresentará informações de alto astral relacionada ao novo Microsoft SQL Server 2016.

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

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

No post de hoje, vou a destacar uma das mais aguardados melhorias relacionadas ao SQL Server, estou me referindo a capacidade de consultor os histogramas de estatísticas de processamento de forma programada, isso mesmo, agora a partir da nova atualização cumulativa do SQL Server 2016 SP1, conhecida como Cumulative Update 2, temos duas novas DMF – Dynamic Management Function – Função de Gerenciamento Dinâmico que nos permitem de forma direta através do uso do comando Select obter informações sobre os histogramas e dados estatísticos.

Vou fazer um pequeno suspense, não vou revelar o nome de ambas as DMFs, somente no decorrer deste post você vai conhece-las.

Muito bem, após deixar este gostinho de quero mais, chegou a hora de conhecer estas novas funcionalidades e ver como podemos aplicar isso no nosso ambiente.

Como aqui o #13 – Para que serve – Uma nova e mais fácil maneira de obter informações sobre o histograma de estatísticas no Microsoft SQL Server 2016 SP1 –

Introdução

Quando se referimos a estatísticas de bancos de dados, estatísticas de processamento ou estatísticas de consumo de operadores do plano de execução, estamos na verdade se referindo ao bom e velho conceito de estatísticas, o qual devemos voltar no tempo para entender melhor se realmente quisermos saber a importância deste assunto, para este post este não é o foco, na verdade o que eu quero é mostrar que a partir da nova atualização cumulativa aplicada para o Service Pack 1 do SQL Server 2016 os times de engenheiros e desenvolvedores do SQL Server introduziram no produto duas novas DMF denominadas sys.dm_db_stats_histogram e sys.dm_db_stats_properties, onde através do uso destas novas DMFs podemos obter todas as informações relacionadas as estatísticas de processamento de nossas querys e principalmente o histograma de maneira mais rápida, fácil e principalmente legível, pois particularmente falando ler o histograma através do comando DBCC Show_Statistics não era nada fácil(kkkkk).

Vamos conhecer um pouco mais sobre cada DMF para entender melhor seu funcionamento:

sys.dm_db_stats_histogram: Retorna o histograma de estatísticas para o objeto de banco de dados especificado (tabela ou exibição indexada) no atual SQL Server banco de dados. Semelhante ao DBCC SHOW_STATISTICS WITH HISTOGRAM.

Ao executar esta nova DMF o Microsoft SQL Server 2016 apresentará uma tabela de resultado contendo o seguinte conjunto de colunas, conforme a Tabela 1 ilustra:

Nome da coluna

Column name
Tipo de dados Description
object_id int ID do objeto (tabela ou exibição indexada) para o qual as propriedades do objeto de estatísticas serão retornadas.
stats_id int ID do objeto de estatísticas. É exclusiva na tabela ou exibição indexada. Para obter mais informações, veja sys.stats.
step_number int O número da etapa do histograma.
range_high_key sql_variant Valor da coluna associada superior de uma etapa do histograma. O valor da coluna também será denominado um valor de chave.
range_rows real Número estimado de linhas cujo valor de coluna fica dentro de uma etapa do histograma, excluindo-se o limite superior.
equal_rows real Número estimado de linhas cujo valor de coluna é igual ao limite superior da etapa do histograma.
distict_range_rows bigint Número estimado de linhas com um valor de coluna distinto dentro de uma etapa do histograma, excluindo-se o limite superior.
average_range_rows real Número médio de linhas com valores de colunas duplicados em uma etapa de histograma, exceto o limite superior (RANGE_ROWS / DISTINCT_RANGE_ROWS para DISTINCT_RANGE_ROWS > 0).

sys.dm_db_stats_properties: Retorna propriedades de estatísticas para o objeto de banco de dados especificado (tabela ou exibição indexada) no banco de dados do SQL Server atual. Para tabelas particionadas, consulte a DMF sys.dm_db_incremental_stats_properties.

Ao executar esta nova DMF o Microsoft SQL Server 2016 apresentará uma tabela de resultado contendo o seguinte conjunto de colunas, conforme a Tabela 2 ilustra:

Nome da coluna Tipo de dados Description
object_id int ID do objeto (tabela ou exibição indexada) para o qual as propriedades do objeto de estatísticas serão retornadas.
stats_id int ID do objeto de estatísticas. É exclusiva na tabela ou exibição indexada. Para obter mais informações, veja sys.stats.
last_updated datetime2 Data e hora da última atualização do objeto de estatísticas.
rows bigint O número total de linhas da tabela ou exibição indexada na última atualização das estatísticas. Se as estatísticas forem filtradas ou corresponderem a um índice filtrado, o número de linhas talvez seja menor do que o número de linhas na tabela.
rows_sampled bigint O número total de linhas amostradas para cálculos de estatísticas.
etapas int O número de etapas no histograma. Para obter mais informações, veja DBCC SHOW_STATISTICS.
unfiltered_rows bigint O número total de linhas da tabela antes da aplicação da expressão de filtro (para estatísticas filtradas). Se as estatísticas não forem filtradas, unfiltered_rows será igual ao valor retornado na coluna de linhas.
modification_counter bigint Número total de modificações da coluna de estatísticas principal (a coluna em que o histograma é criado) desde que as últimas estatísticas de tempo foram atualizadas.

Essa coluna não mantém informações para tabelas com otimização de memória.

Agora que o segredo foi revelado, podemos começar a pensar na maneira que estas novas DMFs podem ser utilizadas, para tal vamos fazer uso do banco de dados analítico: AdventureworksDW2016CTP3 disponível para download através do link: http://www.microsoft.com/en-us/download/details.aspx?id=49502

Utilizando as novas DMFs

Seguindo em frente vamos começar nossa prática, para tal a primeira coisa a fazer é executar o bloco de código 1 declarado abaixo, antes clique no botão Include Actual Execution Plan em seu Management Studio, pois vamos realizar uma análise após a execução.

— Bloco de Código 1 —

Figura 1 – Instrução select declarada para o bloco de código 1.

Após a execução deste bloco de código obtemos o seguinte conjunto de dados relacionados ao operador Clustered Index Scan, conforme a Figura 2 apresentada abaixo:

Figura 2 – Dados relacionadas ao operador Clustered Index Scan.

Note que estou destacando na figura os dados referentes aos seguintes elementos:

  • Number of Rows Read;
  • Actual Number of Rows;
  • Estimated Number of Rows; e
  • Estimated Number of Rows to be Read.

Você pode estar se perguntando, o porque o Junior Galvão acabou destacados estes valores na Figura 2? A resposta é muito simples, uma das maneiras para tentar entender o comportamento do SQL Server no processamento de seus operadores e procurar ter uma ideia de estatísticas de processamento é justamente através da leitura e entendimento destes quatro conjunto de dados, o que posso dizer que não é a melhor forma para se encontrar informações sobre processamento e estatísticas.

Agora imagine que todas as vezes que você desejar obter informações sobre as estatísticas de processamento e como elas estão armazenadas e seus status, pois bem, é justamente neste ponto que agora no novo SQL Server 2016 SP1 CU 2 você terá facilmente a capacidade de fazer isso acontecer, para tal vamos executar o bloco de código 2 fazendo uso da nova DMF, sys.dm_db_status_histogram.

— Bloco de Código 2 —

Figura 3 – Bloco de código 2.

Observe que estamos fazendo uso da nova DMF sys.dm_db_status_histogram e neste momento nosso Management Studio deverá ter retornado um conjunto de linhas conforme a Figura 4 abaixo ilustra:

Figura 4 – Conjunto de dados estatísticas referentes ao processamento do bloco de código 2.

Ao analisarmos a Figura 4 podemos notar facilmente o conjunto de linhas de retornado contendo todas as informações relacionadas ao histograma da estatísticas de número 2 para a tabela [dbo].[FactResellerSales]. Tenho a certeza que você tão surpreso quanto eu quando executei pela primeira vez este mesmo bloco de código, realmente é assustador a facilidade que temos agora em entender o histograma.

Sensacional, mas como o SQL Server consegui apresentar estes dados desta maneira? Como de costume a resposta é simples, através da capacidade de utilizar em tempo de execução uma Table Valued Function denominada DM_DB_STATS_HISTOGRAM, ou seja, uma função que armazena valores em uma determinada tabela utilizada especificamente para esta nova DMF, a comprovação disso esta na Figura 5 que ilustra o plano de execução utilizado para o processamento do bloco de código 2.

Figura 5 – Plano de execução gerado para o processamento do bloco de código 2.

Continuando nossa jornada, o próximo passo é fazer uso da outra DMF, no caso a sys.dm_db_stats_properties, onde a qual vamos nos permitir obter o mesmo conjunto de valores referente ao cabeçalho da estatística o mesmo realizado através do comando DBCC SHOW_STATISTICS com a opção WITH STATS_HEADER.

Vamos então executar o bloco de código 3 apresentado a seguir:

Figura 7 – Bloco de código 3.

E qual será o resultado obtido após o processamento do bloco de código 3? A resposta é apresentada na Figura 7 a seguir:

Figura 7 – Resultado do processamento do bloco de código 3.

Show de bola, temos exatamente o mesmo conjunto de dados retornados pela DMF sys.dm_db_stats_properties da mesma forma que teríamos se estivéssemos utilizando do bom e velho DBCC SHOW_STATISTICS, não é realmente fantástico, só de imaginar a capacidade de possibilidades que teremos de utilizar estes dados a partir de agora realmente é algo surreal.

Da mesma forma que o SQL Server 2016 SP1 CU2 utiliza uma Table Valued Function para armazenar e apresentar os consumidos e coletados pelo processamento da sys.dm_db_status_histogram, também é utilizada uma outra Table Valued Function para o processamento da sys.dm_db_stats_properties denominada DM_DB_STATS_PROPERTIES.

Para finalizar nossa brincadeira e mostrar como estas novas funcionalidades podem nos ajudar, vamos utilizar o bloco de código 4 para através dele conseguir especificar uma determinada range_key existe em nossas estatísticas. Poxa vida especificar em um comando select qual determinada faixa de valores estatísticas nós queremos obter dados realmente é acima do que estávamos pensando, por incrível que isso possa parecer, é totalmente possível de ser feito a partir de agora.

— Bloco de Código 4 —

Figura 8 – Retorno de dados referentes ao filtro da faixa de valores.

Putz, que coisa louco, meu deus, temos com base no bloco de código 4 a comprovação que podemos através do uso de outras DMFs inline retornado dados estatísticos com base em filtros ou predicados declarados na cláusula where existente na linha 26 onde, a coluna sh.range_high_key é justamente uma coluna pertencente a nova DMF sys.dm_db_stats_histogram.

Que loucura isso, fora de série esta nova capacidade do SQL Server, fantástico, inimaginável, fora do comum o que o time de engenheiros do SQL Server fizeram desta vez, show.

Referências

https://msdn.microsoft.com/library/mt794645.aspx

https://blogs.msdn.microsoft.com/sql_server_team/easy-way-to-get-statistics-histogram-programmatically/

https://support.microsoft.com/en-us/help/4013106/cumulative-update-2-for-sql-server-2016-sp1

http://msdn.microsoft.com/library/jj553546.aspx

http://msdn.microsoft.com/library/ms174384.aspx

https://msdn.microsoft.com/pt-br/library/mt761751.aspx

https://msdn.microsoft.com/pt-br/library/ms177623.aspx

Links

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

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

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

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

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

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

Conclusão

A maneira como nossos dados estão constantemente sendo processados é algo que a cada dia um DBA ou profissional de banco de dados se pergunta. Saber em qual momento uma determinada query, transação ou simplesmente um comando select pode ocasionar algo tipo de impacto em nosso ambiente ainda é mais preocupante. Foi justamente pensando nisso que a Microsoft e seu time de profissionais que trabalham com o SQL Server buscaram responder a partir da disponibilidade das duas novas DMFs: sys.dm_db_stats_histogram e sys.dm_db_stats_properties recursos adicionados na versão 2016 SP1 e disponível também para próximas versão do SQL Server, dentre elas a SQL Server vNext.
Esta nova maneira de acessar e consultar os dados coletados e armazenados no histograma poderá ajudar em muito os profissionais de banco de dados e desenvolvedores a entender como seus estatísticas de processamento de dados estão sendo afetadas com base nos processos de manipulação.
Neste post você pode mais uma vez observar que o Microsoft SQL Server esta em constante evolução, um dos produtos mais prestigiados pela Microsoft, buscando sempre trazer melhorais e inovações, algo de extrema importância para qualquer profissional que trabalha com esta tecnologia.

Agradecimentos

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

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

#10 – Para que serve


O louco meu, pleno feriadão e você esta passando por aqui no meu blog……

Que legal, sensacional, fico honrado com a sua ilustre visita, seja bem – vindo mais uma vez ao meu blog, espero que você consiga encontrar o que esta procurando ou algo que possa lhe agradar.

Este é mais um post da sessão Para que serve, lançada no início de 2016 e que esta chegando ao post de número 10, isso mesmo estamos no décimo post dedicado a esta sessão que aos poucos esta conseguindo se tornar uma referência de conhecimento diferenciado no meu Blog.

É isso ai, após esta tradicional saudação, chegou a hora de falar sobre o #10 – Para que serve de hoje, tenho a certeza que você vai gostar….


Introdução

Como você já deve ter percebido os posts relacionados a esta sessão tem o objetivo de apresentar e em alguns casos demonstrar como exemplos de código, aplicativos, utilitários, entre outros elementos envolvidos a banco de dados ou gerenciadores de bancos de dados dentro eles o Microsoft SQL Server podem ser utilizados para se obter uma possível solução de um problema, como em outros casos orientar na sua forma de utilização.

Para o post de hoje vou destacar um script que utilizei recentemente e posso dizer que foi de grande ajuda, mas antes de apresentar este recurso vou destacar um pouco sobre alguns elementos relacionados a ele, dentre os quais destaco File Growth.

File Growth

E ai você já ouviu falar file growth, ou simplesmente crescimento de arquivo de dados ou log? Se você é um administrador de banco de dados, ou um profissional que já trabalha a algum tempo com o banco de dados, tenho a certeza que já deve ter ouvido falar sobre a importância de se saber como esta configurado o fator de crescimento de um banco de dados e seu arquivos de transações.

Trata-se de uma configuração que pode ser aplicada durante a criação de um banco de dados ou posteriormente, sua importância esta totalmente relacionada ao espaço de armazenamento de dados durante sua utilização, o que poderá impactar na capacidade física de uma unidade de disco em gerenciar o quanto estes arquivos podem consumir e alocar espaço em disco no decorrer do seu tempo de vida.

Ao definir a forma de crescimento ou até mesmo o quanto este arquivo poderá ou não crescer de forma ilimitado o Microsoft SQL Server vai trabalhar no processo de alocação, escrita e manipulação da estrutura física e lógica tanto para os arquivos de dados, como principalmente para os arquivos de log.

Justamente sendo estes os arquivos que normalmente consomem um grande espaço física das unidades de disco para catalogar todas as operações processadas em um banco de dados que devem ser registradas em sua estrutura.

Para este tipo de cenário os gerenciadores de banco de dados através de seu mecanismo de Storage Engine observam e monitoram o que esta sendo processado e armazenado dentro de cada arquivo, caso o mesmo tenho que crescer para alocar uma nova área é com base nas configurações de File Growth definidas para o respectivo arquivo que este crescimento poderá ser realizado em fatores de Kilobytes, Megabytes, Gigabytes ou até mesmo em valores de porcentagem.

#10 Para que serve – Obtendo informações sobre database filegrowth —

Agora que conhecemos um pouco que esta relacionada com este post, vamos então conhecer este script que poderá nos ajudar a obter todas as possíveis informações relacionadas ao fator de crescimento de nossos bancos de dados e suas respectivas estruturas de dados e log.

— Bloco de Código —

filegrowth

Muito bem, observe que este código é bastante simples, estamos basicamente fazendo uso das catalogs views existentes no Microsoft SQL Server desdes suas primeiras versões o que nos permite dizer que este bloco de código pode ser aplicado facilmente a partir da versão 2005 em qualquer nível de edição, além disso, o mesmo já foi testado e aprovado nas últimos duas edições 2014 e 2016.

Após executarmos o bloco de código apresentando anteriormente, o Management Studio deverá retornar um conjunto de colunas e valores similares ao apresentado na Figura 1 apresentada abaixo:

filegrowth1Figura 1 – Relação de bancos de dados e informações sobre o filegrowth.

Podemos notar a existência das colunas AutoGrowthStatus, GrowthValue e GrowthIncrement, são justamentes estas as colunas que nos permitem encontrar as informações relacionadas aos fatores de crescimento configurados para cada banco de dados armazenado em nosso servidor ou instância de bancos de dados Microsoft SQL Server.

Falando um pouco sobre estas três colunas é possível observar:

AutoGrowthStatus: Esta coluna apresenta o status da propriedade Auto Growth, sendo esta definida para informar e o arquivo deverá ou não crescer de forma automática.

GrowthValue: Apresenta que pode ser informado a partir de 0 (zero) que indica ao Microsoft SQL Server que o determinado banco de dados não deverá crescer. Os demais valores podem representar uma indicação de crescimento em tamanho fixo ou até mesmo em porcentagem.

GrowthIncrement: Mostra a forma de incremento do fator de crescimento do banco de dados, sendo orientado e calculado através do número de páginas de dados, se o valor apresentado for igual á 0 (zero) significa que este banco de dados não terá seu crescimento realizado, qualquer outro valor acima de 0 (zero) significa que este banco de dados será impactado em algum momento pelo valor definido nas configurações do crescimento do banco de dados. Vale ressaltar que este valor esta relacionado ao tamanho de 8Kb (Kilobytes) para cada página de dados.

Após esta análise posso dizer que fica mais fácil descobrir qual banco de dados poderá apresentar problemas de crescimento acima no normal ou simplesmente aquele banco de dados que necessita crescer além do estimado.

Referências

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

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

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

Links

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

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

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

Conclusão

Administrar um banco de dados não é uma tarefa das mais complicadas do mundo, mas quando se referimos em administratar um servidor de banco de dados ou conjunto de servidores de bancos de dados o cenário com certeza muda bastante.

Foi pensando neste tipo de situação que compartilhei com vocês hoje este script no #10 – Para que serve, que apresenta como podemos de maneira fácil, rápida, segura e muito prática encontrar informações relacionadas ao file growth, ou simplesmente fator de crescimento.

Considerada uma das configurações mais importantes de um qualquer banco de dados alocado em uma instância ou servidor Microsoft SQL Server.

Agradecimentos

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

Até mais.

Material de Apoio – Novembro 2016


Bom dia, bom dia, bom dia…..

Olá comunidade, amantes de bancos de dados e profissionais de tecnologia, estou chegando com mais um post dedicado a sessão Material de Apoio, sendo este o último desta sessão no ano de 2016.

Mesmo sendo o último desta sessão, este post é o primeiro com o nome layout que eu decidi adotar desde o final de Outubro para as novas publicações compartilhadas com você no meu blog, espero que vocês estejam gostando.

O post de hoje

Para aqueles que já acompanham o meu blog a um certo tempo, os posts dedicados a sessão Material de Apoio, possuem o objetivo de compartilhar o conhecimento de recursos, funcionalidades e procedimentos que podemos realizar no Microsoft SQL Server.

Hoje não será diferente, como diria naquele filme vou “compartilhagram” os meus scripts catalogados ao longo dos anos de trabalho como DBA e hoje principalmente como Professor de Banco de Dados.

Neste post você vai encontrar arquivos relacionados com os seguintes temas:

  • Funções de Conversão de dados;
  • Funções Temporáis e Lógicas;
  • Funções Textuais;
  • Funções de Ranking;
  • Páginação de Dados;
  • Objeto Sequence;
  • Windows Function First e Last Value;
  • Windows Function Lad e Lead;
  • Windows Function Percent_Rank e Cume_Dist; e
  • Windows Function Range e Row_Number.

Como de costume gosto de destacar que todos estes arquivos são fruto do trabalho realizado como DBA desde 2000, bem como, a pesquisa e coleta de informações através da internet, onde grande parte do conhecimento aqui compartilhado também é obtido através da  colaboração de seus autores, grande profissionais nacionais e internacionais como reconhecidos especialistas nas áreas de:

  • Modelagem de Dados;
  • Engenharia de Banco de Dados;
  • Engenharia de Softwares e Processos;
  • Banco de Dados;
  • Servidores;
  • Alta Disponibilidade;
  • Desenvolvimento; e
  • Migração e Contingência de Dados.

Obseravação: Toda e qualquer informação, código, exemplo e lógica utilizada nestes scripts foi aplicada em um determinado cenário, situação ou necessidade, o que em muitos casos talvez não se aplica a sua necessidade mas pode ser útil como material de consulta ou aprendizado.

Além disso, todos os arquivos estão renomeados tendo ao final do seu nome a extensão .doc acrescido, isto se torna necessário para garantir a compatibilidade com a plataforma do WordPress.com.

Vale ressaltar que após o seu download será necessário remover esta extensão mantendo a extensão .sql padrão para arquivos do script do SQL Server.

Material de Apoio

A seguir apresento a relação de scripts selecionados:

Exemplo – 1 – Funcoes-de-conversao-sql

Exemplo – 2 – Funcoes-tempororais-e-logicas-sql

Exemplo – 3 – Funcoes-textuais-sql

Exemplo – 4 – Utilizando-paging-sql

Exemplo – 5 – Utilizando-sequence-rank-function-sql

Exemplo – 6 – Windows-function-first-e-last-value-sql

Exemplo – 7 – Windows-function-lad-e-lead-sql

Exemplo – 8 – Windows-function-percent_rank-e-cume_dist-sql

Exemplo – 9 – Windows-function-range-e-row-sql

Bom pessoal, chegamos ao final de mais um Material de Apoio, espero que estes arquivos possam lhe ajudar e ilustrar como podemos fazer dos recursos aqui compartilhados.

Acredito que você tenha observado que estes códigos já são bastante conhecidos no meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server 2012, publicados anteriormente.

Links

Caso você queira acessar os posts anteriores da sessão, não perca tempo acesse os links listados abaixo:

 

Agradecimento

Mais uma vez obrigado por sua visita, um forte abraço, nos encontramos em breve.

#09 – Para que serve


Boa noite pessoal!!! Salve galera….

 

Tudo bem? Como passaram os últimos dias?

Graças a deus continuo forte na minha batalha profissional e acadêmica, como eu sempre falo para meus alunos, a vida é uma roda gigante e não podemos deixar ela parar muito menos perder a chance de curtir e aprender com cada momento.

Seguindo esta onda de oportunidades, estou retornando com mais uma post dedicado a sessão Para que serve, e conforme prometido hoje vamos finalizar o assunto de índices hipotéticos apresentado inicialmente no post: https://pedrogalvaojunior.wordpress.com/2016/08/06/07-para-que-serve/

Neste post vamos entender como o comando DBCC Autopilot pode influenciar o database engine e seus elementos execution plan e query optimizer na execução de nossas consultas, então vamos nessa galera…..


Começa agora o #09 – Para que serve – Índices Hipotéticos – Final.

 

Conforme apresentado nos posts anteriores o conceito de índices hipotéticos é uma técnica antiga, mas pouco conhecida na área de banco de dados. Para muitos profissionais da área este tipo de recurso acaba sendo algo obscuro e de pouco compreensão, por outro lado outros profissionais destacam como sendo como um recurso que permite simular a existência de um índice de forma lógica. Como em qualquer área profissional ou acadêmica sempre vai existir os dois lados da moeda e cabe a cada um de nós procurar entender, respeitar e conhecer estas opiniões.

Seguindo em frente, vamos dar continuidade em nosso estudo, fazendo uso da estrutura criada anteriormente no post: https://pedrogalvaojunior.wordpress.com/2016/09/03/08-para-que-serve/

Como você pode ter verificado, criamos o banco de dados HypotheticalDB e dentro dele os seguintes objetos apresentados na Figura 1:

hypotheticaldb-figura1

Figura 1 – Relação de objetos criados no banco de dados HypotheticalDB.

Podemos observar a existência dos três índices hipotéticos criados anteriormente para tabela ClientesCategorias, bem como, o código da tabela ClientesCategorias definido no valor: 597577167. Anote bem este código post nos próximos passos vamos fazer uso do mesmo.

Agora que já relembramos um pouco do que foi feito anteriormente em relação ao nosso ambiente, podemos continuar a fazer uso dos índices hipotéticos em nosso ambiente, onde neste momento vamos fazer com que o Microsoft SQL Server realize o uso deste recurso de forma empírica na execução da nossa query, para tal iremos utilizar o comando DBCC AutoPilot, caso você ainda não conheça ou não se lembre deste comando o mesmo foi apresentada de maneira detalhada no post: https://pedrogalvaojunior.wordpress.com/2016/08/06/07-para-que-serve/

Então mãos no teclado, chegou a hora de utilizarmos o comando DBCC AutoPilot fazendo uso do bloco de código 1, mas antes de teclar F5, clique no botão “Include Actual Execution Plan” ou tecle Ctrl+M para ativar o mesmo. Para que você possa entender o que será executado neste bloco de código e qual será o resultado apresentado é obrigatório que o plano de execução se encontre ativado.

Agora que você já realizou este procedimento, pode dar continuidade e executar o bloco de código 1 apresentado abaixo:

— Bloco de Código – Utilizando o DBCC AutoPilot forçando o uso do índice clusterizado IND_ClientesCategorias_Clusterizado_CodigoComEstatisticas –

Use HypotheticalDB

Go

 

DBCC AUTOPILOT (5, 5, 0, 0, 0) – Ativando o commando DBCC AutoPilot para iniciar uma nova sessão limpando o buffer de comando executados anteriormente —

 

DBCC AUTOPILOT (6,5,597577167,4) – Utilizando o commando DBCC AutoPilot orientado no uso exclusive de índices clusterizado —

GO

 

SET AUTOPILOT ON — Ativando a diretiva —

Go

 

Select C.Codigo,

Cc.Codigo As ‘Categoria do Cliente’,

C.Nome,

C.Endereco,

C.Estado,

C.DataUltimaCompra

From Clientes C Inner Join ClientesCategorias CC

On C.CodigoCategoria = CC.Codigo

Where C.Estado = ‘SP’

Go

 

SET AUTOPILOT OFF — Desativando a diretiva —

GO

 

Acredito que tudo deva ter ocorrido normalmente e você tenha conseguido realizar a execução do bloco de código 1 apresentado acima, neste momento o Management Studio apresentou em sua guia denominada execution plan o conjunto de operadores similares aos apresentados na Figura 2 a seguir:

hypotheticaldb-figura2

Figura 2 – Resultado da execução do bloco de código 1.

 

Note que o plano de execução nos apresenta dois operados do tipo Clustered Index Seek, respeitando a ordem de execução, temos o segundo operador com o custo de 51% de processamento apontando para o nosso índice clusterizado IND_ClientesCategorias_Clusterizado_CodigoComEstatisticas, neste momento você pode estar se perguntando.

Como o Database Engine em conjunto com o Query Optimizer e Execution Plan identificou a existência deste recurso sendo que o mesmo é algo hipotético, algo que somente existe de forma lógica, a resposta pode ser encontrada justamente na maneira que o comando DBCC AutoPilot foi declarado e posteriormente executado, onde temos o seguinte conjunto de valores passados como parâmetros de entrada:

PARÂMETRO DESCRIÇÃO VALOR DECLARADO
TypeID TypeID = 6: Usar apenas índices clusterizados 6
DbID ID do Banco de Dados 6 – HypotheticalDB
TabID Id da Tabela a ser utilizada 597577167
Indid Id do índice a ser utilizado 4

Foi através deste conjunto de valores apresentado no DBCC AutoPilot e posteriormente reconhecido e interpretados pelo database engine que o Query Optimizer e Execution Plan fizeram uso do nosso índice clusterizado.

Não é algo fantástico, realmente uma capacidade de análise e reconhecimento de recursos fora do comum, realmente o Microsoft SQL Server é um produto acima de qualquer suspeita, um software surpreendente.

Para finalizar vamos agora forçar o uso do nosso índice nonclustered IND_ClientesCategorias_NaoClusterizado_CodigoSemEstatisticas e observar qual será o comportamento e resultado apresentado pelo Management Studio após a execução do bloco de código 2 apresentando na sequência:

— Bloco de Código 2 – Forçando o uso do índice não clusterizado IND_ClientesCategorias_NaoClusterizado_CodigoSemEstatisticas –

DBCC AUTOPILOT (5, 5, 0, 0, 0)

DBCC AUTOPILOT (0,5,597577167,2)

GO

 

SET AUTOPILOT ON — Ativando a diretiva —

Go

 

Select C.Codigo,

Cc.Codigo As ‘Categoria do Cliente’,

C.Nome,

C.Endereco,

C.Estado,

C.DataUltimaCompra

From Clientes C Inner Join ClientesCategorias CC

On C.CodigoCategoria = CC.Codigo

Where C.Estado = ‘SP’

Go

 

SET AUTOPILOT OFF — Desativando a diretiva —

GO

 

Verificando o resultado apresentado na Figura 3 abaixo, tendo como base a guia Execution Plan, podemos notar a presença do operador Index Seek apontando para nosso índice não clusterizado: IND_ClientesCategorias_Clusterizado_CodigoComEstatisticas.

hypotheticaldb-figura3
Figura 3 – Resultado da execução do bloco de código 2.

Analisando com mais calma o resultado apresentado na Figura 3, fica fácil identificar a presença do operador Index Seek como já havia destacado, quando o comando DBCC AutoPilot foi executado com o seguinte conjunto de valores:

PARÂMETRO DESCRIÇÃO VALOR DECLARADO
TypeID TypeID = 0: Usar apenas índices não clusterizados 0
DbID ID do Banco de Dados 6 – HypotheticalDB
TabID Id da Tabela a ser utilizada 597577167
Indid Id do índice a ser utilizado 2

Não é algo surpreendente e simples, esse é o Microsoft SQL Server, mais uma vez dando show, mais uma vez com um grande exibição, monstrando toda sua elegância, simplicidade e capacidade de nos supreender no processamento de transações e apresentação de resultados.

Desta forma, chegamos ao final de mais post da sessão Para que serve!


 

Espero que você tenha gostado, que as informações compartilhadas aqui possam lhe ajudar a se tornar cada vez um profissional de banco de dados reconhecido e valorizado, um dos papéis na área de tecnologia mais importantes para qualquer empresa.

Reconher o verdadeiro papel de um DBA dentro de sua estrutura, é reconhecer o verdadeiro valor de seus dados e como eles podem se tornar uma infomação valiosa para sua tomada de decisão.

Caso deseje acessar os posts anteriores desta sessão, utilize os links listados abaixo:

Mais uma vez obrigado por sua visita, um forte abraço, nos encontramos em breve.

Até mais.

#08 – Para que serve


Boa noite comunidade, boa noite amantes do SQL Server e Banco de Dados…..

Hoje dia 03 de Setembro começo de noite de mais um sabádão, estamos vivendo os primeiros dias de mais um mês e se aproximando do final de 2016. Antes de começar a falar sobre o post de hoje, gostaria de compartilhar mais algumas conquistas obtidas neste últimos dias, bem como uma outra que esta por vir.

Há primeira conquista se refere aos meus alunos do Curso de Sistemas de Informação para Internet da Fatec São Roque, tive o imenso prazer em poder lecionar para esta fantástica turma nos últimos 4 semestre, desejo a vocês tudo de bom, felicidades e muitas conquistas.

A próxima ainda mais importante é o aniversário do meu filho mais velho Eduardo Galvão que no próximo dia 06 de Setembro estará completando 15 anos de vida, com certeza um dos momentos mais felizes e esplêndidos da minha vida. Desejo a você meu pequeno grande menino um futuro fantástico, muito saúde, felicidades e alegrias.

Vamos em frente, voltando ao post de hoje da sessão Para que serve, conforme prometido vou dar continuidade ao post anterior onde começamos a conhecer um pouco sobre o conceito de Índice Hipotéticos. Caso você não tenha acessado o post anterior não perca tempo clique no link acima e conheça um pouco sobre este conceito tão surpreende quando a sua forma de uso.


Começa agora o #08 – Para que serve – Índices Hipotéticos – Parte II.

No #08 – Para que serve, daremos início ao processo de criação do nosso ambiente de banco de dados com objetivo de construir um estrutura específica para conhecermos e simularmos o uso dos índices hipotéticos. É isso siga-me os bons, mãos no teclado, acompanhe a sequência de passos apresentandos abaixo:

— Passo 1 – Criando o Banco de Dados HypotheticalDB —

CREATE DATABASE [HypotheticalDB]
ON  PRIMARY
(NAME = ‘HypotheticalDB-Data’,
 FILENAME = ‘C:\SQLServer2016\Data\HypotheticalDB_Data.mdf’ ,
 SIZE = 4MB ,
 MAXSIZE = 4096MB,
 FILEGROWTH = 2MB )
LOG ON
(NAME = ‘HypotheticalDB-Log’,
 FILENAME = ‘C:\SQLServer2016\Log\HypotheticalDB_Log.ldf’ ,
 SIZE = 8MB ,
 MAXSIZE = 2GB ,
 FILEGROWTH = 4MB)
GO

— Passo 2 – Acessando o Banco de Dados —
Use HypotheticalDB
Go
— Passo 3 – Criando as Tabelas —
Drop Table If Exists dbo.Clientes
CREATE TABLE Clientes
(Codigo  INT Identity(1,1) NOT NULL Primary Key Clustered,
 CodigoCategoria TinyInt NOT NULL,
 Nome  VARCHAR(60) NOT NULL,
 Endereco VARCHAR(80) NOT NULL,
 Estado  CHAR(2) NOT NULL,
 DataUltimaCompra  DATETIME)
Go
Drop Table If Exists dbo.ClientesCategorias
CREATE TABLE ClientesCategorias
(Codigo TinyInt NOT NULL,
 Descricao VARCHAR(20) NOT NULL)
Go

Nota: Observe que no passo 3 estamos utilizando uma nova instrução introduzida no Microsoft SQL Server 2016, estou me referindo ao Drop If Exists uma das mais esperadas melhorias a serem adicionados ao Microsoft SQL Server que por muitos anos estava sendo aguardada.

— Passo 4 – Inserindo dados na Tabela ClientesCategorias —
INSERT Into ClientesCategorias (Codigo, Descricao)
 Values (1, ‘Premier’),
             (2, ‘Advanced’),
             (3, ‘Special’)
Go
— Passo 5 – Inserindo dados na Tabela Clientes —
Insert Into Clientes (CodigoCategoria, Nome, Endereco, Estado, DataUltimaCompra)
Values (3, ‘José Bonito’,’Rua A’,’SP’,GETDATE()-30),
            (1, ‘Dassaev Silva’,’Rua B’,’SP’,GETDATE()-120),
            (3, ‘Viewer Partes’,’Rua 123′,’RJ’,GETDATE()-720),
            (1, ‘Dino Silva Sauros’,’Avenida Parque dos Dinassauros’,’AM’,GETDATE()-240),
            (2, ‘Fernandino Campos Boyd’,’Estrada Velha’,’MG’,GETDATE()-5),
            (1, ‘Katrina Tornado’,’Rua Storm’,’RG’,GETDATE()-300),
            (2, ‘Washington Wizard’,’Place 1′,’PR’,GETDATE()-1024),
            (3, ‘Chicago Bulls’,’Place 2′,’PR’,GETDATE()-89),
            (2, ‘Denver Nuggets’,’Place 3′,’PR’,GETDATE()-289),
            (2, ‘Los Angeles Lakers’,’Place 4′,’PR’,GETDATE()-390)
Go
— Passo 6 – Consultando os dados —
Select Codigo, Descricao From ClientesCategorias
Go
Select Codigo, CodigoCategoria, Nome, Endereco, Estado, DataUltimaCompra From Clientes
Go
Até aqui nada muito diferente do que normalmente utilizamos em nosso dia-á-dia, talvez o uso da Drop If Exists possa ser um diferencial. Dando continuidade os dois próximos passos serão de extrema importância para nosso ambiente, estaremos justamente realizando a criação de três índices:
  • IND_ClientesCategorias_NaoClusterizado_CodigoSemEstatisticas;
  • IND_ClientesCategorias_NaoClusterizado_CodigoComEstatisticas; e
  • IND_ClientesCategorias_Clusterizado_CodigoComEstatisticas.

Observe que serão criados dois índices não-clusterizados e um índice clusterizado, todos vinculados a tabela ClientesCategorias para coluna Codigo, onde você vai poder notar que dois índices devem ser criados sem estatísticas o que indica para o SQL Server que este será um índice hipotético existindo somente de maneira lógica e não terá nenhum tipo de vínculo ou estrutura física criada. Então siga em frente, mãos no teclado, a seguir os passos 7 e 8:

— Passo 7 – Criando índices hipotéticos não-clusterizado na tabela ClientesCategorias —
CREATE INDEX IND_ClientesCategorias_NaoClusterizado_CodigoSemEstatisticas
 ON ClientesCategorias (Codigo) With Statistics_Only = 0
CREATE INDEX IND_ClientesCategorias_NaoClusterizado_CodigoComEstatisticas
 ON ClientesCategorias (Codigo) With Statistics_Only = -1
Go
— Passo 8 – Criando índices hipotéticos clusterizado na tabela ClientesCategorias —
CREATE CLUSTERED INDEX IND_ClientesCategorias_Clusterizado_CodigoComEstatisticas
 ON ClientesCategorias (Codigo) With Statistics_Only = -1
Go
Antes de continuarmos vou apresentar a Figura 1 que ilustra a criação destes índices dentro da estrutura da tabela ClientesCategorias, você vai poder notar na figura que somente existe estatísticas para estes objetos as guias Keys e Indexes estão vazias:
HypotheticalIndex

Figura 1 – Estrutura da Tabela ClientesCategorias.

Vamos que vamos estamos quase lá, falta pouco, agora vamos executar o passo 9 em duas etapas a primeira será a execução do system stored procedure sp_helpindex responsável em apresentar a estrutura de índices existente em uma determinada tabela, neste caso estou me referindo a tabela ClientesCategorias, sendo assim, vamos realizar esta execução:

 

— Passo 9 – Obtendo informações sobre os índices —
Exec sp_helpindex ClientesCategorias
Go

Após a execução o Management Studio vai retornar nossos três índices criados anteriormente, podemos observar a existência de uma coluna chamada index_description, verifique que todos os índices apresentam a seguinte informação: nonclustered, hypothetical

Você pode estar se perguntando, mas nos não criamos um índice clusterizado? A resposta seria sim, criamos ele continua sendo clusterizado, mas como este não apresenta um estrutura física o mesmo é reconhecido e tratado pelo SQL Server como índice não-clusterizado neste momento, a Figura 2 apresentada este resultado:

HypotheticalIndex2

Figura 2 – Relação de índices hipotéticos pertencentes a table ClientesCategorias.

O próximo passo e realizar a segunda parte do passo 9,  onde faremos a execução do comando DBCC Show_Statistics responsável em apresentar informações sobre as estruturas físicas e lógicas vinculadas a estatísticas de um índice, no nosso caso vamos utilizar os índices:

  • IND_ClientesCategorias_NaoClusterizado_CodigoSemEstatisticas; e
  • IND_ClientesCategorias_NaoClusterizado_CodigoComEstatisticas.

Vamos então executar o bloco de código abaixo:

DBCC SHOW_STATISTICS (ClientesCategorias, IND_ClientesCategorias_NaoClusterizado_CodigoSemEstatisticas)

DBCC SHOW_STATISTICS (ClientesCategorias, IND_ClientesCategorias_NaoClusterizado_CodigoComEstatisticas)
Go

Observando  a Figura 3 a seguir fica mais fácil  entender que o índice: IND_ClientesCategorias_NaoClusterizado_CodigoSemEstatisticas não apresenta nenhuma informação relacionada a estatísticas, ao contrário do índice:

IND_ClientesCategorias_NaoClusterizado_CodigoComEstatisticas, onde o SQL Server após o procedimento de crição do mesmo já estabeleceu alguns dados estatísticos que posteriormente será utilizados no processamento das querys de acordo com sua necessidade. Segui a seguir a Figura 3:

HypotheticalIndex3

Figura 3 – Comparativo entre as estruturas dos índices IND_ClientesCategorias_NaoClusterizado_CodigoSemEstatisticas  e IND_ClientesCategorias_NaoClusterizado_CodigoComEstatisticas.

Falta pouco, pouco mesmo, prometo que estamos no final, vamos executar o passo de número 10, onde estaremos obtendo as informações sobre nossos índices através da catalog view sys.sysindexes, onde nosso índice IND_ClientesCategorias_Clusterizado_CodigoComEstatisticas agora será apresentado como um índice clusterizado, isso nos faz entender que em alguns momentos o SQL Server acaba mudando um pouco o seu comportamento de acordo com a forma de obtenção de informações sobre as estruturas de nossos índices, sinceramente não saberia dizer se isso pode ser considerado uma falha ou até mesmo um possível bug.

Pisando fundo em nosso “acelerador” execute o passo 10 apresenta logo a seguir:

— Passo 10 – Obtendo informações sobre a relação de índices —

SELECT object_id,
             OBJECT_NAME(object_id) AS ‘Tabelas’ ,
             name As ‘Nome do Índice’,
             type_desc,
             is_hypothetical As ‘Índice Hipotético = 1 Não-Hipotético=0’
FROM sys.indexes
WHERE object_id in (object_id(‘ClientesCategorias’), object_id(‘Clientes’))
Go

E agora o tão esperado momento, vamos realmente fazer uso de nossos índices hipotéticos através da execução dos passos 11 e 12 teremos a capacidade técnica de entender o comportamento do SQL Server, principalmente através da comparação dos planos de execução gerados para cada query processada, sendo assim, vamos começar executando o passo 11 a seguir:

— Passo 11 – Executando o Select de maneira clássica sem a diretiva SET AUTOPILOT —
SET SHOWPLAN_XML ON
Go
Select C.Codigo,
          Cc.Codigo As ‘Categoria do Cliente’,
    C.Nome,
    C.Endereco,
    C.Estado,
    C.DataUltimaCompra
From Clientes C Inner Join ClientesCategorias CC
                           On C.CodigoCategoria = CC.Codigo
Where C.Estado = ‘SP’
GO
SET SHOWPLAN_XML OFF
Go
Observe que solicitamos ao Management Studio para realizar o plano de execução da nossa query através da diretiva SET SHOWPLAN_XML, onde o mesmo deverá ser gerado no formato XML, recomendo que você salve este plano de execução para que possamos fazer uso do mesmo no último. Agora execute o passo 12, salve o plano de execução gerado em xml e apresentado de forma gráfica.
— Passo 12 – Executando o Select de maneira personalizada ativando a diretiva SET AUTOPILOT  —
SET AUTOPILOT ON — Ativando a diretiva —
Go
Select C.Codigo,
          Cc.Codigo As ‘Categoria do Cliente’,
    C.Nome,
    C.Endereco,
    C.Estado,
    C.DataUltimaCompra
From Clientes C Inner Join ClientesCategorias CC
                           On C.CodigoCategoria = CC.Codigo
Where C.Estado = ‘SP’
Go
SET AUTOPILOT OFF — Desativando a diretiva —
GO
Muito bem, conseguimos executar nossos dois selects, espero que você tenha salvado os respectivos planos de execução, agora após a execução do passo 12 você deverá esta visualizando o plano de execução deste select, clique com o botão da direita na parte branca e escolha a opção Comparative ShowPlan, selecione o arquivo que representa o plano de execução criado após a execução do passo 11 em seguida o mesmo deverá ser aberto, conforme a Figura 4 a seguir apresenta:
HypotheticalIndex4
Figura 4 – Comparação entre os planos de execução gerados durante a execução dos passos 11 e 12.
Podemos observar que os dois planos de execução são praticamentes idênticos de maneira geral, mas se realmente analisarmos cada um dos operadores, será possível notar um uma pequena diferença no operador Select, onde a instrução CompileMemory nos mostra uma diferença de 8(oito) compilações a menos realizada no passo 11 em comparação com o passo 12, conforme ilustra a Figura 5 abaixo:
HypotheticalIndex5
Figura 5 – Comparativo de resultados apresentados na instrução CompileMemory.
Poxa vida, analisando friamente esta é uma diferença tão pequena que talvez não seja necessário se preocupar ou até mesmo querer entender o que pode ter acontecido. Mas DBA que é DBA de verdade não gosta de se deparar com estas situações em seu ambiente, menos ficar sem uma possível resposta, por mais que muitas vezes ela até mesmo não exista.
Então se você quiser realmente saber o que pode ter influenciado o SQL Server mais especificamente o Database Engine e seus componentes dentre eles o Query Optimizer e o Execution Plan a apresentar este resultado não deixe de acessar o próximo post dedicado aos índices hipotéticos, então nos vemos no #09 Para que serve.
Até lá………..

É isso ai galera, chegamos ao final de mais post da sessão Para que serve!

Espero que você tenha gostado, que as informações compartilhadas aqui possam lhe ajudar a se tornar cada vez um profissional de banco de dados reconhecido e valorizado, um dos papéis na área de tecnologia mais importantes para qualquer empresa.

Reconher o verdadeiro papel de um DBA dentro de sua estrutura, é reconhecer o verdadeiro valor de seus dados e como eles podem se tornar uma infomação valiosa para sua tomada de decisão.

Caso deseje acessar os posts anteriores desta sessão, utilize os links listados abaixo:

#05 – Para que serve


Hello everybody, Good Afternoon, Buenas Tardes e Buon pomeriggio!!!!

Domingão chuvoso aqui em São Roque, olha esta chuvinha já esta dando o que falar e causando alguns estranhos na região, poxa vida eu gosto de chuva mas acho que já deu na hora de parar(kkkkk), desse jeito vamos ficar todos embolorados(kkkkkk)….

Comentários e brincadeirasa parte, aproveitando este tempinho para ficar em casa se preparando para mais uma semana de muito trabalho no FIT e na FATEC, falando de FATEC já estamos chegando ao final do semestre.

Pois bem, estava pensando e decidi compartilhar com vocês na sessão Para que serve, mais dois comandos DBCCs (Database Command Console) não documentadas que recentimente acabei me deparando em uma necessidade na empresa e por incrível que pareça uma das QoD (Question of Day) no portal SQLServerCentral.com, alias eu sou suspeito a falar deste portal, sou fã de carterinha desta plataforma que emana conhecimento especializado e dedicado ao Microsoft SQL Server.

Seguindo em frente, estou me referindo a DBCC Freeze_IODBCC Thaw_IO! Por acaso você já teve a necessidade de utilizar ou já ouvi falar destes commandos?

Ficou surpreso em saber da existência destes recursos, eu também, mas tenho a certeza que você vai ficar mais supreso quando fazer uso deles, algo que realmente pode ajudar em muito a entender o comportamento do SQL Server durante o procedimento de leitura e escrita de uma transação.

Bom vamos conhecer um pouco mais sobre estas duas DBCCs não documentadas, tendo como base um tradicional ambiente de testes que normalmente utilizo para este tipo de necessidade, e como de costume, vamos vais uma vez resaltar que este tipo de procedimento deve ser realizado sempre em ambientes ou cenários de teste e desenvolvimento, pois são comandos que podem alterar o comportamento do seu servidor ou instância SQL Server, proporcionando algum tipo de perda de informação ou impactos nos seus dados. Mesmo assim acredito que vale a pena conhece-los.


Começa aqui o #05 – Para que serve, boa viagem meu amigo.

Basicamente estas duas DBCCs não documentadas estão presentes na relação de comandos não oficiais e não reconhecidos pela documentação Microsoft á algum tempo, nas pesquisas que realizei para obter mais exemplos e informações encontrei posts publicados em alguns blogs americas, russos e europeus datados de meados do ano de 2011, sendo assim deduzido que ambas as funcionalidades podem ser utilizadas de uma maneira mais “segura” nas versões 2008 R2, 2012 e 2014.

Para nosso ambiente de testes vou utilizar o Microsoft SQL Server 2014 Express SP1, também realizei testes no Microsoft SQL Server 2012 Express SP3, não observei nenhum tipo de mudança de comportamento ou processamento em relação a cada versão, tanto na sua forma de execução como também na maneira de processamento o resultado obtive foi o mesmo, sendo assim, fique a vontade para escolher a versão que você deseja utilizar. Então vamos conhecer um pouco sobre cada comando DBCC, começando pela DBCC Freeze_IO.

DBCC Freeze_IO

Como seu próprio nome segure, esta dbcc tem a função de congelar de forma temporária do processo de leitura e escrita realizada para um banco de dados, quando eu me referi a congelar estou fazendo referência a possibilidade de suspender todo processo de IO(Input – Output) para gravação de dados no banco de dados que você encontra conectado.

Quando o DBCC Freeze_IO é executado todas as atividades realizadas pelo SQL Server que envolvam processos de IO que estejam vinculadas a operação de escrita serão suspensas, não estou dizendo que estas atividades vão receber uma instrução de encerramento ou finalização, nada disso todas serão congeladas até que o comando DBCC Thaw_IO seja executado.
Uma vez que o IO é congelado, você pode tratar o banco de dados como um banco de dados somente leitura. Qualquer atividade de escrita não retornará nenhum tipo de informação, pois este banco de dados terá naquele exato momento o comportamento de um banco somente leitura. 
Você pode estar se pergunta, cara isso é muito perigo? Sim a resposta é sim, ainda mais se você fizer uso deste tipo de implementação em um ambiente que sofre uma carga enorma de leitura e escritas em pouco espaço de tempo.
DBCC Thaw_IO
Como destacado anteriormente este comando DBCC tem a finalidade de descongelar ou remover o status de suspensão nos processos de leitura e escrita marcados pela DBCC Freeze_IO. Quando executado o DBCC Thaw_IO envia para o Database Engine uma instrução que permite reverter o estado do banco de dados de somente leitura para leitura e escrita, desta forma, o SQL Server entende que a partir daquele momento o banco de dados envolvimento na execução da DBCC Freeze_IO não necessita mais ser impedido de receber novos dados, como também, esta livre para retornar qualquer tipo de informação solicitado pelo usuário e suas aplicações.
Ufa, parece ser bastante assustador fazer uso destes dois comandos, mas não é bem assim, e para mostrar que nem tudo o que parece realmente é tão perigoso, vamos fazer uma pequena prática para ilustrar o comportamento do SQL Server durante a execução de cada DBCC, para isso iremos utilizar uma pequena tabela chamada Estudantes, chegou então a hora de colocar a mão na massa ou melhor no código (kkkkk)….

Colocando a mão no código #05 – Para que serve – DBCC Freeze_IO e DBCC Thaw_IO

Para nosso ambiente de testes, vamos criar um novo banco de dados chamado DBFreeze e como destacado anteriormente será criada uma tabela chamada Estudantes que receberá alguns linhas de registros, conforme apresenta o bloco de Código 1 a seguir:
— Código 1 —

— Criando o Banco de Dados DBFreeze —

Create Database DBFreeze

Go

 

— Acessando o Banco de Dados —

Use DBFreeze

Go

 

— Criando a Tabela Estudantes —

Create Table Estudantes

(Id Int Primary Key Identity(1,1),

Nome Varchar(20) Not Null,

Classificacao TinyInt Not Null,

Curso Varchar(20) Not Null)

Go

 

— Inserindo os dados —

Insert Into Estudantes (Nome, Classificacao, Curso)

Values (‘Kim’, 99, ‘Inglês’),

(‘Thomas’, 95, ‘Inglês’),

(‘Jonh’, 92, ‘Inglês’),

(‘Mag’, 97, ‘Espanhol’),

(‘Sussy’, 90, ‘Espanhol’),

(‘Boby’, 91,‘Português’),

(‘Darth’, 89, ‘Português’)

Go 100000

 

Ótimo, nosso ambiente este criado, agora podemos fazer uma simples simulação do congelamento do banco de dados DBFreeze, para isso vamos utilizar o bloco de código 2, note que iremos executar um simples select com algumas funções de ranking na tabela Estudantes, conforme apresento abaixo:

 

— Código 2 —

Begin Transaction

Select Id, Nome, Classificacao, Curso,

ROW_NUMBER() Over (Order By Curso) As ‘Row Number’,

Rank() Over (Order By Curso) As ‘Rank’,

Dense_Rank() Over (Order By Curso) As ‘Dense Rank’,

NTile(4) Over (Order By Curso) As ‘NTile’

From Estudantes

Go

Observe que para garantir e evitar qualquer tipo de impacto em outras transações, realizei a abertura de uma nova transação para que o comando select e suas respectivas funções de ranking fossem executadas, neste momento esta transação esta sendo executado, vamos então forçar o congelamento do banco de dados, para isso abra uma nova query em seu management studio e execute o bloco de código 3:

— Código 3 —

DBCC Freeze_IO(DBFreeze)
Go

A partir deste momento nosso banco de dados DBFreeze acaba de receber a instrução de Freeze IO, onde o mesmo vai ser obrigado a interromper qualquer processo de atividades que envolvam leitura e escrita de dados, se você decidir voltar a sessão anterior, será possível observar que a mesma continua em execução mas os dados não são retornados em tela, para ter certeza de qual é o atual status desta sessão, podemos fazer uso da system stored procedure sp_lock para obter a lista de recursos e objetos atualmente em lock em nosso SQL Server.

Vamos voltar nosso banco de dados para o estado original, possibilitando que todas as leituras e escritam possam ser executadas normalmente retornando os dados solicitados, para isso utilizaremos o bloco código 4 apresentado a seguir. Para sua execução recomendo utilizar a mesma query criada para execução do código 3:

 

— Código 4 —

DBCC Thaw_IO(9)

Go

Após alguns segundos o Database Engine entende que o nosso banco de dados DBFreeze deve ter o estado de somente leitura alterado para leitura e gravação, sendo assim, o processamento da nossa primeira query será executado e encerrado normalmente, onde teremos todos os nossos dados apresentados conforme a estrutura do comando select executada no bloco de código 2, ilustrado anteriormente.

Observações:

  1. Gostaria de salientar que este tipo de funcionalidade não será muito aplicável ou utilizável em suas atividades diárias, mas poderá ser bastante útil quando você desejar de alguma forma simular uma possível “imagem ou snapshot” do seu banco de dados em um espaço de tempo ou situação; e
  2. O uso do DBCC Freeze_IO, DBCC Thaw_IO e qualquer outro comando DBCC não reconhecido ou documentado pela Microsoft e seus times de Engenheiros e desenvolvedores deve ser utilizado com muita cautela e sempre em ambientes de testes e desenvolvimento. Em algumas situações a adoção de estratégias de backup de bancos de dados ou até mesmo de um snapshot de seu ambiente virtualizado devem ser adotados como forma de garantir a recuperação dos seus recursos.

Então chegamos ao final de mais um post, estamos vivos e salvos e principalmente nosso banco de dados sobreviveu.


É isso ai galera, muito legal este post da sessão Para que serve!

Espero que você tenha gostado, que as informações compartilhadas aqui possam lhe ajudar a se tornar cada vez um profisisonal de banco de dados mais reconhecido e valorizado, algo que atualmente as empresas estão deixando muito de reconhecer o verdadeiro papel de um DBA que muitas vezes passa despercebido, mas é de grande e extremo valor para qual instituição, empresa ou corporação.

Caso deseje acessar os posts anteriores desta sessão, utilize os links postados abaixo:

Mais uma vez obrigado por sua visita, tenha uma ótima semana, nos encontramos em breve.

Até mais.

#04 – Para que serve


Eita mundo bão, boa noite galera, tudo bem?

Final de noite de sábado e após mais uma longa semana de muito trabalho, estou novamente aqui no meu blog para finalizar mais um mês com o post da sessão Para que serve.

No post de hoje vou compartilhar uma funcionalidade que particularmente não conhecia no SQL Server até o começo desta semana, estou me referindo a mais um DBCC – Database Command Console não documentada existente no Microsoft SQL Server, fazendo parte da longa lista de DBCC não documentadas, assunto que eu por diversas vezes já destaquei no meu blog e principalmente nos fóruns MSDN/TechNet.

Estou me referindo a DBCC CheckPrimaryFile, ao encontrar os primeiros posts sobre este commando fiquei surpreso com sua existência, ainda mais por que o cenário que eu me deparei com o uso do mesmo, por diversas outras oportunidade tive a necessidade de fazer aplicar uma possível solução muito parecida ou praticamente idêntica ao que este database command realiza, e ai acabei fazendo um questionamento para eu mesmo, poxa vida como eu nunca me atentei para esta funcionalidade.

Então tomei a descisão de buscar mais informações sobre este recurso e como de costume vamos fazer uso da internet para tentar descobrir mais informações sobre este DBCC. Não consegui encontrar muitas informações, sobre sua origem ou ator, dentre os principais posts que coletei informações foi possível identificar que trata-se de um recurso/funcionalidade presente no SQL Server desde a versão 2005 e mantida neste momento na versão 2014.

Levando-se em consideração as informações que encontrei durante as pesquisas que realizei, fica difícil afirmar se o time de desenvolvimento e engenharia do SQL Server vai manter este recurso de forma oculta na versão 2016 ou talvez venha a remover a compatibilidade da mesma. Mesmo assim, acredito que vale a pena conhecer um pouco mais sobre este comando, sua finalidade, forma de uso e principalmente em qual tipo de cenário pode ser interessante fazer uso deste recurso.

Vamos em frente, é chegada a hora, começa aqui o #04 – Para que serve – DBCC CheckPrimaryFile

Conhecida como um dos Database Command Console “não oficial” ou “não documentada” existente de forma no Microsoft SQL Server desde a versão 2005 e compátivel atualmente com a versão 2014 e suas edições a DBCC CheckPrimaryFile, tem como finalidade possibilitar ao DBA ou qualquer profissional que esteja naquele momento tendo a necessidade de descobrir se um determinado arquivo de dados (conhecido como arquivo .MDF – Master Data File), pode ser reconhecido como o arquivo primário (primary file) que compõem a estrutura física e lógica de respectivo banco de dados.

A DBCC CheckPrimaryFile apresenta uma sintaxe e forma de uso simples, composta por dois parâmetros:

  • PhysicalFileName: Representa e identifica o nome físico do arquivo de dados relacionado a um determinado banco de dados; e
  • Opt: Parâmetro composto por um conjunto de quatro valores sendo eles:
    • 0 – Verifique se o physical file name informado realmente representa e consiste como arquivo de dados primário;
    • 1 – Apresenta como retorno as colunas name, size, maxsize, status and path de todos os arquivos associados ao respectivo physical file name;
    • 2 – Apresenta como retorno as colunas databasename, version e collation do respectivo physical file name; e
    • 3 – Apresenta como retorno as colunas name, status e path do respectivo physical file name informado.

Exemplo:

  • DBCC checkprimaryfile (‘PhysicalFileName’, 0)

Dentre os principais cenários relacionados á este DBCC, estão associados as mensagens de erro 5171 e 5172 apresentadas pela Microsoft SQL Server, sendo elas:

  • An exception occurred while executing a Transact –SQL statement or batch. _db.mdf is not a primary database file. (Microsoft SQL Server, Error: 5171).”
  • is not a primary database file. (Microsoft SQL Server, Error: 5171)
  • Server: Msg 5172, Level 16, State 15, Line 1 The header for file path……\.mdf is not a valid database file header. The PageAudit property is incorrect.”

Basicamente estas mensagens relacionadas aos erros: 5171 e 5172 são apresentados pelo SQL Server em operações que envolvam as possíveis transações de:

  • Attach Database;
  • Attach Database a Single File; e
  • Restore Database.

Além disso, estas mensagem de erro normalmente são indicadores que a estrutura física e lógica dos arquivos de dados existentes a um determinado banco de dados podem estar corrompidas, algo que pode representar em diversas situações possíveis perdas de dados ou risco de seu banco de dados estar totalmente danificado ou condenado.

Agora é chegado a hora de por a mão na massa, vamos executar o DBCC CheckPrimaryFile e observar o conjunto de dados apresentados em cada das suas formas de uso, para este cenário de teste estou utilizando o Microsoft SQL Server 2014 Express em meu ambiente de teste e desenvolvimento. Aproveito a oportunidade para destacar que este tipo de comando não deve ser utilizado em servidores de produção ou ambientes que possuam grande volume de acesso por parte dos usuários, bem como dados de grande importância para você ou sua empresa.

Uma dica importante sempre que você for realizar qualquer tipo de teste sem conhecer um determinado comando ou funcionalidade é altamente recomendável a realização de backup dos seus principais bancos de dados.

— Exemplos de utilização – DBCC CheckPrimaryFile —


— Verificando se o arquivo realmente é um Primary File —
DBCC CheckPrimaryFile (‘C:\Bancos\Laboratorio.MDF’,0)
Go

DBCC CheckPrimaryFile - 0

— Retornando o conjunto completo de informações associados ao Primary File que formam a estrutura do respectivo Banco de Dados —
DBCC CheckPrimaryFile (‘C:\Bancos\Laboratorio.MDF’,1)
Go

DBCC CheckPrimaryFile - 1

— Retornando o nome do banco de dados, versão e collation relacionados ao Primary File —
DBCC CheckPrimaryFile (‘C:\Bancos\Laboratorio.MDF’,2)
Go

DBCC CheckPrimaryFile - 2

— Retornando o status, fileid, name e filename associados ao Primary File que formam a estrutura do respectivo Banco de Dados —
DBCC CheckPrimaryFile (‘C:\Bancos\Laboratorio.MDF’,3)
Go

DBCC CheckPrimaryFile - 3

Você pode notar que após a execução de cada uma das respectivas formas de uso existentes no DBCC CheckPrimaryFile, o conjunto de dados retornados pelo Microsoft SQL Server é apresentado de acordo com os valores informações no physical file name mas principalmente ao número que representa a opção e conjunto de valores deve ser retornados em tela.

Importante destacar a necessidade do usuário que encontra-se conectado no momento da execução deste DBCC em seu servidor ou instância SQL Server ter permissão de acesso ao local em que seu arquivos .mdf, .ndf e .ldf que formam a estrutura física do seu banco de dados estejam armazenados fisicamente.


Sendo assim chegamos ao final……Eita nóis, consegui, ou melhor conseguimos, estamos vivos em mais um post dedicado a sessão Para que serve, foi muito legal poder compartilhar com vocês este Database Command Console existente a muitos anos no SQL Server.

Desejo e torço para que você tenha gostado das informações aqui compartilhadas, caso tenha interesse em acessar os outros posts desta sessão não perca tempo, utilize os links apresentados abaixo e boa viagem meu amigo:

  1. https://pedrogalvaojunior.wordpress.com/2016/04/08/03-para-que-serve/
  2. https://pedrogalvaojunior.wordpress.com/2016/02/26/02-para-que-serve/
  3. https://pedrogalvaojunior.wordpress.com/2016/01/28/01-para-que-serve/

Um grande abraço! Obrigado por sua participação e visita, bom fim de noite e um ótimo domingo.

Short Scripts – Março 2016


Olá comunidade, boa tarde amantes do SQL Server e Banco de dados!!!!

Após alguns meses fora do ar, mais uma sessão do meu blog esta retornando com muita força e disposição, estou me referindo a Short Scripts, sessão que durante os últimos anos tem conseguido alavancar a visitação do meu blog e principalmente ajudar a todos da comunidade na utilização de pequenos scripts dos mais variados possíveis assuntos relacionandos com o banco de dados mas principalmente ao Microsoft SQL Server.

Caso você não se lembre esta é uma sessão publicada a cada bimestre, onde durante o intervalo entre a publicação do post anterior e o novo post, vou adicionando novos arquivos “scripts” a minha biblioteca de scripts e posteriormente selecione aqueles que aparentemente podem ser considerados pequenos e de fácil uso e entendimento, mas que na verdade podem ajudar a resolver diversas situações ou até mesmo servir como recurso para implementação na busca da solução de grandes obstáculos que os profissionais de banco de dados e desenvolvimento possam estar se deparando.

Muitos dos scripts disponibilizados nesta sessão são resultado de pesquisas, posts publicados nos fóruns MSDN e também de respostas encontradas em outros websites dedicados ao SQL Server. Caso você não tenha acessado os últimos posts, não perca tempo, utilize os links publicados a seguir:

https://pedrogalvaojunior.wordpress.com/2015/12/14/short-script-dezembro-2015/

https://pedrogalvaojunior.wordpress.com/2015/10/21/short-scripts-outubro-2015/

https://pedrogalvaojunior.wordpress.com/2015/08/26/short-scripts-agosto-2015/

https://pedrogalvaojunior.wordpress.com/2015/06/03/short-script-junho-2015/

Na relação de short scripts disponibilizados neste post, destaco os seguintes assuntos, conceitos ou funcionalidades:

  • Comando Like;
  • Comando Try…Catch;
  • Comando Print;
  • Concatenação de colunas e linhas;
  • Conversão de dados;
  • Dados Hexadecimais;
  • Database Owner;
  • Diretiva Set Ansi_Defaults;
  • Função Hashbytes;
  • Operador Bitwise;
  • Session Settings;
  • SET Implicit_Transactions;
  • String; e
  • Tipos de dados XML.

A seguir, apresento a relação de short scripts:

— Short Script 1 – Brincando com concatenação de linhas e colunas —

Select 1.8 E,1.8E,1.Eight,3+8E8Eight,

3+8 E8E,‘Six’+‘Seven’ ‘Eight’,‘Six’+‘Seven’+‘Eight’,

8.8Eight,‘Six”Seven’ ‘Eight’

Go

 

— Short Script 2 – Utilizando operador Bitwise para análise lógica de dados —

USE tempdb;

GO

 

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = ‘bitwise’)

DROP TABLE bitwise;

GO

CREATE TABLE bitwise

(

a_int_value int NOT NULL,

b_int_value int NOT NULL

);

GO

INSERT bitwise VALUES (250, 255);

GO

 

USE tempdb;

GO

SELECT a_int_value | b_int_value

FROM bitwise;

GO

 

Select convert(binary, a_int_value | b_int_value) from bitwise

Go

 

— Short Script 3 – Trabalhando com XML – Apresentando os mesmos dados de forma diferente —

create table #customers (

id int,

customer varchar(50)

)

insert into #customers values (1,‘John’)

insert into #customers values (2,‘Lyss’)

insert into #customers values (3,‘Jack’)

insert into #customers values (4,‘David’)

insert into #customers values (5,‘Anne’)

insert into #customers values (6,‘Victoria’)

a)

SELECT 1 AS tag,

NULL AS parent,

id AS [customers!1!id],

customer AS [customers!2!customer]

FROM #customers AS customersa

UNION ALL

SELECT 2 AS tag,

1 AS parent,

id AS [customers!1!id],

customer AS [customers!2!customer]

FROM #customers AS customersb

ORDER BY [customers!2!customer] DESC,parent

FOR XML EXPLICIT, ROOT (‘customers’)

 

B)

SELECT id AS [customers/@id],

customer AS [customers/customers/@customer]

FROM #customers AS customesra

ORDER BY customer DESC

FOR XML PATH(),ROOT(‘customers’)

 

C)

SELECT customersa.id AS [customers.id],

customersb.customer AS [customers.customer]

FROM #customers AS customersa

INNER JOIN #customers AS customersb ON customersb.id=customersa.id

ORDER BY customersa.customer DESC

FOR XML AUTO, ROOT(‘customers’)

 

D)

DECLARE @x AS XML

SET @x=(

SELECT id, customer

FROM #customers AS customersa

FOR XML RAW(‘customer’),ROOT(‘customers’),TYPE

)

SELECT @x.query(

for $e in customers/customer

order by $e/@customer descending

return <customers id=”{$e/@id}”>

<customers customer=”{$e/@customer}”>

</customers>

</customers>’)

FOR XML RAW(‘customers’)

Go

 

— Short Script 4 – Descobrindo e Alterando o Database Owner —

— Obtendo a lista de Database Owners —

Select d.database_id,

d.name,

suser_name(d.owner_sid) as ‘Owner’,

d.user_access_desc,

d.compatibility_level

from sys.databases as d

Go

— Obtendo informações de um banco específico —

Exec sp_helpdb ‘Master’

Go

— Alterando o Database Owner —

Exec sp_changedbowner ‘NomedoNovoOwner’

Go

— Obtendo informações de um banco específico após alteração —

Exec sp_helpdb ‘Master’

Go

 

 

— Short Script 5 – Concatenação de string diretamente no select —

create table xxx (i int identity, a varchar(3))

go

declare @txt varchar(255)

set @txt = ‘Question’

select @txt = isnull (a, ‘?’) from xxx order by i

insert xxx values (‘of’)

insert xxx values (‘the’)

insert xxx values (‘day’)

select @txt = @txt + ‘ ‘ + a from xxx order by i

select @txt

Go

 

— Short Script 6 – Utilizando comando Like na junção de tabelas —

create table #Table1 (Col1 varchar(10))

create table #Table2 (Col1 varchar(10))

Go

insert into #table1 values (‘1’)

insert into #table2 values (‘1’)

insert into #table2 values (‘_1’)

insert into #table2 values (‘%1’)

insert into #table2 values (‘[_]1’)

insert into #table2 values (‘[%]1’)

Go

 

select * from #table1 t1 inner join #table2 t2

on t1.Col1 like t2.Col1

Go

 

 

— Short Script 7 – Session Settings – Utilizando ANSI_Defaults forçando SQL Server trabalhar com SET IMPLICIT_TRANSACTIONS —

Create Table QOD_Customers

(CompanyName Varchar(20),

Region nvarchar(15) Null)

Go

Insert Into QOD_Customers Values (‘A’,‘teste’)

Go 30

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create PROCEDURE [dbo].[QOD_Test_1]

AS

SET ANSI_DEFAULTS ON

— Before rollback Select Statement

SELECT COUNT(CompanyName) AS ‘Before rollback’ FROM [dbo].[QOD_Customers] WHERE [dbo].[QOD_Customers].[Region] IS NULL

UPDATE Dbo.QOD_Customers SET Region = ‘XXX’ WHERE dbo.QOD_Customers.region IS NULL

— The after update Select Statement

SELECT COUNT(CompanyName) AS ‘After update’ FROM [dbo].[QOD_Customers] WHERE [dbo].[QOD_Customers].[Region] IS NULL

ROLLBACK TRANSACTION

SET ANSI_DEFAULTS OFF

— The after rollback Select Statement

SELECT COUNT(CompanyName) AS ‘After Rollback’ FROM [dbo].[QOD_Customers] WHERE [dbo].[QOD_Customers].[Region] IS NULL

GO

 

Exec [QOD_Test_1]

Go

 

— Short Script 8 – Utilizando Try..Catch + Transaction + Print para apresentar mensagem —

declare @i int, @j int

set @i = 1

create table #temp (id int)

while (@i<=5)

begin

begin try

begin transaction

if (@i = 3)

set @j = @i/0

insert into #temp values (@i)

commit transaction

end try

begin catch

rollback transaction

print ‘this is an exception’;

end catch

set @i = @i + 1

end

 

select * from #temp

Go

 

— Short Script 9 – Trabalhando com a função Hashbytes para conversão de dados string para hexadecimal —

Select HASHBYTES(‘MD2’,‘January 1, 2016’)

Select HASHBYTES(‘MD4’,‘January 1, 2016’)

Select HASHBYTES(‘MD5’,‘January 1, 2016’)

Select HASHBYTES(‘SHA’,‘January 1, 2016’)

Select HASHBYTES(‘SHA1’,‘January 1, 2016’)

Select HASHBYTES(‘SHA2_256’,‘January 1, 2016’)

Select HASHBYTES(‘SHA2_512’,‘January 1, 2016’)

Go

 

 


 

Como de costume fique a vontade para compartilhar este conteúdo com seus contatos e redes sociais, contribua também enviando dicas, sugestões, dúvidas, críticas, enfim participe e ajude a melhorar cada vez mais esta sessão.

Mais uma vez obrigado por sua visita, nos encontramos em breve em mais uma post da sessão Short Scripts.

Valeu!!!