Windows Azure Conference 2014 Running SQL Server on

Windows Azure Conference 2014 Running SQL Server on Windows Azure Virtual Machine Performance Considerations @vinodk_sql Windows Azure Conference 2014

Session Objectives And Takeaways Session Objective(s): Learn the performance characteristics of running SQL Server on Windows Azure VM (Iaa. S) Understand the performance best practices for running SQL Server on Windows Azure VM Key Takeaway 1 What impacts performance of SQL Server running on Windows Azure VM Key Takeaway 2 How to monitor and troubleshoot performance of SQL Server running on Windows Azure VM Windows Azure Conference 2014

Performance tuning • Analyzing performance impact – Is it a problem with my SQL application? or – Is it a problem with how I configured the Azure Iaa. S platform for my usage Key SQL Server Performance Considerations Windows Azure Iaa. S Characteristics Best Practices Monitoring Windows Azure Conference 2014

Key SQL Server Performance Considerations • KPIs – Throughput – Response time (aka latency) Metric OLTP Read/Write mix • • • Large number of small transactions Transactions are mostly similar in pattern Significant amount of reads • • Between 8 and 64 K Mostly random • • IO size and pattern # users Windows Azure Conference 2014 DW high • • • low Transaction Log Scan intensive, large portions of data at a time Bulk loading • • Mostly writes Requires low latency 1 64 KB read per 8 512 KB reads Mostly sequential MB/s a critical metric • Highly sequential n/a

Windows Azure VM Characteristics Architecture Latency Variability Configuration options Windows Azure Conference 2014

VM Configuration Options Windows Azure Conference 2014 Virtual Machine size Network bandwidth Disk types and configurations Disk cache settings

Windows Azure VM Size Virtual Machine Size CPU Cores Memory OS Disk Space Allocated Bandwidth (Mbps) Temporary Disk Space Maximum # of data disks (1 TB each) Maximum IOPS (500 maximum per disk) Extra. Small Shared 768 MB 127 GB 20 GB 5 1 1 x 500 Small 1 1. 75 GB 127 GB 70 GB 100 2 2 x 500 Medium 2 3. 5 GB 127 GB 135 GB 200 4 4 x 500 Large 4 7 GB 127 GB 285 GB 400 8 8 x 500 Extra. Large 8 14 GB 127 GB 605 GB 800 16 16 x 500 A 6 4 28 GB 127 GB 285 GB 1, 000 8 8 x 500 A 7 8 56 GB 127 GB 605 GB 2, 000 16 16 x 500 http: //msdn. microsoft. com/en-us/library/windowsazure/dn 197896. aspx Windows Azure Conference 2014

VM Disk Types & Configurations • • • OS disk (persistent) – 127 GB disk optimized for OS access patterns (e. g. boot up) Data disk (persistent) – A VHD you can attach to a VM to store app data – Up to 1 TB in size – Up to 16 disks for XL VMs Temporary local disk (non-persistent) – Used for transient/temporary data storage & OS page files – Hosted in attached disks on physical host – Cleaned up in the event of a VM failure or recycling – Physical disks shared across other VMs on same physical machine – Not recommended for user or system database files Windows Azure Conference 2014

How does VM Disk Caching work? • • Caches VM data inside physical host machine Can reduce disk I/O latency by reducing # transactions against Windows Azure Storage • 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 Windows Azure Conference 2014

What are Disk Caching Best Practices for SQL Server? OS Disk • “Read Write” (default) reduces read latency for IO intensive workloads with smaller DBs (<=10 GB) – Working set can fit in disk cache or memory, reducing blob storage IO Data disks • Cache setting depends on the IO pattern and workload intensity • Use default of “None” (disable) for higher rate of random IOs (e. g. OLTP) & higher throughput – Bypasses physical host local disks, maximizing IO rate • Consider enabling read cache for latency sensitive read heavy workloads Windows Azure Conference 2014

Best Practices Network Latency Windows Azure Conference 2014 Disk configuration Temp. DB placement IO optimization

VM network performance • • • Network latency can be higher compared to on-prem – Virtualization, security, load balancing, proximity – Reducing network round trips can have more impact compared to on-prem Consolidate “chatty” application layers on the same machine Host VMs in the same Virtual Network / Cloud Service – Allows communication via internal IP addresses (DIPs) – VMs involved in the same function should be in the same Cloud Service (example: VMs participating in an AG, or VMs in a given app tier). • Load balance multiple VMs in the same cloud service via public virtual IP addresses (for example: read workload with Always. On AG) – Use Windows Azure Virtual Network for VMs in different cloud services Windows Azure Conference 2014

Windows Azure Conference 2014

Single Data Disk Configuration • • • Recommended for <1 TB storage Acceptable performance Minimal complexity, simpler recovery Sample SQL IO Measurement tests for single disk: Random I/O (8 KB Pages) Sequential I/O (64 KB Extents) Sequential I/O (256 KB Blocks) Reads Writes IOPS 500 500 300 300 Bandwidth 4 MB/s 30 MB/s 20 MB/s 70 MB/s Windows Azure Conference 2014

Multiple Disk Configuration • • Recommended for – >1 TB DB files, OR – for higher IOPS / bandwidth requirements Two Configuration Choices – Option 1: Use Filegroups with multiple database files, and place each database file on a separate data disk • This showed the best performance in our testing • Does not help scaling transaction log throughput and bandwidth – Option 2: Create OS volume on top of multiple data disks (e. g. OS striped volume or Windows Server 2012 storage space) • Storage spaces recommended over OS striped volumes Windows Azure Conference 2014

Aggregated Measurement Samples 4 disks Random I/O (8 KB Pages) Sequential I/O (64 KB Extents) Sequential I/O (256 KB Blocks) Reads Writes IOPS 2000 1300 700 1100 Bandwidth 20 MB/s 120 MB/s 80 MB/s 170 MB/s 270 MB/s 8 disks Random I/O (8 KB Pages) Sequential I/O (64 KB Extents) Sequential I/O (256 KB Blocks) Reads Writes IOPS 4000 2500 2600 700 2200 Bandwidth 30 MB/s 150 MB/s 160 MB/s 170 MB/s 550 MB/s 16 disks Random I/O (8 KB Pages) Sequential I/O (64 KB Extents) Sequential I/O (256 KB Blocks) Reads Writes IOPS 8000 2500 5000 700 2400 Bandwidth 60 MB/s 150 MB/s 300 MB/s 170 MB/s 600 MB/s Windows Azure Conference 2014

Disk Configuration: Scaling Reads and Writes • • Spreading the I/O workload across a number of data disks benefits smaller random operations (more common in OLTP scenarios) where IOPS and bandwidth scale in a nearly linear fashion. As the I/O block size increases, for read operations, IOPS or bandwidth doesn’t linearly scale by adding more data disks. IOPS and bandwidth taper off after 4 disks. • If your workload is read intensive with analytical queries, adding more disks will not necessarily help. For write intensive workload, adding more data disks can increase performance in a nearly linear fashion. This means that you can benefit from placing each transaction log for multiple databases on a separate data disk. For large sequential I/O block sizes, writes generally scale better than reads. Windows Azure Conference 2014

Should TEMPDB go on D: drive? • • • Short answer: No Long answer: No Why? Predictable performance: OS or data disk can provide same or better performance but D: drive can be more variable, being a physical disk sharing IO with other VMs on the host. Size and performance also depends on VM size Configuration overhead: SQL Server has to recreate TEMPDB in D: if VM goes down – SQL Server service account requires Admin privileges. If stored in a separate folder this needs to be created at startup. TEMPDB can be critical to application performance – Follow temp. DB IO best practices Windows Azure Conference 2014

Windows Azure Conference 2014

Reduce IO with Instant File Initialization • • • Not default in Azure VM images Reduces IO for – Creating a DB – Restoring a DB – Adding files to a DB – Extending file size – Autogrow, etc. Add SQL service account to Perform Volume Maintenance Tasks security policy Restart SQL Server Note: Doesn’t help Transaction Log Windows Azure Conference 2014 Impact of Instant File Initialization 60 50 Time (minutes) • • 40 30 20 10 0 Create 100 GB database Restore 100 GB database Without Instant File Initialization With Instant File Initialization

Instant File Initialization – How to? Windows Azure Conference 2014

Instant File Initialization Recommendations • • • Pre-size all database files appropriately – Autogrow is insurance – Not a license for drunk driving Configure Instant File Initialization before creating database Configure Instant File Initialization before extending files. Example: TEMPDB Configure Instant File Initialization before restoring database on a new server Don’t forget to restart SQL Server after configuring Instant File Initialization Windows Azure Conference 2014

What else to consider for data disk performance? • • • Disk warm-up NTFS Allocation Unit Size ? Single vs. multiple storage accounts with a single VM – DO NOT SPREAD DATA FILES OF A SINGLE DATABASE INTO MULTIPLE STORAGE ACCOUNTS !!! • Data in different blobs not written at the same time • BLOBs that make up the stripe set could be out of sync – Instead: • Spread the data files across multiple disks to achieve higher IOPS / bandwidth – Note: a storage account has a limit of 20 K tps Windows Azure Conference 2014

Performance Monitoring & Troubleshooting Key tools Windows Azure Conference 2014 Storage Analytics VM Dashboard SQL Perf counters

Performing IO Benchmarking tests • Goal – determine IO capacity of VM configurations – SQLIO – Disk Subsystem Benchmark Tool – Performance metrics – DMVs Windows Azure Conference 2014

Windows Azure Storage Analytics Metrics • • Tracks aggregated storage usage for Blobs, Tables and Queues – Capacity – e. g. #containers, total #blobs – Requests - #requests, total ingress/egress, average E 2 E latency and server latency, total # failures by category, etc. – Access via storage account namespace https: //<accountname>. table. core. windows. net/Tables("$Metrics. Transactions. Blob") Enable in portal or using Set Blob Service Properties (REST API) – Set retention policy See Windows Azure Storage Metrics: Using Metrics to Track Storage Usage Windows Azure Conference 2014

Performance Charts on the WA Portal • • VM Dashboard Monitor tab for storage account – Enabled under the “configure” tab – VM read and write to their VHDs using Get. Blob and Put. Page commands respectively Windows Azure Conference 2014

Use Perf counters Typical SQL KPIs Typical Web App KPIs • Max val for Process(SQLServ)% Processor Time • Max val for ASP. NET Applications (_Total_)Reqests/sec • Avg val for Process(SQLServ)% Processor Time • Avg val for ASP. NET Applications (_Total_)Reqests/sec • Max val for Processor(_Total)% Processor Time • Avg val for MemoryAvailable Mbytes • Avg val for Processor(_Total)% Processor Time • Max val for SQLServer: SQL StatisticsBatch Requests/sec • Avg val for Processor(_Total)% Processor Time • Avg val for SQLServer: SQL StatisticsBatch Requests/sec • Avg val for ASP. NETRequest Wait Time • Avg val for ASP. NETRequest Execution Time • Avg val for ASP. NETRequests Queued • Avg val for ASP. NETRequests Rejected • Avg val for ASP. NETRequests Current 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 Windows Azure Conference 2014

Troubleshooting Common VM Issues 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 Windows Azure Conference 2014 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 ManagerPage Life Expectancy Memory: Available Bytes Memory: Pages per second SQL Server: Buffer ManagerPage 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.

Whitepaper Performance Guidance for SQL Server in Windows Azure Virtual Machines Published: June 2013 Download it here: http: //go. microsoft. com/fwlink/? Link Id=306266 Windows Azure Conference 2014

Conclusions • Read the Performance Guidance for SQL Server in Windows Azure Virtual Machines white paper • Plan and test for IO perf variability • Identify optimal VM size • Optimize for reduced IO and network round trips • Use Filegroups and multiple data disks for large DBs • Identify your KPIs to monitor • Revisit optimization decisions as workload changes Windows Azure Conference 2014

Windows Azure Conference 2014