일반적으로 실제 테이블을 선택하는 것보다 임시 테이블을 선택하는 것이 더 빠릅니까?

tempdb에 쓰는 것이 tempdb에없는 실제 테이블보다 빠릅니다.

사실입니다. TempDb에는 두 가지 IO 향상이 있습니다.

사용자 데이터베이스의 테이블에 대한 쓰기는 커밋시 로그 레코드를 디스크로 플러시해야합니다. 또는 최소 로그 삽입 (예 : SELECT … INTO)의 경우 데이터베이스 페이지를 디스크로 플러시해야합니다. 사용자 데이터베이스에서 최소 로깅이 작동하는 방식은 데이터베이스 페이지가 디스크에 직접 기록된다는 것입니다. SELECT … INTO가 완료 될 때까지 새 페이지는 모두 실제 파일에 기록되었습니다.

TempDb 쓰기는 실패 후 절대 복구되지 않으므로 커밋시 디스크에 플러시 할 필요가 없습니다. 따라서 변경 사항은 로그 레코드를 생성하지만 로그 버퍼는 모든 커밋이 아닌 전체가 꽉 찼을 때만 디스크로 플러시됩니다.

그리고 SQL Server 2014부터 TempDb의 최소 로그 삽입도 항상 디스크에 기록되는 것은 아닙니다. 작은 파일을로드하면 수명이 짧은 임시 테이블은 디스크에 전혀 기록되지 않을 수 있습니다. 로그에는 테이블에 대한 페이지 할당 및 메타 데이터 항목에 대한 몇 가지 레코드가 있지만 그게 전부입니다.

EG 실행은 다음과 같습니다. tempdb의 배치, 전체 복구 데이터베이스 및 간단한 복구 데이터베이스를 사용하여 차이점을 확인합니다.

drop table if exists foo go declare @data bigint declare @log bigint select @log = sum(case when type_desc = "LOG" then num_of_bytes_written end) ,@data = sum(case when type_desc = "ROWS" then num_of_bytes_written end) from sys.database_files f cross apply sys.dm_io_virtual_file_stats(db_id(),f.file_id) fs select * into foo from sys.objects select -@log + sum(case when type_desc = "LOG" then num_of_bytes_written end) log_bytes ,-@data + sum(case when type_desc = "ROWS" then num_of_bytes_written end) data_bytes , (select recovery_model_desc from sys.databases where database_id = db_id()) recovery_model from sys.database_files f cross apply sys.dm_io_virtual_file_stats(db_id(),f.file_id) fs 

그러면 다음과 같은 내용이 표시됩니다.

단순 복구 :

log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 24576 16384 SIMPLE 

전체 복구 :

log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 36864 0 FULL 

tempdb :

log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 0 0 SIMPLE 

때때로 tempdb의 경우 “로그 버퍼가 플러시 된 것을 볼 수 있습니다.

log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 61440 0 SIMPLE 

코멘트

• 초기 삽입이 더 빠르지 만 나중에 다시 물려 오는 경우가 있습니다. 이 데모는 더 이상 존재하지 않는 임시 테이블에 대해 더티 디스크 tempdb 페이지에 쓰기 작업을하는 지연 작성자가 데이터를 버퍼 캐시로 가져 오는 쿼리를 보여줍니다. youtube .com / watch? v = X60ipwYv1Ms & feature = youtu.be
• 예. ' 객체에 할당되지 않은 버퍼 풀 페이지 플러시를 제거하기위한 잠재적 인 향후 개선 사항이 있습니다. 그러나 큰 임시 테이블을로드하면 캐싱에 사용할 수있는 메모리를 줄임으로써 항상 직접 또는 간접적으로 IO를 유도합니다.

또한 IO에 따라 David Browne의 답변 에서 확장 된 것처럼 tempdb에 쓰기 작업을 수행하는 경우가 종종 있습니다. 데이터가 디스크에 스풀링 될만큼 충분히 큰 경우에도 “일반”테이블로 선택하는 것보다 훨씬 빠릅니다.

• TempDB는 다른 위치에있을 수 있습니다. 드라이브이므로 자체 IO 대역폭이 있습니다. 이는 SSD가 아닌 회전하는 드라이브에서 특히 중요합니다. 동일한 데이터베이스 (또는 동일한 드라이브의 다른 데이터베이스)에서 읽고 쓰기 작업에는 더 많은 헤드 움직임이 필요합니다. 더 많은 IO 지연 시간을 추가하고 잠재적으로 효과적인 IO 대역폭을 제한합니다. 다른 드라이브 / 어레이에있는 데이터베이스간에 데이터를 복사하는 경우 추가 지연 시간이 동일하지 않을 수 있습니다.

• TempDB는 fa에있을 수도 있습니다. 주 스토리지보다 ster 미디어. 아마도 주 저장소가 네트워크에있는 로컬 드라이브 나 주 저장소가 기존 드라이브에있는 NVMe SSD에있을 수 있습니다.

이러한 차이점은 모두 동일한 내부에서 볼 수 있습니다. 여러 파일 그룹을 사용하여 서로 다른 드라이브 / 어레이간에 데이터의 일부를 분산하는 경우 데이터베이스.

현재 사용중인 여러 데이터베이스가있는 경우에도 그 반대가 될 수 있습니다. TempDB는 공유 리소스이므로이를 호스팅하는 드라이브 / 네트워크는 개별 DB의 데이터 파일보다 더 많은 부하를받을 수 있습니다.