Azure SQL Database Performance Hit

Question

Azure SQL Database Performance Hit for Azure SQL Database - Business Critical Version,

1. Failing queries and blocking/locking queries.
2. SQL queries timing out.
3. CPU/Memory hit.. Trying to find solution.

Answer 1

Hi Srihari G,

It looks like you're experiencing significant performance issues with your Azure SQL Database in the Business Critical tier. below steps to help diagnose and resolve these issues:

Identify Blocking Queries: Use the below query to identify blocking sessions

SELECT 
    blocking_session_id AS BlockingSessionID,
    session_id AS VictimSessionID,
    wait_type,
    wait_time,
    wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

Once identified, you can investigate the blocking queries and optimize them to reduce contention. Consider using indexes or rewriting queries to minimize locks.

Identify Slow Queries: Use Extended Events or SQL Trace to identify queries that are timing out1. Look for queries that run longer than the predefined timeout value.

Optimize Queries: Focus on optimizing these queries by adding indexes, updating statistics, and rewriting inefficient queries

Identify High CPU Queries: Use the below query to identify queries consuming high CPU

SELECT TOP 10
    total_worker_time/execution_count AS AvgCPUTime,
    execution_count,
    total_worker_time,
    total_elapsed_time,
    query_hash,
    query_plan_hash,
    sql_handle
FROM sys.dm_exec_query_stats
ORDER BY AvgCPUTime DESC;

Update Statistics and Indexes: Ensure that statistics are up-to-date and consider adding missing indexes.

Review Query Plans: Look for inefficient query plans and parameter-sensitive issues that might be causing high CPU usage

Monitor Resource Usage: Use Azure Monitor to track CPU, memory, and I/O usage. This can help identify patterns and peak usage times.

Scale Up: If your workload has increased, consider scaling up your SQL Database to a higher tier or adding more resources to handle the load

Refer to the https://learn.microsoft.com/en-us/azure/azure-sql/database/identify-query-performance-issues?view=azuresql for more detailed guidance on identifying and resolving performance issue.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.