Scribd Logo
Upload

Welcome to Scribd!

  • 94 views

    Monitoring and Tuning Azure SQL Database: Warner Chaves

    Uploaded by

    Juan Carlos Montoya Murillo
    Azure SQL Database provides tools for monitoring and tuning performance. There are metrics for connectivity, performance, and the database that can be used to set alerts. System views track resource usage and waits at different granularities. The Azure Portal allows browsing performance data and the service can provide tuning recommendations, including automatically creating or dropping indexes.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Monitoring and Tuning Azure SQL Database: Warner Chaves

    Uploaded by

    Juan Carlos Montoya Murillo
    94 views25 pages
    Azure SQL Database provides tools for monitoring and tuning performance. There are metrics for connectivity, performance, and the database that can be used to set alerts. System views track resource usage and waits at different granularities. The Azure Portal allows browsing performance data and the service can provide tuning recommendations, including automatically creating or dropping indexes.

    Original Description:

    Base de datos Azure

    Original Title

    6-azure-sql-database-dba-m6-slides

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Azure SQL Database provides tools for monitoring and tuning performance. There are metrics for connectivity, performance, and the database that can be used to set alerts. System views track resource usage and waits at different granularities. The Azure Portal allows browsing performance data and the service can provide tuning recommendations, including automatically creating or dropping indexes.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    94 views25 pages

    Monitoring and Tuning Azure SQL Database: Warner Chaves

    Uploaded by

    Juan Carlos Montoya Murillo
    Azure SQL Database provides tools for monitoring and tuning performance. There are metrics for connectivity, performance, and the database that can be used to set alerts. System views track resource usage and waits at different granularities. The Azure Portal allows browsing performance data and the service can provide tuning recommendations, including automatically creating or dropping indexes.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 25

    Monitoring and Tuning Azure SQL

    Database

    Warner Chaves
    SQL MCM / MS DATA PLATFORM MVP

    @warchav sqlturbo.com
    What’s in This
    Module?
    Monitoring with Azure Metrics Alerts
    System Views to Track Performance
    Tuning Tools and Automation
    Monitoring

    A DBA still needs to be responsible for proper database


    access and performance.
    You can alert on critical database metrics
    tracked by Azure.
    Metrics Tracked

    Connectivity Performance Database


    CONNECTIVITY
    Blocked by Firewall
    Failed Connections
    Successful Connections
    PERFORMANCE
    DTU Percentage
    DTU Limit
    DTU Used
    CPU Percentage
    Log IO Percentage
    Data IO Percentage
    Deadlocks
    DATABASE
    Sessions Percentage
    Total Database Size
    Database Size Percentage
    Workers Percentage
    In-Memory OLTP Storage Percentage
    Alert Definition

    Condition Threshold
    Select a metric Period Action
    (<,<=,>,>=) value
    Demo
    Setting up Database Alerts with the
    Azure Portal and PowerShell
    Performance System Views

    Resource Usage Views Wait Stats View


    Resources Tracked

    Average CPU % Average Data IO % Average Memory %


    Average Log Write %
    Percentage Meaning

    70% CPU

    S0 Database = 10 DTUs

    This means 70% of the CPU power of 10 DTUs is


    consumed. It’s not a complete measure without the DTU
    value.
    How do these resource
    levels translate to the DTUs
    consumed?
    The percentage consumed of your DTUs
    is equal to the max percentage level of
    CPU, Data or Log.
    For Example

    70% CPU

    85% Data IO

    30% Log Write


    S0 Database = 10 DTUs

    This situation will translate to a 85% DTU Consumption


    measurement. Once any resource reaches 100% you will
    experience throttling of resources.
    Resource Views

    sys.resource_stats sys.dm_db_resource_stats
    Server scope (master) Database scope
    Measures in 5 minute intervals Measures in 15 second intervals
    Retains 14 days of history Retains 1 hour of history
    Captures when resource usage changes Captures even with no activity
    Waits View

    You can use the sys.dm_db_wait_stats view to see the


    wait types and their statistics inside each database.

    The sys.dm_os_wait_stats view is available but doesn’t


    offer much insight.
    Demo
    Tracking performance with system
    views
    Performance Tools

    Query
    Performance
    Performance Automatic Tuning
    Recommendations
    Insights
    Query Store

    Query Store needs to be enabled on the database so


    Azure can use this information for recommendations.
    Automatic Tuning

    Azure can automatically create and drop indexes based


    on your queries.
    Demo
    Reviewing performance and
    recommendations in the Azure Portal
    Azure provides alerts for performance,
    database or connectivity issues.

    Summary There are multiple system views tracking


    resource usage at different granularity.
    The Azure Portal provides an easy way to
    browse performance data.
    The service can provide tuning
    recommendations.
    The service can automatically create or
    drop indexes if desired.
    Apply your DBA experience
    to Azure SQL Database!

    You might also like