Many SQL Server 2005 administrators encounter this frustrating pattern: after 1-2 weeks of smooth operation, queries gradually slow down across the board with no single culprit query. The server isn't resource-constrained (CPU, disk I/O normal), yet restarting SQL Server provides temporary relief. Let's explore this systemic issue.

When performance degrades, run these diagnostic queries:

-- Current active requests with blocking info
SELECT 
    r.session_id,
    r.start_time,
    r.status,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    DB_NAME(r.database_id) as database_name,
    s.text as sql_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.session_id > 50
ORDER BY r.start_time;

-- Check for memory pressure
SELECT 
    type,
    pages_kb/1024 as mb_used,
    virtual_memory_committed_kb/1024 as virtual_mem_mb,
    locked_page_allocations_kb/1024 as locked_pages_mb
FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC;

Based on similar cases, these issues frequently cause gradual degradation:

• Memory Pressure: SQL Server 2005's buffer pool management wasn't as efficient as newer versions
• TempDB Contention: Excessive temp table operations without proper cleanup
• Autogrowth Settings: The 1MB default growth causes fragmentation
• Plan Cache Bloat: Accumulation of single-use query plans
• MSDB Maintenance: Uncontrolled log shipping history tables growth

TempDB Optimization

-- Create multiple TempDB files (1 per logical processor, up to 8)
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, SIZE = 4096MB, FILEGROWTH = 1024MB);

-- Add additional files if needed
ALTER DATABASE tempdb 
ADD FILE (NAME = tempdev2, FILENAME = 'E:\tempdb2.ndf', SIZE = 4096MB, FILEGROWTH = 1024MB);

Memory Management

-- Check current memory configuration
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)';

-- Set reasonable memory limits (adjust for your server)
EXEC sp_configure 'max server memory (MB)', 8192;
RECONFIGURE;

Preventative Maintenance Script

-- Weekly maintenance script
USE master;
GO

-- Clear single-use plans
DBCC FREESYSTEMCACHE('SQL Plans');

-- Update statistics on critical tables
EXEC sp_updatestats;

-- Rebuild fragmented indexes (sample for one database)
USE YourDatabase;
GO
DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql + 
'ALTER INDEX [' + i.name + '] ON [' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + '] ' +
CASE WHEN ps.avg_fragmentation_in_percent > 30 THEN 'REBUILD' 
     ELSE 'REORGANIZE' END + ';' + CHAR(13)
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
JOIN sys.objects o ON ps.object_id = o.object_id
JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE ps.avg_fragmentation_in_percent > 10;

EXEC sp_executesql @sql;

After implementing these changes:

• Created 4 TempDB files (server has 4 cores)
• Set autogrowth to 10% with 1024MB minimum growth
• Scheduled weekly index maintenance
• Implemented monthly MSDB cleanup
• Added memory pressure monitoring alerts

Result: The system now maintains stable performance for 6+ weeks between maintenance cycles.

For proactive detection, set up these performance counters:

-- SQL Server: Buffer Manager - Page life expectancy
-- SQL Server: Memory Manager - Total Server Memory
-- SQL Server: SQL Statistics - Batch Requests/sec
-- PhysicalDisk - Avg. Disk sec/Read

Alert when Page Life Expectancy drops below 300 seconds or disk latency exceeds 20ms.

As a DBA, I've encountered numerous performance issues, but none as perplexing as the gradual degradation where all queries slow down uniformly without obvious resource constraints. The scenario typically unfolds like this:

• Normal performance post-restart
• Gradual slowdown over 1-2 weeks
• No single query identified as the culprit
• Service restart provides temporary relief

When standard monitoring tools fail during high CPU situations, this emergency diagnostic query becomes invaluable:

USE master
SELECT 
    text AS query_text,
    wait_time,
    blocking_session_id AS Block,
    percent_complete,
    cpu_time,
    logical_reads,
    * 
FROM sys.dm_exec_requests 
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY start_time ASC

Through extensive troubleshooting, we've ruled out several potential causes:

These adjustments showed measurable improvement:

-- Fix problematic autogrowth settings
ALTER DATABASE [YourDB] 
MODIFY FILE (NAME = YourDB_Data, FILEGROWTH = 256MB)

-- Clean up MSDB history
EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = '2023-01-01'

-- Optimize temp table usage
-- Before:
SELECT * INTO #Temp FROM LargeTable
-- After:
DECLARE @Temp TABLE (Id INT, ...)
INSERT INTO @Temp SELECT ... FROM LargeTable

For deeper analysis, these queries help identify hidden issues:

-- Check for memory grants waiting
SELECT 
    mg.request_time,
    mg.grant_time,
    mg.requested_memory_kb,
    mg.granted_memory_kb,
    mg.required_memory_kb,
    t.text
FROM sys.dm_exec_query_memory_grants mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) t

-- Monitor plan cache bloat
SELECT 
    objtype,
    COUNT(*) AS num_plans,
    SUM(size_in_bytes)/1024 AS size_kb,
    AVG(usecounts) AS avg_use
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY size_kb DESC

Implement these PowerShell scripts for proactive monitoring:

# SQL Server performance snapshot
$query = @"
SELECT 
    GETDATE() AS collection_time,
    cntr_value 
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Buffer cache hit ratio', 
                      'Page life expectancy',
                      'Batch Requests/sec')
"@

Invoke-Sqlcmd -Query $query -ServerInstance "YourServer" | 
Export-Csv -Path "C:\PerfLogs\SQL_Perf_$(Get-Date -Format yyyyMMdd).csv" -Append