performance tuning azure sql database

Performance Tuning

Azure SQL Database

GOAL

• Understand the different tools,

options, and capabilities needed in

order to tune queries within an Azure

SQL Database.

Let’s Talk

scarydba.com

grant@scarydba.com

@gfritchey

Grant Fritchey

Azure SQL Databse:

Baseline

PERFORMANCE TUNING AZURE SQL DATABASE

Not SQL Server

• Platform as a Service

• Reduced functional foot print

• Radically increased functionality

– Scalability

– Redundancy

– Maintenance

#ITDEVCON

PERFORMANCE TUNING AZURE SQL DATABASE

SQL Server

• Under the covers, just SQL Server

• Same fundamental structures

• Same fundamental language

#ITDEVCON

PERFORMANCE TUNING AZURE SQL DATABASE

Why Tune Queries

• Just use PaaS scaling?

• Improve performance

• Reduce overhead

• Reduce costs

– Query tuning saves money!

#ITDEVCON

Available Tools

#ITDEVCON

PERFORMANCE TUNING AZURE SQL DATABASE

Tools: T-SQL

• Works the same

• Further reduction in learning curve

• Full range of functionality

PERFORMANCE TUNING AZURE SQL DATABASE

Tools: Powershell

• Automation is your friend

• New commands

• Direct integration between On-

premise and Azure

PERFORMANCE TUNING AZURE SQL DATABASE

Tools: SSMS

• Basics all work

• Some GUI functions don’t work

• Advanced functionality doesn’t work

• Let’s you function how you’re used to

#ITDEVCON

PERFORMANCE TUNING AZURE SQL DATABASE

Tools: Third Party

• Some work fully

• Some work partially

• Some don’t work at all

• Few directly focused, but that is

changing

#ITDEVCON

DEMO

#ITDEVCON

Gathering Metrics

PERFORMANCE TUNING AZURE SQL DATABASE

Reduced Footprint

• No Trace Events

• No Extended Events

– Yet

• No Profiler

– Yay!

#ITDEVCON

PERFORMANCE TUNING AZURE SQL DATABASE

DBCC

• SHOW_STATISTICS

• SQL_PERF

• Not much else

– No FREEPROCCACHE

– No DROPCLEANBUFFERS

#ITDEVCON

PERFORMANCE TUNING AZURE SQL DATABASE

Dynamic Management Views

• 80% the same as Earthed SQL Server

• Some unique to Azure SQL Database

#ITDEVCON

PERFORMANCE TUNING AZURE SQL DATABASE

Common DMVs

• Sys.dm_exec_query_stats

• Sys.dm_exec_procedure_stats

• Sys.dm_exec_sql_text

• Sys.dm_exec_query_plan

• Sys.dm_os_wait_stats

#ITDEVCON

PERFORMANCE TUNING AZURE SQL DATABASE

Unique DMVs

• Sys.dm_db_wait_stats

• Sys.database_connection_stats

• Sys.event_log

#ITDEVCON

DEMO

#ITDEVCON

Query Store

#ITDEVCON

PERFORMANCE TUNING AZURE SQL DATABASE

What is the Query Store

• “Flight Data Recorder”

• Built-in query metrics

• Captured execution plans

#ITDEVCON

PERFORMANCE TUNING AZURE SQL DATABASE

Query Store Functionality

• Show query metrics

• Show execution plans

• Control which execution plan gets

used

#ITDEVCON

DEMO

#ITDEVCON

PERFORMANCE TUNING AZURE SQL DATABASE

Conclusion

• Query tuning saves money

• Process is the same

• Learn additional tools available

• Take advantage of them

Resources

• Scarydba.com/resources

• SQL Server Execution Plans

• SQL Server Query Performance

Tuning

Let’s Talk

scarydba.com

grant@scarydba.com

@gfritchey

Grant Fritchey

Rate This Session Now!Rate with Mobile App:

• Select the session from the

Agenda or Speakers menus

• Select the Actions tab

• Click Rate Session

Rate with Website:

Register at www.devconnections.com/logintoratesession

Go to www.devconnections.com/ratesession

Select this session from the list and rate it

Tell Us

What

You

Thought

of This

Session

Be Entered to

WINPrizes!

#ITDEVCON