Download presentation
Presentation is loading. Please wait.
2
SQL 서버 성능 문제 해결 및 Locking Internals
[제목과 문서속성 값을 입력해 주세요. 문서속성 값은 파일 메뉴>속성에서 입력해 주시면 됩니다.] [하단의 슬라이드 노트 내용 입력란에 슬라이드별 스크립트를 삽입해 주시기 바랍니다.] 김종구 대리 기술지원부 마이크로소프트
3
강사 소개 김종구/마이크로소프트 기술지원부 (2000 ~ 현재) Infrastructure RRE
SQL Support Engineer KT NeOSS, Auction, 삼성생명 등 주요 사이트 기술지원 “SQL Memory Architecture” 등 TechNet 세미나 진행 [강사 소개 부분은 필수 사항은 아니나 되도록이면 사진, 경력 등을 기입해 주시기 바랍니다.]
4
목적 성능 관련 문제 발생 시 데이터 수집을 위한 PSSDIAG 툴 소개
성능 문제 해결을 위한 전반적인 방법론 소개 및 관련 지식 전달 Locking Internal에 대한 지식 전달
5
대상 기술범위 PSSDIAG Tool Read80Trace Tool
SQL Server Performance Troubleshooting Methodology Locking Internals [세션에서 다루는 기술범위] [예 : Windows Sever 2003 성능]
6
이 주제를 이해하는 데 필요한 지식 Level 200 - sysprocesses 의 waittype 및 lastwaittype
Performance 문제 해결을 위한 기본 데이터에 대한 이해 - sysprocesses 의 waittype 및 lastwaittype - blocking Monitoring방법 - 성능 로그 사용법 및 주요 counter - Profiler 사용법 [이 세션을 쉽게 이해를 하기 위해서 필요한 지식이나 정보를 입력해주세요.] [Level은 다음과 같은 기준으로 입력합니다.] - 레벨 100: 초보자 – 개념, 기능, 장점, 향후 발전 방향 - 레벨 200: 중급자 – 100 내용에서 좀더 자세히 - 레벨 300: 고급자 – 사용경험과 아키텍처, 상세설명 및 성능, 마이그레이션, 설치, 개발 - 레벨 400: 전문가 – 사용경험 위주의 상세한 설명으로 전문가 대상의 상세한 설명 Level 200
7
목차 PSSDiag SQL Server Performance Troubleshooting
SQL Server Locking Internals and Troubleshooting
8
PSSDiag
9
What is PSSDiag? Wrapper around data collection APIs commonly used in PSS, particularly SQL Server Support (Profiler, blocking script, Perfmon/Sysmon, SQLDIAG, event logs, etc) Designed to provide double-click simplicity and reduce user error Get all the needed data the first time, collected at the same time See KB for information and download location
10
Components GUI configuration utility (PSSDiagConfig.EXE)
Configure types of data to collect Save configuration in XML document Can also manage the collector service Collector (PSSDiag.EXE) Collector app, consumes configuration file created by GUI Can run as a service or a console app
11
GUI configuration utility
Typical use: Select the target version of SQL Server Supply authentication mode and relevant info Select and configure the diagnostics you want to collect Click Start The GUI configures and starts the collector service for you Any output messages from the collector will be displayed in the GUI Diagnostic files will be written to the output folder (.\OUTPUT by default)
12
Collector (PSSDiag.EXE)
Can run as a console app or as a service (GUI always runs it as a service) Can compress files using NTFS or ZIP compression via /Cn parameter Logging out while running as a console will stop data collection Works fine from Terminal Services session Set the output folder via /O parameter To uninstall, run PSSDiag /U to uninstall the service (if you’ve installed it), then delete the files extracted from the archive
13
Collector (PSSDiag.EXE)
Optional command line params: /Cn – (/C1 for NTFS background compression, /C2 for ZIP compression at shutdown) /Nn – erase, overwrite, or rename output folder /B YYYYMMDD_HH:MM:SS – Start time /E YYYYMMDD_HH:MM:SS – Automatic shutdown time /G – Generic mode. Disables SQL Server-centric mode to permit collection on machines without SQL Server installed /R – register as a service /U – deregister service
14
Collecting Data From a Clustered SQL Instance
Two options here: Use the default machine name (“.”) when running on a cluster node, and PSSDiag will collect data from all SQL Server virtual servers on the cluster Supply a virtual SQL Server name for the machine name (leave the instance name set to “*”) and PSSDiag will collect from that virtual server only
15
Collecting Data from a Remote Server
Supply the machine name when starting the GUI Configuration is disabled when connecting to a remote machine Output files will be written to remote machine Output path must exist or be creatable Run the collector as a console app if you wish to capture diagnostic files to the local machine: Profiler trace is always a server-side collection Output path must exist on local machine and the SQL Server Never capture Profiler to a UNC or network drive .TRC files will be copied to local OUTPUT dir on shutdown Other data types (blocker, perfmon, etc) will be captured wherever the collector console app is running
16
Scheduling Collection Start/Stop
When running as a service: Schedule an NT job to run PSSDiagControl START to start the service Schedule an NT job to run PSSDiagControl STOP to stop it When running as a console app: Schedule the console app to start via the NT scheduler Use /E or /B parameters if start/stop time and day (or relative time) is known Schedule an NT job to create a file named PSSDiag.STOP in the output folder Don’t KILL or you will leave Profiler trace running on SQL - see KB for manually stop and delete trace.
17
Collecting Data for Extended Periods
Run as a service so that you can log out of the console (the GUI needn’t keep running) Can schedule start/stop times and delete/rename old output folder Consider using /C1 to minimize space used by rolled over .TRC and .BLG files The fact that .TRC must always be collected on the server makes remote collection not very effective for minimizing disk space use on the server
18
PSSDiag Impact on Server Performance
Impact of PSSDiag.EXE itself is negligible Perf impact of collection equals sum of costs of diagnostics being collected Generally dominated by cost of Profiler tracing (use the “Detailed Performance” trace template only when you actually need it) Blocking detection script and Perfmon shouldn’t have a significant impact
19
Troubleshooting Intent is to save time – don’t let it become an obstacle Main collector log is ##PSSDiag.LOG All console output also written to application event log Some Perfmon counter errors are normal (e.g. “Could not add counter: XYZ. - The specified object is not found on the system.”) All scripts (and profiler trace) are started via osql.exe. Script output files are .OUT files with names like “##server__Run_sp_trace.OUT”
20
Demo - Using PSSDIAG Tool
[90분 강의는 3~4개의 데모가, 60분 강의는 1~2개의 데모가 반드시 포함되어야 합니다.] [데모 주제 아래의 [작업 1][작업 2]는 데모의 흐름, Task 목록 등을 기입해 주시면 됩니다.] [데모별로 상기의 슬라이드가 데모 전에 나와야 합니다.]
21
SQL Server Performance
Troubleshooting
22
Agenda Methodology Resource bottlenecks Determining your bottleneck
Which queries are responsible Tuning the identified queries
23
Methodology System performance is the result of aggregate performance of all queries At a high level what type of bottleneck does system have Find the queries using the most of that resource Is resource being used efficiently Always another bottleneck
24
Common DB Bottlenecks Synchronization (Locks/Latches) CPU IO Memory
is good KB for this CPU Single query/single CPU Single query/parallel Aggregate query load over all CPUs IO Insufficient memory or poor access path? Memory SQL Server throttles the number of concurrently executing queries with sorts/hashes
25
Performance Monitor Synchronization CPU
Locks: Lock Waits/sec, Lock Wait Time (ms) Latches: Total Latch Wait Time, Latch Waits/sec CPU Sustained rates at 75+ percent Compiles/sec, Recompiles/sec
26
Performance Monitor II
IO SQL Server View Page Reads/sec Readahead pages/sec Checkpoint & Lazywrites/sec fn_virtualfilestats Operating System View Avg Disk sec/Read or Write Disk Queue Length is often NOT a good indicator Memory Memory Grants Pending Max/Granted Workspace Memory
27
DBCC SQLPERF(WAITSTATS)
Number of waits & total wait time for each waittype Example PAGEIOLATCH_SH PAGEIOLATCH_UP PAGEIOLATCH_EX KB for description of the various waittypes Take delta between snapshots, or clear with DBCC SQLPERF(WAITSTATS, CLEAR)
28
SQL Server Trace (Profiler)
Use the sp_trace procedures instead of the GUI Significantly less performance impact Won’t “drop” events if rate is high Write trace files to fast drive(s) Configure what you want to trace in GUI and use File – Script Trace option PSS prefers PSSDiag option for one step collection
29
Analyzing Trace Data Use GUI option to sort by a column
Use fn_trace_gettable to load and query the data Problems Time consuming and generally requires you to have a specific problem in mind Individual queries identified may not be relevant to the problem Too manual—easy to miss things
30
Introducing Read80Trace
All text is “normalized” to remove comments, white space & parameters Database only stores the text of the first “unique” entry Detail data is loaded in normalized format to facilitate joins, reduce redundant data Connections Batches/UniqueBatches Statements/UniqueStatements Plans/UniquePlans See KB for download location
31
Demo Using Read80trace Tool - Queries using the most CPU
[90분 강의는 3~4개의 데모가, 60분 강의는 1~2개의 데모가 반드시 포함되어야 합니다.] [데모 주제 아래의 [작업 1][작업 2]는 데모의 흐름, Task 목록 등을 기입해 주시면 됩니다.] [데모별로 상기의 슬라이드가 데모 전에 나와야 합니다.] Using Read80trace Tool - Queries using the most CPU - Query that changes execution plans - Comparing “good” trace with “bad” trace
32
Looking at Specific Queries
Does performance change correlate with plan differences Different execution plan Different amount of work performed Majority of bad plans caused by poor cardinality estimates Use STATISTICS PROFILE to find the problematic part of plan
33
Using STATISTICS PROFILE
Everything except Rows/Executes is compile time information Executes column reflects parallelism For example, scan that executes 4 times Compare Rows with (EstimateRows * EstimateExecutes) Find most deeply nested operator where the error originates; it propagates up the tree from there
34
Demo - Using STATISTICS PROFILE
[90분 강의는 3~4개의 데모가, 60분 강의는 1~2개의 데모가 반드시 포함되어야 합니다.] [데모 주제 아래의 [작업 1][작업 2]는 데모의 흐름, Task 목록 등을 기입해 주시면 됩니다.] [데모별로 상기의 슬라이드가 데모 전에 나와야 합니다.] - Using STATISTICS PROFILE
35
Acceptable Cardinality Error
Reasonable margin of error depends on operator Loop joins – within 2x range pretty reasonable Merge join – 5x is reasonable Hash joins – size of build input (first table below join) affects hash table memory size. Probe input (second table) doesn’t matter much Sorts – size affects memory grant and 2x is reasonable Differences in estimates may not be bugs Should the optimizer do better given the available statistics
36
Cardinality Estimation
Histograms contain most useful information for predicates with literal au_lname = ‘Smith’ StartTime BETWEEN ‘ ’ AND ‘ ’ Density information ColA = ‘x’ and ColB = ‘y’ Equijoins Equality predicates with variables Auto create statistics only creates single column statistics Other estimates usually based on fixed selectivity estimates Percentage based on the comparison operator See Inside SQL Server 2000 for table of values
37
Auto Statistics Samples a percentage of the data
Minimum sampling ~4MB of data Maximum a function of rows in table If you have issues with bad plans Update statistics (sampled) – if this fixes it then histogram is probably out of date or auto update not triggered soon enough for your query If fullscan required to fix a problem use DBCC SHOW_STATISTICS to see how much the density values differ
38
Limitations to Consider
T-SQL variable (as opposed to parameter) Value not known at compile time so can’t use histogram Builtin functions No statistics available Multi-statement table-valued functions Table variables Temp table & recompile uses statistics
39
SQL Server Locking Internals and Troubleshooting
40
Introduction UMS Scheduling and Workers What is a SQL Resource
How SQL Server really waits on a ‘resource’ Blocking Crabbing Fetch Rates Physical vs Logical Protection Scans and Lock Classes Lock Escalation
41
UMS Scheduling User Mode Scheduling Precise Resource Usage
Preemptive vs Non-Preemptive
42
SQL Server Workers What is a worker? Worker Pool
Request bound to Worker for (Life Time) - Example Target Setting - Example Not Dynamic Division of Workers
43
Connection Bound To Scheduler
Assignment: Scheduler with fewest users Life time: Bound for connection life time Workload Matters
44
A SQL Resource Reader / Writer Waiters list FIFO Maintained
45
Blocked / Blocking Blocked Worker tied up in block scenario
Attentions / Query Timeout (Blocking) Diagnostics sysprocesses syslockinfo dbcc opentran - Example Profiler and SQLTrace xact_abort Performance Monitor dbcc sqlperf(waitstats) dbcc pss – Example PSSDiag
46
Resource Crabbing Maintains Data Stability Acquire Next
Release Previous
47
Client Fetch Rate Matters
Sending Results How Crabbing Applies Mobile Links Lock Scope Never Perfect Preemptive Network Writes
48
Batch Size Can Matter Touching Several Objects External Logic
XProcs COM Objects The Transaction Log Sector Alignment Flush to LSN
49
DTC Locking All Under One Roof SQL 7.0 Behavior SQL 2000 Behavior
Deadlocking SPID = -1 or SPID = -2
50
Physical versus Logical Protection
Physical = Latch Reading Page Into Memory Writing A Page To Disk Inserting A Row Internal Data Structures Logical = Lock Inserting / Modifying A Row
51
Scans (the SDES) Lock Classes
What is an SDES? SDES and Query Plan - Example Parallel – Multiple Scans What is a Lock Class? Lock Class and Lock Escalation
52
Lock Escalation What is Lock Escalation? Lock Escalation Paths
Escalation Boundaries Monitoring Lock Escalation Performance Monitor Profiler Work Tables Escalation Failures Trace Flag -T1211 Batch Size Matters
53
Helpful Tidbits Identity Property Update with Variable Assignment
Attentions / Query Cancels Blocked Scheduler Workers Open, Fetch, and Close Implicit Transactions MAXDOP = 1 Locking Hints Isolation Levels
54
세션 요약 성능관련 문제 해결을 위한 데이터 수집 툴인 PSSDIAG에 대한 사용 방법 소개 성능문제 파악을 위한 주요 방법론
Locking Internal에 대한 개념 [세션 내용을 요약하여 고객께 다시 한번 정리하여 줍니다.]
55
참고 자료 (1) TechEd 2004 Korea - SQL Server Performance Toolbox (by 하성희)
“SQL Server 2000 Performance Tuning with Waits and Queues” SQL Magazine (January 2004) By Tom Davidson “Inside SQL Server 2000” by Kalen Delaney “Microsoft SQL Server 2000 Performance Tuning Technical Reference “ by Edward Whalen [참고하신 웹사이트나 관련 문서, 리포트 등의 출처를 기재하여 주십시오.]
56
참고자료 (2) Q Troubleshooting Application Performance with SQL Server Q How to Monitor SQL Server 2000 Blocking Q Understanding and Resolving SQL Server 7.0 Blocking Problem Q Troubleshooting Performance of Ad-Hoc Queries Q Definition of Sysprocesses Waittype and Lastwaittype Fields for SQL Server 7.0 Q INF: How to Monitor SQL Server 2000 Blocking Q INF: Troubleshooting Application Performance with SQL Server Q INF: Troubleshooting Slow-running Queries Q INF: Troubleshooting Stored Procedure Recompilation Q The waittype and lastwaittype fields in the sysprocesses table Q sp_lock2 Returns Additional Data Q Job to Monitor SQL Server 2000 Performance and Activity Q How to View SQL Server 2000 Activity Data Q INF: Using Output Parameters with sp_executesql Q INF: How to Monitor SQL Server 2000 Traces
Similar presentations