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 – Identificando as Top 10 querys mais pesadas e seus respectivos planos de execução


Olá, boa tarde, salve, salve….. tudo bem?

Por um instante até pensei que estava ouvindo aquela conhecida música “Alô, alô realengo aquele abraço……”, mas não era isso não, estou cá com meus botões pensando como começar mais este post, sinceramente esta faltando muita criatividade para tentar fazer algo diferente, mesmo assim, vou tentando quem sabe um dia eu consigo.

Seguindo em frente, falando sério, seja bem vindo a mais um post do meu blog dedicado a sessão “Dica do Mês“, que alias vem recebendo nos últimos dias diversos feedback sobre os atuais posts, bem como, sugestões para novos, mais uma vez obrigado pela participação.

Como a voz do povo é a voz de deus, e se o povo esta pedindo temos que tentar agradar, vou publicar na dica deste mês, um script que eu particularmente uso muito, mas muito mesmo, principalmente quando eu escuto a seguinte frase:  “Nossa como o servidor de banco de dados esta lento….” ou esta outra “O sistema esta lento, muito lento, não consigo fazer nada…..” ao ouvir uma destas frases sinceramente eu fico louco e feliz ao mesmo tempo(kkkkk), louco por muitas vezes o usuário, desenvolvedor, gestor enfim a pessoa que falou não tem noção do que esta dizendo, e feliz porque vou mergulhar de cabeça no lado interno do SQL Server, como eu costumo dizer para meus alunos na lado sobrio e quente de  um servidor ou instância SQL Server, conhecido por muitos como SQL Server Internals.

Alias se conhecer e trabalhar com este lado do SQL Server, posso dizer que não é coisa de outro mundo mas requer muito calma, cuidado e conhecimento, principalmente no que se relaciona as camadas do Database Engine, Storage Engine, Buffer Cache, Query Processor, Query Optimizer, entre outros.

Antigamente poderíamos dizer que esta seria uma tarefa não muito amigável, algo que mudou muito nos últimos anos com a avanço das novas versões do SQL Server e principalmente pela possibilidade de contato com os maiores profissionais do mundo relacionados a banco de dados e SQL Server, dentre os quais destaco: Paul S. Randal, Kalen Delaney, Kimberly L. Tripp, sem se esquecer dos nossos brasileiros Fabricio Catae, Fabiano Amorin, Luciano Moreira, peço desculpas aos outros não citados, mas todos sabem do meu respeito e admiração.

Continuando nossa viagem, e se você que neste momento acabou de se deparar com esta mesma situação que eu relatei anteriormente, por algum motivo de uma hora para outro seu servidor de banco de dados ou até mesmo um determinado sistema começou a apresentar uma possível lentidão, nossa é uma situação bastante difícil e em muitos momento complexa para se decidir em pouco tempo ou tomar alguma possível ação.

É justamente nestes momentos que devemos tentar estabelecer uma linha de raciocínio, uma baseline para darmos início a nossa frente de combate, procurando definir uma forma de análise, mapeando os possíveis riscos e impactos, impactos que normalmente você já conhece e terá que conviver com ele até encontrar a possível causa raiz deste cenário que esta se apresentando.

Causa raiz que por diversas situações ou condições esta se apresentando em seu servidor ou instância SQL Server oriunda da execução de uma ou mais querys consideradas, as quais podem estar gerando um custo de processamento altíssimo para o Database Engine ou Storage Engine em seus componentes que a transforma em uma possível query candidata a participar da lista de querys pesadas.

Você pode estar estranhando isso, mas é exatamente desta forma que o SQL Server nos permite identificar e categorizar nossas querys, onde através de uma análise da complexidade do seu plano de execução, em conjunto com indicadores internos como:

  • Execution Count;
  • Logical Reads;
  • Logical Writes; e
  • Total Elapsed.

Podemos dizer que esta ou outra query esta presente na lista de querys impactantes ao processamento do nosso servidor, ou até mesmo dizer que é uma query pesada e precisa ser analisada e revista toda sua lógica e complexidade de execução.

Há dica de hoje, vai justamente nos ajudar a obter esta lista das top 10 querys consideradas com a maior carga de processamento e permitir apresentar seu plano de execução. E ai isso não é legal, vale a verdade não é uma grande dica que poderá lhe ajudar muito, particularmente falando eu acho uma dica fantástica.

Vamos então conhecer a dica deste mês apresentada abaixo:

— Dica do Mês – Top 10 querys mais pesadas e seus respectivos planos de execução —

SELECT TOP 10

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) As ‘Query’,

qs.execution_count As ‘Execution Count’,

qs.total_logical_reads As ‘Total Logical Reads’,

qs.last_logical_reads As ‘Last Logical Reads’,

qs.total_logical_writes As ‘Total Logical Writes’,

qs.last_logical_writes As ‘Last Logical Writes’,

qs.total_worker_time As ‘Total Worker Time’,

qs.last_worker_time As ‘Last Worker Time’,

qs.total_elapsed_time / 1000000 As ‘Total Elapsed Time in seconds’,

qs.last_elapsed_time / 1000000 As ‘Last Elapsed Time in seconds’,

qs.last_execution_time As ‘Last Execution Time’,

qp.query_plan As ‘Query Execution 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

ORDER BY qs.total_logical_reads DESC

Dicadomesabril1
Figura 1 – Resultado da execução da dica do mês.

 


 

 

Note que os indicadores listados anteriormente são justamente colunas existentes na DMV sys.dm_exec_query_stats, sendo esta uma das principais dynamic management view introduzidas no SQL Server a partir da versão 2008, que nos permite obter todos estes dados referentes ao custo, tempo e esforço de processamento realizado pelo SQL Server. Para saber mais sobre esta DMV acesse: https://msdn.microsoft.com/en-us/library/ms189741.aspx

Para que esta mágica funcione também utilizamos uma outra importante e conhecida DMV sys.dm_exec_text, que possui a finalidade de possibilitar obter exatamente a instrução processada por cada query através do handle “identificador” contido na coluna sql_handle, como também, e o plano de execução desta mesma query com base no seu “identificador” armazenado na coluna plan_handle. Sendo esta dmv um élo de ligação entre a sys.dm_exec_query_stats e a sys.dm_exec_query_plan. Para saber mais sobre esta DMV acesse: https://msdn.microsoft.com/en-us/library/ms181929.aspx

Além disso, outro fator muito importante esta relacionado a capacidade de apresentar de forma gráfica o plano de execução pertencente a cada query, fazendo uso da DMV sys.dm_exec_query_plan, outra fundamental e excencial capacidade adicionada ao SQL Server 2008. Para saber mais sobre esta DMV acesse: https://msdn.microsoft.com/en-us/library/ms189747.aspx

 


Muito bem, chegamos ao final de mais uma dica do mês, tenho a certeza que você gostou deste post, mais uma vez agradeço sua visita, comentários, sugestões, enfim sua participação e interesse.

Caso você ainda não tenha acessado os posts anteriores desta sessão, não se preocupe utilize os links listados abaixo e bom divertimento:

Um grande, meu muito obrigado, nos encontramos no próximo mês com mais uma dica do mês.

Até lá.