#28 – Para que serve

Utilizando o operador relacional Pivot em combinação com querys dinâmicas, funções Stuff(), String_Agg() e muito mais…..


Olá pessoal, boa tarde!!!

E aí, estão no ritmo do carnaval? Se for igual eu tenho a certo que não, particularmente eu não sou muito adepto desta tradicional festa brasileira e nos últimos anos considerada mundial, mesmo assim vou fazer uso dela na escrita deste post para ajudar você a se sentir na avenida.

Que bom saber que mesmo neste feriadão que estamos vivendo você está dando uma passadinha aqui no meu blog, espero que esta visita possa ser muito proveitosa e enriquecedora.

No post de hoje, vou demonstrar um dos mais tracionais operadores relacionais existente no Microsoft SQL Server adicionado a partir da versão 2005, que nos permite utilizar como expressões de Table-Valued (Tabela Valorizada) dentro de outra tabela. Tenho a certeza que em algum momento você já deve ter feito uso deste operador.

Continue Lendo “#28 – Para que serve”

#26 – Para que serve

Conheça uma pequena introdução sobre os Níveis de Isolamento, destacando de forma prática o Read Uncommitted.


Olá, pessoal, bom dia.

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

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

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

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

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

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

Continue Lendo “#26 – Para que serve”

#22 – Para que serve


Oi, bom dia, tudo bem?

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

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

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

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

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

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

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

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


Introdução

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

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

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

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

Propriedade de Banco de Dados Page_Verify

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Criando o Ambiente

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

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

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

  • Database: TesteDatabasePageVerify;
  • Tables: TabelaPageVerify; e PageVerifyTempoDecorrido.

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

— Acessando o Banco de Dados TesteDatabasePageVerify —
Use TesteDatabasePageVerify
Go

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

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

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

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

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

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

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

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

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

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

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

Set @Position=Rand()*130

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

Set @RowCount = @RowCount – 1
End

Set @HoraFim=GetDate()

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

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

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

While (@RowCount >=1)
Begin

Set @Position=Rand()*130

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

Set @RowCount = @RowCount – 1
End

Set @HoraFim=GetDate()

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

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

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

While (@RowCount >=1)
Begin

Set @Position=Rand()*130

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

Set @RowCount = @RowCount – 1
End

Set @HoraFim=GetDate()

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

Set @Counter = @Counter + 1
End
Go

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


Referências

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

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

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

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

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

Links

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

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

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

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

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

Conclusão

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

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

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

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

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

Vai SQL Server, Vai SQL Server….

Agradecimentos

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

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

Um grande abraço e ótima semana.

Valeu.

Dica do Mês – SQL Server 2016 – Obtendo informações sobre o cache de execução de funções


Pessoal, boa tarde.

Tudo bem? Estou retornando com mais um post dedicado a sessão Dica do Mês, este é o post de número 10 dedicado de forma exclusiva a esta sessão.

Recentemente alguns dos leitores do meu blog, fizeram alguns comentários e sugestões pedindo para que eu fosse um pouco mais objetivo no conteúdo e procurando organizar melhor a estrutura do post.

Quero dizer a todos que sugestões, críticas, comentários, enfim tudo é sempre muito bem vindo e estou ainda procurando estabelecer um padrão. No post de hoje, já começo a apresentar um pouco do layout e organização que pretenso manter para os próximos posts.

Então vamos lá, seja bem vindo a mais um Dica do Mês!!!


Introdução

Um das principais atividades de um DBA (Database Administrator) é cuidar e selar para vida de seus servidores e bancos de dados. Mas por diversas situações em alguns momentos algo pode fugir do controle ou simplesmente começar a funcionar de uma maneira diferente da qual estava sendo executado.

Isso também pode acontecer com o Microsoft SQL Server e seus recursos programavéis, dentre eles aqueles que os desenvolvedores tendem a criar para atender necessidades específicas em seus projetos.

Para tais recursos como: Stored Procedure, Triggers, Functions o Microsoft SQL Server apresenta uma infinidade de funcionalidades que permitem a cada versão ou até mesmo atualização serem implementados de maneiras e formas distintas, situação muito comum de se encontrar.

O post de hoje trata justamente uma situação muito corriqueira de se encontrar dentro de um ambiente de banco de dados, no qual estamos fazendo uso de user functions e precisamos de alguma maneira descobrir se esta função esta gerando algum tipo de mudança de comportamento durante ou após sua execução, estou me referindo as chamadas estatísticas de execução.

Problema

Em diversos momentos temos a necessidade obter informações sobre as estatísticas de processamento por parte de um determinado recurso que o Microsoft SQL Server possa estar processando ou tenha sido processado, até o Microsoft SQL Server 2000 essa uma tarefa muito árdua ou praticamente impossível de ser realizada de maneira rápida, cenário que começou a mudar um pouco de comportamento a partir do Microsoft SQL Server 2005.

Solução

A partir da versão 2005 a Microsoft introduziu o conceito de visões de sistemas que permitiam coletar dados estatísticos de execução de querys, posteriormente melhorado no Microsoft SQL Server 2008 a partir da adição do recurso de DMV – Dynamic Management Views, onde tinhamos a possibilidade de rastrear e encontrar informações sobre Stored Procedures e Triggers, através das DMVs:

Onde respectivamente estas DMVs, permitiam obter informações sobre os dados de execução de Stored Procedures e Triggers executadas.

Agora na versão 2016 a Microsoft introduziu no novo SQL Server uma nova DMV, chamada sys.dm_exec_function_stats, que nos permite obter informações sobre os dados estatísticos de processamento e execucação de uma user function.

Vamos então colocar a “mão na massa” ou melhor nos teclados e executar o exemplo apresentando abaixo para que possamos entender como esta nova dmv poderá nos ajudar.

Exemplo

Para que possamos realizar este cenário de exemplo vou utilizar o Microsoft SQL Server 2016 Express em conjunto com o banco de dados de exemplo Microsoft SQL Server 2016 Adventure Works disponibilizados nos seguinte link: https://www.microsoft.com/download/details.aspx?id=49502

Dando continuidade vamos executar o primeiro bloco de código, denominado Bloco de Código 1.

— Bloco de Código 1 —

bloco-de-codigo-1

 

Não se precoupe com a lógica aplicada ao código, o importante é que você observe a existência de três user functions existentes dentro do banco de dados AdventureWorks2016, sendo elas:

  • dbo.ufnGetStock;
  • dbo.ufnGetProductDealerPrice; e
  • dbo.ufnGetProductStandardCost.

Agora que nosso bloco de código 1 foi executado o Microsoft SQL Server através do Database Engine em conjunto com Execution Plan, deve ter criado para nosso select e principalmente as functions utilizadas dentro um cache de execução contendo as informações sobre o processamento realizado e o quanto custou para executar cada function envolvida neste código.

E justamente neste momento que poderemos fazer uso da nova DMV sys.dm_exec_function_stats para obter os principais indicadores estatísticos coletados através do cache criado pelo SQL Server com base no bloco de código 1. Vamos então executar o Bloco de Código 2 apresentado abaixo.

— Bloco de Código 2 —

bloco-de-codigo-2

Observe que além da sys.dm_exec_function_stats estamos utilizando em conjunto a sys.dm_exec_sql_text que nos permite obter mais detalhes sobre o código do objeto programado executado, neste caso as functions apresentadas anteriormente.

A Figura 1 apresentada abaixo ilustra a relação de dados estatísticos armazenando no cache de execução do database engine e coletados através da  sys.dm_exec_function_stats:

figura1-sys-dm_exec_function_statsFigura 1 – Dados estatísticos de processamento das functions utilizadas no bloco de código 1.

Referências

Conclusão

Como de costume a cada nova versão ou atualização a Microsft esta apresentando diversas inovações e melhorias no Microsoft SQL Server.

Isso não foi diferente na versão 2016 que agora através da nova DMV sys.dm_exec_function_stats nos permite obter informações estatísticas de processamento de nossas functions armazenadas em cache.

Desta forma, temos a possibilidade de analisar estes dados e permitir ter uma melhor análise de processamento por parte das aplicações que necessitam fazer uso de functions, com certeza este recurso será muito importante e de extrema utilidade para qualquer profissional da área de banco de dados e desenvolvimento.

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.

MICROSOFT ALERTA PARA O FIM DO SUPORTE DO SQL SERVER 2005 EM ABRIL


A Microsoft confirmou no site do SQL Server o fim do suporte do SQL Server 2005 no dia 12 de abril de 2016. Este prazo também afeta a versão Express, que é gratuita.

O SQL Server 2005 está atualmente na fase de suporte estendido e depois desta data ele deixará de receber novas atualizações de segurança.

Microsoft alerta para o fim do suporte do SQL Server 2005 em abril

A Microsoft confirmou no site do SQL Server o fim do suporte do SQL Server 2005 no dia 12 de abril de 2016. Este prazo também afeta a versão Express, que é gratuita

FIM DO SUPORTE DO SQL SERVER 2005

Empresas que ainda estão rodando a versão 2005 terão que migrar para uma versão mais recente e suportada do SQL Server se quiserem manter seus ambientes seguros.

Com o fim do suporte do SQL Server 2005, ele não receberá mais nenhuma atualização de segurança. Com isso ele ficará vulnerável a ataques caso novas vulnerabilidades sejam descobertas depois do dia 12 de abril de 2016.

De acordo com a Microsoft, o SQL Server 2014, que é versão mais recente, é 13 vezes mais rápido do que a versão 2005.

Para quem está preocupado com o fim do suporte do SQL Server 2005, a Microsoft colocou no ar uma página com informações úteis para empresas e profissionais de TI.

Disponível aqui, a página oferece informações sobre o upgrade, link para download da versão de avaliação do SQL Server 2014, link para download do SQL Server 2014 Express, link para o Microsoft Assessment and Planning Toolkit, e mais.

Fontes e Direitos Autorais: Baboo.com – SID VICIOUS @ 3 MAR 2016 | 3:55 PM

Material de Apoio – Maio – 2013


Galera, boa tarde.

Como de costume vou compartilhar com vocês  a relação de Scripts e Códigos de Exemplo, como Material de Apoio para o SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 e 2012.

Na relação de hoje, vocês vão encontrar exemplos sobre:

  • Alteração de Collate em Tables;
  • Alteração de Collate em Colunas;
  • Buffers;
  • Criação de Stored Procedure;
  • Comando IF;
  • Comparação de Dados entre Tables;
  • Common Table Expression
  • Função Substring;
  • PadIndex;
  • Pools de Memória;
  • Tempo de Processamento, etc.

Sendo assim, a seguir disponibilizo a relação de arquivos, renomeados com a extensão .doc para manter a compatiblidade com o WordPress, bastando somente remover esta extensão para poder utilizar diretamente no Management Studio.

Segue abaixo os arquivos:

 

Pois bem, esta aqui a relação de arquivos, espero que possam ser úteis e venham a ajudar em suas atividades profissionais e acadêmicas.

Mais uma vez obrigado por sua visita, nos encontramos em breve.

Até mais.

Short Scripts – Abril – 2013 – Especial – DMVs


Salve, Salve Galera….

Hahahaha, até parece nome de novela, mas não é não, muito pelo contrário, como de costume de volta com mais uma relação de Short Scripts.

Como vocês deve saber, esta sessão tem como objetivo, disponibilizar a compartilhar, pequenos Códigos de Exemplo e Scripts que podem ajudar, demonstrar ou server como base para obtenção de informações em uma Instância ou Servidor Microsoft SQL Server 2005, 2008, R2 e 2012.

Nesta relação vocês vão encontrar somente Short Scripts que trabalham com DMVs (Dynamic Management Views – Visões de Gerenciamento Dinâmico), criadas a partir do Microsoft SQL Server 2005, trata-se de uma relação espacial somente de DMVs.

Muito bem, vamos a relação de Short Scripts – DMVS:

1 — Analisar o Auto Page Repair History introduzido a partir do SQL Server 2008 —
SELECT DB_NAME(database_id) AS [database_name] ,
database_id,
file_id,
page_id,
error_type,
page_status,
modification_time
FROM sys.dm_db_mirroring_auto_page_repair;

2 — Identificar e apresentar Transactions com Longo tempo de Execução —

SELECT os.task_address,
os.[state],
os.last_wait_type,
clr.[state],
clr.forced_yield_count
FROM sys.dm_os_workers AS os INNER JOIN sys.dm_clr_tasks AS clr
ON ( os.task_address = clr.sos_task_address );

 

3 — Total de Buffers utilizados por Banco de Dados —

 

SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8 / 1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4
AND database_id <> 32767
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC;

4 — Breaks down Buffers por objetos tabelas e índices dentro do Pool Buffer —

SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName],
p.index_id,
COUNT(*) / 128 AS [Buffer size(MB)],
COUNT(*) AS [Buffer_count]
FROM sys.allocation_units AS a INNER JOIN sys.dm_os_buffer_descriptors                                                                 AS b                                              ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p ON a.container_id = p.hobt_id
WHERE b.database_id = DB_ID()
AND p.[object_id] > 100
GROUP BY p.[object_id],
p.index_id
ORDER BY buffer_count DESC;

4 — Identificando o número de itens em diferentes partes do Buffer Cache —

SELECT name,
[type],
entries_count,
single_pages_kb,
single_pages_in_use_kb,
multi_pages_kb,
multi_pages_in_use_kb
FROM sys.dm_os_memory_cache_counters
WHERE [type] = ‘CACHESTORE_SQLCP’
OR [type] = ‘CACHESTORE_OBJCP’
ORDER BY multi_pages_kb DESC;

5 — Obtendo informações básicas sobre Memória e Status de Memória no Microsoft SQL Server 2008 e R2 —

SELECT total_physical_memory_kb,
available_physical_memory_kb,
total_page_file_kb,
available_page_file_kb,
system_memory_state_desc
FROM sys.dm_os_sys_memory ;

6 — Obtendo a média de tarefas e tarefas em execução —

SELECT AVG(current_tasks_count) AS [Avg Task Count],
AVG(runnable_tasks_count) AS [Avg Runnable Task Count]
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
AND [status] = ‘VISIBLE ONLINE’;

 

Bom, vou chegando ao final da mais esta relação de Short Scripts, sendo esta especial contendo somente Códigos que envolvam DMVs.

Mais uma vez obrigado por sua visita, nos encontramos em breve.

Até mais.

Utilizando a Ferramenta Surface Area Configuration no Microsoft SQL Server 2008 R2 através de Política de Gerenciamento.

Hoje gostaria de destacar para vocês uma das maiores funcionalidades adicionados ao Microsoft SQL Server, conhecida como Surface Area Configuration, presente desde a versão 2005, mas que na versão 2008 acabou sendo integrada a uma nova funcionalidade denominada Política de Gerenciamento ou Policy Management.


Olá pessoal, tudo bem com vocês?

Hoje gostaria de destacar para vocês uma das maiores funcionalidades adicionados ao Microsoft SQL Server, conhecida como Surface Area Configuration, presente desde a versão 2005, mas que na versão 2008 acabou sendo integrada a uma nova funcionalidade denominada Política de Gerenciamento ou Policy Management.

Presente no Microsoft SQL Server 2005, a Surface Area Configuration, foi introduzida como uma ferramenta que permitia ao administrador visualizar e gerenciar o estado de inicialização dos serviços instalados no Microsoft SQL Server 2005, independente da quantidade de instâncias instaladas.

Administrando servidores com Gerenciamento Baseado em Políticas

A partir do Microsoft SQL Server 2008, a Microsoft introduziu um novo conceito de gerenciamento e administração de recursos e funcionalidades de instâncias SQL Server, através do próprio Management Studio, como também, utilizando o Policy Management, sendo este, composto por um conjunto de configurações de políticas de segurança para tornar ainda mais segura o processo de administração de qualquer Servidor Microsoft SQL Server.

Gerenciamento Baseado em Políticas é um sistema para gerenciar uma ou mais instâncias do SQL Server 2008. Quando os administradores de políticas do SQL Server usam o Gerenciamento Baseado em Políticas, eles usam o SQL Server Management Studio para criar políticas para gerenciar entidades no servidor, como as instâncias do SQL Server, bancos de dados ou outros objetos do SQL Server.

O Gerenciamento Baseado em Políticas tem três componentes:

  • Gerenciamento de política: Os administradores de políticas criam políticas.
  • Administração explícita: Os administradores selecionam um ou mais destinos gerenciados e verificam explicitamente se eles estão de acordo com uma política específica, ou explicitamente fazem com que os destinos estejam de acordo com uma política.
  • Modos de avaliação: Há quatro modos de avaliação, três dos quais podem ser automatizados:
  1.                                                                i.      Sob demanda. Este modo avalia a política quando especificado diretamente pelo usuário.
  2.                                                              ii.      Ao alterar: impedir. Esse modo automatizado usa gatilhos DDL para impedir violações de política.
  1.                                                           iii.      Ao alterar: log apenas. Este modo automatizado usa a notificação de eventos para avaliar uma política quando uma alteração relevante é feita.

 

  1.                                                            iv.      Ao agendar. Este modo automatizado usa um trabalho do SQL Server Agent para avaliar periodicamente uma política.

Foi a partir deste novo contexto que o Surface Area Configuration deixou de ser utilizado como uma ferramenta externa e passou a ser integrado a este novo ambiente, mas de forma discreta e praticamente desconhecida por boa parte dos profissionais.

A meu ver um ponto falho em que a Microsoft subestimou o conhecimento e uso desta ferramenta por parte dos profissionais, conhecedores, especialistas e amantes do Microsoft SQL Server. Mas independente de qualquer opinião, esta ferramenta ainda esta presente, o que nos fortalece a possibilidade de fazermos uso de seus recursos.

Termos e conceitos do Gerenciamento Baseado em Políticas

  • Destino gerenciado pelo Gerenciamento Baseado em Políticas: As entidades gerenciadas pelo Gerenciamento Baseado em Políticas, como uma instância do Mecanismo de banco de dados do SQL Server, um banco de dados, uma tabela ou um índice. Todos os destinos em uma instância de servidor formam uma hierarquia de destino. Um conjunto de destino é aquele resultante da aplicação de um conjunto de filtros de destino à hierarquia de destino.
  • Faceta do Gerenciamento Baseado em Políticas: Um conjunto de propriedades lógicas que modelam o comportamento ou as características de certos tipos de destinos gerenciados. O número e as características das propriedades são incorporados à faceta e só podem ser adicionados ou removidos pelo criador da faceta. Um tipo de destino pode programar uma ou mais facetas de gerenciamento, e uma faceta de gerenciamento pode ser implementada por um ou mais tipos de destino. Algumas propriedades de uma faceta só podem ser aplicadas a uma versão específica. Por exemplo, a propriedade Database Mail da faceta Configuração da Área da Superfície só se aplica ao SQL Server 2005 e versões posteriores.
  • Condição de Gerenciamento Baseado em Políticas: Uma expressão booleana que especifica um conjunto de estados permitidos de um destino gerenciado pelo Gerenciamento Baseado em Políticas em relação a uma faceta de gerenciamento.
  • Política de Gerenciamento Baseado em Políticas: Uma condição do Gerenciamento Baseado em Políticas e o comportamento esperado, como, por exemplo, modo de avaliação, filtros de destino e agenda. Uma política só pode conter uma condição. As políticas podem ser habilitadas ou desabilitadas.
  • Categoria de política do Gerenciamento Baseado em Políticas: Uma categoria definida pelo usuário para ajudar a gerenciar políticas. Os usuários podem classificar as políticas em categorias diferentes. Uma política pertence a somente uma categoria. Categorias de políticas se aplicam a bancos de dados e servidores. No nível de banco de dados, as seguintes condições se aplicam:
  • Os proprietários de banco de dados podem assinar um conjunto de categorias de política para um banco de dados.
  • Somente as políticas das categorias assinadas podem governar um banco de dados.
  • Todos os bancos de dados assinam implicitamente a categoria de política padrão.

No nível de servidor, é possível aplicar categorias de políticas a todos os bancos de dados.

Política efetiva: As políticas efetivas de um destino são aquelas que governam esse destino. Uma política só será efetiva em relação a um destino se todas as seguintes condições forem satisfeitas:

  • A política está habilitada.
  • O destino pertence ao conjunto de destino da política.
  • O destino ou um dos destinos ancestrais assina o grupo de políticas que contém essa política.

Bom, após este pequeno overview sobre o Policy Management, seus termos, conceitos e formas de administração, vou destacar um pouco de como o Surface Area Configuration é definido no Microsoft SQL Server 2008 R2.

Compreendendo o Surface Area Configuration

Na configuração padrão de novas instalações do SQL Server, muitos recursos não estão habilitados. O SQL Server instala de forma seletiva e inicia somente os principais serviços e recursos, para minimizar o número de recursos que podem ser atacados por um usuário mal-intencionado.

Um administrador de sistema pode alterar esses padrões no momento da instalação e também seletivamente habilitar ou desabilitar recursos de uma instância em execução do SQL Server. Além disso, alguns componentes podem não estar disponíveis ao estabelecer conexão a partir de outros computadores até que os protocolos sejam configurados.

Importante destacar, que ao contrário das novas instalações, nenhum serviço ou recurso existente é desativado durante uma atualização, mas as opções adicionais de configuração da área da superfície poderão ser aplicadas após a conclusão da atualização.

Utilizando o Surface Area Configuration de forma básica

Para trabalharmos com o Surface Area Configuration no Management Studio é bastante simples, seguindo os passos apresentados abaixo:

A habilitação e desabilitação de recursos do SQL Server podem ser configuradas usando as facetas no SQL Server Management Studio. Para configurar área da superfície usando as facetas:

  1. No Management Studio, conecte-se a sua instância do SQL Server.
  2. No Pesquisador de Objetos, clique com o botão direito do mouse no servidor e clique em Facetas, conforme apresenta a Figura 1.

Figura 1 – Opção Facetas no Management Studio.

3. Na caixa de diálogo Exibir Facetas, expanda a lista Faceta e selecione a faceta Configuração da área da superfície apropriada, conforme apresenta a Figura 2. Por padrão estas facetas estão definidas em três categorias:

    1. Configuração       da área da superfície;
    2. Configuração       da área da superfície para o Analysis Services; e
    3. Configuração da área da superfície para o Reporting Services.

Figura 2 – Tela de Configuração das Facetas de acordo com a categoria selecionada.

A seguir apresenta somente as opções disponíveis para faceta Surface Area Configuration, conforme ilustra a Figura 3:

Figura 3 – Relação de Opções disponíveis para faceta Surface Area Configuration.

  1. Na área Propriedades da faceta, selecione os valores desejados para cada propriedade. Ou se preferir selecione, por exemplo, a Faceta DatabaseMailEnabled.
  2. Clique em OK.

Pronto, desta forma simples acabamos de realizar a ativação de uma faceta no Microsoft SQL Server 2008 R2, que posteriormente estará sendo refletido no comportamento e configuração de sua instância.

Algo que pode parecer muito similar a utilização da System Stored Procedure SP_Configure, quando estamos se referindo a configuração de recursos específicos do nosso servidor como no procedimento que realizamos anteriormente, executando o processo de ativação do Database Mail, que pode ser feito de ambas as formas, tanto por SP_Configure, como também por configuração de Facetas em Nível de Servidor.

Combinando o uso do Surface Area Configuration com a SP_Configure

Como destacado anteriormente a configuração de algumas facetas disponíveis através do Policy Management, com base no Surface Area Configuration pode ser comparado e até mesmo utilizado como alternativa á System Stored Procedure SP_Configure.

Pois bem, por procurar destacar como podemos através de cada um destes recursos fazer uso de funcionalidades do Microsoft SQL Server 2008 R2, como por exemplo, ativar o uso da Extended Stored Procedure XP_CMDShell. Começando pelo uso da Stored Procedure para ativação da XP_CMDShell, conforme apresentado abaixo:

  1. 1.       Ativando a XP_CMDShell e consultando seu status através das Facetas de Servidor:

Por se tratar de uma funcionalidade externa e também um recurso que pode possibilitar o acesso indevido a um usuário mal intencionado o Microsoft SQL Server, por padrão mantém este recurso oculto, bem como, desativado. Vamos então realizar o processo de ativação desta funcionalidade e posteriormente consultar o seu status através da Faceta, conforme o código apresentado abaixo:

sp_configure ‘ xp_cmdshell’,1

reconfigure with override

Após executar este pequeno bloco de código, vamos consultar o valor atribuído para Faceta Surface Area Configuration em sua propriedade DatabaseMailEnabled, utilizando os passos realizados anteriormente. Para ilustrar o valor da propriedade DatabaseMailEnabled, apresento a Figura 4:

Figura 4 – Propriedades disponíveis para faceta Surface Area Configuration, em especial a propriedade DatabaseMailEnabled.

Como já esperado ao executar a SP_Configure, o propriedade DatabaseMailEnabled, teve o seu valor alterado de False para True, o que representa que acabamos de alterar o seu valor e ativar esta funcionalidade para nossa instância de banco de dados.

O próximo passo é realizar justamente o mesmo procedimento, só que de forma inversa, alterando o valor na propriedade e consultado o seu status através da SP_Configure.

  1. Desativando a XP_CMDShell e consultando seu status através da SP_Configure:

Como a tela de Visualização de Facetas já esta aberta e também com a categoria Surface Area Configuration selecionada, vamos alterar o valor da propriedade DatabaseMailEnabled, alterando o seu valor de True para False e depois clicando no botão Ok.

Agora vamos executar o bloco de código apresenta abaixo para obter o retorno da alteração do status da propriedade DatabaseMailEnabled:

sp_configure ‘xp_cmdshell’

Ótimo, nossa alteração foi realizada de forma correta e neste momento a XP_CMDShell voltou ao seu status padrão, ou seja, esta desativada, conforme podemos observar a Figura 5, com base na valor apresentado na coluna Run_Value, retornado como zero (Zero), o que indica pelo SQL Server que o recurso, funcionalidade e propriedade esta desativada:

Figura 5 – Resultado da execução da System Stored Procedure SP_Configure.

Utilizando o Surface Area Configuration de forma avançada

Nossa última jornada será realizar a configuração de uma nova política disponível para o Surface Area Configuration, com base, no conjunto de condições (conditions) e facetas (facets) disponíveis no Policy Management (Gerenciador de Políticas).

Vamos então configurar uma nova Condição, conforme os passos apresentados abaixo:

  1. Conecte-se ao seu Servidor ou Instância SQL Server;
  2. No Explorador de Objetos (Object Explorer), navegue até o Menu Gerenciamento (Management);
  3. Selecione a opção Condições (Conditions), clicando com o botão da direita do mouse e escolhendo a opção Nova Condição (New Condition);
  4. Como base na Figura 6, realize a configuração da sua nova condição, utilizando os seguintes valores:
    1. Name: Condição – Ativar – AdHocRemoteQueries;
    2. Facet: Surface Area Configuration;
    3. Expression:
      1.                                                                           i.      Field: @ADHocRemoteQueries
      2.                                                                          ii.      Operator: =
      3.                                                                        iii.      Value: True
  5. Clique no botão Ok, para confirmar a criação desta nova condição.

Figura 6 – Tela de Configuração da Condição – Ativar – AdHocRemoteQueries.

Pronto, nossa nova condição esta configurado e criada, agora devemos configurar nossa nova política que estará fazendo uso desta condição e de sua faceta. Para isso, vamos realizar os seguintes passos:

  1. Utilizando o menu Management aberto, clique como o botão da direita sobre a opção Políticas (Policies), selecionando a opção Nova Política (New Policy);
  2. Como base na Figura 7, realize a configuração da sua nova condição, utilizando os seguintes valores:
    1. Name: Política – Ativando – AdHocRemoteQueries;
    2. Check Condition: Condição – Ativar – AdHocRemoteQueries;
    3. Evalution Mode: On Change: Log Only;
    4. Server Restriction: None; e
    5. Marque a opção Ativo (Enabled).
  3. Clique no botão Ok, para confirmar a criação desta nova política.

Figura 7 – Tela de Configuração da Política – Ativando – AdHocRemoteQueries.

Muito bem, nossa política esta criada e definida para ser executa somente quando o log de eventos sofre algum tipo de mudança que possa estar relacionada com esta política. Mas caso você queria testar ou avaliar a execução desta política, basta clicar com o botão da direita do mouse sobre a ela e escolher a opção Evaluate(Avaliar).

Observação: Vale ressaltar que estes procedimentos podem somente ser executados por usuários com permissões de nível de System Administrator, Security Administrator, Server Administrator ou Setup Administrator.

Itens relacionados

A seguir apresenta a relação de Stored Procedures que podem ser utilizadas com as Políticas de Gerenciamento

sp_syspolicy_add_policy_category   sp_syspolicy_rename_policy_category  
sp_syspolicy_add_policy_category_subscription   sp_syspolicy_repair_policy_automation
sp_syspolicy_configure sp_syspolicy_set_config_enabled
sp_syspolicy_delete_policy_category   sp_syspolicy_set_config_history_retention
sp_syspolicy_delete_policy_category_subscription   sp_syspolicy_set_log_on_success
sp_syspolicy_delete_policy_execution_history   sp_syspolicy_subscribe_to_policy_category
sp_syspolicy_purge_health_state   sp_syspolicy_unsubscribe_from_policy_category
sp_syspolicy_purge_history sp_syspolicy_update_policy_category
sp_syspolicy_rename_condition sp_syspolicy_update_policy_category_subscription
sp_syspolicy_rename_policy

Perfeito pessoal acredito ter conseguido cumprir o meu objetivo e mostrar de uma forma bastante simples como podemos utilizar o Surface Area Configuration dentro do Microsoft SQL Server 2008 R2.

Espero que você possa ter gostado, mais uma vez agradeço a sua visita, o seu interesse o meu blog, conto com sua participação.

Nos vemos em breve.

Um grande abraço, até a próxima

Conhecendo a Stored Procedure não documentada MSunmarkreplinfo

Dica – Conhecendo a Stored Procedure não documentada MSunmarkreplinfo


Galera, bom dia.

Tudo bem?

A dica de hoje tem o objetivo de apresentar uma das diversas Stored Procedures existentes no SQL Server, mas que não possuem documentação oficial por parte da Microsoft, estou se referindo a MSunmarkreplinfo, que recentemente acabei conhecendo e utilizando.

Para apresentar mais sobre esta Stored Procedure, vou contar um pouco do que aconteceu e como consegui encontrar esta relíquia.

Nos últimos dias um dos meus clientes solicitou a configuração de uma estrutura de Replicação Transacional para suas bases de dados, até ai sem problemas, todo processo de configuração do Publisher e Subscriber realizado normalmente, especificando os artigos que seriam replicados entre os servidores, horário de replicação, filtros, entre outros detalhes.

Pois bem, durante alguns dias o processo de replicação funcionou sem problemas, mas no último final de semana, um dos programadores da empresa tinha a necessidade de aplicar algumas atualizações ao ambiente, dentre os elementos que seriam atualizados uma das bases de dados que estava envolvida na replicação seria envolvida nesta atualização.

Dentre os procedimentos padrões definidos pelo desenvolvedor para aplicar as atualizações consistia em realização do backup e posteriormente fazer um detach do banco de dados, foi justamente neste ponto que os problemas começaram a aparecer!!!

Não sei exatamente por qual motivo mas somente realizando o detach do banco de dados as atualizações na aplicação e banco eram aplicadas corretamente e o ambiente voltava a funcionar. Em conversa com o programar informei que poderíamos manter a estrutura de replicação e no processo de detach realizar alguns procedimentos específicos para os bancos envolvidos no processo de replica. Mas como santo de casa não faz milagre, o cara ficou louco, e disse que desta forma não poderia mais trabalhar, então acabou sobrando pra mim, você já podem imaginar o que aconteceu. Tive que remover todo processo de replicação para este banco de dados, ai que vem o pior, durante o processo de remoção da estrutura de replicação o servidor sem qualquer motivo específico travou.

Com certeza vocês podem imaginar alguns dos principais problemas durante um processo de reconfiguração de um ambiente o servidor travar, mas para minha felicidade ou infelicidade o banco de dados, não sofreu maiores problemas, mas o SQL Server não conseguiu remover o status das tabelas envolvidas na replicação, como sendo artigos replicados, desta forma, todo e qualquer processo de alteração na estrutura ou até mesmo exclusão das tabelas não poderiam ser realizados.

Nesta momento, eu consegui entender que o SQL Server ainda estava trabalhando com a replicação mesmo que toda estrutura tivesse sido removida, o que naquele momento poderia dizer que exista então um processo de replicação fantasma, conhecida como Ghost Replication, algo muito comum de se acontecer quando realizamos alterações nas configurações de uma replicação transacional e ocorrem falhas no ambiente.

Mesmo assim, sabendo da possível existência da Ghost Replication, o programador, tentou excluir a tabela ou até mesmo realizar algumas alterações em sua estrutura, foi neste momento que ao realizar por exemplo um simples Drop Table ou Alter Table, o SQL Server apresentava a seguinte mensagem:

Unable to drop a table even after removing replication. Getting the following errors, when try to drop or alter the table:
Server: Msg 3724, Level 16, State 2, Line 1
Cannot drop the table ‘t1’ because it is published for replication.
Server: Msg 4931, Level 16, State 1, Line 1
Cannot add columns to table ‘t1’ because it is being published for merge replication.

Básicamente a mensagem informava que era impossível realizar a exclusão ou alterações na tabela pois a mesma estava envolvida em um processo de replicação.

Ao entrar em contato comigo e alertar sobre esta situação, o programador destacou a impossibilidade de manter o ambiente em funcionamento sem poder aplicar as novas atualizações.

Foi justamente com base nesta situação e sabendo que o status das tabelas envolvidas na replicação não haviam sido alterados, comecei a realizar algumas tentativas de alteração direta nas tabelas e visões de catálogo de sistema existentes no SQL Server 2005, trabalhando diretamente com a Sys.Objetcs e Sys.SysObjects.

Dentre as diversas tentativas, tentei alterar as colunas ReplInfo e Is_Published existentes nas respectivas tabelas e visões de catálogo, mas para minha raiva não consegui realizar a alteração. O SQL Server retornava uma mensagem informando que existia dependência entre estes objetos de sistemas o que impossibilitava esta alteração.

Lógicamente neste instante o que me restava era tentar de alguma forma buscar mais informações, através do Books On-Line, como também, utilizando a própria Internet. Em ambos as alternativas praticamente encontrei os mesmos exemplos, procedimentos, dicas e sugestões. Mas como a esperança é a última que morre comecei a buscar mais informações nos Fóruns MSDN e TechNet nos Estados Unidos e foi justamente neste locais que encontrei informações e exemplos de como utilizar MSunmarkreplinfo.

Não encontrei muita coisa sobre esta Stored Procedure, mas o pouco que encontrei foi o suficiente para conseguir resolver o meu problema, e justamente esta solução que vou compartilhar com vocês, conforme o exemplo apresentado a seguir:

 — Exemplo: Utilizando a MSunmarkreplinfo para remover o Status de tabela envolvida em replicação —

SET NOCOUNT ON
DECLARE @tablename NVARCHAR(128), @RC INT

DECLARE curTable CURSOR FOR
SELECT [name] AS tbl FROM sys.tables

OPEN curTable
FETCH NEXT FROM curTable INTO @tablename

 WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE @RC = dbo.sp_MSunmarkreplinfo @tablename
FETCH NEXT FROM curTable INTO @tablename
END

 CLOSE curTable
DEALLOCATE curTable
GO

Com o código apresentado anteriormente consegui alterar de uma forma forçada as colunas ReplInfo e Is_Published na tabela de catálogo de sistema Sys.Objects e utilizada também pela Sys.SysObjects, desta forma, o programador já poderia realizar todas as alterações desejadas, mecher na estrutura das tabelas, como também, executar qualquer outro tipo de procedimento em seu ambiente.

Sendo assim, tudo voltou ao normal, mas o processo de replicação não pode mais ser configurado a este ambiente, fazendo-se necessário adotar outros processos de disponibilidade dos dados entre os servidores da empresa.

Espero que esta dica possa ter ilustrado um pouco de como é possível utilizar Stored Procedures não documentadas em algumas situações, vale ressaltar que qualquer procedimento desconhecido deve ser analisado e realizado em ambientes de teste.

Agradeço a sua visita, nos encontramos em breve.

Até mais.

Troubleshooting no SQL Server 2008 com Extendeds Events – Parte 2.

Dica – Troubleshooting no SQL Server 2008 com Extendeds Events – Parte 2.


Pessoal, boa tarde.

Alguma novidade?

Estou de volta conforme o prometido para dar continuidade na minha nova série sobre Extendeds Events, disponibilizados pela Microsoft no SQL Server a partir da versão 2005 e aperfeiçoado de diversas formas no SQL Server 2008.

Na primeira parte deste artigo destaquei alguns dos conceitos relacionados aos Eventos Estendidos, nesta segunda parte, vou continuar apresentando estes conceitos e apresentar como podemos começar a consultar informações através de algumas DMV´s (Visões de Gerenciamento Dinâmico).

—————————————————————————————————————-

Além dos Eventos e Predicados utilizados pelos Extendeds Events, podemos nos deparar com as Actions(Ações).


O que é uma Ação?

As ações são identificadas pelo SQL Server como um conjunto de comandos executados de forma síncrona, antes de um
evento ser consumido.


O que um payload?

Podemos definir um payload, como um conjunto de informações que identificam um evento durante sua execução. As ações executadas para cada evento, normalmente adicionam mais informações ao payload do evento, como um plano de execução, por exemplo. O que proporciona ao SQL Server armazenar um conjunto de informações sobre payload e ações relacionadas ao evento.

Agora que já sabemos o que significa Ações e Payload, vamos conhecer outro conceito, muito importante para os Extendeds Events, chamado Destino(Target).
O que é um Destino?
Os Destino (Target) são reconhecidos como uma maneira de consumir os eventos. Podemos consumir qualquer evento (ou descartar o mesmo caso não haja o que consumir no evento). Os Targets podem consumir eventos de forma síncrona (o código que disparou o evento espera o evento ser consumido) ou de forma assíncrona.

Outros conceitos – Pacotes(Packages): 
Conhecidos como um container que define objetos de eventos extendidos (eventos, ações, destinos). Os Pacotes são encontrados dentro dos módulos (DLLs, executáveis), conforme apresenta a Figura 1 abaixo.
 Figura 1 – Pacotes e sua Estrutura: Módulos, Pacotes e Objetos.

Outros conceitos – Sessões(Sessions):
Maneira de “conectar” objetos de eventos extendidos para processamento (um evento com uma ação para ser consumido por um destino.

Outros conceitos – Mapas(Maps):
Permitem que o usuário entenda o sentido do valor, fornecendo descrições sobre os valores internos. Os mapas contêm tudo desde estados do broker até os wait types, e incluem até o canal e palavra-chave para categorizar eventos em seus respectivos grupos.
Bom agora que já conhecimentos todos os principais conceitos relacionados diretamente com os Eventos Estendidos, vou apresentar como podemos consultar informações sobre cada conceitos, através das Visões de Gerenciamento Dinâmico criadas a partir do SQL Server 2005, conforme apresenta a Tabela 1 abaixo:
Agora que já sabemos as Visões de Gerenciamento Dinâmico envolvidas com o Eventos Estendidos, vou apresentar a seguir como podemos consultar cada uma destas DMVs, conforme os códigos apresentados abaixo:

Código 1 – Obtendo a relação de Eventos:

SELECT xp.[name], xo.*

FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp

WHERE xp.[guid] = xo.[package_guid]

AND xo.[object_type] = ‘event’

ORDER BY xp.[name];

 

Código 2 – Obtendo a relação de Payloads de um Evento:

SELECT * FROM sys.dm_xe_object_columns

WHERE [object_name] = ‘sql_statement_completed’;

GO

 

Código 3 – Obtendo a relação de Predicados de um Evento:

SELECT * FROM sys.dm_xe_objects

WHERE object_type in (‘pred_compare’, ‘pred_source’)

ORDER BY name

 

Código 4 – Obtendo a relação de Ações relacionadas a um Pacote:

SELECT xp.[name], xo.*

FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp

WHERE xp.[guid] = xo.[package_guid]

AND xo.[object_type] = ‘action’

ORDER BY xp.[name];

 

Código 5 – Obtendo a relação de Destinos de um Pacote:

SELECT xp.[name], xo.*

FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp

WHERE xp.[guid] = xo.[package_guid]

AND xo.[object_type] = ‘target’

ORDER BY xp.[name];

 

Código 6 – Obtendo a relação de Sessões:

SELECT * FROM sys.dm_xe_sessions;

 

Código 7 – Obtendo a relação de Mapas:

SELECT name, map_key, map_value FROM sys.dm_xe_map_values

ORDER BY name, map_key

Muito bem, chegamos ao ponto final desta segunta parte, todos os principais conceitos foram apresentados, as Visões de Gerenciamento Dinâmico utilizadas para cada conceito também foram descritas, como também os códigos para utilizar, consultar e retornar informações sobre estes conceitos e suas principais informações.
Agradeço mais uma vez a sua visita, nos encontramos em breve na terceira parte, onde vou apresentar os cenários e soluções que podemos trabalhar com os Extendeds Events.
Mais uma vez obrigado.
Até mais.

Troubleshooting no SQL Server 2008 com Extendeds Events – Parte 1.

Troubleshooting no SQL Server 2008 com Extendeds Events – Parte 1.


Pessoal, boa tarde.

Tudo bem com você?

Após um período de reflexão, inatividade e um pouco de cuidados com a minha saúde, estou de volta com minhas atividades, principalmente em relação ao meu blog.

Neste últimos dias andei pensando qual poderia seria ser a nova série de assuntos que gostaria de abordar em meu blog, pesquisei muito na Internet, vasculhei também meus Scripts e não poderia deixar de consultar o fantástico Books On-Line.

Muito bem, repleto de ideias e pensamentos, como também curioso para aprender algo novo, vou começar hoje a apresentar um pouco sobre os chamados Extendeds Events, mais conhecidos como Eventos Estendidos. De que forma este recurso pode ser utilizado no SQL Server 2008 como uma ferramenta ou técnica para Troubleshooting.

Antes de querer apresentar algum tipo de código, cenários, formas de uso, vou tentar explicar inicialmente o que estes eventos estendidos representam para o SQL Server e também para o próprio Windows.

Troubleshooting SQL Server 2005?

Apesar do SQL Server 2005 ter aumentado a gama de recursos para troubleshooting ainda havia alguns problemas com tais
ferramentas. Os comandos DBCC precisam ter seu resultado armazenado em algum local, como uma tabela, para posterior análise, e utilizar o SQL Trace/SQL Server Profiler pode consumir muitos recursos do servidor, principalmente se não utilizados com os devidos cuidados.

Ainda no SQL Server 2005:  As DMVs apresentam uma série de informações internas do SQL Server. Parte das informações já podiam ser obtidas através de DBCCs, só que agora de forma mais fácil e completa. Outras possibilidades seriam utilizar os DDL Triggers e também os Event Notifications.

Justamente para tentar melhorar e possibilitar outras formas de resolução de problemas que o Extendeds Events foram criados.

Para isso vamos começar com a seguinte pergunta.

O que seriam Extendeds Events?

Basicamente os Extendeds Events (Eventos Estendidos) são reconhecidos como um sistema de manipulação de eventos para sistemas operacionais de servidores. A infraestrutura de Eventos Estendidos oferece suporte à correlação de dados do SQL Server e, em certas condições, à correlação de dados entre sistema operacional e aplicativos de banco de dados.

Podem também ser definidos ou denominados como, um sistema genérico de gerenciamento de eventos para servidores. Permitindo relacionar informações do SQL Server e do sistema operacional.

Uma das principais vantagens dos Extended Events é o fato de podermos relacionar estes com a estrutura de Event Tracing for
Windows (ETW), que é uma estrutura que permite obter informações de diagnóstico no Windows.
Os eventos no SQL Server são categorizados de forma similar a encontrada no ETW, utilizando os chamados Channels (canais) e Keywords (palavras-chave). Isso foi feito exatamente para permitir a correlação de informações.

O sistema Eventos Estendidos tem os seguintes principais aspectos:

  • O mecanismo Eventos Estendidos é agnóstico, o que permite que o mecanismo associe qualquer evento a qualquer destino porque não é restrito ao conteúdo do evento.
  • Os eventos são separados dos consumidores de evento, que são chamados destinos em Eventos Estendidos. Isso significa que qualquer destino pode  receber qualquer evento. Além disso, qualquer evento gerado pode ser consumido automaticamente pelo destino, que pode registrar ou fornecer contexto de evento adicional.
  • Os eventos são distintos quanto à ação no disparo de um evento. Como resultado, qualquer ação pode ser associada a qualquer evento.

O mecanismo Eventos Estendidos pode gerar dados de evento de forma síncrona (e processar os dados de forma assíncrona) o que fornece uma solução flexível para manipulação de eventos. Além disso, o mecanismo Eventos Estendidos fornece os seguintes recursos:

  • Uma abordagem unificada no tratamento de eventos em todo o sistema de servidor, permitindo, ao mesmo tempo, que os usuários isolem eventos específicos com a finalidade de solucionar problemas;
  • Um mecanismo de tratamento de evento completamente configurável com base no Transact-SQL; e
  • A habilidade de monitorar processos ativos dinamicamente com efeito mínimo sobre esses processos.

Os Eventos Estendidos possuem alguns elementos que formam a sua estrutura básica de funcionamento, dentre estes elementos, vou destacar inicialmente os Predicados.

O que é um Predicado?

Os Predicados são conhecidos como filtros dinamicos envolvidos nos disparos realizados para cada evento, com objetivo de aumentar a flexibilidade da infraestrutura do mecanismo Eventos Estendidos.

Outro elemento importante que esta diretamente relacionado com os Eventos Estendidos, são os chamados Events(Eventos):
O que é um Evento? Qual sua finalidade?
Evento (Event): é um ponto definido no código. Cada evento tem o seu “payload” determinado, ou seja, o conjunto de colunas que retorna. O SQL Server 2008 RTM apresenta 254 eventos, apesar de que mais eventos devem ser adicionados com o tempo.
Todo evento pertence a um dos quatro canais existentes, conforme a tabela abaixo:
Além de pertencer a um canal, estes são agrupados em palavras-chave, que são basicamente a parte do sistema que diz ter ativado o evento. Os Eventos podem ser considerados objetos similares a agrupamento de objetos do SQL Server Profiler.
Observação: Dentre estes diversos eventos existentes na versão 2008 RTM, alguns apresetam problemas que foram corrigidos, a partir da aplicação do Service Pack1 para o SQL Server 2008.
Bem pessoal, vou encerrar esta primeira parte aqui. Acredito que para começar a falar deste assunto os conceitos básicos já foram apresentados, o que nos deixa capaz de entender e começar a imaginar como os Extendeds Events podem ser utilizados.
Na próxima parte, vou destacar os demais conceitos que podem ser utilizados com este recurso, como também, suas tabelas e visões de sistemas.
Por enquanto muito obrigado, agradeço mais uma vez sua visita, nos encontramos nos próximos dias.
Até mais.

Determinando o uso de Memória por Banco de Dados e Objetos no SQL Server 2008.

Determinando o uso de Memória por Banco de Dados e Objetos no SQL Server 2008.


Quando se referimos a utilização de memória por parte do SQL Server, muitas discussões, dúvidas e até mesmo informações
desencontradas são apresentadas. Para muitos profissionais, desenvolvedores e até mesmo entusiastas do SQL Server, este assunto é algo que acaba se tornando um grande enigma.

Básicamente uma porcentagem da memória consumida pelo SQL Server refere-se ao pool de buffers(exclusivamente, por dados em uso), independente da instância que possa estar sendo utilizada.

Mas como podemos tentar descobrir como este sendo consumido pelo SQL Server? Quais objetos e até mesmo Banco de Dados estão fazendo uso dos pools de buffers?

Pois bem, é justamente a respostas para estas perguntas que vou procurar responder neste artigo!!!

Para realizarmos este tipo de busca de informações, necessitamos vasculhar um pouco das informações armazenadas pelo SQL Server em seu catálogo de visões. Isso pode ser considerado algo difícil de se dizer.  Qual dos seus bancos de dados estão consumindo mais  memória de pool de buffer, e mais ainda, que objetos dentro desses bancos de dados, podem ser forçando este tipo de situação.

No que estas informações podem me ajudar?

Estas respostas podem se tornar informações de grande importância, por exemplo, se você está considerando uma mudança de
aplicativo para dividir seu banco de dados em vários servidores ou tentando identificar bancos de dados que são candidatos à consolidação. Além disso, se o seu ambiente estiver apresentando algum tipo de consumo excessivo de memória em determinados aplicativos ou funcionalidades em execução.  

Utilizando a DMV Sys.dm_os_buffer_descriptors.

Para conseguirmos encontrar as informações referentes aos buffers existentes em cache colocados pelo SQL Server após a execução de suas transações, podemos utilizar a exibição de gerenciamento dinâmico (DMV) introduzido no SQL Server 2005, chamada sys.dm_os_buffer_descriptors. Esta DMV contém uma linha para cada página que foi colocado no cache na área de buffer.

Usando esta DMV, você poderá determinar rapidamente quais bancos de dados estão utilizando a maioria da memória de pool do buffer.  Depois de ter identificado os bancos de dados que estão ocupando grande parte da área de buffer, fica mais fácil detalhar de
forma individual  o que esta sendo consumido por cada banco.

Para demonstrar como podemos começar a utilizar a sys.dm_os_buffer_descriptors, apresento a seguir a Listagem 1.
Através deste código conseguiremos descobrir exatamente como grande pool de buffers atualmente é esta sendo gerado fazendo uso de outra DMV (sys.dm_os_performance_counters) o que vai permitir calcular a porcentagem de área de buffer que está sendo usada por cada banco de dados, conforme apresenta a Figura 1.

Observação: Para consultar a DMV sys.dm_os_buffer_descriptiors é necessário possuir permissão de acesso as visões de estado do servidor(View Server State). Segue abaixo a Listagem 1:

— Listagem 1: Consultando o pool de buffers em uso –

–Declarando a varíavel @Buffers_EmUso —

Declare @Buffers_EmUso Int;

/* Acumando o valor dos contadores na variável @Buffers_EmUso, filtrando pelo Object_Name=Buffer Manager e Counter_Name=Total Pages*/

Select @Buffers_EmUso = cntr_value From Sys.dm_os_performance_counters

Where Rtrim(Object_name) LIKE ‘%Buffer Manager’

And counter_name = ‘Total Pages’;

— Declarando a CTE Buffers_Pages para contagem de Buffers por página —

;With DB_Buffers_Pages AS

(

SELECT database_id, Contagem_Buffers_Por_Pagina  = COUNT_BIG(*)

From Sys.dm_os_buffer_descriptors

Group By database_id

)

— Retornando informações sobre os pools de Buffers por Banco de Dados com base na CTE DB_Buffers_Pages —

Select Case [database_id] WHEN 32767 Then ‘Recursos de Banco de Dados’ Else DB_NAME([database_id]) End As ‘Banco de Dados’,

Contagem_Buffers_Por_Pagina,

‘Buffers em MBs por Banco’ = Contagem_Buffers_Por_Pagina / 128,

‘Porcentagem de Buffers’ = CONVERT(DECIMAL(6,3), Contagem_Buffers_Por_Pagina * 100.0 / @Buffers_EmUso)

From DB_Buffers_Pages

Order By ‘Buffers em MBs por Banco’ Desc;

Figura 1 – Pool de Buffers utilizados por Bancos de Dados.

Você pode observar através da Figura 1,  que os os Bancos de dados de Sistema foram incluídos em nossa listagem, caso você deseje retornar somente seus próprios bancos de dados, basta realizar uma pequena alteração na CTE DB_Buffers_Page.

Acrescente na claúsula Where a filtragem por bancos de dados usuário, algo que poderá variar entre as versões do SQL Server,
inclusive na próxima versão por enquando denominada Denali, que deverá apresentar um novo banco de dados para os serviços de integração, chamado SSISDB.

Analisando o resultado apresentado na Figura 1, podemos observar que neste momento na minha instância SQL Server, o Resource DB(Recursos Internos de Banco de Dados) estão ocupando a maior porcentagem de Pool Buffers, aproximadamente 29% de todos os recursos de pools buffers disponíveis. Mas a análise que pretendo apresentar neste artigo, será realizado sobre um dos meus bancos de dados, vou então utilizar o banco de dados denominado CRIPTOGRAFIA,
que esta consumindo aproximadamente 13% dos recursos de pools buffers disponíveis.

Então, agora que sabemos que este banco de dados esta ocupando uma parte considerável dos meus recursos disponíveis para o SQL Server, vamos posteriormente a esta análise, começar a vasculhar quais os objetos estão consumindo memória dentro deste banco.

Para realizarmos este procedimento, podemos utilizar mais uma vez a DMV,  sys.dm_os_buffer_descriptors só que desta vez, em vez de agregar as contagens de página no nível do banco de dados, nós podemos utilizar um conjunto de exibições do catálogo para determinar o número de páginas (e, portanto, quantidade de memória) dedicado a cada objeto. Neste caso vou utilizar as seguinte visões de catálogo:

  • Sys.partitions: Contém uma linha para cada partição de todas as tabelas e índices no banco de dados. Todas as tabelas e índices no SQL Server 2008 contêm pelo menos uma partição, estejam ou não divididos
    explicitamente.
  • Sys.allocation_units: Contém uma linha para cada unidade de alocação no banco de dados.
  • Sys.objects: Contém uma linha para cada objeto criado pelo usuário no banco de dados.
  • Sys.indexes: Contém uma linha para cada índice criado pelo usuário no banco de dados.

Agora que já sabemos quais visões de catálogo vou utilizar em conjunto com dmv sys.dm_os_buffers_descriptions, podemos executar a Listagem 2 apresentada a seguir:

— Listagem 2: Retornando pools Buffers de Objetos por Banco de Dados —

USE CRIPTOGRAFIA

GO

— Declarando a CTE Buffers_Pages para retorno dos Objetos alocados em Pool —

;WITH DB_Buffers_Pages_Objetos AS

(

Select

SO.name As Objeto,

SO.type_desc As TipoObjeto,

COALESCE(SI.name, ”) As Indice,

SI.type_desc As TipoIndice,

p.[object_id],

p.index_id,

AU.allocation_unit_id

From sys.partitions AS P INNER JOIN sys.allocation_units AS AU

ON p.hobt_id = au.container_id

INNER JOIN sys.objects AS SO
ON p.[object_id] = SO.[object_id]
INNER JOIN sys.indexes AS SI
ON SO.[object_id] = SI.[object_id]
AND p.index_id = SI.index_id

Where AU.[type] IN (1,2,3)

And SO.is_ms_shipped = 0

)

— Retornando informações sobre os pools de Buffers de Objetos por Banco de Dados com base na CTE DB_Buffers_Pages_Objetos —

Select Db.Objeto, Db.TipoObjeto  As ‘Tipo Objeto’,

Db.Indice,

Db.TipoIndice,

COUNT_BIG(b.page_id) As ‘Buffers Por Página’,

COUNT_BIG(b.page_id) / 128 As ‘Buffers em MBs’

From DB_Buffers_Pages_Objetos Db INNER JOIN sys.dm_os_buffer_descriptors AS b

ON Db.allocation_unit_id = b.allocation_unit_id

Where b.database_id = DB_ID()

Group By Db.Objeto, Db.TipoObjeto, Db.Indice, Db.TipoIndice

Order By ‘Buffers Por Página’ Desc, TipoIndice Desc;

Figura 2 – Pool de Buffers utilizados por Objetos dentro do Banco de Dados Criptografia.

Analisando o resultado apresentado na Figura 2, podemos observar os objetos, seus respectivos índices, a quantidade de buffers por página e buffers em megabytes. Vale ressaltar que somente a tabela Eventos possui um índice clusterizado.

Outra observação importante todos os pool de buffers possuem um  fluxo constante, e que esta consulta apresenta exatamente o último pool de buffer objetos do sistema, estes números vão sempre variar, e conforme o uso dos objetos os mesmo poderam aumentar de acordo com sua carga de dados. Ainda assim, isso deve dar uma ideia de quais objetos estão usando mais de seu pool de buffers.

Ao investigar o desempenho de seus servidores, dados de pool do buffer são apenas uma parte da imagem, mas que em muitas vezes esquecido pelos Profissionais durante os procedimentos de manutenção ou investigação.

Bom após esta enchurrada de informações, acredito ter conseguido atinguir o objetivo deste artigo, apresentando como o SQL Server trabalha com a memória em seus bancos de dados e objetos. De que forma ocorre o consumo deste recursos, qual a importância dos pools de buffers disponíveis para estes objetos.

Espero que você tenha gostado de mais este artigo, que as informações apresentas aqui sobre Consumo de Memória, Pools de Buffers, possam ser úteis no seu trabalho e estudos.

Agradeço a sua visita, até o próximo artigo.

Valeu.

———————————————————————————————————————————————————-

Referências: http://www.mssqltips.com/tip.asp?tip=2393 – Aaron Bertrand.

Books On-Line SQL Server 2008 R2.

Material de Apoio – SQL Server 2005, 2008 e R2

Material de Apoio – SQL Server 2005, 2008 e R2.


Pessoal, boa tarde.

Seguindo a minha missão e pagando a promessa, estou de volta com mais uma sessão de artigos, apresentações, documentos, códigos entre outros tipos de material sobre o SQL Server. Nesta sessão vou disponibilizar para você alguns dos arquivos mais importantes e que sempre utilizo em minhas aulas, apresentações e profissionalmente.

Nos arquivos de hoje, você poderam encontrar informações sobre:

  • Cluster,
  • Agrupamento de Dados,
  • Endereços de Conexão e IP,
  • Políticas de Recuperação de Bancos de Dados,
  • Trace Flags,
  • Tipos de Dados,
  • Memória,
  • entre outros.

Fiquem a vontade para realizarem o seu download, disponibilizar para seu colegas, contatos e amigos, espero que estas informações possam ser valiosas no seu dia-á-dia.

Segue abaixo a relação de arquivos:

Artigo – SQL Server 2005 – FailOver Cluster

Artigo – Relação de portas utilizadas por endpoints no SQL Server

Artigo – Relação – Trace Flags não documentadas

Artigo – Relação – Conexões agrupadas por IP

Artigo – Recuperando Banco de Dados no SQL Server 2005 e 2008

Artigo – Lista de Ferramentas – Livres – SQL Server

Artigo – Implementando Política de Recuperação para os Bancos de Dados do Sistema

Artigo – Consultando os planos de execução em memória no SQL Server 2008 R2

Artigo – Como utilizar diversos tipos de dados em uma única coluna no SQL Server

Bom, agradeço a sua visita, nos encontramos nos próximos posts.

Até mais.

 

 

 

 

 

 

 

 

 

Material de Apoio – SQL Server 2008 e 2005

Material de Apoio – SQL Server 2008 e 2005


Salve galera,

Tudo bem? Como de costume, estou mais uma vez disponibilizando em meu blog, mais alguns arquivos sobre o SQL Server 2008 e 2005. Sendo estes arquivos, apresentações utilizandos em alguns eventos da Microsoft que participei.

Na relação de hoje, você poderam encontrar dicas, informações e alguns exemplos sobre:

  • Alta performance;
  • Ferramentas grátis da Microsoft para se trabalhar com o SQL Server;
  • Índices Clustered e Non-Clustered;
  • Grandes Bancos de Dados(VLDBs) a partir do SQL Server 2005;
  • Segurança e Auditoria no SQL Server 2008;
  • Migração de versões para o SQL Server 2008, entre outros.

Muito bem, a seguir você encontra-se disponível a relação de arquivos, fique a vontade para fazer o seu download e compartilhar com seus colegas.

Construindo sistemas de alta performance usando o SQL Server 2005 e ferramentas grátis da Microsoft

Estratégias avançadas de Indexação para o Microsoft SQL Server 2005