Short Scripts – Junho 2017


Boa noite galera, olá comunidade de banco de dados.

Tudo bem? Graças a deus mais um final de semana se aproximando, finalzinho de noite de quinta – feira, acredito que neste momento minha linda esposa esta curtindo mais um episódio de uma das suas séries favoritas, ou lendo mais um dos seus intermináveis livros (kkkkkk), isso não é um crítica, muito ao contrário, a leitura faz bem para a alma e principalmente para a mente, mas posso dizer que minha pequena Fernanda é uma degustadora insaciável de livros.

Mudando de assunto, este é o segundo post deste ano dedicado exclusivamente a sessão Short Scripts, sessão criada a alguns anos que  esta atraindo um número interessante de visitantes, principalmente de profissionais da área de banco de dados na busca por exemplos de códigos que possam solucionar ou elucidar na resolução de um determinado problema.

O post de hoje

Para este post, selecionei como de costume os principais scripts armazenados recentemente na minha biblioteca de códigos, que apresentam os seguintes assuntos:

  • Check Constraint;
  • Common Table Expression;
  • Índices;
  • Information_schema.columns;
  • Monitoramento de Processos;
  • Network Protocol;
  • Operador Outer Apply;
  • Requisição de Disco;
  • Sys.dm_server_registry;
  • Sys.dm_tcp_listener_states;
  • sys.dm_exec_query_plan;
  • sys.dm_exec_sql_text;
  • sys.dm_exec_query_stats;
  • sys.types;
  • sys.tables;
  • sys.dm_db_index_usage_stats;
  • Sys.Identity_Columns; e
  • User Defined Function.

Então mãos no teclado, a seguir apresento os códigos e exemplos selecionados para o Short Script – Junho 2017. Vale ressaltar que todos os scripts publicados nesta sessão são devidamente testados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, vale sim todo cuidado possível para evitar maiores problemas.

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

Short Scripts

— Short Script 1  – Utilizando Check Constraint para cálculo de datas —

Create Table Alunos
(Codigo Int)
Go

— Adicionando a coluna e constraint —
Alter Table Alunos
Add DataNascimento DateTime
Constraint CK_Alunos_DataNascimento Check (DateDiff(Year,DataNascimento, GetDate()) >=18)
Go

— Adicionando somente a constraint —
Alter Table Alunos
Add Constraint CK_Alunos_DataNascimento
Check (DateDiff(Year,DataNascimento, GetDate()) >=18)
Go

— Default – Estado —
Alter Table Alunos
Add Constraint [DF_Estado] Default ‘SP’ for Estado
Go

— Short Script 2  – Aplicando o uso de Common Table Expression para inserir registros com valores de um registro anterior —

DECLARE @Metas TABLE ( Data DATE, Meta INT );

INSERT INTO @Metas
( Data, Meta )
VALUES ( DATEFROMPARTS(2017, 03, 29), 50 ),
( DATEFROMPARTS(2017, 04, 11), 35 ),
( DATEFROMPARTS(2017, 04, 13), 50 );

DECLARE @Producao TABLE
(
Data DATE ,
Quantidade INT
);

INSERT INTO @Producao
( Data, Quantidade )
VALUES ( DATEFROMPARTS(2017, 04, 10), 49 ),
( DATEFROMPARTS(2017, 04, 11), 35 ),
( DATEFROMPARTS(2017, 04, 12), 36 ),
( DATEFROMPARTS(2017, 04, 13), 50 ),
( DATEFROMPARTS(2017, 04, 14), 50 );

WITH DadosProduzidos
AS ( SELECT P.Data ,
Quantidade = SUM(P.Quantidade)
FROM @Producao AS P
GROUP BY P.Data
)
SELECT D.Data ,
[Produzido] = D.Quantidade ,
Meta = ( SELECT TOP 1 M.Meta
FROM @Metas AS M
WHERE M.Data <= D.Data
ORDER BY M.Data DESC
)
FROM DadosProduzidos D;

— Short Script 3  – Identificando a relação de todos os índices existentes em um banco de dados —

SELECT DB_NAME(Database_ID) DBName,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(ius.OBJECT_ID) ObjName,
i.type_desc,
i.name,
user_seeks,
user_scans,
user_lookups,
user_updates
FROM sys.dm_db_index_usage_stats ius INNER JOIN sys.indexes i
ON i.index_id = ius.index_id
AND ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.tables t
ON t.OBJECT_ID = i.OBJECT_ID
GO

— Short Script 4 – Obtendo informações de network protocols e device através da sys.dm_server_registry —

SELECT MAX(CONVERT(VARCHAR(15),value_data)) As ‘Default Port’ FROM sys.dm_server_registry
WHERE registry_key LIKE ‘%MSSQLServer\SuperSocketNetLib\Tcp\%’
AND value_name LIKE N’%TcpPort%’
AND CONVERT(float,value_data) > 0
Go

SELECT MAX(CONVERT(VARCHAR(15),value_data)) As ‘Dynamic Port ‘ FROM sys.dm_server_registry
WHERE registry_key LIKE ‘%MSSQLServer\SuperSocketNetLib\Tcp\%’
AND value_name LIKE N’%TcpDynamicPort%’
AND CONVERT(float,value_data) > 0
Go

— Short Script 5 – Obtendo informações de network protocols e device através da sys.dm_tcp_listener_states —

SELECT port As ‘Default Port’ FROM sys.dm_tcp_listener_states
WHERE is_ipv4 = 1
AND [type] = 0
AND ip_address <> ‘127.0.0.1’
Go

— Short Script 6 – Criando uma User Defined Function com operador Outer Apply —

CREATE FUNCTION AttributesOfTable (@tableToSearch nvarchar(500))
returns table
return SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
from information_schema.columns
where TABLE_NAME = @tableToSearch;
go

Declare @tableToSearch table (nome_tabela varchar(50));
INSERT into @tableToSearch values (‘Customer’), (‘Order’), (‘Papagaio’);

SELECT T1.nome_tabela as [nome da tabela],
T2.TABLE_SCHEMA as [nome do esquema],
T2.COLUMN_NAME as [nome da coluna]
from @tableToSearch as T1
outer apply dbo.AttributesOfTable(T1.nome_tabela) as T2;

— Short Script 7 – Utilizando a DMV sys.identity_columns para identificar o valor identity de uma determinada coluna —

SELECT sys.tables.name AS [Table Name],
sys.identity_columns.name AS [Column Name],sys.types.name as Type,
last_value AS [Last Value]
FROM sys.identity_columns
INNER JOIN sys.tables
ON sys.identity_columns.object_id = sys.tables.object_id
Inner join sys.types on sys.types.user_type_id = sys.identity_columns.user_type_id
ORDER BY last_value DESC

— Short Script 8 – Consultando informações sobre o SQL Server armazenadas no Registro do Windows —

— A. Display the SQL Server services —
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE key_name LIKE N’%ControlSet%’
Go

— B. Display the SQL Server Agent registry key values —
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE key_name LIKE N’%SQLAgent%’
Go

— C. Display the current version of the instance of SQL Server —
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE value_name = N’CurrentVersion’
Go

— D. Display the parameters passed to the instance of SQL Server during startup —
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE registry_key LIKE N’%Parameters’
Go

— E. Return network configuration information for the instance of SQL Server —
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE keyname LIKE N’%SuperSocketNetLib%’
Go

— Short Script 9 – Identificando a relação de querys que apresentam o maior consumo de disco durante seu período de processamento – 

SELECT TOP 20 SUBSTRING(qt.text,
(qs.statement_start_offset/2)+1,
((CASE
qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE
qs.statement_end_offset
END –
qs.statement_start_offset)/2)+1),
qs.execution_count,

qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads,
qs.max_logical_reads,
qs.total_elapsed_time,
qs.last_elapsed_time,
qs.min_elapsed_time,
qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats
qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY
sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY
qs.total_logical_reads DESC

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


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

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

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

Links

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

Agradecimento

Agradeço sua visita, com certeza é imenso prazer saber que você teve interesse em acessar este post.

Nos encontramos novamente no mês de setembro, para o próximo post dedicado a sessão Short Scripts.

Um grande abraço, boa noite, bom descanso.

Valeu.

Dica do Mês – Conhecendo e aplicando o uso de atualização de estatísticas incrementais


Muito boa tarde pessoal, salve, salve comunidade e amantes de banco de dados.

Tudo bem com vocês? Estou aqui mais uma vez em um novo post do meu blog na sessão Dica do Mês, hoje falando de um assunto que até alguns dias atrás eu sinceramente nunca havia feito uso, mas com base em um post publicado do Ahmad Yaseen no MSSQLTips.com, acabou me servindo como fonte de inspiração para elaborar e compartilhar este post com vocês.

Antes de começarmos a falar sobre o post de hoje, nada mais justo começar agradecendo como de costume a você está aqui neste momento acessando meu blog. Espero que possa ter encontrado o que precisa, bem como, esteja gostando do conteúdo publicado, fique a vontade para entrar em contato expressando suas opiniões e demais pensamentos.

Dando continuidade, vamos conhecer um recurso adicionado na versão 2014 do Microsoft SQL Server a partir do Service Pack 2 e mantido no Microsoft SQL Server 2016 SP1 conhecido como Estatísticas Incrementais ou Incremental Statistics, pode parecer estranho o nome, mas é exatamente isso que este recurso permite, realizar o processo de atualização de estatísticas de maneira incremental, ou para muitos incrementar o processo de atualização de estatísticas aplicadas aos nossos bancos de dados e seus respectivos objetos.

Parece ser coisa de louco isso, mas posso garantir que não é, absolutamente é algo totalmente viável e aplicável a qualquer ambiente que se faça uso do Microsoft SQL Server em conjunto com as funções e scheme de particionamento de dados.

E ai esta curioso para saber um pouco sobre este recurso?

Eu estou, sendo assim, vamos em frente, seja bem vindo ao post Dica do Mês – Conhecendo e aplicando o uso de atualização de estatísticas incrementais.

Seguindo….


Introdução

O otimizador de consultas do Microsoft SQL Server depende fortemente das estatísticas na geração a execução de plano de consulta mais eficiente. Estas estatísticas fornecem ao otimizador a distribuição dos valores de colunas na tabela e o número de linhas, também chamada a cardinalidade que resultará da consulta.

A ausência destas estatísticas, ou a existência de estatísticas desatualizadas, proporciona a ocorrência de querys consideradas lentas, neste sentido, o otimizador de consulta “query optimizer” acaba sendo obrigado a utilizar estatísticas imprecisas para criar o plano de execução, que pode ser considerado um plano não ideal para executar a consulta neste caso.

O SQL Server geralmente faz o seu trabalho em manter estas estatísticas atualizadas, mas como um administrador de banco de dados, você deve fazer seu trabalho, em alguns casos, atualizando as estatísticas manualmente. Atualizar estatísticas manualmente em tabelas grandes pode ser como um grande desafio, bem como, em tabelas pequenas pode-se imaginar que a estatística já esteja atualizada, o que em alguns cenários isso acaba não ocorrendo.

Um dos cenários mais impactados pelo uso de estatísticas desatualizadas ou atualizadas parcialmente são as tabelas particionadas. Como destacado anteriormente através do uso das funções de particionamento de dados introduzido no Microsoft SQL Server 2008, temos a capacidade de distribuir nossos dados em partições “pequenos fatias de armazenamento de dados” que nos possibilitar distribuir respectivos valores com base em uma função que análise e identifica o local de armazenamento do mesmo.

Para este tipo de ambiente, o uso de estatísticas como mecanismo para auxiliar no obtenção mais rápida do dado, pode apresentar simultaneamente o papel de herói como também de vilão, isso pode parecer meio confusão, mas não é! Basicamente quando trabalhamos com estatísticas acreditamos que sempre teremos todas as informações armazenados no histograma atualizadas de forma automática de maneira mais precisa possível, algo que não acontece exatamente desta maneira quando trabalhando com particionamento de dados.

Uma das situações mais comuns quando se uso particionamento de dados é a possibilidade de ocorrer a atualização de estatísticas de maneira parcial, ou seja, apena um partição de todo estrutura de partições acaba tendo suas informações de estatísticas atualizadas, o que poderá provocar uma alteração no plano de execução ou a possibilidade de criação de um plano incoerente.

Sabendo desta possibilidade e comportamento, o time de engenheiros e desenvolvedores do Microsoft SQL Server, implementou a partir da versão 2014 SP1 as Estatísticas Incrementais, funcionalidade que nos permite justamente contornar este tipo de situação.

Estatísticas Incrementais – Incremental Statistics

As estatísticas Incrementais, ajudam na atualização de estatísticas para apenas a partição ou partições que você escolher. Em vez de analisar e varrer a tabela inteira para atualizar as estatísticas, a partição selecionada será verificada somente para a atualização, reduzindo o tempo necessário para executar a operação de atualização de estatísticas, atualizando-se apenas a partição modificada.

O outro ponto importante é que a porcentagem de alterações de dados necessário para acionar a atualização automática de estatísticas, sendo este o valor 20% de linhas alteradas, o que proporcionará o uso de atualização de estatísticas no nível da partição, comportamento que não era permitido anteriormente.

Muito legal este novo recurso e principalmente o comportamento do Microsoft SQL Server, agora que já conhecemos conceitualmente como as estatísticas incrementais funcionam, chegou a hora de colocar as mãos no teclado e começar a conhecer de maneira prática esta funcionalidade.

Preparando o ambiente

Para entender a atualizar as estatísticas incrementais, vamos preparar um banco de dados de teste com uma tabela particionada. Começamos com a criação de um novo banco de dados denominado IncrementalStatistics, formado por quatro novos grupos de arquivos além de grupo de arquivos primário padrão, para tal vamos utilizar o Bloco de Código 1 apresentado a seguir:

— Bloco de Código 1 —

— Criando o Banco de Dados IncrementalStatistics —
Create Database IncrementalStatistics
Go
— Adicionando os Filegroups —
Alter Database IncrementalStatistics
Add Filegroup IncrementalStatisticsGrupo1
Go
Alter Database IncrementalStatistics
Add Filegroup IncrementalStatisticsGrupo2
Go
Alter Database IncrementalStatistics
Add Filegroup IncrementalStatisticsGrupo3
Go
Alter Database IncrementalStatistics
Add Filegroup IncrementalStatisticsGrupo4
Go

 

— Adicionando os Arquivos aos seus respectivos Filegroups —

Alter Database IncrementalStatistics
Add File (Name = N’IncrementalStatisticsGrupo1′,
FileName = N’S:\MSSQL-2016\Data\Arquivo-Grupo1-Data.ndf’,
Size = 4096KB,
FileGrowth =1024KB) To Filegroup IncrementalStatisticsGrupo1
Go

Alter Database IncrementalStatistics
Add File (Name = N’IncrementalStatisticsGrupo2′,
FileName = N’S:\MSSQL-2016\Data\Arquivo-Grupo2-Data.ndf’,
Size = 4096KB,
FileGrowth =1024KB) To Filegroup IncrementalStatisticsGrupo2
Go

Alter Database IncrementalStatistics
Add File (Name = N’IncrementalStatisticsGrupo3′,
FileName = N’S:\MSSQL-2016\Data\Arquivo-Grupo3-Data.ndf’,
Size = 4096KB,
FileGrowth =1024KB) To Filegroup IncrementalStatisticsGrupo3
Go

Alter Database IncrementalStatistics
Add File (Name = N’IncrementalStatisticsGrupo4′,
FileName = N’S:\MSSQL-2016\Data\Arquivo-Grupo4-Data.ndf’,
Size = 4096KB,
FileGrowth =1024KB) To Filegroup IncrementalStatisticsGrupo4
Go

Uma vez que o banco de dados é criado com os novos grupos de arquivos e arquivos de dados, precisamos prepará-lo para hospedar a tabela particionada. Nosso próximo passo consiste na criação da função particionada PartitionFunctionIncrementalStatistics que classifica os dados de acordo com os quatro trimestres do ano, sendo assim, vamos utilizar o Bloco de Código 2 apresentado abaixo:

— Bloco de Código 2 —

— Criando a Partition Function PartitionFunctionIncrementalStatistics —
USE IncrementalStatistics
GO

CREATE PARTITION FUNCTION PartitionFunctionIncrementalStatistics (Int)
AS
RANGE RIGHT FOR VALUES
(20171, 20172, 20173, 20174)
Go

Note que nossa PartitionFunctionIncrementalStatistics é composta por quatro partições de valores subdivididos da seguinte forma: 

  • 20171 – Valor que representa o Primeiro Quartil;
  • 20172 – Valor que representa o Segundo Quartil;
  • 20173 – Valor que representa o Terceiro Quartil do Ano; e
  • 20174 – Valor que representa o Quarto Quartil do Ano.

    Talvez você ainda não esteja entendendo o porque estamos fazendo uso deste tipo de implementação, tenha calma no decorrer do post tudo vai ficar mais claro e você terá total noção do porquê estamos utilizando este recurso.

Continuando com a nossa longa caminhada, você deve saber que para se trabalhar com particionamento de dados devemos além de criar uma Partition Function devemos obrigatoriamente criar um Partition Scheme,  que estará vinculado lógicamente a nossa partition function, sendo assim, este é nosso próximo passo, fazendo uso do Bloco de Código 3:

— Bloco de Código 3 —

— Criando o Partition Scheme PartitionSchemeIncrementalStatistics —
CREATE PARTITION SCHEME PartitionSchemeIncrementalStatistics AS
PARTITION PartitionFunctionIncrementalStatistics
TO
(
IncrementalStatisticsGrupo1,
IncrementalStatisticsGrupo2,
IncrementalStatisticsGrupo3,
IncrementalStatisticsGrupo4,
[PRIMARY])
Go

Esta quase tudo pronto para nossa brincadeira, seguiremos com a criação na nossa tabela TableIncrementalStatistics, este é um ponto importante do nosso ambiente, onde estamos fazendo uso da tabela particionada para ilustrar como as estatísticas incrementais vão realizar o seu papel.

TableIncrementalStatistics será composta por algumas colunas, dentre as quais a coluna Quartil, responsável em armazenar o valor do quartil de acordo com o ano informado, como também, é através desta coluna que estaremos realizando o particionamento dos dados. Para isso utilizaremos o Bloco de Código 4 a seguir:

— Bloco de Código 4 —

— Criando a Tabela TableIncrementalStatistics —

CREATE TABLE TableIncrementalStatistics
(ID Int Null,
Acao NVarchar(40) Default NewID(),
Data DateTime Null,
Quartil  AS (datepart(year,[Data])*(10)+datepart(quarter,[Data])) PERSISTED
) ON PartitionSchemeIncrementalStatistics (Quartil)
Go

Ótimo, toda estrutura para armazenar nossos dados já esta pronta, bem como, a lógica para distribuir e particionar os dados que serão inseridos na tabela TableIncrementalStatistics.

Ufa, ainda temos um bom caminho pela frente, mas já avançamos bastante, agora temos realizar uma alteração nas configurações do nosso banco de dados IncrementalStatistics, sendo esta necessária para podermos aplicara o uso de estatísticas incrementais, estou me referindo a opção Auto Create Statistics muito conhecida, onde vamos alterar o seu valor default para Incremental = On, conforme apresenta o Bloco de Código 5 abaixo:

— Bloco de Código 5 —

— Habilitando o uso de Incremental Statistics —
Alter Database IncrementalStatistics
Set Auto_Create_Statistics On (INCREMENTAL = On)
Go

O próximo passo consiste na criação do índice que iremos utilizar em na TableIncrementalStatistics pois você deve ter notado que realizamos a criação da tabela sem a definição de uma chave primária, desta maneira  utilizamos o Bloco de Código 6 para criação dos respectivo índice em seguida confirmamos se esta tabela esta fazendo uso das estatísticas incrementais habilitada no bloco de código 5:

— Bloco de Código 6 —

— Criação do índice Clustered —
Create Clustered Index Ind_TableIncrementalStatistics_ID
On [TableIncrementalStatistics] (ID)
GO

— Confirmando se as estatísticas incrementais está habilita —
SELECT
OBJECT_NAME(object_id) TableName
,name
,is_incremental
,stats_id
FROM sys.stats
WHERE name = ‘Ind_TableIncrementalStatistics_ID’
Go

Figura 1 – Confirmando o uso das estatísticas incrementais no índice Ind_TableIncrementalStatistics_ID.

Observação: Note que ao executar o Select realizado na visão de sistema sys.stats a coluna Is_Incremental deverá retornar e apresentar o valor igual á 1, isso indica que TableIncrementalStatistics esta neste momento fazendo uso das estatísticas incrementais.

Muito bem, chegou a hora de popular nossas tabelas, realizaremos a inserção de 8.000 linhas de registros, sendo estes particionados em grupos de 2.000 registros para cada partição que forma e compõem a estrutura da nossa tabela. Vamos então utilizar o Bloco de Código 7 apresentado na sequência:

— Bloco de Código 7 —

— Inserindo os dados na TableIncrementalStatistics —
Insert Into TableIncrementalStatistics (ID, Data)
Values (1, ‘2017-11-22’)
Go 2000

Insert Into TableIncrementalStatistics (ID, Data)
Values (2, ‘2017-06-05’)
Go 2000

Insert Into TableIncrementalStatistics (ID, Data)
Values (3, ‘2017-01-25’)
Go 2000

Insert Into TableIncrementalStatistics (ID, Data)
Values (4, ‘2017-08-13’)
Go 2000

Após a inserção das 8.000 linhas de registros, vamos confirmar a distribuição dos dados através do Bloco de Código 8 declarado abaixo, conforme ilustra o resultado da Figura 2:

— Bloco de Código 8 —

— Consultando a distribuição e particionamento dos dados —
Select partition_number, rows
From sys.partitions
Where OBJECT_NAME(OBJECT_ID)=’TableIncrementalStatistics’
Go

Figura 2 – Distribuição dos dados na tabela TableIncrementalStatistics de acordo com o valor e partição.

Estamos chegando no final, agora vamos realizar algumas manipulações no conjunto de dados armazenados na tabela TableIncrementalStatistics afim de forçarmos o processos de atualização das estatísticas, procedimento que vai nos ajudar a entender o processo de incremento na atualização das estatísticas de armazenamento e processamento utilizados pelo Microsoft SQL quando solicitado acesso aos dados armazenados em nossa table, para tal operação vamos utilizar o Bloco de Código 9:

— Bloco de Código 9 —

— Consultando dados na TableIncrementalStatistics —
Select Id, Acao, Data, Quartil From TableIncrementalStatistics
Where ID = 1
Go

Select Id, Acao, Data, Quartil From TableIncrementalStatistics
Where ID >= 2
Go

Select Id, Acao, Data, Quartil From TableIncrementalStatistics
Where ID <> 3
Go

Pronto, realizamos algumas operações de Select com intuito de forçar a criação de novas estatísticas, e principalmente a atualização das estatísticas atuais. Por enquanto nada de diferente, na sequência vamos consultar as informações sobre as estatísticas relacionadas a nossa tabela, fazendo uso do Bloco de Código 10 e analisando o resultado apresentado através da Figura 3:

— Bloco de Código 10 —

— Consultando as informações sobre as estatísticas da tabela TableIncrementalStatistics —
Select object_id, stats_id , last_updated , rows , rows_sampled , steps
From sys.dm_db_stats_properties(OBJECT_ID(‘[TableIncrementalStatistics]’),1);
Go

Figura 3 – Dados relacionados a estatísticas da TableIncrementalStatistics.

Como você pode ver, o DMF sys.dm_db_stats_properties mostra-nos que as estatísticas foram atualizadas na data do dia 23/05/2017 ás 16:55, para a tabela que tem 8000 linhas.

Neste momento, podemos nos perguntar: Qual partição da tabela inclui as estatísticas atualizadas?

A resposta para esta sua pergunta vem justamente atráves do uso nova DMF sys.dm_db_incremental_stats_properties já apresentada aqui no meu blog. Sendo esta DMF responsável em apresentar as propriedades estatísticas incremental, recuperando as mesma informação obtida a partir do DMF sys.dm_db_stats_properties, também super conhecida e apresentada no meu blog. Neste caso a sys.dm_db_stats_properties vai apresentar dados de  cada partição da tabela particionada, fornecendo-lhe com os mesmos parâmetros; a identificação do objeto e a identificação de estatísticas.

Caminhando mais um pouco, estamos próximos do final, vamos então formar o SQL Server a justamente realizar o processo de atualização das estatísticas para nossa partição de número 3, realizando o processo de exclusão de 1.500 linhas de registros, em seguida consultando nossa TableIncrementalStatistics, conforme apresenta o Bloco de Código 11:

— Bloco de Código 11 —

— Excluíndo 1.500 linhas —
Delete Top (1500) From TableIncrementalStatistics
Where ID = 2
Go

— Consultando os dados —
Select Id, Acao, Data, Quartil From TableIncrementalStatistics
Where ID <> 4
Go

Agora vamos novamente consultar os dados estatísticas, sendo assim repita a execução do Bloco de Código 10, observe que você deverá receber um conjunto de valores similares a Figura 4, onde a coluna Last_Updated deverá apresentar a data e hora da última atualização:

Figura 4 – Data e hora da última atualização da estatística.

Ufa, estamos quase lá, agora chegou a hora da verdade, hora de comprovar se realmente o SQL Server esta fazendo as coisas certas, vamos fazer uso da DMF sys.dm_incremental_stats_properties para validar se a estatística da partição 3 foi atualizada, o resultado pode ser analisado através da Figura 5. Para isso vamos utilizar o Bloco de Código 12 a seguir:

— Bloco de Código 12 —

— Consultando as informações sobre as estatísticas incrementais —
Select object_id, stats_id,
partition_number,
last_updated,
rows, rows_sampled,
steps
From sys.dm_db_incremental_stats_properties(OBJECT_ID(‘TableIncrementalStatistics’),1)
Go

Figura 5 – Informações sobre as atualizações de estatísticas, onde a partição 3 foi atualizada de maneira independente das demais.

Sensacional, conseguimos, muito legal este recurso, como sempre o Microsoft SQL Server nos surpreende com a sua capacidade e potencialidade de recursos.

Referências

Post Anteriores

https://pedrogalvaojunior.wordpress.com/2017/04/13/dica-do-mes-microsoft-sql-server-identificando-as-transacoes-que-estao-utilizando-o-transact-log/

https://pedrogalvaojunior.wordpress.com/2017/03/01/dica-do-mes-microsoft-sql-server-2016-sp1-novo-argumento-use-hint-disponivel-para-query-hints/

https://pedrogalvaojunior.wordpress.com/2017/01/16/dica-do-mes-conhecendo-a-nova-dmf-sys-dm_exec_input_buffer-no-microsoft-sql-server-2016/

https://pedrogalvaojunior.wordpress.com/2016/11/28/dica-do-mes-sql-server-2016-sp1-comando-create-or-alter/

https://pedrogalvaojunior.wordpress.com/2016/10/24/dica-do-mes-sql-server-2016-obtendo-informacoes-sobre-o-cache-de-execucao-de-funcoes/

Conclusão

Administrar, gerenciar, cuidar e prover um ambiente sempre no melhor estado possível não é uma das atividades mais tranquilas e simples desempenhadas por profissionais ou administradores de banco de dados, mas também não pode ser considerada um “bicho de sete cabeças” ou uma “caixa preta” ainda mais quando este servidor de banco de dados utiliza o Microsoft SQL Server.

Neste post você pode conhecer um pouco mais de como o Microsoft SQL Server trabalha de maneira árdua na busca da melhor maneira para encontrar e retornar os dados solicitados em nossas transações. Ao longo de novas versões o produto esta cada vez mais maduro, confiável e inteligente, sempre nos surpreendendo com sua capacidade.

Algo que não poderia ser diferente no uso das Estatísticas Incrementais, recurso que nos permite adotar uma nova maneira de atualização dos dados internos relacionados ao armazenamento das nossas informações, mas principalmente prover um auxílio para próprio Database Engine mas atividades para identificar o melhor caminho para se processar uma query.

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.

Um forte abraço, nos encontramos logo logo…

Valeu….

#14 – Para que serve


Olá, boa noite….

Final de noite de domingo, véspera de feriado e nosso Brasil desde a última sexta – feira dia 28/04 vivendo fortes emoções na política, economia, esporte e principalmente cidadania. Alias dia 28/04/2017 uma das datas mais importantes da minha vida, neste dia comemorei mais uma primavera como gostam de dizer alguns dos meus familiares, já se vão 37 anos, muitos destes anos dedicados a minha esposa, filhos, filha, trabalho e principalmente a áreas de educação e tecnologia.

Aproveito para agradecer a todos os amigos, colegas, familiares, alunos, enfim pessoas que por algum momento passaram pela minha vida nestes últimos 37 anos.

Dando continuidade, este é o novo post da sessão Para que serve, sendo o post de número 14, muito bom, lentamente esta sessão esta ganhando corpo e força com os meus seguidores.

Hoje vou destacar um conteúdo bastante simples e direto, mas muito interesse e bastante útil, que consiste basicamente em como através da linguagem Transact-SQL podemos identificar ou até mesmo descobrir quais portas de rede estão em uso em uma instância ou servidor SQL Server baseadas no protocolo TCP/IP e na versão IPV4 do protocolo IP.

Isso pode parecer algo bastante simples de ser feito, na verdade é mesmo, mas até a versão do SQL Server 2008 R2 SP1 era um pouco chato e até mesmo complexo para se obter esta simples informação, cenário que muito drasticamente a partir da versão 2012 e se mantem presente na versão 2016.

Desta forma, seja bem – vindo ao #14 – Para que serve – Identificando as portas de rede TCP/IP através da DMV – sys.dm_tcp_listener_states.

Introdução

Obter informações sobre as portas de rede utilizadas por uma instância ou servidor SQL Server, por mais simples que parece ser era considerada por muitos profissionais de bancos de dados uma das tarefas mais chatas e até mesmo tediosas pelo simples fato de não existir especificamente uma ferramenta da Microsoft dedicada para este cenário, mesmo assim existem algumas possibilidades que podemos ou não considerar práticas ou inseguras.

A seguir apresento as possibilidades mais conhecidas:

BPCheck: Não pode ser considerada dentre as possibilidades a mais conhecida, muito menos a mais simples, mas sim a mais completa no conjunto de dados retornados para o usuário. O BPCheck – Best Practices and Performance Check, criado em 28-07-2011 por Pedro Lopes (Senior Program Manager for the Microsoft SQL Server Product Group – Tiger Team), com base na versão 2005 do SQL Server e mantido até as versões atuais.

Posso dizer, que este é um daqueles scripts mágicos criados pelos maiores profissionais do SQL Server espalhados pelo mundo, dentre os quais o Pedro Lopes faz parte, o nível de complexidade existente no código fonte deste arquivo comprova o grau de conhecimento e capacidade técnica que este profissional apresenta.

Microsoft SQL Server 2008 e 2008 R2: Microsoft trabalhou e adicionou a partir da versão 2008 R2 SP1 uma forma não muito usual, nem muito interessante de se obter informações sobre as portas de rede fazendo uso da DMV – Dynamic Management View (Visão de Gerenciamento Dinâmico): sys.dm_server_registry, onde era possível coletar informações com base nas chaves de registro do Windows, o que sinceramente não podemos dizer que é algo muito indicado ou até mesmo seguro, mesmo assim era a única forma direta através do Management Studio de se encontrar estas informações. Esta DMV apresenta o seguinte conjunto de colunas:

Nome da coluna Tipo de dados Descrição
registry_key nvarchar(256) Nome da chave do Registro. Permitir valor nulo.
value_name nvarchar(256) Nome do valor da chave. Este é o item mostrado na coluna Nome do Editor do Registro. Permitir valor nulo.
value_data sql_variant Valor dos dados da chave. Este é o valor mostrado na coluna Dados do Editor do Registro para uma determinada entrada. Permitir valor nulo.

Microsoft SQL Server 2012: Talvez pode ser considerada até o presente momento a forma mais de se obter através de uma ferramenta gráfica neste caso o Management Studio as informações relacionadas a portas e protocolos de rede TCP/IP, fazendo-se uso da DMV – Dynamic Management View (Visão de Gerenciamento Dinâmico): sys.dm_tcp_listener_states, introduzida neste versão do SQL Server. Esta DMV apresenta o seguinte conjunto de colunas:

Nome da coluna Tipo de dados Descrição
listener_id int A ID interna do ouvinte. Não permite valor nulo.

Chave primária.

ip_address nvarchar48 O endereço IP do ouvinte que está online e está sendo escutando no momento. IPv4 ou IPv6 é permitido. Se um ouvinte possuir os dois tipos de endereços, eles serão listados separadamente. Um curinga de IPv4, exibido como “0.0.0.0”. Um curinga de IPv6, exibido como “::”.

Não permite valor nulo.

is_ipv4 bit Tipo de endereço IP

1 = IPv4

0 = IPv6

port int O número da porta na qual o ouvinte está escutando. Não permite valor nulo.
Tipo tinyint Tipo de ouvinte, um dos seguintes:

0 = Transact-SQL

1 = Service Broker

2 = Espelhamento do banco de dados

Não permite valor nulo.

type_desc nvarchar(20) Descrição do tipo, um dos seguintes:

TSQL

SERVICE_BROKER

DATABASE_MIRRORING

Não permite valor nulo.

state tinyint O estado do ouvinte do grupo de disponibilidade, um dos seguintes:

1 = Online. O ouvinte está escutando e processando solicitações.

2 = Reinício pendente. o ouvinte está offline, pendente de uma reinicialização.

Se o ouvinte do grupo de disponibilidade estiver escutando na mesma porta que a instância do servidor, esses dois ouvintes sempre terão o mesmo estado.

Não permite valor nulo.

Observação Observação
Os valores desta coluna são oriundos do objeto TSD_listener. A coluna não dá suporte a um estado offline porque, quando o TDS_listener está offline, ele não pode ser consultado para obter o estado.
state_desc nvarchar(16) Descrição do estado, um dos seguintes:

ONLINE

PENDING_RESTART

Não permite valor nulo.

start_time datetime Carimbo de data/hora que indica quando o ouvinte foi iniciado. Não permite valor nulo.

Bom, agora que já conhecemos as possibilidades de se coletar as informações relacionadas a portas e protocolos de rede, vamos colocar a mão na massa ou melhor no teclado e por em prática o uso das DMVs: sys.dm_server_registry e sys.dm_tcp_listener_states.

Exemplos

1 – Identificando a Default Port através da sys.dm_server_registry:

SELECT MAX(CONVERT(VARCHAR(15),value_data)) As ‘Default Port’ FROM sys.dm_server_registry

WHERE registry_key LIKE ‘%MSSQLServer\SuperSocketNetLib\Tcp\%’

AND value_name LIKE N’%TcpPort%’

AND CONVERT(float,value_data) > 0

Go

 

 2 – Identificando a Dynamic Port através da sys.dm_server_registry:

SELECT MAX(CONVERT(VARCHAR(15),value_data)) As ‘Dynamic Port ‘ FROM sys.dm_server_registry

WHERE registry_key LIKE ‘%MSSQLServer\SuperSocketNetLib\Tcp\%’

AND value_name LIKE N’%TcpDynamicPort%’

AND CONVERT(float,value_data) > 0

Go

 

3 – Obtendo a relação de Listeners, Ports, Protocols e demais dados relacionadas a network através da sys.dm_server_registry:

select Registry_key, Value_Name, Value_Data FROM sys.dm_server_registry

where registry_key like ‘%SuperSocketNetLib%’

Go

 

4 – Identificando a Default Port através da sys.dm_tcp_listener_states:

SELECT port As ‘Default Port’ FROM sys.dm_tcp_listener_states

WHERE is_ipv4 = 1

AND [type] = 0

AND ip_address <> ‘127.0.0.1’

Go

 

5 – Obtendo a relação de Listeners, Ports e Protocols através da sys.dm_tcp_listener_states:

Select listener_id, ip_address, is_ipv4,

Port, Type, type_desc, state_desc,

start_time

from sys.dm_tcp_listener_states

Go

Show de bola, legal, legal, aqui estão os exemplos, se você obter realmente o uso da DMV sys.dm_server_registry em comparação com a DMV sys.dm_tcp_listener_states pode ser considerado bem mais complexo e confuso, pois torna-se necessário conhecer um pouco da estrutura de chaves de registro do Windows, bem como, o que representa a sequência de valores apresentados na coluna Registry_Key o que para muitos profissionais não é algo são comum de ser entendido.

Referências

https://blogs.msdn.microsoft.com/sql_server_team/programmatically-find-sql-server-tcp-ports/

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tcp-listener-states-transact-sql

https://msdn.microsoft.com/en-us/library/hh204561.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/2017/03/25/13-para-que-serve/

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

Conclusão

Mesmo com todas as possíveis dificuldades, falta de ferramenta exclusiva ou facilidade para se conseguir obter uma simples informação relacionadas as portas de rede e protocolos, sempre vai existir alguma maneira de se conseguir encontrar o que deseja no Microsoft SQL Server, seja através de um script mágico como o destacado hoje neste post ou através de um recurso não muito usual, independente da maneira que possa ser dentro da estrutura, do coração do SQL Server em suas tabelas internar em conjunto com o uso das DMVs torna-se totalmente viável coletar qualquer tipo de dado desejado.

Neste post, você pode comprovar como é possível encontrar os dados relacionados á protocolos, portas, listeners e demais elementos envolvidos nos processos de network, onde uma simples aplicação, website, aplicativo ou ERP venha a necessitar acessar, consumir e trocar dados via pacotes de rede com o 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.
Nos encontramos em breve, até lá….

Dica do Mês – Microsoft SQL Server – Identificando as transações que estão utilizando o Transact Log


Bom dia pessoal, tudo bem?

Véspera de feriado religioso e um dos mais importantes para boa parte da população mundial! Justamente por ser véspera de feriado hoje é um daqueles dias que qualquer profissional da área de banco de dados reza para que nada ocorra de errado próximo ás 17hrs ou mais conhecido como 5 horas da tarde.

Pensando neste tipo de necessário o post de hoje dedicado a sessão Dica do Mês tem o objetivo de ajudar você profissional que esta em apuros neste momento tentando identificar o que pode estar ocorrendo em seu ambiente ou servidor Microsoft SQL Server.

Hoje vou apresentar como você pode no Microsoft SQL Server a partir da versão 2008 R2 identificar quais transações estão realizando o acesso e demandando informações para o Transact – Log, arquivo utilizado por qualquer banco de dados existente em seu servidor ou instância SQL Server para registrar dados relacionados as transações atuais ou já processadas, sendo este mais conhecido como log de transações.

Estão vamos em frente…..


Introdução

Quando se referimos ao log de transações, temos sempre aquele boa e velha definição, “o transact log é o arquivo utilizado pelo banco de dados para registrar o que esta sendo processado por nossas transações, conhecido como o dedo duro”, na verdade o transact-log é bem mais do que isso, trata-se de um dos principais componentes de qualquer banco de dados criado no Microsoft SQL Server que nos permite através do seu uso garantir a continuidade e disponibilidade de nossos dados para diversos cenários de armazenamento e principalmente recuperação de informações.

Durante o processamento de uma ou mais querys o transact-log pode apresentar comportamentos diversas de acordo com o que esta sendo processado, mas principalmente no que se relacionado aos processos de escrita e leitura do log, onde nossas querys podem gerar processos de gravação de dados no transact-log e internamente o SQL Server pode necessitar a acessar outros dados em log para atender outras possíveis demandas.

Estes processos são conhecidos como leitura ou escrita do transact-log, situações que podem impactar aleatoriamente no processamento de nossas querys.

Pensando neste tipo de possibilidade, o post de hoje visa ilustrar e apresentar como através do Management Studio ferramenta de administração e gerenciamento do Microsoft SQL Server podemos em conjunto com as DMVs – Dynamic Management Views listadas abaixo, monitorar, catalogar e identificar quais querys ou transações estão fazendo uso do Transact-Log em possíveis processos de leitura ou escrita do log.

Dynamic Management Views

Para saber mais sobre cada DMV acesse as referências apresentadas no final do post:

  • sys.dm_tran_session_transactions;
  • sys.dm_tran_active_transactions;
  • sys.dm_tran_database_transactions;
  • sys.dm_exec_sessions;
  • sys.dm_exec_requests; e
  • sys.dm_exec_sql_text.

O Cenário

Para tentar ilustrar como podemos realizar de maneira fácil este tipo de monitoramento e identificação, vamos criar um pequeno cenário através do Bloco de Código 1, onde estaremos realizando a criação de um novo banco de dados denominado UsingTransactLog para armazenar a tabela TabelaTransactionLog.

— Bloco de Código 1 —
— Criando o banco de dados —
Create Database UsingTransactLog
Go

— Acessando o banco de dados —
Use UsingTransactLog
Go
— Criando a tabela —
Create Table TabelaTransactLog
(TransactionID Int Default Current_Transaction_ID(),
TransactionUser Varchar(100) Default System_User,
TransactionDate DateTime Default Current_TimeStamp)
Go

 

Note que nosso tabela TabelaTransactLog possui um simples estrutura composto por três colunas: TransactionID, TransactionUser e TransactionDate, sendo estes elementos básicos utilizados para catalogar o ID da transação em processamento, o usuário conectado ao banco de dados e data/hora do momento da inserção da linha.

Até aqui nada de tão importante, mas importante para podemos simular o uso do log, nosso próximo passo consiste no processamento do Bloco de Código 2, onde estaremos realizando o processamento de uma transação de inserção de 50.000 mil linhas de registros, sendo a operação de insert uma das mais diversas operações que demando processos de escrita ao transact – log, sendo assim, apresento a seguir o Bloco de Código 2:

— Bloco de Código 2 —
Insert Into TabelaTransactLog Default Values
Go 50000

Observe que não estamos passando nenhum tipo de valores para nossas colunas, na verdade estou forçando o SQL Server a utilizar a criação de valores padrões durante o processamento de cada linha de registro através da instrução Default Values (valores padrões), o que vai obrigar nossa instância e ter que gerar valores durante o tempo de processamento e posteriormente registrar em log.

Muito bem, o processamento desta massa de dados, teremos a necessidade de abrir uma nova query para justamente fazer uso do Bloco de Código 3, sendo este o principalmente elemento do nosso cenário, a query que nos permiti monitorar e identificar as transações que estão em tempo real utilizando o transact-log. Então abra uma nova query e log o Bloco de Código 3 apresentado a seguir:

— Bloco de Código 3 —
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Go

SELECT tst.session_id As ‘Sessão’,
es.original_login_name As ‘Login’,
DB_NAME(tdt.database_id) AS ‘Database’,
DATEDIFF(SECOND, tat.transaction_begin_time, GETDATE()) AS ‘Tempo decorrido’,
tdt.database_transaction_log_record_count AS ‘Espaço Utilizado’,
CASE tat.transaction_state
WHEN 0 THEN ‘A transação não foi completamente inicializada ainda…’
    WHEN 1 THEN ‘A transação foi inicializada, mas não começou…’
    WHEN 2 THEN ‘A transação esta ativa…’
    WHEN 3 THEN ‘A transação foi encerrada…’
    WHEN 4 THEN ‘Foi iniciado o processo de confirmação sobre o transação distribuída…’
    WHEN 5 THEN ‘A transação está em estado preparação e esperando resolução…’
    WHEN 6 THEN ‘A transação foi confirmada…’
    WHEN 7 THEN ‘A transação esta sendo revertida para o estado anterior…’
    WHEN 8 THEN ‘A transação foi revertida para o estado anterior…’
   ELSE ‘Estado da transação desconhecido’
   END AS ‘Estado da Transação’,
SUBSTRING(TXT.text, ( er.statement_start_offset / 2 ) + 1, ((CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), TXT.text)) * 2
ELSE er.statement_end_offset
END – er.statement_start_offset ) / 2 ) + 1) AS ‘Query Atual’,
TXT.text AS ‘Query relacionada’,
es.host_name As ‘Hostname’,
   CASE tat.transaction_type
    WHEN 1 THEN ‘Transação Read/Write’
    WHEN 2 THEN ‘Transação Read-Only’
    WHEN 3 THEN ‘Transação de Sistema’
                WHEN 4 THEN ‘Transação distribuída’
            ELSE ‘Tipo de Transação desconhecido’
            END AS ‘Tipo da Transação’,
tat.transaction_begin_time AS ‘Iniciado em’
FROM sys.dm_tran_session_transactions AS tst INNER JOIN sys.dm_tran_active_transactions AS tat
ON tst.transaction_id = tat.transaction_id
INNER JOIN sys.dm_tran_database_transactions AS tdt
ON tst.transaction_id = tdt.transaction_id
INNER JOIN sys.dm_exec_sessions es
ON tst.session_id = es.session_id
INNER JOIN sys.dm_exec_requests er
ON tst.session_id = er.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) TXT
ORDER BY [Tempo decorrido] DESC
Go
Pois bem, você pode estar querendo saber um pouco mais sobre o que exatamente esta acontecendo ao processar o Bloco de Código 3, pode ficar tranquilo que a seguir vou fazer um pequeno overview sobre este bloco de código, começando pelo conjunto de colunas retornadas por ele:
  • Sessão – Apresenta o ID da sessão que esta utilizando o transact-log;
  • Login – Nome da conta de login que esta conectada ao SQL Server;
  • Database – Nome do banco de dados utilizado pela query que esta acessando o transact-log;
  • Tempo decorrido – Representa o tempo de processamento que o query esta demandando para utilizar o transact-log;
  • Log Record Count– Identifica a quantidade de registros de log gerados pela query ou transações em processamento;
  • Estado da transação – Esta coluna apresenta o estado atual da query em processamento, sendo este formado por um conjunto de valores;
  • Query Atual – Apresenta as instruções que estão sendo processadas por cada query;
  • Query Relacionada– Apresenta as instruções processadas por querys que podem estar relacionadas com as atuais querys em processamento;
  • Hostname – Apresenta o nome dos hosts utilizados por cada query em processamento;
  • Tipo de Transação – Identifica se a transação em processamento esta realizando acesso de leitura ou escrita ao log, bem como, se esta query é uma query de sistema ou distribuída; e
  • Iniciado em – Apresenta o horário de início do acesso ao transact-log por parte da query em processamento.

Agora que você já conhece um pouco sobre cada coluna que será retornada após o processamento do Bloco de Código 3, vou abordar o uso da instrução Set Transaction Isolation Level declarada no início do código, que possui a finalidade de alterar no nível de isolamento do nosso banco de dados para Read Uncommitted afim de força o SQL Server a descartar ou desconsiderar qualquer possível ocorrência de bloqueios tanto no nível de tabela e registros, mas principalmente no escopo de banco de dados.

Nota: Se você me perguntar que isso é obrigatório ser feito para conseguir identificar as querys que estão utilizando o transact-log eu vou responder NÃO, isso não é obrigatório, mas pode ser considerada uma possível solução para garantir que o Bloco de Código 3 possa ser processado e consultar as dynamic management views na busca das informações catalogadas por ela em tempo de execução.

Ufa, estamos quase lá, agora nos resta fazer a execução do mesmo e observar o retorno dos dados coletados por este bloco de código, com base, na Figura 1 apresentada abaixo:

Figura 1 – Relação de querys em execução utilizando transact-log.

Perfeito, nosso monitoramento funcionou, conseguimos coletar os dados referentes as querys que estão neste momento em processamento fazendo acesso e utilizando o transact-log criado para o banco de dados UsingTransactLog.

Isso não é sensacional, desta forma, chegamos ao final de mais um post dedicado a sessão Dica do Mês, espero que você tenha gostado.

Referências

Post anteriores

  1. https://pedrogalvaojunior.wordpress.com/2017/03/01/dica-do-mes-microsoft-sql-server-2016-sp1-novo-argumento-use-hint-disponivel-para-query-hints/
  2. https://pedrogalvaojunior.wordpress.com/2017/01/16/dica-do-mes-conhecendo-a-nova-dmf-sys-dm_exec_input_buffer-no-microsoft-sql-server-2016/

  3. https://pedrogalvaojunior.wordpress.com/2016/11/28/dica-do-mes-sql-server-2016-sp1-comando-create-or-alter/

  4. https://pedrogalvaojunior.wordpress.com/2016/10/24/dica-do-mes-sql-server-2016-obtendo-informacoes-sobre-o-cache-de-execucao-de-funcoes/

Conclusão

Administrar, gerenciar, cuidar e prover um ambiente sempre no melhor estado possível não é uma das atividades mais tranquilas e simples desempenhadas por profissionais ou administradores de banco de dados, mas também não pode ser considerada um “bicho de sete cabeças” ou uma “caixa preta” ainda mais quando este servidor de banco de dados utiliza o Microsoft SQL Server.

Neste post você pode observar que através do uso de alguns DMVs – Dynamic Management Views a Microsoft nos permite coletar, analisar e entender de forma visual e muito intuitiva como o um servidor ou instância SQL Server gerencia o que esta sendo processado por nossas aplicações, querys ou transações no que se relaciona aos seus componentes e recursos internas, algo que nos permite em diversas situações de administração ou manutenção tentar identificar o que pode estar ocorrendo, bem como, impactando em nosso ambiente.

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.

Um forte abraço, bom feriado, espero que neste momento você não esteja no seu trabalho tentando resolver problemas do seu servidor (kkkkk).

Valeu….

Short Scripts – Março 2017


Hoje é sexta – feira, sexta – feira…

Salve, salve comunidade e amantes de bancos de dados e SQL Server, Tudo bem? Mais uma final de semana próximo após uma longa semana de muito trabalho, se eu for falar de muito trabalho, sinceramente  esta semana foi complicada, repleta de novidades e muita troca de conhecimento.

Conforme o prometido no final de 2016, estou retornando com o primeiro post da sessão Short Scripts, sessão criada a alguns anos no meu blog que lentamente esta conseguindo ajudar diversos profissionais da área de banco de dados na busca por exemplos de códigos que possam solucionar ou elucidar na resolução de um determinado problema.

O post de hoje

Como já destaquei acima, este é o primeiro post de 2017 dedicado exclusivamente a sessão Short Scripts, na relação de scritps selecionados para hoje, você vai poder encontrar códigos relacionados com os seguintes assuntos:

  • Auditoria,
  • Comando Intersect,
  • Comando OpenQuery,
  • Comando Order By,
  • Datatype Char,
  • Datatype Int,
  • DMF Sys.dm_exec_sessions,
  • DMV Sys.system_internals_partitions,
  • DMV Sys.system_internals_allocation_units,
  • Função Substring,
  • Índices,
  • Informações sobre conexão de usuário,
  • Páginas de Dados,
  • Trigger, e
  • Variáveis.

Então mãos no teclado, a seguir apresento os códigos e exemplos selecionados para o Short Script – Março 2017. Vale ressaltar que todos os scripts publicados nesta sessão são devidamente testados antes de serem publicados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, vale sim todo cuidado possível para evitar maiores problemas. Fique a vontade para compartilhar, comentar, melhorar cada um destes códigos.

Short Scripts

— Short Script 1 – Realizando Auditoria in Live —

SELECT [Spid] = session_Id
, ecid
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
, [Status] = er.status
, [Wait] = wait_type
, [Individual Query] = SUBSTRING (qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset END –
er.statement_start_offset)/2)
,[Parent Query] = qt.text
, Program = program_name
, Hostname
, nt_domain
, start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE session_Id > 50              — Ignore system spids.
AND session_Id NOT IN (@@SPID)     — Ignore this current statement.
ORDER BY 1, 2
Go

— Short Script 2 – Utilizando comando Intersect com Datatypes Char e Int —

Create Table #A (x Char(2));
Go
Insert Into #A Values (‘1’);
Insert Into #A Values (‘6’);
Insert Into #A Values (‘2’);
Insert Into #A Values (‘3’);
Insert Into #A Values (‘5’);
Insert Into #A Values (‘5’);
Insert Into #A Values (‘6’);
Insert Into #A Values (‘9’);
Go
Create Table #B (M BigInt);
Go
Insert Into #B Values(5);
Insert Into #B Values(5);
Insert Into #B Values(6);
Insert Into #B Values(7);
Insert Into #B Values(7);
Go
— (Select #1)
SELECT x AS ‘Select #1’ FROM #A
INTERSECT SELECT M FROM #B
Go
— (Select #2)
SELECT DISTINCT(x) AS ‘Select #2’
FROM #A LEFT OUTER JOIN #B
ON #A.x = #B.M
Go
— (Select #3)
SELECT DISTINCT(x) AS ‘Select #3’
FROM #A LEFT OUTER JOIN #B
ON #A.x = #B.M
Go
— (Select #4)
SELECT DISTINCT(x) AS ‘Select #4’
FROM #A INNER JOIN #B
ON #A.x = #B.M
Go
— (Select #5)
SELECT x AS ‘Select #5’
FROM #A INNER JOIN #B
ON #A.x = #B.M
Go
— Short Script 3 – Utilizando comando OpenQuery com variáveis —
— Valores Básicos —
DECLARE @TSQL varchar(8000), @VAR char(2)
SELECT  @VAR = ‘teste’
SELECT  @TSQL = ‘SELECT * FROM OPENQUERY(MeuLinkedServer,”SELECT * FROM MinhaTabela WHERE User = ””’ + @VAR + ”””’)’
EXEC (@TSQL)
Go
— Query Complexa —
DECLARE @OPENQUERY nvarchar(4000), @TSQL nvarchar(4000), @LinkedServer nvarchar(4000)
SET @LinkedServer = ‘MyLinkedServer’
SET @OPENQUERY = ‘SELECT * FROM OPENQUERY(‘+ @LinkedServer + ‘,”’
SET @TSQL = ‘SELECT au_lname, au_id FROM pubs..authors”)’
EXEC (@OPENQUERY+@TSQL)
— Use o Sp_executesql procedimento armazenado —
DECLARE @VAR char(2)
SELECT  @VAR = ‘CA’
EXEC MyLinkedServer.master.dbo.sp_executesql
N’SELECT * FROM pubs.dbo.authors WHERE state = @state’,
N’@state char(2)’,
@VAR
Go
— Short Script 4 – Realizando order by com base na função SubString —
Declare @Tabela Table
(Codigo VarChar(15))
Insert Into @Tabela Values(‘191-XXX-003’)
Insert Into @Tabela Values(‘192-XXX-003’)
Insert Into @Tabela Values(‘193-XXX-003’)
Insert Into @Tabela Values(‘194-XXX-003’)
Insert Into @Tabela Values(‘195-XXX-003’)
Insert Into @Tabela Values(‘191-XXX-001’)
Insert Into @Tabela Values(‘192-XXX-001’)
Insert Into @Tabela Values(‘193-XXX-001’)
Insert Into @Tabela Values(‘194-XXX-001’)
Insert Into @Tabela Values(‘195-XXX-001’)
Insert Into @Tabela Values(‘191-XXX-002’)
Insert Into @Tabela Values(‘192-XXX-002’)
Insert Into @Tabela Values(‘193-XXX-002’)
Insert Into @Tabela Values(‘194-XXX-002’)
Insert Into @Tabela Values(‘195-XXX-002’)
Select * from @Tabela
Order By SubString(codigo,Len(Codigo)-2,3) Asc
Go
— Short Script 5 – Monitorando querys em execução —
SELECT
DES.SESSION_ID,
DES.CPU_TIME,
DES.READS,
DES.WRITES,
DES.LOGICAL_READS,
DES.ROW_COUNT,
DER.SESSION_ID,
DES.STATUS,
DES.HOST_NAME,
DES.PROGRAM_NAME,
DES.LOGIN_NAME,
DES.ORIGINAL_LOGIN_NAME,
DEC.CLIENT_NET_ADDRESS,
DEC.AUTH_SCHEME,
DEC.NET_TRANSPORT,
SUBSTRING(T.[TEXT], DER.[STATEMENT_START_OFFSET] / 2,
COALESCE(NULLIF(DER.[STATEMENT_END_OFFSET], – 1) / 2, 2147483647)) AS COMANDO
FROM
SYS.DM_EXEC_SESSIONS AS DES
INNER JOIN SYS.DM_EXEC_REQUESTS DER
ON DER.BLOCKING_SESSION_ID = DES.SESSION_ID
INNER JOIN SYS.DM_EXEC_CONNECTIONS DEC
ON DEC.SESSION_ID = DES.SESSION_ID
INNER JOIN SYS.DM_EXEC_REQUESTS DER2
ON DER2.SESSION_ID = DES.SESSION_ID
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(DER.[SQL_HANDLE]) AS T
GO
— Short Script 6 – Identificando tabelas e suas respectivas páginas de dados —
— Exemplo 1: —
SELECT P.partition_id,
OBJECT_NAME(P.object_id) As ObjectName,
U.allocation_unit_id,
SU.First_Page,
SU.Root_Page,
SU.First_IAM_Page
From Sys.Partitions As P INNER JOIN Sys.Allocation_Units As U
ON P.hobt_id = U.container_id
Inner Join Sys.system_internals_allocation_units SU
On u.allocation_unit_id = su.allocation_unit_id
Go
— Exemplo 2: —
SELECT SIP.partition_id,
OBJECT_NAME(SIP.object_id) As ObjectName,
sip.rows,
SU.First_Page,
SU.Root_Page,
SU.First_IAM_Page
From Sys.system_internals_partitions As SIP Inner Join Sys.system_internals_allocation_units SU
On sip.partition_id = su.allocation_unit_id
Go
— Short Script 7 – Identificando índices com Escrita Excessiva —
— Quantidade de Índices com Escrita Excessiva em comparação a leitura —
SELECT  OBJECT_NAME(s.object_id),
i.name,
i.type_desc
FROM    sys.dm_db_index_usage_stats s WITH ( NOLOCK ) Inner JOIN sys.indexes i WITH (NOLOCK)
ON s.index_id = i.index_id
AND s.object_id = i.object_id
WHERE OBJECTPROPERTY(s.[object_id], ‘IsUserTable’) = 1
AND s.database_id = DB_ID()
AND s.user_updates > ( s.user_seeks + s.user_scans + s.user_lookups )
AND s.index_id > 1
Go
— Quantidade de Índices com Escrita Excessiva —
SELECT  COUNT(*)
FROM    sys.dm_db_index_usage_stats s WITH ( NOLOCK )
WHERE   OBJECTPROPERTY(s.[object_id], ‘IsUserTable’) = 1
AND s.database_id = DB_ID()
AND s.user_updates > ( s.user_seeks + s.user_scans + s.user_lookups )
AND s.index_id > 1
Go
— Short Script 8 – Identificando o espaço ocupado por conexões em uso —
SELECT A.session_id,
B.host_name, B.Login_Name ,
(user_objects_alloc_page_count + internal_objects_alloc_page_count)*1.0/128 as TotalalocadoMB,
D.Text
FROM sys.dm_db_session_space_usage A Inner JOIN sys.dm_exec_sessions B
ON A.session_id = B.session_id
Inner JOIN sys.dm_exec_connections C
ON C.session_id = B.session_id
CROSS APPLY sys.dm_exec_sql_text(C.most_recent_sql_handle) As D
WHERE A.session_id > 50
and (user_objects_alloc_page_count + internal_objects_alloc_page_count)*1.0/128 > 100 — Ocupam mais de 100 MB
ORDER BY totalalocadoMB desc
COMPUTE sum((user_objects_alloc_page_count + internal_objects_alloc_page_count)*1.0/128)
Go
— Short Script 9 – Obtendo a diferença de datas entre registro anterior e o próximo registro —
Create Table Datas
(ID Int Identity(1,1),
DataInicio Date,
DataFinal Date)
Go
Insert Into Datas Values (GETDATE(), GETDATE()+30)
Insert Into Datas Values (GETDATE()+1, GETDATE()+20)
Insert Into Datas Values (GETDATE()+2, GETDATE()+10)
Insert Into Datas Values (GETDATE(), GETDATE()+5)
Go
— Exemplo 1 —
Select I.ID, I.DataInicio, F.DataFinal,
DateDiff(D, I.DataInicio, F.DataFinal) As Intervalo
From Datas I Left Join Datas F
On I.ID = F.ID + 1

Go

— Exemplo 2 —
SELECT
[current].Id,
[current].Time CurrentValue,
[next].Time          NextValue
FROM #temp AS [current] LEFT JOIN #temp AS [next]
ON [next].Id = (SELECT MIN(Id) FROM #temp

                                 WHERE Id > [current].Id)
Go
— Short Script 10 – Criando um trigger condicional —
–Criando a Table de Novos Produtos–
Create Table NovosProdutos
(Codigo Int Identity(1,1),
Descricao VarChar(10))
–Criando a Table de Histórico Novos Produtos–
Create Table HistoricoNovosProdutos
(Codigo Int,
Descricao VarChar(10))
Go
–Inserindo valores —
Insert Into Novosprodutos Values(‘Arroz’)
Insert Into Novosprodutos Values(‘Arroz1’)
Insert Into Novosprodutos Values(‘Arroz2’)
Insert Into Novosprodutos Values(‘Arroz3’)
Go
–Criando a Trigger para controle de histórico–
Create TRIGGER T_Historico
ON NovosProdutos
for update
AS
IF (Select Descricao from Inserted) <> (Select Descricao from Deleted)
BEGIN
INSERT Into HistoricoNovosProdutos (Codigo, Descricao)
SELECT Codigo, Descricao FROM INSERTED
END
Go
–Fazendo os teste —
Update NovosProdutos
Set Descricao=’Arroz 4′
Where Codigo = 1
Go
Update NovosProdutos
Set Descricao=’Arroz1′
Where Codigo = 2
Go
Select * from NovosProdutos
Go

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


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

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

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

Links

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

Agradecimento

Mais uma vez obrigado por sua visita, um forte abraço…

Nos encontramos em breve nos próximos posts desta e outras sessões do meu blog, 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 uns 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á…..

Dica do Mês – Microsoft SQL Server 2016 SP1 – Novo argumento USE HINT disponível para Query Hints


Boa tarde minha comunidade, salve, salve meu mestre SQL e porta bandeira Server……

É isso pessoal, quarta – feira de cinzas para muitos no brasil, mas graças a deus deste mal ou depressão eu não vou morrer, estou retornando em mais um post da sessão Dica do Mês, e se por acaso você não tem acessado o meu blog nos últimos meses não tem problema, uso um dos links abaixo para acessar as três últimos dicas do mês publicadas:

1.      https://pedrogalvaojunior.wordpress.com/2017/01/16/dica-do-mes-conhecendo-a-nova-dmf-sys-dm_exec_input_buffer-no-microsoft-sql-server-2016/

2.      https://pedrogalvaojunior.wordpress.com/2016/11/28/dica-do-mes-sql-server-2016-sp1-comando-create-or-alter/

3.      https://pedrogalvaojunior.wordpress.com/2016/10/24/dica-do-mes-sql-server-2016-obtendo-informacoes-sobre-o-cache-de-execucao-de-funcoes/

Hoje vou destacar mais uma melhoria adicionada recentemente ao Microsoft SQL Server 2016 a partir da instalação do Service Pack 1, sendo este sem dúvida nenhuma o maior conjunto de melhorias e inovações já adicionadas ao SQL Server desde suas primeiras versões.

Caso você ainda não fez download deste service pack, não perca tempo, acesse o link abaixo e realize o download o quanto antes: https://pedrogalvaojunior.wordpress.com/2016/11/19/microsoft-disponibiliza-service-pack-1-para-o-microsoft-sql-server-2016/

Dando continuidade, hoje vou apresentar o novo Query Hint adicionado após a instalação do SP1 chamado de USE HINT, quando encontrei informações sobre este novo recursos acabei ficando um pouco confuso e sem entender em qual momento ele poderia ser útil, mas após um busca mais detalhada pude ter a certeza que valia a pena compartilhar um pouco sobre esta novidade com vocês.

Se você ainda não ouvir falar sobre uso de query hints ou dicas de consulta existentes no SQL Server, isso assunto não é nada novo muito pelo contrário trata-se de uma das práticas mais comuns utilizadas por profissionais de bancos de dados e desenvolvedores com objetivo de otimizar a execução de uma ou mais transações.

Agora no SP1 do 2016 o time de engenheiros do SQL Server adicionou este novo argumento que nos permite dentre suas finalidades fazer uso de alguns dos mais específicos query hints sem exigir elevação no nível de permissão, declaração de trace flags ou até mesmo que o usuário que esteja fazendo uso deste hint pertença ao server role sysadmin.

Bom se você quiser saber um pouco mais sobre este novo argumento, continue lendo este post….


Introdução

Cenários no qual SQL Server deve ser sugerido mudança de comportamento para o query optimizer são bastante comuns de serem encontrados, tradicionalmente o uso de query hints apresentam um papel fundamental neste contexto independente de qual sinalizador você esteja utilizando, sendo este reconhecido como um documentado ou não-documentados.

Entretanto, quando estes sinalizadores de rastreamento são definidos globalmente, eles podem ter um efeito adverso sobre outras cargas de trabalho. Além disso, o uso por sessão não é prático com aplicativos, bem como, por consulta com QUERYTRACEON, sendo que esta opção requer associação na função de servidor fixa sysadmin. Embora você pode contornar esse comportamento usando um guia de plano ou um procedimento armazenado, credenciais elevadas são ainda necessárias.

Estes sinalizadores de rastreamento são conhecidos como trace flags,  usados para configurar temporariamente características de servidor específico ou para desligar um determinado comportamento, podem ser difíceis de gerenciar e compreender sua forma de uso ou impactos.

A partir do Microsoft SQL Server 2016 SP1 temos a capacidade de fazer uso do novo query hint argument USE HINT diretamente em nossas querys ou transações de forma simples, rápida e segura, contornando qualquer necessidade de elevação permissional ou mudança no contexto de execução.

Dentre os diversos query hints existentes no SQL Server, o USE HINT nos permite fazer uso dos seguintes:

Opção Sinalizador de rastreamento equivalente Descrição
FORCE_LEGACY_CARDINALITY_ESTIMATION TF 9481 Permite que você defina o modelo de estimativa de cardinalidade do otimizador de consulta para o SQL Server 2012 e versões anteriores, independentemente do nível de compatibilidade do banco de dados.
ENABLE_QUERY_OPTIMIZER_HOTFIXES TF 4199 Controles de consulta alterações otimizador lançadas em Service Packs e atualizações cumulativas do SQL Server.
DISABLE_PARAMETER_SNIFFING TF 4136 Desabilita o parâmetro de rastreamento, a menos que o valor OPTION(RECOMPILE), WITH RECOMPILE ou OPTIMIZE FOR seja usado.
ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES TF 4137 Faz com que o SQL Server gere um plano usando seletividade mínima ao estimar e predicados para filtros de correlação, em modelo de estimativa de cardinalidade de consulta otimizador do SQL Server 2012 e versões anteriores.
DISABLE_OPTIMIZER_ROWGOAL TF 4138 Faz com que o SQL Server gere um plano que não usa ajustes de meta de linha com consultas que contenham as palavras-chave TOP, OPTION (FAST N), IN ou EXISTS.
ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS TF 4139 Ativar automaticamente gerado estatísticas rápidas (emenda do histograma), independentemente do status da coluna de chave. Se essa opção for usada, independentemente do status de coluna estatísticas principais (em ordem crescente, decrescente ou papel de carta), o histograma usado para estimar a cardinalidade será ajustado em tempo de compilação de consulta.
ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS TF 9476 Faz com que o SQL Server gerar um plano usando a suposição de confinamento simples em vez da suposição de confinamento de Base padrão, no query optimizer cardinalidade estimativa modelo do SQL Server 2014 através de versões do SQL Server 2016.
DISABLE_OPTIMIZED_NESTED_LOOP TF 2340 Faz com que o SQL Server não use uma operação de classificação (classificação em lotes) para associações de loop aninhado otimizado ao gerar um plano.
FORCE_DEFAULT_CARDINALITY_ESTIMATION TF 2312 Permite que você definir o modelo de estimativa de cardinalidade de Otimizador de consulta para o SQL Server de 2014 por meio de versões do SQL Server 2016, dependentes do nível de compatibilidade do banco de dados.

Para consultar esta relação de hint options disponíveis para uso com USE HINT, a Microsoft adicionou um nova DMV denominada: sys.dm_exec_valid_use_hints, sendo esta melhor detalhada no próximo post da sessão Para que serve (não deixe de conferir). A seguir destaco algumas formas de uso do novo USE HINT em conjunto com alguns hints options.

Exemplos

Para ilustrar e ajudar a entender melhor os exemplos a seguir, utilize o banco de Dados AdventureWorksDWCTP3 disponível para download através do link: https://www.microsoft.com/en-us/download/details.aspx?id=49502

— Exemplo 1 – Utilizando o hint option FORCE_LEGACY_CARDINALITY_ESTIMATION —

usehint

— Exemplo 2 – Utilizando o hint option FORCE_LEGACY_CARDINALITY_ESTIMATION em conjunto com a QUERYTRACEON 9481 —

usehint2

Dica: Para que você possa entender e analisar melhor como o SQL Server 2016 se comportar a partir do momento que o argumento USE HINT é declarado, recomendo que você habilite a apresentação do recurso de Include Actual Execution Plan em seu Management Studio e execute estes exemplos sem a declaração do USE HINT e posteriormente com a declaração do mesmo, além disso, também realize a comparação dos planos de execução para notar e identificar as mudanças comportamentais.

Referências

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

·         https://support.microsoft.com/en-us/help/3189813/update-introduces-use-hint-query-hint-argument-in-sql-server-2016

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

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

·         https://blogs.msdn.microsoft.com/sql_server_team/developers-choice-use-hint-query-hints/

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, mantendo o produto no seu mais alto nível de funcionalidades, recursos e inovações.

Neste post você pode perceber mais uma melhoria adicionada ao produto, onde através deste novo query hint USE HINT temos a capacidade de usar dicas de consulta orientando o query optimizer a mudar sua forma de execução e comportamento sem necessitarmos de elevação no nível de permissão ou fazermos partes de uma função de servidor sysadmin, algo que representa uma grande evolução por parte do produto no que se relacionado a controle de permissões e níveis de segurança.

Sinceramente eu vejo este novo recurso como um pequeno easter egg, pois na minha humilde opinião estava muito mas muito bem escondido.

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.

Valeu….