DBI 314 Logical Server SQL Azure Gateway Service

DBI 314

Logical Server SQL Azure Gateway Service Machine 4 Machine 5 Machine 6 SQL Instance SQL DB User DB 1 User DB 2 SQL DB User DB 3 User DB 4 Scalability and Availability: Fabric, Failover, Replication, and Load balancing User DB 1 User DB 2 User DB 3 User DB 4

North Europe North Central US West Europe South Central US East Asia Southeast Asia Regional hosting locations 200 ms Latency from 2 regional hosting locations Hosting locations within 100 ms of the customer At least one hosting location can be reached within 100 ms, but not two No points to test from or greater than 200 ms latency

SQL Azure

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

FromTo (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

Reactive Load Balancer

%d

Code = Why am I throttled? 131075 Step 1: Reasons = Code/256 = 512 Step 2: Convert Reasons to binary 512 => 100000(2) Step 3: Group in sets of 2 digits from right to left: 10|00|00(2) 100000 Resource Code 8 7 6 5 4 3 2 1 0 Throttling Type How bad is it? Throttling Impact = Code % 4 If remainder is 0: No throttling 1: Reject Update/Insert 2: Reject All Writes 3: Reject all

Solution: Yes. Load balancer moves A or B away from this machine Throttling Trigger: B Throttling Victim: B Fairness: Fair to throttle B - B uses more CPU than A - B triggered throttling on the machine

Solution: Yes. Load balancer moves A or B away from this machine Throttling Trigger: B Throttling Victim: A Fairness: Not quite fair to throttle A - B triggered throttling on the machine

Solution: No. A will get throttle anywhere it is placed. A exceeds a machine’s total CPU Throttling Trigger: A Throttling Victim: A Fairness: 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

select * from sys. all_views where name like '%dm%'

select sum(reserved_page_count)*8. 0/1024 AS [Storage_in_MB] from sys. dm_db_partition_stats

select highest_cpu_queries. total_worker_time, q. text AS [Query_Text], highest_cpu_queries. plan_handle from (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

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

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

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 available Follow the @SQLServer Twitter account to watch for updates Try the new SQL Server Mission Critical Bare. Metal Hand’s on-Labs 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® Programmability • Microsoft® SQL Server® Mission Critical • Microsoft® SQL Server® Optimization and Scalability • Microsoft® SQL Server® Data Warehousing • Microsoft® SQL Server® Data Integration

Resources Connect. Share. Discuss. http: //northamerica. msteched. com Sessions On-Demand & Community Microsoft Certification & Training Resources www. microsoft. com/teched www. microsoft. com/learning Resources for IT Professionals Resources for Developers http: //microsoft. com/technet http: //microsoft. com/msdn

To access more details on this session, capture this TAG