Microsoft SQL Azure Performance Considerations and Troubleshooting

Henry ZhangSenior Program ManagerMicrosoft

DBI314

Objectives

Performance Consideration beyond the DatabaseNetwork LatencyLoad Balancer and Cluster-wide ManagementThrottling Service Drill downDMVs and E2E MonitoringPerf Baseline Facilitates Meaningful ComparisonLeverage Elasticity

Review - SQL Azure Architecture

Shared infrastructure at SQL database and belowMassively distributed cluster w/ commodity hardwareScalable HA technology provides the glue

Each SQL Azure DB has 3 replicasAutomatic replication and failover

Gateway Service forwards TDS requests

Machine 5

SQL Instance

SQL DBUserDB1

UserDB2

UserDB3

UserDB4

SQL Azure Gateway Service

Machine 6

SQL Instance

SQL DBUserDB1

UserDB2

UserDB3

UserDB4

Machine 4

SQL Instance

SQL DB UserDB1

UserDB2

UserDB3

UserDB4

Scalability and Availability: Fabric, Failover, Replication, and Load balancing

Logical Server

Microsoft Azure Data Centers World Wide

Regional hosting locations

200ms Latency from 2 regional hosting locations

South Central US

North Central US

North Europe

Southeast Asia

West Europe

Hosting locations within 100ms of the customer

At least one hosting location can be reached within 100ms, but not two

No points to test from or greater than 200ms latency

East Asia

Network Latency

Network Latency: User and ApplicationApplication and SQL Azure DB

Perceived performance: Response Time = 2x(Latency_1 + Latency_2) + Query_Exec_Time

OptimizationMinimize latency 1: select data center closest to majority of your usersMinimize latency 2: co-locate with Windows Azure application Minimize network round trips

SQLAzure

Latency_1 Latency_2

Blog Post: Testing Client Latency to SQL Azurehttp://blogs.msdn.com/b/sqlazure/archive/2010/05/27/10016392.aspx

SQLAzure

Azure Cross Datacenter Latencies

From\To (ms) North-central US South-central US North Europe West Europe East Asia South-East Asia

North-central US 4.2 35.3 97 103.6 190.9 219.7

South-central US 37.8 2.3 111.2 117.5 190 218.6

North Europe 99.8 111.8 2 20.9 283.3 312.3

West Europe 107.5 118.6 21.1 2.3 291.8 320.8

East Asia 194.5 190.8 284.2 291.7 1.6 32.6

South-East Asia 223.1 219.2 312.9 320.1 32.2 1.9

Resource Management and Multitenancy

Resource shared on machine with neighbor databasesCPU, memory, data/log spindlesTempDB, worker threads, networkNeighbors: size and activity can affect your DB

Multi-tenancy management provided in SQL AzureLoad Balancer Throttling Service

Load Balancer

Balance resource utilization across all machines

Minimize overloaded machines and reduce throttling

Swap vs. move mechanisms

Runs periodically, solves long term imbalance for cluster

Reactive Load Balancer solves short term spikesReact to spikes before the next regular LB runAlleviate high throttling occurrences on hot machinesLocal optimization, fast solution

Resource Throttling in SQL Azure

Throttling ServiceProtect a machine from sustained high usage of system resourcesEvaluate actual resource usage vs. safe thresholds real-timeThrottle the busiest DBs first (soft throttle)Throttle every DB if necessary (hard throttle)

Throttling show as connection error 40501“The service is currently busy. Retry the request after 10 seconds. Code: %d.”

Decode throttling code for more insight

Decoding Throttling Code

Throttling Impact = Code % 4If remainder is 0: No throttling1: Reject Update/Insert2: Reject All Writes3: Reject all

Step 1: Reasons = Code/256 = 512

Step 2: Convert Reasons to binary512 => 1000000000(2)

Step 3: Group in sets of 2 digits from right to left: 10|00|00|00|00(2)Resource Code

0: Physical Database Space1: Physical Log Space2: LogWriteIODelay3: DataReadIODelay4: CPU5: Database Size6: Internal7: SQL Worker Threads8: Internal

Throttling Type 1000000000

Resource Code 8 7 6 5 4 3 2 1 0

Throttling Type – Hard vs. Soft 00: not throttled on this resource01: soft throttled on this resource10: hard throttled on this resource

Example:Resource Code: (4) - CPU throttlingThrottling Type: (10)- Hard throttlingConclusion: CPU Hard throttling

Why am I throttled? How bad is it?

Code =

131075

Throttling – Scenario 1

Customer A using 30% CPU on a machineCustomer B kicks of load of 70% additional CPU on the same machineCustomer B gets throttled

Solution: Yes. Load balancer moves A or B away from this machineThrottling Trigger: BThrottling Victim: BFairness: Fair to throttle B

- B uses more CPU than A- B triggered throttling on the machine

Throttling – Scenario 2

Customer A using 70% CPU on a machineCustomer B kicks of load to 30% additional CPU on the same machineCustomer A gets throttled

Solution: Yes. Load balancer moves A or B away from this machineThrottling Trigger: BThrottling Victim: AFairness: Not quite fair to throttle A

- B triggered throttling on the machine

Throttling – Scenario 3

Machine has no active workloadCustomer A kicks of load to 100% CPU and gets throttled repeatedlyCustomer A gets throttled

Solution: No. A will get throttle anywhere it is placed. A exceeds a machine’s total CPU Throttling Trigger: AThrottling Victim: AFairness: Fair from system perspective but customer will not be happy =(

Customer A needs to optimize and reduce resource usage to fit within a SQL Azure machine

Get the Most out of Throttling Information

Monitor throttling reasons for your DB, find distributionUse throttling code to identify potential inefficiency in DBMay need to scale out to more than 1 DB

DMVs and Monitoring

10 Perf Related DMVs:select * from sys.all_views where name like '%dm%'

DMV data mapped to proper userDB contextWorks identical to SQL Server 2008 DMVs

Update Statistics SupportedMinimize Index FragmentationProfiler not yet, think E2E monitoringDMV Examples

DMV Example: Find Total DB Storage Used

select sum(reserved_page_count)*8.0/1024 AS

[Storage_in_MB] fromsys.dm_db_partition_stats

DMV Example: Find CPU Intensive Queries

select highest_cpu_queries.total_worker_time, q.text AS [Query_Text], highest_cpu_queries.plan_handlefrom (select top 50 qs.plan_handle, qs.total_worker_time from sys.dm_exec_query_stats qs order by qs.total_worker_time desc) as highest_cpu_queries cross apply sys.dm_exec_sql_text(plan_handle) as q order by highest_cpu_queries.total_worker_time desc

DMV Example: Find IO Intensive Queries

select top 25 (total_logical_reads/execution_count) as avg_logical_reads, (total_logical_writes/execution_count) as avg_logical_writes, (total_physical_reads/execution_count) as avg_phys_reads, Execution_count, sql_handle, plan_handle from sys.dm_exec_query_stats order by (total_logical_reads + total_logical_writes) Desc

Setting Performance Expectations

Cloud does not SOLVE perf problems

Cloud does not guarantee same perf compared to on-premHardware is differentMulti-tenancy environmentNetwork latency

Know your on-prem DB well before migrating to SQL Azure

Get a Baseline before We Compare Perf

Baselining on-prem PerformanceWhat is the on-prem hardware spec?Data size in on-prem testing?Use of DOP?Concurrent txns?How chatting is the middle-tier to the DB?Has anything changed after moving to SQL Azure?

A busy DB may exceed the hardware limits of 1 machineThink Scale out

Leveraging Elasticity for New DB Applications

Traditional Capacity Planning = Buy sufficient hardwareSQL Azure Capacity Planning = Determine number of DBs needed

Create DB = Get more resourceDrop DB = Release resourceWhen to create/drop?

Use DB Copy to Separate Read/Write WorkloadPartition Aware Middle-tierBuild 2 level Composite Key for Federation/Scale out

Customer IDMonth

Composite Key = (bigint) [customer_ID + MonthYear_Key]

Related Content

DBI 403 - Building Scalable Database Solutions Using Microsoft SQL Azure Database Federations

DBI 313 - Building Scalable Database Solutions Using Microsoft SQL Azure Database Federations

Database Platform (DAT) Resources

Try the new SQL Server Mission Critical BareMetal Hand’s on-Labs

Visit the updated website for SQL Server® Code Name “Denali” on www.microsoft.com/sqlserver and sign to be notified when the next CTP is availableFollow the @SQLServer Twitter account to watch for updates

Visit the SQL Server Product Demo Stations in the DBI Track section of the Expo/TLC Hall. Bring your questions, ideas and conversations!

• Microsoft® SQL Server® Security & Management • Microsoft® SQL Server® Optimization and Scalability• Microsoft® SQL Server® Programmability • Microsoft® SQL Server® Data Warehousing• Microsoft® SQL Server® Mission Critical • Microsoft® SQL Server® Data Integration

Resources

www.microsoft.com/teched

Sessions On-Demand & Community Microsoft Certification & Training Resources

Resources for IT Professionals Resources for Developers

www.microsoft.com/learning

http://microsoft.com/technet http://microsoft.com/msdn

Learning

http://northamerica.msteched.com

Connect. Share. Discuss.

Complete an evaluation on CommNet and enter to win!

To access more details

on this session,

capture this TAG

© 2011 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to

be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS

PRESENTATION.