simplify database performance tuning with azure sql database · simplify database performance...
TRANSCRIPT
Simplify database performance tuning with Azure SQL Database
Vladimir Ivanovic, Borko Novakovic, Veljko Vasic
Program Managers, Microsoft Database Systems Group
Traditional RDBMS world:
- All aspects of building, running and tuning are left up to the users
- Requires significant expertise and large investment of time/energy
- User focus is on how the RDBMS works
Azure SQL Database today:
- Platform automatically manages HW/SW stack, backups, HA
- Users still need to worry about a lot of DB-specific details
- User focus is on how AzureDB platform works
Azure SQL DB Workload Insights
Powered by Workload Insight Azure SQL Database Service
Towards Azure SQL Database as an intelligent service
- Platform does the tedious work automatically
- Users focus on guiding the platform according to their needs
Workload Insights in Azure SQL Database
Strike the desired price/perf balance Optimum elastic pool across your many DBs
Easily tune the workload performance Optimize the indexes in your DBs
Recommendations or auto-management
Quickly troubleshoot your DB performance Query Store and insights on top queries
Workload Insights in Azure SQL Database
Strike the desired price/perf balance Optimum elastic pool across your many DBs
Easily tune the workload performance Optimize the indexes in your DBs
Recommendations or auto-management
Quickly troubleshoot your DB performance Query Store and insights on top queries
Azure SQL Database Index Advisor
Vladimir Ivanovic, Program Manager, Microsoft Database Systems Group
Intelligent service for implementing and
refining index recommendations
Index tuning recommendations tailored to
each DB
Tuning based on the observed usage, and
evolves as workload changes
Full-auto mode – service takes full care of
the indexes for your DB
Manual “review and apply” mode also
available for full control
Report + visualization of index impact
Easily Tune Database Performance with Index Advisor
SQL Database Index Advisor
Tuning
Models
Azure Cloud
Index Advisor Demo
Elastic Pool Recommendations
Veljko Vasic, Program Manager, Microsoft Database Systems Group
Single Database
5 10 20 50 100 125 250 500 1000 1750
B S0 S1 S2 S3 P1 P2 P4 P6 P11
Basic Standard Premium
Database Transaction Units (DTUs)
Elastic Database Pools
`
Auto-scale up to 5 eDTUs per DB Auto-scale up to 100 eDTUs per DB Auto-scale up to 1,000 eDTUs per DB
Basic Standard Premium
Premium elastic database pool 125–1,500 eDTUs per pool
Basic elastic database pool 100–1,200 eDTUs per pool
Standard elastic database pool 100–1,200 eDTUs per pool
Elastic Pools Demo
Azure SQL Database Query Performance Insight and Query Store Borko Novakovic, Program Manager, Microsoft Database Systems
Query Store
Workload data recorder for your database
• queries, plans, compilation and runtime
statistics available at your fingertips
• allows you to identify and fix performance
issues in the range of minutes
Now you can easily…
• fix serious performance issues
• learn and optimize your workloads
• apply system changes more safely
Comprehensive query performance information when you need it most
SQL Database
Queries
SQL Server 2016
Cloud and on premises
Queries
Data driven troubleshooting
14
sys.query_store_runtime_stats execution_type_id
count_executions
avg_ min_ max_ std_dev
duration cpu_time logical_io_reads logical_io_writes physical_io_reads
dop query_max_used_memory rowcount
plan_id runtime_stats_interval_id
clr_time
Determine query frequency (ad-hoc/ regular/frequent)? Combine with avg_ metrics to calculate totals.
Which queries are “slow”? Look at multiple intervals for regressions Pinpoint CPU intensive queries cpu_time << duration => waits
Pinpoint I/O intensive queries
Check if you suspect on DOP issues
Has data statistics changed?
0-regular, 3-client aborted, 4-server aborted
Separate regularly finished from incomplete ones. What is the frequency of incomplete queries? Which queries are aborted (frequently / recently)?
Separate CLR from the rest of the workload
Conclusions Key takeaways Query Store is a query data recorder that dramatically simplifies self-service performance troubleshooting.
Query Store is a platform for new performance tuning products in Azure SQL Database.
Thank you! Try Query Store, Query Performance Insights and Index Advisor Today!