SQLServer Performance

• MS SQL Server 성능 측정 / 튜닝
• SET SHOWPLAN_ALL (Transact-SQL) | Microsoft Docs
• Execution Plan Basics - Simple Talk

SQLServer의 기본 Isolation Level은 READ_COMMITTED이다.

• SQLServer SSMS SqlServer Management Studio 에서 쿼리를 작성하고 쿼리 선택후 Ctrl+M 혹은 우클릭후 Include Actual Execution Plan 을 체크하고 쿼리를 실행하면 결과 창에 Execution plan 탭이 생성된다.

• How to find compiled parameter values for SQL Server cached plans

• exec sp_helpindex [tablename] : 인덱스 확인

EXEC sys.sp_helpindex @objname = N'User' -- nvarchar(77)

• SQL Server는 SELECT를 할 때 SHARED Lock을 건다.
• 이 상황에서는 다른 데서 SHARED Lock을 건 데이터는 읽을 수 있지만 UPDATE/INSERT/DELETE에 의해 EXCLUSIVE Lock이 걸리면 데이터를 읽지 못하고 SHARED Lock 확보시까지 대기한다.
• 또한 매우 실행 시간이 길고 폭넓은 데이터를 선택하는 SELECT 문 실행시에 SHARED Lock이 걸려 있으면 UPDATE/INSERT/DELETE가 EXCLUSIVE Lock을 확보하지 못해서 CUD 작업이 느려질 가능성이 있다.
• 따라서 장시간 걸리고, 여러 행(row)을 탐색하는 SELECT 쿼리의 경우 WITH (NOLOCK)으로 SHARED Lock 없이 쿼리를 수행한다.

• Locking Hints
• ISOLATION을 READ_UNCOMMITTED로 하거나 모든 FROM절의 테이블 명 뒤에 WITH (NOLOCK)을 지정한다. 붙여쓰지 말것.

SELECT id
FROM table_a WITH (NOLOCK)
 
SELECT a.id, b.id
FROM table_a a WITH (NOLOCK), table_b b WITH (NOLOCK)
WHERE a.id=b.id

• isolation level READ_UNCOMMITED의 모든 문제점을 가지게 된다.
• Dirty Reads : update 중이지만 아직은 commit이 안 된 데이터를 읽게됨. Rollback 시 데이터가 바뀌어버림.
• Phantom Reads : insert/delete 됐지만 아직은 commit이 안 된 데이터를 읽게됨. Rollback 시 데이터가 사라지거나 생김.
• Nonrepeatable Reads : 동일 트랜잭션내에서 동일 쿼리로 데이터를 읽는데 결과가 달라질 수 있다.
• 결론적으로
  • 단건 쿼리 혹은 정합성이 중요한 쿼리에서는 사용하지 말 것
  • 실행시간이 긴 통계성 쿼리에서는 거의 필수적으로 필요함.
  • CUD 트랜잭션을 짧게 가져갈 수 있도록 항상 노력할 것.

• What is the Role of the UPDATE Lock in SQL Server? - Madeira Data Solutions UPDATE Lock이 필요한 이유 설명.
• UPDATE문이 업데이트 대상 행을 탐색할 때 SELECT 처럼 SHARED Lock을 사용하면 동시에 둘 이상의 UPDATE 수행시 나중에 EXCLUSIVE Lock으로 변경이 불가하다.(SHARED Lock이 걸린 상태에서는 EXCLUSIVE Lock 불가 원칙)
• 따라서 대상 데이터 탐색시에는 UPDATE Lock을 걸고, 실제 업데이트 수행시 EXCLUSIVE Lock으로 변경한다.

• SQL Server – Execution Plan – Warnings – “Columns with no statistics” | Learning in the Open
• Understanding the SQL Server NOLOCK hint
• All about locking in SQL Server