SQL Homework – August 2023 – Have the best practices changed while you weren’t looking?
17August 1, 2023 by Kenneth Fisher
It’s been a bit since I did one of these so let’s go back to the basics. Best practices. We all know they exist and probably know at least a few. A while back I talked about the fact that best practices change over time. Turns out they also change rather dramatically depending on your environment. Kendra Little (blog|twitter) recently posted a discussion of index maintenance on Azure SQL DBs and Managed Instances and how the best practices should be somewhat different than on-premises.
Obviously, cloud environments have their own unique requirements but even when on-premises things are changing. Storage is getting faster and bigger, and memory and CPUs are more plentiful than ever before. Is it any wonder that best practices are going to change?
So this month take a look at your best practices. Start with some research. What are considered the current best practices? I’ll give you a hint too. Not all of it is going to be what Microsoft says (blasphemy!). Look at what other DBAs out there are doing and what they are finding to work the best. Kendra’s post above for example. Then take a look for any anti-patterns in your code, review your maintenance plans (FYI I mean your plans for maintenance, not actual maintenance plans. Don’t use those.), your backup plans and schedule, and last but certainly not least, check your instance and database settings. Make sure all of it makes sense given your current setup. Far too often things get put in place and forgotten about for years. And of course last but not least make plans to update what you are doing with the current best practices. This should include testing and figuring out if the best practice is what’s best for you.
SQL Studies 
Based on the sp_blitz outputs I see every day…best practices is more “lets push all the buttons” or “don’t touch anything, ever”
Is that the best practice or mpst common practice? 😁
Yes
I always believe that without a reasoning explaining why something is a “best practice” it is little more than cargo cult practices.
@CujoDeSoque
Preach it! If you have 82 minutes to be entertained, see the following ‘tube where I destroy many myths and lay waste to the old supposed “Best Practice” Index Maintenance that, despite the changes that MS started making in their documentation back in April of 2021, most people in the world are still using!
Here’ the link… and, like I say at the beginning, it’s NOT just about GUIDs.
Also, if you’re listening with a headset, be aware that they added 3 advertisements at the 15:00, 30:15, and 45:25. The advertisements are important to help support this event but I wanted to let you know that they’re sudden and they’re loud! They WILL lift your headset!
Yes!!! Thanks for that, Jeff. After your presentation has been released I’ve started doing simple tweaks to FILLFACTOR + only full rebuilds. I’ve created a simple, crippled script to calculate an “ideal fillfactor” based on previous FF + fragmentation accumulated over time, adjusting usually by 10 percent, and it helps me maintain greatest page fullness versus fragmentation for the most often used tables.
Such a shame SQL Server doesn’t obey fillfactor on inserts!!!
I couldn’t agree more! I mention Grant’s cargo cult post quite a bit when talking about best practices.
“Not all of it is going to be what Microsoft says (blasphamy!)”
What? Cost threshold for parallelism shouldn’t be set to 5? Say it ain’t so!
Ha! 🤣🤣
Hey, it is important. When an old PC from 199x takes 5 full seconds CPU to do this sort, join, filtering etc. than you really want to support him by a few other grandpa’s from the retirement home…
😂
I’ve seen it more than once and I never have a problem revisiting it, Jeff.
Aye… Thank you for the feedback and the kind words, twoknightsthenight.
Keep shrinking databases is bad but I used AUTO_SHRINK on my Azure SQL Databases and it worked perfectly!
I arrived to make them all fit inside my Elastic Pool. And no performance problems (they are more than 25).
Is this a blasphamy?
I’m not sure about auto shrink, but I’m regularly checking the DBs in my MIs for excessive excess space and shrinking them.
Nope… not blasphemy. It just means 1 of 4 things…
1. No indexes were inverted or…
2. Any indexes that were inverted are not used for range scans or…
3. Any indexes that were inverted are small enough to live in memory and not get bounced out once loaded or…
4. Your performance was bad to begin with and nothing could make it worse. 😀
5. your indexes were in the reverse order before from another shinking and are now in the right order again 🙂