테이블을 캐시에 보관하려면 얼마나 많은 메모리가 필요합니까?(SQL Server 2008)


5

나는 하나의 큰 테이블 (8GB, 40M 행)을 주로 사용하는 길고 복잡한 쿼리를 실행합니다.AFAIK, 모든/대부분의 행은 각 쿼리에서 사용됩니다.첫 번째 쿼리와 이후의 모든 쿼리에 대해 활동 모니터링에 많은 IO가 표시됩니다.서버가 현재 6.5GB의 메모리를 사용 중이므로 업그레이드하고 싶습니다.문제는 이러한 모든 디스크 읽기를 피하기 위해 얼마나 많은 메모리가 필요합니까?테이블 크기 이상의 야구장에 있습니까?

이것은 SET STATISTICS IO 출력입니다.BigTable은 제가 묻는 것입니다. SmallTable은 BigTable과 1 대 다수의 관계를 가지고 있습니다.#entrance는 쿼리의 출력 (수백 개의 출력 행)을 보유합니다.

Table 'SmallTable'. Scan count 249005, logical reads 2829948, physical reads 2605, read-ahead reads 10395, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'BigTable'. Scan count 194004, logical reads 13482115, physical reads 33841, read-ahead reads 1181136, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#entrance__000000000023'. Scan count 0, logical reads 1568, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
+2

이 서버/데이터베이스는 8GB의 데이터 1 테이블을 통해 쿼리를 실행하는 것 외에는 다른 작업을 수행합니까?나는 또한이 질문의 1 개의 테이블 그러나 복잡한 질문 양상에 나의 머리를 경미하게 긁고있다.이 복잡성의 본질에 대한 DDL 또는 약간의 통찰력의 기회? 14 sep. 112011-09-14 22:20:40

  0

그냥 기본 확인, 어떤 SQL Server 버전을 사용하고 있습니까?그것의 기업이 아니라면 당신은 현재의 모든 메모리를 SQL 서버용으로 사용할 수 없을 것입니다. 16 sep. 112011-09-16 15:30:00

  0

결국 서버의 RAM을 업그레이드하고 테스트를 수행했습니다.아래 답변을 결과로 게시했습니다. 09 feb. 132013-02-09 08:50:11

3

검색어의 특성은 무엇입니까?선택 만?또는 DUI (DELETE, UPDATE, INSERT)의 혼합?복구 모델은 무엇입니까?기본 트랜잭션 격리 수준을 사용하고 있습니까?

모든 페이지를 메모리에 저장하려면 더 많은 메모리가 필요합니다 (단, 이미 알고 있음).'얼마나'인지 알고 싶지만 쿼리의 특성에 따라 전체 테이블을 보유 할만큼 충분한 메모리가 있어도 I/O 문제를 볼 수 있습니다.

위의 모든 것을 고려해보십시오 ... 예, 디스크의 크기를 메모리의 양만큼 줄이십시오.나중에 디스크 I/O가있는 것을 발견하면 놀라지 마십시오.

  0

나는 서버 RAM을 업그레이드하고 "디스크의 크기를 디스크의 양만큼 크게"정확하게 수정했습니다.내 대답은 아래를 참조하십시오. 08 feb. 132013-02-08 11:59:48


8

이것은 두 부분으로 된 대답입니다.

첫 번째 부분은 (서버에 들어갈 수있는 한) 여유가있는만큼의 RAM을 구입하는 것입니다.

두 번째 부분은 항상 데이터에 대한 읽기가 표시된다는 것입니다.속임수는 읽기가 물리적인지 논리인지 확인하는 것입니다.물리적 읽기가 디스크로 내려갑니다.논리 읽기는 메모리의 버퍼 풀에서 읽습니다.SQL 스크립트의 맨 위에 "SET STATISTICS IO ON"을 입력 한 다음 SSMS에서 실행하여이를 볼 수 있습니다.메시지 탭에서 실행중인 각 쿼리에 대한 구체적인 IO 정보를 얻을 수 있습니다. 특히 테이블 단위로 테이블에서 발생하는 논리적 및 물리적 작업 (사용자의 경우 읽기) 수를 확인할 수 있습니다.실제 읽기 숫자가 꽤 ​​높을 경우 (이 숫자는 기본적으로 읽혀진 블록 수이므로 8을 곱한 다음 1024로 나눠 메그 수를 얻습니다) 그런 다음 더 많은 RAM이 필요합니다.논리적 인 읽기 번호가 높으면 실제로 디스크에 가지는 않지만 쿼리에 튜닝, 생성 된 인덱스 등이 필요할 수 있습니다.

(참고 :이 대답을 위해 SELECT 문만 가정하고 INSERT/UPDATE/DELETE 작업은 좀 더 복잡하지만 동일한 기본 사항이 적용됩니다.)

  0

나는 시도 할 것이다 : SET STATISTICS IO ON 그리고보고한다.추가 정보 : 쿼리는 100 % 선택됩니다.각 쿼리에는 몇 분이 걸립니다.서버는 또한 쿼리를 수행하고 결과를 Excel로 덤프하는 작은 응용 프로그램을 실행하지만 이것이 서버 성능에 영향을 미치지는 않습니다. 15 sep. 112011-09-15 05:00:37

  0

출력을 읽는 데 도움이 필요하면 실행 계획 및 SET STATISTICS IO 출력을 자유롭게 게시하십시오. 15 sep. 112011-09-15 05:05:59


1

더 많은 RAM이 여기서 도움이 될지 확신하지 못합니다.빠른 계산은 125GB의 데이터 영역에서 쿼리가 처리되고 있음을 나타내며 8GB가 포함 된 테이블의 경우 매우 인상적입니다.

(2829948 + 13482115 + 1568) x 8/1048576 = 125GB

270MB의 물리적 읽기는 느리게 실행되는 부분을 차지하지만 메모리 내 오버 헤드만큼이나 비쌉니다.

추가 메모리를 추가하는 것보다 쿼리를 다시 작업하여 더 나은 결과를 얻을 수 있습니다.새로운 질문으로 DDL/쿼리/실행 계획을 게시하면 누군가가 분명 도움을 줄 수 있습니다.


3

나는 항상 파티에 늦었습니다.:)이 쿼리는 대단히 효과적 일 수 있습니다.그러나 8GB 테이블을 캐시하는 데 필요한 RAM의 양은 SQL Server가 서버에 대해보고하는 메모리 노드의 수에 대한 예비 질문입니다.각 메모리 노드 (물리적 NUMA 노드와 논리적으로 동일한)에는 독립적으로 관리되는 버퍼 풀 캐시가 있습니다.해당 노드의 프로세서에서 실행되는 태스크는 해당 노드의 버퍼 풀에 블록을 삽입합니다.

따라서 8GB 테이블을 전체적으로 읽는 작업이 단일 NUMA 노드에서 실행될 가능성이있는 경우 해당 노드의 버퍼 풀이 8GB 테이블을 포함 할 수 있어야합니다.따라서 NUMA 노드 당 최대 8GB의 최대 서버 메모리부터 시작하십시오.그런 다음 그것을 증가 시키십시오.쿼리 메모리는 버퍼 풀의 최대 70 %를 소비 할 수 있으며 데이터베이스 블록 내용의 경우 30 % 만 남겨 둡니다.따라서 8GB의 테이블은 서버의 단일 NUMA 노드에서 최대 서버 메모리의 30 %를 넘지 않아야합니다.26 2/3 GB (최대 서버 메모리).NUMA 노드 당.SQL Server 2012의 경우 SQL Server 2008 R2 및 이전 버전에서 다중 페이지 할당자를 사용하는 항목 (최대 서버 메모리 범위를 벗어남)은 이제 최대 서버 메모리 범위 내에 있으며이를 고려해야합니다.SQL Server 최대 서버 메모리 제한 여부와 상관없이 전체 서버 메모리에 포함되어야합니다.

오, 그래 ... temp 테이블이나 다른 tempdb 사용은 최대 서버 메모리에도 수용되어야하므로 테이블이 캐시에서 제거되지 않도록해야합니다.

Windows OS (4GB 이상)에 충분한 메모리를 추가하십시오.SQL Server 작업자 스레드, SQL Server 핵심 프로세스, SQL Agent 작업, 기타 응용 프로그램 메모리 요구 사항을 충분히 추가하십시오.

단일 NUMA 노드 서버에서 8 기가 바이트 테이블을 안정적으로 캐시하려면 최대 서버 메모리가 27GB 이상인 약 36GB의 RAM이 있어야합니다.또한 NUMA 노드가 여러 개 있으면 NUMA 노드 당 최대 2/3 GB의 최대 서버 메모리가 증가하고 추가 NUMA 노드의 추가 코어를 동반하는 추가 작업자 스레드가 증가합니다.

과도한 것처럼 보일 수도 있지만 SQL Server가 NUMA를 처리하는 방법에 일부 기인합니다.하지만 운에 좋을지도 몰라.단일 지연 기록기로 충분하면 시작 추적 플래그 8015를 사용하여 NUMA를 무시하고 단일 버퍼 풀을 관리 할 수 ​​있습니다.그렇게하면 서버에 NUMA 노드가 몇 개인지라도 8GB 테이블 메모리 상주를 유지하려면 약 36 - 40GB가 충분합니다.다음 블로그 게시물에서 조금 더 논의하십시오.추적 플래그 8015를 사용하기로 결정한 경우, 메모리 할당과 관련된 스핀 록 경쟁 및 CMEMTHREAD 대기가 확대되는 것을 방지하기 위해 추적 플래그 8048을 함께 평가하는 것이 좋습니다.http://sqlsasquatch.blogspot.com/2013/02/sql-server-cache-database-working-set.html


2

나는 서버 RAM을 8GB에서 64GB로 업그레이드하고 테스트를 수행했다.테이블 크기는 이제 16.05GB이고 인덱스 공간은 187MB입니다 (1 개의 클러스터 된 및 1 개의 고유 한 클러스터되지 않은 인덱스).테이블의 137 개 필드를 모두 평균화 한 쿼리 실행 전후의 총 서버 RAM 사용량을 확인했습니다.총 메모리는 4.22GB에서 20.4GB = 16.18GB로 테이블 크기의 100.8 % 증가했습니다.SQLRockstar는 RAM 사용량이 테이블 크기의 구장임을 예측할 때 절대적으로 옳았습니다.

더 많은 정보:

  • 클러스터 된 인덱스의 페이지 충만도는 98.1 %입니다.
  • 테이블에 대한 추가 쿼리로 인해 디스크 IO가 발생하지 않았습니다.
  • SQL Server 2008R2 개발자
  • 실행 계획은 클러스터 된 인덱스가 사용되었음을 보여줍니다.