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…

Anúncios

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

#02 – Para que serve


Ei galera, comunidade e amantes de Banco de Dados, boa tarde.

Sexta – feira, mas que beleza final de semana chegando, dois dias de descanso merecidos, mas isso pra mim não muda em nada, sabe por que? Pois ainda estou de férias, férias (kkkkkkkkkk).

Deixando isso de lado a vida continua, ela não pode parar principalmente a vida tecnlógica, alias essa minha vida tecnológica viva a toda sempre em um ritmo acelerado, e como vocês podem observar nos últimos dias meu blog esta bombando de posts, parece até que estou com mais vontade para interagir com vocês.

Neste segundo post da sessão Para que Serve, vou trazer algo que muitos dos profissionais de banco de dados sempre utilizam naqueles momentos em que tudo parece não dar certo, estou me referindo as boas e velhas Trace Flags(isso não é magia negra, bruxaria ou assombração, isso é tecnologia, kkkkkk) existentes no Microsoft SQL Server desde suas primeiras versões.

Posso dizer com grande tranquilidade, elas são algo mágico que a equipe de engenheiros e desenvolvedores do Microsoft SQL Server está sempre acrescentando e melhorando em cada versão do produto. Ao fazer a escolhar de usar uma, duas ou três trace flags, podemos estar alterando totalmente o comportamento por parte do SQL Server uma sessão, transação, conexão, banco de dados e até mesmo servidor/instância durante o processamento de uma ou mais querys.

No post de hoje, vou apresentar e ilustrar três trace flags pertencentes a lista de trace flags não documentadas, como você deve ter ouvido falar em algum momento, existem diversas trace flag(alguns dizem que são fantasmas existentes no SQL Server) que a Microsoft não apresenta ou possui documentação oficial, ainda mais se pesquisarmos nos próprios sites Microsoft dedicados os produto ou no Books On-Line.

Estou me referindo especificamente as Trace Flags: 3604, 9204, 9292, onde as flags 9204 e 9292 possuem algo em comum que você vai descobrir no decorrer deste post. Então vamos em frente, vamos lá conhecer um pouco mais sobre estas “trace flags fantasminhas”, abaixo destaco um pequeno resumo de cada trace flag, no final dois exemplos de como podemos utilizar cada uma delas no Microsoft SQL Server 2012 ou 2014.

Caso você não possua nenhuma destas versões, utilize os links abaixo para realizar os downloads:


 

Bom divertimento, começa aqui o #02 – Para que serve!!!

  • Trace Flag 3604: Dentre as trace flags desconhecidas ou não documentas a 3604 é bastante comum de ser encontrada na internet em diversos posts, artigos e publicações. Basicamente sua função é apresentar as mensagens geradas pelo SQL Server durante o processo de rastreamento ou monitoramento estatístico na guia de mensagens existentes no Management Studio.
    Quando realizamos habilitamos esta flag para uso através do comando DBCC TraceON (3604) esta orientando o SQL Server a direcionar as mensagens oriundas ou resultando do rastreamento estatístico para guia de mensagens, desta forma poderemos fazer a análise destas mensagens. Para ativar a trace flag utilize o comando DBCC TraceON (3604) ou DBCC TraceOFF (3604) para desativar, ou se preferir utilize a opção QueryTraceOn em sua respectiva query.

 

  • Trace Flag 9204: Esta trace flag pode ser considerada um dos elementos fundamentais para os profissionais de banco de dados, no momento em que estão realizando uma análise ou monitoramento da performance do SQL Server durante a execução de uma query. A 9204 permite entender e apresentar como podemos saber quais as estatísticas na verdade estão sendo utilizadas pelo otimizador de querys para calcular ou estimar o nível de cardinalidade. Desta maneira, torna-se possível identificar possíveis problemas por parte do SQL Server em obter dados relacionados. Ativando a Trace Flag 9204 o SQL Server vai apresentar na guia de mensagens existem no Management Studio um pequeno cabeçalho sobre objetos de estatísticas totalmente carregado e usado pelo otimizador de querys para estimativar o número de cardinalidade gerado e consumido para obtenção dos dados requeridos por parte da transação executado. Este cabeçalho é conhecido como Stats loaded.
    Para ativar esta a flag utilize o comando DBCC TraceON (9204) ou DBCC TraceOFF (9204) para desativar, ou se preferir utilize a opção QueryTraceOn em sua respectiva query.

 

  • Trace Flag 9292: No momento em tomamos a decisão de utilizar a flag 9292, orientamos o SQL Server a apresentar todos os objetos estatísticos considerados úteis por parte do plano de execução para realizar o processamento e retorno dos dados. O uso da trace flag 9292 dentro de uma sessão ou query específica, nos ajuda a entender e conhecer como as estatísticas e seus elementos podem mudar totalmente a maneira que o plano de execução é idealizado, armazenado e processado.
    Através da flag 9292 podemos obter um relatório sobre as estatíticas para cada objeto envolvido em nossa query, onde estes supostos objetos devem ser considerados úteis, ou melhor dizendo válidos e aplicáveis no decorrer do caminho realizado até a apresentação do resultado.
    Esta é uma trace flag que pode ser usado durante a resolução de problemas, onde sua função é apresentar na guia de mensagens do Management Studio, um pequeno cabeçalho contendo informações estatísticas sobre cada componente útil e válido para formas os dados estatísticos de processamento da query. Este cabeçalho é conhecido como Stats header loaded.
    Para ativar a trace flag utilize o comando DBCC TraceON (9292) ou DBCC TraceOFF (9292) para desativar, ou se preferir utilize a opção QueryTraceOn em sua respectiva query.

 


Conforme destacado no início deste post, vou apresentar dois exemplos básicos de como podemos fazer uso destas trace flags:

— 1 – Exemplo – Utilizando as Trace Flags: 3604, 9204 e 9292 na sessão através do comando DBCC TraceOn/TraceOff —

Use [AdventureWorks2012]
Go

DBCC FreeProcCache
Go

DBCC TraceOn (3604)
DBCC TraceOn (9292)
DBCC TraceOn (9204)

Select * From Person.Person
Where lastName like ‘D%’

DBCC TraceOff (3604)
DBCC TraceOff (9292)
DBCC TraceOff (9204)
Go

— 2- Exemplo – Utilizando as Trace Flags: 3604, 9204 e 9292 de forma específica na query através da opção QueryTraceOn —

Use [AdventureWorks2012]
Go

DBCC FreeProcCache
Go

Select * From Person.Person
Where lastName like ‘D%’
option
(
querytraceon 3604,
querytraceon 9292,
querytraceon 9204
)
Go

Ótimo que estão os exemplos, podemos observar através da Figura 1 apresentada a seguir como os respectivos cabeçalhos são apresentados pelas Trace Flags 9292 e 9204 na guia de mensagens do Management Studio, vale ressaltar que a importância da trace flag 3604 para ativar apresentação de informações ocultas na guia de mensagens do Management Studio.

Cabeçalhos-Trace-Flags
Figura 1 – Cabeçalhos gerados na utilização das Trace Flags 9292 e 9204.

 

 

 


 

Ficou interessado em saber mais sobre as Trace Flags? Acesse um dos links apresentados abaixo e descubra mais sobre estas fantásticas funcionalidades presentes e também “escondidas” no Microsoft SQL Server.

Links:


 

 

E ai gostou do #02 – Para que serve, eu gostei, claro sou suspeito a falar, mas torço e espero que você tenha gostado, de preferência de todo post, mas isso não for possível, quem sabe pelo menos uma parte espero que tenha chamado a sua atenção.

Com isso chegamos ao final de mais um Para que Serve, agradeço sua participação e visita ao meu blog. Não deixe de contribuir com críticas, sugestões, comentários, opiniões, enfim contribua também interagindo cada vez mais proporcionando esta troca de conhecimento.

Um grande abraço, sucesso, nos encontramos em breve.

Tchau.

Short Script – Maio – 2015


Bom dia, pessoal, mais uma semana começando.

Mantendo a tradição e escrita, estou começando mais um mês no meu blog, compartilhamento com todos vocês a minha relação de Scripts que atualmente esta crescendo muito graças ao SQLServerCentral.com.

Venho diariamente estudando e respondendo as questões diárias que são publicadas no SQLServerCentral.com, pelos principais nomes e especialistas em SQL Server do Mundo, estou seguindo nesta caminhada desde o final do mês de fevereiro. Posso dizer que esta sendo uma oportunidade fantástica de relembrar, conhecer, aprender e também descobrir muitos segredos, novidades e principalmente informações sobre o Microsoft SQL Server em todas as suas versões.

Aproveito a oportunidade para indicar e recomendar sua visita ao SQLServerCentral.com, principalmente a sessão de Question of the Day: http://www.sqlservercentral.com/Questions

Pois bem, na relação de Short Scripts que estou disponibilizando hoje, boa parte destes códigos são resultado das questões que eu respondi no SQLServerCentral.com e identifiquei como questões importantes ou relevantes para meus estudos, bem como, para repassar para meus alunos.

Nesta relação, você terá a oportunidade de conhecer e aprender um pouco mais sobre:

·         Análise e Lógica Condicional com operadores Not, And e Or;
·         Colunas Computadas;
·         Complexidade Lógica;
·         CTE Recursiva;
·         Declaração de variáveis do Tipo XML em conjunto com Select;
·         Função Coalesce em conjunto com Left Join e valores nulos; e
·         Funções Char(), NChar(), Unicode() e ASCII();
·         Identificando ausência de índices em tabelas;
·         Índices Clusterizados e Índices Chaves;
·         Índices com colunas duplicadas;
·         Operador Intersect;
·         Taxas de Fragmentação de Tabelas antes e depois do Rebuild;
·         Transações aninhadas;
·         Utilitário BCP para criação de arquivos XML.

 

Legal, a seguir você poderá encontrar os Short Scripts separados por seus respectivos conteúdos:

  • Análise, Precedência e Lógica Condicional com operadores Not, And e Or    VALUES ( 1, ‘Baseball Hat’ ),           ( 3, ‘Snowboard’ ),           ( 5, ‘Shows’ )SELECT ID   WHERE Product = ‘Bicycle’ OR Product = ‘Snowboard’ AND ID = 4

 

  1.    FROM @Logic
  2.            ( 4, ‘Goggles’ ),
  3.          ( 2, ‘Bicycle’ ),
  4. INSERT INTO @Logic
  5. DECLARE @Logic TABLE ( ID INT, Product VARCHAR(50) )

 

  • Colunas Computadas   (   , col1Computed AS LEFT(col1, 4)   , col2Computed AS LEFT(col2, 4)   , col3Computed AS LEFT(col3, 4)   , col4Computed AS LEFT(col4, 4) 

 

  1.    );
  2.    , col4 CHAR(8) NOT NULL
  3.    , col3 CHAR(8) NULL
  4.    , col2 VARCHAR(8) NOT NULL
  5.      col1 VARCHAR(8) NULL
  6. CREATE TABLE t
  7. Complexidade Lógica

DECLARE @AA VARCHAR(12) = ‘AAA’

,@XXX           VARCHAR(12) = ‘AAA’

,@YYY           VARCHAR(12) = ‘BBB’

,@ZZZ           VARCHAR(12) = ‘CCC’

,@AAXXX         VARCHAR(12) = ‘XXAAA’

,@AAYYY         VARCHAR(12) = ‘XXBBB’

,@AAZZZ         VARCHAR(12) = ‘XXCCC’

,@XXXCount       INT = 1

,@YYYCount       INT = 1

,@ZZZCount       INT = 1

,@AAXXXCount     INT = 1

,@AAYYYCount     INT = 1

,@AAZZZCount     INT = 1

,@Result       INT;

 

— Much to your chagrin, you encounter the following logic in a SQL SP.

— You may assume that each local variable is DECLAREd and @AA,

— @XXX, @YYY, @ZZZ, @AAXXX, @AAYYY, @AAZZZ each have a value assigned,

— with only @AA required to be NOT NULL. Each @xxxCount variable

— is also assigned an integer value.

SET @Result = 0;

 

IF @AA = @XXX

BEGIN

IF @XXXCount > @AAXXXCount SET @Result = 2

END

ELSE IF @AA = @YYY

BEGIN

IF @YYYCount > @AAYYYCount SET @Result = 2

END

ELSE IF @AA = @ZZZ

BEGIN

IF @ZZZCount > @AAZZZCount SET @Result = 2

END

ELSE IF @AA = @AAXXX

BEGIN

IF @XXXCount = @AAXXXCount SET @Result = 3

END

ELSE IF @AA = @AAYYY

BEGIN

IF @YYYCount = @AAYYYCount SET @Result = 3

END

ELSE IF @AA = @AAZZZ

BEGIN

IF @ZZZCount = @AAZZZCount SET @Result = 3

END

 

— Identify the equivalent logic from the options shown:

— Option 1:

SET @Result = 0;

 

IF @AA = @XXX AND @XXXCount > @AAXXXCount SET @Result = 2

ELSE IF @AA = @YYY AND @YYYCount > @AAYYYCount SET @Result = 2

ELSE IF @AA = @ZZZ AND @ZZZCount > @AAZZZCount SET @Result = 2

ELSE IF @AA = @AAXXX AND @XXXCount = @AAXXXCount SET @Result = 3

ELSE IF @AA = @AAYYY AND @YYYCount = @AAYYYCount SET @Result = 3

ELSE IF @AA = @AAZZZ AND @ZZZCount = @AAZZZCount SET @Result = 3

 

SELECT [Option 1:]=@Result

SELECT @Result = NULL;

 

 

— Option 2:

IF @AA = @XXX AND @XXXCount > @AAXXXCount SET @Result = 2

ELSE IF @AA = @YYY AND @YYYCount > @AAYYYCount SET @Result = 2

ELSE IF @AA = @ZZZ AND @ZZZCount > @AAZZZCount SET @Result = 2

ELSE IF @AA = @AAXXX AND @XXXCount = @AAXXXCount SET @Result = 3

ELSE IF @AA = @AAYYY AND @YYYCount = @AAYYYCount SET @Result = 3

ELSE IF @AA = @AAZZZ AND @ZZZCount = @AAZZZCount SET @Result = 3

ELSE SET @Result = 0;

 

SELECT [Option 2:]=@Result

SELECT @Result = NULL;

 

— Option 3:

SELECT @Result = CASE @AA

WHEN @XXX THEN CASE WHEN @XXXCount > @AAXXXCount THEN 2 ELSE 0 END

WHEN @YYY THEN CASE WHEN @YYYCount > @AAYYYCount THEN 2 ELSE 0 END

WHEN @ZZZ THEN CASE WHEN @ZZZCount > @AAZZZCount THEN 2 ELSE 0 END

WHEN @AAXXX THEN CASE WHEN @XXXCount = @AAXXXCount THEN 3 ELSE 0 END

WHEN @AAYYY THEN CASE WHEN @YYYCount = @AAYYYCount THEN 3 ELSE 0 END

WHEN @AAZZZ THEN CASE WHEN @ZZZCount = @AAZZZCount THEN 3 ELSE 0 END

END;

 

SELECT [Option 3:]=@Result          — Not identical

SELECT @Result = NULL;

 

— Option 4:

SELECT @Result = CASE @AA

WHEN @XXX THEN CASE WHEN @XXXCount > @AAXXXCount THEN 2 ELSE 0 END

WHEN @YYY THEN CASE WHEN @YYYCount > @AAYYYCount THEN 2 ELSE 0 END

WHEN @ZZZ THEN CASE WHEN @ZZZCount > @AAZZZCount THEN 2 ELSE 0 END

WHEN @AAXXX THEN CASE WHEN @XXXCount = @AAXXXCount THEN 3 ELSE 0 END

WHEN @AAYYY THEN CASE WHEN @YYYCount = @AAYYYCount THEN 3 ELSE 0 END

WHEN @AAZZZ THEN CASE WHEN @ZZZCount = @AAZZZCount THEN 3 ELSE 0 END

ELSE 0 END;

 

SELECT [Option 4:]=@Result

SELECT @Result = NULL;

 

— Option 5:

SELECT @Result = CASE @AA

WHEN @XXX THEN CASE WHEN @XXXCount > @AAXXXCount THEN 2 END

WHEN @YYY THEN CASE WHEN @YYYCount > @AAYYYCount THEN 2 END

WHEN @ZZZ THEN CASE WHEN @ZZZCount > @AAZZZCount THEN 2 END

WHEN @AAXXX THEN CASE WHEN @XXXCount = @AAXXXCount THEN 3 END

WHEN @AAYYY THEN CASE WHEN @YYYCount = @AAYYYCount THEN 3 END

WHEN @AAZZZ THEN CASE WHEN @ZZZCount = @AAZZZCount THEN 3 END

ELSE 0 END;

 

SELECT [Option 5:]=@Result   — Not identical

 

  • CTE Recursiva set @DataFim = ‘20150430’;with CTE_Datas as   select @DataIni as Data, @DataFim as DataFim   union all   select DATEADD(DAY, 1, Data), DataFim   where Data < DataFim 

 

  1. select Data from CTE_Datas
  2. )
  3.    from CTE_Datas
  4. (
  5. set @DataIni = ‘20150401’;
  6. declare @DataIni date, DataFim date;

 

  • Declaração de variáveis do Tipo XML em conjunto com Select

 

DECLARE @Xml XML = N’

<products>

<product id=”1″ name=”Adjustable Race” />

<product id=”879″ name=”All-Purpose Bike Stand” />

<product id=”712″ name=”AWC Logo Cap” />

<product id=”19910″ name=”Cozonac” />

</products>’;

 

SELECT

xt.xc.value(‘@id’, ‘INT’) AS ProductID,

xt.xc.value(‘@name’,’NVARCHAR(50)’) AS Name

FROM

@Xml.nodes(‘/products/product’) AS xt(xc);

 

  • Função Coalesce em conjunto com Left Join e valores nulos(a int null, c int null)(x int null, z int null)

 

  1. y int null,
  2. Create table #xyz
  3. b int null,
  4. Create table #abc

 

 

Insert Into #abc values(null, null, 3), (1,null,3),(1,null,null),(null,2,null)

 

Insert Into #xyz values(null,2,3),(null,null,1),(null,null,2)

 

— Q1

SELECT

*

FROM

#abc a

LEFT JOIN #xyz x

ON a.a = x.x

AND a.b = x.y;

 

— Q2

SELECT

*

FROM

#abc a

LEFT JOIN #xyz x

ON COALESCE(a.a, 9) = COALESCE(x.x, 9)

AND COALESCE(a.b, 9) = COALESCE(x.y, 9);

 

 

  • Funções Char(), NChar(), Unicode() e ASCII()SELECT ASCII(36)   SELECT NCHAR(10084)  

 

    1. SELECT UNICODE(10084)
    2. SELECT CHAR(36)
  • Identificando ausência de índices em tabelas             user_scans, ID.equality_columns, FROM sys.dm_db_missing_index_group_stats GS LEFT OUTER JOIN sys.dm_db_missing_index_groups IG                                                                                                                                                                                                                                LEFT OUTER JOIN sys.dm_db_missing_index_details ID Where ID.database_id = DB_ID(‘CycleCount’) 

 

    1. ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC
    2.                                                                                                                                                                                                                                On (ID.index_handle = IG.index_handle)
    3.                                                                                    On (IG.index_group_handle = GS.group_handle)
    4.              ID.inequality_columns, ID.included_columns, ID.statement
    5. SELECT avg_total_user_cost, avg_user_impact, user_seeks,
  • Índices Clusterizados e Índices Chaves

 

SELECT t.name, i.name, i.type_desc, c.name

FROM sys.tables t

INNER JOIN sys.indexes i

INNER JOIN sys.index_columns ic

INNER JOIN sys.columns c

ON ic.object_id = c.object_id AND ic.column_id = c.column_id

ON i.object_id = ic.object_id AND i.index_id = ic.index_id

ON t.object_id = i.object_id AND i.index_id = 1;

GO

 

  • Índices com colunas duplicadas   (select distinct object_name(i.object_id) tablename,i.name indexname,                                       from sys.index_columns ic1 inner join                                                              ic1.column_id=c.column_id                                           ic1.object_id=i.object_id and                                      order by index_column_id FOR XML PATH(”)),1,2,”)               where object_id=i.object_id and index_id=i.index_id) as columnlist        sys.index_columns ic on i.object_id=ic.object_id and            sys.objects o on i.object_id=o.object_id    (select distinct object_name(i.object_id) tablename,i.name indexname,                                       from sys.index_columns ic1 inner join                                                              ic1.column_id=c.column_id                                           ic1.object_id=i.object_id and                                     order by index_column_id FOR XML PATH(”)),1,2,”)               where object_id=i.object_id and index_id=i.index_id) as columnlist        sys.index_columns ic on i.object_id=ic.object_id and            sys.objects o on i.object_id=o.object_id        substring(t2.columnlist,1,len(t1.columnlist))=t1.columnlist and          (t1.columnlist=t2.columnlist and t1.indexname<>t2.indexname))

 

  1. Go
  2.        (t1.columnlist<>t2.columnlist or
  3. where o.is_ms_shipped=0) t2 on t1.tablename=t2.tablename and
  4.                                    i.index_id=ic.index_id inner join
  5.        from sys.indexes i inner join
  6.                from sys.index_columns ic
  7.                                            ic1.index_id=i.index_id
  8.                                      where ic1.index_id = ic.index_id and
  9.                                            sys.columns c on ic1.object_id=c.object_id and
  10.              (select distinct stuff((select ‘, ‘ + c.name
  11.      where o.is_ms_shipped=0) t1 inner join
  12.                                    i.index_id=ic.index_id inner join
  13.        from sys.indexes i inner join
  14.                from sys.index_columns ic
  15.                                            ic1.index_id=i.index_id
  16.                                      where ic1.index_id = ic.index_id and
  17.                                            sys.columns c on ic1.object_id=c.object_id and
  18.              (select distinct stuff((select ‘, ‘ + c.name
  19. select t1.tablename,t1.indexname,t1.columnlist,t2.indexname,t2.columnlist from

 

  • Operador Intersect

 

CREATE TABLE A ( x INT )

 

INSERT INTO A

VALUES ( 18 ),

( 20 ),

( 4 ),

( 1 ),

( 2 )

 

CREATE TABLE B ( Y INT )

 

INSERT INTO B

VALUES ( 1 ),

( 20 ),

( 2 ),

( 3 )

 

SELECT X AS ‘Intersecting’

FROM A

INTERSECT

SELECT Y

FROM B

 

  • Taxas de Fragmentação de Tabelas antes e depois do Rebuild

 

Select

object_name(ddips.object_id) [Table_name],

convert(decimal(5,2),isnull(ddips.avg_fragmentation_in_percent,0)) [Ext_frag],

ddips.page_count [Pages],

ddips.compressed_page_count

From sys.dm_db_index_physical_stats(db_id(), object_id(‘produtos’), null, null, ‘detailed’) ddips

  • Transações aninhadas

 

CREATE Table TranTable(col varchar(3))

GO

 

BEGIN TRAN A

INSERT INTO TranTable (col) Values (‘abc’)

BEGIN TRAN B

INSERT INTO TranTable (col) Values (‘def’)

SAVE TRAN B

BEGIN TRAN C

INSERT INTO TranTable (col) Values (‘ghi’)

COMMIT TRAN C

ROLLBACK TRAN B

INSERT INTO TranTable (col) Values (‘xyz’)

COMMIT TRAN A

GO

 

Select col from TranTable

GO

 

Drop TABLE TranTable

GO

 

  • Utilitário BCP para criação de arquivos XML

 

— Exemplo 1 —

 

DECLARE @FileName varchar(50),

@bcpCommand varchar(2000)

 

SET @FileName = ‘C:\Users\test.xml’

SET @bcpCommand = ‘bcp “SELECT

”CustomerID” AS ”Parameter/name”, CustomerID AS ”Parameter/value”,

”ContactName” AS ”Parameter/name”, ContactName AS ”Parameter/value”,

”CompanyName” AS ”Parameter/name”, CompanyName AS ”Parameter/value”

FROM Sales.Customers

FOR XML PATH(”T2Method”), ROOT(”Parking”), TYPE, ELEMENTS” queryout “‘

 

SET @bcpCommand = @bcpCommand + @FileName + ‘” -T -c -x’

 

print(@bcpCommand)

 

EXEC master..xp_cmdshell @bcpCommand

 

 

 

— Exemplo 2 —

Declare @Contador Int,

@FileName varchar(50),

@bcpCommand varchar(2000)

 

Set @Contador = 1

 

While @Contador <=(Select Count(IDdaSuaTabela) from SuaTabela)

Begin

 

SET @bcpCommand = ‘bcp “SELECT ”CustomerID” AS ”Parameter/name”, CustomerID AS ”Parameter/value”,

”ContactName” AS ”Parameter/name”,

ContactName AS ”Parameter/value”,

”CompanyName” AS ”Parameter/name”,

CompanyName AS ”Parameter/value”

FROM Sales.Customers

Where IDdaSuaTabela = @Contador

FOR XML PATH(”T2Method”), ROOT(”Parking”), TYPE, ELEMENTS” queryout “‘

 

SET @FileName = ‘C:\Users\test-arquivo-‘+Convert(Varchar(10),@Contador)+’.xml’

SET @bcpCommand = @bcpCommand + @FileName + ‘” -T -c -x’

 

EXEC master..xp_cmdshell @bcpCommand

 

Set @Contador += 1

End

Mais uma vez obrigado por sua visita, fique à vontade para compartilhar este material com seus contatos. Dúvidas, comentários, sugestões e críticas, envie seu comentário ou se preferir um e-mail.

Até mais.