연습 - 애플리케이션 성능 최적화

10분

이 연습에서는 새로운 성능 시나리오를 확인하고 애플리케이션 및 쿼리를 최적화하여 문제를 해결합니다.

Azure SQL을 사용하여 애플리케이션 성능 최적화

경우에 따라 기존 애플리케이션 및 SQL 쿼리 워크로드를 Azure로 마이그레이션하여 쿼리를 최적화하고 조정할 수 있는 기회를 얻을 수 있습니다.

AdventureWorks 주문용 웹 사이트에 대해 새로운 확장을 지원하여 고객을 위한 평가 시스템을 제공하려면 많은 동시 INSERT 활동 세트를 위한 새 테이블을 추가해야 합니다. 데이터베이스 및 트랜잭션 로그를 위한 로컬 SSD 드라이브가 있는 SQL Server 2022를 사용하여 개발 컴퓨터에서 SQL 쿼리 워크로드를 테스트했습니다.

범용 계층(8개 vCore)을 사용하여 테스트를 Azure SQL Database로 이동하는 경우 INSERT 워크로드는 더 느려집니다. 새 워크로드를 지원하기 위해 서비스 목표 또는 계층을 변경해야 합니까? 아니면 애플리케이션을 확인해야 합니까?

이 연습에 대한 모든 스크립트는 복제한 GitHub 리포지토리의 04-Performance\tuning_applications 폴더 또는 다운로드한 Zip 파일에서 찾을 수 있습니다.

애플리케이션에 대한 새 테이블 만들기

개체 탐색기에서 AdventureWorks 데이터베이스를 선택합니다. AdventureWorks 데이터베이스에 테이블을 만들려면 파일>열기>파일을 사용하여 order_ating_ddl.sql 스크립트를 엽니다. 쿼리 편집기 창은 다음 텍스트와 같이 표시됩니다.

DROP TABLE IF EXISTS SalesLT.OrderRating;
GO
CREATE TABLE SalesLT.OrderRating
(OrderRatingID int identity not null,
SalesOrderID int not null,
OrderRatingDT datetime not null,
OrderRating int not null,
OrderRatingComments char(500) not null);
GO

스크립트를 실행하려면 실행을 선택합니다.

쿼리를 로드하여 쿼리 실행 모니터링

이제 DMV(동적 관리 뷰)에 대한 일부 T-SQL 쿼리를 로드하여 활성 쿼리, 대기 및 I/O의 쿼리 성능을 확인해 보겠습니다. AdventureWorks 데이터베이스의 컨텍스트에서 이러한 쿼리를 모두 로드합니다.

개체 탐색기에서 AdventureWorks 데이터베이스를 선택합니다. 활성 SQL 쿼리를 보려면 파일>열기>파일을 사용하여 sqlrequests.sql 스크립트를 엽니다. 쿼리 편집기 창은 다음 텍스트와 같이 표시됩니다.
```
SELECT er.session_id, er.status, er.command, er.wait_type, er.last_wait_type, er.wait_resource, er.wait_time
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions es
ON er.session_id = es.session_id
AND es.is_user_process = 1;
```
개체 탐색기에서 AdventureWorks 데이터베이스를 선택합니다. 상위 대기 형식을 개수별로 살펴보려면 파일>열기>파일을 사용하여 top_waits.sql 스크립트를 엽니다. 쿼리 편집기 창은 다음 텍스트와 같이 표시됩니다.
```
SELECT * FROM sys.dm_os_wait_stats
ORDER BY waiting_tasks_count DESC;
```
개체 탐색기에서 AdventureWorks 데이터베이스를 선택합니다. 트랜잭션 로그 쓰기에 대한 대기 시간을 관찰하려면 파일>열기>파일을 사용하여 tlog_io.sql 스크립트를 엽니다. 쿼리 편집기 창은 다음 텍스트와 같이 표시됩니다.
```
SELECT io_stall_write_ms/num_of_writes as avg_tlog_io_write_ms, * 
FROM sys.dm_io_virtual_file_stats
(db_id('AdventureWorks'), 2);
```

실행을 위해 워크로드 스크립트 준비

order_rated_insert_single.cmd 워크로드 스크립트를 열고 편집합니다.

-S parameter의 서버 이름을 첫 번째 연습에서 제공한 unique_id로 대체합니다.
-P parameter를 첫 번째 연습의 데이터베이스 배포에 제공한 암호로 대체합니다.
변경 내용을 파일에 저장합니다.

워크로드 실행

PowerShell 명령 프롬프트에서 이 모듈 활동에 대한 디렉터리로 변경합니다.
```
cd c:<base directory>\04-Performance\tuning_applications
```
다음 명령을 사용하여 워크로드 실행:
```
.\order_rating_insert_single.cmd
```
이 스크립트는 ostress.exe 프로그램을 사용하여 다음 T-SQL 문을 실행하는 25명의 동시 사용자를 실행합니다(스크립트 order_rating_insert_single.sql).
```
DECLARE @x int;
SET @x = 0;
WHILE (@x < 500)
BEGIN
SET @x = @x + 1;
INSERT INTO SalesLT.OrderRating
(SalesOrderID, OrderRatingDT, OrderRating, OrderRatingComments)
VALUES (@x, getdate(), 5, 'This was a great order');
END
```
이 스크립트에서 웹 사이트에서 들어오는 데이터의 실제 표현이 아니라는 것을 확인할 수 있습니다. 그러나 데이터베이스에 수집되는 많은 주문 평가를 시뮬레이션합니다.

DMV 및 워크로드 성능 확인

이제 이전에 로드한 SSMS(SQL Server Management Studio)에서 쿼리를 실행하여 성능을 관찰합니다. sqlrequests.sql, top_waits.sql 및 tlog_io.sql에 대한 쿼리를 실행합니다.

이러한 쿼리를 사용하여 다음과 같은 사실을 확인할 수 있습니다.

많은 요청은 값이 0보다 큰 WRITELOG의 wait_type을 계속 유지합니다.
WRITELOG 대기 형식은 대기 형식 중 가장 높은 수 중 하나입니다.
트랜잭션 로그(tlog_io.sql 결과 집합의 avg_tlog_io_write_ms 열)에 쓰는 평균 시간은 약 2ms입니다.

SSD 드라이브를 사용하는 SQL Server 2022 인스턴스에서 이 워크로드의 기간은 약 10~12초입니다. Gen5 v8 코어를 사용하는 Azure SQL Database의 총 기간은 약 25초입니다.

대기 시간이 더 긴 WRITELOG 대기 유형은 트랜잭션 로그에 대한 대기 시간 플러시를 나타냅니다. 쓰기당 2밀리초의 대기 시간은 거의 비슷하지만 로컬 SSD 드라이브에서는 이러한 대기 시간이 1밀리초 미만일 수 있습니다.

해결 방법 결정

이 문제는 로그 쓰기 작업에서 별로 많이 발생하지 않습니다. Azure Portal 및 sys.dm_db_resource_stats에는 20~25%보다 높은 숫자가 표시되지 않습니다(쿼리할 필요 없음). 문제는 IOPS 제한이 아닙니다. 이 이슈는 이 애플리케이션 워크로드가 트랜잭션 로그 쓰기의 대기 시간을 단축하는 데 중요하고, 범용 계층은 이러한 유형의 대기 시간 요구에 맞게 디자인되지 않았기 때문입니다. Azure SQL Database의 예상 I/O 대기 시간은 5~7ms입니다.

참고 항목

범용 Azure SQL Database 문서에서는 I/O 대기 시간을 대략적으로 5-7(쓰기) 및 5-10(읽기)으로 표시합니다. 이러한 수치만큼 대기 시간이 발생할 수 있습니다. 범용 Azure SQL Managed Instance에 대한 대기 시간은 비슷합니다. 애플리케이션이 I/O 대기 시간과 매우 중요한 경우 중요 비즈니스용 계층을 고려할 수 있습니다.

order_rated_insert_single.sql 워크로드 T-SQL 스크립트를 검사합니다. 각 INSERT는 트랜잭션 로그 플러시가 필요한 단일 트랜잭션 커밋입니다.

삽입별로 하나의 커밋이 있는 것은 효율적이지 않지만 로컬 SSD의 애플리케이션은 각 커밋이 매우 빠르기 때문에 영향을 받지 않습니다. 중요 비즈니스용 가격 책정 계층(서비스 목표 또는 SKU)은 대기 시간이 짧은 로컬 SSD 드라이브를 제공합니다. 애플리케이션 최적화가 있을 수 있으므로 워크로드는 트랜잭션 로그의 I/O 대기 시간에 민감하지 않습니다.

워크로드에 대한 T-SQL 일괄 처리를 변경하여 INSERT 반복 주위에 BEGIN TRAN/COMMIT TRAN을 래핑할 수 있습니다.

수정된 보다 효율적인 워크로드 실행

스크립트를 편집하고 실행하여 보다 효율적인 I/O 성능을 확인합니다. order_rated_insert.sql 스크립트에서 수정된 워크로드를 찾을 수 있습니다.

order_rating_insert.cmd을 편집하고 올바른 서버 이름과 암호를 사용하여 워크로드 스크립트를 준비합니다.
이전 워크로드 스크립트를 실행한 것과 비슷한 방식으로 order_rating_insert.cmd 스크립트를 사용하여 수정된 워크로드를 실행합니다.

새 결과 확인

SSMS에서 sqlrequests.sql에 대한 T-SQL 스크립트의 결과를 확인합니다. WRITELOG 대기가 짧을수록 이러한 대기에 대한 전반적인 대기 시간이 짧습니다.

이제 워크로드는 이전 실행과 비교할 때 훨씬 더 빠르게 실행됩니다. 다음은 Azure 내외에서 실행되는 SQL 쿼리에 대한 애플리케이션을 조정하는 예제입니다.

참고 항목

이 워크로드는 리디렉션 연결 형식을 사용하는 Azure SQL Database 인스턴스에 대해 훨씬 더 빠르게 실행될 수 있습니다. 이 연습에서 수행한 배포는 Azure 외부와 연결되어 있으므로 프록시 유형이 되는 기본 연결 형식을 사용합니다. 클라이언트에서 서버로의 왕복이 필요한 경우 리디렉션을 사용하면 워크로드의 속도가 크게 단축될 수 있습니다.
워크로드 기간을 확인합니다. 워크로드는 상당히 빠르게 실행되므로, 이전에 이 작업에서 사용된 쿼리의 진단 데이터를 확인하기가 어려울 수 있습니다.

“일괄 처리” 개념은 Azure SQL에 연결된 애플리케이션을 비롯한 대부분의 애플리케이션을 지원할 수 있습니다.

팁

Azure의 리소스 거버넌스는 매우 큰 트랜잭션에 영향을 미칠 수 있으며 증상은 LOG_RATE_GOVERNOR입니다. 이 예제에서는 char(500)의 null이 아닌 열이 공백을 채우고 대량 트랜잭션 로그 레코드를 발생시킵니다. 해당 열을 가변 길이 열로 설정하여 성능을 좀 더 최적화할 수도 있습니다.

다음 단원에서는 Azure SQL의 인텔리전트 성능에 대해 알아봅니다.