성능을 위해 Microsoft SQL Server를 조정하는 방법

게시 됨: 2022-03-11

사용자를 유지하려면 모든 응용 프로그램이나 웹 사이트가 빠르게 실행되어야 합니다. 미션 크리티컬 환경의 경우 정보를 얻는 데 몇 밀리초가 지연되면 큰 문제가 발생할 수 있습니다. 데이터베이스 크기가 나날이 증가함에 따라 우리는 가능한 한 빨리 데이터를 가져와야 하고 가능한 한 빨리 데이터를 데이터베이스에 다시 써야 합니다. 모든 작업이 원활하게 실행되도록 하려면 성능을 위해 데이터베이스 서버를 조정해야 합니다.

이 기사에서는 시장에 나와 있는 최고의 데이터베이스 서버 중 하나인 Microsoft SQL Server(줄여서 SQL Server)에서 기본적인 성능 조정을 위한 단계별 절차를 설명합니다.

#1 범인 찾기

다른 소프트웨어와 마찬가지로 SQL Server는 복잡한 컴퓨터 프로그램이라는 것을 이해해야 합니다. 문제가 있는 경우 예상대로 실행되지 않는 이유를 찾아야 합니다.

SQL 서버 성능

SQL Server에서 가능한 한 빠르고 정확하게 데이터를 가져오고 밀어넣어야 합니다. 문제가 있는 경우 몇 가지 기본적인 이유와 먼저 확인해야 할 두 가지 사항은 다음과 같습니다.

  • SQL Server 요구 사항이 특정하기 때문에 수정이 필요할 수 있는 하드웨어 및 설치 설정
  • SQL Server가 구현할 올바른 T-SQL 코드를 제공한 경우

SQL Server는 독점 소프트웨어이지만 Microsoft는 SQL Server를 이해하고 효율적으로 사용할 수 있는 많은 방법을 제공했습니다.

하드웨어가 정상이고 설치가 제대로 완료되었지만 SQL Server가 여전히 느리게 실행되는 경우 먼저 소프트웨어 관련 오류가 있는지 확인해야 합니다. 무슨 일이 일어나고 있는지 확인하려면 서로 다른 스레드가 수행하는 방식을 관찰해야 합니다. 이것은 다른 스레드의 대기 통계를 계산하여 달성됩니다. SQL 서버는 모든 사용자 요청에 대해 스레드를 사용하며 스레드는 SQL Server라는 복잡한 프로그램 내부의 또 다른 프로그램일 뿐입니다. 이 스레드는 SQL Server가 설치된 운영 체제 스레드가 아닙니다. SQL Server의 의사 운영 체제인 SQLOS 스레드와 관련이 있습니다.

대기 통계는 현재 상태에 대한 추가 정보를 제공하는 sys.dm_os_wait_stats DMV(동적 관리 보기)를 사용하여 계산할 수 있습니다. 온라인에 이 보기를 쿼리하는 스크립트가 많이 있지만 내가 가장 좋아하는 것은 Paul Randal의 스크립트입니다. 이 스크립트는 이해하기 쉽고 대기 통계를 관찰하기 위한 모든 중요한 매개변수가 있기 때문입니다.

 WITH [Waits] AS (SELECT [wait_type], [wait_time_ms] / 1000.0 AS [WaitS], ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS], [signal_wait_time_ms] / 1000.0 AS [SignalS], [waiting_tasks_count] AS [WaitCount], 100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage], ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum] FROM sys.dm_os_wait_stats WHERE [wait_type] NOT IN ( N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT') AND [waiting_tasks_count] > 0 ) SELECT MAX ([W1].[wait_type]) AS [WaitType], CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S], CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S], CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S], MAX ([W1].[WaitCount]) AS [WaitCount], CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage], CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S], CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S], CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S] FROM [Waits] AS [W1] INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum] GROUP BY [W1].[RowNum] HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- percentage threshold GO

이 스크립트를 실행할 때 결과의 맨 위 행이 가장 먼저 설정되고 최대 대기 유형을 나타내기 때문에 집중해야 합니다.

올바른 결정을 내리려면 대기 유형을 이해해야 합니다. 다양한 대기 유형에 대해 알아보려면 우수한 Microsoft 설명서를 참조하십시오.

PAGEIOLATCH_XX 가 너무 많은 예를 들어 보겠습니다. 이것은 스레드가 디스크에서 버퍼로 데이터 페이지 읽기를 기다리고 있음을 의미합니다. 버퍼는 메모리 블록일 뿐입니다. 무슨 일이 일어나고 있는지 확실히 이해해야 합니다. 이것은 반드시 I/O 하위 시스템이 불량하거나 메모리가 충분하지 않다는 것을 의미하지 않으며 I/O 하위 시스템과 메모리를 늘리면 문제가 해결되지만 일시적일 뿐입니다. 영구적인 솔루션을 찾으려면 디스크에서 왜 그렇게 많은 데이터를 읽고 있는지 확인해야 합니다. 어떤 유형의 SQL 명령이 이 문제를 일으키는가? where 절과 같은 필터를 사용하여 더 적은 데이터를 읽는 대신 너무 많은 데이터를 읽고 있습니까? 테이블 스캔이나 인덱스 스캔으로 인해 너무 많은 데이터 읽기가 발생합니까? 기존 인덱스를 구현하거나 수정하여 인덱스 검색으로 변환할 수 있습니까? SQL Optimizer(SQL 서버 프로그램 내부의 다른 프로그램)에서 오해하는 SQL 쿼리를 작성하고 있습니까?

우리는 다른 각도에서 생각하고 다른 테스트 케이스를 사용하여 솔루션을 제시해야 합니다. 위의 각 대기 유형에는 다른 솔루션이 필요합니다. 데이터베이스 관리자는 조치를 취하기 전에 철저히 조사해야 합니다. 그러나 대부분의 경우 문제가 있는 T-SQL 쿼리를 찾아 조정하면 문제의 60~70%가 해결됩니다.

#2 문제가 있는 쿼리 찾기

위에서 언급했듯이 우리가 할 수 있는 첫 번째 일은 문제가 있는 쿼리를 검색하는 것입니다. 다음 T-SQL 코드는 성능이 가장 낮은 쿼리 20개를 찾습니다.

 SELECT TOP 20 total_worker_time/execution_count AS Avg_CPU_Time ,Execution_count ,total_elapsed_time/execution_count as AVG_Run_Time ,total_elapsed_time ,(SELECT SUBSTRING(text,statement_start_offset/2+1,statement_end_offset ) FROM sys.dm_exec_sql_text(sql_handle) ) AS Query_Text FROM sys.dm_exec_query_stats ORDER BY Avg_CPU_Time DESC

결과에 주의해야 합니다. 쿼리는 최대 평균 실행 시간을 가질 수 있지만 한 번만 실행하면 평균 실행 시간이 중간이고 하루에 여러 번 실행되는 쿼리에 비해 서버에 대한 전체 영향이 낮습니다.

#3 미세 조정 쿼리

T-SQL 쿼리의 미세 조정은 중요한 개념입니다. 기본적으로 이해해야 할 것은 T-SQL 쿼리를 얼마나 잘 작성하고 인덱스를 구현하여 SQL 옵티마이저가 원하는 작업을 수행할 수 있는 최적화된 계획을 찾을 수 있는지입니다. SQL Server의 새 릴리스마다 최적화되지 않은 SQL 쿼리를 작성하는 실수를 커버하고 이전 최적화 프로그램과 관련된 모든 버그를 수정하는 보다 정교한 최적화 프로그램이 제공됩니다. 그러나 옵티마이저가 아무리 지능적일지라도 우리가 원하는 것을 말할 수 없다면(적절한 T-SQL 쿼리를 작성하여) SQL 옵티마이저는 작업을 수행할 수 없습니다.

SQL Server는 고급 검색 및 정렬 알고리즘을 사용합니다. 검색 및 정렬 알고리즘에 능숙하다면 대부분의 경우 SQL Server가 특정 조치를 취하는 이유를 추측할 수 있습니다. 그러한 알고리즘을 더 많이 배우고 이해하기 위한 최고의 책은 Donald Knuth 의 Art of Computer Programming 입니다.

미세 조정이 필요한 쿼리를 검사할 때 해당 쿼리의 실행 계획을 사용하여 SQL 서버가 쿼리를 해석하는 방법을 찾을 수 있어야 합니다.

여기에서 실행 계획의 모든 측면을 다룰 수는 없지만 기본적인 수준에서는 고려해야 할 사항을 설명할 수 있습니다.

  • 먼저 쿼리 비용의 대부분을 차지하는 연산자를 찾아야 합니다.
  • 운영자가 비용을 많이 들이면 그 이유를 알아야 합니다. 대부분의 경우 스캔은 탐색보다 비용이 더 많이 듭니다. 인덱스 검색 대신 특정 스캔(테이블 스캔 또는 인덱스 스캔)이 발생하는 이유를 조사해야 합니다. 테이블 열에 적절한 인덱스를 구현하여 이 문제를 해결할 수 있지만 복잡한 프로그램과 마찬가지로 고정된 솔루션은 없습니다. 예를 들어 테이블이 작으면 검색이 검색보다 빠릅니다.
  • SQL Server 실행 계획의 다양한 작업과 결정을 나타내는 약 78개의 연산자가 있습니다. Microsoft 설명서를 참조하여 심층적으로 연구하여 더 잘 이해하고 적절한 조치를 취해야 합니다.
관련: SQL 인덱스 설명, Pt. 1

#4 실행 계획 재사용

테이블에 적절한 인덱스를 구현하고 좋은 T-SQL 코드를 작성하더라도 실행 계획을 재사용하지 않으면 성능 문제가 발생합니다. 쿼리를 미세 조정한 후 필요할 때 실행 계획을 재사용할 수 있는지 확인해야 합니다. 대부분의 CPU 시간은 계획을 재사용할 경우 제거할 수 있는 실행 계획을 계산하는 데 사용됩니다.

아래 쿼리를 사용하여 실행 계획이 재사용된 횟수를 확인할 수 있습니다. 여기서 usecounts 는 계획이 재사용된 횟수를 나타냅니다.

 SELECT [ecp].[refcounts] , [ecp].[usecounts] , [ecp].[objtype] , DB_NAME([est].[dbid]) AS [db_name] , [est].[objectid] , [est].[text] as [query_ext] , [eqp].[query_plan] FROM sys.dm_exec_cached_plans ecp CROSS APPLY sys.dm_exec_sql_text ( ecp.plan_handle ) est CROSS APPLY sys.dm_exec_query_plan ( ecp.plan_handle ) eqp

실행 계획을 재사용하는 가장 좋은 방법은 매개변수화된 저장 프로시저를 구현하는 것입니다. 저장 프로시저를 구현할 위치에 있지 않은 경우 sp_executesql 을 사용할 수 있습니다. SQL 문에 대한 유일한 변경 사항이 매개 변수 값일 때 T-SQL 문을 실행하는 대신 사용할 수 있습니다. SQL Server는 첫 번째 실행에서 생성한 실행 계획을 재사용할 가능성이 높습니다.

다시 말하지만, 복잡한 컴퓨터 프로그램과 마찬가지로 고정된 솔루션은 없습니다. 때로는 계획을 다시 컴파일하는 것이 좋습니다.

다음 두 가지 예제 쿼리를 살펴보겠습니다.

  • select name from table where name = 'sri';
  • select name from table where name = 'pal';

name 열에 클러스터되지 않은 인덱스가 있고 테이블의 절반에 값 sri 가 있고 name 열에 pal 이 있는 행이 거의 없다고 가정해 보겠습니다. 첫 번째 쿼리의 경우 테이블의 절반이 동일한 값을 가지므로 SQL Server는 테이블 스캔을 사용합니다. 그러나 두 번째 쿼리의 경우 pal 값이 있는 행이 거의 없으므로 인덱스 스캔을 사용하는 것이 좋습니다.

쿼리가 비슷하더라도 동일한 실행 계획은 좋은 솔루션이 아닐 수 있습니다. 대부분 다른 경우이므로 결정하기 전에 모든 것을 신중하게 분석해야 합니다. 실행 계획을 재사용하지 않으려면 저장 프로시저에서 항상 "재컴파일" 옵션을 사용할 수 있습니다.

저장 프로시저나 sp_executesql 을 사용한 후에도 실행 계획이 재사용되지 않는 경우가 있음을 명심하십시오. 그들은:

  • 쿼리에서 사용하는 인덱스가 변경되거나 삭제된 경우
  • 쿼리가 사용하는 테이블의 통계, 구조 또는 스키마가 변경된 경우
  • "재컴파일" 옵션을 사용할 때
  • 많은 수의 삽입, 업데이트 또는 삭제가 있는 경우
  • 단일 쿼리 내에서 DDL과 DML을 혼합할 때

#5 불필요한 인덱스 제거

쿼리를 미세 조정한 후 인덱스가 어떻게 사용되는지 확인해야 합니다. 인덱스 유지 관리에는 많은 CPU와 I/O가 필요합니다. 데이터베이스에 데이터를 삽입할 때마다 SQL Server는 인덱스도 업데이트해야 하므로 사용하지 않는 경우 제거하는 것이 좋습니다.

SQL 서버 성능

SQL 서버는 인덱스 통계를 찾기 위해 dm_db_index_usage_stats DMV를 제공합니다. 아래의 T-SQL 코드를 실행하면 다양한 인덱스에 대한 사용 통계를 얻을 수 있습니다. 전혀 사용되지 않거나 거의 사용되지 않는 인덱스를 찾으면 성능 향상을 위해 해당 인덱스를 삭제할 수 있습니다.

 SELECT OBJECT_NAME(IUS.[OBJECT_ID]) AS [OBJECT NAME], DB_NAME(IUS.database_id) AS [DATABASE NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM SYS.DM_DB_INDEX_USAGE_STATS AS IUS INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = IUS.[OBJECT_ID] AND I.INDEX_ID = IUS.INDEX_ID

#6 SQL Server 설치 및 데이터베이스 설정

데이터베이스를 설정할 때 데이터와 로그 파일을 별도로 보관해야 합니다. 그 주된 이유는 데이터 파일의 쓰기와 접근은 순차적이지 않고 로그 파일의 쓰기와 접근은 순차적이기 때문입니다. 같은 드라이브에 넣으면 최적화된 방식으로 사용할 수 없습니다.

SAN(Storage Area Network)을 구입할 때 공급업체에서 설정 방법에 대한 권장 사항을 제공할 수 있지만 이 정보가 항상 도움이 되는 것은 아닙니다. 데이터 및 로그 파일을 개별적으로 최적화된 방식으로 보관하는 방법에 대해 하드웨어 및 네트워킹 담당자와 자세한 논의가 필요합니다.

#7 SQL 서버를 오버로드하지 마십시오

모든 데이터베이스 관리자의 기본 작업은 프로덕션 서버가 원활하게 실행되고 가능한 한 고객에게 서비스를 제공하는지 확인하는 것입니다. 이렇게 하려면 다음 환경에 대해 별도의 데이터베이스(가능한 경우 별도의 시스템에서)를 유지해야 합니다.

  • 생산
  • 개발
  • 테스트
  • 분석적

프로덕션 데이터베이스의 경우 전체 복구 모드가 있는 데이터베이스가 필요하고 다른 데이터베이스의 경우 단순 복구 모드로 충분합니다.

프로덕션 데이터베이스에서 테스트하면 트랜잭션 로그, 인덱스, CPU 및 I/O에 많은 부하가 가해집니다. 이것이 우리가 생산, 개발, 테스트 및 분석을 위해 별도의 데이터베이스를 사용해야 하는 이유입니다. 가능하면 각 데이터베이스에 대해 별도의 시스템을 사용하십시오. CPU 및 I/O의 로드가 줄어들기 때문입니다.

#8 트랜잭션 로그, tempdb 및 메모리

로그 파일에 대한 자동 증가 작업은 시간이 많이 걸리고 다른 작업이 완료될 때까지 대기하도록 할 수 있으므로 로그 파일에는 일반 작업을 위한 충분한 여유 공간이 필요합니다. 각 데이터베이스의 로그 파일 크기와 사용량을 확인하려면 DBCC SQLPERF(logspace) 를 사용할 수 있습니다.

tempdb를 설정하는 가장 좋은 방법은 별도의 디스크에 저장하는 것입니다. 자동 증가 상황에 도달하면 성능이 저하되기 때문에 초기 크기를 감당할 수 있는 한 크게 유지해야 합니다.

앞서 언급했듯이 SQL 서버가 별도의 시스템에서 실행되는지 확인해야 합니다. 가급적이면 다른 애플리케이션이 없는 시스템에서 실행하는 것이 좋습니다. 운영 체제를 위한 일부 메모리와 클러스터의 일부인 경우 추가 메모리를 유지해야 하므로 대부분의 경우 약 2GB가 필요합니다.

미션 크리티컬 환경의 경우 정보를 얻는 데 밀리초가 지연되면 거래 차단기가 될 수 있습니다.
트위터

결론:

여기에 설명된 절차와 제안은 기본 성능 조정만을 위한 것입니다. 이러한 단계를 따르면 평균적으로 성능이 약 40~50% 향상될 수 있습니다. 고급 SQL Server 성능 조정을 수행하려면 여기에서 다루는 각 단계를 훨씬 더 깊이 파고들 필요가 있습니다.

Toptal 엔지니어링 블로그에 대한 추가 정보:

  • SQL 인덱스 및 파티션으로 병목 현상 해결
  • Oracle에서 SQL Server로 및 SQL Server에서 Oracle로 마이그레이션 가이드