POC: Best approach to automate DB Query Stress Testing in CI/CD Pipeline for .NET Core + SQL Server + AKS?

Sikdar, Sameer 0 Reputation points
2026-04-17T16:07:06.1566667+00:00

Hi Community,

We are planning a POC to automate database query stress and load testing integrated into our CI/CD workflow. Looking for guidance on tools, patterns, and real-world experience.

Our Tech Stack:

  • Backend: .NET / .NET Core Web API
  • Database: SQL Server (Azure SQL)
  • Deployment: Azure Kubernetes Service (AKS)
  • CI/CD: Azure DevOps

Problem Statement:

Currently when a developer raises a Pull Request that contains changes to:

  • EF Core migrations or model changes
  • Raw SQL queries embedded in the codebase
  • Stored Procedures (new or modified)

There is no automated mechanism to validate the database performance impact before the code is merged. Issues are only discovered in Production.

What we are trying to solve:

1. Pre-merge (PR Pipeline):

  • Detect SQL/EF Core changes automatically when a PR is raised
  • Stress test the affected queries against an isolated SQL Server environment in AKS
  • Post results back to the PR as a comment with a pass/fail merge gate
  • Ideally suggest corrections (missing indexes, inefficient query patterns) as part of the PR feedback

2. Production (ongoing):

  • Identify slow or expensive queries already running in Production
  • Run scheduled load/stress tests against those queries
  • Alert the team if performance degrades after a deployment

Questions:

  1. What tools or frameworks work well for query-level stress testing (not full application load) in a CI/CD pipeline?
  2. Is there a recommended way to automatically extract and isolate changed queries from EF Core migrations or .cs files in a PR diff?
  3. Are there any Azure DevOps marketplace extensions available specifically for SQL Server query performance regression testing?
  4. What is the recommended approach for spinning up a short-lived isolated SQL Server environment in AKS purely for pipeline-scoped testing?
  5. For Production query monitoring, what is the best way to replay real query workloads from Query Store against a test environment at scale?
  6. If any other approach which can fulfill other than CI/CD, please recommend that as well.

Any guidance, real-world patterns, or tool recommendations would be greatly appreciated. Thanks!

SQL Server Database Engine
0 comments
Answer recommended by moderator
Erland Sommarskog 137.5K Reputation points MVP Volunteer Moderator
2026-04-17T20:10:33.9966667+00:00

What you have in mind is very bold and ambitious. And, quite likely, overly ambitious. It would be very challenging to get that going, and for the investment to pay back, the cost for performance regression in production must be very high.

Running performance tests on every checkin is very difficult. You cannot run your full test suite, but how do you know what tests you should run? A little more reasonable approach is to set up a full performance test that you run maybe once very 24 hours or so. Composing such a test is still anything but trivial. Exactly how complex depends on your system. The more functionality your system provides, the more challenging it will be. Probably, you should identify the core functions and start there. Then you can add things as you move on (and you get burned by performance regressions in production).

It's important to understand what while such a performance-test environment will catch some performance regressions before they reach production, they will not catch all, even if you have that specific stored procedure in your test suite. Keep in mind that SQL Server, like all other major RDBMS, has a cost-based optimiser that estimates which is the best plan from statistics sampled about the data. The statistics in your perf-test environment may differ from production, or there may be other differences which results in different plans with widely different performance.

And speaking of that, ideally, the perf-test environment should be a copy of production. But your production database may have very sensitive data, which makes it impermissible to have an unmasked copy in a test environment. There are tools for masking data, but that will also affect statistics, which increases the risk for that the perf-test environments yields different plans from production.

Was this answer helpful?

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest

  1. AI answer

    2026-04-17T16:07:59.6966667+00:00

    Automated database performance validation in CI/CD is typically implemented as part of a broader automated testing and performance-testing strategy rather than as a single “query-only” tool.

    Within Azure and SQL Server, the following patterns and services are relevant:

    1. Use CI/CD to build and deploy database artifacts, then run automated tests
      For SQL Server/Azure SQL, the common pattern is:
      • Build database projects or migration artifacts in CI to produce a deployable artifact (for example, a .dacpac).
      • Deploy that artifact to an isolated test database.
      • Run automated tests (unit, smoke, load, performance) against that database.
      SQL projects automation guidance describes:
      • Building the SQL project to produce a .dacpac with dotnet build.
      • Deploying the .dacpac to a target database using SqlPackage or platform-specific tasks.
      • Separating build and deploy stages so the same artifact can be reused across environments.
        This pattern is directly applicable to integrating database performance checks into Azure DevOps pipelines.
      On managed build agents (Azure Pipelines hosted agents), .NET SDK and SqlPackage are preinstalled on Windows, so pipelines can build and deploy SQL artifacts without additional setup. On Linux/macOS agents, SqlPackage must be installed in the workflow. Self-hosted agents can be used when more control is needed, for example to reach private databases or to pin specific SqlPackage versions.
    2. Integrate load and performance testing into CI/CD
      Azure Load Testing is the primary Azure-native service for automated load and performance testing:
      • It can be triggered from Azure Pipelines to run at meaningful points in the lifecycle, such as on each sprint end or in staging for release candidates.
      • It supports defining test fail criteria (for example, average response time or error thresholds) and auto-stop conditions to prevent unnecessary cost.
      • It can be integrated into CI/CD using Azure Pipelines or GitHub Actions, or via Azure CLI for other CI tools.
      A recommended pattern is:
      • Commit test scripts (for example, Apache JMeter scripts) and test configuration YAML into the repository.
      • Use the Azure portal “Set up CI/CD” experience on an Azure Load Testing resource to generate a pipeline definition in Azure DevOps that runs an existing load test.
      • Configure the pipeline to run the load test as a stage after deploying the database changes to an isolated environment.
      While Azure Load Testing is typically used for end-to-end application load, it can also be configured to target specific endpoints or operations that exercise particular queries. This allows focusing on query-level performance by designing tests around specific API calls or database operations.
    3. Types of tests to include in the pipeline
      For mission-critical workloads, continuous validation and testing guidance recommends a layered approach:
      • Unit testing for business logic.
      • Smoke testing to confirm basic availability and behavior (for example, reaching a web endpoint or querying a database).
      • Load testing to validate scalability and operation under defined load.
      • Stress testing to determine limits and recovery behavior under overload.
      • Performance testing to establish benchmarks and validate performance under load.
      These test types can be automated in CI/CD and targeted at the database layer by designing tests that focus on database interactions.
    4. Performance testing integration with Azure Pipelines
      Performance testing guidance highlights Azure Pipelines as the vehicle to integrate performance tests into CI/CD:
      • Add load/performance test steps into the pipeline after deployment to a test environment.
      • Use Azure Load Testing to generate high-scale load and validate performance and scalability.
      • Use Azure Monitor and Application Insights to analyze results, identify bottlenecks, and compare runs over time.
    5. Automated testing for data pipelines and services
      For data-oriented workloads (such as Azure Data Factory or Synapse), the recommended pattern for automated testing in CI/CD is:
      • Set up an Azure DevOps CI/CD project.
      • Create build artifacts containing scripts and deployment resources.
      • Run unit tests as part of the release pipeline or independently using SDKs (for example, .NET, Python).
      This pattern generalizes to database workloads: build and deploy artifacts, then run automated tests (including performance tests) as part of the pipeline to catch regressions before production.
    6. Load vs. stress testing for ASP.NET Core / web APIs
      For .NET / ASP.NET Core APIs that front SQL Server:
      • Load tests validate that the app can handle a specified load while meeting response goals.
      • Stress tests validate stability under extreme conditions and ability to recover.
      Azure Load Testing is the recommended service to generate high-scale load for such apps. Existing Apache JMeter scripts can be reused to drive load, and tests can be integrated into CI/CD.
    7. Query tuning and assessment tools (outside CI/CD)
      For manual or semi-automated query performance analysis:
      • SQL Server Management Studio (SSMS) includes a Query Hint Recommendation Tool that can help with tuning queries by suggesting hints.
      • Data Migration Assistant (DMA) can assess databases for compatibility, deprecated features, and remediation steps, which is useful when planning migrations or major changes that might affect performance.
    8. General migration and data-movement considerations
      When moving or splitting data (for example, to create test databases or microservice databases), performance guidance includes:
      • Running operations in parallel where possible.
      • Disabling nonclustered indexes and foreign keys during bulk copy, then re-enabling them.
      • Using batching aligned with the clustered index for large data volumes.
      • Rehearsing and testing scripts thoroughly.

    These references collectively describe how to:

    • Use Azure Pipelines and SQL project automation to build and deploy database changes.
    • Integrate Azure Load Testing into CI/CD for automated load and performance testing.
    • Apply a layered testing strategy (unit, smoke, load, stress, performance) to validate database-related changes continuously.
    • Use SSMS and related tools for manual query tuning and assessment outside the pipeline.

    The context does not provide specific mechanisms for automatically extracting changed queries from EF Core migrations or .cs files, nor does it list Azure DevOps marketplace extensions dedicated to query-level regression testing, or detailed guidance on replaying Query Store workloads. Those aspects would require additional tooling or custom implementation beyond the provided information.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.