1 잔소리

2 제공되는 내용

3 유용한 문서들

SQL문법이나 툴의 사용법 등 기본적인 내용들은 설명하지 않았다. 그러므로 초보자들은 처음에 읽다가 이해가 안 되는 부분이 있을 것이다. 이런 경우 정신 건강을 위해서는 필자를 욕하고 실력 향상을 위해서는 독자 자신을 욕하라. 제품의 사용법은 다른 서적과 도움말, 웹 문서를 이용하여 익히기 바란다. 또한 ERD는 여러 가지 표기법을 사용하였다. 각각의 제품에서 사용하는 표기법도 있을 것이며, 직접 그린 표기법도 있을 것이다. 혼란을 가중하기 위한 것이 아니라 여러 표기법들이 있음을 나타내기 위함이다. (고치기 귀찮기도 하고..)
이 자료는 그 동안 필자가 공부했던 것, 생각했던 것, 경험했던 것, 남의 솔루션 등을 짬뽕한 문서이다. 즉, 이전의 글들의 엑기스에 내용을 추가한 2판이라고 생각하면 맞겠다. 내용은 여러 책들에서 참고하여 만들었다. 또한 중복되는 내용도 많이 있을 것이다. 틀린 내용도 많으니 알아서 취할 것은 취하고 버릴 것은 버리길...

(예전 홈페이지의 내용이다. 모델링&설계, SQL, 서버 등의 내용을 모두 포함하고 있어서 이렇게 써 놨었다. 써 논게 아까워 그냥 옮겨놨다.)

사실 Performance Tuning이라고 타이틀을 달아 놨지만 부끄러운 메뉴의 타이틀이다. Tip 정도나 될까? 기본만 잘 알아도 95%의 성능 튜닝은 끝난 것인데, 뭘 또 씨부리는 건지..허허 화려한 테크닉은 기초에서 나온다!!!

1. 튜닝에 들어가면서
   
2. 튜닝의 개요
   
3. 튜닝의 절차
   
4. 디자인 튜닝
   
5. 어플리케이션 튜닝
   
6. 성능측정
   
   

DBA가 내가 관리하는 DB 성능튜닝했다고 자랑하지마라. DBA가 있음에도 성능튜닝했다고 자랑하는거 자체가 쪽팔린 일이다.

• SQL Server 상태 모니터링(https://technet.microsoft.com/ko-kr/library/bb838723(v=office.12).aspx)
  
• SQLQueryStress Tool(http://www.datamanipulation.net/sqlquerystress/)
  
• 12 RAM Disk Software Benchmarked for Fastest Read and Write Speed(http://www.raymond.cc/blog/12-ram-disk-software-benchmarked-for-fastest-read-and-write-speed/)
  
• http://raptor.martincarlisle.com/
  
• CPU and Scheduler Performance Monitoring using SQL Server and Excel(http://www.sqlservercentral.com/articles/SQL/92823/)
  
• Most common latch classes and what they mean(http://feedproxy.google.com/~r/PaulSRandal/~3/mFJFqxaIQP8/post.aspx)
  
• SQL Server 2012 and New Perfmon Counters(http://sqlblogcasts.com/blogs/martinbell/archive/2012/07/15/SQL-Server-2012-and-New-Perfmon-Counters.aspx)
  
• SQL Server Prefetch and Query Performance(http://www.simple-talk.com/sql/performance/sql-server-prefetch-and-query-performance/?utm_source=ssc&utm_medium=email-ssc&utm_content=Prefetch-20120529&utm_campaign=SQL)
  
• Running a TPC-C workload on SQL Server(http://www.sqlservercentral.com/blogs/aschenbrenner/archive/2011/11/23/running-a-tpc_2D00_c-workload-on-sql-server.aspx)
  
• Replaying a SQL Server Profiler Trace(http://www.sqlshare.com/replaying-a-sql-server-profiler-trace_448.aspx)
  
• DBCC SHRINKDATABASE 성능
  
• A faster DBCC CHECKDB(http://www.sqlservercentral.com/articles/DBCC+CHECKDB/74693/)
  
• Analyzing_Characterizing_and_IO_Size_Considerations.docx
  
• CLR Performance Testing(http://www.simple-talk.com/sql/t-sql-programming/clr-performance-testing/)
  
• Capturing wait stats for a single operation(http://feedproxy.google.com/~r/PaulSRandal/~3/dMMJIoH3ZoA/post.aspx)
  
• Diagnosing and Resolving Latch Contention on SQL Server(http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26665) SQLServerLatchContention.pdf
  
• Diagnosing and Resolving Spinlock Contention on SQL Server(http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26666) SQLServerSpinlockContention.pdf
  
• Benchmarking: Multiple data files on SSDs (plus the latest Fusion-io driver)(http://www.sqlskills.com/BLOGS/PAUL/post/Benchmarking-Multiple-data-files-on-SSDs-(plus-Fusion-ios-latest-driver).aspx)
  
• Performance Troubleshooting with Windows Resource Monitor(http://www.sql-server-performance.com/articles/per/performance_windows_resouce_monitor_p1.aspx)
  
• How to Identify I/O Bottlenecks in MS SQL Server(http://www.mssqltips.com/tip.asp?tip=2329)
  
• How to Identify CPU Bottlenecks in SQL Server(http://www.mssqltips.com/tip.asp?tip=2316&home)
  
• Working with tempdb in SQL Server 2005(http://technet.microsoft.com/en-us/library/cc966545.aspx)
  
• SQL Server Perfmon Counters Poster(http://www.quest.com/documents/landing.aspx?id=11635&technology=34&prod=&prodfamily=&loc) SQL_Server_Perfmon_Counters_Poster.pdf
  
• Partition offset and allocation unit size of a disk for SQL Server(http://www.mssqltips.com/tip.asp?tip=2119)
  
• Trick to Optimize TOP clause in SQL Server(http://www.mssqltips.com/tip.asp?tip=2053)
  
• Performance Counter DMV sys.dm_os_performance_counters(http://databaser.net/moniwiki/wiki.php/PerformanceTuning?action=edit§ion=3)
  
• 쓸모 없는 ad-hoc plan 을 sql 서버에서 강제로 지우기(http://www.sqler.com/?document_srl=192999&mid=bColumn&rnd=193346#comment_193346) adhoc_plan.sql
  
• Benchmarking: do multiple data files make a difference?(http://feedproxy.google.com/~r/PaulSRandal/~3/0AqNqvrR8L0/post.aspx)
  
• TPC-E – Raising the Bar in OLTP Performance(http://blogs.msdn.com/sqlperf/archive/2008/10/23/tpc-e-raising-the-bar-in-oltp-performance.aspx)
  
• The state of TPC-E(http://iablog.sybase.com/paulley/2008/10/the-state-of-tpc-e/)
  
• Solid State Disks and SQL Server(http://www.sqlservercentral.com/articles/SSD+Disks/69693/)
  
• SQL Server 2008 Spatial Index Performance(http://www.mssqltips.com/tip.asp?tip=1976&home)
  
• Get Performance Tips Directly From SQL Server
  
• Top 10 steps to optimize data access in SQL Server(http://www.codeproject.com/KB/database/IndexAndDenormalize.aspx)
  
• ADO.NET_and_SQL_Server_Performance_Tips.txt
  
• SQL Server Tempdb Usage and Bottlenecks tracked with Extended Events(http://www.mssqltips.com/tip.asp?tip=1853)
  
• Find Session State Settings Associated with Cached Plans in SQL Server(http://www.mssqltips.com/tip.asp?tip=1829)
  
• High CPU consumed by Resource Monitor due to low virtual memory(http://blogs.msdn.com/psssql/archive/2009/08/20/high-cpu-consumed-by-resource-monitor-due-to-low-virtual-memory.aspx)
  
• SQL Server 2008 확장 이벤트를 사용한 고급 문제 해결
  
• Troubleshooting Performance Problems in SQL Server 2008
  
• SQL Server 비용산정(http://blogs.msdn.com/sqlprogrammability/archive/2007/01/22/3-0-changes-in-caching-behavior-between-sql-server-2000-sql-server-2005-rtm-and-sql-server-2005-sp2.aspx)
  
• Understanding More Complex Query Plans(http://www.simple-talk.com/sql/performance/understanding-more-complex-query-plans/)
  
• Automating Performance Monitor Statistics Collection for SQL Server and Windows(http://www.mssqltips.com/tip.asp?tip=1776)
  
• SSD IO 성능 테스트 -> 참고: 가비지 컬렉트에 대한 내용이 빠져 있습니다.
  
• Statment 타입별 실행 빈도 및 리소스 사용량 확인
  
• Automating Performance Monitor Statistics Collection for SQL Server and Windows(http://www.mssqltips.com/tip.asp?tip=1776)
  
• Retaining historical index usage statistics for SQL Server(http://www.mssqltips.com/tip.asp?tip=1749)
  
• Service Broker: Performance and Scalability Techniques(http://msdn.microsoft.com/en-us/library/dd576261.aspx)
  
• Tuning the Performance of Change Data Capture in SQL Server 2008(http://msdn.microsoft.com/en-us/library/dd266396.aspx)
  
• The Analysis Services 2008 Performance Guide(http://msdn.microsoft.com/en-us/library/dd542635.aspx)
  
• Top 10 Performance and Productivity Reasons to Use SQL Server 2008 for Your Business Intelligence Solutions(http://sqlcat.com/top10lists/archive/2009/02/24/top-10-performance-and-productivity-reasons-to-use-sql-server-2008-for-your-business-intelligence-solutions.aspx)
  
• The Data Loading Performance Guide(http://msdn.microsoft.com/en-us/library/dd425070.aspx)
  
• SQL Server 2008 Whitepapers(http://www.sqlskills.com/whitepapers.asp)
  
• Troubleshooting Performance Problems in SQL Server 2008
  
• The Data Loading Performance Guide(http://msdn.microsoft.com/en-us/library/dd425070.aspx)
  
• Windows Scalable Networking Pack – Possible Performance and Concurrency Impacts to SQL Server Workloads(http://blogs.msdn.com/psssql/archive/2008/10/01/windows-scalable-networking-pack-possible-performance-and-concurrency-impacts-to-sql-server-workloads.aspx)
  
• SQL Server 2005 Performance Tuning Waits Queues
  
• Running SQK2k8 Analysis Services on Win2k8 vs. Win2k3 and Memory Preallocation(http://sqlcat.com/technicalnotes/archive/2008/07/16/running-microsoft-sql-server-2008-analysis-services-on-windows-server-2008-vs-windows-server-2003-and-memory-preallocation-lessons-learned.aspx)
  
• SQL Server 2005 Performance Statistics Script(http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-2005-performance-statistics-script.aspx) (PerfStatsScript2005.zip)
  
• Performance Optimizations for the XML Data Type in SQL Server 2005(http://technet.microsoft.com/en-us/library/ms345118.aspx)
  
• Performance Dashboard
  
• DBA Dashboard 원본링크(http://www.sqlserverexamples.com/v2/Products/tabid/76/Default.aspx)
  
• Performance Health Status - DMV
  
• 성능의 기본 7원칙
  
• How To Collect Performance Data With TYPEPERF.EXE(http://www.mssqltips.com/tip.asp?tip=1575)
  
• SQL_Perfmon.zip