Bora.Choi

SQL Server Magazine  2011.01 Cover Store
- Andrew J.Kelly

1. TIP 1: Stop Waiting Around

먼저 Waits and Queues 대해서 알아야 한다. 많이 보이는 Waits Type에 대해 알고 있어야, 튜닝을 할 수 있다.

2. TIP 2:Locate I/O Bottlenecks
 

I/O bottlenecks 에는 Performance의 중요한 key다. page_I/O_latch waits 혹은 log_write 대기가 발생하는지 살펴봐야 한다.
sys.dm_io_virtual_file_stats() 를 통해서 확인이 가능하다.
성능 counter Avg.Disk sec/Read , Avg sec/Write 카운터를 살펴본다.
OLTP 환경에서는 log file 대기는 몇 ms 초여야 한다. 적어도 10ms 이하여야 한다. 허나, 이보다 높을 경우에도 서비스에는 문제되지 않을 수 있다. 항상 자기 서버의 base line을 알아야 한다.

3. Tip 3: Root out Problem Queries

놀랍게도 서비스에 문제되는 쿼리는 8~ 10개 정도이다. 이 쿼리들이 해당 리소스를 80 ~ 90% 사용한다.
그러므로 문제되는 쿼리를 찾아서 튜닝을 해야 한다.
sys.dm_execc_query_stats, sys.dm_exec_cached_plans, sys.dm_exec_sql_text()를 이용
-> 각자에 맞는 쿼리를 만들어야 한다. 저 같은 경우도 주기적으로 쿼리하여 적재하고 모니터링 한다.

SELECT
COALESCE(DB_NAME(t.[dbid]),'Unknown') AS [DB Name],
ecp.objtype AS [Object Type],
t.[text] AS [Adhoc Batch or Object Call],
            SUBSTRING(t.[text], (qs.[statement_start_offset]/2) + 1,
            ((CASE qs.[statement_end_offset]
                        WHEN -1 THEN DATALENGTH(t.[text]) ELSE qs.[statement_end_offset] END
                                   - qs.[statement_start_offset])/2) + 1) AS [Executed Statement]
            , qs.[execution_count] AS [Counts]
            , qs.[total_worker_time] AS [Total Worker Time], (qs.[total_worker_time] /
qs.[execution_count]) AS [Avg Worker Time]
            , qs.[total_physical_reads] AS [Total Physical Reads],
(qs.[total_physical_reads] / qs.[execution_count]) AS [Avg Physical Reads]
            , qs.[total_logical_writes] AS [Total Logical Writes],
(qs.[total_logical_writes] / qs.[execution_count]) AS [Avg Logical Writes]
            , qs.[total_logical_reads] AS [Total Logical Reads],
(qs.[total_logical_reads] / qs.[execution_count]) AS [Avg Logical Reads]
            , qs.[total_clr_time] AS [Total CLR Time], (qs.[total_clr_time] /
qs.[execution_count]) AS [Avg CLR Time]
            , qs.[total_elapsed_time] AS [Total Elapsed Time], (qs.[total_elapsed_time]
/ qs.[execution_count]) AS [Avg Elapsed Time]
            , qs.[last_execution_time] AS [Last Exec Time], qs.[creation_time] AS [Creation Time]
FROM sys.dm_exec_query_stats AS qs
    JOIN sys.dm_exec_cached_plans ecp ON qs.plan_handle = ecp.plan_handle
            CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
--    ORDER BY [Total Worker Time] DESC
--    ORDER BY [Total Physical Reads] DESC
--    ORDER BY [Total Logical Writes] DESC
--    ORDER BY [Total Logical Reads] DESC
--    ORDER BY [Total CLR Time] DESC
--    ORDER BY [Total Elapsed Time] DESC
         ORDER BY [Counts] DESC

 4.Tip 4: Plan to Reuse

Plan을 재 사용하는지 확인 한다.

5. Tip 5: Monitor Index Usage

sys.dm_db_index_operational_stats() DMF를 이용해서 인덱스가 잘 사용되고 있는지, 어떤 정보로 사용되는지 확인한다.

6. Tip 6: Separate Datea and Log File
7. Tip 7:Use Separate Staging Database

Temp성 데이터를 exporting or importing 하는데는 많은 비용이 들어간다. 서비스되고 있는 DB는 Full 모드이라면 모든 활동을 logging 한다.
몇몇 DB는 성능 이익을 위해서 simple 모드로 해야 한다. 작은 로깅은 성능을 향상 시키고 작은 load만 있으면 된다.
Staging  Database는 Simple 모드로 하는것이 좋고 백업이 필요 없을 수도 있다.

8. Tip 8: Pay Attention to Log File

항상 Log File의 여유 공간을 확인해야 하고 증가를 주의깊게 봐야 한다. 
VLFs가 증가하면 성능은 저하된다.
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx

9. Tip 9: Minimize tempdb contention

application이 tempdb를 무겁게 사용하면 내부적으로 tempdb를 할당하기 위해 contention이 발생한다.
tempdb contention은 최소화 해야 한다.
Workign with tempdb in SQL Server 2005 : http://technet.microsoft.com/en-us/library/cc966545.aspx

processor core 하나에 tempdb file 하나를 생성하기를 권장한다. 모든 같은 사이즈여야 한다.그렇지 않으면 이익이 없다.
임시성 테이블을 항상 drop 해줘야 한다.

10. Tip 10 : Change the Max Memory Limit