Top PerfMon Counters For Analyzing SQL Server Performance .
Top PerfMon Counters forAnalyzing SQL ServerPerformance IssuesJeffry A. SchwartzNovember 13, 2008SQLRx Webinar Seriesjeffrys@isi85.comSQLRx.com
IntroductionAnalysts often look in the wrong places to solveperformance problems because Users provide vague descriptions regarding poor performanceMisleading or misinterpreted published informationInadequate training involving system and database performanceDatabase automatically blamed regardless of actual causeAnalysts must possess techniques for accuratelydetermining Causes of poor performance- Which hardware or software components are in actually in trouble- Using Windows Performance Monitor to identify major problem areas Which queries are most troublesome, if any How to develop appropriate solutionsSQLRx.com2
IntroductionToday’s is first of several performanceoriented sessions Provide performance metrics and analysis techniques to expediteanalyses Hardware-related and preliminary SQL Server performanceanalysis discussed today Described information and techniques applicable to Windows2000/2003/2008 and SQL Server 2000/2005/2008 More in-depth measurements and techniques presented in futuresessionsSQLRx.com3
IntroductionMany analysts misled by metrics that are useless,incomplete or whose importance is overly inflated Processor Queue Length% Disk TimeDisk Queue LengthBuffer Cache Hit Ratio (SQL)Unaware of invaluable metrics % User Time (Processor)% Idle Time (Disk)Avg. Disk sec/TransferAvailable Bytes (Memory)Page Life Expectancy (SQL)Page Reads/sec (Memory and SQL)SQLRx.com4
Query Side-EffectsPoorly designed SQL queries can cause asystem to appear to be out of Processor Memory DiskBecause they can perform excessive Processor work while churning through memory-resident databuffers Physical I/Os when data is not memory-resident that ultimatelyexhaust physical memorySorts often create all of the aboveSQLRx.com5
Why Use Windows PerformanceMonitor?Essential to focus any kind of analysisRemember SQL Server is only an applicationthat runs under Windows If Windows does not perform well, neither will SQL Server SQL Server can make Windows perform poorly if improperlyconfiguredSQL Server metrics independent of hardware Most imply hardware performance issues, but cannot definitivelyisolate hardware componentsCan gather performance data over timeSQLRx.com6
Analysis MethodologyUse Windows Performance Monitor, a.k.a.PerfMon, to determine Times when problems occur along with their durations Which hardware components are involved Many small queries or a few large resource-intensive queries?Retain and analyze performance data usingtool like ExcelCorrelate PerfMon data with other userexperience, business, or computer dataSQLRx.com7
PerfMonCapture PerfMon data continuously to log file All bad periods will be captured, even the unreported ones Dangerous to assume you know all the bad periods and theircharacteristicsLogging to a file Extremely low overhead unless process or thread objects captured Process or thread objects generally unnecessary on SQL ServermachinesInformation logged primarily in two ways Binary Comma-separatedOn x64, must use version that matches SQL Server If using 32-bit SQL Server on x64, must use 32-bit collectorsSQLRx.com8
PerfMonPerformance data buffers not locked Minimizes impact of performance data gathering on Windowsperformance Values not always synchronized with each other, even for thesame object Reconciling multiple counters with each other may be difficult, ifnot impossibleObtainable from local or remote machine Local collection can increase system overhead- Use binary format when captured on local machine to reduceoverhead Remote collection (preferred) can increase network overhead- Almost never an issue- Can use CSV format to simplify analysesSQLRx.com9
PerfMonData collection frequency Do not use GUI default of one second for any monitoring- Places undue pressure on system- Displays misleading values caused by volatility- Minimize GUI usage unless small # of counters and lowcollection frequency 30 seconds usually sufficient for performance problems 1 to 5 minutes usually sufficient for trending/capacity planningGUI default settings can be changed andsaved so proper behavior automatic Update frequency Selected objects, instances, and countersSQLRx.com10
Performance Data AnalysisPerfMon Initially not intended as primary performance data analysis tool Evolved into just that Analysis typically VERY slow and laborious, especially with largefiles No really effective way to export substantial amounts of raw datavia GUIMost analysts familiar with Excel 2003 limited to 256 columns so smaller, i.e., 255 item countersets must be used 2007 virtually unlimited, so full counter sets can be usedSQLRx.com11
PerfMon Analysis ConsiderationsCSV format Easier to use outside of PerfMon, if # of counters 256 or XL2007 Records can be impossibly long for spreadsheet programs Imposes 10x more overhead on collection machine (NOT targetmachine) than if binary usedBinary format Usually significantly larger than CSV formatCumbersome because primarily usable only with PerfMonRequired if process data gatheredConvertible using Relog program shipped with 2003- Converted files MUCH smaller, but NO data lost in conversionSQLRx.com12
RelogConverts NT 4 and Windows 2000/2003/2008logs for easier manipulation outside PerfMon Any format CSV, tab-delimited, SQL Server tablesUse 2003 version instead of 2000, XP, or Vista 2003 version has fewest problemsRuns properly on non-2003 OS when 2003pdh.dll located in same directory asexecutable XP pdh.dll 2003 pdh.dll Vista pdh.dll 2003 pdh.dllhttp://www.mssqltips.com/tip.asp?tip 1607SQLRx.com13
PerfMon Analysis ProceduresIf PerfMon data file in binary format, convertusing relogImport CSV into appropriate version of ExcelAdd formulas for missing Disk data Discussed later in this presentationUse Excel’s conditional formatting tohighlight warning, danger, and extremedanger conditionsGraph important entities against each otherto create visual correlationsSQLRx.com14
Windows Performance CountersLiterally hundreds of unique counters Potentially thousands of instance-countercombinationsImperative system categories to collect ProcessorMemoryPhysical DiskLogical Disk (usually present)Network I/OSQL Server – all objects and instancesSQLRx.com15
SQL Server Performance CountersOne set per SQL Server instanceSometimes will not appear in PerfMon wheninstance stoppedSometimes must be rebuilt because registrybecomes corrupt Seems to most often occur on SQL Server 2000 clusteredenvironments Occurs far less often on SQL Server 2005SQLRx.com16
Which Performance Counters Useful?Processor % Processor Time% Privileged Time% User Time% Interrupt Time% DPC Time (Deferred Procedure Calls)System Context Switches/secSQLRx.com17
Which Performance Counters Useful?Physical Disk Avg. Disk sec/Transfer- Should be 0.020 seconds (20 ms) at most unless I/O size huge % Idle Time- Once this reaches zero, I/O rate cannot be increased- Performance usually degrades as it approaches zero Disk Transfers/sec, Disk Bytes/sec- Beware of disk specs because they usually cite very large I/Os Read and Write-specific counters also valuable, especially whenread/write performance disparity exists or using RAID 5Logical Disk Same counters available plus space-related ones Useful when multiple logical drives reside on one physical LUNSQLRx.com18
Which Performance Counters Useful?Memory Page Reads/sec- Not just reads from paging file!- SQL Server I/Os not counted here- Should be almost zero on dedicated SQL Server machineexcept when Reading flat files into the database Working with backups Recreating full text indices Available Bytes (Kbytes or Mbytes)- Should be at least 500 MB to allow for above activities- Some books suggest 4 MB ok – it is NOT- System will stop responding long before this pointSQLRx.com19
Page Reads ExampleSQLRx.com20
Which Performance Counters Useful?Network Interface Bytes Total/sec (for each NIC) Packets/sec (for each NIC)- Packets usually saturate NICs long before byte traffic does- Especially true if outboard optimizations disabled Many have been disabled by default in the past Sometimes helpful to highlight application server problems thatreally are not database server problems- If very little data is arriving, problems may lie elsewhereSQLRx.com21
Minimal SQL Server Object ListSQL Server ObjectsSQL Server: Access MethodsCategoryDatabase accessSQL Server: Buffer ManagerMemory managementSQL Server: Memory ManagerMemory managementSQLRx.com22
Interpreting Performance CountersProcessor queue length cannot be used reliablyunless long spikes occur Most useful as relative measurementMany counters misunderstood, e.g., % Disk TimePerpetuated by PerfMon explanation “% Disk Time is the percentage of elapsed time that the selected diskdrive was busy servicing read or write requests.”Actually % Disk Time 100 * Avg. Disk Queue Length Frequently referenced and interpreted as disk “busy” time Completely useless metric Artificially constrained to 100% by PerfMonActual busy 100 - % Idle TimeSQLRx.com23
Physical I/O MeasurementsCritical for SQL Server systems because theyare most frequently I/O constrainedI/O time measured directly by disk driver Provides transfer times to WindowsI/O time service time queue time due todriver’s location in I/O path Disk response timeMust know whether queuing causing large I/Otimes Reducing large service times usually requires additionalhardwareSQLRx.com24
Interpreting Performance CountersDisk Queue lengths By far, most commonly quoted and used disk performancemeasurement- Actually least useful, except when outrageously high Use Avg. Disk sec/Transfer and % Idle Time insteadSQLRx.com25
Utilization versus Queue Depth GraphSQLRx.com26
Performance Counters IncompleteTwo important metrics not measured orreported directly Avg. Disk Service Time per Transfer Avg. Disk Queuing Time per TransferMissing values can be computed using theUtilization LawSQLRx.com27
Using Utilization Law to Compute MissingI/O-Related TimesAll calculations use PhysicalDisk counters LogicalDisk counters can be used, if necessaryDisk Utilization 100 - % Idle TimeDisk service time Disk Utilization / DiskTransfers/secDisk queue time Avg. Disk sec/Transfer Disk service timeSQLRx.com28
RAID Example Calculations #1 and #2LUN #1LUN #2Disk UtilizationDisk Utilization36.57%77.67%Disk Transfers/sec0.65Disk Transfers/sec30.89Avg. Disk sec/TransferAvg. Disk sec/Transfer2.0095 seconds!2.4424 seconds!Disk service time0.3657 / 0.65 0.563 seconds or563 millisecondsDisk queue time2.0095 – 0.563 1.447 secondsor 1,447 millisecondsBytes/Transfer1,307Disk service time0.7767 / 30.89 0.025 secondsor 25 millisecondsDisk queue time2.4424 – 0.025 2.4174seconds or 2,417 millisecondsBytes/Transfer22,437SQLRx.com29
RAID Example #1 vs. #2I/O times (2.0095 vs. 2.4424) not that differentdespite being outrageously highQueuing occurred on both disksLow I/O rate of Disk #1 appears to contributeto high service times 1,307 bytes should not require 563 millisecondsSQLRx.com30
RAID Example #1 vs. #2Disk #2 doing much more work Utilization double that of Disk #1 I/O size 17 times larger, but not huge Service time much more reasonable @ 25 millisecondsProblems began when faster processorcomplex attached Customer blamed new processor for poor performance Wanted vendor to take it back because architecture wassupposedly defective and slower than original In reality, it was MUCH faster!Solution was to reconfigure EMC drives Customer refused to state exactly what they changed Probably multiple LUNs shared same physical drivesSQLRx.com31
Database I/O CountersPage reads/sec and Page writes/sec countersMeasures physical I/Os, not logical I/OsMay indicate Insufficient database memory Applications improperly accessing database Improper database table implementationUseful to plot reads and writes together onsame graph Highlights changes in workload behavior Heavy write activity may coincide with periods of poorperformanceSQLRx.com32
SQL Server I/O Activity GraphSQLRx.com33
Memory Very Important to SQL ServerCan reduce I/O subsystem load and improveperformance Remember disk speed still in 10-3 seconds range, whereasmemory speed in 10-9 seconds range Also depends upon Read/Write ratio- Reads helped by memory- # of writes may be reduced slightly until a checkpointSQLRx.com34
Buffer Cache Hit RatioFrequency database read requests satisfiedfrom database cache memory instead of disk Often quoted exclusively as a measure of memory pressureHigher values may result in lower disk usage Recommended value at least 90% Raw performance data can sometimes exceed 100%Not nearly as useful as many believeObserved numerous customer systems BCHR never dropped below 90% However, Page Life Expectancy was seldom above 300SQLRx.com35
Page Life ExpectancyMeasures amount of time non-locked bufferremains in memoryFar more useful for identifying insufficientmemory situations Values consistently under 300 seconds indicate SQL Server doesnot possess enough memorySQLRx.com36
Detecting Insufficient SQL MemoryCompare Memory Manager object’s TargetServer Memory (KB) with Total ServerMemory (KB) counters If Total consistently less than Target, possibly insufficient memory Procedure cache can be consuming remainder of memoryIf Page Life Expectancy too low Allocate more memory to SQL Server or optimize queries Malformed queries that read inappropriate amounts of data cancause low Page Life Expectancy and Buffer Cache Hit RatiosSQLRx.com37
SQL Statement HandlingDefinition of a Batch Group of SQL statements Possibly hundreds or thousands of lines Must be parsed and compiled into optimized execution planSQLRx.com38
Batch Requests/secDoes not adhere to actual batch definitionEach select, insert, or delete statementtriggers a batch event Causes counter to be incremented Note: Each select, insert, or delete statement within a storedprocedure countedSQLRx.com39
Page Lookups/sec CounterMeasures number of times databaseattempted to find page in buffer pool“Logical” read Corresponds to a read in SQL Profiler TraceCompare Batch Requests/sec with Page Lookups/sec Page Life Expectancy with Page Lookups/secSQLRx.com40
Batch Requests vs. Page LookupsGraphSQLRx.com41
Page Life Expectancy versus PageLookups GraphSQLRx.com42
Using SQLRx VitalSigns PerfMonTemplateEdit SQLRxVitalSignsV1.htm in NotepadReplace CUSTOMERSYSTEM with desired servername and save fileRun PerfMonSelect Counter Logs from Performance Logs andAlertsRight click in right pane and choose Select LogSettings FromNavigate to SQLRxVitalSignsV1.htm (OK)If collecting remotely, change properties from binaryto CSVContact me for Vista or Server 2008 templateSQLRx.com43
Using SQLRx(SQLRxVitalSignsV1 Sample.xls)Open created CSV (or converted binary) logfile in ExcelDetermine column letters for PhysicalDisk %Idle and Disk Transfers/secCopy formulas in columns BJ and BK fromSQLRxVitalSignsV1 Sample.xlsChange column values to reflect actualcolumnsCopy for all LUNsSQLRx.com44
Using SQLRx(SQLRxVitalSignsV1 Sample.xls)SQLRx.com45
Assessing Your SystemPotential problems exist if following countersconsistently % Processor Time 70%% Privileged Time 30%% Interrupt Time 20%% DPC Time 25% (Processor)Available Bytes 500 MB (Memory)% Idle Time 40% for any Disk LUN and especially SQL LUNsAvg. Disk sec/Transfer 0.040 seconds (40 ms)Avg. Disk sec/Write 0.040 seconds (40 ms)Page Life Expectancy 300 seconds (SQLServer:BufferManager)SQLRx.com46
ConclusionsWindows Performance Monitor should alwaysbe used to focus tuning effortsExtremely important to combine Windowssystem performance and SQL Serverinformation Especially for processor, memory, I/O, and networkExcel can be used to analyze PerfMon dataImportant missing Disk metrics can becomputedSQLRx.com47
Topics Covered in Future SessionsUsage and interpretation of additional Windows and SQL Server Counters Computed metrics from PerfMon counter dataDisk SAN and RAID performance issuesInternal SQL Server performance data Usage of SQL Server 2005/2008 Dynamic Management ViewsUsing lean SQL Traces to identifyperformance issues quickly New SQL 2005/2008 SQL Trace event classes and their usageOptimizing queriesYour suggestions SQLRx.com48
Next Steps1.Download VitalSigns tools zip file from www.sqlrx.com.2.Collect data using VitalSigns PerfMon template.3.Import data into Excel & add disk formulas using sampleworkbook.4.Schedule a 15-minute HealthCheck (no charge) to reviewcollected data. Email me, Dan Hooper, at dhooper@isi85.comor use “Contact Us” link on www.sqlrx.com .5.Attend next in series and email suggested performance topicsof interest to jeffrys@isi85.com.SQLRx.com49
2000/2003/2008 and SQL Server 2000/2005/2008 More in-depth measurements and techniques presented in future sessions. SQLRx.com 4 . Essential to focus any kind of analysis Remember SQL Server is only an application that runs under Windows If Windows does not perform well, neither will SQL Server .
including viewing performance counters and counter descriptions, and perfmon logs. Chapter 7, "Viewing and Troubleshooting Perfmon Logs" Provides information about how to download perfmon logs or view them locally. Chapter 8, "Monitoring the Connection Port Monitor" Provides information on the Port Monitor for Cisco Unity Connection. Alerts
Introduction – COUNTERS A counter is a register that goes through a predetermined sequence of states upon the application of clock pulses. Asynchronous counters Synchronous counters Asynchronous CountersAsAsynchronous Countersynchronous Counters (or Ripple counters) the clock signal (CLK) is only used to clock th
Bruksanvisning för bilstereo . Bruksanvisning for bilstereo . Instrukcja obsługi samochodowego odtwarzacza stereo . Operating Instructions for Car Stereo . 610-104 . SV . Bruksanvisning i original
10 tips och tricks för att lyckas med ert sap-projekt 20 SAPSANYTT 2/2015 De flesta projektledare känner säkert till Cobb’s paradox. Martin Cobb verkade som CIO för sekretariatet för Treasury Board of Canada 1995 då han ställde frågan
service i Norge och Finland drivs inom ramen för ett enskilt företag (NRK. 1 och Yleisradio), fin ns det i Sverige tre: Ett för tv (Sveriges Television , SVT ), ett för radio (Sveriges Radio , SR ) och ett för utbildnings program (Sveriges Utbildningsradio, UR, vilket till följd av sin begränsade storlek inte återfinns bland de 25 största
Hotell För hotell anges de tre klasserna A/B, C och D. Det betyder att den "normala" standarden C är acceptabel men att motiven för en högre standard är starka. Ljudklass C motsvarar de tidigare normkraven för hotell, ljudklass A/B motsvarar kraven för moderna hotell med hög standard och ljudklass D kan användas vid
LÄS NOGGRANT FÖLJANDE VILLKOR FÖR APPLE DEVELOPER PROGRAM LICENCE . Apple Developer Program License Agreement Syfte Du vill använda Apple-mjukvara (enligt definitionen nedan) för att utveckla en eller flera Applikationer (enligt definitionen nedan) för Apple-märkta produkter. . Applikationer som utvecklas för iOS-produkter, Apple .
Windows Vista and above, the uninstall process may restart the services that have loaded this module. The SuperSockets module integrates with the operating system performance counters. To view the Total Bytes Sent and Bytes Received counters, run perfmon and look for Dolphin LSP Statistics inside the Performance Object list. 5.
