http://go.microsoft.com/fwlink/?Li nkId=306266 Key Perf considerations & bottlenecks Windows Azure VM characteristics Troubleshooting Monitoring Best practices Metric OLTP DW Log Read/Write mix Mostly reads, smaller # of rows at a time Scan intensive, large portions of data at.
Download ReportTranscript http://go.microsoft.com/fwlink/?Li nkId=306266 Key Perf considerations & bottlenecks Windows Azure VM characteristics Troubleshooting Monitoring Best practices Metric OLTP DW Log Read/Write mix Mostly reads, smaller # of rows at a time Scan intensive, large portions of data at.
http://go.microsoft.com/fwlink/?Li nkId=306266 Key Perf considerations & bottlenecks Windows Azure VM characteristics Troubleshooting Monitoring Best practices Metric OLTP DW Log Read/Write mix Mostly reads, smaller # of rows at a time Scan intensive, large portions of data at a time, bulk loading Mostly writes, requires low latency IO size and pattern Between 8 and 64K, mostly random 1 64KB read per 8 512KB reads, Mostly sequential MB/s a critical metric Highly sequential # users high low n/a Architecture Latency Variability Configuration options Virtual Machine size Network bandwidth Disk types and configurations Disk cache settings Memory Maximum Disk Space Disk Space Allocated data disks for Cloud for Virtual Bandwidth (1 TB Services Machines (Mbps) each) Maximum IOPS (500 maximum per disk) ExtraSmall Shared 768 MB 19 GB 20 GB 5 1 1x500 Small 1 1.75 GB 224 GB 70 GB 100 2 2x500 Medium 2 3.5 GB 489 GB 135 GB 200 4 4x500 Large 4 7 GB 999 GB 285 GB 400 8 8x500 ExtraLarge 8 14 GB 2,039 GB 605 GB 800 16 16x500 A6 4 28 GB 999 GB 285 GB 1,000 8 8x500 A7 8 56 GB 2,039 GB 605 GB 2,000 16 16x500 Virtual Machine Size CPU Cores http://msdn.microsoft.com/en-us/library/windowsazure/dn197896.aspx Geo-replication Intra-stamp replication Intra-stamp replication Storage Stamp Storage Stamp • 2-tier cache Recently accessed data stored in host RAM cache - space shared by all VMs on machine Less recently accessed data stored on local hard disks of physical machine. • Reserved cache space for VM “OS Disk” and “Data Disks” based on the VM size Read Only: All requests cached for future reads. All writes persisted directly to Windows Azure Storage Read Write: Reads and writes cached for future reads. Regular writes persisted to local cache. If Write-through, Forced Unit Access (FUA) or flush is asserted, write commits directly to Windows Azure Storage - Lowest disk latency for light workloads None (disabled): Bypasses cache. All disk transfers persisted to Windows Azure Storage - Highest I/O rate for I/O intensive workloads - Also consider TX cost Disk type OS disk Data disk Temporary disk Read Only Read Write None (disabled) Supported Default mode Not supported Supported (up to 4) Supported (up to 4) Default mode Implemented using local attached storage Key tools Storage Analytics VM Dashboard SQL Perf counters SQLIO ::Test random 8K reads/writes sqlio -kW -s300 -frandom -o32 -b8 -LS -Fparam.txt sqlio -kR -s300 -frandom -o32 -b8 -LS -Fparam.txt sys.dm_os_wait_stats sys.dm_exec_query_stats sys.dm_io_virtual_file_stats sys.dm_exec_requests 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 Typical storage term IOPS Latency Suggested actions in Windows Azure virtual machine environment Measure the number of I/O’s per second. Consider adding more data disks in line with your IOPS requirements. Measure disk latency. Note: Numbers might vary; look at averages over time. Block size Measure the size of I/O’s being issued. Note: Larger I/O’s tend to have higher latency, such as those associated with BACKUP/RESTORE. Outstanding or waiting IOPS Provides insight into the applications I/O pattern. Throughput or aggregate throughput Measure of total disk throughput. Note: Ideally, larger block scans should be able to heavily utilize connection bandwidth (for example, your throughput can be higher with a smaller number of larger IOPS). Set Blob Service Properties (REST API) Windows Azure Storage Metrics: Using Metrics to Track Storage Usage • • • • • • • • • • • Typical User/test characteristics • • • • • Number of concurrent users Average/Max request execution time Number of web servers Ramp up period, test method Start and end time of test • Classic performance factors High-level troubleshooting steps Common issues Plan change/choice issues Software/hardware configuration Multi-user operations and blocking Locking & latching Checkpoint & system operations Define KPIs to monitor resource utilization Monitor KPIs to track utilization over time Examine trends and patterns as workload increases Monitor DMVs to understand resource contention/waits Issue KPIs To Monitor Actions to Consider CPU at or near 80% % Processor Time (_Total) SOS_SCHEDULER_YIELD waits Increase instance size Identify top consuming queries and tune Load balance (e.g. move DB to another instance) Near I/O capacity limits or IO Latency Increases Memory resource pressure Average disk reads per second Average disk writes per second Disk reads per second Disk writes per second io_virtual_file_stats PAGEIOLATCH waits SQL Server: Buffer Manager\Page Life Expectancy Memory: Available Bytes Memory: Pages per second SQL Server: Buffer Manager\Page Life Expectancy Process: Working Set (for SQL Server) RESOURCE_SEMAPHORE waits Check Page Life Expectancy counter, for mem pressure. Increase instance size Identify which DB and log files have I/O bottleneck Add more data disks and separate data files if near IOPS limits per disk Tune queries to reduce reads and writes Consider enabling row or page compression Check max server memory setting for SQL Server. Use high memory instance Identify SQL component (such as, CLR, high memory grants for app queries, et.), tune appropriately. Network Latency Disk configuration Tempdb placement IO optimization 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 IOPS 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) Sequential I/O (64KB Extents) Sequential I/O (256KB Blocks) Reads Writes Reads Writes Reads Writes IOPS 4000 4000 2500 2600 700 2200 Bandwidth 30 MB/s 30 MB/s 150 MB/s 160 MB/s 170 MB/s 550 MB/s 16 disks 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 150 MB/s 300 MB/s 170 MB/s 600 MB/s IOPS Bandwidth 60 MB/s tempDB IO best practices Query Performance with Data Compression 250000 OLTP Throughput and CPU Usage with Data Compression 1000000 900000 700000 150000 600000 500000 100000 400000 NONE CPU Time Elapsed Time PAGE Logical Reads Reads 800000 Time (ms) 200000 CPU Time (%), Throughput (Business Transactions/sec) 70 60 50 40 30 20 10 0 NONE Physical Reads (+RA) Throughput PAGE CPU Time (%) Impact of Instant File Initialization 60 Time (minutes) 50 40 30 20 10 0 Create 100 GB database Without Instant File Initialization Restore 100 GB database With Instant File Initialization adaptive partitioning and load balancing adjusts to workload changes Geo-replication Intra-stamp replication Intra-stamp replication Performance Guidance for SQL Server in Windows Azure Virtual Machines Performance Guidance for SQL Server in Windows Azure Virtual Machines TechEd 2013 Sessions DBI-B201: Lap Around Windows Azure SQL Database and Microsoft SQL Server in Windows Azure Virtual Machines SQL Server in Windows Azure Virtual Machines SQL Server HA/DR on IaaS MDC-B406: Achieve High Availability with Microsoft SQL Server on Windows Azure Virtual Machines 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 DBI-B316: Running BI Workloads on Windows Azure Virtual Machines DBI-B206: Private Cloud – Better Together: Microsoft SQL Server 2012, Windows Server 2012 and System Center 2012 DBI-B306: Microsoft SQL Server High Availability and Disaster Recovery on Windows Azure DBI-B329: Overview, Best Practices and Lessons Learned on Microsoft SQL Server in Windows Azure Virtual Machines MDC-B361: Best Practices from Real Customers: Deploying to Windows Azure Infrastructure Services (IaaS) WAD-B402: Windows Azure Internals 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 Windows Azure mva http://channel9.msdn.com/Events/TechEd www.microsoft.com/learning http://microsoft.com/technet http://microsoft.com/msdn