SET STATISTICS IO OFF

use TreinamentoDBA

-- Cria e popula a tabela com vários registros
create table dbo.Teste_Memoria_SQL (
	cod int identity(1,1),
	Dt_Log datetime,
	Descrição varchar(5000)
)

-- Popula a tabela (pode demorar alguns minutos)
insert into dbo.Teste_Memoria_SQL
select getdate(), replicate('A',5000)
go 1000000


-- Query para o SQL Server aumentar o uso da memória
SELECT TOP 1 *
FROM TreinamentoDBA..Teste_Memoria_SQL
WHERE Descrição LIKE 'Fabricio%'


DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

---
-- Utilização de memória por base de dados
SELECT  CASE database_id
          WHEN 32767 THEN 'ResourceDb'
          ELSE DB_NAME(database_id)
        END AS database_name ,
        COUNT(*) AS cached_pages_count ,
        COUNT(*) * .0078125 AS cached_megabytes /* Each page is 8kb, which is .0078125 of an MB */
FROM    sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id) ,
        database_id
ORDER BY cached_pages_count DESC ;

-- Utilização de memória por tabela
SELECT TOP 5 COUNT(*) AS cached_pages_count,COUNT(*)/128.0000 MB,
name AS BaseTableName, IndexName,
IndexTypeDesc
FROM sys.dm_os_buffer_descriptors AS bd
	INNER JOIN	(
				SELECT s_obj.name, s_obj.index_id,
				s_obj.allocation_unit_id, s_obj.OBJECT_ID,
				i.name IndexName, i.type_desc IndexTypeDesc
				FROM
					(SELECT OBJECT_NAME(OBJECT_ID) AS name,	index_id ,
						allocation_unit_id, OBJECT_ID
					 FROM sys.allocation_units AS au
					    INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id	AND (au.TYPE = 1 OR au.TYPE = 3)
					 UNION ALL
					 SELECT OBJECT_NAME(OBJECT_ID) AS name,
						index_id, allocation_unit_id, OBJECT_ID
					 FROM sys.allocation_units AS au
						INNER JOIN sys.partitions AS p ON au.container_id = p.partition_id	AND au.TYPE = 2
					) AS s_obj
					LEFT JOIN sys.indexes i ON i.index_id = s_obj.index_id
			    	AND i.OBJECT_ID = s_obj.OBJECT_ID 
			    ) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID()
GROUP BY name, index_id, IndexName, IndexTypeDesc
ORDER BY cached_pages_count DESC;

SELECT 8000088   /1024 Tamanho_MB

sp_spaceused Teste_Memoria_SQL

SELECT 8000088   /1024 Tamanho_MB