Category: Performance Tuning
-
SQL Server MAXDOP and Cost Threshold: What To Set, and Why 5 Is Not a Recommendation
Two settings decide when SQL Server splits a query across processors, and both ship with values you are probably meant to change. Microsoft’s current MAXDOP table, what they actually say about cost threshold, and why CXPACKET is no longer the evidence it used to be.
Written by
-
DBA Scripts: Get Lock Escalation, Contention Analysis, and Blocking Chains with Plan
🔧Part of the DBA-Tools Project, copy/paste SQL Server scripts and health checks.In: Performance & Troubleshooting › Blocking & Locking Three Different Views of the Same Underlying Problem Blocking Chains shows what’s blocked right now. These 3 scripts go wider and deeper: Get-LockEscalationStats finds which tables have converted row/page locks into full table locks (a common,…
Written by
-
DBA Scripts: Get Last DBCC CHECKDB
🔧Part of the DBA-Tools Project, copy/paste SQL Server scripts and health checks.In: Maintenance & Automation DBCC CHECKDB is the only thing that actually confirms a database’s data is structurally sound; backups only prove a database can be restored, not that what gets restored is undamaged. Skipping it, or letting it silently stop running, means corruption can…
Written by
-
SQL Server Wait Types Library
⏳The complete SQL Server Wait Types Library, every wait type explained, with a verdict on whether it is worth investigating or normally just noise.Related pillar: Performance & Troubleshooting Wait statistics are the single best first step in any SQL Server performance investigation. Every time a query waits on a resource, whether that’s a lock, disk…
Written by
-
DBA Scripts: Get Error Log Patterns
🔧Part of the DBA-Tools Project, copy/paste SQL Server scripts and health checks.In: Performance & Troubleshooting Group the Error Log Instead of Scrolling It The SQL Server error log is where the instance tells you what’s wrong, but on a busy server it’s also where hundreds of routine startup, backup, and checkpoint messages bury the handful…
Written by
-
DBA Scripts: Generate Collector Alerts
🔧Part of the DBA-Tools Project, copy/paste SQL Server scripts and health checks.In: Maintenance & Automation › Collectors & Baselines Alert on What Your Collectors Find Your collectors fill tables every few minutes and nobody reads them. Generate-CollectorAlertJob.sql writes one SQL Agent job that checks the latest collector data every 30 minutes, lists every CRITICAL and…
Written by
-
DBA Scripts: Index Maintenance
🔧Part of the DBA-Tools Project, copy/paste SQL Server scripts and health checks.In: Maintenance & Automation Index problems rarely announce themselves. A missing index shows up as a slow query someone eventually complains about; a duplicate or unused one just sits there costing write overhead and storage with nothing to show for it; fragmentation quietly degrades…
Written by
-
DBA Scripts: Blocking and Locking
🔧Part of the DBA-Tools Project, copy/paste SQL Server scripts and health checks.In: Performance & Troubleshooting “The database is slow” and “the database is blocked” look identical from the outside, timeouts, a frozen application, users complaining, and the fix is completely different depending on which one it actually is. Four scripts on this site cover a…
Written by
-
DBA Scripts: Get Open Transactions
🔧Part of the DBA-Tools Project, copy/paste SQL Server scripts and health checks.In: Performance & Troubleshooting › Blocking & Locking The Transaction Nobody Remembered to Close An open transaction that never commits or rolls back is one of the quietest ways SQL Server gets into trouble: it holds locks other sessions wait on, it stops the…
Written by
-
DBA Scripts: Get Blocking Summary
🔧Part of the DBA-Tools Project, copy/paste SQL Server scripts and health checks.In: Performance & Troubleshooting › Blocking & Locking When Everything Looks Busy But Nothing Is Moving Blocking is one of the most common causes of a SQL Server that suddenly looks frozen. Applications start timing out, users report that everything has stopped responding, and…
Written by
- SQL Injection in Dynamic SQL: Use sp_executesql
- RESOURCE_SEMAPHORE When SQL Server Is Nowhere Near Its Memory Cap
- Dynamic Data Masking: What a WHERE Clause Still Reveals
- DBA Scripts: Get Log Shipping Status
- SQL Server Row-Level Security: The Filter Does Not Stop Cross-Tenant Writes
- View the Definition of a Stored Procedure in SQL Server
- Backups & Recovery (17)
- DBA Scripts (152)
- High Availability (HA) (13)
- Installation & Configuration (49)
- Maintenance (25)
- Migration & Upgrades (14)
- Monitoring (9)
- Performance Tuning (37)
- Security (Encryption & Permissions) (22)
- Storage & Capacity (16)
- T-SQL Fundamentals (15)
- Troubleshooting (95)
- Wait Types (235)