1 / 45

Performance Tuning Microsoft Azure SQL Database

Performance Tuning Microsoft Azure SQL Database. Eric D. Boyd Founder & CEO, responsiveX Microsoft Azure MVP, Insider & Advisor. About Eric D. Boyd. @ EricDBoyd EricDBoyd.com. Founder/CEO Microsoft Azure MVP Speaker & Author. Agenda. Understanding SQL Database Architecture

mia-welch
Download Presentation

Performance Tuning Microsoft Azure SQL Database

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Performance Tuning Microsoft Azure SQL Database • Eric D. Boyd • Founder & CEO, responsiveX • Microsoft Azure MVP, Insider & Advisor

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

  3. Agenda Understanding SQL Database Architecture Managing Connections Monitoring Performance Scaling SQL Database Get Started with new Service Tiers

  4. Understanding SQL Database Architecture Experience, Capabilities, Characteristics

  5. 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

  6. 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

  7. 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

  8. 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

  9. Multi-Tenant Services Are Tricky  Take What You Can Get  Policed Resources  Dedicated Resources

  10. 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

  11. Getting Back to the Basics Open connections late, close early Pool connections Limit the size and shape of the data Tune queries

  12. 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

  13. Is It Transient?

  14. Is It Transient? http://msdn.microsoft.com/library/azure/dn338079.aspx

  15. Don’t Reinvent The Wheel • Recent versions of ADO.NET provides retry capability • SqlAzureExecutionStrategy

  16. Handling Transient Faults in Entity Framework 6 DEMO

  17. 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

  18. Transient Fault Handling Application Block

  19. Retry Strategies

  20. Transient Fault Handling Application Block DEMO

  21. Monitoring SQL Database • Connections • sys.database_connection_stats – database connections • sys.event_log – connectivity events

  22. Connection Events DEMO

  23. 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

  25. Database – Dynamic Management Functions

  26. Database Dynamic Management Objects DEMO

  27. Execution – Dynamic Management Views

  28. Execution – Dynamic Management Functions

  29. ExecutionDynamic Management Objects DEMO

  30. Transaction – Dynamic Management Views

  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

  35. 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

  37. Web & Business Edition

  38. Goal of New Service Tiers

  39. SQL Database Editions and Service Tiers

  40. New Service Tiers DEMO

  41. More SQL Database? – Get the Book • Get Started • Setup and Configuration • Migration • Security • Backup • Reporting • Performance • Scalability • Monitoring • Management

  42. 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 eric@responsiveX.com www.EricDBoyd.com @EricDBoyd

More Related