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.

#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 um 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á….

#12 – Para que serve


Boa tarde, boa tarde…. Olá pessoal, tudo bem?

Mais uma semana começando, para alguns volta as aulas (kkkkk)…. é a mamata esta acabando e o futuro deste país tem que voltar para sua realidade, no mundo capitalista que estamos vivendo, sem o mínimo de educação civica e moral não somos nada.

Deixando de lado este pequeno pensamento, seguindo em frente este é o post de número 12 dedicado exclusivamente a sessão Para que serve, que lentamente esta atraindo novos seguidores ao meu Blog.

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 #12 – Para que serve de hoje, tenho a certeza que você vai gostar.

No post de hoje, vou a destacar uma alteração que a Microsoft introduziu no novo SQL Server 2016, que a partir desta versão alterar de maneira direta o comportamento padrão existente atualmente para alocação de dados e autocrescimento para os bancos de dados de usuário ou para o system database TEMPDB.

Em contra partida, neste post vou destacar um pouco sobre a relação das Trace Flag 1117 e 1118 para com estes dois recursos que compõem o SQL Server, sabendo que durante anos ambas foram recomendadas pelas equipes de engenheiros da Microsoft como técnicas para alterar este comportamento padrão, que a partir da versão 2016 poderá ser realizado de uma maneira bem diferente ou até mesmo de forma automática.

Vamos lá….começa aqui o #12 – Para que serve – Alterando o comportamento padrão para alocação de dados e autocrescimento no Microsoft SQL Server 2016 –

Introdução

Até a versão 2014 o Microsoft SQL Server apresentava o mesmo padrão definido desde a versão 2000 para alocação de dados e autocrescimento de banco de dados, comportamento que poderia ser alterado através do uso de recursos externos entre deles as tão conhecidas e temidas trace flags.

Para um melhor entendimento, vou abordar brevemente os dois conceitos, visando esclarecer um pouco o papel de cada um deles, começando por:

Alocação de Dados: Quando se referimos a alocação de dados em uma instância ou servidor SQL Server, estamos fazendo referência a dois recursos de extrema importância que forma o SQLOS, me refiro ao Database Engine e Storage Engine, sendo estes responsáveis em possibilitar o armazenamento, contenção e consumo de dados manipulados pelo SQL Server.

Como destacado anteriormente o Microsoft SQL Server até a versão 2014 não apresentava a capacidade de criar páginas de dados iniciais ou as primeiras oito páginas de dados conhecidas como extended (extensão) identificadas internamente como páginas ou extensões mista, no qual as primeiras páginas ou extended deveriam se iguais, uniforme, do mesmo tipo e apresentar a mesma estrutura contendo somente informações relacionadas a tabelas ou índices.

Você pode estar se perguntando, mas isso não era possível de ter alterado nas versões mais antigas? A resposta simples e direta é SIM, e para tal finadade eramos obrigados a utilizar a Trace Flags 1118 (se quiser saber mais sobre ela acesse: https://www.brentozar.com/archive/2014/06/trace-flags-1117-1118-tempdb-configuration/)

Mas isso na versão 2016 não é mais necessário, para oferecer e permitir esta mudança de comportamento o time de engenheiros da Microsoft dedicados ao SQL Server aplicaram uma pequena mudança na estrutura da Dynamic Management View: sys.databases existente desde as primeiras versões do produto, na qual foi adicionada uma nova coluna chamada is_mixed_page_allocation_on, que pode ser utilizada através do comando ALTER DATABASE. Falarei um pouco mais sobre esta nova coluna posteriormente.

Dando continuidade, vamos conhecer um pouco sobre AutoGrow (Autocrescimento):

Autocrescimento: Opção aplicada aos bancos de dados que define qual deverá ser a fator e forma de crescimento de um banco de dados, também sofreu algumas mudanças.

A partir do SQL Server 2016 todo processo de autocrescimento e alocação de dados será realizado de forma automática, no qual o Database Engine e parceria com o Storage Engine serão autosuficientes capazes de identificar a necessidade de mudar a forma de alocação e autocrescimento do banco de dados, sem recorrer a necessidade de fazer uso da trace flag 1117.

Desta forma, de acordo com a distribuição dos dados alocados em seus respectivos arquivos de dados ou filegroups permitirá que quando um arquivo de dados crescer todos os demais arquivos relacionados ao banco de dados ou filegroup deverão crescer ao mesmo tempo, sendo este o novo comportamento adotado para este banco de dados, algo revolucionará se levarmos em consideração do processo desempenho pelo Storage Engine para alocar e contar os dados.

Esta mudança de comportamento pode ser considerada uma peça chave para o SQL Server no que se relaciona a performance, pois de maneira simultânea teremos arquivos alocados ao mesmo tempo na mesma transação, oferecendo uma redução no tempo estimado para contenção de alocação de dados, o que no final das contas provacará uma sensível diminuição para o Storage Engine controlar o número de pontos de marcação de dados relacionado ao que está alocado para uso.

MIXED_PAGE_ALLOCATION

Nova opção adicionada ao comando ALTER DATABASE que permite aplicar aos bancos de dados de usuário e system database TEMPDB a nova forma de alocação de dados adotada para a versão 2016 do SQL Server, denominada Mixed Page Allocation ou Alocação de Páginas Mistas, na qual destacado anteriormente será possível alocar para toda estrutura de um banco de dados o uso de páginas ou extended mistas, aplicada de implícita para tabelas e índices.

A coluna is_mixed_page_allocation_on apresenta dois valores, sendo eles:

  • 0 = A estrutura de tabelas e índices será alocada de forma uniforme e não permitirá que as primeras páginas de dados ou a primeira extended possa ser formada por uma estrutura mista.
  • 1 = A estrutura de tabelas e índices poderá ser alocada de forma mista permitindo que as primeras páginas de dados ou a primeira extended possa ser formada por uma estrutura mista.

Vale ressaltar que ao realizar uma simples consulta na DMV sys.databases, valor padrão apresentado na coluna is_mixed_page_allocation_on para os bancos de dados de usuário é 0(zero), e para os bancos de dados de sistema: Master, Model e MSDB é 1(Hum) sendo que para este bancos de dados não é permitido alterar a forma de alocação.

Perguntas e respostas

Muito bem, você pode estar coçando a sua cabeça e ainda contendo algumas dúvidas sobre ese possível novo comportamento entre outros conceitos aqui apresentados, no intuito de tentar ajudar, elaborei algumas perguntas:

1. Afinal esta nova opção Mixed_Page_Allocations possui alguma relação com as trace flags 1117 e 1118?
Respondendo de bate pronto: SIM possuem total relação.

2. Esta nova opção substituio uso de ambas as traces flags?
Sim, tem este finalidade mas aplicada somente a partir da versão 2016.

3. Após alterar a forma de alocação para Mixed Page Allocation posso voltar ao formato anterior?
Sim, sem nenhum tipo de risco ou impedimento.

Além das questões a Tabela 1 apresentada abaixo poderá lhe ajudar a entender em qual cenário você poderá fazer uso da mixed_page_allocation ou das trace flags 1117 e 1118:

Database TF 1117 TF 1118
tempdb Não requerida (default) Não requerida (default)
user databases Por padrão será realizada o autocrescimento de forma simples, ou seja, de um único arquivo por vez. Use ALTER DATABASE <dbname> MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES para habilitar o crescimento para todos os arquivos de forma simultânea Não requerida. Use ALTER DATABASE <dbname> SET MIXED_PAGE_ALLOCATION  ON para voltar a utilizar alocação mista.
Other system databases (master, model, msdb) -NA- Alocação de páginas em modo mista não pode ser alterada para estes bancos de dados.

Tabela 1 – Cenários para uso da alocação mista ou mudança no autocrescimento.

Referências

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

https://msdn.microsoft.com/en-US/library/bb522682.aspx

https://support.microsoft.com/en-us/kb/2964518

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

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

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

Links

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

https://pedrogalvaojunior.wordpress.com/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

Cuidar da vida de nossos dados é algo muito importante, mas saber como e de que forma estes podem ser armazenados esta bem acima de qualquer outra preocupação, pensando nisso a Microsoft permitiu a partir da versão 2016 alterar de forma simples, rápida e segura a maneira com nossos bancos de dados podem crescer no decorrer do tempo, bem como, as estruturas internas podem ser criadas e alocadas, capacidade que nos permite melhrorar de maneira sensível atividades relacionadas a como nossos dados podem estar alocados para consulta, possibilitando ganhos de processamento de dados.
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 – 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.

Monitorando o progresso de execução de querys em tempo real no Microsoft SQL Server 2014.


Olá pessoal, boa tarde!!!

E ai quais as novidades? Estamos se aproximando da primavera, e pensar que nos encontramos no inverno com temperaturas na casa dos 30° graus que coisa, algo bastante fora do comum para esta época do ano, mas é a realidade que estamos vivendo.

Aproveitando esta onda de calor ou melhor dizendo de temperaturas um pouco mais elevados, quero também entrar nesta onda e compartilhar com vocês algo que considero quente para qualquer profissional de banco de dados.

Você pode estar se perguntando, algo quente para um profissional de banco de dados:

  • Será que ele esta falando em processos de restauração de dados?
  • Não, não, o Junior Galvão esta pensando em Failover Cluster, AlwasOn….
  • Putz, não é isso não, ele esta pensando em performance, tunning, estatísticas…

Talvez esta última possibilidade passe perto, na verdade como diria meu pai, “Estou matutando aqui….” sobre monitoramento em tempo real no SQL Server. E ai você diz, “monitoramento em tempo real do SQL Server”, ai isso é fácil, isso já existem a muito tempo, podemos utilizar:

  • O SQL Server Profiler;
  • Podemos fazer uso de Trace Files;
  • Podemos utilizar o Activity Monitor; e
  • Ou até mesmo Extendend Events.

Sim eu sei disso, já fazemos isso de diversas formas e em versões mais antigas do Microsoft SQL Server, mas o que realmente eu estou pensando e quero destacar é uma nova DMV que foi introduzida na versão 2014 do SQL Server e que mudou um pouco para não dizer bastante a maneira que podemos acompanhar em tempo real o que esta acontecendo no SQL Server, mas o quanto cada uma “transações” ou “querys” esta executando.

Então vamos nessa, a brincadeira de hoje é apresentar e destacar a nova DMV sys.dm_exec_query_profiles disponível a partir do Microsoft SQL Server 2014 em todas as suas edições, conforme destaca a documentação oficial da Microsoft: https://msdn.microsoft.com/pt-br/library/dn223301.aspx

 

Introdução

Você tem uma consulta no SQL Server que sempre demora muito para ser executada ou encerrada. Esta é o cenário mais comum que qualquer DBA passa pelo menos uma vez por mês em seu trabalho, e ai diversos e diversos questionamentos, análises, considerações são feitas. Você pensa, eu tenho o plano de execução, vou fazer a análise deste plano para reconhecer e entender o que esta consulta esta fazendo ou deveria fazer.

E ai uma das maiores dúvidas que pode passar sobre sua cabeça, o que será exatamente que esta consulta esta fazendo neste momento? Para tentar ajudar a responder a esta pergunta e muitas outras, foi que o time de desenvolvimento e engenharia do Microsoft SQL Server, teve a fantásitca ideia de liberar na versão 2014 a DMV sys.dm_exec_query_profiles.

Agora você já deve ter percebido que esta DMV(Dinamyc Management View ou Visão de Gerenciamento Dinâmico) é algo bastante especial, pois mostrar em tempo real o que a sua consulta esta fazendo naquele momento, somente o Microsoft SQL Server pode te oferecer isso.

 

Conhecendo a sys.dm_exec_query_profiles

Como destacado anterior a sys.dm_exec_query_profiles, consiste basicamente em uma das novas DMVs que foram disponibilizadas em conjunto com o Microsoft SQL Server na versão 2014. Por padrão sua finalidade é monitorar o progresso da consulta em tempo real, enquanto a consulta está em execução. Para tal objetivo, ela faz uso de contadores executados em segundo plano, trabalhando como threads.

Os contadores estão organizados em duas categorias:

  • Primeira categoria contadores cumulativos: Como row_count, elapsed_time_ms,
  • Segunda categoria: Conhecidos como “marcadores” ou “carimbos” de data/hora. Os carimbos de data/hora marcam o tempo que certos eventos acontecem e podem ser usados para correlacionar esses eventos com dados externos no SQL Server. Exemplos desses dados são perfmon, XPerf, etc.

Vale ressaltar, que os contadores fornecem dados em uma granularidade maior do que SET STATISTICS IO ON já que eles são por interpretados e tratados como thread.

Pensar como esta dmv trabalha não é algo fácil, mas como o SQL Server é repleto de funcionalidades, torna-se possível entender.

 

Comportamento

A sys.dm_exec_query_profiles durante seu período de execução, realiza a coleta de informações de forma serializada e apresenta estes dados após o final da sua execução no modelo do SHOWPLAN XML, ou seja, os dados são estruturados na forma de plano de execução que podem ser apresentados e analisados diretamente no Management Studio.

 

Considerações

A forma de uso da sys.dm_exec_query_profiles é bastante simples e comum para aqueles já acostumados a trabalhar com as DMVs ou Diretivas SETs existente no SQL Server, o primeiro passo consiste na utilização da SET STATISTICS PROFILE ON(Introduzida no Microsoft SQL Server 2008) ou SET STATISTICS XML ON(Introduzida no Microsoft SQL Server 2005).

Para maiores informações sobre a SET STATISTICS XML ON acesse: https://msdn.microsoft.com/en-us/library/ms176107.aspx

Ao fazer uso da sys.dm_exec_query_profiles por padrão o SQL Server vai disponibilizar o resultado desta DMV através de uma tabela com a seguinte estrutura:

Nome da coluna Tipo de dados Descrição
session_id smallint Identifica a sessão na qual esta consulta é executada. Referencia dm_exec_sessions.session_id.
request_id int Identifica a solicitação de destino. Referencia dm_exec_sessions.request_id.
sql_handle varbinary(64) Identifica a consulta de destino. Referencia dm_exec_query_stats.sql_handle.
plan_handle varbinary(64) Identificar a consulta de destino. Referencia dm_exec_query_stats.plan_handle.
physical_operator_name nvarchar(256) Nome do tipo de operador físico.
node_id int Identifica um nó do operador na árvore de consulta.
thread_id int Distingue os threads (para uma consulta paralela) que pertencem ao mesmo nó do operador de consulta.
task_address varbinary(8) Identifica a tarefa do sistema operacional SQL que esse thread está usando. Referencia dm_os_tasks.task_address.
row_count bigint Número de linhas retornadas pelo operador até o momento.
rewind_count bigint Número de retrocessos até o momento.
rebind_count bigint Número de reassociações até o momento.
end_of_scan_count bigint Número de término de exames até o momento.
first_active_time bigint A hora em que o construtor foi chamado primeiro em milissegundos.
estimate_row_count bigint Número estimado de linhas. Pode ser útil comparar estimated_row_count com o row_count real.
last_active_time bigint A hora em que o construtor foi chamado por último em milissegundos.
open_time bigint Tempo total de CPU (em milissegundos) acumulados por operações do nó de destino (por exemplo abertura/fechamento/getrow) até o momento.
first_row_time bigint Carimbo de data/hora quando aberto (em milissegundos).
last_row_time bigint Carimbo de data/hora quando obtém a primeira linha (em milissegundos).
close_time bigint Carimbo de data/hora quando fechado (em milissegundos).
elapsed_time_ms bigint A última hora em que o nó foi visto no estado de execução até o momento.
cpu_time_ms bigint Tempo total decorrido (em milissegundos) acumulados por operações do nó de destino (por exemplo abertura/fechamento/getrow) até o momento.
database_id smallint O banco de dados no qual o conjunto de linhas é aberto.
object_id int A tabela na qual o conjunto de linhas é aberto.
index_id int O índice (se houver) no qual o conjunto de linhas é aberto.
scan_count bigint Número de verificações de tabela/índice até o momento.
logical_read_count bigint Número de leituras lógicas até o momento.
physical_read_count bigint Número de leituras físicas até o momento.
read_ahead_count bigint Número de read-aheads até o momento.
write_page_count bigint Número de gravações de página até o momento devido ao derramamento.
lob_scan_count bigint Número de verificações de tabela/índice LOB até o momento.
lob_logical_read_count bigint Número de leituras lógicas LOB até o momento.
lob_physical_read_count bigint Número de leituras físicas LOB até o momento.
lob-read_ahead_count bigint Número de read-aheads LOB até o momento.
segment_read_count int Número de read-aheads de segmento até o momento.
segment_skip_count int Número de segmentos ignorados até o momento.

Você deve ter observado que o conjunto de dados retornados por esta dmv apresenta uma relação com outras DMVs existentes no SQL Server, dentre elas destaco:

Colocando a mão na massa….na verdade no teclado

Pensando em uma forma bastante simples e prática para simular o uso desta dmv, vamos trabalhar em um cénario muito comum para qualquer profissional da área de banco de dados, desenvolvimento e análise. Basicamente nosso ambiente será estrutura da seguinte forma:

  • Banco de Dados – DBMonitor; e
  • Tabelas – Impostos e Paises.

Na tabela de impostas faremos o cadastro de uma lista de diversos impostos fictícios, informando a taxa base do imposto e uma taxa de equilíbrio. Já a tabela de Países iremos fazer o cadastro de alguns países e posteriormente estabeleceremos um relacionamento entres estas duas tabelas, apresentando seu plano de execução com seus operadores e em seguida vamos utilizar a sys.dm_exec_query_profiles.

Em mãos a obra, começando pelo Código 1:

— Código 1 – Preparando o ambiente –

— Criando o Banco de Dados DBMonitor —

Create Database DBMonitor

Go

— Acessando o Banco de Dados DBMonitor —

Use DBMonitor

Go

Observe que realizamos dois dos mais comuns procedimentos existentes no SQL Server, criar um banco de dados através do comando Create Database e acessar este banco através do comando Use.

Agora vamos criar nossas duas respectivas tabelas: Impostos e Países, conforme apresenta o Código 2:

 

— Código 2 – Criando as Tabelas —

— Criando a Tabela Países —

Create Table Paises

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

Nome Varchar(80))

Go

— Criando a Tabela Impostos —

Create Table Impostos

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

IdPais Int Not Null,

TaxaBase Float,

TaxaEquilibrio As (TaxaBase)*10)

Go

— Adicionando o relacionamento —

Alter Table Impostos

Add Constraint [FK_ImpostosxPaises] Foreign Key (IdPais)

References Paises(Id)

Go

Nosso próximo passo é gerar uma massa de dados para estas tabelas, vale ressaltar que a estrutura das tabelas e seus respectivos dados são totalmente fictícias, não existe nenhum tipo de relação, veracidade ou regra de negócio aplicada neste cenário. Vamos em frente, Código 3:

— Código 3 – Inserindo a massa de dados –

— Inserindo a massa de dados de Países —

SET NOCOUNT ON

Go

Insert Into Paises Values(‘Brasil’),

(‘Jamaica’),

(‘Kiribati’),

(‘Papau Nova Guiné’),

(‘São Tomé e Príncipe’),

(‘Estônia’)

Go

— Inserindo a massa de dados de Impostos —

SET NoCount On

Go

Declare @Contador TinyInt

Set @Contador = 0

While @Contador < 255

Begin

Insert Into Impostos (IdPais, TaxaBase)

Values(IIF(@contador <=50, 1,

IIF(@Contador >=51 And @Contador <=100, 2,

IIF(@Contador >=101 And @Contador <=150, 3,

IIF(@Contador >=151 And @Contador <=200, 4,

IIF(@Contador >=201 And @Contador <=220, 5, 6))))),

RAND()*4)

Set @Contador = @Contador + 1

End

 

Perfeito, já temos nossa massa de dados criada, ambas as tabelas já estão populadas, vamos então simular um junção destes dados, posteriormente ver o plano de execução em seguida faremos uso da dmv sys.dm_exec_query_profiles para verificar em tempo real o processamento da nossa query, seguindo em frente com o Código 4:

 

 

— Código 4 – Inserindo a massa de dados –

Select P.Nome,

Sum(I.TaxaBase) As SomaTaxaBase,

Sum(I.TaxaEquilibrio) As SomaTaxaEquilibrio

From Impostos I Inner Join Paises P

On I.IdPais = P.Id

Group By P.Nome

Order By SomaTaxaEquilibrio

Neste momento acabamos de fazer uma simples consulta solicitando para o SQL Server que retorne a relação de países, com a soma das respectivas taxa base e taxa de equilíbrio. Para ilustrar o resultado desta query apresento a Figura 1:

progresso1

Figura 1 – Resultado da query executada no código 4.

Agora vamos ver o plano de execução utilizado por este mesma query, para isso apresenta a seguir a Figura 2:

progresso2

Figura 2 – Plano de execução utilizado pelo SQL Server para processar a query do código 4.

Mas você pode estar se pergunta, a onde esta a dmv sys.dm_exec_query_profiles, em qual parte do código ela foi utilizada, até agora em nenhuma parte, este será o nosso próximo passo. Para isso utilizaremos o Código 5:

— Código 5 – Monitorando o processamento da nossa query em tempo real —

— Atividando o monitoramento da nossa query —

SET STATISTICS PROFILE ON;

Go

— Executando a query —

Select P.Nome,

Sum(I.TaxaBase) As SomaTaxaBase,

Sum(I.TaxaEquilibrio) As SomaTaxaEquilibrio

From Impostos I Inner Join Paises P

On I.IdPais = P.Id

Group By P.Nome

Order By SomaTaxaEquilibrio Desc

Go

Observe que utilizamos a mesma query declarada na código 4, a diferença é que agora fizemos a declaração da diretiva SET STATISTICS PROFILE ON, onde estamos orientando o SQL Server a fazer todo monitoramente em tempo real da nossa query, sendo assim, teremos o seguinte resultado, conforme apresenta a Figura 3:

progresso3

Figura 3 – Resultado do monitoramento em tempo real do processamento do código 5.

Mas ainda não temos realmente um monitoramente próximo ao que esta sendo executado em nosso SQL Server, para isso acontecer tempo que fazer uso da sys.dm_exec_query_profiles ao mesmo tempo que nossa query esta sendo processada, sendo assim, podemos imaginar que somente querys que venham a demandar um custo de processamento considerável pela SQL Server poderão ser acompanhadas.

Vamos então, turbinar um pouco mais o nosso cenário, vamos criar uma nova tabela chamada BigTable, com base, nesta tablea vamos tentar acompanhar e monitorar nossa query. Vamos então utilizar o Código 6:

— Código 6 – Criando a BigTable –

— Criando a Tabela BigTable —

CREATE TABLE BigTable

(OrderID int NOT NULL IDENTITY(1, 1),

CustomerID int NULL,

OrderDate date NULL,

Value numeric (18, 2) NOT NULL)

GO

— Alterando a Tabela Adicionando Primary Key —

ALTER TABLE BigTable

ADD CONSTRAINT PK_BigTable PRIMARY KEY CLUSTERED  (OrderID)

GO

— Inserindo 5 Milhões de Linhas de Registro —

Insert Into BigTable(CustomerID, OrderDate, Value)

SELECT Top 15000000

ABS(CONVERT(Int, (CheckSUM(NEWID()) / 10000000))) As CustomerID,

CONVERT(Date, GetDate() – ABS(CONVERT(Int, (CheckSUM(NEWID()) / 10000000)))) As OrderDate,

ABS(CONVERT(Numeric(18,2), (CheckSUM(NEWID()) / 1000000.5))) As Value

FROM sysobjects a, sysobjects b, sysobjects c, sysobjects d

GO

A BigTable já esta criada e populada com uma grande massa de dados, podemos dar continuidade em nosso estudo, fazendo justamente o monitoramento da nossa query, para isso vamos utilizar o Código 7:

— Código 7 – Consulta pesada –

SELECT a.CustomerID,

CASE (SELECT AVG(b.Value)

FROM BigTable b

WHERE b.CustomerID = a.CustomerID)

WHEN 1000 THEN ‘Média = 1 mil’

WHEN 2000 THEN ‘Média = 2 mil’

WHEN 3000 THEN ‘Média = 3 mil’

WHEN 4000 THEN ‘Média = 4 mil’

WHEN 5000 THEN ‘Média = 5 mil’

ELSE ‘Não é número exato’

END AS Sts

FROM BigTable AS a

GROUP BY a.CustomerID

ORDER BY a.CustomerID

OPTION (MAXDOP 1)

Note que nossa query utiliza o operador Case, algo que normalmente consumo muito do SQL Server, além disso, estamos fazendo agrupamento de dados, organização e removendo o uso do paralelismo com a opção MaxDop 1.

Agora vamos para os finalmente, abra uma nova query, volta para a query que você declarou o código 7 faça com que o SQL Server execute esta query, retorne para nova query e execute o Código 8, apresentado a seguir:

— Código 8 – Monitorando nossa query pesada em tempo de execução –

SELECT  session_id ,

node_id ,

physical_operator_name ,

SUM(row_count) row_count ,

SUM(estimate_row_count) AS estimate_row_count ,

IIF(COUNT(thread_id) = 0, 1, COUNT(thread_id)) [Threads] ,

CAST(SUM(row_count) * 100. / SUM(estimate_row_count) AS DECIMAL(30, 2)) [% Complete] ,

CONVERT(TIME, DATEADD(ms, MAX(elapsed_time_ms), 0)) [Operator time] ,

DB_NAME(database_id) + ‘.’ + OBJECT_SCHEMA_NAME(QP.object_id,

qp.database_id) + ‘.’

+ OBJECT_NAME(QP.object_id, qp.database_id) [Object Name]

FROM    sys.dm_exec_query_profiles QP

GROUP BY session_id , node_id , physical_operator_name ,

qp.database_id , QP.OBJECT_ID , QP.index_id

ORDER BY session_id , node_id

GO

Pronto, era justamente isso que precisavamos, neste momento enquanto nossa query pessada esta sendo processada, o Microsoft SQL Server 2014, através da dmv sys.dm_exec_query_profiles encontra-se em tempo real realizando o monitoramento do progresso de execução do nosso Código 7, para ilustrar e comprovar apresento a Figura 4:

progresso4

Figura 4 – Monitoramento em tempo real da execução do Código 7.

Caso você não tenha notado, existe uma coluna declarada como % Complete, esta coluna é um dos principais marcadores ou melhor indicadores do acompanhamento e evolução do progresso de execução da nossa query. Para realmente comprovar este monitoramento, repita mais duas ou três vezes a execução do Código 8.

Provavelmente a nossa query ainda encontra-se em execução e o SQL Server poderá retornar novas informações sobre o andamento de processamento a qualquer momento, desta forma, chegamos ao final de mais este post.

Conclusão

Neste post foi possível destacar como o Microsoft SQL Server 2014 esta a frente das demais versões, principalmente no que se relacionamento a monitoramento em tempo real, através da Visão de Gerenciamento Dinâmico – Sys.dm_exec_query_profiles, a Microsoft introduziu um novo mecanismos que nos permite obter dados importantes do que nossa query ou transação esta fazendo naquele exatamente.

Acredito que esta funcionalidade venho justamente como forma de possibilitar aos profissionais de banco de dados e aqueles que trabalham com este tipo de tecnologia, como é possível entender e identificar em qual ponto do processamento de nossas querys o SQL Server esta apresentando maiores dificuldades ou até mesmo lentidão.

Além dos recursos já existentes, dentro eles o SQL Server Profiler, a sys.dm_exec_query_profiles, nos garante de forma simples, fácil, rápida e prática uma outra maneira de interpretar e reconhecer todos os passos necessário que o SQL Server usa para conseguir coletar e resultar os dados solicitados á ele.

Mais uma vez a Microsoft inovou, mostrou como é possível evoluir uma ferramenta tão fantástica quando o SQL Server, sem impactar em sua estrutura, forma de trabalho e configurações, com certeza é um grande passo alcançado com esta nova feature.

Agradeço a você por sua visita, espero que tenha gostado deste artigo, fique a vontade para compartilhar com seus contatos.

Nos encontramos em breve.

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.