[SQL Server] Insert와 버퍼캐시

테스트 쿼리

SELECT
objects.name AS object_name,
objects.type_desc AS object_type_description,
COUNT(*) AS buffer_cache_pages,
COUNT(*) * 8 / 1024  AS buffer_cache_used_MB
FROM sys.dm_os_buffer_descriptors
INNER JOIN sys.allocation_units
ON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_id
INNER JOIN sys.partitions
ON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))
OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))
INNER JOIN sys.objects
ON partitions.object_id = objects.object_id
WHERE allocation_units.type IN (1,2,3)
and objects.name = 'minjae_test'
AND objects.is_ms_shipped = 0
AND dm_os_buffer_descriptors.database_id = DB_ID()
GROUP BY objects.name,
objects.type_desc
ORDER BY COUNT(*) DESC;

declare @num1 int, @num2 int, @num3 int
set @num1 = 1
set @num2 = @num1 * 10
set @num3 = @num1 * 100
while @num1 < 5000
begin 
	insert into minjae_test values(@num1,@num2,@num3)

	set @num1 = @num1+1
	set @num2 = @num1 * 10
	set @num3 = @num1 * 100
end
--1회 입력당 12바이트 증가
--5000번 입력시 60,000 바이트 -> 58KB -> 필팩터 80% 약 9개 페이지 증가 예상
--단순 입력
SELECT 
objects.name AS object_name,
objects.type_desc AS object_type_description,
COUNT(*) AS buffer_cache_pages,
COUNT(*) * 8 / 1024  AS buffer_cache_used_MB
FROM sys.dm_os_buffer_descriptors
INNER JOIN sys.allocation_units
ON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_id
INNER JOIN sys.partitions
ON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))
OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))
INNER JOIN sys.objects
ON partitions.object_id = objects.object_id
WHERE allocation_units.type IN (1,2,3)
and objects.name = 'minjae_test'
AND objects.is_ms_shipped = 0
AND dm_os_buffer_descriptors.database_id = DB_ID()
GROUP BY objects.name,
objects.type_desc
ORDER BY COUNT(*) DESC;

 

 

단순 입력도 버퍼캐시, 트랜잭션 로그를 사용.

예상치 10개 페이지에서  13~14 페이지씩 증가