http://go.microsoft.com/fwlink/?Li nkId=306266 SQL Performance considerations Platform characteristics Best practices Monitoring Troubleshooting 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 SQL Performance considerations Platform characteristics Best practices Monitoring Troubleshooting 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 SQL Performance considerations Platform characteristics Best practices Monitoring Troubleshooting 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 Datacenter 1 Datacenter network … Datacenter n … Region 1 … Region 2 Roles Instances Cloud Service is a management, configuration, security, networking and service model boundary Stateless Roles : Web/Worker Role – requires 2 or more instances Persistent Roles : Virtual Machine can work with single instance Port Forwarding Public Port Local Port Protocol (TCP/UDP) Name Load Balanced Sets Public Port Local Port Protocol (TCP/UDP) Name Single Public IP Per Cloud Service Custom Load Balancer Health Probes -Health check with probe timeouts, HTTP based probing, allowing granular control of health checks Virtual Machine C:\ OS Disk RAM Cache Local Disk Cache D:\ Temporary Disk Dynamic VHD E:\, F:\, etc. Data Disks Memory Maximum Disk Space Allocated data disks for Virtual Bandwidth (1 TB Machines (Mbps) each) Maximum IOPS (500 maximum per disk) ExtraSmall Shared 768 MB 20 GB 5 1 1x500 Small 1 1.75 GB 70 GB 100 2 2x500 Medium 2 3.5 GB 135 GB 200 4 4x500 Large 4 7 GB 285 GB 400 8 8x500 ExtraLarge 8 14 GB 605 GB 800 16 16x500 A6 4 28 GB 285 GB 1,000 8 8x500 A7 8 56 GB 605 GB 2,000 16 16x500 Virtual Machine Size CPU Cores http://msdn.microsoft.com/en-us/library/windowsazure/dn197896.aspx Storage Location Service LB LB Front-ends Front-ends Partition Layer Stream Layer Geo-replication Partition Layer Stream Layer 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. Non-write-through writes persisted to local cache first. For SQL Server, writes are persisted to WA storage because it uses Writethrough - 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 Supported Default mode Supported (up to 4) Supported (up to 4) Implemented using local attached storage None (disabled) Not supported Default mode 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 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 Set Blob Service Properties (REST API) Windows Azure Storage Metrics: Using Metrics to Track Storage Usage technet SQLIO • • • • • • • • • • • • • 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 • • • 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. Performance Guidance for SQL Server in Windows Azure Virtual Machines Performance Guidance for SQL Server in Windows Azure Virtual Machines TechEd EMEA 2013 Sessions MDC-B212: Infrastructure Services on Windows Azure: Virtual Machines and Virtual Networks with Mark Russinovich Today 10:15AM, Hall 7.2 SQL Server in Windows Azure Virtual Machines SQL Server HA/DR on IaaS DBI-B206: Private Cloud – Better Together: Microsoft SQL Server 2012, Windows Server 2012 and System Center 2012 Today 5PM, N104 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-B329: Overview, Best Practices and Lessons Learned on Microsoft SQL Server in Windows Azure Virtual Machines June 27 10:15AM, N102 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 WAD-B402: Windows Azure Internals with Mark Russinovich Today 3:15PM, Hall 7.2 DBI-B306: Microsoft SQL Server High Availability and Disaster Recovery on Windows Azure June 27 3:15PM, N104 http://channel9.msdn.com/Events/TechEd www.microsoft.com/learning http://microsoft.com/technet http://microsoft.com/msdn