Performance Tuning Guide¶

Version: 1.0 Last Updated: 2026-01-18

Overview¶

This guide provides comprehensive performance optimization recommendations for Cesivi Server. It covers database optimization, caching strategies, connection pooling, thread configuration, and load testing.

Related Documentation: - PRODUCTION_DEPLOYMENT_CHECKLIST.md - Production deployment guide - SECURITY_HARDENING_GUIDE.md - Security hardening recommendations

Performance Targets¶

Baseline Metrics¶

Response Times (95th percentile): - Simple queries (GET web): < 100ms - List operations (GET lists): < 200ms - Item queries (GET items, no filter): < 300ms - Complex queries (OData filters, sorting): < 500ms - List/item creation: < 200ms - File uploads (< 10MB): < 2s - File downloads (< 10MB): < 1s

Throughput: - Concurrent users: 100+ (typical) - Requests per second: 1000+ (peak) - Database connections: 50-100 (pooled)

Resource Usage: - CPU: < 60% (average) - Memory: < 2GB (typical workload) - Database connections: < 50% of pool - Disk I/O: < 100 MB/s (typical)

Database Optimization¶

2.1: Indexing Strategy¶

Critical Indexes:

Lists Table:

-- Primary key (clustered)
CREATE CLUSTERED INDEX PK_Lists ON Lists(Id);

-- Frequently queried columns
CREATE NONCLUSTERED INDEX IX_Lists_WebId ON Lists(WebId) INCLUDE (Title, BaseTemplate);
CREATE NONCLUSTERED INDEX IX_Lists_Title ON Lists(Title) WHERE IsDeleted = 0;

ListItems Table:

-- Primary key (clustered)
CREATE CLUSTERED INDEX PK_ListItems ON ListItems(Id);

-- Frequently queried columns
CREATE NONCLUSTERED INDEX IX_ListItems_ListId ON ListItems(ListId) INCLUDE (Title, Created, Modified);
CREATE NONCLUSTERED INDEX IX_ListItems_ListId_Status ON ListItems(ListId, Status) WHERE IsDeleted = 0;

-- OData filter support
CREATE NONCLUSTERED INDEX IX_ListItems_Created ON ListItems(Created DESC);
CREATE NONCLUSTERED INDEX IX_ListItems_Modified ON ListItems(Modified DESC);

Files Table:

-- Primary key (clustered)
CREATE CLUSTERED INDEX PK_Files ON Files(Id);

-- Frequently queried columns
CREATE NONCLUSTERED INDEX IX_Files_ListId ON Files(ListId) INCLUDE (Name, ServerRelativeUrl);
CREATE NONCLUSTERED INDEX IX_Files_ServerRelativeUrl ON Files(ServerRelativeUrl);

Index Maintenance:

-- Rebuild fragmented indexes (monthly)
ALTER INDEX ALL ON Lists REBUILD;
ALTER INDEX ALL ON ListItems REBUILD;
ALTER INDEX ALL ON Files REBUILD;

-- Update statistics (weekly)
UPDATE STATISTICS Lists WITH FULLSCAN;
UPDATE STATISTICS ListItems WITH FULLSCAN;
UPDATE STATISTICS Files WITH FULLSCAN;

2.2: Query Optimization¶

Avoid N+1 Queries:

❌ BAD:

// N+1 query problem
var lists = await context.Lists.ToListAsync();
foreach (var list in lists)
{
    var items = await context.ListItems.Where(i => i.ListId == list.Id).ToListAsync();
    // 1 query for lists + N queries for items = N+1 queries
}

✅ GOOD:

// Single query with join
var listsWithItems = await context.Lists
    .Include(l => l.Items)
    .ToListAsync();
// 1 query total

Use Projection (Select):

❌ BAD:

// Loads all columns
var lists = await context.Lists.ToListAsync();

✅ GOOD:

// Loads only required columns
var lists = await context.Lists
    .Select(l => new { l.Id, l.Title, l.ItemCount })
    .ToListAsync();

Use AsNoTracking for Read-Only Queries:

// Faster for read-only queries (no change tracking overhead)
var lists = await context.Lists
    .AsNoTracking()
    .ToListAsync();

2.3: Connection String Optimization¶

Optimized Connection String:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=CesiviDB;User=cesivi_app;Password=***;Min Pool Size=10;Max Pool Size=100;Connection Timeout=30;Command Timeout=30;Pooling=true;MultipleActiveResultSets=true;"
  }
}

Parameters Explained: - Min Pool Size=10: Keep 10 connections warm (faster first request) - Max Pool Size=100: Maximum 100 connections (adjust based on load) - Connection Timeout=30: 30 seconds to establish connection - Command Timeout=30: 30 seconds for query execution - Pooling=true: Enable connection pooling (critical for performance) - MultipleActiveResultSets=true: Allow multiple result sets per connection

Caching Strategies¶

3.1: Cache Configuration¶

In-Memory Cache (Development, Single Server):

{
  "Cesivi": {
    "Cache": {
      "Type": "InMemory",
      "DefaultExpirationMinutes": 60,
      "SlidingExpiration": true,
      "MaxSize": 1000
    }
  }
}

Redis Cache (Production, Multi-Server):

{
  "Cesivi": {
    "Cache": {
      "Type": "Redis",
      "ConnectionString": "localhost:6379,abortConnect=false,connectTimeout=5000,syncTimeout=5000",
      "InstanceName": "Cesivi:",
      "DefaultExpirationMinutes": 60,
      "SlidingExpiration": true
    }
  }
}

Redis Configuration:

# Install Redis (Ubuntu)
sudo apt-get install redis-server

# Configure Redis
sudo nano /etc/redis/redis.conf

# Recommended settings:
maxmemory 2gb
maxmemory-policy allkeys-lru  # Evict least recently used keys
appendonly yes  # Persistence
save 900 1  # Save to disk every 15 minutes if 1+ key changed

# Start Redis
sudo systemctl start redis
sudo systemctl enable redis

3.2: What to Cache¶

Cache Aggressively: - Web properties (Title, Url, Description) - rarely change - List metadata (Title, BaseTemplate, ItemCount) - infrequent changes - User information (Name, Email, Groups) - changes rarely - Site groups (Owners, Members, Visitors) - static

Cache Moderately: - List items (with short TTL: 5-15 minutes) - changes frequently - File metadata (Name, Size, Modified) - moderate changes

Don't Cache: - Authentication tokens (security risk) - Real-time data (current user session) - Large binary files (use CDN instead)

3.3: Cache Invalidation¶

Invalidation Strategies:

1. Time-Based (TTL):

// Cache for 60 minutes
_cache.Set("web_" + webId, web, TimeSpan.FromMinutes(60));

2. Event-Based:

// Invalidate on update
public async Task UpdateListAsync(List list)
{
    await _context.SaveChangesAsync();
    _cache.Remove("list_" + list.Id);  // Invalidate cache
}

3. Tag-Based (Redis):

// Cache with tags
await _cache.SetAsync("item_" + itemId, item, new DistributedCacheEntryOptions
{
    AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(60),
    Tags = new[] { "list_" + listId }  // Tag by list
});

// Invalidate all items in list
await _cache.RemoveByTagAsync("list_" + listId);

3.4: Cache Performance Metrics¶

Monitor: - Cache hit rate (target: > 80%) - Cache miss rate (target: < 20%) - Cache eviction rate (target: < 5%) - Average cache retrieval time (target: < 5ms)

Diagnostics Endpoint:

app.MapGet("/_diagnostics/cache", (IDistributedCache cache) =>
{
    return new
    {
        Type = "Redis",
        HitRate = GetCacheHitRate(),  // From metrics
        MissRate = GetCacheMissRate(),
        EvictionRate = GetCacheEvictionRate(),
        TotalKeys = GetTotalCacheKeys()
    };
});

Connection Pooling¶

4.1: HTTP Client Connection Pooling¶

❌ BAD (Creates new connection per request):

public async Task<string> GetDataAsync(string url)
{
    using var client = new HttpClient();  // ❌ Don't do this
    return await client.GetStringAsync(url);
}

✅ GOOD (Reuses connections):

// Register HttpClient in DI
builder.Services.AddHttpClient("CesiviClient")
    .ConfigureHttpClient(client =>
    {
        client.Timeout = TimeSpan.FromSeconds(30);
        client.DefaultRequestHeaders.Add("User-Agent", "Cesivi/2.0");
    })
    .ConfigurePrimaryHttpMessageHandler(() => new SocketsHttpHandler
    {
        PooledConnectionLifetime = TimeSpan.FromMinutes(5),  // Recycle connections
        PooledConnectionIdleTimeout = TimeSpan.FromMinutes(2),
        MaxConnectionsPerServer = 10
    });

// Use in service
public class MyService
{
    private readonly HttpClient _httpClient;

    public MyService(IHttpClientFactory httpClientFactory)
    {
        _httpClient = httpClientFactory.CreateClient("CesiviClient");
    }

    public async Task<string> GetDataAsync(string url)
    {
        return await _httpClient.GetStringAsync(url);
    }
}

4.2: Database Connection Pooling¶

Already configured via connection string (see section 2.3).

Monitor Connection Pool:

app.MapGet("/_diagnostics/connections", async (CesiviDbContext context) =>
{
    var connectionString = context.Database.GetConnectionString();

    // Query SQL Server DMVs
    var poolStats = await context.Database.SqlQueryRaw<dynamic>(@"
        SELECT
            DB_NAME() AS DatabaseName,
            COUNT(*) AS TotalConnections,
            SUM(CASE WHEN login_time > DATEADD(MINUTE, -5, GETDATE()) THEN 1 ELSE 0 END) AS RecentConnections
        FROM sys.dm_exec_sessions
        WHERE database_id = DB_ID()
    ").ToListAsync();

    return poolStats;
});

Thread Pool Tuning¶

5.1: Kestrel Configuration¶

Optimized Kestrel Settings:

builder.WebHost.ConfigureKestrel(options =>
{
    // Limits
    options.Limits.MaxConcurrentConnections = 1000;
    options.Limits.MaxConcurrentUpgradedConnections = 1000;
    options.Limits.MaxRequestBodySize = 52428800;  // 50 MB
    options.Limits.MinRequestBodyDataRate = new MinDataRate(100, TimeSpan.FromSeconds(10));
    options.Limits.MinResponseDataRate = new MinDataRate(100, TimeSpan.FromSeconds(10));

    // Timeouts
    options.Limits.KeepAliveTimeout = TimeSpan.FromMinutes(2);
    options.Limits.RequestHeadersTimeout = TimeSpan.FromSeconds(30);

    // HTTP/2 settings
    options.Limits.Http2.MaxStreamsPerConnection = 100;
    options.Limits.Http2.HeaderTableSize = 4096;
    options.Limits.Http2.MaxFrameSize = 16384;
    options.Limits.Http2.MaxRequestHeaderFieldSize = 8192;

    // Disable server header
    options.AddServerHeader = false;
});

5.2: Thread Pool Configuration¶

Configure Thread Pool (Program.cs):

// Increase thread pool size for high concurrency
ThreadPool.SetMinThreads(50, 50);  // Min worker threads, Min I/O threads
ThreadPool.SetMaxThreads(1000, 1000);  // Max worker threads, Max I/O threads

Monitor Thread Pool:

app.MapGet("/_diagnostics/threadpool", () =>
{
    ThreadPool.GetMinThreads(out int minWorker, out int minIo);
    ThreadPool.GetMaxThreads(out int maxWorker, out int maxIo);
    ThreadPool.GetAvailableThreads(out int availWorker, out int availIo);

    return new
    {
        MinWorkerThreads = minWorker,
        MinIoThreads = minIo,
        MaxWorkerThreads = maxWorker,
        MaxIoThreads = maxIo,
        AvailableWorkerThreads = availWorker,
        AvailableIoThreads = availIo,
        ActiveThreads = maxWorker - availWorker
    };
});

Response Compression¶

6.1: Enable Compression¶

Configuration:

builder.Services.AddResponseCompression(options =>
{
    options.EnableForHttps = true;  // Enable for HTTPS
    options.Providers.Add<GzipCompressionProvider>();
    options.Providers.Add<BrotliCompressionProvider>();

    // Compress these MIME types
    options.MimeTypes = ResponseCompressionDefaults.MimeTypes.Concat(new[]
    {
        "application/json",
        "application/xml",
        "text/plain",
        "text/css",
        "text/html",
        "application/javascript",
        "image/svg+xml"
    });
});

builder.Services.Configure<GzipCompressionProviderOptions>(options =>
{
    options.Level = CompressionLevel.Fastest;  // Balance speed vs. compression
});

builder.Services.Configure<BrotliCompressionProviderOptions>(options =>
{
    options.Level = CompressionLevel.Fastest;
});

app.UseResponseCompression();  // Add early in pipeline

Compression Benchmarks: - JSON response (100 KB): 85% reduction (100 KB → 15 KB) - XML response (200 KB): 90% reduction (200 KB → 20 KB) - HTML response (50 KB): 80% reduction (50 KB → 10 KB)

6.2: Selective Compression¶

Don't compress: - Already compressed files (JPEG, PNG, ZIP, PDF) - Very small responses (< 1 KB) - overhead not worth it - Streaming responses

Load Testing¶

7.1: Load Testing Tools¶

Recommended Tools: - Apache JMeter - GUI-based, comprehensive - k6 - Modern, scriptable, Grafana integration - wrk - Simple, command-line - NBomber - .NET-based load testing

7.2: JMeter Test Plan¶

Scenario: Simulate 100 concurrent users:

Create Thread Group:
Number of Threads: 100
Ramp-Up Period: 60 seconds
Loop Count: 10
Add HTTP Request Samplers:
GET /_api/web (baseline)
GET /_api/web/lists (list metadata)
GET /_api/web/lists/getbytitle('Tasks')/items (query items)
POST /_api/web/lists/getbytitle('Tasks')/items (create item)
Add Assertions:
Response code: 200 or 201
Response time: < 500ms (95th percentile)
Add Listeners:
Summary Report
Aggregate Report
Response Time Graph

7.3: k6 Load Test Script¶

// load-test.js
import http from 'k6/http';
import { check, sleep } from 'k6';

export let options = {
  stages: [
    { duration: '1m', target: 50 },   // Ramp up to 50 users
    { duration: '3m', target: 100 },  // Stay at 100 users
    { duration: '1m', target: 0 },    // Ramp down
  ],
  thresholds: {
    http_req_duration: ['p(95)<500'],  // 95% of requests < 500ms
    http_req_failed: ['rate<0.01'],    // Error rate < 1%
  },
};

const BASE_URL = 'http://localhost:5000';
const AUTH = 'Basic ' + encoding.b64encode('admin:password');

export default function () {
  // Test 1: Get web
  let res = http.get(`${BASE_URL}/_api/web`, {
    headers: { 'Authorization': AUTH, 'Accept': 'application/json' },
  });
  check(res, { 'GET web status 200': (r) => r.status === 200 });

  sleep(1);

  // Test 2: Get lists
  res = http.get(`${BASE_URL}/_api/web/lists`, {
    headers: { 'Authorization': AUTH, 'Accept': 'application/json' },
  });
  check(res, { 'GET lists status 200': (r) => r.status === 200 });

  sleep(1);

  // Test 3: Query items
  res = http.get(`${BASE_URL}/_api/web/lists/getbytitle('Tasks')/items`, {
    headers: { 'Authorization': AUTH, 'Accept': 'application/json' },
  });
  check(res, { 'GET items status 200': (r) => r.status === 200 });

  sleep(2);
}

Run Test:

k6 run load-test.js

7.4: Interpreting Results¶

Key Metrics: - Response Time (p95): 95% of requests complete within this time - Throughput: Requests per second (RPS) - Error Rate: Percentage of failed requests - Concurrency: Number of simultaneous users

Good Results: - p95 response time: < 500ms - Throughput: > 1000 RPS - Error rate: < 1% - CPU usage: < 70% - Memory usage: Stable (no leaks)

Bad Results (Investigate): - p95 response time: > 2s (slow queries, no caching) - Error rate: > 5% (application errors, database timeouts) - CPU usage: > 90% (insufficient resources, inefficient code) - Memory usage: Growing continuously (memory leak)

Monitoring and Profiling¶

8.1: Application Metrics¶

Metrics to Monitor: - Request rate (requests/second) - Response time (average, p50, p95, p99) - Error rate (errors/second, percentage) - Active connections - Cache hit rate - Database query time

Prometheus Metrics:

// Add to Program.cs
builder.Services.AddOpenTelemetry()
    .WithMetrics(metrics =>
    {
        metrics.AddPrometheusExporter();
        metrics.AddMeter("Cesivi");
    });

// Create custom metrics
var meter = new Meter("Cesivi");
var requestCounter = meter.CreateCounter<long>("cesivi_requests_total");
var responseTimeHistogram = meter.CreateHistogram<double>("cesivi_response_time_seconds");

app.Use(async (context, next) =>
{
    var sw = Stopwatch.StartNew();
    await next();
    sw.Stop();

    requestCounter.Add(1, new KeyValuePair<string, object>("endpoint", context.Request.Path));
    responseTimeHistogram.Record(sw.Elapsed.TotalSeconds, new KeyValuePair<string, object>("endpoint", context.Request.Path));
});

app.MapPrometheusScrapingEndpoint();  // Expose at /metrics

8.2: Database Profiling¶

SQL Server Query Performance:

-- Find slow queries
SELECT TOP 10
    qs.execution_count,
    qs.total_elapsed_time / 1000000.0 AS total_elapsed_time_seconds,
    qs.total_elapsed_time / qs.execution_count / 1000000.0 AS avg_elapsed_time_seconds,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2)+1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qt.text LIKE '%ListItems%'  -- Filter by table
ORDER BY qs.total_elapsed_time DESC;

-- Find missing indexes
SELECT
    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    'CREATE INDEX IX_' + OBJECT_NAME(mid.object_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,''),', ','_'),'[',''),']','') +
        CASE WHEN mid.inequality_columns IS NOT NULL THEN '_' + REPLACE(REPLACE(REPLACE(mid.inequality_columns,', ','_'),'[',''),']','') ELSE '' END + ' ON ' +
        mid.statement + ' (' + ISNULL(mid.equality_columns,'') +
        CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END +
        ISNULL(mid.inequality_columns, '') + ')' +
        ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY improvement_measure DESC;

8.3: Memory Profiling¶

dotnet-trace (CPU and Memory Profiling):

# Install tool
dotnet tool install --global dotnet-trace

# Collect trace
dotnet trace collect --process-id <PID> --duration 00:00:30

# Analyze trace in Visual Studio or PerfView

dotMemory (JetBrains): - Attach to running Cesivi process - Capture snapshot - Analyze object retention graph - Find memory leaks

Performance Checklist¶

Database¶

[ ] Indexes created on frequently queried columns
[ ] Index maintenance scheduled (monthly rebuild)
[ ] Statistics updated regularly (weekly)
[ ] N+1 queries eliminated (use Include/joins)
[ ] AsNoTracking used for read-only queries
[ ] Connection pooling enabled (Min=10, Max=100)
[ ] Query timeout configured (30 seconds)

Caching¶

[ ] Cache configured (Redis for multi-server, InMemory for single server)
[ ] Web metadata cached (60 minutes TTL)
[ ] List metadata cached (60 minutes TTL)
[ ] Cache hit rate monitored (target > 80%)
[ ] Cache invalidation on updates

Connection Pooling¶

[ ] HttpClient registered in DI (don't create per request)
[ ] Connection lifetime configured (5 minutes)
[ ] Max connections per server set (10)
[ ] Database connection pooling enabled

Threading¶

[ ] Kestrel MaxConcurrentConnections configured (1000)
[ ] Thread pool min threads set (50)
[ ] Thread pool max threads set (1000)
[ ] Async/await used throughout (no blocking calls)

Compression¶

[ ] Response compression enabled (Gzip + Brotli)
[ ] HTTPS compression enabled
[ ] MIME types configured (JSON, XML, HTML)
[ ] Compression level set (Fastest)

Load Testing¶

[ ] Load testing performed (JMeter/k6)
[ ] Baseline metrics established
[ ] 95th percentile response time < 500ms
[ ] Error rate < 1%
[ ] Throughput > 1000 RPS

Monitoring¶

[ ] Application metrics exposed (Prometheus /metrics)
[ ] Database query performance monitored
[ ] Memory profiling performed (no leaks)
[ ] Alerting configured (high error rate, slow responses)

Last Updated: 2026-01-18 Version: 1.0 For Support: See README.md