Component(s)

receiver/sqlserver

Is your feature request related to a problem? Please describe.

Proposal: Implement Shared Database Connection Pool in SQL Server Receiver

Problem Statement

The SQL Server receiver currently creates a separate database connection pool for each scraper (one per enabled query). This design has significant performance and resource efficiency implications:

Current Architecture

• Each metric query (Database IO, Performance Counters, Properties, Wait Stats) creates its own scraper
• Each scraper creates its own *sql.DB connection pool via dbProviderFunc()
• All pools connect to the same SQL Server instance with the same credentials

Issues with Current Design

1. Connection Pool Fragmentation

   • Multiple independent pools instead of one optimized pool
   • Each pool manages its own queue, idle timeout, and lifecycle independently
   • Query execution is not load-balanced across a shared resource

2. Resource Inefficiency

   • If 4 scrapers are enabled, resources are allocated 4x for connection management
   • Each pool maintains minimum connections separately
   • Higher memory overhead from redundant pool metadata

3. Configuration and Tuning Complexity

   • Connection pool settings (max connections, idle timeout, max lifetime) multiply across pools
   • Difficult to understand actual resource consumption from configuration alone
   • Hard to tune for specific deployment scenarios

4. Connection Limit Exhaustion

   • Database connection limits are reached sooner than necessary
   • In environments with strict connection quotas, fewer concurrent scrapers can run
   • No ability to gracefully degrade across pools

5. Observability Gaps

   • No clear insight into how many actual connections are open to the database
   • Pool-level metrics are scattered across multiple independent pools
   • Harder to diagnose connection-related issues

Evidence

• TODO comment in factory.go (lines 123-124): "Test if this needs to be re-defined for each scraper"
• Suggests original developers recognized this as a potential issue

Date: 2026-03-28
Credits: AI-Assisted (Claude Code)

Describe the solution you'd like

Proposed Solution

Implement a shared connection pool at the receiver level that all scrapers use:

Architecture Changes

1. Create pool at receiver initialization (in createMetricsReceiver/createLogsReceiver)

   • Single *sql.DB connection pool created once per receiver instance
   • Owned and managed by the receiver

2. Pass pool to scrapers instead of a provider function

   • Modify newSQLServerScraper() to accept a pre-initialized *sql.DB
   • Remove dbProviderFunc parameter

3. Update scraper lifecycle

   • Start(): Verify connection works, but don't create pool
   • Shutdown(): Close scraper resources, but not the pool (receiver owns it)
   • Receiver's Shutdown(): Close the single shared pool

Code Changes Overview

Before:

// factory.go
dbProviderFunc := func() (*sql.DB, error) {
    return sql.Open("sqlserver", getDBConnectionString(cfg))
}

// Each scraper creates its own pool
func (s *sqlServerScraperHelper) Start(...) error {
    s.db, err = s.dbProviderFunc()  // Creates new pool
    ...
}

After:

// factory.go - create pool once
db, err := sql.Open("sqlserver", getDBConnectionString(cfg))
if err != nil {
    return nil, fmt.Errorf("failed to open database connection: %w", err)
}

// Pass same pool to all scrapers
for _, query := range queries {
    scraper := newSQLServerScraper(..., db)  // Use shared pool
}

Benefits

Implementation Details

Files to Modify

1. 📄 factory.go:123-124 - TODO about re-defining pool per scraper

   • Create connection pool in createMetricsReceiver() and createLogsReceiver()
   • Pass pool to setupSQLServerScrapers() and setupSQLServerLogsScrapers()
   • Update these functions to pass *sql.DB to scrapers instead of dbProviderFunc

2. 📄 scraper.go:101-110 - Current Start() creates pool

   • Remove dbProviderFunc field from sqlServerScraperHelper
   • Update newSQLServerScraper() signature to accept *sql.DB
   • Simplify Start() to verify connection without creating pool
   • Simplify Shutdown() to not close the pool (receiver owns it)

Testing Strategy

1. Unit Tests: Verify single pool is reused across scrapers
2. Integration Tests: Ensure all scrapers work with shared pool
3. Resource Tests: Confirm reduced connection overhead
4. Error Tests: Handle pool initialization failures gracefully

Backwards Compatibility

User-facing: No breaking changes

• Configuration schema unchanged
• Metrics output unchanged
• Behavior from user perspective unchanged

Implementation: Minor changes to internal scraper lifecycle

• dbProviderFunc becomes implementation detail
• Error handling improved

Risks and Mitigation

Performance Impact

Expected Improvements

• Memory: Reduced overhead from N→1 pool managers
• Latency: Minimal (may slightly improve if pool has better utilization)
• Throughput: Potentially better (shared pool can batch work better)
• Connection count: Reduced by ~75% with 4 scrapers (4 separate pools → 1 shared)

Success Criteria

1. ✅ All scrapers function with single shared pool
2. ✅ Resource usage reduced (measurable in integration tests)
3. ✅ All existing tests pass
4. ✅ New integration test validates shared pool behavior
5. ✅ Documentation updated with resource consumption information

Next Steps

1. Community Discussion: Present this proposal for feedback
2. Design Review: Get sign-off from SQL Server receiver maintainers
3. Implementation: Create PR with changes
4. Testing: Comprehensive testing across scenarios
5. Release: Include in next minor version with release notes

Date: 2026-03-28
Credits: AI-Assisted (Claude Code)

Describe alternatives you've considered

No response

Additional context

No response

Tip

_{React with 👍 to help prioritize this issue. Please use comments to provide useful context, avoiding +1 or me too, to help us triage it. Learn more here.}