Azure SQL Database performance is influenced by a combination of service tiers, compute size, query design, and intelligent features built into the platform.
Service Tiers: General Purpose, Business Critical, and Hyperscale each provide different I/O, latency, and storage characteristics.
Compute Size: Measured in DTUs or vCores, determines the CPU, memory, and I/O capacity.
Intelligent Insights: Automatic tuning, query performance insights, and adaptive query processing help maintain optimal performance.
| Area | Recommendation | Tool |
|---|---|---|
| Indexing | Create missing indexes & remove duplicates | Query Performance Insight |
| Statistics | Update statistics regularly | Automatic Statistics |
| Query Design | Rewrite inefficient joins, avoid scalar UDFs | SQL Profiler |
| Resource Limits | Scale up/down based on DTU/vCore usage | Azure Monitor |
| Cache | Leverage result set caching and plan caching | Intelligent Performance |
Consider the following example where a table scan is causing latency:
SELECT *
FROM Orders O
JOIN Customers C ON O.CustomerID = C.CustomerID
WHERE O.OrderDate > '2024-01-01'
AND C.Region = 'North America';
Optimized version using proper indexing and filtered columns:
SELECT O.OrderID, O.OrderDate, C.Name, C.Region
FROM Orders O
JOIN Customers C ON O.CustomerID = C.CustomerID
WHERE O.OrderDate > '2024-01-01' AND C.Region = 'North America';
Ensure indexes exist on Orders(OrderDate, CustomerID) and Customers(Region, CustomerID).
Set up alerts to notify when performance thresholds are crossed.
{
"condition": {
"metricName": "cpu_percent",
"operator": "GreaterThan",
"threshold": 80,
"timeAggregation": "Average",
"windowSize": "PT5M"
},
"action": {
"type": "Email",
"emailAddress": "ops@example.com"
}
}