How to safely and surgically restore filegroups
Most DBAs are familiar with full and differential ...
2025-09-02 (first published: 2025-08-01)
949 reads
Chandan Shukla
What is Delayed Durability in SQL Server — And Should You Turn It On?
Learn about delayed durability in SQL Server and how it might help you with a heavily loaded server.
2025-08-29
8,627 reads
From Rows to Pages: The Hidden Chaos Behind SQL Server’s Sampling Methods
Learn about the TABLESAMPLE option in T-SQL and uncover some of the pitfalls of assuming this works as you think it does.
2025-08-22
2,150 reads
How a Legacy Logic Choked SQL Server in a 30-Year-Old Factory
When a SQL Server Express-based factory app started crawling, the culprit wasn’t hardware or network — it was a decades-old WHILE loop migrated from C/C++ to SQL. This real-world story breaks down how procedural habits, memory grants, and lack of window functions nearly derailed a production floor.
2025-07-28
14,422 reads
We Gave Memory-Optimized Tables a Hash Lookup — Then Tried Pattern Matching Instead
Introduction It was the week before Black Friday — the biggest online ad rush of the year. Our US-based ad-tech platform was gearing up for an insane traffic spike. Hundreds of real-time campaigns were about to go live across multiple brands, each with thousands of user sessions flowing through our system. Every incoming user impression […]
2025-07-22
2,428 reads
When INCLUDE Columns Quietly Inflate Your Transaction Logs
In this article, I wanted to test a common assumption we DBAs make – that adding INCLUDE columns to indexes is harmless. I created a FULL recovery test database with a realistic wide Orders table containing extra large VARCHAR columns to simulate an ERP workload. I ran updates and measured transaction log backup sizes before and after adding INCLUDE columns to a nonclustered index. The results shocked me. The update without INCLUDE columns generated a 10 MB log backup, while the same update with INCLUDE columns produced over 170 MB – a 17x increase in log volume. I explain why this happens: INCLUDE columns are physically stored in index leaf rows, so updates affecting them write bigger log records. I also clarify that updating key columns generates even more log than INCLUDE updates because it involves row movement (delete + insert), but INCLUDE updates still cost more log than if those columns weren’t indexed at all. The takeaway is clear – INCLUDE columns are powerful, but they silently increase transaction log generation, impacting backup sizes, replication lag, and DR readiness. Always measure their real cost before deploying to production.
2025-07-18
713 reads
Unmasking CXPACKET and CXCONSUMER in SQL Server: What Your Execution Plan Isn’t Telling You
This article dives deep into cxpacket and cxconsumer in sql server, explaining how to simulate each, when they appear, and why they matter. Using live execution plans, wait monitoring, and worker thread diagnostics, we uncover how uneven parallelism triggers thread sync waits—and how SQL Server sometimes hides real issues behind innocent-looking CXCONSUMER waits. Includes step-by-step queries, tuning tips, and a real-world scenario where repartition streams quietly ruined performance.
2025-07-07
4,073 reads
The Hidden Cost of MAXDOP: CPU Spikes, Query Slowdowns, and What We Learned
Misusing MAXDOP can silently kill performance across your SQL Server. In this deep dive, we uncover how one bad query caused CPU meltdown, run real-world tests, and show how tuning—not parallelism—often holds the true fix.
2025-06-23
4,613 reads
Analyzing Tempdb Spills and Usage Across Recursive Queries
This article examines how tempdb is affected by recursive queries, using a few different methods.
2025-05-23
2,132 reads
How to Safely Remove a SQL Server Data File Without Downtime
Learn how to safely remove a SQL Server .ndf data file without any downtime using DBCC SHRINKFILE (EMPTYFILE). This hands-on tutorial walks through real-world Azure-based setup, data redistribution, and storage cleanup — ideal for DBAs managing enterprise SQL Server environments.
2025-05-16
2,795 reads
Blogs
Finding and Updating Duplicate IDs: #SQLNewBlogger
By Steve Jones
Finding duplicates was an interview question for me years ago, and I’ve never forgotten...
Optimising Costs: Strategies for Efficient Cloud Resource Management
By HeyMo0sh
Over time, I’ve realised that one of the hardest parts of cloud management isn’t...
Cost Visibility: Tracking and Analysing Your Cloud Spend
By HeyMo0sh
One of the biggest challenges I’ve faced in cloud operations is maintaining clear visibility...
Forums
Fun with JSON II
Comments posted to this topic are about the item Fun with JSON II
Changing Data Types
Comments posted to this topic are about the item Changing Data Types
Answering Questions On Dropped Columns
Comments posted to this topic are about the item Answering Questions On Dropped Columns
Question of the Day
Fun with JSON II
I have some data in a table:
CREATE TABLE #test_data
(
id INT PRIMARY KEY,
name VARCHAR(100),
birth_date DATE
);
-- Step 2: Insert rows
INSERT INTO #test_data
VALUES
(1, 'Olivia', '2025-01-05'),
(2, 'Emma', '2025-03-02'),
(3, 'Liam', '2025-11-15'),
(4, 'Noah', '2025-12-22');
If I run this query, how many rows are returned?
SELECT t1.[key] AS row,
t2.*
FROM OPENJSON(
(
SELECT t.* FROM #test_data AS t FOR JSON PATH
)
) t1
CROSS APPLY OPENJSON(t1.value) t2; See possible answers