Performance tuning for mssql-django

This article provides guidance on optimizing Django application performance when using the mssql-django backend with SQL Server.

Connection optimization

Reduce connection overhead by tuning pooling, persistence, and timeout settings.

Enable connection pooling

Connection pooling is enabled by default. Verify it isn't disabled in your settings.py:

# Keep this True (or omit it entirely) for best connection performance
DATABASE_CONNECTION_POOLING = True

Use CONN_MAX_AGE

Set CONN_MAX_AGE to keep database connections open across requests, avoiding the overhead of establishing a new connection for each request:

DATABASES = {
    "default": {
        "ENGINE": "mssql",
        "NAME": "<your-database>",
        "USER": "<your-username>",
        "PASSWORD": "<your-password>",
        "HOST": "<your-server>",
        "PORT": "1433",
        "CONN_MAX_AGE": 600,  # Keep connections open for 10 minutes
        "OPTIONS": {
            "driver": "ODBC Driver 18 for SQL Server",
        },
    },
}

Set query timeout

Prevent long-running queries from consuming resources indefinitely:

DATABASES = {
    "default": {
        "ENGINE": "mssql",
        "NAME": "<your-database>",
        "USER": "<your-username>",
        "PASSWORD": "<your-password>",
        "HOST": "<your-server>",
        "PORT": "1433",
        "OPTIONS": {
            "driver": "ODBC Driver 18 for SQL Server",
            "query_timeout": 30,
        },
    },
}

Query optimization

Reduce database round-trips and query count with these ORM techniques.

Avoid N+1 query patterns

Use select_related for foreign key relationships (single JOIN query) and prefetch_related for many-to-many or reverse relationships (separate query with IN clause):

# Bad: N+1 queries
orders = Order.objects.all()
for order in orders:
    print(order.customer.name)  # Each access triggers a query

# Good: Single JOIN query
orders = Order.objects.select_related("customer").all()
for order in orders:
    print(order.customer.name)  # No additional queries

# Good: Two queries instead of N+1
orders = Order.objects.prefetch_related("items").all()
for order in orders:
    for item in order.items.all():  # Uses prefetched data
        print(item.name)

Use only() and defer()

Limit the columns retrieved when you don't need all fields:

# Retrieve only specific fields
products = Product.objects.only("name", "price").all()

# Defer loading of large fields
products = Product.objects.defer("description", "metadata").all()

Use values() and values_list()

When you don't need model instances, use values() or values_list() for lighter queries:

# Returns dictionaries instead of model instances
prices = Product.objects.values("name", "price")

# Returns tuples
names = Product.objects.values_list("name", flat=True)

Work within the 2,100 parameter limit

SQL Server limits each query to 2,100 parameters. Django generates parameterized queries, so operations that produce large IN clauses or bulk value lists can hit this limit.

Automatic optimization for large IN clauses:

When a filter(field__in=list) call has more than 2,048 values, the mssql-django backend automatically inserts the values into a temporary table (in batches of 1,000) and rewrites the query as WHERE field IN (SELECT params FROM #Temp_params). This optimization avoids the parameter limit without any code changes. It applies to all __in lookups, including those generated by prefetch_related(). The 2,048 threshold is set by the backend's max_in_list_size() to stay safely under SQL Server's 2,100 parameter limit.

This rewrite has a cost: creating and populating #Temp_params adds extra round-trips and tempdb activity. For lists near the threshold, benchmark both approaches in your workload.

When manual intervention is still needed:

The automatic temp-table optimization handles __in lookups, but these operations can still hit the 2,100 parameter limit because each field value is a separate parameter:

• bulk_create() or bulk_update() with many objects and many fields
• Complex Q() expressions with many chained conditions
• Cases where you want to avoid the round-trips required to populate #Temp_params (for example, when a smaller list and a normal IN (...) would be faster)

Solutions:

1. Use batch_size on bulk operations to keep each batch under the limit:

   # Backend cap with 10 fields: min(1000, 2050 // 10 // 2) = 102 rows per batch
   # The backend applies the conservative // 2 divisor for both bulk_create and bulk_update.
   Product.objects.bulk_create(products, batch_size=100)
   

2. Chunk large IN queries when you want to bypass the automatic temp-table mechanism:

   from itertools import islice
   
   def chunked_filter(queryset, field, values, chunk_size=2000):
       """Filter a queryset in chunks to stay within the 2,100 parameter limit."""
       results = []
       it = iter(values)
       while chunk := list(islice(it, chunk_size)):
           results.extend(queryset.filter(**{f"{field}__in": chunk}))
       return results
   
   # Returns a list of model instances, not a QuerySet
   products = chunked_filter(Product.objects, "pk", large_id_list)
   

3. Use subqueries instead of materializing ID lists:

   # Instead of: Order.objects.filter(product_id__in=list(Product.objects.values_list("id", flat=True)))
   # Use a subquery (Django generates a single SQL statement with no parameter explosion)
   Order.objects.filter(product__in=Product.objects.filter(active=True))
   

4. Use Prefetch with filtered querysets to limit the number of IDs passed to prefetch_related():

   from django.db.models import Prefetch
   
   orders = Order.objects.prefetch_related(
       Prefetch("items", queryset=OrderItem.objects.select_related("product"))
   )[:500]  # Limit parent queryset size
   

Bulk operations

Use bulk operations to reduce the number of database round-trips:

from decimal import Decimal

from myapp.models import Product

# Bulk create
new_products = [Product(name=f"Item {i}", price=Decimal("1.99") * i) for i in range(1000)]
Product.objects.bulk_create(new_products, batch_size=500)

# Bulk update: refetch so each instance has a primary key
products = list(Product.objects.filter(name__startswith="Item "))
for product in products:
    product.price *= Decimal("1.10")
Product.objects.bulk_update(products, ["price"], batch_size=500)

For more information about the return_rows_bulk_insert and default parameters, see Bulk operations with mssql-django.

Index strategies

Django creates indexes automatically for ForeignKey, OneToOneField, and fields with db_index=True. For additional indexes, use Meta.indexes:

from django.db import models

class Product(models.Model):
    name = models.CharField(max_length=100, db_index=True)
    category = models.CharField(max_length=50)
    price = models.DecimalField(max_digits=10, decimal_places=2)
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        indexes = [
            models.Index(fields=["category", "price"]),
            models.Index(fields=["-created_at"]),
        ]

For SQL Server-specific indexes (such as indexes with INCLUDE columns), use raw SQL in migrations:

from django.db import migrations

class Migration(migrations.Migration):
    dependencies = [("myapp", "0001_initial")]
    operations = [
        migrations.RunSQL(
            sql="CREATE INDEX IX_product_category ON myapp_product (category) INCLUDE (name, price);",
            reverse_sql="DROP INDEX IX_product_category ON myapp_product;",
        ),
    ]

The mssql-django backend supports covering indexes (supports_covering_indexes = True in mssql/features.py). On all Django versions supported by mssql-django (3.2 and later), you can use the include parameter on models.Index instead of raw SQL:

class Product(models.Model):
    name = models.CharField(max_length=100)
    category = models.CharField(max_length=50)
    price = models.DecimalField(max_digits=10, decimal_places=2)

    class Meta:
        indexes = [
            models.Index(fields=["category"], include=["name", "price"], name="ix_product_cat_cover"),
        ]

Filegroup placement

The mssql-django backend maps Django's db_tablespace to SQL Server's ON filegroup clause. Use this to place tables or indexes on specific filegroups:

class LargeAuditLog(models.Model):
    timestamp = models.DateTimeField(auto_now_add=True)
    message = models.TextField()

    class Meta:
        db_tablespace = "ARCHIVE_FG"

This generates: CREATE TABLE ... ON [ARCHIVE_FG].

Window functions

The backend supports SQL Server's window functions (supports_over_clause = True). Use Django's Window expressions for ranking, running totals, and partitioned calculations:

from django.db.models import F, Window
from django.db.models.functions import Rank, RowNumber

# Rank products by price within each category
products = Product.objects.annotate(
    price_rank=Window(
        expression=Rank(),
        partition_by=F("category"),
        order_by=F("price").desc(),
    )
)

# Row numbers across the full result set
products = Product.objects.annotate(
    row_num=Window(
        expression=RowNumber(),
        order_by=F("created_at").asc(),
    )
)

Monitor query performance

Use Django's built-in query logging to identify slow queries during development:

LOGGING = {
    "version": 1,
    "handlers": {
        "console": {
            "class": "logging.StreamHandler",
        },
    },
    "loggers": {
        "django.db.backends": {
            "level": "DEBUG",
            "handlers": ["console"],
        },
    },
}

For staging and production workloads, use SQL Server performance tools to analyze the SQL that Django generates:

1. Start with built-in performance reports before you query DMVs directly.

   • For SQL Server and Azure SQL Managed Instance, use Performance Dashboard in SQL Server Management Studio.
   • For Azure SQL Database, use Query Performance Insight for Azure SQL Database.

   These reports are usually the fastest way to find expensive queries, waits, blocking, and resource pressure with less room for mistakes than ad hoc DMV queries.

2. Use Query Store to identify top resource-consuming queries and queries that recently regressed.

3. Use the Top Resource Consuming Queries, Regressed Queries, and Query Wait Statistics views in SQL Server Management Studio to determine whether the bottleneck is CPU, I/O, memory, or waits. For guidance, see Best practices for monitoring workloads with Query Store.

4. Open an actual execution plan for the slow statement to look for scans, expensive key lookups, inaccurate row estimates, and missing indexes.

5. If a query became slower after a deployment or schema change, compare its plans in Query Store before changing application code. A DBA can temporarily force a known good plan while you fix the underlying index, statistics, or query-shape issue.

If Query Store shows waits instead of high CPU time, use Identify bottlenecks to separate CPU, memory, disk I/O, connection pressure, and blocking problems.

Related content

• Bulk operations with mssql-django
• Connection pooling in mssql-django
• Retry logic and connection resilience with mssql-django
• mssql-django configuration reference
• Performance Dashboard
• Query Performance Insight for Azure SQL Database
• Monitor performance by using the Query Store
• Analyze an actual execution plan
• Django database optimization