Monitorar Processo Criação de Index (SQL Server)

Dale galera hoje vamos falar um pouco de uma rotina para monitorar o processo de criação de Index

Quem que nunca precisou de criar um index em horário de produção e ficou se cagando todo !!!

Bom a menos que a versão do SQL Server seja 2016 ou superior e Enterprise, que possibilita utilizar a opção de criar um index ONLINE ( dai já é outra historia ).

Para criar um index em horário de operação tem que acima de tudo entender a regra de negócio aplicada assim como validar os impactos que esse pode causar com a alocação da tabela. Para possibilitar a analise do tempo de criação e progresso criei a view VW_PROGRESS_SESSION.

Para que seja possível acompanhar o progresso na criação de um Index, é preciso setar o parâmetro SET STATISTICS PROFILE ON, e executar junto com o comando de Create do Index , como no exemplo abaixo:

SET STATISTICS PROFILE ON

GO

CREATE INDEX [IX_nome_index] ON [Nome_Database].[dbo].[Nome_Tabela] ([Coluna_index])
INCLUDE ([Colunas_include])
WITH(FILLFACTOR= 80, ONLINE=ON); -- Hint ONLINE disponível somente na versão Enterprise do SQL Server

Após rodar o script de create do index, abra uma nova aba do Management Studio e rode a consulta da View, como no exemplo abaixo:

SELECT
*
FROM VW_PROGRESS_SESSION
WHERE COMMAND = 'CREATE INDEX'

Será retornado um resultado do progresso com as colunas abaixo:

command,  -- Comando que esta sen executado No caso ( 'CREATE INDEX')
session_id, --  Id da Seção
start_time,  --  Data hora que iniciou a execução
text,            -- Script que está rodando na seção
Ds_Operador_Atual,
Qt_Linhas_Total,
Qt_Linhas_Processadas,
Qt_Linhas_Restantes,
Vl_Percentual_Completado,
Qt_Segundos_Decorridos,
Qt_Segundos_Restantes,
Dt_Prevista,
host_name,
program_name

Imagem de exemplo:

Bom saber que a estimativa prevista do percentual completado, e Data previsa para termino é calculado conforme o total de linhas processadas, ou seja, é uma estimativa


Script de criação da view VW_PROGRESS_SESSION:

/*
SELECT
 * 
FROM VW_PROGRESS_SESSION
WHERE COMMAND = 'CREATE INDEX'
--WHERE COMMAND = 'UPDATE'
--'CREATE INDEX'
--'ALTER INDEX'

*/

CREATE VIEW VW_PROGRESS_SESSION
AS
WITH CTE_SESSION AS 
(
    SELECT
        B.session_id,
  C.command ,
        B.login_time,
        B.[host_name],
        B.[program_name],
        B.nt_user_name,
        B.original_login_name,
        D.[text],
        MIN(C.start_time) AS start_time,
        SUM(A.[row_count]) AS Qt_Linhas_Processadas,
        SUM(A.[estimate_row_count]) AS [Qt_Linhas_Total],
        MAX(A.last_active_time) - MIN(A.first_active_time) AS [Qt_Tempo_Decorrido_MS],
        MAX(IIF(A.[close_time] = 0 AND A.[first_row_time] > 0, [physical_operator_name], N'<Transition>')) AS [Ds_Operador_Atual]
    FROM
        sys.dm_exec_query_profiles A
        JOIN sys.dm_exec_sessions B ON B.session_id = A.session_id
        JOIN sys.dm_exec_requests AS C WITH (NOLOCK) ON B.session_id = C.session_id
        CROSS APPLY sys.dm_exec_sql_text(C.[sql_handle]) D
    --WHERE    C.command = 'CREATE INDEX'
    GROUP BY
        B.session_id,
  C.command ,
        B.login_time,
        B.[host_name],
        B.[program_name],
        B.nt_user_name,
        B.original_login_name,
        D.[text]
),
CTE_RESULT AS 
(
    SELECT
         *,
         ( [Qt_Linhas_Total] - Qt_Linhas_Processadas ) AS [Qt_Linhas_Restantes],
         ( [Qt_Tempo_Decorrido_MS] / 1000.0 ) AS [Qt_Segundos_Decorridos]
     FROM
         CTE_SESSION
)
SELECT
 command ,
    session_id,
    start_time,
    [text],
    [Ds_Operador_Atual],
    [Qt_Linhas_Total],
    Qt_Linhas_Processadas,
    [Qt_Linhas_Restantes],
    CONVERT(DECIMAL(5, 2), (( Qt_Linhas_Processadas * 1.0 ) / [Qt_Linhas_Total] ) * 100) AS [Vl_Percentual_Completado],
    [Qt_Segundos_Decorridos],
    (( [Qt_Segundos_Decorridos] / Qt_Linhas_Processadas ) * [Qt_Linhas_Restantes] ) AS [Qt_Segundos_Restantes],
    DATEADD(SECOND, (( [Qt_Segundos_Decorridos] / Qt_Linhas_Processadas ) * [Qt_Linhas_Restantes] ), GETDATE()) AS [Dt_Prevista],
    [host_name],
    [program_name],
    nt_user_name,
    original_login_name
FROM
    CTE_RESULT

Snippets de código Transact-SQL

Olá Pessoal !!, voltando aqui no blog hoje para falar um pouco sobre Snippets de código Transact-SQL


Um snippet Transact-SQL.code é um modelo que contém a estrutura básica de um bloco ou uma instrução Transact-SQL . Você pode usar os snippets como ponto de partida ao adicionar instruções ao Editor de Consultas do Mecanismo de Banco de Dados . Você pode inserir os snippets predefinidos fornecidos com o SQL Server ou criar o seu próprio.


Mas Luiz explica melhor oque é isso e para que server esse tal de Snippets, galera nada mais que um atalho que você pode configurar no SQL Managament Studio para abrir seus código T-SQL mais utilizados com mais facilidade.



Para usar a essa opção precisamos adotar alguns padrões, seu código T-SQL precisar estar em um arquivo salvo com a extensão .Snippet e seguir o formato como na imagem abaixo:
essa imagem tenho um script que retorna as bases da instancia e tamanho: 

Seguindo o padrão acima, respeitando as tags, faça o mesmo com todos os seus scripts mais utilizados e salve em uma pasta, eu coloquei os meus na parta Aux_DBA, como exemplo abaixo:
Imagem01

Após salvar seus scripts em um diretório especifico e não se esqueça o arquivo precisa estar com a extensão .Snippet. Para que o Management Studio reconheça os snippets é preciso informar o local onde está os arquivos, veja como fazer essa configuração na imagem abaixo:
Passo a passo:
1 - Acesse Tools e em seguida Code Snippets Manager
Imagem02

2 - Click Add, selecione o diretório que salvou todos os scripts  (Imagem01), e depois em Selecionar Pasta, como na imagem abaixo:
Imagem03

Feito esse passo seus scripts ja podem ser acessados facilmente, abra uma nova Aba do SQLQuery, e em seguida use as combinações de teclas, CTRL+K, CTRL+X e vai ser apresentado o Snipped como na imagem abaixo:
Imagem04

Lembra o diretório que criamos Aux_DBA e no passo anterior configuramos para ler os scripts Snipped, após as combinações de teclas conforme passo anterior será exibido uma guia conforme imagem acima (Imagem04), selecione Aux_DBA e será exibido o titulo definido na tag dos arquivos em seguida conforme a imagem abaixo:
Imagem05

Conforme imagem acima, selecione o script que queira , e seguida o script será exibo na SQLQuery.
Imagem06

Script Modelo:

<?xml version="1.0" encoding="utf-8" ?> 
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet"> 
<CodeSnippet Format="1.0.0"> 
 <Header>
  <Title>Size Database </Title>
  <Description>Database Size Info</Description>
  <Author>Luiz F L Silva</Author> 
  <SnippetTypes><SnippetType>Expansion</SnippetType></SnippetTypes>
 </Header>
 <Snippet>
 <Code Language="SQL">
  <![CDATA[ -- Inicio Script
  with fs
  as
  (
   select database_id, type, size * 8.0 / 1024 size
   from sys.master_files
  )
  select
   name as NomeDoBanco,
   ((select sum(size) from fs where type = 0 and fs.database_id = db.database_id)/1024) DataFileSizeGb,
   ((select sum(size) from fs where type = 1 and fs.database_id = db.database_id)/1024) LogFileSizeGB
  into #tmp_database
  from sys.databases db


  select 
  *
  from #tmp_database
  order by DataFileSizeGb desc
  drop table #tmp_database; -- Fim Script
  ]]>
 </Code>
 </Snippet>
</CodeSnippet></CodeSnippets>

Bom galera por hoje é isso ai, espero que essa dica seja util !!!


"Confiai-me todas as suas preocupações" (I Pedro 5:7)




"Sem sonhos, a  vida não tem brilho. Sem metas, os sonhos não tem alicerces. Sem prioridades, os sonhos não se tornam reais. Sonhe, trace metas, estabeleça prioridades e corra riscos para executar seus sonhos. Melhor é errar por tentar do que errar por omitir."

Um pouco sobre o Execution plan (plano de execução) do SQL Server

Um pouco sobre o Execution plan (plano de execução) do SQL Server

Como você lê os planos de execução? Da direita para a esquerda, esquerda para a direita ou verificando os custos? Ou o que dizer de objetos como verificações de índice, verificações de tabela e pesquisas?

Embora o SQL Server geralmente gere um bom plano, às vezes não é inteligente o suficiente para validar seus planos e corrigir os fracos.

Existem dois tipos de planos de execução: 

Estimated execution plan: Os planos estimados fornecem uma estimativa do trabalho que o SQL Server deve executar para obter os dados. 

Actual execution plan: Os planos de execução reais são gerados após a execução das consultas TransactSQL ou dos lotes. Por esse motivo, um plano de execução real contém informações de tempo de execução, como as métricas reais de uso de recursos e quaisquer avisos de tempo de execução.

Processamento de dados

O plano estimado e o plano real na maioria das vezes, será o mesmo, mas poderá ser diferente devido a alterações nas estatísticas, alterações relacionadas ao esquema ou alterações nos dados. Você sempre deve verificar o plano de execução real ao solucionar problemas.

Leia o plano de execução corretamente para identificar o ponto real. Comece analisando o fluxo de dados e não o custo. Não pense nas leituras lógicas ou físicas. É importante reduzir o número de operações de entrada / saída (E / S). Você sabe que acessar o armazenamento é o mais lento dos recursos de hardware; portanto, tente minimizar essa atividade. Então, como você explora as estatísticas e o plano de execução mostra isso? Sim! Confira as linhas de direção passando o mouse sobre o indicador da direita para a esquerda. Ele exibe o número de registros e o tamanho dos dados. Cada linha é mais grossa ou mais fina com base no volume de dados retornado pela operação, conforme mostrado na ilustração a seguir:

Se você tiver muitos objetos, precisará de uma maneira melhor de obter uma visão geral da quantidade de dados processados para cada operação.

Caso precise reduzir o stress de E / S, consulte o valor SET STATISTICS IO ON T-SQL para obter uma ideia geral do uso de E / S para a consulta. Você deve definir isso antes da execução da consulta no SSMS para ver o resultado alternar para a guia mensagem no painel de resultados. Deve ser semelhante ao seguinte resultado:

O resultado de “logical reads 14”, mostra que o SQL Server lê páginas de 112KB (14 * 8 KB) para obter os dados da memória. Ao otimizar a consulta, não ignore as leituras lógicas, pois isso pode ser físico e lógico nos servidores ativos. Você nunca sabe se as páginas às quais você está se referindo estão na memória ou no disco. O objetivo deve ser reduzir o número de operações de leitura cumulativas.   

Estimativa versus planos reais

Após a geração do plano real, passe o mouse sobre qualquer operação ou unidade lógica no plano de execução. Consulte a imagem a seguir para ver o tipo de operações físicas, como os resultados de busca por índice em cluster ou varredura. Procure o número de execuções e o número real e estimado de linhas. Evite analisar o custo de operações individuais, porque essa é uma estimativa em segundos com base no hardware antigo e pode não fornecer detalhes precisos. Na imagem a seguir, o operador Clustered Index Seek foi executado uma vez para obter 100 registros, e o SQL estima 356 registros. A diferença pode dever-se a estatísticas desatualizadas ou ao desempenho da consulta.

Execution iterations (Iterações de Execução)

A imagem a seguir mostra o número estimado de execuções 1154121 vezes em um índice de cluster para obter um registro. Isso é significativo. Embora o custo do operador seja menor, e poderíamos simplesmente ignorá-lo, observando as outras operações com custos mais altos, isso pode ter se mostrado uma opção dispendiosa. Essas operações têm um alto impacto no desempenho da consulta, apesar de terem um índice de cluster. As pesquisas de ID de linha (RID) são uma operação semelhante para o heap.

Banco de dados temporário

A diferença entre o número estimado e real de registros é outra coisa que você deve considerar. Uma estimativa incorreta pode resultar em uma consulta com memória limitada alocada. Quando isso acontece, ele usa um banco de dados temporário (tempdb) para realizar as tarefas. A escolha incorreta de um operador ou plano do SQL Server pode resultar em operações e extrações lentas das consultas, conforme mostrado na imagem a seguir. O número real de registros é o mesmo, mas as estimativas são diferentes. Isso pode ser devido a estatísticas desatualizadas ou ausentes. Observe que as variáveis da tabela não possuem estatísticas; portanto, o plano sempre retorna 1 e 1K em novos releases até que a opção de recompilar seja usada. Portanto, variáveis de tabela não são uma boa opção para um grande número de registros.

Sort operator(Operador de classificação)

Você precisa considerar o impacto da classificação. O operador de Sort é usado principalmente para as seguintes funções: agregação, junção de mesclagem ou ordem por cláusula. Isso pode não ter impacto com apenas alguns registros, mas, com cada registro adicional, o processamento diminui. Tente evitar a classificação ou não use a ordem por cláusula. Se a classificação for necessária, use a grade do aplicativo para fazer a classificação em vez de enviar dados classificados para um aplicativo. 

Operador de spool

Outro operador importante que você precisa observar é o spool. O spool é um operador lento porque armazena objetos ocultos ou temporários ou tabelas de trabalho no tempdb. Isso também diminui a velocidade com operadores específicos que fazem com que ela seja religada ou retrocedida. Existem muitos tipos de spools no SQL Server, como Eager, Lazy, Table / Index e assim por diante. O SQL Server usa o spool quando é melhor consultar uma tabela de trabalho temporária do que retornar à tabela de origem para conjuntos de resultados intermediários. A imagem a seguir mostra um exemplo

Hash and nested loop operators (Operadores de hash e loop aninhado)

Como eles apresentam bom desempenho em pequenos conjuntos de registros, hash e loops aninhados são os próximos operadores que você deve considerar. No entanto, para grandes conjuntos de registros ou quando há uma grande diferença entre os planos estimado e real, esses operadores podem criar um grande impacto, pois podem usar tempdb em vez de memória. O SQL Server publica o seguinte aviso nos detalhes do operador: “operador usou tempdb para espalhar dados durante as execuções”. Se isso acontecer, preste atenção às estatísticas. Com uma estimativa incorreta, o loop fica aquém da alocação de memória ou continua repetindo. Veja a memória alocada para a execução. Para visualizar a alocação de memória, abra a caixa de propriedades selecionando o ponto inicial do plano de execução (da esquerda para a direita). Se nada estiver anormal, o ajuste da consulta deve ser a escolha ideal. 

Conclusão

Seu primeiro objetivo para otimizar uma consulta deve ser reduzir leituras e gravações gerais (ou seja, E / S no disco). Não se esqueça de leituras lógicas para leituras e gravações na memória. Reduzir a E / S resolve a maior parte do problema e as consultas são executadas muito mais rapidamente. 

Em seguida, observe outras operações caras devido à atividade no tempdb. Lembre-se, o tempdb é usado por muitas operações e sempre é caro. Procure por retrocessos analisando o número de execuções de qualquer operação, spools, sort e loops. Estes são caros quando usados com tempdb. 

Não se esqueça de revisar os avisos de cada operador, porque eles fornecem boas pistas. Embora esta postagem não discuta o operador de índice ausente, isso não significa que você possa ignorá-lo. Revise-o, mas não crie o índice às cegas. Verifique os outros índices disponíveis nas mesmas colunas e considere o impacto nas consultas em execução no seu banco de dados.

"A vida é a experiência mais fascinante que existe e a maior inspiração para o mundo e as pessoas."

fonte:https://developer.rackspace.com/blog/understanding-a-sql-server-execution-plan/

Como saber se seu Index está sendo bem utilizado ( foi bem ou mal criado rs.. )

Dale pessoal hoje vou falar um pouco sobre utilização de um índice especifico.
Aquele índice que acabei criei na base, será que está realmente sendo utilizado, como, será que está com as colunas corretas ou precisa de mais algum ajuste !!!

Bom vamos lá,  a consulta abaixo mostra como está sendo a utilização dos índices, quantos Seek e Scans, já é um norte para saber se podemos considerar se um índice foi bem criado ou um mal criado rs....

SELECT
 OBJECT_NAME(U.OBJECT_ID) AS "TABLE",
 I.NAME AS "INDEX", 
 U.USER_SEEKS,
 U.USER_SCANS,
 U.USER_LOOKUPS,
 U.LAST_USER_SEEK,
 U.LAST_USER_SCAN,
 U.LAST_USER_LOOKUP,
 U.LAST_USER_UPDATE
FROM SYS.DM_DB_INDEX_USAGE_STATS U
JOIN SYS.INDEXES I ON ( I."OBJECT_ID" = U."OBJECT_ID" AND I.INDEX_ID = U.INDEX_ID )
WHERE U."OBJECT_ID" = OBJECT_ID('Nome_da_Tabela') -- Nome da Tabela
 AND U.DATABASE_ID = DB_ID()
ORDER BY U.INDEX_ID ASC

Na consulta acima, identificamos se um determinado índice está sendo bem utilizado ou não.

A consulta abaixo, foi retirada do blog do Dirceu Resende (https://www.dirceuresende.com/blog/sql-server-como-identificar-as-consultas-que-utilizam-um-determinado-indice-atraves-do-plan-cache/) e com algumas melhorias, podemos verificar quais consultas estão fazendo uso do índice, e providenciar ajustes quando necessário.

SELECT
    DB_Name(b.dbid) AS DatabaseName,
    SUBSTRING(C.[text], ( A.statement_start_offset / 2 ) + 1, ( CASE A.statement_end_offset WHEN -1 THEN DATALENGTH(C.[text]) ELSE A.statement_end_offset END - A.statement_start_offset ) / 2 + 1) AS sqltext,
    A.execution_count,
    A.total_logical_reads / execution_count AS avg_logical_reads,
    A.total_logical_writes / execution_count AS avg_logical_writes,
    A.total_worker_time / execution_count AS avg_cpu_time,
    A.last_elapsed_time / execution_count AS avg_elapsed_time,
    A.total_rows / execution_count AS avg_rows,
    A.creation_time,
    A.last_execution_time,
    CAST(query_plan AS XML) AS plan_xml,
    B.query_plan,
    C.[text]
FROM
    sys.dm_exec_query_stats AS A
    CROSS APPLY sys.dm_exec_text_query_plan(A.plan_handle, A.statement_start_offset, A.statement_end_offset) AS B
    CROSS APPLY sys.dm_exec_sql_text(A.[sql_handle]) AS C
WHERE
    B.query_plan LIKE '%Nome_do_Seu_Indice%'
    AND B.query_plan NOT LIKE '%dm_exec_text_query_plan%'
 --AND DB_Name(b.dbid) = 'Nome_da_Base'
ORDER BY
    A.last_execution_time DESC
OPTION(RECOMPILE)

Deixei comentado a condição que filtra as statistics por database, pois algumas execuções podem estar vindo pelo sp_execute e sendo executada direta do master, nesse caso poderíamos identificar a base que rodou validando a coluna Text.

Um exemplo do retorno dessa ultima consulta, com detalhes de quais consultas estão consumindo um determinado índice.

Bom é isso ai, espero que esse post seja util

Reaja com otimismo e positividade a tudo, pois a vida também é a consequência de como encaramos o que nos acontece.

Scripts para monitorar o uso de buffer e memória no SQL Server

Nessa seção apresento alguns scripts para validações do uso de memória no SQL Server

Scripts para monitoração de buffer e memoria no SQL Server:

1 - Uso do buffer pool
2 - Informações de memória do sistema
3 - Informações de uso de memória de processo do SQL Server
4 - Uso de buffer por banco de dados
5 - Uso de buffer por objeto
6 - Os 25 procedimentos armazenados com maior - Leituras lógicas

1 - Uso do buffer pool:

Resultados:
BPool_Committed_MB: memória real confirmada / usada pelo processo (SQL Server).
BPool_Commit_Tgt_MB: A memória real que o SQL Server tentou consumir.
BPool_Visible_MB: Número de buffers de 8 KB no buffer pool que estão diretamente acessíveis no espaço de endereço virtual do processo (SQL Server VAS).

Análise:
BPool_Commit_Tgt_MB> BPool_Committed_MB: O SQL Server Memory Manager tenta obter memória adicional
BPool_Commit_Tgt_MB <BPool_Committed_MB: O SQL Server Memory Manager tenta reduzir a quantidade de memória confirmada
Se o valor de BPool_Visible_MB for muito baixo: Podemos receber erros de falta de memória ou o despejo de memória será criado.

 /*********************************************/
--Script: Captures Buffer Pool Usage
--Works On: 2008, 2008 R2, 2012, 2014, 2016
/*********************************************/

-- SQL server 2008 / 2008 R2

SELECT
     (bpool_committed*8)/1024.0 as BPool_Committed_MB,
     (bpool_commit_target*8)/1024.0 as BPool_Commit_Tgt_MB,
     (bpool_visible*8)/1024.0 as BPool_Visible_MB
FROM sys.dm_os_sys_info;

-- SQL server 2012 / 2014 / 2016
SELECT
      (committed_kb)/1024.0 as BPool_Committed_MB,
      (committed_target_kb)/1024.0 as BPool_Commit_Tgt_MB,
      (visible_target_kb)/1024.0 as BPool_Visible_MB
FROM  sys.dm_os_sys_info;

2 - Monitorar o uso de memória do SQL Server: informações de memória do sistema

Resultados:
total_physical_memory_mb: memória física real instalada no sistema operacional
available_physical_memory_mb: memória física disponível
total_page_file_mb: tamanho do arquivo de paginação no SO
available_page_file_mb: tamanho do arquivo de página disponível
Percentage_Used: porcentagem de memória física usada
system_memory_state_desc: Status atual de integridade da memória

Análise:
available_physical_memory_mb: deve haver algum sinal positivo com base na memória física total
available_page_file_mb: deve haver algum sinal positivo com base no arquivo total da página
Percentage_Used: 100% por um longo tempo indica uma pressão na memória
system_memory_state_desc: deve estar disponível A memória física disponível é alta / constante

/*********************************************************************/
--Script: Captures System Memory Usage
--Works On: 2008, 2008 R2, 2012, 2014, 2016
/*********************************************************************/

select
      total_physical_memory_kb/1024 AS total_physical_memory_mb,
      available_physical_memory_kb/1024 AS available_physical_memory_mb,
      total_page_file_kb/1024 AS total_page_file_mb,
      available_page_file_kb/1024 AS available_page_file_mb,
      100 - (100 * CAST(available_physical_memory_kb AS DECIMAL(18,3))/CAST(total_physical_memory_kb AS DECIMAL(18,3))) 
      AS 'Percentage_Used',
      system_memory_state_desc
from  sys.dm_os_sys_memory;

3 - Uso de memória de processo do SQL Server

Resultados:
Physical_memory_in_use: indica o processo definido em KB, conforme relatado pelo sistema operacional, bem como alocações rastreadas usando APIs de páginas grandes locked_page_allocations: especifica as páginas de memória bloqueadas na memória
virtual_address_space_committed: indica a quantidade de espaço de endereço virtual reservado que foi confirmado ou mapeado para páginas físicas.
available_commit_limit: indica a quantidade de memória disponível para ser confirmada pelo processo (servidor SQL)
page_fault_count: indica o número de falhas de página incorridas pelo processo do SQL Server

Análise:
Physical_memory_in_use: Não podemos descobrir a quantidade exata de memória física usada pelo sqlservr.exe usando o gerenciador de tarefas, mas esta coluna mostra a quantidade real de memória física usada pelo SQL Server.
locked_page_allocations: se for> 0 significa que o Locked Pages está ativado para o SQL Server, que é uma das práticas recomendadas
available_commit_limit: Indcia a quantidade de memória disponível que pode ser confirmada pelo processo sqlservr.exe
page_fault_count: páginas que buscam no arquivo de paginação no disco rígido e não na memória física. Um número constantemente alto de falhas graves por segundo representa a pressão da memória.

/**************************************************************/
-- Script: SQL Server Process Memory Usage
-- Works On: 2008, 2008 R2, 2012, 2014, 2016
/**************************************************************/
select
      physical_memory_in_use_kb/1048576.0 AS 'physical_memory_in_use (GB)',
      locked_page_allocations_kb/1048576.0 AS 'locked_page_allocations (GB)',
      virtual_address_space_committed_kb/1048576.0 AS 'virtual_address_space_committed (GB)',
      available_commit_limit_kb/1048576.0 AS 'available_commit_limit (GB)',
      page_fault_count as 'page_fault_count'
from  sys.dm_os_process_memory;

4 - Monitorar uso do buffer por banco de dados

Resultados:
db_name: nome do banco de dados na instância do servidor SQL especificada
db_buffer_pages: número total de páginas de banco de dados correspondentes que estão no buffer pool
db_buffer_Used_MB: tamanho do buffer do banco de dados usado em MB
db_buffer_Free_MB: Tamanho do buffer livre do banco de dados (soma do espaço livre em todas as páginas) em MB.
db_buffer_percent: porcentagem sábia do banco de dados de uso do Buffer Pool

Análise:
Podemos descobrir rapidamente os principais bancos de dados que estão consumindo mais memória / buffer pool da instância do SQL Server

/**************************************************************/
--Script: Database Wise Buffer Usage
--Works On: 2008, 2008 R2, 2012, 2014, 2016
/**************************************************************/

DECLARE @total_buffer INT;
SELECT  @total_buffer = cntr_value 
FROM   sys.dm_os_performance_counters
WHERE  RTRIM([object_name]) LIKE '%Buffer Manager' 
       AND counter_name = 'Database Pages';

;WITH DBBuffer AS
(
SELECT  database_id,
        COUNT_BIG(*) AS db_buffer_pages,
        SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]
FROM    sys.dm_os_buffer_descriptors
GROUP BY database_id
)
SELECT
       CASE [database_id] WHEN 32767 THEN 'Resource DB' ELSE DB_NAME([database_id]) END AS 'db_name',
       db_buffer_pages AS 'db_buffer_pages',
       db_buffer_pages / 128 AS 'db_buffer_Used_MB',
       [mbempty] AS 'db_buffer_Free_MB',
       CONVERT(DECIMAL(6,3), db_buffer_pages * 100.0 / @total_buffer) AS 'db_buffer_percent'
FROM   DBBuffer
ORDER BY db_buffer_Used_MB DESC;

5 - Uso de buffer por objeto

Resultados:
Objeto: Nome do objeto
Tipo: Tipo do objeto Ex: USER_TABLE
Índice: Nome do Índice
Index_Type: Tipo do índice "Clustered / Non Clustered / HEAP" etc
buffer_pages: o número de páginas do objeto está no buffer pool
buffer_mb: uso de buffer inteligente em objetos em MB

Análise:
A partir do script anterior, podemos obter os principais bancos de dados usando memória. Este script ajuda você a encontrar os principais objetos que estão usando o buffer pool. Os objetos principais informam os objetos que estão usando a maior parte do buffer pool. Se você achar algo suspeito, poderá cavá-lo.

/**************************************************************/
--Script: Object Wise Buffer Usage
--Works On: 2008, 2008 R2, 2012, 2014, 2016
/**************************************************************/

;WITH obj_buffer AS
(
SELECT
       [Object] = o.name,
       [Type] = o.type_desc,
       [Index] = COALESCE(i.name, ''),
       [Index_Type] = i.type_desc,
       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 o ON p.[object_id] = o.[object_id]
       INNER JOIN sys.indexes AS i ON o.[object_id] = i.[object_id] AND p.index_id = i.index_id
WHERE
       au.[type] IN (1,2,3) AND o.is_ms_shipped = 0
)
SELECT
       obj.[Object],
       obj.[Type],
       obj.[Index],
       obj.Index_Type,
       COUNT_BIG(b.page_id) AS 'buffer_pages',
       COUNT_BIG(b.page_id) / 128 AS 'buffer_mb'
FROM
       obj_buffer obj 
       INNER JOIN sys.dm_os_buffer_descriptors AS b ON obj.allocation_unit_id = b.allocation_unit_id
WHERE
       b.database_id = DB_ID()
GROUP BY
       obj.[Object],
       obj.[Type],
       obj.[Index],
       obj.Index_Type
ORDER BY
       buffer_pages DESC;

6 - Os 25 principais procedimentos armazenados mais caros por leituras lógicas

Resultados:
Nome do SP: nome do procedimento armazenado
TotalLogicalReads: Número total de leituras lógicas desde a última compilação deste procedimento armazenado
AvgLogicalReads: Número médio de leituras lógicas desde a última compilação deste procedimento armazenado
Execution_count: número de vezes que o SP foi executado desde que foi compilado
total_elapsed_time: tempo total decorrido para este processo desde a última compilação
avg_elapsed_time: tempo médio decorrido
cached_time: horário em que o procedimento armazenado foi adicionado ao cache.

Análise:
Isso ajuda a encontrar os procedimentos armazenados em cache mais caros do ponto de vista da memória
Você deve observar isso se detectar sinais de pressão da memória
Maior número de leituras lógicas significa que você precisa verificar o plano de execução para encontrar o gargalo

/**************************************************************/
--Script: Top 25 Costliest Stored Procedures by Logical Reads
--Works On: 2008, 2008 R2, 2012, 2014, 2016
/**************************************************************/

SELECT  TOP(25)
        p.name AS [SP Name],
        qs.total_logical_reads AS [TotalLogicalReads],
        qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],
        qs.execution_count AS 'execution_count',
        qs.total_elapsed_time AS 'total_elapsed_time',
        qs.total_elapsed_time/qs.execution_count AS 'avg_elapsed_time',
        qs.cached_time AS 'cached_time'
FROM    sys.procedures AS p
        INNER JOIN sys.dm_exec_procedure_stats AS qs 
                   ON p.[object_id] = qs.[object_id]
WHERE
        qs.database_id = DB_ID()
ORDER BY
        qs.total_logical_reads DESC;

"Homens fracos acreditam na sorte. Homens fortes acreditam em causa e efeito."Ralph Waldo Emerson

É isso ai, aproveite as dicas, bons estudos e bom trabalho.

fonte: http://udayarumilli.com/script-to-monitor-sql-server-memory-usage/

sp_WhoIsActive @delta_interval

sp_WhoIsActive  @delta_interval

Caros, participando do SQL Saturday de SP no dia 28/09 e da palestra do @Fabricio Lima, em uma de suas perguntas para os participantes, percebi que muitos não conheciam o recurso modo delta da sp_WhoIsActive.


Na imagem abaixo podemos notar que algumas rotinas estavam em execução a mais de 09:00, nesse caso é normal pois estava em um loop. porem ao utilizar a sp_whoisactive, as colunas Cpu, Reads e Writes traz o valor cumulativo desde o inicio do start da execução, e não o valor real nesse momento. 

click na imagem para ampliar

Eita mas e agora, quero saber oque realmente esta consumindo de Cpu, leituras e escritas !!!
Para obter esses dados podemos utilizar a rotina sp_whoisactive @delta_interval = 5, onde o delta_interval quer dizer intervalo de comparação ( esse valor pode ser de 1 a 255), ou seja, a rotina vai executar durante 5 segundos, e vai trazer a mais as colunas CPU_delta,Reads_delta,Writes_delta, com o os dados desses intervalo informado

Uma explicação mais completa está no link http://whoisactive.com/docs/26_delta/

Identificar Jobs em Execução.

Identificar Jobs em Execução.
Database_name : Database que está configurado o Job
JOB_NAME : Nome do job em execução
SESSION_ID : Id da sessão de processamento, util quando utilizado a SP_WHOISACTIVE, que retorna no Program_name somente o id do job exemplo (SQLAgent - TSQL JobStep (Job 0x026126442B14604095F04ABC60C87310 : Step 2))
start_execution_date: Inicio da Execução
time_elapsed: Tempo de Execução
step_name: Step que está em execução no momento

 ;WITH JOBDETAILS
 AS (
  SELECT DISTINCT JOB_ID = LEFT(INTR1, CHARINDEX(':', INTR1) - 1)
   ,STEP = SUBSTRING(INTR1, CHARINDEX(':', INTR1) + 1, CHARINDEX(')', INTR1) - CHARINDEX(':', INTR1) - 1)
   ,[SESSION_ID] = SPID
  FROM MASTER.DBO.SYSPROCESSES X WITH(NOLOCK)
  CROSS APPLY (
   SELECT REPLACE(X.PROGRAM_NAME, 'SQLAGENT - TSQL JOBSTEP (JOB ', '')
   ) CS(INTR1)
  WHERE SPID > 50
   AND X.PROGRAM_NAME LIKE 'SQLAGENT - TSQL JOBSTEP (JOB %'
  )
 SELECT 
  Database_name,
  NAME AS JOB_NAME,
  JD.[SESSION_ID],
  A.start_execution_date,
  CONVERT(VARCHAR, CONVERT(VARCHAR, DATEADD(ms, ( DATEDIFF(SECOND, A.start_execution_date, GETDATE()) % 86400 ) * 1000, 0), 114)) AS time_elapsed,
  d.step_name
 FROM MSDB.DBO.SYSJOBS J WITH(NOLOCK)
 INNER JOIN JOBDETAILS JD WITH(NOLOCK) ON JD.JOB_ID = CONVERT(VARCHAR(MAX), CONVERT(BINARY (16), J.JOB_ID), 1)
 INNER JOIN MSDB.DBO.SYSJOBACTIVITY A ON A.job_id = J.job_id
 INNER JOIN MSDB.DBO.SYSJOBSTEPS                   D   WITH(NOLOCK)    ON A.JOB_ID = D.JOB_ID AND ISNULL(A.LAST_EXECUTED_STEP_ID, 0) + 1 = D.STEP_ID
 --WHERE NAME LIKE '%Nome Job%';
 --WHERE JD.[SESSION_ID] = 459

SQL Server Get free space in all files in an Instance

Pensa numa consulta completa para quem precisa saber o quanto um Database está reservando de espaço em seu arquivo, assim como o tamanho dos arquivos mdf e ldf.

Com algumas melhorias aplicadas na rotina da fonte: https://www.sqlservergeeks.com/sql-server-get-free-space-in-all-files-in-an-instance/

DECLARE @DBName NVARCHAR(100) = NULL, 
        @Drive NVARCHAR(2) = NULL 
 
DECLARE @cmd NVARCHAR(4000)
IF (SELECT OBJECT_ID('tempdb.dbo.#DBName')) IS NOT NULL
DROP TABLE #DBName
CREATE TABLE #DBName (Name NVARCHAR(100))
 
IF @DBName IS NOT NULL
INSERT INTO #DBName SELECT @DBName
ELSE
INSERT INTO #DBName SELECT Name FROM sys.databases WHERE state_desc = 'ONLINE'
 
IF (SELECT OBJECT_ID('tempdb.dbo.##FileStats')) IS NOT NULL
DROP TABLE ##FileStats
CREATE TABLE ##FileStats (ServerName NVARCHAR(100), DBName NVARCHAR(100), FileType NVARCHAR(100), 
FileName NVARCHAR(100), CurrentSizeMB FLOAT, FreeSpaceMB FLOAT, PercentMBFree FLOAT, FileLocation NVARCHAR(1000))
 
WHILE (SELECT TOP 1 * FROM #DBName) IS NOT NULL
BEGIN
 
    SELECT @DBName = MIN(Name) FROM #DBName
 
    SET @cmd = 'USE [' + @DBName + ']
    INSERT INTO ##FileStats
    SELECT @@ServerName AS ServerName, DB_NAME() AS DbName, 
    CASE WHEN type = 0 THEN ''DATA'' ELSE ''LOG'' END AS FileType,
    name AS FileName, 
     size/128.0 AS CurrentSizeMB,
 --ROUND(CAST(((size*8) / 1024.0 / 1024.0) AS FLOAT),2) AS CurrentSizeMB,  
    (size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0) AS FreeSpaceMB,
    100*(1 - ((CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0)/(size/128.0))) AS PercentMBFree,
    physical_name AS FileLocation
    FROM sys.database_files
 '
     
    IF @Drive IS NOT NULL
    SET @cmd = @cmd + ' WHERE physical_name LIKE ''' + @Drive + ':\%'''
 
    EXEC sp_executesql @cmd
     
    DELETE FROM #DBName WHERE Name = @DBName
     
END
 
SELECT 
 ServerName,
 DBName,
 ROUND(CAST((CurrentSizeMB  / 1024.0) AS FLOAT),2) AS "FileSize_GB",
 ROUND(CAST((FreeSpaceMB*100/CurrentSizeMB) AS FLOAT),2) AS "PercentFree", 
 ROUND(CAST((FreeSpaceMB  / 1024.0) AS FLOAT),2) AS "FreeSpace_GB",
 FileName,
 FileLocation
FROM ##FileStats
--WHERE FileLocation LIKE 'D:%'
ORDER BY CurrentSizeMB DESC
DROP TABLE #DBName
DROP TABLE ##FileStats

SQL Server: Size Table and Total Index size ocupped

Complementando um post anterior Consulta Index ( Size, Unused, Clustered, NoClustered)(https://luizflsilva.blogspot.com/2019/06/consulta-index-size-unused-clustered.html)

A consulta abaixo possibilita validar o tamanho de cada tabela em KB, MB e GB, e também um sub total dessa tabela incluindo o espaço ocupado pelos indices existentes na tabela.

IF OBJECT_ID('tempdb..#SpaceUsed') IS NOT NULL DROP TABLE #SpaceUsed

CREATE TABLE #SpaceUsed 
(
 TableName sysname ,
 [Rows] int ,
 [Reserved] varchar(20),
 [Data] varchar(20),
 [Index_Size] varchar(20),
 [Unused] varchar(20),
 [Reserved_KB] bigint,
 [Data_KB] bigint,
 [Index_Size_KB] bigint,
 [Unused_KB] bigint
)

DECLARE @CMD NVARCHAR(MAX) =''
SELECT @CMD +='EXEC sp_spaceused ' +  ''''+QUOTENAME(TABLE_SCHEMA)+'.'+ QUOTENAME(TABLE_NAME)+''''+';'+CHAR(10)

FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE'
PRINT @CMD

INSERT INTO #SpaceUsed (TableName ,[Rows] , [Reserved], [Data] , [Index_Size] , [Unused] )
EXEC sp_executesql @CMD



UPDATE #SpaceUsed 
SET [Reserved_KB] = CONVERT(BIGINT,RTRIM(LTRIM(REPLACE([Reserved] , ' KB', '')))),
    [Data_KB] = CONVERT(BIGINT,RTRIM(LTRIM(REPLACE([Data] , ' KB', '')))),
    [Index_Size_KB]= CONVERT(BIGINT,RTRIM(LTRIM(REPLACE([Index_Size] , ' KB', '')))),
    [Unused_KB]= CONVERT(BIGINT,RTRIM(LTRIM(REPLACE([Unused] , ' KB', ''))))


SELECT 
 TableName, 
 [Rows], 
 Reserved_KB , 
 Data_KB , 
 Index_Size_KB, 
 Unused_KB,  
 ROUND(CAST(Data_KB / 1024.0 AS FLOAT),2) Data_MB,
 ROUND(CAST(Data_KB / 1024.0 / 1024.0 AS FLOAT),2) Data_GB
FROM #SpaceUsed
ORDER BY Data_KB DESC 

SELECT 
 SUM(Reserved_KB) Reserved_KB , 
 SUM(Data_KB) Data_KB,
 ROUND(CAST(SUM(Index_Size_KB / 1024.0 / 1024.0) AS FLOAT),2) Index_Size_GB, 
 SUM(Unused_KB) Unused_KB ,SUM(Data_KB / 1024.0) Data_MB , 
 ROUND(CAST(SUM(Data_KB / 1024.0 / 1024.0) AS FLOAT),2) Data_GB
FROM #SpaceUsed

DROP TABLE #SpaceUsed

Validar Memoria, CPU, Consumo Memoria, Consumo de CPU, Tipo Servidor, Batch Requests/sec e Page Life Expectancy

Pessoal, abaixo um script muito interessante para uma validação inicial de de um Servidor SQL Server:
Retorno da consulta:
-- Total de Memoria no Servidor
-- % Uso de Memoria
-- % Qtde de CPU
-- VM ou servidor Fisico
-- Transações por segundo no SQL Server (bom para monitorar como está a utilização do banco de dados);
-- Quantidade de conexões no banco de dados (bom para monitorar quando temos um maior número de usuários conectados)
-- Consumo de CPU do servidor (bom para acompanhar o consumo de CPU);
-- [Page Life Expectancy] Expectativa de vida em segundos de uma página na memória do SQL Server (bom para monitorar se você está mantendo dados em cache por muito tempo, evitando acessos a disco

Parte do script veio da rotina de contatores do brother Fabrício Lima (https://www.fabriciolima.net/blog/2016/06/22/queries-do-dia-a-dia-monitorando-contadores-de-forma-simples-via-dmv/)

USE MASTER
GO

DECLARE 
 @BATCHREQUESTS INT,
 @USER_CONNECTION INT,
 @CPU INT, 
 @PLE INT,
 @V_CPU_COUNT INT,
 @V_BATCH_REQUESTS INT

DECLARE 
 @REQUESTSPERSECONDSAMPLE1 BIGINT,
 @REQUESTSPERSECONDSAMPLE2 BIGINT

DECLARE 
 @V_SERVER_MEMORY_GB VARCHAR(255),
 @V_PER_MEMORY_USED VARCHAR(255),
 @V_VIRTUAL_MACHINE_TYPE_DESC VARCHAR(255)


SELECT 
 @REQUESTSPERSECONDSAMPLE1 = CNTR_VALUE 
FROM SYS.DM_OS_PERFORMANCE_COUNTERS WHERE COUNTER_NAME = 'BATCH REQUESTS/SEC'

WAITFOR DELAY '00:00:05'

SELECT 
 @REQUESTSPERSECONDSAMPLE2 = CNTR_VALUE 
FROM SYS.DM_OS_PERFORMANCE_COUNTERS WHERE COUNTER_NAME = 'BATCH REQUESTS/SEC'

SELECT @BATCHREQUESTS = (@REQUESTSPERSECONDSAMPLE2 - @REQUESTSPERSECONDSAMPLE1)/5

SELECT 
 @USER_CONNECTION = CNTR_VALUE
FROM SYS.DM_OS_PERFORMANCE_COUNTERS
WHERE COUNTER_NAME = 'USER CONNECTIONS'
        
SELECT  TOP(1) @CPU  = (SQLProcessUtilization + (100 - SystemIdle - SQLProcessUtilization ) )
FROM ( 
  SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, 
   record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') 
   AS [SystemIdle], 
   record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 
   'int') 
   AS [SQLProcessUtilization], [timestamp] 
  FROM ( 
   SELECT [timestamp], CONVERT(xml, record) AS [record] 
   FROM sys.dm_os_ring_buffers 
   WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
   AND record LIKE '%<SystemHealth>%') AS x 
  ) AS y 
        
        
SELECT
 @PLE = CNTR_VALUE 
FROM SYS.DM_OS_PERFORMANCE_COUNTERS
WHERE COUNTER_NAME = 'PAGE LIFE EXPECTANCY'
 AND OBJECT_NAME LIKE '%Buffer Manager%'


SELECT  
 @V_SERVER_MEMORY_GB = round(total_physical_memory_kb/1048576.0,2),-- AS "SERVER_MEMORY_GB"
 @V_PER_MEMORY_USED = round(100 - (100 * CAST(available_physical_memory_kb AS DECIMAL(18,3))/CAST(total_physical_memory_kb AS DECIMAL(18,3))) ,2),-- AS "PER_MEMORY_USED"
 @V_CPU_COUNT = CPU_COUNT,
 @V_VIRTUAL_MACHINE_TYPE_DESC = VIRTUAL_MACHINE_TYPE_DESC
FROM SYS.DM_OS_SYS_INFO SYSINFO
OUTER APPLY SYS.DM_OS_SYS_MEMORY 


DECLARE @V1 BIGINT, @DELAY SMALLINT = 2, @TIME DATETIME;
 
SELECT @TIME = DATEADD(SECOND, @DELAY, '00:00:00');
 
SELECT @V1 = CNTR_VALUE 
FROM MASTER.SYS.DM_OS_PERFORMANCE_COUNTERS
WHERE COUNTER_NAME = 'Batch Requests/sec';
 
WAITFOR DELAY @time;
 
 /* [VIRTUAL_MACHINE_TYPE_DESC]
 https://docs.microsoft.com/pt-br/sql/relational-databases/system-dynamic-management-views/sys-dm-os-sys-info-transact-sql?view=sql-server-2017
 NONE = SQL Server não está em execução em uma máquina virtual.
 HYPERVISOR = o SQL Server está em execução dentro de um hipervisor, o que implica uma virtualização assistida por hardware. Quando a função Hyper-V é instalada, o hipervisor hospeda o sistema operacional, para que uma instância em execução no sistema operacional host esteja em execução no hipervisor.
 OTHER = o SQL Server está em execução dentro de uma máquina virtual que não usa assistente de hardware, como o Microsoft Virtual PC.
 */

SELECT 
 @V_BATCH_REQUESTS = (CNTR_VALUE - @V1)/@DELAY   -- Transações por segundo no SQL Server (bom para monitorar como está a utilização do banco de dados);
FROM MASTER.SYS.DM_OS_PERFORMANCE_COUNTERS
WHERE COUNTER_NAME='Batch Requests/sec';


SELECT 
 @V_SERVER_MEMORY_GB    AS [SERVER_MEMORY_GB]   -- Total de Memoria no Servidor
 ,@V_PER_MEMORY_USED    AS [PER_MEMORY_USED]   -- % Uso de Memoria
 ,@V_CPU_COUNT     AS [CPU_COUNT]     -- % Qtde de CPU
 ,@V_VIRTUAL_MACHINE_TYPE_DESC AS [VIRTUAL_MACHINE_TYPE_DESC] -- VM ou servidor Fisico
 ,@V_BATCH_REQUESTS    AS [BATCH REQUESTS/SEC]   -- Transações por segundo no SQL Server (bom para monitorar como está a utilização do banco de dados);
 ,@USER_CONNECTION    AS QT_USER_CONNECTION   -- Quantidade de conexões no banco de dados (bom para monitorar quando temos um maior número de usuários conectados)
 ,@CPU       AS [%_CPU]      -- Consumo de CPU do servidor (bom para acompanhar o consumo de CPU);
 ,@PLE       AS [Page Life Expectancy]  -- [Page Life Expectancy] Expectativa de vida em segundos de uma página na memória do SQL Server (bom para monitorar se você está mantendo dados em cache por muito tempo, evitando acessos a disco