Common performance issues:
Tools:
Dataabse maintenace best practices:
Often times, customer don’t know that it was a performance issue or connectivity issue.
It could be performance issue/connectivity issue.
It is good to understand the error message..
Even though error looks similar per eg:
- Connection Timeout Expired. the timeout period elapsed.
- Command Timeoutsqlexception: Timeout expired. the timeout period elapsed prior.
Is the issue only with particular queries?
entire database is slow? Resource, wait stats, temp db, long running queries.
Particular query: mutliplan ids, indexes, plan, stats.
increase in work load, check for any pattern.
significant increase in execution count..
Elastic pool: db is added in the elastic pool. per database setings? database reached its limit or elastic pool reached limit.
==========================================
High CPU Utilization, DTU are pegged.
Queried are running slow.
Execution Timeouts.
Queries taking longer than usual.
After moving to Azure performance slow
TempDB out of space.
====================================
common causes: High CPU Usage: scale up to higher tier.== for eg: so to s3 to s6. failover happened during the scale up operation.
Common causes: check if workload increased. certain queryconsuming all the CPU
Compare Query execution count during same timeframe prior day.
check for long running queries using DBCC OPENTRAN.
Are you perfoming ay maintenance task: such stats, index
================================================
if query slow timeout from application, try to repro form the SSMS.
Check wait stats: AYSNC_NETWORK, RESOURCE_SEMAPHORE, LCK XXX, PAGELATCH_XX.
Blocking/Deadlocks:
Missing Index, Index Fragmentation:
Update statistics
Execution Plans:
Azure portal/Quey Store will helpful to monitor the performance.
Azure SQL Database 