DBA Operations Dashboard
Real-time overview of your DBA handbook progress and key operational areas.
Checklist Progress
0%
0 of 0 tasks completed
Scripts Mapped
68
SQL scripts across all categories
Sections Covered
23
Operational areas with checklists
Quick Actions
Section Progress
01. DBA Principles & Production Rules
The foundational mindset for production database administration.
The First Rule of Production DBA
Before changing anything, collect evidence. Never immediately restart, kill, shrink, or change configuration without understanding the situation.
Never immediately: Restart SQL Server, Kill sessions, Shrink databases, Rebuild all indexes, Change configuration, Increase memory, Restart server
First capture: What happened? When did it start? Who reported it? What changed? Is it affecting CPU, Memory, Disk, Network, SQL workload, or Application?
Evidence collection: Always gather DMV snapshots, wait stats, and execution plans before making any changes.
04_Performance_Diagnostics/wait_statistics.sql
The 5-Layer Diagnostic Model
When a problem arrives, never assume SQL Server is the problem. Investigate each layer:
Application Layer
SQL Query Layer
SQL Engine Layer
Operating System Layer
Hardware Layer
02. First Responder Checklist
Step-by-step triage when "the database is slow" alert fires at 2 AM.
Step 1: Is SQL Server Alive?
Can I connect to SQL Server?
SELECT @@SERVERNAME, @@VERSION, GETDATE();
SQL Service is running
SQL Agent is running
Cluster status is healthy
Availability Group status is healthy
06_HA_DR/alwayson_ag_monitor.sql
Step 2: Check Overall Health
Run comprehensive health check
00_Framework/sp_DBA_HealthCheck.sql
Check for unexpected restart, memory changes, CPU configuration changes
01_Server_OS/cpu_utilization.sql
Check memory diagnostics
01_Server_OS/memory_diagnostics.sql
Check disk latency
01_Server_OS/disk_latency.sql
Step 3: Check Current Blocking
Identify blocking sessions
00_Framework/sp_DBA_ActiveSessions.sql
Analyze blocking and deadlock patterns
04_Performance_Diagnostics/blocking_and_deadlocks.sql
Questions: Who is blocking? What is the blocker doing? Is it safe to kill?
Step 4: Check Running Queries
Find top resource-consuming queries
04_Performance_Diagnostics/top_resource_queries.sql
Look for: Long running queries, Massive reads, Large writes, Bad plans
Analyze wait statistics to identify bottleneck
00_Framework/sp_DBA_WaitAnalysis.sql
03. Production Environment Discovery
Essential knowledge when joining a new organization or taking over a new environment.
Production Environment Profile
Fill this out first when taking over a new environment.
Database Inventory
Map all databases: Size, Recovery model, Compatibility level, Owner
00_Framework/sp_DBA_HealthCheck.sql
Document Criticality, RPO/RTO requirements, Backup strategy per database
Check database compatibility levels
02_Instance_Config/database_compatibility_audit.sql
Audit OS integration checks
02_Instance_Config/os_integration_checks.sql
Required Documentation
Server inventory documented
Database inventory documented
Backup policy documented
Recovery document created
Monitoring dashboard configured
Escalation matrix documented
Maintenance window defined
Architecture diagram created
04. SQL Server Configuration Checklist
Validate critical configuration settings that directly impact performance and stability.
Configuration Audit
Run full server configuration audit
02_Instance_Config/server_configuration_audit.sql
Memory Configuration
Max server memory is set correctly (SQL Server should NOT consume all OS memory)
EXEC sp_configure 'max server memory';
Verify no OS paging, slow queries, or application timeouts from memory pressure
01_Server_OS/memory_diagnostics.sql
Parallelism Configuration
MAXDOP is set based on CPU count, NUMA, and workload type
EXEC sp_configure 'max degree of parallelism';
Cost Threshold for Parallelism reviewed (default of 5 is often too low)
EXEC sp_configure 'cost threshold for parallelism';
TempDB Configuration
TempDB has multiple data files, equal size, equal growth, on fast storage
03_Storage_Engine/tempdb_configuration.sql
05. Daily Health Checks
Morning production health check routine for SQL Server environments.
Morning Health Check
Run comprehensive health check stored procedure
00_Framework/sp_DBA_HealthCheck.sql
SQL Service is running
SQL Agent is running
Disk space is adequate
01_Server_OS/disk_latency.sql
CPU usage is within normal range
01_Server_OS/cpu_utilization.sql
Memory pressure check
01_Server_OS/memory_diagnostics.sql
Check for failed SQL Agent jobs
09_Maintenance/failed_jobs.sql
Check for suspect/offline databases
SELECT name, state_desc FROM sys.databases;
SQL Agent job monitor
08_Advanced/sql_agent_job_monitor.sql
Backup Validation
Review backup history
00_Framework/sp_DBA_BackupReview.sql
Verify backup chain integrity
06_HA_DR/backup_log_chain.sql
Verify backup file integrity
06_HA_DR/backup_verification.sql
Verify: Full backup, Differential, Log backup, Restore test
DBA Daily Operational Workflow
Time-based workflow for structured DBA operations across different cadences.
8:00 AM
Morning
Morning
Morning Health Check
- Backup validation - Full, Differential, Log
- Disk space review - All drives
- Failed SQL Agent jobs
- Error log review - Last 24 hours
- Blocking sessions - Current state
- AG/Replication health
- Service status - SQL Server + Agent
During Day
Monitor
Monitor
Continuous Monitoring
- Blocking alerts - Respond immediately
- CPU spikes - Investigate source
- Deadlock tracking - Review patterns
- Storage alerts - Growth monitoring
- Long running queries - Identify outliers
- Application connection issues
Weekly
Maintenance
Maintenance
Weekly Review Tasks
- Index fragmentation review
- Statistics freshness check
- Database growth analysis
- Security review - New logins, permission changes
- Performance baseline comparison
- Capacity trending
Monthly
Strategic
Strategic
Monthly Strategic Tasks
- Capacity planning review
- CU/Security patch review
- DR validation and failover test
- Backup restore test
- Security audit - Full scope
- Configuration drift check
- License compliance review
06. Monitoring Checklist
Continuous monitoring setup and validation for proactive DBA operations.
Active Monitoring
Active sessions monitoring is configured
00_Framework/sp_DBA_ActiveSessions.sql
Extended Events sessions are running
12_Extended_Events/active_xe_sessions.sql
Query Store is enabled and healthy
08_Advanced/query_store_health.sql
Performance baselines are captured
14_Baselines/performance_snapshot.sql
Baseline capture procedure is set up
00_Framework/sp_DBA_BaselineCapture.sql
Error log monitoring is configured
08_Advanced/error_log_and_connectivity.sql
Alert management is configured
preventive_measures/08_Alert_Management.sql
Dashboard views are operational
preventive_measures/09_Dashboard_Views.sql
07. Incident Response Playbook
Structured response procedures for production incidents.
Incident Response Flow
Alert Received / Issue Reported
Collect Evidence (DMV snapshots, wait stats)
Identify the Bottleneck Layer (5-Layer Model)
Root Cause Investigation
Apply Fix
Document RCA & Prevent Recurrence
Incident Priority Model (P1-P4)
Every incident must be classified. Classification drives response time and escalation path.
| Severity | Meaning | Response Time | Escalation | Example |
|---|---|---|---|---|
| P1 - Critical | Database unavailable / Data loss risk | Immediate | DBA + App + Infra + Management | Instance down, AG failover, corruption |
| P2 - High | Major degradation / Service impacted | < 30 min | DBA + App team | Severe blocking, CPU 100%, log full |
| P3 - Medium | Performance issue / Degraded experience | Planned window | DBA team | Slow queries, index fragmentation, growth |
| P4 - Low | Improvement / Optimization | Backlog | DBA team | Config tuning, stats update, cleanup |
Troubleshooting Decision Tree
Interactive diagnostic flow. Scroll horizontally/vertically to navigate. Start at the top and follow the Yes/No path.
Decision
Action
Script
No Path
Yes Path
Application is Slow / Down
Can you connect to SQL Server?
NO
Check SQL Service status
Check Memory / OS
Check Memory / OS
01_Server_OS/memory_diagnostics.sql
YES
Is blocking present?
YES
Analyze blocker chain
Check transaction age
Check transaction age
04_Performance_Diagnostics/blocking_and_deadlocks.sql
NO
Is CPU > 90%?
YES
04_Performance_Diagnostics/top_resource_queries.sql
NO
Check wait statistics
00_Framework/sp_DBA_WaitAnalysis.sql
Production Execution Checklist
Before running any script, verify these items. Check each box to confirm:
Before You Execute
Evidence Collection Module
Collect this data BEFORE making any changes. Use the Incident Snapshot to export all evidence at once.
Incident Snapshot Checklist
Collected Evidence:
Incident Response Checklist
Capture initial evidence before any action
Run health check to establish baseline
00_Framework/sp_DBA_HealthCheck.sql
Check active sessions and blocking
00_Framework/sp_DBA_ActiveSessions.sql
Analyze wait statistics
00_Framework/sp_DBA_WaitAnalysis.sql
Identify top resource-consuming queries
04_Performance_Diagnostics/top_resource_queries.sql
Check for recent changes (deployments, config changes)
Apply appropriate fix and document RCA
08. Performance Troubleshooting
Systematic approach to identifying and resolving SQL Server performance issues.
Before Tuning Checklist
Collect: Query text, Execution plan, Wait stats, IO stats, CPU stats
CPU Issues
Analyze CPU utilization history
01_Server_OS/cpu_utilization.sql
Find top resource-consuming queries
04_Performance_Diagnostics/top_resource_queries.sql
Deep plan cache analysis
00_Framework/sp_DBA_PlanCacheAnalyzer.sql
Plan cache deep dive
04_Performance_Diagnostics/plan_cache_deep_dive.sql
IO & Disk Bottlenecks
Check disk latency
01_Server_OS/disk_latency.sql
Check database file growth patterns
03_Storage_Engine/database_files_growth.sql
Check VLF fragmentation
03_Storage_Engine/vlf_fragmentation.sql
Wait Statistics
Analyze wait statistics to identify bottleneck
00_Framework/sp_DBA_WaitAnalysis.sql
Review wait statistics reference
04_Performance_Diagnostics/wait_statistics.sql
Index & Statistics
Review index usage and fragmentation
00_Framework/sp_DBA_IndexReview.sql
Advanced index analysis
05_Index_Statistics/advanced_index_analysis.sql
Index usage efficiency
05_Index_Statistics/index_usage_efficiency.sql
Physical stats and heap analysis
05_Index_Statistics/physical_stats_and_heaps.sql
Statistics freshness check
05_Index_Statistics/statistics_freshness.sql
Query Store regressions
00_Framework/sp_DBA_QueryStoreRegressions.sql
09. Blocking & Deadlocks
Production checklist for identifying and resolving blocking and deadlock issues.
Blocking Troubleshooting
Check active sessions and blocking chains
00_Framework/sp_DBA_ActiveSessions.sql
Analyze blocking and deadlock patterns
04_Performance_Diagnostics/blocking_and_deadlocks.sql
Deep deadlock analysis with XML deadlock graphs
04_Performance_Diagnostics/deadlock_analysis.sql
Root Cause Investigation
Identify root blocker and transaction age
DBCC OPENTRAN;
Review: Kill session? Application fix? Index improvement? Transaction redesign?
10. Transaction Log Issues
Handling "transaction log full" errors and log management.
Transaction Log Full Checklist
Check recovery model
SELECT name, recovery_model_desc FROM sys.databases;
Find log reuse wait reason
SELECT name, log_reuse_wait_desc FROM sys.databases;
Check VLF fragmentation
03_Storage_Engine/vlf_fragmentation.sql
Verify backup chain is intact (no broken log chain)
06_HA_DR/backup_log_chain.sql
Common Causes
| Cause | Diagnosis | Resolution |
|---|---|---|
| Missing log backup | Check backup schedule | Enable log backups |
| Long running transaction | DBCC OPENTRAN | Kill or fix transaction |
| Replication delay | Check replication status | Fix distribution agent |
| AG sync issue | Check AG dashboard | Resolve sync blocker |
11. Backup & Recovery
Backup strategy validation and disaster recovery readiness.
RPO & RTO Definitions
| Term | Definition | Question |
|---|---|---|
| RPO | Recovery Point Objective | How much data loss is acceptable? |
| RTO | Recovery Time Objective | How quickly must recovery happen? |
Backup Checklist
Review backup history
00_Framework/sp_DBA_BackupReview.sql
Verify backup chain integrity
06_HA_DR/backup_log_chain.sql
Verify backup file integrity (RESTORE VERIFYONLY)
06_HA_DR/backup_verification.sql
Restore test simulation
06_HA_DR/restore_test_simulator.sql
Backup tested and restore verified
DR procedure documented
Failover tested
12. Security Audit
SQL Server security review and compliance checklist.
Security Audit Checklist
Run comprehensive security audit
00_Framework/sp_DBA_SecurityAudit.sql
Login audit
07_Security/login_audit.sql
Authorization audit
07_Security/authorization_audit.sql
Encryption hardening review
07_Security/encryption_hardening.sql
Security Review Items
Excess sysadmin users identified and reduced
Disabled accounts cleaned up
Shared/generic accounts eliminated
SA account review completed
13. High Availability
AlwaysOn AG, Failover, and Replication health checks.
High Availability Checklist
Monitor AlwaysOn AG health
06_HA_DR/alwayson_ag_monitor.sql
Failover readiness validated
Replication health check
08_Advanced/replication_monitor.sql
Log shipping / backup chain validation
06_HA_DR/backup_log_chain.sql
14. Disaster Recovery
DR planning, testing, and validation procedures.
DR Readiness Checklist
RPO/RTO requirements documented per database
Restore test simulation completed
06_HA_DR/restore_test_simulator.sql
DR runbook documented and tested
Failover tested with documented results
Backup verification completed
06_HA_DR/backup_verification.sql
15. Capacity Planning
Proactive capacity monitoring and growth forecasting.
Capacity Planning Checklist
Database growth forecast
10_Capacity_Planning/database_growth_forecast.sql
Database file growth analysis
03_Storage_Engine/database_files_growth.sql
Performance snapshot baseline
14_Baselines/performance_snapshot.sql
Questions: Data growth expected? Index growth? Log growth? Purging strategy?
16. Change Management
Pre-production change validation checklist.
Change Request Checklist
Before Change
Expected downtime documented
Rollback plan documented and tested
Full backup completed before change
Testing completed in non-production
Approval received from stakeholders
Impact assessment completed
After Change
Validation completed successfully
Monitoring enabled and alerts active
Documentation updated
Application connectivity verified
Performance baseline compared
Change ticket closed with results
17. Automation Scripts
DBA automation framework and utility scripts.
Automation Maturity Roadmap
Track your automation journey from manual processes to self-healing infrastructure.
1
Manual Checklist
DBA manually runs scripts. All actions are human-initiated.
Starting point
2
Scheduled Reports
Health checks run on schedule via SQL Agent.
SQL Agent Jobs
3
Alert-Driven
Threshold-based alerts trigger investigation proactively.
Alerts + Dashboards
4
Self-Healing
Automated remediation for known patterns.
Autonomous Ops
Framework Deployment
Deploy DBA Framework (all stored procedures and functions)
00_Framework/00_Deploy_Framework.sql
Deploy via PowerShell automation
00_Framework/00_Deploy_Framework.ps1
DBA Repository database created
00_Repository/DBARepository_Create.sql
Utility Procedures
| Procedure | Purpose | Path |
|---|---|---|
sp_DBA_ActiveSessions | Real-time active session monitor | 00_Framework/sp_DBA_ActiveSessions.sql |
sp_DBA_ForEachDatabase | Run command against all databases | 00_Framework/sp_DBA_ForEachDatabase.sql |
sp_DBA_SaveAssessmentRun | Persist health check results | 00_Framework/sp_DBA_SaveAssessmentRun.sql |
fn_DBA_ExcludedWaitTypes | Filter wait types | 00_Framework/fn_DBA_ExcludedWaitTypes.sql |
fn_DBA_AgentRunDurationSeconds | Calculate job duration | 00_Framework/fn_DBA_AgentRunDurationSeconds.sql |
Preventive Measures
| Script | Purpose | Path |
|---|---|---|
| Governance DB Setup | Create governance database | preventive_measures/01_Create_Governance_Database.sql |
| Running Query Capture | Capture running queries | preventive_measures/02_Capture_Running_Queries.sql |
| Long Running Queries | Monitor long queries | preventive_measures/03_Check_Long_Running_Queries.sql |
| Massive DML | Check massive DML operations | preventive_measures/04_Check_Massive_DML.sql |
| Blocked Apps | Check blocked applications | preventive_measures/05_Check_Blocked_Applications.sql |
| Query Policy | Enforce query policies | preventive_measures/06_Enforce_Query_Policy.sql |
| Extended Events | Setup Extended Events | preventive_measures/07_Setup_Extended_Events.sql |
| Alert Management | Configure alerts | preventive_measures/08_Alert_Management.sql |
| Dashboard Views | Create monitoring views | preventive_measures/09_Dashboard_Views.sql |
| Agent Jobs | Create SQL Agent jobs | preventive_measures/10_Create_SQL_Agent_Jobs.sql |
| Resource Governor | Setup Resource Governor | preventive_measures/11_Setup_Resource_Governor.sql |
18. DBA Case Studies
Real-world production scenarios with resolution approaches.
Scenario 1: "Application is slow"
Check CPU pressure
01_Server_OS/cpu_utilization.sql
Check blocking
04_Performance_Diagnostics/blocking_and_deadlocks.sql
Check expensive queries
04_Performance_Diagnostics/top_resource_queries.sql
Review wait statistics
00_Framework/sp_DBA_WaitAnalysis.sql
Validate execution plan
00_Framework/sp_DBA_PlanCacheAnalyzer.sql
Check recent changes and apply fix
Scenario 2: Blocking Storm
Symptoms: Users cannot save, Queries hanging
Find root blocker using sp_who2 active
Check transaction age with DBCC OPENTRAN
Resolution: Kill session / Application fix / Index improvement / Transaction redesign
Scenario 3: Transaction Log Full
Error: "The transaction log for database is full"
Check recovery model and log reuse wait
03_Storage_Engine/vlf_fragmentation.sql
Common causes: Missing log backup / Long running tx / Replication delay / AG sync issue
Scenario 4: SQL Server CPU 100%
Find top 20 expensive queries
04_Performance_Diagnostics/top_resource_queries.sql
Investigate: Bad plan / Parameter sniffing / Missing index / Data growth
Scenario 5: Database Growth Problem
Find largest tables with sp_spaceused
03_Storage_Engine/database_files_growth.sql
Questions: Data growth expected? Index growth? Log growth? Purging strategy?
19. RCA Templates
Root Cause Analysis documentation template for post-incident review.
Incident Summary
| Field | Details |
|---|---|
| Incident Date/Time | |
| Duration | |
| Severity | |
| Affected Systems | |
| Impact |
Root Cause Analysis
| Field | Details |
|---|---|
| Root Cause | |
| Contributing Factors | |
| Detection Method | |
| Time to Detect | |
| Time to Resolve |
Resolution & Prevention
| Field | Details |
|---|---|
| Immediate Fix | |
| Permanent Fix | |
| Prevention Measures | |
| Monitoring Improvements | |
| Lessons Learned |
Reference Scripts for Investigation
| Investigation Area | Script |
|---|---|
| Health Check | 00_Framework/sp_DBA_HealthCheck.sql |
| Wait Analysis | 00_Framework/sp_DBA_WaitAnalysis.sql |
| Blocking Analysis | 04_Performance_Diagnostics/blocking_and_deadlocks.sql |
| Deadlock Analysis | 04_Performance_Diagnostics/deadlock_analysis.sql |
| Top Resource Queries | 04_Performance_Diagnostics/top_resource_queries.sql |
| Plan Cache | 00_Framework/sp_DBA_PlanCacheAnalyzer.sql |
| CPU Analysis | 01_Server_OS/cpu_utilization.sql |
| Memory Diagnostics | 01_Server_OS/memory_diagnostics.sql |
| Disk Latency | 01_Server_OS/disk_latency.sql |
20. DBA Growth Path
Career development checklist from Junior DBA to Senior DBA.
Junior DBA / 0-2 Years
Understand SQL Server architecture (storage engine, query processor, memory manager)
Master backup and restore operations
Understand DMVs and basic performance monitoring
Learn to read execution plans
Understand SQL Server Agent jobs and maintenance plans
Mid-Level DBA / 2-5 Years
Master performance tuning and query optimization
Understand AlwaysOn, Failover Clustering, Log Shipping
Implement monitoring and alerting frameworks
Master security auditing and compliance
Develop automation scripts (PowerShell, T-SQL)
Senior DBA / 5+ Years
Capacity planning and architecture design
Disaster recovery planning and testing
Change management process ownership
Mentoring junior DBAs and knowledge transfer
Cloud migration and hybrid architecture
21. SQL Server Patch Management
CU and security patch lifecycle management for SQL Server environments.
Patch Assessment
Current CU level documented
SELECT @@VERSION;
Check for latest available CU from Microsoft
Review security patches / CVEs for current version
Compatibility validation completed in non-production
02_Instance_Config/database_compatibility_audit.sql
Application regression testing completed
Patch Application Checklist
Full backup completed before patching
Change ticket approved
Maintenance window confirmed
Application team notified
Rollback plan documented
Post-Patch Validation
SQL Server version verified post-patch
SELECT @@VERSION;
All services running (SQL + Agent)
Application connectivity verified
Run health check post-patch
00_Framework/sp_DBA_HealthCheck.sql
AG/HA health validated
06_HA_DR/alwayson_ag_monitor.sql
Performance baseline comparison
14_Baselines/performance_snapshot.sql
22. Data Corruption Response
Handling database corruption: detection, assessment, and recovery procedures.
Corruption Detection
Run DBCC CHECKDB on affected database
09_Maintenance/last_checkdb_dates.sql
Check SQL Server error log for 823/824/825 errors
08_Advanced/error_log_and_connectivity.sql
Review Windows Event Log for I/O errors
Corruption Assessment
1
Identify Scope
Which database? Which objects? How many pages affected? Is it clustered vs nonclustered index vs heap?
2
Check Backup Chain
Verify last known good backup. Check if log backups are available for point-in-time recovery.
06_HA_DR/backup_log_chain.sql
3
Assess Recovery Options
Can you restore from backup? Is page-level restore possible? Is emergency mode repair the last resort?
4
Decision Point
Preferred: Restore from backup. Alternative: Page-level restore. Last resort: DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS (requires Microsoft support).
Recovery Procedures
Restore from most recent clean backup
Apply log backups for point-in-time recovery if possible
If page restore: identify specific corrupt pages and restore individually
If emergency mode repair needed: document approval from Microsoft Support
Post-recovery: Run DBCC CHECKDB to verify integrity
09_Maintenance/last_checkdb_dates.sql
Investigate root cause: storage failure, power issue, driver bug, memory issue
Increase DBCC CHECKDB frequency if corruption detected
23. Wait Statistics Knowledge Base
Senior DBA reference for interpreting wait types and determining corrective actions.
Wait Type Lookup
PAGEIOLATCH
Meaning: Waiting for physical I/O read/write to complete from disk
Symptoms: Slow queries, high disk latency, timeouts
Check: Disk latency, IO statistics, storage health
Scripts: 01_Server_OS/disk_latency.sql
Fix: Upgrade storage (SSD/NVMe), optimize queries to reduce I/O, review file placement
Severity: High impact
CXPACKET
Meaning: Waiting for parallel query threads to synchronize
Symptoms: High parallelism, skewed thread distribution, CXCONSUMER also high
Check: MAXDOP setting, parallel execution plans, Cost Threshold
Scripts: EXEC sp_configure 'max degree of parallelism';
Fix: Adjust MAXDOP, increase Cost Threshold for Parallelism, fix skewed parallel plans
Severity: Often benign if CXCONSUMER present
CXCONSUMER
Meaning: Consumer thread waiting for producer thread in parallel exchange
Symptoms: Paired with CXPACKET; indicates consumer is slower than producer
Check: Execution plans for parallelism skew
Scripts: 00_Framework/sp_DBA_PlanCacheAnalyzer.sql
Fix: Optimize the query causing skew; usually a query-level issue, not a config issue
Severity: Usually benign
RESOURCE_SEMAPHORE
Meaning: Query waiting for memory grant to start execution
Symptoms: Queries queued, memory pressure, query timeouts
Check: Memory grants pending, available memory, large queries
Scripts: 01_Server_OS/memory_diagnostics.sql
Fix: Reduce memory-intensive queries, increase max server memory, fix parameter sniffing
Severity: High impact
WRITELOG
Meaning: Waiting for transaction log write to complete
Symptoms: Slow commits, high log file I/O, latency on log drive
Check: Log file latency, VLF count, transaction activity
Scripts: 01_Server_OS/disk_latency.sql | 03_Storage_Engine/vlf_fragmentation.sql
Fix: Move log to faster storage, reduce large transactions, fix VLF fragmentation
Severity: High impact
LCK_M_* (All Lock Waits)
Meaning: Waiting to acquire a lock of specific type (S, X, U, IX, etc.)
Symptoms: Blocking chains, timeouts, application delays
Check: Blocking sessions, lock escalation, transaction isolation level
Scripts: 00_Framework/sp_DBA_ActiveSessions.sql | 04_Performance_Diagnostics/blocking_and_deadlocks.sql
Fix: Optimize blocking queries, add indexes, review isolation levels, consider lock escalation
Severity: High impact
PAGELATCH_UP / PAGELATCH_EX / PAGELATCH_SH
Meaning: Waiting for in-memory latch on a data page (not disk I/O)
Symptoms: High contention on specific pages, often TempDB allocation
Check: Contention on alloc pages, TempDB page contention
Scripts: 03_Storage_Engine/tempdb_configuration.sql
Fix: Add TempDB files, enable TF-1118, optimize TempDB-heavy operations
Severity: Medium impact
SOS_SCHEDULER_YIELD
Meaning: Task voluntarily yielded the scheduler, waiting to be rescheduled
Symptoms: High when many CPU-bound queries, often with high CPU usage
Check: CPU utilization, top CPU-consuming queries
Scripts: 01_Server_OS/cpu_utilization.sql | 04_Performance_Diagnostics/top_resource_queries.sql
Fix: Optimize CPU-heavy queries, reduce unnecessary compiles, review parallelism
Severity: Medium impact - investigate when CPU is high
LATCH_EX / LATCH_SH / LATCH_UP
Meaning: Waiting for a non-page latch (internal SQL Server structure protection)
Symptoms: Metadata contention, tempdb contention, or buffer pool issues
Check: Latch breakdown by class, identify hot pages
Scripts: 03_Storage_Engine/tempdb_configuration.sql
Fix: Depends on latch class - often related to memory, tempdb, or schema contention
Severity: Variable
ASYNC_NETWORK_IO
Meaning: SQL Server waiting for the client application to consume results
Symptoms: Large result sets, client application slow to process, chatty applications
Check: Application-side processing, network latency, result set sizes
Scripts: 00_Framework/sp_DBA_ActiveSessions.sql
Fix: Optimize application code, reduce result sets, use pagination, check network
Severity: Application-side issue
PREEMPTIVE_OS_*
Meaning: SQL Server calling an OS function and waiting for it to complete
Symptoms: Slow backup/restore, slow DBCC, authentication delays
Check: Identify specific PREEMPTIVE_OS wait subtype
Scripts: 00_Framework/sp_DBA_WaitAnalysis.sql
Fix: Depends on OS function - may indicate storage, network, or OS-level issue
Severity: Variable
WAITFOR / WAITFOR_DELAY
Meaning: Explicitly programmed wait (not a performance issue by itself)
Symptoms: Appears in wait stats but is intentional
Check: Review queries using WAITFOR statement
Scripts: 00_Framework/sp_DBA_WaitAnalysis.sql
Fix: Usually benign; review if excessive and tied to specific applications
Severity: Usually benign
Script Explorer
Searchable catalog of all SQL scripts in the repository.
| Script | Category | Description | Risk | Prod Safe | Environment | Path |
|---|