Here’s what we discussed:

• 00:00 Start
• 01:41 youAskedForAProblemThatWouldBenefitFromLargerTable: Problem:Table is 100% larger than ideal. Page splits decrease page density to 51% after adding just 1% new records following rebuild with 100% filfactor. Fix:Making table 10% larger w/ FF 90% allows adding 8% new records without decreasing page density or enlarging table further.
• 04:59 AndrewG: (I am SaaS support) I see a lot of DBAs utilizing Query Store. Is there any reason I should use this instead of the first responder kit?
• 05:55 chris: Hi Brent! You’ve shared the RSS feed you use and it’s a very lengthy list. Do you manage to keep up with the entire feed?
• 07:30 Jersey: When designing an index that uses included columns, is there a “sweet spot” for the number of columns included in the INCLUDE clause, or does it primarily depend on the query workload and performance requirements?
• 08:22 PursuitOfExcellenceDBA: Hello Brent. Being a SQL guru yourself, just curious since you always refer others for their area of expertise. Have you ever reached out to Paul Randall or others and/or vice versa to solve a complicated SQL server issue that took you to finish line faster?
• 10:47 AlwaysLearningDBA: I have daily index maintenance job per your blog & weekly reindexing job.DB size is around 4TB (will keep increasing). Weekly index maintenance is running over 18 hours.How do I run Ola’s job in parallel for multiple indexes at a time? Looking for a scalable solution. Thank you.

(The common phrase is to “have your cake and eat it too”, but did you ever think about how that’s backwards, and it doesn’t make any sense? It actually makes a lot more sense in the opposite order – as in, eat your cake and have it too – because that represents the best-of-both-worlds scenario. Have your cake and eat it too implies that you would have it, and then eat it, but once you eat it, you can no longer have it, so – okay, I’ll stick with teaching database stuff. Moving on.)

Let’s use the Users table from the Stack Overflow database, and let’s pretend that the Users.Location column is stored as JSON rather than letting users type in whatever they want. Let’s pretend we make them pick their Country, Province, and City. Then, let’s use one of my Database Animations to illustrate the pain of querying it without the JSON index, and then the cake-y-ness of the added index:

▶ Watch the animated version of Json Index

Before we have the JSON index, we have to do:

• The read-intensive work of scanning every row in the table, and
• The CPU-intensive work of breaking open the string and parsing the JSON data to identify each attribute

The JSON index turns the work into a simple index-seek-and-key-lookup – the same awesome, ground-shaking power and speed you’re used to having from traditional table designs & indexes. You get to eat your cake (not bothering to design your table structures), and have it too (get fast querying – as long as you’re willing to design indexes to support your queries.)

However, this only works if:

• You actually create the JSON indexes
• You filter on the indexed attributes (you can filter on more attributes too, but at minimum you need to filter on an indexed attributes so SQL Server can reduce your search space before checking the other filters in your query)
• The indexed columns are selective – they reduce your search space (as opposed to a attributes like Alive = Yes, which doesn’t really reduce your search space)
• You keep the number of indexed attributes to a minimum – say, 5-10 attributes max

That limit of 5-10 attributes surprises developers.

After all, Microsoft makes it sound practically unlimited in the CREATE JSON INDEX documentation. The first example they use looks like this:

CREATE JSON INDEX json_content_index ON dbo.Users (UserAttributes);

In that example, EVERYTHING we stuff into that JSON junk drawer gets indexed – including in our example above, whether they want Theme:Dark or Theme:Light. However, that ends up creating an index on every attribute we put in our JSON. The size growth is explosive, and it’s not unusual to see the index size be a multiple of the size of the JSON data we’re storing. (And you thought JSON data was size-inefficient!) There aren’t any warnings about that in the documentation, but trust me, the overhead is spectacular. If we stored the entire Users table columns all in JSON, and then ran Microsoft’s style of create-index, check out the size overhead:

The table itself is only 3.9GB, but the JSON index atop that data is 6GB – almost double the size of the table! Plus, if you modify any attribute on that table – even attributes you don’t filter on, like Theme = Dark vs Light – then you pay a performance penalty to keep the Theme’s index updated.

Instead, what you wanna do is index only the specific attributes that you’re going to regularly filter on, like the example I use in the animation:

CREATE JSON INDEX json_content_index ON 
dbo.Users ('$.Location.Country', '$.Location.Province', '$.Location.City');

That way you get the best of all worlds: fast performance on the filters you use, low insert/update/delete overhead for the index maintenance on those indexed attributes, and for any other properties you wanna save in JSON, you get schema-less design, fast iteration on the app side, and low overhead on the database side.

Where does the 5-10 number come from? My Fundamentals of Index Tuning class, where I explain the overhead that each additional index costs in terms of insert/update/delete performance. The more copies of the table that you add, the slower your inserts/updates/deletes go, and you start hitting the point where your users notice long blocking delays under concurrent workloads. In that class, I talk about when you can exceed that 5-10 number, and when you may need to go even lower on your number of indexes in order to get the ingestion speed you need. See you in class!

Note: I generated the animations in this post with Claude Code, but all of the text & demos are completely written by me.

If you don’t set the program names, you’ll either end up with empty strings, or your dev tool’s default name, like Core .Net SqlClient Data Provider, which doesn’t tell you jack.

It’s up to the developers to set the program name in code or in their connection string. Just tack it on to the end of your connection string like this:

server=MyServer;database=MyDatabase;Integrated Security=SSPI;Application Name=My Incredible Application;

And then in your monitoring tools, you’ll be able to group sessions together by application.

One question that comes up a lot afterwards is, “I’m looking at the plan cache and Query Store – what application and what user ran this query?” Unfortunately SQL Server can’t/won’t store that since a query might conceivably be run thousands of times per second, all from different servers/apps/users. If you need to track down who’s running a query, your best bet will be to set up an Extended Events trace, or if it’s a long-running query, do sampling of sp_BlitzWho or sp_WhoIsActive to table periodically.

Here’s what we discussed:

• 00:00 Start
• 01:42 Spicy Ninja Attack: If parameter sniffing fears justify skipping daily stats updates, should I also turn off intelligence query processing features like CE feedback unless I know they’re solving problems? Both cause recompiles.
• 03:12 Ted Striker: What are your pros / cons of periodically shrinking a SQL DB that grows large at times but then empties out quite a bit?
• 04:22 Sir Index Alot: The vast majority of our deadlock errors are resolved by indexing vs changing the underlying queries. How often do you have to change the underlying query vs indexing to fix a deadlock error?
• 06:14 AndrewG: Should I be enabling RCSI on every database? Is there any drawbacks or have seen any issues occur from it?
• 08:09 MyTeaGotCold: Now that SQL Server 2022 is finally ready, do you find that Managed Instance Link is meeting people’s Standard Edition HA/DR needs?
• 09:34 The Query Whisperer: How do your SQL AG clients deal with replicating users, permissions, agent jobs to the replicas? Run powershell script from another machine?
• 11:20 LooseCannon: I support a number of internal SQL Server-based applications. Our SQL Server is curently 2017 and our IT department is upgrading this to 2022.Given the current state of play is this a wise choice over 2025. I don’t have much influence myself on the upgrade path.