Transcript Intro_to_IO_Monitoring_-_Counters_That_Count_

Introduction to Monitoring I/O for SQL Server:
The Counters That Count
Jimmy May, MCM
SQL Server Solutions Architect
[email protected]
@aspiringgeek
1
Data Propulsion Laboratory — DPL
Copyright © 2015 SanDisk. All rights reserved.
2
C:\>sqlservr.exe /faster
Copyright © 2015 SanDisk. All rights reserved.
3
Forward Looking Statements - all
During our meeting today, we may make forward-looking statements.
Any statement that refers to expectations, projections or other characterizations of future events or circumstances is a
forward-looking statement, including those relating to market position, market growth, product sales, industry trends,
supply chain, future memory technology, production capacity, production costs, technology transitions, construction
schedules, production starts, and future products. This presentation contains information from third parties, which
reflect their projections as of the date of issuance. Actual results may differ materially from those expressed in these
forward-looking statements due to factors detailed under the caption “Risk Factors” and elsewhere in the documents
we file from time to time with the SEC, including our annual and quarterly reports. We undertake no obligation to
update these forward-looking statements, which speak only as of the date hereof.
©2014 SanDisk Corporation. All rights reserved. SanDisk is a trademark of SanDisk Corporation. Lightning, Lightning Eco, Lightning Ascend, Lightning Ultra, Optimus, Optimus Eco, Optimus Ascend,
Optimus Ultra, Optimus Extreme, CloudSpeed, CloudSpeed Eco, CloudSpeed Ascend, CloudSpeed Ultra, CloudSpeed Extreme, FlashSoft, ZetaScale, Guardian Technology, FlashGuard, DataGuard,
EverGuard, SanDisk ION Accelerator, Fusion ioMemory, Fusion ioSphere, Fusion ioTurbine, Fusion ioVDI, and others are trademarks of SanDisk Enterprise IP LLC. Other brand names mentioned herein are
for identification purposes only and may be the trademarks of their respective holder(s).
4
Bio
Jimmy May, MCM
• SanDisk SQL Server Solutions Architect
Formerly:
• Sr. Program Manager, SQL CAT
• SQL Server Customer Advisory Team
• MSIT Principal Architect: Database
Microsoft Certified Master: SQL Server (2009)
MS IT Gold Star Recipient (2008)
Microsoft Oracle Center of Excellence (2008)
SQL Server MVP Nominee (2006)
Indiana Windows User Group www.iwug.net
• Founder & Board of Directors
Indianapolis Professional Association for SQL Server www.indypass.org
• Founder & Member of Executive Committee
SQL Server Pros: Founder & Visionary-in-Chief
SQL Innovator Award Recipient (2006)
• Contest sponsored in part by Microsoft
Formerly Chief Database Architect for high-throughput OLTP VLDB at
ExactTarget (recent IPO)
Senior Database Administrator for OpenGlobe/Escient
www.twitter.com/aspiringgeek [email protected]
http://sqlblog.com/blogs/jimmy_may
[email protected]
5
Intro to I/O Monitoring
6
What if I told you...
...the only good I/O is the one
for which you do not ask?
9
The best I/O is the one you don’t have to do.
—Gene Amdahl, author of Amdahl's Law
10
SQL Server I/O Sizes
11
SQL I/O: Common Read/Write Sizes

512B – 60KB: Log File Sequential Writes
–
–

Very small block to small block
Do not randomize by intermingling with other workloads!
8KB: Data File Random Reads
–
Multiples thereof up to 64KB

64KB: Data File Reads & fragmented scans

1024KB (Enterprise Edition) / 512KB (Standard Edition) Readahead
–
Data file reads (sequential row store scans, columnstore)

Up to 256KB: Checkpoint Writes (one thread per NUMA node)

Pivots
–
Small block vs. Big block
–
Reads vs. Writes
–
Random vs. Sequential
12
Demo: SQL Server I/O Sizes
13
May’s I/O Mantra:
Requesting & Monitoring Storage
X capacity (GB)
at Y IOPs (transfers/sec)
at <=10ms latency for OLTP data files
at Z throughput (MB/sec)
at <=30ms latency for DW data files
at 0ms-2ms latency for log files
14
I/O Metrics: PerfMon Counters That Count
Performance Monitor: Logical Disk

–
–
–


Latency
Measured in milliseconds (ms)
Measured in microseconds (µs)
Performance Monitor: Logical Disk
• Avg. Disk sec/Transfer
• Avg. Disk sec/Read
• Avg. Disk sec/Write
Transfer Size
–

Disk Queue Length
–
IOPS
• Disk Transfers/sec
• Disk Read/sec
• Disk Writes/sec

Throughput
• Disk Bytes/sec
• Disk Read Bytes/sec
• Disk Write Bytes/sec
Measured in KB
• Avg. Disk Bytes/Transfer
• Avg. Disk Read Bytes/Transfer
• Avg. Disk Write Bytes/Transfer
Unitless
• Current Disk Queue Length
• Avg. Disk Queue Length
• Avg. Disk Read Queue Length
• Avg. Disk Write Queue Length
Capacity
–
–
The easy one!
Measured in MB/GB/TB
• % Free Space
• Free Megabytes
15
Perfmon Counters – Explained
Inspired by Thomas Kejser,
SQL CAT, ex-Fusion-io CTO
64
KB
2
3
8
KB
Avg Disk Bytes /
Transfer
1
Current Disk Queue Length = n
”in-flight” I/O
4
5
6
7
½
B
8
KB
8
KB
8
9
10
64
KB
64
KB
½
B
n
Transfers
Disk Bytes / sec = Throughput
152 KB
Disk Transfers/sec = IOPs
Time (sec)
T=0
T=1
Avg. Disk sec/Transfer =
Average amount of time within the unit of measure the I/O takes to
complete (Latency)
16
Perfmon
17
Windows Performance Monitor (perfmon)

Microsoft Management Console (MMC) snap-in that provides tools
for analyzing system performance

From a single console:
–
Monitor application and hardware performance in real time
–
Customize what data collected in logs
–
Define thresholds for alerts and automatic actions
–
Generate reports
–
View past performance data
18
PerfMon

perfmon.exe

Monitor real-time or analyse historical info

Hierarchical Collection: Performance Object > Counter [> Instance]

–
Processor > % Processor Time > _Total
–
Logical Disk Object > Avg. Disk sec/Write > D:
–
SQLServer:Buffer Manager > Page Life Expectancy
All are generically referred to as counters
19
Real-time: Adding Counters
View Current Activity (Ctrl+T)
Add Counters (Ctrl+N)
Confirm Computer
Select Performance Object(s)
& Counter(s)
Select Instance(s)
Click OK
20
Historical Data: Adding Counters from Log Files

View Log Data (Ctrl+L)

Click Log Files

Click Add...

Select one or more .blg files

Click Open

Then add counters as
previously instructed
21
Logman for I/O
::create perfmon log called PerfLogIO
call Logman create counter "PerfLogIOdemoChi" -si 05 -v MMddHHmm -max 512 -o
"C:\perflogs\PerfLogIOdemo" -c "\Processor(*)\*" "\Memory(*)\*"
"\LogicalDisk(*)\*"
::management
logman start PerfLogIO
logman stop PerfLogIO
22
Logman for SQL Server 2016 (default instance)
call Logman create counter "dbPerfLogs" -si 05 -v MMddHHmm -max 512 -o
"C:\perflogs\dbPerfLogAGs-13" -c "\LogicalDisk(*)\*" "\Process(*)\*"
"\Processor(*)\*" "\System\*" "\Network Interface(*)\*" "\Memory(*)\*"
"\Paging File(*)\*" "\SQLServer:Access Methods(*)\*" "\SQLServer:Buffer
Manager(*)\*" "\SQLServer:Databases(*)\*" "\SQLServer:Exec Statistics(*)\*"
"\SQLServer:General Statistics(*)\*" "\SQLServer:Latches(*)\*"
"\SQLServer:Locks(*)\*" "\SQLServer:Memory Manager(*)\*" "\SQLServer:Plan
Cache(*)\*" "\SQLServer:SQL Errors(*)\*" "\SQLServer:SQL Statistics(*)\*"
"\SQLServer:Wait Statistics(*)\*" "\SQLServer:Buffer Partition(*)\*"
"\SQLServer:Buffer Node(*)\*" "\SQLServer:Columnstore (*)\*"
"\SQLServer:Availability Replica(*)\*" "\SQLServer:Database Replica(*)\*"
"SQLServer:Database Mirroring(*)\*" "\XTP Transactions(*)\*“
For named instances, replace \SQLServer: with \<instance name>:, e.g.,
"\MSSQL$SQL2014SP1:Access Methods(*)\*"
23
Demo: Logman
24
Perfmon References
PerfMon Objects, Counters, Thresholds, & Utilities for SQL Server
http://blogs.msdn.com/b/jimmymay/archive/2008/10/15/perfmon-objects-counters-thresholdsutilities-for-sql-server.aspx
Customizing the Default Counters for Performance Monitor
www.sqlskills.com/blogs/erin/customizing-the-default-counters-for-performance-monitor
Performance Monitor Tips and Tricks with John Rodriguez
http://blogs.technet.com/b/neiljohn/archive/2012/01/16/performance-monitor-tips-and-tricks-withjohn-rodriguez.aspx
25
Related Tools References

Excellent hands-on intros
–
Ask the Performance Team blog:
Two Minute Drills
logman.exe
http://blogs.technet.com/b/askperf/archive/2008/05/13/two-minute-drill-logman-exe.aspx
typeperf.exe
http://blogs.technet.com/b/askperf/archive/2009/05/12/two-minute-drill-typeperf.aspx
relog.exe
http://blogs.technet.com/b/askperf/archive/2008/05/20/two-minute-drill-relog-exe.aspx
26
Virtual File Stats
27
Virtual File Stats
sys.dm_io_virtual_file_stats (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms190326.aspx

Syntax

Usage
28
VFS: Column Names

database_id

file_id

sample_ms

num_of_reads

num_of_writes

num_of_bytes_read

num_of_bytes_written

io_stall_read_ms

io_stall_write_ms

io_stall_queued_read_ms

io_stall_queued_write_ms io_stall

size_on_disk_bytes

file_handle
29
Virtual File Stats: Derived Columns

virtual file latency: stalls ÷ I/O count
–
–
–

avg bytes per I/O (transfer size): bytes ÷ I/O count
–
–
–

total
read
write
total
read
write
drive letter
–
note: doesn’t account for mount points
db_name
 physical file name (via sys.master_files)
 Filter by

–
–
–
db_id
db_name
type_desc = 'LOG', 'DATA' (via sys.master_files)
30
Virtual File Stats à la DMV All-Stars
31
Wait Stats
32
Wait Stats
sys.dm_os_wait_stats (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms179984.aspx

Usage (trivial, not recommended)

Usage (improved)
33
Wait Stats: Columns & Derived Columns

wait_type

waiting_tasks_count

wait_time_ms

max_wait_time_ms

signal_wait_time_ms

resource_wait_time_ms

signal_wait_time_per_wait

resource_wait_time_per_wait
34
Wait Stats: Benign Waits
Wait statistics, or please tell me where it hurts
Paul Randal
@PaulRandal
35
I/O-related Wait Stats

PAGEIOLATCH_xx
Disk-to-memory transfers
– PAGELATCH_SH: Most commonly reading/writing data file pages
– PAGELATCH_EX: Typically PFS or SGAM contention or last-page-insert
–

WRITELOG
–

ASYNC_IO_COMPLETION & BACKUP_IO
–

Waiting for log buffer to flush to disk
Typically (not always) related to backup
IO_COMPLETION, WRITE_COMPLETION
–
Typically not top waits
36
References

Download from TechNet:
Diskspd, a Robust Storage Testing Tool, Now Publically Available
https://gallery.technet.microsoft.com/DiskSpd-a-robust-storage-6cd2f223

Drum Roll, Please...The Debut of The SQL DMV All-Stars Dream Team!
http://blogs.msdn.com/b/jimmymay/archive/2008/10/30/drum-roll-please-the-debut-of-the-sql-dmv-all-stars-dream-team.aspx
Jimmy May
@AspiringGeek

Revised File & Wait Statistics Procedures
http://sqlblog.com/blogs/andrew_kelly/archive/2015/09/02/revised-file-wait-statistics-procedures.aspx
Andrew Kelley
@GunneyK

Wait statistics, or please tell me where it hurts
http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts
Paul Randal
@PaulRandal

Anatomy of an I/O Tuning Exercise
http://www.sqlsaturday.com/446/Sessions/Details.aspx?sid=40420
Theresa Iserman
@TheresaIserman
37
Demo: Wait Stats & Virtual Files Stats
38
I/O Subsystem Validation & Testing
39
Demo: DiskSpd
40
References

Download from TechNet:
Diskspd, a Robust Storage Testing Tool, Now Publically Available
https://gallery.technet.microsoft.com/DiskSpd-a-robust-storage-6cd2f223

Storage Benchmarking with DiskSpd
www.davidklee.net/2015/04/01/storage-benchmarking-with-diskspd
David Klee, MVP @kleegeek

Automating Storage Benchmarking with DiskSpd and PowerShell
http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts
David Klee, MVP @kleegeek
Michael Fall @mike_fal

DiskSpd, PowerShell and storage performance: measuring IOPs, throughput
and latency for both local disks and SMB file shares
http://blogs.technet.com/b/josebda/archive/2014/10/13/diskspd-powershell-and-storage-performance-measuring-iopsthroughput-and-latency-for-both-local-disks-and-smb-file-shares.aspx
Jose Berreto, MCM
@JoseBerreto
41
Thank You!
42