Performance testing for Azure SQL Database and Cosmos DB

Cloud data platforms can absorb considerable growth, yet a reassuring dashboard does not prove that an application will meet its response-time commitments when customers, background jobs and growing data volumes compete for the same resources. Performance testing for Azure SQL Database and Cosmos DB exposes those constraints before release, giving delivery teams evidence for capacity decisions, prioritisation and remediation.

This guide focuses on load, stress and capacity testing for these two Azure services. The approach fits an Australian delivery context: teams are often distributed across Sydney, Melbourne, Brisbane and Perth, and users connect to Azure through corporate networks and VPNs. Tests must capture that geographic latency, align with Australian business-hour demand, and respect local data-handling expectations. Budgets, commercial approvals and cloud consumption are usually considered in Australian dollars, making runaway test traffic a commercial issue as well as a technical one.

Define Workloads, Targets and Service Tiers

Begin with the business transactions that matter most. Record their expected concurrency, arrival rate, read-to-write ratio and acceptable response times. Include batch processes, reporting queries and administrative activity where they compete with customer traffic. A target expressed only as “fast enough” cannot guide a useful test; use percentile-based objectives such as p95 and p99 latency, throughput and error-rate limits.

Service tiers then determine what the test can reveal. Azure SQL Database commonly uses vCore-based compute tiers, with serverless options for intermittent workloads. Cosmos DB uses request units per second for provisioned capacity, alongside autoscale and serverless alternatives. Test the configuration intended for production, including memory, storage and scaling limits. Autoscale supplies headroom, but it cannot remove inefficient queries, oversized documents or skewed partitions.

Set measurable acceptance criteria before generating traffic. They should cover latency under the expected peak, error rates, database resource consumption and behaviour at projected data volumes. Establish a baseline and repeat it under identical conditions later. Record any service limits or subscription quotas that could distort the results, and obtain quota increases early where a planned test could exceed them.

Build a Production-Like Test Environment

Use the intended Azure region, service versions, configuration settings and network paths whenever possible. An application running in Australia East, commonly associated with Sydney, may behave differently from one in Australia Southeast around Melbourne, particularly for users in Perth or Brisbane. A same-region test can isolate database behaviour, while a geographically distributed run can expose network latency and connection-management issues.

Replace customer records with carefully designed synthetic data. The substitute information should preserve realistic table sizes, column distributions, index selectivity and tenant concentrations. For Cosmos DB, retain comparable document sizes, nesting, property access paths and partition-key distributions. Evenly distributed test data can conceal hot partitions, poor parameter-sensitive plans and unexpectedly large responses. Confirm that masking or generation has not removed the characteristics that influence performance.

Treat data governance as a test requirement. Assess personal information handling against the Privacy Act 1988 and the Australian Privacy Principles, with additional obligations such as APRA CPS 230 or ASD ACSC guidance where relevant. Keep the exercise in an isolated non-production subscription—often called “not prod” in team shorthand—and restrict access to load generators. Apply spending budgets, concurrency ceilings and automatic stop conditions so an expensive test cannot continue unnoticed.

Generate Realistic Traffic and Data

Select tools that match the protocol and the questions being tested. JMeter, k6 and Gatling can generate database-level traffic, while Azure Load Testing suits HTTP and API scenarios. Confirm that the generator itself is not the bottleneck by measuring its resource use and network throughput. Run controlled comparisons from Australian locations and relevant overseas regions, including outside the database’s region, so network effects are visible rather than hidden.

Build workloads from realistic transaction mixes. Customer logins and searches may dominate one service, while telemetry ingestion creates sustained writes for another. Include connection-pool growth, cold and warm caches, parameter variations, large tenants and overlapping batch jobs. Reproduce the Australian afternoon demand peak, and schedule around AEST and AEDT handovers so background processing overlaps customer demand as it does in production. Daylight-saving changes can alter these overlaps and should prompt another test run.

Use staged profiles rather than an unexplained sudden jump. A ramp-up establishes a baseline, a steady phase measures normal behaviour, a spike tests burst handling and a soak test reveals cumulative problems such as log growth or connection leaks. Controlled stress testing can identify the breaking point, but establish resource ceilings beforehand. The purpose is to learn where performance degrades and how the system fails, not to consume the entire Azure budget or accidentally overload a shared environment.

Observe Runtime Behaviour and Bottlenecks

Capture end-to-end latency and database-side measurements together. For Azure SQL Database, use Azure Monitor, Query Store, dynamic management views and extended events to examine query duration, CPU, waits, blocking, deadlocks, storage latency and temporary database pressure. A slow request may originate in a connection pool, application code or a parameter-sensitive query, so server CPU alone is not a sufficient diagnosis.

For Cosmos DB, review request-unit consumption, request charges, operation duration, throttling responses and partition-level activity through Azure Monitor metrics and diagnostic logs sent to Log Analytics. Investigate hot partitions, cross-partition fan-out, inefficient indexing and oversized documents. A 429 response may reflect deliberate throttling, but repeated retries can create a retry storm and consume additional request units. Retrying through client backoff without correcting the underlying workload pattern only hides the bottleneck.

Compare each run with the baseline and retain the configuration, workload and results together. Investigate changes in p99 latency as well as averages, because the slowest requests often determine the customer experience. Also test relevant failure paths: SQL backup restoration, serverless resume delays, Cosmos DB failover and replication lag where applicable. Read consistency choices can affect both latency and data freshness, so they belong in the test scenario when those guarantees matter.

Automate Performance Checks in DevOps

Place workload scripts, test data definitions, infrastructure configuration and result baselines under version control. Run lightweight performance checks against every pull request, fuller load tests nightly and capacity tests periodically as data and usage grow. A practical rhythm catches gradual regressions before they reach a release, while the changing role of testers in DevOps teams makes performance evidence part of everyday delivery rather than a specialist activity at the end.

Define pipeline gates around agreed service objectives instead of a single universal response-time threshold. Compare measured p95 and p99 latency, throughput, error rates, request-unit consumption and resource saturation with the baseline. Keep routine gates stable enough to avoid false alarms, and send exceptional results with the relevant commit, configuration and telemetry. Generate meaningful load without running expensive capacity tests on every build, and include generator and Azure consumption in Australian-dollar budgets.

A side-by-side view clarifies why a single pass-or-fail threshold cannot cover both engines.

Focus	Azure SQL Database	Cosmos DB
Workload shape	Transactional queries, joins, batch processes and reporting	Document reads and writes, tenant access and distributed queries
Capacity measure	vCores, memory, storage performance and log throughput	Request units per second and request charges per operation
Scaling options	Fixed, elastic-pool or serverless compute, depending on the service tier	Provisioned throughput, autoscale or serverless capacity
Primary design risk	Blocking, parameter-sensitive plans, index quality and statistics accuracy	Hot partitions, cross-partition fan-out and excessive indexing
Key runtime signals	CPU, waits, blocking, deadlocks, query duration and temporary database pressure	Request units, 429 responses, partition skew and operation duration
Test-data emphasis	Table size, cardinality, skew and realistic indexes	Document size, nesting, access paths and partition-key distribution
Useful load profiles	Concurrent transactions, connection growth and overlapping batch jobs	Bursty APIs, write-heavy tenants and high fan-out reads

Bring your Azure workloads to a focused assessment with nFocus Software Testing. The team can help define realistic workloads, select suitable test environments, automate load execution and interpret the results across SQL and Cosmos DB. Explore nFocus testing services to arrange testing that matches your Agile delivery, DevOps pipelines and Australian operating conditions.

Share your target transactions, expected volumes and response-time objectives with the team. You will receive a practical approach, measurable success criteria and clear findings that support release decisions, capacity planning and cloud-cost control.