[[TableOfContents]] ==== 잔소리 ==== SQL문법이나 툴의 사용법 등 기본적인 내용들은 설명하지 않았다. 그러므로 초보자들은 처음에 읽다가 이해가 안 되는 부분이 있을 것이다. 이런 경우 정신 건강을 위해서는 필자를 욕하고 실력 향상을 위해서는 독자 자신을 욕하라. 제품의 사용법은 다른 서적과 도움말, 웹 문서를 이용하여 익히기 바란다. 또한 ERD는 여러 가지 표기법을 사용하였다. 각각의 제품에서 사용하는 표기법도 있을 것이며, 직접 그린 표기법도 있을 것이다. 혼란을 가중하기 위한 것이 아니라 여러 표기법들이 있음을 나타내기 위함이다. (고치기 귀찮기도 하고..) 이 자료는 그 동안 필자가 공부했던 것, 생각했던 것, 경험했던 것, 남의 솔루션 등을 짬뽕한 문서이다. 즉, 이전의 글들의 엑기스에 내용을 추가한 2판이라고 생각하면 맞겠다. 내용은 여러 책들에서 참고하여 만들었다. 또한 중복되는 내용도 많이 있을 것이다. __틀린 내용도 많으니 알아서 취할 것은 취하고 버릴 것은 버리길...__ (예전 홈페이지의 내용이다. 모델링&설계, SQL, 서버 등의 내용을 모두 포함하고 있어서 이렇게 써 놨었다. 써 논게 아까워 그냥 옮겨놨다.) ==== 제공되는 내용 ==== 사실 Performance Tuning이라고 타이틀을 달아 놨지만 부끄러운 메뉴의 타이틀이다. Tip 정도나 될까? 기본만 잘 알아도 95%의 성능 튜닝은 끝난 것인데, 뭘 또 씨부리는 건지..허허 화려한 테크닉은 기초에서 나온다!!! 1. [튜닝에 들어가면서] 2. [튜닝의 개요] 3. [튜닝의 절차] 4. [디자인 튜닝] 5. [어플리케이션 튜닝] 6. [성능측정] '''DBA가 내가 관리하는 DB 성능튜닝했다고 자랑하지마라. DBA가 있음에도 성능튜닝했다고 자랑하는거 자체가 쪽팔린 일이다.''' ==== 유용한 문서들 ==== * [https://technet.microsoft.com/ko-kr/library/bb838723(v=office.12).aspx SQL Server 상태 모니터링] * [http://www.datamanipulation.net/sqlquerystress/ SQLQueryStress Tool] * [http://www.raymond.cc/blog/12-ram-disk-software-benchmarked-for-fastest-read-and-write-speed/ 12 RAM Disk Software Benchmarked for Fastest Read and Write Speed] * http://raptor.martincarlisle.com/ * [http://www.sqlservercentral.com/articles/SQL/92823/ CPU and Scheduler Performance Monitoring using SQL Server and Excel] * [http://feedproxy.google.com/~r/PaulSRandal/~3/mFJFqxaIQP8/post.aspx Most common latch classes and what they mean] * [http://sqlblogcasts.com/blogs/martinbell/archive/2012/07/15/SQL-Server-2012-and-New-Perfmon-Counters.aspx SQL Server 2012 and New Perfmon Counters] * [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 SQL Server Prefetch and Query Performance] * [http://www.sqlservercentral.com/blogs/aschenbrenner/archive/2011/11/23/running-a-tpc_2D00_c-workload-on-sql-server.aspx Running a TPC-C workload on SQL Server] * [http://www.sqlshare.com/replaying-a-sql-server-profiler-trace_448.aspx Replaying a SQL Server Profiler Trace] * [DBCC SHRINKDATABASE 성능] * [http://www.sqlservercentral.com/articles/DBCC+CHECKDB/74693/ A faster DBCC CHECKDB] * attachment:PerformanceTuning/Analyzing_Characterizing_and_IO_Size_Considerations.docx * [http://www.simple-talk.com/sql/t-sql-programming/clr-performance-testing/ CLR Performance Testing] * [http://feedproxy.google.com/~r/PaulSRandal/~3/dMMJIoH3ZoA/post.aspx Capturing wait stats for a single operation] * [http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26665 Diagnosing and Resolving Latch Contention on SQL Server] attachment:PerformanceTuning/SQLServerLatchContention.pdf * [http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26666 Diagnosing and Resolving Spinlock Contention on SQL Server] attachment:PerformanceTuning/SQLServerSpinlockContention.pdf * [http://www.sqlskills.com/BLOGS/PAUL/post/Benchmarking-Multiple-data-files-on-SSDs-(plus-Fusion-ios-latest-driver).aspx Benchmarking: Multiple data files on SSDs (plus the latest Fusion-io driver)] * [http://www.sql-server-performance.com/articles/per/performance_windows_resouce_monitor_p1.aspx Performance Troubleshooting with Windows Resource Monitor] * [http://www.mssqltips.com/tip.asp?tip=2329 How to Identify I/O Bottlenecks in MS SQL Server] * [http://www.mssqltips.com/tip.asp?tip=2316&home How to Identify CPU Bottlenecks in SQL Server] * [http://technet.microsoft.com/en-us/library/cc966545.aspx Working with tempdb in SQL Server 2005] * [http://www.quest.com/documents/landing.aspx?id=11635&technology=34&prod=&prodfamily=&loc SQL Server Perfmon Counters Poster] attachment:PerformanceTuning/SQL_Server_Perfmon_Counters_Poster.pdf * [http://www.mssqltips.com/tip.asp?tip=2119 Partition offset and allocation unit size of a disk for SQL Server] * [http://www.mssqltips.com/tip.asp?tip=2053 Trick to Optimize TOP clause in SQL Server] * [http://databaser.net/moniwiki/wiki.php/PerformanceTuning?action=edit§ion=3 Performance Counter DMV sys.dm_os_performance_counters] * [http://www.sqler.com/?document_srl=192999&mid=bColumn&rnd=193346#comment_193346 쓸모 없는 ad-hoc plan 을 sql 서버에서 강제로 지우기] attachment:PerformanceTuning/adhoc_plan.sql * [http://feedproxy.google.com/~r/PaulSRandal/~3/0AqNqvrR8L0/post.aspx Benchmarking: do multiple data files make a difference?] * [http://blogs.msdn.com/sqlperf/archive/2008/10/23/tpc-e-raising-the-bar-in-oltp-performance.aspx TPC-E – Raising the Bar in OLTP Performance] * [http://iablog.sybase.com/paulley/2008/10/the-state-of-tpc-e/ The state of TPC-E] * [http://www.sqlservercentral.com/articles/SSD+Disks/69693/ Solid State Disks and SQL Server] * [http://www.mssqltips.com/tip.asp?tip=1976&home SQL Server 2008 Spatial Index Performance] * [Get Performance Tips Directly From SQL Server] * [http://www.codeproject.com/KB/database/IndexAndDenormalize.aspx Top 10 steps to optimize data access in SQL Server] * attachment:PerformanceTuning/ADO.NET_and_SQL_Server_Performance_Tips.txt * [http://www.mssqltips.com/tip.asp?tip=1853 SQL Server Tempdb Usage and Bottlenecks tracked with Extended Events] * [http://www.mssqltips.com/tip.asp?tip=1829 Find Session State Settings Associated with Cached Plans in SQL Server] * [http://blogs.msdn.com/psssql/archive/2009/08/20/high-cpu-consumed-by-resource-monitor-due-to-low-virtual-memory.aspx High CPU consumed by Resource Monitor due to low virtual memory] * [attachment:SQLServer2008_ExEvent_TroubleShooting.docx SQL Server 2008 확장 이벤트를 사용한 고급 문제 해결] * [attachment:TShootPerfProbs2008_1.docx Troubleshooting Performance Problems in SQL Server 2008] * [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 SQL Server 비용산정] * [http://www.simple-talk.com/sql/performance/understanding-more-complex-query-plans/ Understanding More Complex Query Plans] * [http://www.mssqltips.com/tip.asp?tip=1776 Automating Performance Monitor Statistics Collection for SQL Server and Windows] * [attachment:SSD_IO_Test.pdf SSD IO 성능 테스트] -> 참고: 가비지 컬렉트에 대한 내용이 빠져 있습니다. * [Statment 타입별 실행 빈도 및 리소스 사용량 확인] * [http://www.mssqltips.com/tip.asp?tip=1776 Automating Performance Monitor Statistics Collection for SQL Server and Windows] * [http://www.mssqltips.com/tip.asp?tip=1749 Retaining historical index usage statistics for SQL Server] * [http://msdn.microsoft.com/en-us/library/dd576261.aspx Service Broker: Performance and Scalability Techniques] * [http://msdn.microsoft.com/en-us/library/dd266396.aspx Tuning the Performance of Change Data Capture in SQL Server 2008] * [http://msdn.microsoft.com/en-us/library/dd542635.aspx The Analysis Services 2008 Performance Guide] * [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 Top 10 Performance and Productivity Reasons to Use SQL Server 2008 for Your Business Intelligence Solutions] * [http://msdn.microsoft.com/en-us/library/dd425070.aspx The Data Loading Performance Guide] * [http://www.sqlskills.com/whitepapers.asp SQL Server 2008 Whitepapers] * [attachment:TShootPerfProbs2008.docx Troubleshooting Performance Problems in SQL Server 2008] * [http://msdn.microsoft.com/en-us/library/dd425070.aspx The Data Loading Performance Guide] * [http://blogs.msdn.com/psssql/archive/2008/10/01/windows-scalable-networking-pack-possible-performance-and-concurrency-impacts-to-sql-server-workloads.aspx Windows Scalable Networking Pack – Possible Performance and Concurrency Impacts to SQL Server Workloads] * [attachment:PerformanceTuning/SQL_Server_2005_Performance_Tuning_Waits_Queues_Kr.zip SQL Server 2005 Performance Tuning Waits Queues] * [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 Running SQK2k8 Analysis Services on Win2k8 vs. Win2k3 and Memory Preallocation] * [http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-2005-performance-statistics-script.aspx SQL Server 2005 Performance Statistics Script] (attachment:PerformanceTuning/PerfStatsScript2005.zip) * [http://technet.microsoft.com/en-us/library/ms345118.aspx Performance Optimizations for the XML Data Type in SQL Server 2005] * [attachment:Tools/PerformanceDashboard.zip Performance Dashboard] * [attachment:Tools/SQL_Server_DBA_Dashboard.zip DBA Dashboard] [http://www.sqlserverexamples.com/v2/Products/tabid/76/Default.aspx 원본링크] * [Performance Health Status - DMV] * [성능의 기본 7원칙] * [http://www.mssqltips.com/tip.asp?tip=1575 How To Collect Performance Data With TYPEPERF.EXE] * attachment:Tools/SQL_Perfmon.zip