Transcript Dr Greg Low CEO and Principal Mentor SQL Down Under In Azure or on-premises, app design is critical!

Dr Greg Low
CEO and Principal Mentor
SQL Down Under
In Azure or on-premises, app design is critical!
CPU and memory issues are largely
unchanged on Azure VMs
Buffer Cache
Metric
Requirements
Mix of reads vs writes
Mostly reads
Nature of writes
Mostly asynchronous
Average rows per I/O
Low
I/O size
8 to 64 KB common
I/O pattern
Mostly random
Concurrent users
High
Metric
Requirements
Mix of reads vs writes
Mostly reads
Nature of writes
Mostly asynchronous
Average rows per I/O
High (often large scans)
I/O size
Up to megabytes
I/O pattern
Mostly sequential
Concurrent users
Low
Buffer Cache
Metric
Requirements
Mix of reads vs writes
Mostly writes
Nature of writes
Mostly synchronous (low latency critical)
Average rows per I/O
Low
I/O size
8 to 64 KB common
I/O pattern
Mostly sequential
Datacenter 1
Datacenter network
…
Datacenter n
…
Region 1
…
Region 2
CPU Cores
Memory
Allocated
Bandwidth
(Mbps)
Shared
768 MB
5
1
1 x 500
Small
1
1.75 GB
100
2
2 x 500
Medium
2
3.5 GB
200
4
4 x 500
Large
4
7 GB
400
8
8 x 500
ExtraLarge
8
14 GB
800
16
16 x 500
A6
4
28 GB
1,000
8
8 x 500
A7
8
56 GB
2,000
16
16 x 500
Virtual
Machine Size
ExtraSmall
Maximum data
disks (each up
to 1 TB)
Maximum
IOPS
http://msdn.microsoft.com/en-us/library/windowsazure/dn197896.aspx
Virtual Machine
C:\
OS Disk
RAM Cache
Local Disk
Cache
D:\
Temp Disk
(Dynamic
VHD)
E:\, F:\, etc.
Data Disks
Virtual Machine
C:\
OS Disk
RAM Cache
Local Disk
Cache
D:\
Temp Disk
(Dynamic
VHD)
E:\, F:\, etc.
Data Disks
Virtual Machine
C:\
OS Disk
RAM Cache
Local Disk
Cache
D:\
Temp Disk
(Dynamic
VHD)
E:\, F:\, etc.
Data Disks
Virtual Machine
C:\
OS Disk
RAM Cache
Local Disk
Cache
D:\
Temp Disk
(Dynamic
VHD)
E:\, F:\, etc.
Data Disks
Storage Location Service
LB
LB
Front-ends
Partition Layer
Stream Layer
Intra-stamp replication
Storage Stamp
Front-ends
Geo-replication
Partition Layer
Stream Layer
Intra-stamp replication
Storage Stamp
Cache size
based
on VM size
Disk type
OS disk
Data disks
Temporary disk
Read Only
Supported
Supported (up to 4)
N/A
Read Write
Default
Supported (up to 4)
None (disabled)
Not supported
Default
Random I/O
(8KB Pages)
Sequential I/O
(64KB Extents)
Sequential I/O
(256KB Blocks)
Reads
Writes
Reads
Writes
Reads
Writes
IOPS
500
500
500
300
300
300
Bandwidth
4 MB/s 4 MB/s
30 MB/s
20 MB/s
70 MB/s
70 MB/s
4 disks
Random I/O (8KB Pages)
Sequential I/O (64KB Extents)
Sequential I/O (256KB Blocks)
Reads
Writes
Reads
Writes
Reads
Writes
2000
2000
2000
1300
700
1100
Bandwidth
20 MB/s
20 MB/s
120 MB/s
80 MB/s
170 MB/s
270 MB/s
8 disks
Random I/O (8KB Pages)
IOPS
IOPS
Bandwidth
16 disks
IOPS
Bandwidth
Sequential I/O (64KB Extents)
Sequential I/O (256KB Blocks)
Reads
Writes
Reads
Writes
Reads
Writes
4000
4000
2500
2600
700
2200
30 MB/s
30 MB/s
150 MB/s
160 MB/s
170 MB/s
550 MB/s
Random I/O (8KB Pages)
Sequential I/O (64KB Extents)
Sequential I/O (256KB Blocks)
Reads
Writes
Reads
Writes
Reads
Writes
8000
8000
2500
5000
700
2400
60 MB/s
60 MB/s
150 MB/s
300 MB/s
170 MB/s
600 MB/s
Query Performance with Data Compression
1000000
900000
700000
150000
600000
500000
100000
400000
NONE
CPU Time
Elapsed Time
PAGE
Logical Reads
Physical Reads (+RA)
Reads
800000
Time (ms)
200000
CPU Time (%), Throughput (Business Transactions/sec)
250000
OLTP Throughput and CPU Usage with Data
Compression
70
60
50
40
30
20
10
0
NONE
PAGE
Throughput
CPU Time (%)
Impact of Instant File Initialization
60
50
Time (minutes)
40
30
20
10
0
Create 100 GB database
Without Instant File Initialization
Restore 100 GB database
With Instant File Initialization
tempDB IO best practices
Test random 8K reads/writes
sqlio -kW -s300 -frandom -o32 -b8 -LS -Fparam.txt
sqlio -kR -s300 -frandom -o32 -b8 -LS -Fparam.txt
Target
Instance level waits
Expensive queries
DMV
sys.dm_os_wait_stats
sys.dm_exec_query_stats
I/O consumption and
characteristics
Executing requests
Memory object contention
sys.dm_io_virtual_file_stats
sys.dm_exec_requests
sys.dm_os_spinlock_stats
Logical Disk Counter
Disk reads / second
Disk writes / second
Average disk sec / read
Average disk sec / write
Average disk bytes / read
Average disk bytes / write
Average / current disk queue
length
Disk read bytes/sec
Disk write bytes/sec
Metric
IOPs
Latency
Block size
Details
Number of I/O operations per second
Consider adding more data disks in line with your IOPS
requirements.
Time taken per I/O operation
Variation is common but look at averages over time
Average size of I/O operations
Larger I/O operations tend to have higher latency (very common in
BACKUP/RESTORE)
Outstanding
Not very relevant for Azure VM
IOPS
Total volume of data
Throughput Larger block scans should be able to heavily utilize connection
bandwidth
SQL Server in Windows Azure Virtual Machines
SQL Server HA/DR on IaaS
Windows Azure’s Flat Network Storage and 2012 Scalability Targets
Windows Azure Storage: A Highly Available Cloud Storage Service with Strong Consistency
Erasure Coding in Windows Azure Storage
SQL Server Backup and Restore with Windows Azure Blob Storage Service
Analyzing I/O Characteristics and Sizing Storage Systems for SQL Server Database Applications
Compilation of SQL Server TempDB IO Best Practices
Windows Azure SQL Database and SQL Server -- Performance and Scalability Compared and Contrasted
YOUR OWN
QR CODE
GOES HERE
Develop & Test on cloud VMs at no additional cost!
Activate your MSDN Benefit & try it by 9/30
You could win* an Aston Martin V8 Vantage!
Redeem your benefit today!
http://bit.ly/140upRt
[email protected]
http://sqlblog.com/blogs/greg_low
http://www.sqldownunder.com
http://channel9.msdn.com/Events/TechEd/Australia/2013
http://www.microsoftvirtualacademy.com/
http://technet.microsoft.com/en-au/
http://msdn.microsoft.com/en-au/
•
Sign up for a Free Azure Trial:
http://www.windowsazure.com/en-us/pricing/free-trial/
•
Activate your Azure MSDN Benefits:
http://msdn.microsoft.com/dn369243