Share via

Performance Issues in Azure SQL Database with Service Tier S2

GeethaThatipatri-MSFT 29,017 Reputation points Microsoft Employee
2024-07-25T19:55:40.22+00:00

What could be causing performance issues in my Azure SQL Database when using the Standard S2 service tier, and how can I resolve them?

PS - Based on common issues that we have seen from customers and other sources, we are posting these questions to help the Azure community

Azure SQL Database
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. GeethaThatipatri-MSFT 29,017 Reputation points Microsoft Employee
    2024-07-25T19:56:53.6733333+00:00

    Greetings!

    Performance issues in your Azure SQL Database using the Standard S2 service tier can occur because this tier provides less than one vCore (CPU), which may not be sufficient for resource-intensive workloads. The recommended solution is to upgrade to a service tier of S3 or greater, which offers more compute power and can handle higher transaction volumes more efficiently.

     

    To determine the current database memory and CPU usage based on the service tier, you can run the following query against the database:

     

    
WITH dtu_vcore_map AS (

    SELECT rg.slo_name,

        CAST(DATABASEPROPERTYEX(DB_NAME(), Edition) AS NVARCHAR(MAX)) AS dtu_service_tier,

        CASE

            WHEN slo.slo_name LIKE '%S0%' THEN 'standard_series'

            WHEN slo.slo_name LIKE '%S1%' THEN 'standard_series'

            WHEN slo.slo_name LIKE '%S2%' THEN 'standard_series'

            END AS dtu_hardware_gen,

        s.scheduler_count * 1.7 AS dtu_logical_cpus,

        CAST((jo.process_memory_limit_mb / s.scheduler_count) / 1024. AS DECIMAL(10,2)) AS dtu_memory_per_core_gb

    FROM sys.dm_user_db_resource_governance AS rg

    CROSS JOIN (SELECT COUNT(*) AS scheduler_count FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE') AS s

    CROSS JOIN sys.dm_os_job_object AS jo

    CROSS APPLY (SELECT UPPER(rg.slo_name) AS slo_name) slo

    WHERE rg.dtu_limit > 0

        AND DB_NAME() <> 'master'

        AND rg.database_id = DB_ID()

)

SELECT dtu_logical_cpus,

    dtu_memory_per_core_gb,

    dtu_service_tier,

    CASE

        WHEN dtu_service_tier = 'Basic' THEN 'General Purpose'

        WHEN dtu_service_tier = 'Standard' THEN 'General Purpose or Hyperscale'

        WHEN dtu_service_tier = 'Premium' THEN 'Business Critical or Hyperscale'

        END AS vcore_service_tier,

    CASE

        WHEN dtu_hardware_gen = 'standard_series' THEN dtu_logical_cpus

        END AS standard_series_vcores,

    5.05 AS standard_series_memory_per_core_gb

FROM dtu_vcore_map;

     

    For more information on the memory and CPU provided for DTU-based models and to understand the differences between various service tiers, you can refer to the following documentation links:

     

     

    Please do not forget to "up-vote" wherever the information provided helps you, as this can be beneficial to other community members.

    Regards

    Geetha

    0 comments