KDA: Echoes of Deception - Case 5
A deleted podcast episode, 17 million archive log entries, and KQL's time series analysis to find the backup that was never supposed to survive.
Posts
58 posts
KDA: Echoes of Deception - Case 4
Someone hacked Digitown's municipality and stole classified documents. 45 million rows of router traffic, an IP lookup table, and KQL's anomaly detection to find who did it.
My Toolbox - AI
I trusted AI to redesign this blog, be my personal assistant, and organize my life. But I won't let it near a production database. Here's why that's not a contradiction - and what I learned about trust along the way.
Inspecting TSQL Abstract Syntax Trees
What does SQL Server actually see when it parses your code? I built a free web tool that lets you paste TSQL and explore the abstract syntax tree - click a token, see the node, read the fragment.
KDA: Echoes of Deception - Case 3
20 stolen cars with swapped license plates - using KQL to trace VIN changes through traffic data and find the common storage location.
KDA: Echoes of Deception - Case 2
Digitown's citizens are being targeted by phishing calls. Using KQL, I analyze call patterns - duration, hidden caller IDs, and disconnect behavior - to unmask the phisher.
KDA: Echoes of Deception - Case 1
Digitown's utility bills doubled overnight. Using KQL and the EXPLAIN feature, I dig through billing data to find duplicate charges and negative consumption.
KDA: Echoes of Deception - Onboarding
A walkthrough of the Kusto Detective Agency UI and the onboarding challenge - finding which detective earned the most bounty money in 2022.
Kusto Detective Agency - Intro
I have a soft spot for Kusto. This series walks through the Kusto Detective Agency challenges - data mysteries you solve with KQL instead of a magnifying glass.
Deploy at Low Priority
A utility script for deploying schema changes without a maintenance window - using a time-based loop, lock checking, and GOTO to minimize blocking on busy SQL Servers.
Why is fn_xe_file_target_read_file the worst SQL function?
A rant about fn_xe_file_target_read_file - the nvarchar(max) return type, broken time filtering, and the circular dependency for finding rollover files.
Finding nested references
Your stored procedure calls another procedure, which calls a view. How deep does it go? Here's a recursive CTE that traces the full dependency chain, including cross-database references.
Database CI/CD Basics (T-SQL Tuesday #177)
A crash course on database CI/CD: source control as the source of truth, state-based vs migration-based versioning, and the many moving parts of automated database deployments.
Service Broker Blocking
I was paged for a blocking chain where the lead blocker had a transaction name I'd never seen: CSbRollbackHandlerTask::DisableQ. Here is how I reproduced it and why the blocked process report refuses to help.
My Toolbox - SSMS
My SSMS setup guide: layout tweaks, essential Options settings, hotkeys I can't live without, and miscellaneous tips for a better SQL Server Management Studio experience.
Async stats update causing blocking
An index rebuild quietly blocked an async stats update, and the whole server started timing out. Here is the lock-partitioning repro and the SQL Server 2022 fix.
Are ABORT_AFTER_WAIT's victims logged?
Does ALTER INDEX REBUILD with WAIT_AT_LOW_PRIORITY log its victims in the error log the same way KILL does? Let's find out.
Extended Events and I (T-SQL Tuesday #166)
Grant Fritchey asked us to talk about our favourite SQL Server tool for T-SQL Tuesday #166. Mine is Extended Events - and I have the GitHub repo and a public speaking session to prove it.
Misleading SSMS Connection
Read a cautionary tale on how I've deceived the SSMS, then promptly forgot about it and, in turn, ultimately confused myself.
Advent of Code 2022
This December I'm solving Advent of Code in two languages at once - SQL and KQL. Here's where I'm posting the code and how far I think I'll get.
Default event_file path for Extended Events
Create an Extended Events session with just a filename and the .xel file lands somewhere - but where? Here's what the default path is and how to change it.
T-SQL Tuesday #156 - Wrap Up
Fifteen SQL bloggers answered one question: what turns ordinary code into production code? Here's what they taught me.
Testing the code (T-SQL Tuesday #156)
I picked the T-SQL Tuesday topic and still struggled to answer it. The quality of production code I value most: it has tests.
Production Code (T-SQL Tuesday #156)
I'm hosting T-SQL Tuesday this November. The question: which secret ingredient turns ordinary code into production-grade code?
Scary Scalar Functions - Part Four: Your Environment
You've learned why Scalar UDFs are slow and how to remove them. The last question: where do you even start? Here's how I triage them across a whole environment.
My Toolbox - Everything
Another productivity tool in my toolbox that I use daily. This time it's about file search. Everything will help you find... well, everything.
Unexpected Blocking during the Indexed View Creation
Here are the chronicles of my struggles to add a large Clustered index to a View without causing an outage.
My Toolbox - Greenshot
Print Screen and Paint never cut it for me. Greenshot did: precise region capture, a real annotation editor, obfuscation, and one-key sharing. Here's the tour.
Scary Scalar Functions - Part Three: The Cure
Nuke them from orbit, or inline them. The practical ways to neutralize Scalar UDF performance pain, from a free SCHEMABINDING win to a full ITVF rewrite.
What's in a name? (T-SQL Tuesday #152)
My T-SQL Tuesday rant: an app that connects as '.Net SqlClient Data Provider' turns debugging into a manhunt. Please, name your apps.
Scary Scalar Functions - Part Two: Performance
A Scalar function that does literally nothing still runs over 20 times slower. Here is the proof, measured four different ways.
What is causing my constraint to be untrusted?
You fix an untrusted constraint, and by morning it's back to untrusted. Here's how to catch the process responsible, using Extended Events.
Scary Scalar Functions - Part One: Parallelism
Consultants hate me for this one weird trick. Because I'll be uncovering all the mess Scalar functions can cause in a series of articles.
Coding Standards (T-SQL Tuesday #151)
My T-SQL Tuesday #151 entry: the SQL habits I swear by, and the ones I'd ban from every codebase if I could.
New Extended Events in SQL Server 2022
SQL Server 2022's CTP has dropped. I diffed its Extended Events against 2019 and pulled out the few new ones worth a look.
Efficient Cheating at Lego Video Games
My daughter's LEGO game makes you re-enter every cheat on a clunky six-dial lock. So I built a TSQL solver to find the shortest path through all of them.
SQL Server Module Signing for Secure Cross-Database Access
I keep relearning how to set up secure cross-database access, so here's the reminder: a diagram plus a full follow-along example you can run yourself.
Why my way of writing SQL is superior
Semicolons, leading commas, single-letter aliases. Here is the SQL style everyone loves to argue about, and the practical reasons I refuse to budge.
Query Hash and Query Plan Hash Mapping
Ever struggled to match the binary hash from DMVs and Query Store against the numeric one in Extended Events? Let's fix that for good.
KEEP PLAN Demystified
Ever wondered what the KEEP PLAN hint actually does? The docs are vague, so I tested it against every recompilation threshold to find out.
Extended Events Timezones
Open an XE event file in SSMS and the timestamps stare back at you. Local time or server time? I spun up a container in another timezone to find out.
The SQLBits 2022, or There and Back Again
My first in-person conference, my first time speaking, and a costume that needed a face mask. A SQLBits 2022 recap told in Lord of the Rings quotes.
SQL Wordle Series - Part Two: Playing
Last time we cheated. This time we actually play: a set-based scoring engine in TSQL, duplicate letters and all.
SQL Wordle Series - Part One: Cheating
Your Twitter feed is probably already full of Wordle posts, so why not one more? Let's start the series the lazy way: by cheating.
Expecting Subvertations
How can 15 be less than 13? A perfect storm of bad assumptions about sql_variant comparison rules.
How to audit data modifications with surgical precision
A colleague needed to find who was changing a specific cell in a busy table. SQL Audit was too noisy, Query Store too vague. I found a way to combine triggers with Extended Events for surgical precision.
IS Lock in RCSI Enabled Database
I found an IS lock in a deadlock report on an RCSI database. That shouldn't be possible. Here's what caused it.
Don't Be Lazy
Sometimes the fastest help is the kind you find yourself. A rundown of the lazy question patterns I keep seeing in SQL help channels.
Short Code Examples (T-SQL Tuesday #143)
This month's T-SQL Tuesday invitation is from John McCormack. I'm sharing a few handy snippets I use on a regular basis - a time loop, impersonation testing, database recreation, and some RegEx for SSMS Find & Replace.
Discover potential obstacles in your design
One query against sys.messages can reveal every edge case SQL Server knows about before you commit to a new design.
Query Store default settings
Query Store comes with default settings - but are they what the documentation says? Let's find out using a simple Extended Events session.
Investigating Errors With Extended Events
Production errors are hard to reproduce. Here's my go-to Extended Events session that captures errors with their full TSQL calling stack.
Out with the old…
WordPress was making me hate blogging. Moving to Hugo, GitHub, and Azure Static Web Apps fixed that.
Work Life Balance (T-SQL Tuesday #141)
Work-life balance has been my focus for the past year and I've already made some changes to my lifestyle. Now it's time to take a look back and evaluate.
Containers and Me (T-SQL Tuesday #140)
It took three tries before containers clicked for me. Each time I learned a bit more, but it wasn't until I had my own project that it all came together.
My Favourite Data Type (T-SQL Tuesday #136)
Brent Ozar asked the SQL community about their favourite data type. My answer surprised even me - sql_variant. Here's why, plus the data type I can't stand.
My Experience Doing the Online Proctored Exam
My impressions right after taking an online proctored exam. There were a couple of problems I ran into. Hopefully, this article will help you prepare.
Generate XML documents efficiently
Believe it or not, there is a wrong and correct way when it comes to generating XML documents in SQL.