[Video] Office Hours: Open Q&A for Microsoft Databases
Today I’m back in the home office, finishing up teaching Mastering Index Tuning and getting ready to head out on a Mexico cruise. Let’s go through your top-voted questions from https://pollgab.com/room/brento.
Here’s what we covered:
- 00:00 Start
- 00:30 Need For Speed Round: What do you recommend for statistics maintenance on TB-scale tables with billions of rows? We’ve gone 5 years with neither scheduled nor automatic statistics updates and it hurts. WITH SAMPLE 1 PERCENT is many millions of rows.
- 03:43 CantSayWithoutThemKnowing: Hi Brent, long time listener/supporter. Are you seeing a battle starting between experienced DBAs vs a AI developer DBA who think they know better? I go to battle sometimes and use the same AI tools but leverage my experience to and prove them wrong. #NeverLostACase #TrainTheLLM
- 05:30 I_like_SQL_Server: Hi Brent, I can see there are some new classes with 2026 in the name. How are they different from your recorded classes? Will they be included in the forever bundle for fundamentals and mastering you offered some time ago? Keep on being awesome!
- 06:50 MyTeaGotCold: Particularly with regards to temp tables, have you ever saved the day with OPTION(KEEP PLAN) or OPTION(KEEPFIXED PLAN)?
- 07:42 Ricky Bobby: How do you decide what to automate first in a SQL Server environment, and how do you determine when the automation is worth the effort?
- 09:16 EdgeCase: Hi, We see short PAGELATCH waits on TempDB system objects (eg sysallocunits) during heavy workloads (many sessions using many table variables and temp tables). We are looking at testing with “IsTempDBMetadataMemoryOptimized” and wondered if you had any experience of using this?
- 10:22 retired dba: I have been on both sides of the table when it comes to consulting and found that workers at a client site are often resentful and sometimes ambivalent towards consultants (they felt slighted by mgmt). Have you ever experienced this and how best did you handle the situation?
- 14:15 Thomas Franz: When I select the ID column of a large table (COLUMNSTORE) into a #tmp and join both tables again later, SQL Server often estimates, that just ~30% of the rows in the #tmp will match (I could have added random ids). Is there a way to indicate, that it will be a 100% match?
- 17:09 chris: I understand AGs are complicated to setup and require a team of people to support them; however, at what point are simpler methods inadequate for a company’s HA/DR needs?
- 18:59 Margaret: I’ve created a job to use sp_BlitzWho to collect data for using to troubleshooting. The XML for the query plan (or live query plan) exceeded 132 levels, so the job failed. I made a small modification to the code to circumvent that – where do I post it for you?
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
For automation ROI break-even, check out this chart https://xkcd.com/1205/
PS – you forgot the era of “All they’re doing is copy/pasting from USENET, they don’t really know what they’re doing!”
Thank you for the praise, Brent, I guess I better bookmark this video for my next salary negotiation 🙂
Sadly neither of the two suggestions would really help me, since it would hurt more than help.
I process ~500 mio rows per day and have to price them depending on a whole bunch of difficult rules.
Even with 2 TB RAM the SQL server can’t really read, join, calculate and write 500 mio rows at once (and even if it could, I don’t want to risk some Arithmetic overflow in the last 10 rows that would roll back the whole statement) and of course the pricing rules / needed joins can’t done performant when you apply every special rule for an edge case to every common row instead of just that 0.5% relevant rows.
For this reason I use #temp tables that are usually filled with about 3 mio rows per SELECT INTO (much faster than INSERT INTO SELECT since it writes parallel) and apply the pricing rules (multiple UPDATEs) before I write (insert or – after price changes – updates) the result into the destination table.
Inserting 6 mio extra rows with negative IDs into the #tmp, adding a clustered index or FOREIGN KEYs (not really possible, when the source table is a COLUMNSTORE with billions of rows, I’d prefer to not add an UNIQUE PK onto it) would cost more time than I currently loose because of the bad estimates.
BTW: I tested with
OPTION (USE HINT(‘ASSUME_FULL_INDEPENDENCE_FOR_FILTER_ESTIMATES’))
on the queries that are joining #tmp and source and had – sometimes – much better estimates, even if the name of the hint indicates the opposite. I really wish Microsoft would allow me to use similar hints (ideally separate per JOIN) to specify a fitting factor, that it could use in the estimates.
Regarding the first question (statistic updates on a table with billions of rows):
I hope that this table is partitioned. If yes you can (since SQL 2014) create / rebuild indexes with STATISTICS_INCREMENTAL = ON
and then use e.g.
UPDATE STATISTICS dbo.BigTable(SomeStatistic)
WITH RESAMPLE ON PARTITIONS (25);
to update just the few latest once.
As far I understand SQL server will create a “mini statistic” on every partition and merge all those into the final histogram. The final (used) histogram will still have just 200 steps but , but it may help you, when your company expanded to New Zealand and 10% of your current customers are now there (while your outdated statistics didn’t even know this country).