Blog

Indexing

First things first: pick up that filthy keyboard, take it over to the trash can, turn it upside down, and give it a good, vigorous shake.

Next, go to your biggest SQL Server and run:

EXEC sp_BlitzIndex @GetAllDatabases = 1,
  @Mode = 2, @SortOrder = 'rows';

1 2	EXEC sp_BlitzIndex @GetAllDatabases = 1, @Mode = 2, @SortOrder = 'rows';

This will give you an inventory of all of the objects in your databases, ordered from biggest to smallest. Here’s what to look for:

Ask simple questions about the 10 biggest tables. Are they supposed to be this large, and contain this much history? Do we have history-deletion jobs that are supposed to be running, but aren’t? I swear, this happens at almost every client I work with! By simply cleaning out the unused, unwanted history, we can make our maintenance jobs like DBCC CHECKDB run faster.

Look for objects with names like Backup_ or Temp_ or ToBeDeleted. We’ve all done a quick backup by copying data into another table, and then accidentally forgotten to go back and delete it afterwards. Check the Reads column to make sure it’s 0, and then rename the objects with a prefix like __To_Be_Dropped_On_20250425. Over the coming days, make sure you’ve got a good copy of the backup so that you can undelete those objects if necessary, and then on April 25, do the needful. (And this time, actually drop them – set yourself a calendar reminder.) Again, the goal here is faster maintenance jobs.

Next, look for indexes with Reads = 0, and Writes > 0. These are indexes that are slowing down your inserts, updates, and deletes, and they don’t have the payoff of making select queries go faster. Script out their definitions in an undo script so you can recreate ’em if necessary, and then drop ’em. (I’m not a fan of disabling them because I’ve seen too many homemade index maintenance scripts that simply rebuild indexes without checking the disabled status, and when you rebuild a disabled index, it goes back into place.)

Finally, look for tables with Reads = 0 and Writes = 0. Do we have old versions of tables lying around that used to be part of the production app, but no longer are? Do we still need those?

This work only takes an hour or two, and it reduces dead weight that’s lengthening your backups, DBCC CHECKDB, statistics updates, and more.

Updated First Responder Kit and Consultant Toolkit for April 2025

Last Updated April 14, 2025

First Responder Kit Updates

This quarter’s release contains a few advanced warnings & tools, like table input for sp_BlitzLock, new warnings about paused resumable index operations that will be deleted automatically by SQL Server 2022, and a new option to maintain carriage returns and line feeds in sp_BlitzCache’s output.

How I Use the First Responder Kit — Wanna watch me use it? Take the free class.

To get the new version:

Download the updated FirstResponderKit.zip
Get The Consultant Toolkit to quickly export the First Responder Kit results into an easy-to-share spreadsheet
PowerShell users: run Install-DbaFirstResponderKit from dbatools

Consultant Toolkit Changes

Updated to this month’s First Responder Kit, but no changes to the spreadsheet template.

sp_Blitz Changes

Enhancement: added warning for non-default database scoped configuration changes for SQL Server 2022’s options, like PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES. (#3620, thanks Reece Goding.)
Fix: @OutputType = ‘markdown’ would fail if the output had an ampersand in it. (#3610, thanks Roger Dawkins.)

sp_BlitzCache Changes

Enhancement: new @KeepCRLF parameter lets you retain the carriage returns & line feeds in query text if you want. (#3611, thanks Vlad Drumea.)

sp_BlitzIndex Changes

Enhancement: added warning for paused resumable index operations. (#3609, thanks Reece Goding.)
Enhancement: added warning for nonclustered indexes on temporal table history tables, which can break merge statements. (#3618, thanks Reece Goding.)

sp_BlitzLock Changes:

Enhancement: add table mode input. New parameters for @TargetDatabaseName, @TargetSchemaName, @TargetTableName, etc. (#3614, thanks Erik Darling!)

sp_DatabaseRestore Changes:

Fix: really long database names would cause the script to fail. (#3605, thanks Philippe Meunier.)

For Support

When you have questions about how the tools work, talk with the community in the #FirstResponderKit Slack channel. Be patient: it’s staffed by volunteers with day jobs. If it’s your first time in the community Slack, get started here.

When you find a bug or want something changed, read the contributing.md file.

When you have a question about what the scripts found, first make sure you read the “More Details” URL for any warning you find. We put a lot of work into documentation, and we wouldn’t want someone to yell at you to go read the fine manual. After that, when you’ve still got questions about how something works in SQL Server, post a question at DBA.StackExchange.com and the community (that includes me!) will help. Include exact errors and any applicable screenshots, your SQL Server version number (including the build #), and the version of the tool you’re working with.

The Waitlist is Open Now for My SQLBits Workshop!

Last Updated April 13, 2025

SQLBits

I’m excited to return to London for SQLBits again this year!

I’ve written about why SQLBits is my favorite conference, and it’s hard to believe it’s been over a decade since I wrote that post. I still feel the same way about Bits – it’s fun, different, and unafraid to run big experiments.

This year, they’re running a BIG experiment: the conference runs Wednesday, Thursday, Friday, and Saturday. You’re used to pre-conference and post-conference all-day workshops running before or after the conference, but that’s not how it goes this year. This year, the workshops are during the conference!

My workshop is all day Thursday. That means if you choose to attend mine, you’re going to miss the Microsoft keynote (which is usually just marketing stuff anyway), and you’re going to miss the general sessions that run on Thursday. I know that’s going to be a tough choice for folks – you’re going to have fear of missing out (FOMO) no matter what you choose.

Unfortunately, my workshop sold out as soon as registration opened – before I could even get a blog post out – but they’re running a waitlist. Register for the conference, use coupon code ozar102025 to save 10% on your registration, and after you register, email contactus@sqlbits.com with the email address you used for registration, and tell them that you want to join the waitlist for my session. They’re looking at adding my workshop on another day – and if they do, the space will instantly get sold out to the people on the waitlist.

Thursday Workshop:
Faster, Cheaper Cloud Databases

You’re managing databases that live in cloud whether it be AWS, Azure, and Google, and you’re getting a lot of questions about slow performance and crazy costs. Your users want everything faster and cheaper, and you’re not finding good answers.

Join me, Brent Ozar, for a day of real-time demos, honest war stories, and practical fixes. I’ll show how I use the First Responder Kit to assess the bottleneck, mitigate as much as I can with index and query tuning, and then write up a business case for management for better future budgeting.

Throughout the day, I’ll include real-life stories from my cloud clients, with names changed to protect the innocent. I’ll explain what worked, what didn’t, and what went up in expensive flames.

I’ll be using different flavours of SQL from across the clouds like Azure SQL DB Managed Instance and Amazon RDS SQL Server to highlight how each has nuances but also lots of similarities

You’ll learn:

How Brent uses the First Responder Kit to assess a cloud SQL Server’s wait types
Why each cloud vendor’s VM quirks make performance tuning tricky, and how to adapt
How to fix blocking, logging, and CPU issues when the hardware isn’t yours
How to write a persuasive performance report for management to fairly lay out where the problem is, and what steps to take next

By the end of the day, you’ll be armed with real-world techniques for keeping your cloud databases faster, cheaper, and more future-proof.

Space is limited, and you can register here. See you in London!

[Video] Office Hours in North Bay, Canada

Last Updated April 6, 2025

Every year, Aaron Bertrand runs a curling tournament up in scenic North Bay where he grew up. I joined in this year, and before the tournament, sat on the shores of Lake Napissing to answer your top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

00:00 Start
01:25 MyTeaGotCold: Kendra Little’s recent article on the decline of SQL Server scared me and it matches much of what I’ve been thinking privately. I’m in my late 20s and heavily specialised into SQL Server. Should I be worried?
04:03 sqlknitter: I have a chunky 17TB database, largest table 2TB (1TB IDX). CheckDB takes days to run (5 days in, 27% complete per sp_whoisactive), with a weekly reboot. How can I tell which tables have actually been checked, before I make the decision to refactor the job to use checktable?
06:11 Smart Python: What metrics can I use to determine if my manual failover AG can safely be promoted to automatic failover?
07:31 Always Query With TABLOCKX: I have an app that churns temp tables outside tempdb so often that schema locks are my biggest problem. I can ask devs to not create temp tables this way if I back the reason with stats—but how do I gather stats on something blocked for minutes yet only used for seconds?
08:45 SaintBear: What’s your opinion of the SQL upgrade assessment functionality in SSMS21?
09:08 Mike: Hi Brent! You mentioned you go to bed and get up very early. How and why you started doing this, were there any specific reasons or benefits for you ?
09:52 Dick_Bowen: I restored a sql 2008 db to sql 2022 and the size doubled. What would cause this to happen?
10:40 Venkat: What is your opinion of Uber’s new QueryGPT for converting natural language to SQL queries?
11:32 Vinneet: Is throwing more hardware at DB performance problems more likely in PostgreSQL than In SQL Server? What are your thoughts?
12:29 SilentKnight: What’s your take on standards other than “good to have”. What effort would you expend to enforce standards on an environment that is very inconsistent. e.g. DB locations, physical file names, table names. PROD: 30 srv 300 DBs of similar design (table structures, SPROCs, views)

Tap Tap Tap – Short, Silent SQL Server Tutorials

Last Updated April 7, 2025

One of the things I love about TikTok is that it exposes me to wildly new video ideas like this:

@tiipititap

? original sound – tiipititap

There’s a whole genre of videos in that style – people using tapping plastic pointer fingers on a grainy screen with no voiceover. One sub-genre (like the video above) likes to draw your attention to something that seems bad, only to have wild plot twists or last-minute surprises. Other sub-genres purport to “analyze” something completely obvious. I laugh way too hard at these, and the comments are usually hilarious too.

So, I thought to myself, hey, these look relatively easy to film – why not try it with SQL Server?

@brentozarultd

Why you should use TOP 100 or below, not TOP 101 or higher #sqlserver #sql #dba #database #azure #microsoft #tips #brentozar #pollgab #computerscience #computerconsulting #database #databaseadministrator #careergoals #spanishsubtitles #Postgres #Postgressql

? original sound – Brent Ozar Unlimited

@brentozarultd

Next title: Why you probably shouldn’t be using NOLOCK #sqlserver #sql #dba #database #azure #microsoft #tips #brentozar #pollgab #computerscience #computerconsulting #database #databaseadministrator #careergoals #spanishsubtitles #Postgres #Postgressql

? original sound – Brent Ozar Unlimited

I’ve put several of ’em on our LinkedIn page, TikTok channel, YouTube shorts, and Instagram reels so you can follow me wherever you like to get your social media.

My home office filming setup is pretty amusing. I put my phone on a video tripod so that it’s held in place, but I can smoothly turn it from left to right – useful for when I want to run multiple demos in the same video.

I hope y’all enjoy these as much as I love making ’em. I giggle to myself whenever I set these up!

Free Webcast: Avoiding Deadlocks by Query Tuning

Last Updated April 7, 2025

Deadlocks used to scare me.

I’d get the alert from a monitoring tool, and I’d look around to see if anyone was watching me. If the coast was clear, I’d… delete the notification.

Deadlocks were a mystery to me because I didn’t know how I was supposed to fix them. Sometimes the same queries could cooperate, and sometimes they couldn’t? The database server decided which one could pass, and which one would fail? What was I supposed to do about any of that? Besides, most of the time it wasn’t a problem – apps retried their queries, or users just ignored error messages and tried the process again.

But eventually, when I became a consultant, I had to confront my ignorance head-on and do something about it because some clients were having serious deadlock issues that they couldn’t ignore. I got help from smart people like Kendra Little, Erik Darling, and Jeremiah Peschka. I solved those client issues, claimed victory, and today deadlocks don’t scare me at all.

You can solve deadlocks in (at least) 3 ways:

Having enough indexes to make your queries go fast, but not so many that they cause problems for inserts, updates, & deletes
Using the right isolation level for your app’s needs, which typically isn’t the default – it’s RCSI
Or worst case, tuning your queries

I say worst case because tuning your queries is usually the most labor-intensive option. It requires knowing the queries involved in the deadlocks, being able to understand what about them is causing a problem, and knowing how to rewrite/reorder them in a way that’ll avoid deadlocks.

In this free session on April 29th sponsored by Idera, I’ll show you why your queries need to work through tables in a consistent order while minimizing the number of times you touch a table. I’ll use a real-world deadlock example and rewrite the query to resolve the issue. Whether you’re a DBA or developer, you’ll walk away with practical strategies you can apply immediately to make your queries deadlock-resistant and your applications more resilient.

If you can’t make it live, Idera will email registrants the recording later to watch on their own time. See you there!

Free Webcast Tomorrow: Fundamentals of Columnstore

Last Updated April 6, 2025

It’s time for SQL Server Spring Training! I’m teaching 60-90 minutes of my most popular classes for free over the next few months.

Tomorrow I’m teaching Fundamentals of Columnstore, and we’re going to be covering the internals of how SQL Server and Azure SQL DB store data in columnstore indexes. You’ll learn why people think it’s an index on every column, but in reality, it’s something wildly different.

There’s a lot more to cover in columnstore, like how columnstore indexes are rebuilt, why it’s so important to do index maintenance on columnstore indexes (unlike rowstore), the differences between clustered and nonclustered columnstore indexes, and why partitioning makes so much sense for columnstore indexes. We cover those in our full Fundamentals of Columnstore class.

If you’ve never attended a formal SQL Server training class before, let’s fix that and get you started for free tomorrow morning. Register now and I’ll see you at 11AM Eastern, 10AM Central, 8AM Pacific. The webcast will not be recorded – be there, be square, or buy the full class for $89.

Free Training Registration

"*" indicates required fields

Facebook

This field is for validation purposes and should be left unchanged.

Name*

First Last

Email*

Things I want*

Free Training Class Invites

Subscribe to Blog Posts (3-4 per week)

[Video] Office Hours in My Vegas Backyard

Last Updated March 16, 2025

I’m doing a lot of travel this month, but I took a break in my Vegas backyard to go through the top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

00:00 Start
00:29 Nick H: I’m a new starter at an organisation who have lots of rules, one of which is that the use of Intellisense is forbidden. I asked why and was told that this was a decision taken “a long time ago” and is due to some apparent negative impact on servers. Is there any truth in this?
01:28 Adrian: Hi Brent, have you ever recommended Capture Data Change or Change Tracking to your customers? If yes, what for? What is your opinion about these features? I have an internal team pushing for this to replicate the data to a different system and I am not sure it is a good idea.
02:18 Frozt: What can you say about SQL 2025? do you recommend upgrading from 2016 to 2025 or you recommend 2022 for stability? Any SQL 2025 gotchas we should think about and are you excited with something for SQL 2025? Thank you
03:43 Snowman: I recently learned that you can only take copy-only backups on AG secondaries. What is the use case for this? I don’t see how it reduces my need to backup the primary.
04:14 Dopinder: Is it worth it to be a dual stack DBA (MSSQL / Postgres)? How do you keep them separate in your brain?
05:18 sqlpadawan: Came here after your post about your sexuality. It takes guts to bring that subject publicly and receive the virtual stones. Q: How was “that” conversation with your ex-partner? Are you still close/friends? Have you lost clients/followers/friends after sharing that in public?
06:12 NikTrs: When corruption happens on non clustered indexes or non important tables which can be restored from backups, is it safe to drop the objects and recreating them to remove corruption instead of restoring the database from backup
07:29 Sysadmim: Is there a way to remove cached statistics on temp tables?Trying to solve an issue with skewed row-count on temp table by 1 odd-out execution resulting in massive scans, I’m not able to change the stored procedure to add an option recompile or update stats on the said temp table.
07:46 Trapped in the cloud: Do you still distribute “SQL Server Performance Tuning in Google Compute Engine”? It’s not in the First Responder Kit, your website, or Google’s.
08:33 Uncle: In terms of SQL DB Backups, is it better to rely on Enterprise Backup solution like Networker or Veritas (knowing it requires sysadmin, and the DBA does not have access to it) or have conventional (Ola’s script) backups to a network share and enterprise backup the shared drive?
09:40 MyTeaGotCold: I’ve never seen anyone talk about Contained Availability Groups since they were announced. Were they any good?
10:37 RoJo: Can you discuss your current guidelines for Antivirus on the SQL server. Some say don’t do it at all and harden access to it so nothing gets in. Agents can make SQL slow. Where is your balance. Trying to solve security vs performance. (remembering CloudStrike-shudder)
11:39 Mr. Zbd409: Is there any reason to keep data forever? With cheap storage (archive tier azure) , I feel like this is a consideration for businesses. is there any reason to delete data? Is it cheaper to store archive data in json rather than proprietary sql server backup file?
12:30 Bandhu: What are your pros / cons for implementing multi – tenancy at the server level (each tenant has their own database server)? Do you see this much in the field?
13:00 30-years-till-retirement: I’m about 10 years into my data career. I’ve heard some SQL MVPs on a podcast say it takes 3-6 months to find a new job. I’d assume it was 6 weeks at most. Is this a case of me having low standards for future employers? In case it wasn’t clear, I’m not a SQL MVP!
14:45 Ricardo: We use chromebooks instead of windows laptops. Whats the best way to administer cloud based (mostly azure) sql servers?
15:02 What’s the Problem You’re Trying to Solve?: “My friend” recently ran sp_BlitzIndex on a table with 1.5 M rows, result: 450 indexes, mostly “IN-MEMORY/HYPOTHETICAL” with no usage. Also 5,200 Statistics, mostly Last Updated 5 years ago. Both indexes and stats are named with “_dta” prefix. Should he be worried?
16:32 Mike: How do you treat people who do not use “sp_” prefix when creating their stored procedures ? Is it OK for you, or you kind of strict and think everything should be with a prefix ?

Fundamentals Week 2025 is Coming This Month! Get Your Tickets Now.

Last Updated February 28, 2025

Conferences and Classes

You’ve attended some of my free online streams, read my blog posts, and you use the First Responder Kit. You enjoy my laid-back, humorous approach to sharing what I’ve learned over the years.

You love live classes. You’ve tried watching recordings or stepping through demos yourself, but…you just can’t block out the time, and you can’t stay motivated while you’re watching a recording. You like the fast-paced energy of seeing me live, asking me questions, and seeing me respond to your chats.

You hate free conferences that feel like Zoom meetings. You’ve tried attending a few online events, but they felt just like sitting through one meeting after another. They didn’t dive deeply into any one subject – they just hopped around from one topic to another, and many of those topics just weren’t relevant to you. They were good for free stuff, but…

You’re ready for Fundamentals Week! You’ll spend the week with me learning about indexes, query tuning, columnstore, how I use the First Responder Kit, and more.

The conference dates are April 22-25, 2025, and classes are 11AM-5PM Eastern, 8AM-2PM Pacific. Here’s what we’ll cover:

Register now for $1,595. See you in class!

What’s New in SQL Server 2025

Last Updated April 1, 2025

Humor

This year’s release of Microsoft’s enterprise-grade relational database focuses on the most highly requested features from customers. Highlights include:

Tooling support for SSMS like adding the debugger back in and executing the query where your cursor is on
Production DBA enhancements like the ability to restore a single table from backup, and automatic background corruption checks
New investments in SQL Server Reporting Services, like a report viewer for .NET Core
T-SQL enhancements like DISTINCT support in STRING_AGG and enhanced NoSQL support

Microsoft’s Corporate Vice President of Azure Data, Rohan Kumar, explained in an interview for the Data Exposed podcast, “For years, we’ve been telling folks to post their feature requests at feedback.azure.com, and to upvote the features they found most compelling. We’ve heard their requests loud and clear, and we’re proud to announce that SQL Server 2025 delivers on what customers have really been asking for.”

This approach marks quite the departure from the last few versions, in which the flagship features like Machine Learning Services, Ledger (blockchain-ish history), and Big Data Clusters have suffered from dramatic adoption problems. “We learned from our mistakes, and we know database engine competition is tougher than ever,” Rohan explained in the podcast. “We know we can’t afford to just slap AI on it and call it a day.”

When asked about pricing and feature limitations on Standard Edition, Rohan couldn’t promise anything, but he did say, “We’ve heard from customers loud and clear that SQL Server price increases have to stop, and that today’s hardware has outpaced our restrictions on Standard Edition. We’ll have information closer to release, and I think customers will be very excited about our willingness to compete.”

Free Webcast Tomorrow: Fundamentals of TempDB

Last Updated March 23, 2025

It’s time for SQL Server Spring Training! I’m teaching 60-90 minutes of my most popular classes for free over the next few months.

Tomorrow I’m teaching Fundamentals of TempDB, and we’re going to be covering temp table statistics, execution plans, and plan reuse.

There’s a lot more to cover in TempDB, like table variables, memory-optimized table variables, cursors, index builds, AG statistics, and provisioning, but tomorrow’s focus will just be temp tables.

Free Training Registration

"*" indicates required fields

This field is for validation purposes and should be left unchanged.

Name*

First Last

Email*

Things I want*

Free Training Class Invites

Subscribe to Blog Posts (3-4 per week)

[Video] Office Hours: Back in the Home Office

Last Updated March 6, 2025

I’m back home after a 2-week Iceland vacation. Let’s catch up with your top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

00:00 Start
02:04 Suresh: Hi Brent . Question about 128 GB memory limit for SQL server standard edition.If I set ‘Max SQL Server Memory’ to 228 GB on a 256 GB VM , SQL will use the additional 100 GB for anything other than buffer pool . Is that correct assumption ?
03:41 Jens Herrmann: Two 20-year-old databases without any clustered indexes. The key columns are uniqueidentifier and some int columns. A colleague of mine changed all the table key columns to clustered indexes, and everything became noticeably slower. Is it a bad Idea to have a clustered index?
04:52 PAul: A production database is approaching 16TB. Adding another datafile to the PRIMARY filegroup is a solution. Is there any (dis)advantage to creating a new Filegroup on a different drive and moving large tables/indexes to it?
07:07 TokTik: What are your top recommended books on SQL server performance tuning for someone planning to explore this field for the first time?
07:51 Gary: Hi B. We have a Prod db that periodically experiences hundreds of long LCK_M_X waits and blocking on the same proc. Oddly, every one of those waiting sessions and the lead blocker is a COMPILE wait on the proc’s objectid. No RECOMPILE used anywhere. Why is sql trying to compile?
09:22 MyTeaGotCold: My devs use temporal tables a lot. Can you tell me any horror stories about them? I hit a SQL Server bug with system-versioned tables today and I’m scared.
10:30 Bandhu: What’s your faith level in third party SQL backup software? Considering it to get table level restores since Microsoft won’t add this any time soon.
10:53 Archivist: I’m trying to make backups faster by making cold data read only. How do I decide if I should make an RO database, make archive tables in an RO filegroup, or partition tables and make part of them RO?
12:10 neil: A frequently used dashboard randomly chooses horrible plan and kills production server. I tried forcing plan with Query Store. Works for a while then fails, says the index it’s using no longer exists? But I didn’t drop any indexes that I know of. Not sure where to look next.
13:33 thetechguy23: The role of a DBA today seems very blurred, at least at my organisation. As well as “keeping the lights on” we’re often asked to develop integrations exchanging data using REST API’s and then own that process. Data comes from the database, right? Where do you stand on that?

Query Exercise Answer: Finding Email Addresses

Last Updated March 20, 2025

Query Exercise Answers

For this week’s Query Exercise, your mission had two parts:

Do a first pass search through the Users table looking for columns where people had snuck in email addresses where they didn’t belong
Write a more high-quality query to identify exactly which rows we needed to handle

I wrote the challenge that way because in database work, we often write a quick-and-dirty proof of concept query to get a rough idea of what we’re dealing with. Sure, in a perfect world, we’d write a query that could capture any email address, but first let’s just get a rough idea if there’s anything that looks like an email address in the Location column:

SELECT TOP 100 Location, Id
FROM dbo.Users
WHERE Location LIKE '%@%'
  AND Location LIKE '%.com';

SELECT TOP 100 Location, Id

FROM dbo.Users

WHERE Location LIKE '%@%'

AND Location LIKE '%.com';

That runs nearly instantly and returns results – some of which aren’t actually email addresses, like “@bloginfoguide.com”, but there are more than enough genuine email addresses in that list to confirm that we’ve got a real problem. (And that’s only .com emails!)

If we need to check all string columns, we could concatenate them together into one big long string, and check to see if that big long string has @ and .com in it. However, that won’t tell us which columns are problematic, and that’s going to be important if we need to do this kind of research at real-life scale – across not just all the string columns in one table, but all the tables in the database, too.

When we hand our analysis over to someone (because it’s usually a dev manager or compliance officer who needs it), they probably want it in a format like this:

That’s where dynamic SQL and metadata come in.

For challenges like this, it’s tough to beat dynamic SQL. (Trust me, I’ve tried to beat it repeatedly. I put on boxing gloves and punch my screen all the time when I’m working with dynamic SQL, but I’m still the one who ends up huddled on the floor, crying. Wait, where were we?)

Let’s start by taking that query we just wrote, but modifying it to produce output similar to what we’re looking for, and let’s build it with dynamic SQL – but not make it metadata-driven just yet:

DECLARE @StringToExec NVARCHAR(4000);
SET @StringToExec = N'SELECT TOP 10 ''StackOverflow'' AS DatabaseName,
    ''dbo'' AS SchemaName,
    ''Users'' AS TableName,
    ''Location'' AS ColumnName,
    Location AS PossiblePII
    FROM StackOverflow2010.dbo.Users
    WHERE Location LIKE ''%@%''
      AND Location LIKE ''%.com'';';
PRINT @StringToExec;
EXEC(@StringToExec);

DECLARE @StringToExec NVARCHAR(4000);

SET @StringToExec = N'SELECT TOP 10 ''StackOverflow'' AS DatabaseName,

''dbo'' AS SchemaName,

''Users'' AS TableName,

''Location'' AS ColumnName,

Location AS PossiblePII

FROM StackOverflow2010.dbo.Users

WHERE Location LIKE ''%@%''

AND Location LIKE ''%.com'';';

PRINT @StringToExec;

EXEC(@StringToExec);

That produces this query:

SELECT TOP 10 'StackOverflow' AS DatabaseName,
    'dbo' AS SchemaName,
    'Users' AS TableName,
    'Location' AS ColumnName,
    Location AS PossiblePII
    FROM StackOverflow2010.dbo.Users
    WHERE Location LIKE '%@%'
      AND Location LIKE '%.com';

SELECT TOP 10 'StackOverflow' AS DatabaseName,

'dbo' AS SchemaName,

'Users' AS TableName,

'Location' AS ColumnName,

Location AS PossiblePII

FROM StackOverflow2010.dbo.Users

WHERE Location LIKE '%@%'

AND Location LIKE '%.com';

Now, let’s get a little fancier and build this with metadata. We’ll source our object definitions from the system object INFORMATION_SCHEMA.COLUMNS:

For my quick-and-dirty purposes, I’m going to filter for schema <> ‘sys’, because those are typically system objects. I’m also going to filter for only string columns (varchar and nvarchar), but if you’ve gotten fancy with user-defined data types, you’ll have some additional work. I trust you’ll be smart enough to figure that part out, since you’re the one who got fancy there. Anyhoo, back in my environment, this gives me enough to metadata-drive my dynamic SQL:

SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.DATA_TYPE IN ('nvarchar', 'varchar')

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS c

WHERE c.DATA_TYPE IN ('nvarchar', 'varchar')

So now let’s put those two things together – our dynamic SQL peanut butter and our metadata chocolate:

SELECT N'SELECT TOP 10 N''' + TABLE_CATALOG + N''' AS DatabaseName,
    N''' + TABLE_SCHEMA + N''' AS SchemaName,
    N''' + TABLE_NAME + N''' AS TableName,
    N''' + COLUMN_NAME + N''' AS ColumnName,
    ' + COLUMN_NAME + N' AS PossiblePII
    FROM StackOverflow2010.dbo.Users
    WHERE ' + COLUMN_NAME + N' LIKE ''%@%''
      AND ' + COLUMN_NAME + N' LIKE ''%.com'';' AS StringToExecute
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.DATA_TYPE IN ('nvarchar', 'varchar');

SELECT N'SELECT TOP 10 N''' + TABLE_CATALOG + N''' AS DatabaseName,

N''' + TABLE_SCHEMA + N''' AS SchemaName,

N''' + TABLE_NAME + N''' AS TableName,

N''' + COLUMN_NAME + N''' AS ColumnName,

' + COLUMN_NAME + N' AS PossiblePII

FROM StackOverflow2010.dbo.Users

WHERE ' + COLUMN_NAME + N' LIKE ''%@%''

AND ' + COLUMN_NAME + N' LIKE ''%.com'';' AS StringToExecute

FROM INFORMATION_SCHEMA.COLUMNS c

WHERE c.DATA_TYPE IN ('nvarchar', 'varchar');

When executed, that returns the queries we need to run against our database:

I like building my dynamic SQL gradually like this and testing it first manually to see if the queries are even correct. Given my typing skills and my use of alcohol, that’s not always the case, but here, these queries actually compile and run, woohoo! So now let’s turn them into dynamic SQL.

If I just built and executed them one by one, then our SSMS results tab would be chock full of lots of different result sets. I want one result set that I can copy/paste into Excel and hand to the project manager who will be dealing with this. (Ideally, I’d rather just give them the metadata-driven query so they can run it, and I won’t be responsible for copy/pasting PII into Excel, but managers sometimes just wave their hands and say, “Enough already, just put it in Excel and email it to me.”)

So I need it in one result set – to do that, I’m going to stage the data into a table. You could use a temp table for this, but I’d rather have it be restartable in case my dynamic SQL hits a snag, or in case I want to check its progress from another session.

So our script gets a little more complicated again:

CREATE TABLE dbo.DBA_PossiblePII
    (Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    DatabaseName NVARCHAR(256),
    SchemaName NVARCHAR(256),
    TableName NVARCHAR(256),
    ColumnName NVARCHAR(256),
    PossiblePII NVARCHAR(MAX));

SELECT N'INSERT INTO dbo.DBA_PossiblePII
    (DatabaseName, SchemaName, TableName, ColumnName, PossiblePII)
    SELECT TOP 10 N''' + TABLE_CATALOG + N''' AS DatabaseName,
    N''' + TABLE_SCHEMA + N''' AS SchemaName,
    N''' + TABLE_NAME + N''' AS TableName,
    N''' + COLUMN_NAME + N''' AS ColumnName,
    ' + COLUMN_NAME + N' AS PossiblePII
    FROM StackOverflow2010.dbo.Users
    WHERE ' + COLUMN_NAME + N' LIKE ''%@%''
      AND ' + COLUMN_NAME + N' LIKE ''%.com'';' AS StringToExecute
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.DATA_TYPE IN ('nvarchar', 'varchar')
  AND c.TABLE_SCHEMA <> 'sys';

CREATE TABLE dbo.DBA_PossiblePII

(Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

DatabaseName NVARCHAR(256),

SchemaName NVARCHAR(256),

TableName NVARCHAR(256),

ColumnName NVARCHAR(256),

PossiblePII NVARCHAR(MAX));

SELECT N'INSERT INTO dbo.DBA_PossiblePII

(DatabaseName, SchemaName, TableName, ColumnName, PossiblePII)

SELECT TOP 10 N''' + TABLE_CATALOG + N''' AS DatabaseName,

N''' + TABLE_SCHEMA + N''' AS SchemaName,

N''' + TABLE_NAME + N''' AS TableName,

N''' + COLUMN_NAME + N''' AS ColumnName,

' + COLUMN_NAME + N' AS PossiblePII

FROM StackOverflow2010.dbo.Users

WHERE ' + COLUMN_NAME + N' LIKE ''%@%''

AND ' + COLUMN_NAME + N' LIKE ''%.com'';' AS StringToExecute

FROM INFORMATION_SCHEMA.COLUMNS c

WHERE c.DATA_TYPE IN ('nvarchar', 'varchar')

AND c.TABLE_SCHEMA <> 'sys';

Test that, and it runs. Now, it’s time to think about actually executing it.

Executing this sucks in the real world.

In small-world situations like the Users table, I wouldn’t even bother with dynamic SQL. I’d simply take the output of the above query, and run it manually in SSMS. It’d finish in a few seconds, populating the table, and I’d take that output and run with it. However, the larger your data set becomes, the more problematic this is going to be.

You could build a cursor and loop over the contents of INFORMATION_SCHEMA.COLUMNS, executing the dynamic queries one at a time, one per column. However, you don’t wanna do that because I’ve hit situations where read-only metadata queries, even with READ UNCOMMITTED and NOLOCK hints, have actually either been blocked by other queries, or been the cause of blocking outages. (My favorite was when a client had a production outage because someone was running sp_BlitzIndex at the same time someone else was creating a columnstore index.) I wouldn’t want your query to be the cause of the next production outage.

So if you want to take the cursor approach, dump the contents of INFORMATION_SCHEMA.COLUMNS into a user table or temp table first, and then iterate over that, using Aaron Bertrand’s tips on cursor tuning.

Another consideration is query performance. The way we’ve written this, we’re doing multiple passes against the same table over and over. This technique works great if you have a lot of nonclustered indexes on your string columns. However, if you don’t, or if you purposely want to do a table scan instead, AND if you have hardly any PII, this is going to suck because you’re going to do a full table scan for each column you want to check. In that situation, it’d be better to write your PII-checking query differently, more like this pseudocode:

SELECT *
FROM dbo.Users
WHERE Location LIKE '%pii%'
   OR AboutMe LIKE '%pii%'
   OR WebsiteUrl LIKE '%pii%';

SELECT *

FROM dbo.Users

WHERE Location LIKE '%pii%'

OR AboutMe LIKE '%pii%'

OR WebsiteUrl LIKE '%pii%';

That would require a different result-capture method, though, since it doesn’t identify which column is the one with the PII, and returns all columns. However, if you’ve got a lot of string columns, and no indexes, this query might execute faster (albeit be harder to build.)

That brings me to my last consideration: the goal of this Query Exercise wasn’t to give you a ready-to-go PII analysis solution, because after all, we’re only checking for email addresses here! We haven’t even gone into things like social security numbers or dates of birth or whatever.

Let’s zoom out.

Your original query challenge had two parts:

Do a first pass search through the Users table looking for columns where people had snuck in email addresses where they didn’t belong
Write a more high-quality query to identify exactly which rows we needed to handle

And we’ve already faced a lot of complexity with just working on #1, and only for email addresses! That’s why in the real world, when clients ask me about this kind of thing, I walk them through a quick-and-dirty check like this:

SELECT TOP 100 *
FROM dbo.Users
WHERE Location LIKE N'%.com';

SELECT TOP 100 *

FROM dbo.Users

WHERE Location LIKE N'%.com';

And if email addresses show up, our proof-of-concept check is done. We’ve got a problem: PII is where it isn’t supposed to be, and frankly, IT ALWAYS IS. Users are boundlessly creative in how they sneak PII into systems.

At that point, the project scope just exploded. We’re going to have to identify all of the PII types we’re looking for, and write robust queries to detect that PII hidden in various formats. This work is genuinely hard and not particularly performant, especially when we try to do it in T-SQL, as we covered in the recent regex post.

This is where a dedicated team, and perhaps buying a third party app, make sense. You and I aren’t going to write queries that do a good job detecting all the types of PII, especially when they’re cleverly hidden in columns like Location or masses of text in AboutMe. If our work needs to be legally defensible – and in PII cases, it does – then there’s no substitute for a team with the time to really do this topic justice.

Or, you know, just, uh, sweep it under the rug. Time to go build another feature for our app!

Why Your Azure SQL DB Hyperscale Bill is Higher Than You’d Expect

Last Updated March 23, 2025

Azure SQL DB, Hyperscale

tl;dr: if you use Azure SQL DB Hyperscale’s auto-scaling, it’s probably not cutting your bill down because like SQL Server, Hyperscale doesn’t automatically relinquish memory, and it’ll be up to you to manually manage your own memory if you wanna cut your bill.

Now for the long story.

Microsoft’s Azure SQL DB Hyperscale has an auto-scaling option that’s supposed to automatically scale up and down. It’s perfect for bursty workloads like Have I Been Pwned, a free online service where you can put in your email address and see if your login details have been leaked. (Mine’s been part of 44 data breaches, which is one of the many reasons why I use 1Password to create separate logins for each web site, and sync my logins across all devices, plus manage my one-time passwords.)

Have I Been Pwned’s back end architecture relies on Azure SQL DB Hyperscale for data storage, and uses autoscaling to automatically add more horsepower (like when there’s a big breach to import), and cut horsepower during low-load periods to save money.

However, Stefán J. Sigurðarson discovered why their bill wasn’t going down. It turns out that Hyperscale’s automatic cost savings only kicks in when your CPU usage is low and when your memory usage is low, too. That’s where the problem comes in.

Microsoft SQL Server uses all your available memory by default, and doesn’t give it up automatically. I’m simplifying – there are some small guardrails, and SQL Server will back down its usage when other apps fire up and demand memory. But the vast majority of users will look at the vast majority of servers and say, “Whoa, SQL Server sure is using a lot of memory, and it never seems to go down.”

That’s a good thing for well-managed database servers (as I explained in that post above, more than a decade ago) – and that design served Microsoft well for decades. However, it’s a problem for the cloud, especially for cloud services that are supposed to automatically scale up and down based on demand. Azure SQL DB Hyperscale is built with Microsoft SQL Server’s code, and inherits some of those design choices.

Azure SQL DB Hyperscale’s memory documentation says they fixed SQL Server’s problem, saying things like:

Unlike provisioned compute databases, memory from the SQL cache is reclaimed from a serverless database when CPU or active cache utilization is low.

Except as Stefán discovered and documented in his post, that doesn’t appear to be the case. Stefán’s post explains how to work around it by creating a stored procedure to manually down-scale your Hyperscale database based on low demand, and to run it in an Elastic Job. Good stuff. You should subscribe to his blog (button at the top right of his site) because it’s hard to find folks publicly sharing real-world Hyperscale knowledge, especially at scale.

I do expect Hyperscale’s behavior to change as it gains more public awareness, and just over time in general, Microsoft’s strategy for how they scale down (and scale up) will change. That’s just a normal part of any development process, and hopefully they document those changes and share ’em with customers.

[Video] Office Hours: Waiting for the Hot Tub to Fill

Last Updated March 5, 2025

While waiting for my Husavik hot tub to fill up, I took your top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

00:00 Start
01:28 1440×1080: always on with 2 nodes 1db, readable secondary. I’ve optimized both nodes to a point where the primary can withstand both workloads.i want to downgrade licensing from enterprise to standard. What is the process to switch from AOAG to BAG ?Do i just change the license ?
04:05 Compatability Level 140: Soon after auto-update stats triggers, I get single-row estimates for date ranges starting with yesterday. I thought the modern cardinality estimator was supposed to prevent this major error. What do I need to study?
05:11 Chase: When would having tempdb configured to only have a single file (mdf) be favorable, even if you more than 1 CPU? I was taught to have an mdf per CPU up to 8, then test for balance. A vendor system imports faster using temp tables using only 1 mdf, which makes no sense. Any ideas?
07:00 Manolis: Do you foresee a specific DB technology exploding in popularity due to close alignment with AI?
07:22 MySpecialHoneyPot: Hi, in various cases SQL Server chooses to use the clustered columnstore index where a suitable nonclustered index exists that delivers better performance. Besides index hints, is there a way to push Query Optimizer to not use the clustered indexes?
08:37 green tissue: What breaks when databases are owned by windows users? People always say it’s evil but I have never found a demo or specific example.
09:40 JerseyDBA: I was looking through sp_blitz on a server and found all the DBs have the legacy target recovery time set to 0 not 60. Auto checkpointing isn’t causing issues, any harm in leaving this and only flipping to 60 seconds if I start seeing I/O issues?
10:55 Austin Powers Shagadelic Box: My big server’s CPU never hits 70%, but I see lots of parallelism waits. Will adding more cores help? I’m just checking to see if I understand how SQL server uses CPU.
12:15 James: Hi Brent, In your opinion, would creating triggers to enforce cross database referential integrity be something you would consider, and not fall in the generic realm of ‘bad practice’?
14:08 chris: Do we know what Azure Managed Instance is using “under the hood” for HA/DR? If not Availability Groups is it something proprietary?
15:03 ChompingBits: What are you thoughts on how to work with vendors that don’t follow best practices. Our environment is mostly SQL isntances for vendor software we buy. We run SP_Blitz weekly and see lots of configuration warnings. How do you tell a vendor they don’t know what they’re doing?
16:44 AzureNewb: Do most of your clients use Azure Managed Instance or Azure SQL?
18:21 Contineo: Not all RDS instance types support putting tempdb on the local storage. What would you choose between: – a r5d or m5d instance with tempdb on the local storage but a EBS Bandwidth or 4750Mbps or – a r6i instance without tempdb on the local storage but an EBS Bandwidth of 10Gbps
20:05 SGU: So while I know that “Missing Indexes” are not 100% reliable as to the exactly index to be created, is there a was to determine what query “caused” that missing index recommendation to be made?
20:58 Depthcharge: Hi Brent! I’ve been trying to reduce PAGEIOLATCH_SH on a 25TB production database. Monitoring tool identified a 25TB+/hour of logical reads from 300 exec/hr “Task Manager” commands in the master DB, no associated SQL. Worth investigating, or should I focus on individual queries?

T-SQL Has Regex in SQL Server 2025. Don’t Get Too Excited.

Last Updated December 10, 2025

SQL Server 2025, T-SQL

Regular expressions are a way of doing complex string searches. They can be really useful, but they have a reputation: they’re hard to write, hard to read, and they’re even harder to troubleshoot. Once you master ’em, though, they come in handy for very specific situations.

This post isn’t about their complexity, though. This post is about Azure SQL DB & SQL Server 2025’s regex performance.

In this case, regular expressions are like Everclear.

Everclear is a brand of alcohol, and when you read that, you might think, “Sounds interesting, I wonder what it tastes like.” It’s marketed as odorless, flavorless, and colorless. I say “marketed” because if you’ve ever been around it, you will not use terms like odorless or flavorless. To get a rough idea of what it’s like, watch The Unemployed Wine Guy try it.

See, Everclear is 95% pure alcohol by volume. It’s so potent that many states have outlawed it. You know Nevada, the state with gambling, weed, hookers, and assault weapons? Yeah, no Everclear allowed here. Gotta draw the line somewhere.

You’re not supposed to drink Everclear straight, but that trained professionals are supposed to use it – in very small quantities, in combination with other ingredients.

Let’s give regex a taste.

We’ll use the Stack Overflow 2010 database (the small one) in Azure SQL DB. The Users table on it has about 9M rows amounting to about 1GB in size.

Say we’re trying to find out if any sneaky users have been putting PII in places that it shouldn’t belong, like this week’s Query Exercise challenge. We want to find all Users.Location values where the user put in something resembling an email address. We’ll put an index on the Location column to give Azure SQL DB a fighting chance, and to give it statistics so it can better estimate how many rows might match a predicate, then run our query:

CREATE INDEX Location ON dbo.Users(Location);

SET STATISTICS IO, TIME ON;

SELECT TOP 100 *
FROM dbo.Users 
WHERE REGEXP_LIKE(Location, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

CREATE INDEX Location ON dbo.Users(Location);

SET STATISTICS IO, TIME ON;

SELECT TOP 100 *

FROM dbo.Users

WHERE REGEXP_LIKE(Location, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

The resulting execution plan is really disappointing:

SQL Server could have scanned the Location index rather than the full table, but it chose not to.

Update 2025/12/02 – Good news! SQL Server 2025 RTM fixes half of the problem here. In the actual query plan, it still scans the table rather than touching the index, but at least the CPU usage is waaaay down.

I blogged more about SQL Server 2025’s implementation (and its remaining problems) here.

Why’s the performance taste so bad?

Reader: “It’s because you asked for SELECT *, and your index only has the Location column.” Not so fast – I only asked for 100 rows, which means that Azure SQL DB could have scanned the index to find the first 100 matching rows, and then done key lookups to fetch the columns, and it would have been faster. Let’s prove it:

SELECT TOP 100 *
FROM dbo.Users 
WHERE REGEXP_LIKE(Location, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

SELECT TOP 100 *
FROM dbo.Users WITH (INDEX = Location)
WHERE REGEXP_LIKE(Location, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

SELECT TOP 100 *

FROM dbo.Users

WHERE REGEXP_LIKE(Location, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

SELECT TOP 100 *

FROM dbo.Users WITH (INDEX = Location)

WHERE REGEXP_LIKE(Location, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

And the abridged version of STATISTICS IO shows that the index would have been way, way less page reads:

Table 'Users'. Scan count 1, logical reads 157281
   CPU time = 60500 ms,  elapsed time = 60955 ms.

Table 'Users'. Scan count 1, logical reads 20827
   CPU time = 58546 ms,  elapsed time = 58606 ms.

Table 'Users'. Scan count 1, logical reads 157281

CPU time = 60500 ms, elapsed time = 60955 ms.

Table 'Users'. Scan count 1, logical reads 20827

CPU time = 58546 ms, elapsed time = 58606 ms.

And the query plan can indeed happily use the index. The top plan is the default, the bottom plan is with my index hint:

Reader: “So why is Azure SQL DB avoiding the index? Is the tipping point involved?” If Azure SQL DB believes a lot of rows will match your query’s predicate, then can veer towards clustered index usage rather than nonclustered indexes. However, look at those plans again: they can’t produce more than 100 rows! My query is specifically asking for the first 100 rows. There’s no sort. Just gimme the first 100 rows that match. I absolutely guarantee that this query could not produce more than 100 key lookups.

Reader: “Then why is Azure SQL DB avoiding the index then?” Well, when Microsoft coded the row estimates for regex, they made some unbelievably drunken decisions, perhaps involving chugging straight Everclear. Let’s run a different query to expose what’s happening:

SELECT COUNT(*)
FROM dbo.Users 
WHERE REGEXP_LIKE(Location, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

SELECT COUNT(*)

FROM dbo.Users

WHERE REGEXP_LIKE(Location, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

The number to pay attention to here is the estimated number of rows that will match. Hey, Azure SQL DB, how many rows in our Location column will match this regex search?

Azure SQL DB believes that 2,675,250 rows will match – when only 68 do. It’s off by a factor of 39,342X!

Performance is bad because Microsoft got lazy – again.

That 2,675,250 row estimate will be familiar to those of you who shelled out money for my Fundamentals of Query Tuning class. That number just happens to be exactly 30% of the row count of the Users table.

When Microsoft coded the row estimates for their fancy new regex functions – like many other kinds of search predicates – they simply hard-coded it and called it a day. No matter what your regexp_like query is looking for, Azure SQL DB guesses that 30% of the rows match. Sometimes, that estimate is fine – but most of the time, it’s not.

This is the kind of performance issue that doesn’t seem bad at small data sizes, but as your data grows, it –

wait a second

hang on

Our table only has 9 million rows, and it’s less than 1GB in size, and the regexp_like takes sixty seconds of CPU time!

That’s… terrifying. Sixty seconds of CPU time to query a 1GB table. That isn’t gonna scale, full stop. I mean, it’s fine for one-off utility queries on really small amounts of data, like if you’ve filtered the rowset down to a manageable number (like, 1,000 rows), but nothing you’d want to put into production on an OLTP system.

Plain ol’ queries are way better.

If you only need to find out if PII is getting out there, try a regular like query instead:

SELECT TOP 100 *
FROM dbo.Users
WHERE Location LIKE '%.com';

SELECT TOP 100 *

FROM dbo.Users

WHERE Location LIKE '%.com';

Reader: “Whoa Brent, you can’t start a like search with a leading wildcard! That’s bad!” Seriously, you gotta shut your pie hole every now and then, or at least go buy my Fundamentals classes, because you’re in for a big surprise. That query doesn’t have an index hint, and yet, Azure SQL DB is smart enough to use the index:

And it does less logical reads, and runs in just a couple seconds:

Table 'Users'. Scan count 1, logical reads 17734
Table 'Worktable'. Scan count 0, logical reads 0

 SQL Server Execution Times:
   CPU time = 2359 ms,  elapsed time = 2455 ms.

Table 'Users'. Scan count 1, logical reads 17734

Table 'Worktable'. Scan count 0, logical reads 0

SQL Server Execution Times:

CPU time = 2359 ms, elapsed time = 2455 ms.

Are the results the same? No – but it’s enough to solve the business problem and tell us that yes, people are indeed using email addresses in their Location columns. (I’m not putting those here on the blog, but you’re welcome to run the query yourself to see the results.)

That’s why I say regex is like Everclear.

Everclear isn’t inherently bad or evil – at least, not any more than gambling, weed, hookers, and assault weapons. (You hear me, Nevada?) You just have to be really careful when you use it, and understand what you’re in for. It shouldn’t be your first resort on a typical Saturday night – try other solutions like gin & tonic instead.

Similarly, regex in Microsoft databases can be fine, too – in combination with other ingredients. It shouldn’t be your first resort, and it certainly shouldn’t be the only thing in your where clause or joins. If you have a bad experience with it, your first resort should be to take it out, and try an alternative solution.

And whatever you do, don’t use regex and Everclear together.

Update 2025-07-05: Louis Davidson experimented with the new ASSUME_FIXED_MIN_SELECTIVITY_FOR_REGEXP and ASSUME_FIXED_MAX_SELECTIVITY_FOR_REGEXP query hints. It doesn’t improve the performance of queries like this blog post, but if you’re going to use REGEXP_LIKE in your code, they could be useful hints for tuning other queries.

Query Exercise: Looking for Email Addresses

Last Updated March 15, 2025

Query Exercises

Personally identifiable information (PII) is freakin’ everywhere.

When companies first start looking to identify and lock down their data, they think it’s going to be as easy as identifying common columns like EmailAddress, DateOfBirth, SocialSecurityNumber, and so forth. They think, “We’ll just encrypt those columns and we’ll be fine.”

Ho ho, my sweet summer child. No, that’s not nearly enough.

Your users are completely diabolical. If you give a user a text box, they will find amazingly creative ways to stuff your database chock full of PII that you didn’t ask for, and you certainly don’t want. One of my favorite client examples was a help desk ticketing system where the support team routinely logged things like usernames, passwords, and other PII mixed in with their case notes. (Example: “Person needs help resetting their account, but can’t find their account. Tried searching under Jane Doe, Doe Jane, 123 Main Street, even tried their DOB of 1/2/1999, but I can’t find them, even though their credit card is getting billed under 5432-1234-9876-4567.”)

So when you start the journey of making your database safer by identifying and handling PII, it’s up to you to proactively dig through your database looking for this stuff. This kind of research work is often handed to database administrators because they’re trusted to look everywhere in the database, in every row and column. Plus, doing this kind of search is more efficient inside the database itself rather than on the app side – since it involves looking at every row and column.

This week, you’re that DBA, assigned that project.

Take any Stack Overflow database and examine the Users table. To keep things simple, I’m only going to ask you to look for email addresses.

This week’s challenge is a two-parter:

Write a one-time query to check the Users table for email addresses. Do we have a problem with users putting email addresses where they don’t belong, and if so, what columns do we need to dig deeper into?
Assuming we have a problem, write a query to find exactly which rows we need to address. Perhaps we’ll send them emails (ha ha ho ho) to log in and correct their profile, but we’re not actually going to modify their data.

Put your queries in a Github Gist and include those link in your comments. Check out the solutions from other folks, compare and contrast your work, and next week I’ll circle back with my answers & thoughts. Have fun!

Free Webcast Tomorrow: Fundamentals of Query Tuning

Last Updated February 14, 2025

It’s time for SQL Server Spring Training! I’m teaching 60-90 minutes of my most popular classes for free over the next few months.

Tomorrow I’m doing Fundamentals of Query Tuning. It’s for folks who are comfortable writing queries to get the data they want, but wildly uncomfortable reading query plans and trying to explain why queries aren’t going faster. If you look at a query plan and think that the percentages shown mean anything, then it’s time for you to get the fundamentals under your belt.

That’s part of the fun of my Fundamentals classes: folks often think, “I’ve been doing this database work for years – I know all those fundamentals.” But when they attend the Fundamentals classes, they’re surprised by how a lot of their basic assumptions were incorrect.

It’s not your fault! You’ve never attended a formal SQL Server training class before. Let’s fix that and get you started for free tomorrow morning. Register now and I’ll see you at 11AM Eastern, 10AM Central, 8AM Pacific. The webcast will not be recorded – be there, be square, or buy the full class for $89.

Free Training Registration

"*" indicates required fields

This field is for validation purposes and should be left unchanged.

Name*

First Last

Email*

Things I want*

Free Training Class Invites

Subscribe to Blog Posts (3-4 per week)

What’s Coming in SQL Server 2025, DMV Edition

Last Updated March 5, 2025