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/