이번에는 성능측정을 위한 여러가지 설정들에 대해 간단히 적어볼까 합니다.
항상...자세히는 못적고 간단히 적게 되는군요. 제 실력이..적당한 수준이여서...ㅡㅡ;;;
성능이란 무엇일까요? 막상 떠오르는 것은 쿼리를 수행한 시간일 아닐까 싶습니다.
쿼리문의 응답시간도 성능이고, 해당 쿼리를 위해 사용된 CPU양도 성능측정의 한가지입니다.
또한, 해당 쿼리를 수행하기 위해 접근한 페이지의 양도 성능의 중요한 요인입니다.
이러한 성능이 어떠한지 파악하기 위해 제일 중요한 것은
제대로 된 성능을 내기 위한 실행계획을 만들었는지도 볼 줄 알아야 합니다.
(그렇다고 제가 잘 본다는 것은 아니고...저도 열심히 노력중이죠..^^)
쿼리가 제대로 된 실행계획을 만들어서 사용하고 있는지,
인덱스 Seek를 해야 하는데 테이블 스캔을 하고 있는지,
때로는, 테이블 스캔을 해야 하는데, 인덱스 Seek를 하는지 등을 말입니다.
물론 SQL Server의 옵티마이져는 대부분, 현재 상황에서 최적이라 할 만할 실행계획을 만들어 낼 겁니다.
하지만, 옵티마이져가 최적의 실행계획을 만들어 낼 수 있는 환경을 만들어 주는 것은 우리 개발자 자신입니다.
그러므로 실행계획을 보고 어떤 환경을 만들어줄지 결정해주는 능력이 필요한 거죠.
1. 그래픽으로 실행계획 보는 방법
먼저, 간단하게 그래픽으로 실행계획을 보는 방법을 알아보도록 하죠.
QA(쿼리분석기)를 실행하셔서 메뉴를 보면 쿼리(Query)라는 메뉴가 있습니다.
해당 메뉴에 보면 쿼리 실행 계획(Ctrl + K)이라는 항목이 있습니다.
쿼리 실행 계획 항목을 클릭해서 실행계획을 활성화 시키게 되면
실행되는 쿼리에 대해 실행계획을 그래픽으로 볼 수 있습니다.
활성화 시키고 다음 쿼리를 수행해 보도록 하죠.
USE Northwind
go
SELECT *
FROM Customers
WHERE CustomerID LIKE 'A%'
go
실행을 하고 나면, 결과창의 아래쪽, 표형태(결과)/실행계획/(메시지) 탭이 있는 것을 알 수 있습니다.
만약에 현재 결과 표시 방식이 표형태 방식이라면, 표형태/실행계획/메시지가 있을것이고
텍스트로 결과 표시가 설정이 되어 있다면, 결과/실행계획 탭이 있을 것입니다.
실행계획 탭을 클릭해 보면, 그림으로서 실행계획이 표시되어 있는 것을 확인 할 수 있습니다.
이 방법은 아주 보기도 쉽고, 여러가지 유용한 점이 있습니다.
그림으로 표시된 실행계획에 마우스를 올려 놓아 보면, 해당 연산에 대한 기타의 정보를 볼 수 있습니다.
여기서 화살표 역시 정보를 포함하고 있음을 알 수 있습니다. 화살표는 행 개수와 예상행 크기를 보여주죠.
실행계획 위쪽을 보면 '쿼리 1:쿼리 비용(일괄 처리와 비교): 100.00%.' 라고 적혀 있습니다.
이건, 말그대로 이 쿼리가 전체 실행한 쿼리(일괄 처리)중에 몇 %의 비용을 사용하고 있는 것을 보여 줍니다.
이 비용은 쿼리문이 하나만 있을 때는 항상 100%입니다.
다음의 두 개의 쿼리문을 같이 수행하면, 각 쿼리가 상대적으로 어느정도 비용을 소모하는지 알 수 있습니다.
SELECT *
FROM Customers
WHERE CustomerID LIKE 'B%'
SELECT *
FROM Customers
결과에는 두 개의 결과가 보여지게 되고, 실행계획 역시 두 개의 실행계획이 있는 것을 알 수 있습니다.
이 그래픽으로 표시된 실행계획의 최대 이점은 보기 편한다는 것과,
더불어 두 개 이상의 쿼리를 쉽게 비교할 수 있다는 점입니다.
아주 보기 쉽게, 첫번째 쿼리가 14%의 비용을 사용하고 있고
두번째 쿼리가 85%의 비용을 사용하는 것을 알 수 있습니다.(사용자 환경만다 이 비용은 틀리게 나타납니다.)
물론 적은 비용이 효율이 더 좋은 쿼리입니다.
이런 방법은 같은 결과를 내보내주는 쿼리를 동시에 실행해서, 어느쪽이 더 유용한지 판단하는데 큰 도움이
됩니다.
SELECT *
FROM Orders T1 (INDEX =PK_Orders) JOIN [Order Details] T2
ON T1.OrderID = T2.OrderID
WHERE T1.OrderID = 10248
SELECT *
FROM Orders T1 JOIN [Order Details] T2 (INDEX =OrderID)
ON T1.OrderID = T2.OrderID
WHERE T2.OrderID = 10248
위의 쿼리는 동일한 결과를 보여주지만, 사용인덱스가 틀리므로 해서 실행비용이 틀리다는 것을 알 수 있습니다.
2. 예상 실행계획 보기
이번에는 예상실행계획을 보는 방법을 알아보도록 하죠
다음과 같은 쿼리를 만든 후에 쿼리를 블록으로 묶어서 Ctrl+L을 눌러보시죠.
SELECT *
FROM Customers
WHERE CustomerID LIKE 'B%'
결과창에는 예상실행계획/메시지 탭이 있는 것을 알 수 있습니다.
이 예상 실행계획이란, 쿼리를 실제로 수행하지는 않고, 실행계획만 보여주는 방법입니다.
만약에 시간이 오래 걸리는 쿼리에 대해 실행계획을 보고 싶다면,
이 예상 실행 계획을 통해 보는 것이 좋겠죠.
이번에는 문자형태로 보는 방법을 알아보도록 하겠습니다.
먼저 Ctrl+K를 눌러서 실행계획 표시를 없애도록 하고, 다음과 같이 수행합니다.
SET SHOWPLAN_TEXT ON
SELECT *
FROM Customers
WHERE CustomerID LIKE 'B%'
실행계획에 대해 StmtText라는 정보만 보여주고 있는 것을 알 수 있습니다.
이 Text만으로도 많은 도움이 됩니다.
실행계획을 TEXT가 아닌ALL로서 설정하게 되면 StmtText외에도 유용한 여러가지 정보를 볼 수 있습니다.
SET SHOWPLAN_TEXT OFF
go
SET SHOWPLAN_ALL ON
go
위와 같이 SHOWPLAN_ALL을 ON으로 한 다음에 다음 쿼리를 수행합니다.
SELECT *
FROM Customers
WHERE CustomerID LIKE 'B%'
StmtText외에도 여러가지 정보가 이어지는 것을 알 수 있습니다.
어떤 컬럼들이 있는지 한번씩 살펴보시기 바랍니다. 저도, 대부분 StmtText만 보므로,
어떤 것이 딱히 중요하다 말씀드리기엔 어렵군요.
3.실행계획 텍스트로 보기
다음에 설정할 것은 STATISTICS PROFILE이란 것입니다.
이것은 그래픽으로 실행계획을 봤던 것처럼, 실제 실행된 실행계획에 인한 결과까지 보여주도록 됩니다.
SET SHOWPLAN_ALL OFF
go
SET STATISTICS PROFILE ON
go
위를 수행한 다음에 다음 쿼리를 수행해 보도록 하죠.
SELECT *
FROM Customers
WHERE CustomerID LIKE 'B%'
실행계획에 Row와 Executes가 실행된 것을 알 수 있습니다.
저는 주로 그래픽으로 실행계획을 봅니다. 보기 편하거든요.
하지만, 설명을 하기 위해서는 SET STATISTICS PROFILE을 이요하게 됩니다.
어떤 것이 편한지는 개인의 취향에 따라 달라지겠죠.
여러분들 취향에 맞도록 어느 하나든 설정해서 사용하면 될 것입니다.
실행계획을 볼줄 아는 것 자체가 중요하지, 어떤 방법으로 보느냐가 중요한 것은 아니니까요.
4. 페이지IO알아보기
실행계획에 대해 지금까지 알아봤습니다.
이 외에 성능 측정을 위한 방법에는 페이지의 IO를 보는 방법이 있습니다.
SET STATISTICS IO ON
위를 수행하고 나서 다음 쿼리를 수행합니다.
SELECT *
FROM Customers
수행 후에 결과창에 메시지를 클릭하게 되면 다음과 같은 메시지가 있는 것을 알 수 있습니다.
'Customers' 테이블. 스캔 수 1, 논리적 읽기 수 4, 물리적 읽기 수 0, 미리 읽기 수 0.
이것은 해당 쿼리를 수행하기 위해 테이블에 대해 얼마만큼의 IO가 있었는지를 알려줍니다.
논리적 읽기란, 데이터 캐시에서 읽은 페이지 수이고, 물리적 읽기 수는 디스크에서 읽은 페이지 수입니다.
성능을 고려할 때는 논리적 읽기수가 주요 항목이 됩니다.
미리 읽기 수는 쿼리에 대해 데이터 캐시에 넣은 페이지 수입니다.
만약에 쿼리문등으로 처음 참조되는 테이블이라면, 미리읽기수와 물리적 읽기 수가 높게 나타날 것입니다.
그만큼, 처음 쿼리는 두번째 이후 쿼리보다 시간이 오래 걸릴 가능성이 있는 것이죠.
한번 실행된 쿼리의 데이터는 데이터 캐시에 남아 있습니다.
그러므로 성능 측정을 위해 같은 쿼리를 두번째 수행하게 되면 성능이 좋아 보이게 되죠.
이런 경우, 데이터 캐시에 있는 내용을 지우기 위해 다음 DBCC문을 사용하실 수 있습니다.
DBCC DROPCLEANBUFFERS
현재 운영되는 시스템에서 사용한다면 안좋은 일이 있을 수도 있겠죠.^^
운영을 하기 위해 메모리에 올라가 있던 데이터들이 다 내려간다면,
모두들 갑자기 속도가 느려지게 되겠죠.
5. Time측정하게
IO에 대하 통계를 OFF로 하시고, 다음은 Time이라는 설정을 해보도록 하겠습니다.
SET STATISTICS IO OFF
go
SET STATISTICS PROFILE OFF
go
SET STATISTICS TIME ON
go
위를 수행하고 다음을 수행합니다.
SELECT *
FROM Customers
실행을 하게 되면, 구문 분석 및 컴파일 시간과 실행시간이 나타나는 것을 알 수 있습니다.
시간의 단위는 밀리세컨드로 나타나게 됩니다.
시간에는 Cpu시간과 경과시간이 있습니다.
Cpu시간과 경과시간은 거의 비슷하게 나타나는 것이 좋습니다.
이 시간 자체로 성능을 측정하는 일은 별로 없었던거 같습니다.
한 문장에 대해서 이 방법을 사용하는 것은 괜찮지만, 여러개의 쿼리를 동시에 측정할 경우는
보기에 쉽지 않습니다.
그러므로 일괄적인 쿼리문에서 시간을 구하기 위해 다음처럼 수행할 수 있습니다.
DECLARE @startTime as datetime
SET @startTime = GETDATE()
SELECT * FROM Customers
SELECT DATEDIFF(ms, @startTime, GETDATE())
SET @startTime = GETDATE()
SELECT * FROM Orders T1 JOIN [Order Details] T2 ON T1.OrderID = T2.OrderID
SELECT DATEDIFF(ms, @startTime, GETDATE())
SET @startTime = GETDATE()
SELECT * FROM Orders T1 JOIN [Order Details] T2 ON T1.OrderID = T2.OrderID
WHERE T1.OrderID = 10248
SELECT DATEDIFF(ms, @startTime, GETDATE())
이 방법도 그렇게 보기 편한 방법은 아니지만, 그럭저럭 사용할 만 한 방법이죠.
아무튼, 이 시간 자체에는 크게 의미를 둘 필요는 없습니다.
온라인상에서 커버할 수 있는 정도면 충분한 시간이라고 생각하시는게 맘 편합니다.
6. 프로필러 사용하기
마지막으로 살펴볼 방법은 SQL 프로필러를 사용하는 방법입니다.
윈도우즈의 모든프로그램에서 SQL Server메뉴에 보면, 프로필러라는 실행 아이콘이 있는 것을 알 수 있습니다.
프로필러를 사용 안하셔본 분들이 많은 것 같은데, 아주 유용한 도구이므로 꼭 한번 사용해보시기 바랍니다.
하지만, 프로필러는 운영환경에 큰 영향을 줄 수 있으므로 주의가 필요합니다.
프로필러는 해당 SQL Server에서 실행되는 모든 이벤트들을 캡쳐해서 실행성능을 보여줍니다.
너무 많은 양의 이벤트들이 캡쳐된다면 프로필러가 실행되는 컴퓨터에 부하가 걸리겠죠.
뿐만 아니라, 모든 이벤트를 잡는다면, 사용자가 많은 환경에서는 시스템 전체적으로 부하가 걸릴 수도 있겠죠.
아직까지 이런 경험을 갖진 못했지만, 충분히 이럴 수 있다고 생각되니 조심하시는게 좋습니다.
하루 종일, 프로필러를 가동시키지 않고, 필요한 경우 어느 정도만 실행시키는게 좋다고 개인적으로는 생각합니다.
프로필러의 사용법은 아주 쉽습니다. 프로필러를 실행하시고
메뉴의 파이르이 새로만들기의 추적을 클릭하시면, QA(쿼리 분석기)에 들어갈 때 처럼 Login하는 화면이 나옵니다.
그럼, 트레이스를 하고자 하는 서버와 ID, 비밀번호를 입력하셔서 접속을 하시면 됩니다.
접속을 하시고 나면 추적속성이란 화면이 나옵니다.
여기에서는 추적 편하게 하기 위한 속성을 결정해 줄 수 있습니다.
먼저 일반탭에 적당한 이름을 입력하시면 됩니다.
기존의 추적에 대한 템플릿을 만들었다면 해당 화일을 열어서 사용하실 수도 있습니다.
개인적으로 적절한 템플릿을 만든다면 아주 유용하겠죠.
그리고, 추적내용을 파일이나, 테이블에 저장할 수 있는 옵션이 있는데, 저는 일반적으로 저장안하고 사용합니다.
나중에, 추적중에도 파일이나, 테이블에 추적 내용을 저장할 수 있으니까요.
그다음 이벤트 탭을 클릭하시면, 추적하고자 하는 이벤트만 설정할 수 있습니다.
여기서 이벤트를 줄이는 것이 좋겠죠. 이벤트가 많을수록 알아보기 힘드니까요.
보통, Login, Logout은 필요 없으니, 제거하는게 보기 편합니다.
그다음 탭은 이벤트 열로서, 이벤트를 캡쳐해서 보여줄 항목들을 정하는 부분입니다.
여러가지 항목들이 있지만, 개인적으로 필요한 부분만 적절한 순서로 배치하시는 것이 보기 좋습니다.
보통, TextData, Cpu, Reads, Writes, Duration이 중요하게 사용되어 집니다.
TextData는 해당 캡쳐된 내용을 텍스트로 보여줍니다.
Cpu는 해당 이벤트 발생시, 사용된 CPU시간을 보여주고, Reads는 해당 이벤트 발생시
발생된 페이지 읽기 양입니다. Writes는 페이지를 쓴 양이고, Duration은 해당 이벤트가 실행된 총 시간입니다.
마지막 탭에 보시면 필터가 있습니다.
필터를 사용해서 특정 경우에만 이벤트를 보고 싶도록 설정할 수가 있습니다.
SELECT문의 WHERE과 같은 내용이죠.
저는 보통, 시간이 오래걸리는 쿼리만을 찾아내기 위해 Duration의 크거나 같은 값을 설정해서 수행합니다.
1000이 1초를 나타내므로, 2초가 넘게 걸리는 쿼리만 보고 싶다면 2000을 주시면 됩니다.
마지막으로 실행을 눌러주시면, SQL Server에 발생되는 이벤트들이 추적되는 것을 볼 수 있습니다.
이것을 근거로, 시스템에 나쁜 영향을 주는 쿼리를 찾아내거나 해서 수정을 할 수가 있는 것이죠.
항상...자세히는 못적고 간단히 적게 되는군요. 제 실력이..적당한 수준이여서...ㅡㅡ;;;
성능이란 무엇일까요? 막상 떠오르는 것은 쿼리를 수행한 시간일 아닐까 싶습니다.
쿼리문의 응답시간도 성능이고, 해당 쿼리를 위해 사용된 CPU양도 성능측정의 한가지입니다.
또한, 해당 쿼리를 수행하기 위해 접근한 페이지의 양도 성능의 중요한 요인입니다.
이러한 성능이 어떠한지 파악하기 위해 제일 중요한 것은
제대로 된 성능을 내기 위한 실행계획을 만들었는지도 볼 줄 알아야 합니다.
(그렇다고 제가 잘 본다는 것은 아니고...저도 열심히 노력중이죠..^^)
쿼리가 제대로 된 실행계획을 만들어서 사용하고 있는지,
인덱스 Seek를 해야 하는데 테이블 스캔을 하고 있는지,
때로는, 테이블 스캔을 해야 하는데, 인덱스 Seek를 하는지 등을 말입니다.
물론 SQL Server의 옵티마이져는 대부분, 현재 상황에서 최적이라 할 만할 실행계획을 만들어 낼 겁니다.
하지만, 옵티마이져가 최적의 실행계획을 만들어 낼 수 있는 환경을 만들어 주는 것은 우리 개발자 자신입니다.
그러므로 실행계획을 보고 어떤 환경을 만들어줄지 결정해주는 능력이 필요한 거죠.
1. 그래픽으로 실행계획 보는 방법
먼저, 간단하게 그래픽으로 실행계획을 보는 방법을 알아보도록 하죠.
QA(쿼리분석기)를 실행하셔서 메뉴를 보면 쿼리(Query)라는 메뉴가 있습니다.
해당 메뉴에 보면 쿼리 실행 계획(Ctrl + K)이라는 항목이 있습니다.
쿼리 실행 계획 항목을 클릭해서 실행계획을 활성화 시키게 되면
실행되는 쿼리에 대해 실행계획을 그래픽으로 볼 수 있습니다.
활성화 시키고 다음 쿼리를 수행해 보도록 하죠.
USE Northwind
go
SELECT *
FROM Customers
WHERE CustomerID LIKE 'A%'
go
실행을 하고 나면, 결과창의 아래쪽, 표형태(결과)/실행계획/(메시지) 탭이 있는 것을 알 수 있습니다.
만약에 현재 결과 표시 방식이 표형태 방식이라면, 표형태/실행계획/메시지가 있을것이고
텍스트로 결과 표시가 설정이 되어 있다면, 결과/실행계획 탭이 있을 것입니다.
실행계획 탭을 클릭해 보면, 그림으로서 실행계획이 표시되어 있는 것을 확인 할 수 있습니다.
이 방법은 아주 보기도 쉽고, 여러가지 유용한 점이 있습니다.
그림으로 표시된 실행계획에 마우스를 올려 놓아 보면, 해당 연산에 대한 기타의 정보를 볼 수 있습니다.
여기서 화살표 역시 정보를 포함하고 있음을 알 수 있습니다. 화살표는 행 개수와 예상행 크기를 보여주죠.
실행계획 위쪽을 보면 '쿼리 1:쿼리 비용(일괄 처리와 비교): 100.00%.' 라고 적혀 있습니다.
이건, 말그대로 이 쿼리가 전체 실행한 쿼리(일괄 처리)중에 몇 %의 비용을 사용하고 있는 것을 보여 줍니다.
이 비용은 쿼리문이 하나만 있을 때는 항상 100%입니다.
다음의 두 개의 쿼리문을 같이 수행하면, 각 쿼리가 상대적으로 어느정도 비용을 소모하는지 알 수 있습니다.
SELECT *
FROM Customers
WHERE CustomerID LIKE 'B%'
SELECT *
FROM Customers
결과에는 두 개의 결과가 보여지게 되고, 실행계획 역시 두 개의 실행계획이 있는 것을 알 수 있습니다.
이 그래픽으로 표시된 실행계획의 최대 이점은 보기 편한다는 것과,
더불어 두 개 이상의 쿼리를 쉽게 비교할 수 있다는 점입니다.
아주 보기 쉽게, 첫번째 쿼리가 14%의 비용을 사용하고 있고
두번째 쿼리가 85%의 비용을 사용하는 것을 알 수 있습니다.(사용자 환경만다 이 비용은 틀리게 나타납니다.)
물론 적은 비용이 효율이 더 좋은 쿼리입니다.
이런 방법은 같은 결과를 내보내주는 쿼리를 동시에 실행해서, 어느쪽이 더 유용한지 판단하는데 큰 도움이
됩니다.
SELECT *
FROM Orders T1 (INDEX =PK_Orders) JOIN [Order Details] T2
ON T1.OrderID = T2.OrderID
WHERE T1.OrderID = 10248
SELECT *
FROM Orders T1 JOIN [Order Details] T2 (INDEX =OrderID)
ON T1.OrderID = T2.OrderID
WHERE T2.OrderID = 10248
위의 쿼리는 동일한 결과를 보여주지만, 사용인덱스가 틀리므로 해서 실행비용이 틀리다는 것을 알 수 있습니다.
2. 예상 실행계획 보기
이번에는 예상실행계획을 보는 방법을 알아보도록 하죠
다음과 같은 쿼리를 만든 후에 쿼리를 블록으로 묶어서 Ctrl+L을 눌러보시죠.
SELECT *
FROM Customers
WHERE CustomerID LIKE 'B%'
결과창에는 예상실행계획/메시지 탭이 있는 것을 알 수 있습니다.
이 예상 실행계획이란, 쿼리를 실제로 수행하지는 않고, 실행계획만 보여주는 방법입니다.
만약에 시간이 오래 걸리는 쿼리에 대해 실행계획을 보고 싶다면,
이 예상 실행 계획을 통해 보는 것이 좋겠죠.
이번에는 문자형태로 보는 방법을 알아보도록 하겠습니다.
먼저 Ctrl+K를 눌러서 실행계획 표시를 없애도록 하고, 다음과 같이 수행합니다.
SET SHOWPLAN_TEXT ON
SELECT *
FROM Customers
WHERE CustomerID LIKE 'B%'
실행계획에 대해 StmtText라는 정보만 보여주고 있는 것을 알 수 있습니다.
이 Text만으로도 많은 도움이 됩니다.
실행계획을 TEXT가 아닌ALL로서 설정하게 되면 StmtText외에도 유용한 여러가지 정보를 볼 수 있습니다.
SET SHOWPLAN_TEXT OFF
go
SET SHOWPLAN_ALL ON
go
위와 같이 SHOWPLAN_ALL을 ON으로 한 다음에 다음 쿼리를 수행합니다.
SELECT *
FROM Customers
WHERE CustomerID LIKE 'B%'
StmtText외에도 여러가지 정보가 이어지는 것을 알 수 있습니다.
어떤 컬럼들이 있는지 한번씩 살펴보시기 바랍니다. 저도, 대부분 StmtText만 보므로,
어떤 것이 딱히 중요하다 말씀드리기엔 어렵군요.
3.실행계획 텍스트로 보기
다음에 설정할 것은 STATISTICS PROFILE이란 것입니다.
이것은 그래픽으로 실행계획을 봤던 것처럼, 실제 실행된 실행계획에 인한 결과까지 보여주도록 됩니다.
SET SHOWPLAN_ALL OFF
go
SET STATISTICS PROFILE ON
go
위를 수행한 다음에 다음 쿼리를 수행해 보도록 하죠.
SELECT *
FROM Customers
WHERE CustomerID LIKE 'B%'
실행계획에 Row와 Executes가 실행된 것을 알 수 있습니다.
저는 주로 그래픽으로 실행계획을 봅니다. 보기 편하거든요.
하지만, 설명을 하기 위해서는 SET STATISTICS PROFILE을 이요하게 됩니다.
어떤 것이 편한지는 개인의 취향에 따라 달라지겠죠.
여러분들 취향에 맞도록 어느 하나든 설정해서 사용하면 될 것입니다.
실행계획을 볼줄 아는 것 자체가 중요하지, 어떤 방법으로 보느냐가 중요한 것은 아니니까요.
4. 페이지IO알아보기
실행계획에 대해 지금까지 알아봤습니다.
이 외에 성능 측정을 위한 방법에는 페이지의 IO를 보는 방법이 있습니다.
SET STATISTICS IO ON
위를 수행하고 나서 다음 쿼리를 수행합니다.
SELECT *
FROM Customers
수행 후에 결과창에 메시지를 클릭하게 되면 다음과 같은 메시지가 있는 것을 알 수 있습니다.
'Customers' 테이블. 스캔 수 1, 논리적 읽기 수 4, 물리적 읽기 수 0, 미리 읽기 수 0.
이것은 해당 쿼리를 수행하기 위해 테이블에 대해 얼마만큼의 IO가 있었는지를 알려줍니다.
논리적 읽기란, 데이터 캐시에서 읽은 페이지 수이고, 물리적 읽기 수는 디스크에서 읽은 페이지 수입니다.
성능을 고려할 때는 논리적 읽기수가 주요 항목이 됩니다.
미리 읽기 수는 쿼리에 대해 데이터 캐시에 넣은 페이지 수입니다.
만약에 쿼리문등으로 처음 참조되는 테이블이라면, 미리읽기수와 물리적 읽기 수가 높게 나타날 것입니다.
그만큼, 처음 쿼리는 두번째 이후 쿼리보다 시간이 오래 걸릴 가능성이 있는 것이죠.
한번 실행된 쿼리의 데이터는 데이터 캐시에 남아 있습니다.
그러므로 성능 측정을 위해 같은 쿼리를 두번째 수행하게 되면 성능이 좋아 보이게 되죠.
이런 경우, 데이터 캐시에 있는 내용을 지우기 위해 다음 DBCC문을 사용하실 수 있습니다.
DBCC DROPCLEANBUFFERS
현재 운영되는 시스템에서 사용한다면 안좋은 일이 있을 수도 있겠죠.^^
운영을 하기 위해 메모리에 올라가 있던 데이터들이 다 내려간다면,
모두들 갑자기 속도가 느려지게 되겠죠.
5. Time측정하게
IO에 대하 통계를 OFF로 하시고, 다음은 Time이라는 설정을 해보도록 하겠습니다.
SET STATISTICS IO OFF
go
SET STATISTICS PROFILE OFF
go
SET STATISTICS TIME ON
go
위를 수행하고 다음을 수행합니다.
SELECT *
FROM Customers
실행을 하게 되면, 구문 분석 및 컴파일 시간과 실행시간이 나타나는 것을 알 수 있습니다.
시간의 단위는 밀리세컨드로 나타나게 됩니다.
시간에는 Cpu시간과 경과시간이 있습니다.
Cpu시간과 경과시간은 거의 비슷하게 나타나는 것이 좋습니다.
이 시간 자체로 성능을 측정하는 일은 별로 없었던거 같습니다.
한 문장에 대해서 이 방법을 사용하는 것은 괜찮지만, 여러개의 쿼리를 동시에 측정할 경우는
보기에 쉽지 않습니다.
그러므로 일괄적인 쿼리문에서 시간을 구하기 위해 다음처럼 수행할 수 있습니다.
DECLARE @startTime as datetime
SET @startTime = GETDATE()
SELECT * FROM Customers
SELECT DATEDIFF(ms, @startTime, GETDATE())
SET @startTime = GETDATE()
SELECT * FROM Orders T1 JOIN [Order Details] T2 ON T1.OrderID = T2.OrderID
SELECT DATEDIFF(ms, @startTime, GETDATE())
SET @startTime = GETDATE()
SELECT * FROM Orders T1 JOIN [Order Details] T2 ON T1.OrderID = T2.OrderID
WHERE T1.OrderID = 10248
SELECT DATEDIFF(ms, @startTime, GETDATE())
이 방법도 그렇게 보기 편한 방법은 아니지만, 그럭저럭 사용할 만 한 방법이죠.
아무튼, 이 시간 자체에는 크게 의미를 둘 필요는 없습니다.
온라인상에서 커버할 수 있는 정도면 충분한 시간이라고 생각하시는게 맘 편합니다.
6. 프로필러 사용하기
마지막으로 살펴볼 방법은 SQL 프로필러를 사용하는 방법입니다.
윈도우즈의 모든프로그램에서 SQL Server메뉴에 보면, 프로필러라는 실행 아이콘이 있는 것을 알 수 있습니다.
프로필러를 사용 안하셔본 분들이 많은 것 같은데, 아주 유용한 도구이므로 꼭 한번 사용해보시기 바랍니다.
하지만, 프로필러는 운영환경에 큰 영향을 줄 수 있으므로 주의가 필요합니다.
프로필러는 해당 SQL Server에서 실행되는 모든 이벤트들을 캡쳐해서 실행성능을 보여줍니다.
너무 많은 양의 이벤트들이 캡쳐된다면 프로필러가 실행되는 컴퓨터에 부하가 걸리겠죠.
뿐만 아니라, 모든 이벤트를 잡는다면, 사용자가 많은 환경에서는 시스템 전체적으로 부하가 걸릴 수도 있겠죠.
아직까지 이런 경험을 갖진 못했지만, 충분히 이럴 수 있다고 생각되니 조심하시는게 좋습니다.
하루 종일, 프로필러를 가동시키지 않고, 필요한 경우 어느 정도만 실행시키는게 좋다고 개인적으로는 생각합니다.
프로필러의 사용법은 아주 쉽습니다. 프로필러를 실행하시고
메뉴의 파이르이 새로만들기의 추적을 클릭하시면, QA(쿼리 분석기)에 들어갈 때 처럼 Login하는 화면이 나옵니다.
그럼, 트레이스를 하고자 하는 서버와 ID, 비밀번호를 입력하셔서 접속을 하시면 됩니다.
접속을 하시고 나면 추적속성이란 화면이 나옵니다.
여기에서는 추적 편하게 하기 위한 속성을 결정해 줄 수 있습니다.
먼저 일반탭에 적당한 이름을 입력하시면 됩니다.
기존의 추적에 대한 템플릿을 만들었다면 해당 화일을 열어서 사용하실 수도 있습니다.
개인적으로 적절한 템플릿을 만든다면 아주 유용하겠죠.
그리고, 추적내용을 파일이나, 테이블에 저장할 수 있는 옵션이 있는데, 저는 일반적으로 저장안하고 사용합니다.
나중에, 추적중에도 파일이나, 테이블에 추적 내용을 저장할 수 있으니까요.
그다음 이벤트 탭을 클릭하시면, 추적하고자 하는 이벤트만 설정할 수 있습니다.
여기서 이벤트를 줄이는 것이 좋겠죠. 이벤트가 많을수록 알아보기 힘드니까요.
보통, Login, Logout은 필요 없으니, 제거하는게 보기 편합니다.
그다음 탭은 이벤트 열로서, 이벤트를 캡쳐해서 보여줄 항목들을 정하는 부분입니다.
여러가지 항목들이 있지만, 개인적으로 필요한 부분만 적절한 순서로 배치하시는 것이 보기 좋습니다.
보통, TextData, Cpu, Reads, Writes, Duration이 중요하게 사용되어 집니다.
TextData는 해당 캡쳐된 내용을 텍스트로 보여줍니다.
Cpu는 해당 이벤트 발생시, 사용된 CPU시간을 보여주고, Reads는 해당 이벤트 발생시
발생된 페이지 읽기 양입니다. Writes는 페이지를 쓴 양이고, Duration은 해당 이벤트가 실행된 총 시간입니다.
마지막 탭에 보시면 필터가 있습니다.
필터를 사용해서 특정 경우에만 이벤트를 보고 싶도록 설정할 수가 있습니다.
SELECT문의 WHERE과 같은 내용이죠.
저는 보통, 시간이 오래걸리는 쿼리만을 찾아내기 위해 Duration의 크거나 같은 값을 설정해서 수행합니다.
1000이 1초를 나타내므로, 2초가 넘게 걸리는 쿼리만 보고 싶다면 2000을 주시면 됩니다.
마지막으로 실행을 눌러주시면, SQL Server에 발생되는 이벤트들이 추적되는 것을 볼 수 있습니다.
이것을 근거로, 시스템에 나쁜 영향을 주는 쿼리를 찾아내거나 해서 수정을 할 수가 있는 것이죠.