Performance Tuning Microsoft Azure SQL Database

Eric D. Boyd Founder & CEO, responsiveX Microsoft Azure MVP, Insider & Advisor

About Eric D. Boyd

Founder/CEO Microsoft Azure MVP Speaker & Author @EricDBoyd EricDBoyd.com

Agenda 1) Understanding SQL Database Architecture 2) Managing Connections 3) Monitoring Performance 4) Scaling SQL Database 5) Get Started with new Service Tiers

Understanding SQL Database Architecture

Experience, Capabilities, Characteristics

Azure SQL DB is like SQL Server

• Same TDS protocol as SQL Server • Same port (1433) as SQL Server • Same tools as SQL Server • SQL Server Management Studio • • Visual Studio Third-Party Tools • Same T-SQL as SQL Server 5

Azure SQL DB is resilient

• Up to 99.99% Service Level Agreement • • • 6

Azure SQL DB is scalable

• • • 7

Azure SQL DB is multi-tenant

• Customers share physical resources • This enables you to save money when you don’t need all of the physical resources 8

Multi-Tenant Services Are Tricky

 Take What You Can Get  Policed Resources  Dedicated Resources

Web & Business Edition

• Take What You Can Get + Policed Resources • Editions are not based on performance, but based on database size • Unpredictable performance • Terminated connections 10

Getting Back to the Basics

Open connections late, close early Pool connections Limit the size and shape of the data Tune queries 11

Managing Connections

• Connections can be terminated when: • Hardware failures or server balancing occurs • • You consume too many resources, too quickly You take to long to do what you need to do • You should retry when a connection termination is a transient fault and temporary 12

Is It Transient?

13

Is It Transient?

http://msdn.microsoft.com/library/azure/dn338079.aspx

14

Don’t Reinvent The Wheel

• Recent versions of ADO.NET provides retry capability • SqlAzureExecutionStrategy 15

Handling Transient Faults in Entity Framework 6

DEMO

Don’t Reinvent The Wheel

• Recent versions of ADO.NET provides retry capability • SqlAzureExecutionStrategy • Microsoft Azure CAT and Patterns & Practices (P&P) created the Transient Fault Handling Application Block 17

Transient Fault Handling Application Block

Component

Detection Strategy Retry Strategy Retry Policy

Description

Determines when an exception is a transient fault condition Defines how often and how many times to retry when a fault is identified as a transient fault Combines a detection strategy and a retry strategy, and is used to call services that might encounter transient faults 19

Retry Strategies

Class Description

ExponentialBackoff Retries a specified number of times, exponentially delaying retries based on specified back-off parameters FixedInterval Incremental Retries a specified number of times with a fixed interval between each retry Retries a specified number of times with an incrementing interval between each retry 20

Transient Fault Handling Application Block

DEMO

Monitoring SQL Database

• Connections • sys.database_connection_stats – database connections • sys.event_log – connectivity events 22

Connection Events

DEMO

Monitoring SQL Database

• Dynamic Management Objects – views and functions • Monitor and Manage: • Database – indexes, partitions and waits • • Execution – connections, sessions, requests Transaction – transactions and locks 24

Database – Dynamic Management Views

Name

dm_db_index_usage_stats dm_db_missing_index_details dm_db_missing_index_group_stats

Description

Returns counts of different types of index operations and the time each type of operation was last performed Returns detailed information about missing indexes Returns summary information about groups of missing indexes dm_db_missing_index_groups dm_db_wait_stats Returns information about what missing indexes are contained in a specific missing index group dm_db_objects_impacted_on_version_change Provides an early warning system to determine objects that will be impacted by major release upgrades dm_db_partition_stats Returns page and row-count information for every partition in the database Returns information about all the waits encountered by threads that executed during operation 25

Database – Dynamic Management Functions

Name

dm_db_index_operational_stats dm_db_index_physical_stats dm_db_missing_index_columns

Description

Returns current low-level I/O, locking, latching, and access method activity for each partition of a table or index in the database Returns size and fragmentation information for the data and indexes of the specified table or view Returns information about database table columns that are missing and index 26

Database Dynamic Management Objects

DEMO

Execution – Dynamic Management Views

Name

dm_exec_cached_plans dm_exec_connections dm_exec_procedure_stats dm_exec_query_memory_grants dm_exec_query_stats dm_exec_requests dm_exec_sessions dm_exec_trigger_stats

Description

Returns a row for each query plan that is cached by SQL Database Returns information about the connections established to SQL Database Returns aggregate performance statistics for cached stored procedures Returns information about the queries that have acquired a memory grant or that still require a memory grant to execute Returns aggregate performance statistics for cached query plans Returns information about each request that executes Returns information about all active user connections and internal tasks Returns aggregate performance statistics for cached triggers 28

Execution – Dynamic Management Functions

Name

dm_exec_describe_first_result_set dm_exec_describe_first_result_set_for_object dm_exec_query_plan dm_exec_sql_text dm_exec_text_query_plan

Description

Returns the metadata description of the first result set for the statement Returns the metadata description of the first result based on an object Id Returns the Showplan in XML format for the batch specified by the plan handle Returns the text of the SQL batch that is identified by the specified sql handle Returns the Showplan in text format for the batch specified by the plan handle or a specific statement within the batch 29

Execution Dynamic Management Objects

DEMO

Transaction – Dynamic Management Views

Name

dm_tran_active_transactions dm_tran_database_transactions dm_tran_locks dm_tran_session_transactions

Description

Returns information about transactions for your current database Returns information about transactions at the database level Returns information about currently active lock manager resources Returns correlation information for associated transactions and sessions 31

Scaling SQL Database

• Scale Up • Web & Business allow you to scale up storage • New Service Tiers enable you to scale up performance • Scale Out • Partitioning and sharding enable you to scale out without limits 32

Partition By Function

Customer Catalog Order Vendor 33

Sharding

CustomerShard01 Customer CustomerShard02 Shard Map 34

Scaling Out by Sharding

DEMO

Additional Resources

•

Cloud Service Fundamentals

• http://code.msdn.microsoft.com/windowsazure/Cloud Service-Fundamentals-4ca72649 36

New Performance Based Editions

SQL Database Service Tiers and Performance Levels

8 7 6 2 1 0 5 4 3 Web & Business Edition

PERFORMANCE

38

8 7 6 2 1 0 5 4 3 Goal of New Service Tiers

PERFORMANCE

39

SQL Database Editions and Service Tiers

Tier

Basic Standard Standard Standard Premium Premium Premium S0 S1 S2 P1 P2 P3

Perf Level DTU

5 10 20 50 100 200 800

Max DB Size Max Worker Threads Benchmark Transaction Rate

2 GB 250 GB 250 GB 250 GB 500 GB 500 GB 500 GB 30 60 90 120 200 400 1600 16,600/hour 521/minute 934/minute 2,570/minute 105/second 228/second 735/second

Benchmark Transaction Rate/Hour

16,600 31,260 56,040 154,200 378,000 820,800 2,646,000 40

New Service Tiers

DEMO

More SQL Database? – Get the Book

• • • • • • • • • • Get Started Setup and Configuration Migration Security Backup Reporting Performance Scalability Monitoring Management

Summary

• SQL Database is familiar, resilient and scalable • You need to consider fundamentals like connection management and query optimization when building applications using SQL DB • New Service Tiers provide consistent and predicable performance 43

Questions?

Eric D. Boyd Founder + CEO, responsiveX Microsoft Azure MVP, Insider & Advisor [email protected]

www.EricDBoyd.com

@EricDBoyd