[Video] Office Hours on Mount Charleston
For this episode, I’m up in the mountains just outside Vegas, enjoying the nice cool temperatures, spending the weekend at Mount Charleston. Let’s go through your top-voted questions from https://pollgab.com/room/brento.
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.
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.
What about the people who have a life time access to smart postgres?Will it remain the same in Brentozer.com?
Yep!
You asked for a problem that would benefit from larger tables.
You might classify this as somewhat unreal/unrealistic as well, but it really happened:
We were building a relatively small, brand-new application on a brand-new database with only very minimal, hand-crafted mock data. A colleague had written many dozens of integration tests. Running these tests would consistently result in several tests failing after ~30 seconds. With every different run, different tests would fail with the same symptoms: massive locking in the database.
Apparently, SQL Server very often chose to escalate to table locks because there were so few records in the tables.
After adding production-like amounts of data, all locking disappeared without any changes to the queries or database settings.
This ‘problem’ benefited from larger tables.
I’m gonna be brutally honest: I don’t think your lock escalation had anything to do with fill factor. That sounds like a classic lock escalation problem due to minimal statistics and/or indexes (especially since it’s brand new objects), and we cover that in the Mastering Index Tuning class.
I’m sure it had nothing to do with fill factor. I’ve probably missed the origin of this story/exercise/puzzle. I though it was purely about finding/knowing a situation where a bigger table would be benificial.
Our indexes were designed neatly. Statistics, however insignificant, were up2date.
I think our problem was that there were so few (a handful at most) records in our tables that Sql Server immediately chose table locking instead of row or page locking.
So, yes, classic lock escalation.
Could you point me towards the origin of this story? Always interested in anything from you I might have missed.
I’m not sure what you mean about the origin of the story – origin of what story?
The name of the OP (youAskedForAProblemThatWouldBenefitFromLargerTable) sounds to me like you challenged your followers some time earlier.
Ah! Yeah, it was a previous Office Hours episode where someone was asking for valid reasons to set a low fill factor.