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/
Nenhum comentário:
Postar um comentário