Performance Tuning Microsoft Azure SQL Database Eric D

Performance Tuning Microsoft Azure SQL Database Eric D. Boyd Founder & CEO, responsive. X Microsoft Azure MVP, Insider & Advisor

About Eric D. Boyd Founder/CEO Microsoft Azure MVP @Eric. DBoyd. com Speaker & Author

Agenda 1) 2) 3) 4) 5) Understanding SQL Database Architecture Managing Connections Monitoring Performance Scaling SQL Database 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 • Databases are replicated on three servers • Databases can be geo-replicated to other data centers • Azure manages the underlying infrastructure for you 6

Azure SQL DB is scalable • You can scale up resources for better performance • You can scale out across multiple databases • You can architect solutions with a mix of scale up and scale out to efficiently meet your needs 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/dn 338079. aspx 14

Don’t Reinvent The Wheel • Recent versions of ADO. NET provides retry capability • Sql. Azure. Execution. Strategy 15

Handling Transient Faults in Entity Framework 6 DEMO

Don’t Reinvent The Wheel • Recent versions of ADO. NET provides retry capability • Sql. Azure. Execution. Strategy • Microsoft Azure CAT and Patterns & Practices (P&P) created the Transient Fault Handling Application Block 17

Transient Fault Handling Application Block Component Detection Strategy Description Determines when an exception is a transient fault condition Retry Strategy 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 Retry Policy 19

Retry Strategies Class Description Exponential. Backoff Retries a specified number of times, exponentially delaying retries based on specified back-off parameters Fixed. Interval Retries a specified number of times with a fixed interval between each retry Incremental 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 Description dm_db_index_usage_stats Returns counts of different types of index operations and the time each type of operation was last performed dm_db_missing_index_details Returns detailed information about missing indexes dm_db_missing_index_group_stats Returns summary information about groups of missing indexes dm_db_missing_index_groups 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 dm_db_wait_stats Returns information about all the waits encountered by threads that executed during operation 25

Database – Dynamic Management Functions Name Description dm_db_index_operational_stats Returns current low-level I/O, locking, latching, and access method activity for each partition of a table or index in the database dm_db_index_physical_stats Returns size and fragmentation information for the data and indexes of the specified table or view dm_db_missing_index_columns Returns information about database table columns that are missing and index 26

Database Dynamic Management Objects DEMO

Execution – Dynamic Management Views Name Description dm_exec_cached_plans Returns a row for each query plan that is cached by SQL Database dm_exec_connections Returns information about the connections established to SQL Database dm_exec_procedure_stats Returns aggregate performance statistics for cached stored procedures dm_exec_query_memory_grants Returns information about the queries that have acquired a memory grant or that still require a memory grant to execute dm_exec_query_stats Returns aggregate performance statistics for cached query plans dm_exec_requests Returns information about each request that executes dm_exec_sessions Returns information about all active user connections and internal tasks dm_exec_trigger_stats Returns aggregate performance statistics for cached triggers 28

Execution – Dynamic Management Functions Name Description dm_exec_describe_first_result_set Returns the metadata description of the first result set for the statement dm_exec_describe_first_result_set_for_object Returns the metadata description of the first result based on an object Id dm_exec_query_plan Returns the Showplan in XML format for the batch specified by the plan handle dm_exec_sql_text Returns the text of the SQL batch that is identified by the specified sql handle dm_exec_text_query_plan 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 Description dm_tran_active_transactions Returns information about transactions for your current database dm_tran_database_transactions Returns information about transactions at the database level dm_tran_locks Returns information about currently active lock manager resources dm_tran_session_transactions 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 Customer. Shard 01 Customer. Shard 02 Shard Map 34

Scaling Out by Sharding DEMO

Additional Resources • Cloud Service Fundamentals • http: //code. msdn. microsoft. com/windowsazure/Cloud. Service-Fundamentals-4 ca 72649 36

New Performance Based Editions SQL Database Service Tiers and Performance Levels

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

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

SQL Database Editions and Service Tiers Tier Perf Level Basic DTU Max DB Size Max Worker Threads Benchmark Transaction Rate/Hour 5 2 GB 30 16, 600/hour 16, 600 Standard S 0 10 250 GB 60 521/minute 31, 260 Standard S 1 20 250 GB 90 934/minute 56, 040 Standard S 2 50 250 GB 120 2, 570/minute 154, 200 Premium P 1 100 500 GB 200 105/second 378, 000 Premium P 2 200 500 GB 400 228/second 820, 800 Premium P 3 800 500 GB 1600 735/second 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, responsive. X Microsoft Azure MVP, Insider & Advisor eric@responsive. X. com www. Eric. DBoyd. com @Eric. DBoyd