[Video] Office Hours: Microsoft Database Q&A
Let's go through a LOT of your top-voted questions from https://pollgab.com/room/brento on a VERY early Saturday morning.
https://www.youtube.com/watch?v=V7lGcm8CyEM
Statistics Management
Creating, updating, inspecting, and understanding optimizer statistics.
40 associated posts37 primary posts
How Multi-Column Statistics Work
The short answer: in the real world, only the first column works. When SQL Server needs data about the second column, it builds its own stats on that column instead (assuming they don't already exist), and uses those two statistics together - but they're not really correlated.
For the longer answer, let's take a large version of the Stack Overflow database, create a two-column index on the Users table, and then view the resulting statistics:
[Video] Office Hours, Standing in the Ocean Edition
My tripod is probably never gonna recover from the salt water, but the water was so nice that I couldn't resist. This is a 360 video, so you can grab the screen and move it around to see Magens Bay in St Thomas, US Virgin Islands, as I go through your top-voted questions from https://pollgab.com/room/brento.
[Video] Office Hours: Back in the Bahamas Edition
Yes, I'm back on a cruise ship with another 360 degree video. Lest you think I'm being wildly irresponsible (or responsible perhaps?) with your consulting and training money, be aware that this particular cruise was free thanks to the fine folks in the casino department at Norwegian Cruise Lines. In between beaches and blackjack, let's go through your top-voted questions from https://pollgab.com/room/brento.
Automatic Stats Updates Don’t Always Invalidate Cached Plans
Normally, when SQL Server updates statistics on an object, it invalidates the cached plans that rely on that statistic as well. That's why you'll see recompiles happen after stats updates: SQL Server knows the stats have changed, so it's a good time to build new execution plans based on the changes in the data.
However, updates to system-created stats don't necessarily cause plan recompiles.
[Video] Office Hours at the Hoover Dam
Dam! I took your top-voted questions from https://pollgab.com/room/brento without making any dam or beaver puns. Rather proud of myself for that one.
https://www.youtube.com/watch?v=nUqIJtps8Y4
[Video] Office Hours with a Special Guest: Count Distinct
I was busy, so I asked a friend to fill in for me and answer your top-voted questions from https://pollgab.com/room/brento. He did a pretty good job:
https://youtu.be/oaRIxcf8sKE
[Video] Halloween Office Hours in Salem, MA
I visited friends in Salem, Massachusetts, home of the 1692 witch trials, and it turns out Salem is a great place to visit around Halloween! There were tours, characters in costume, witch gear shops, and all kinds of spooky-themed happenings.
I sat down outside of the Charter Street Cemetery to take your top-voted questions from https://pollgab.com/room/brento:
[Video] Office Hours at the Arctic Circle
Just after passing the Arctic Circle marker and celebrating with a glass of champagne, I took your top-voted questions from https://pollgab.com/room/brento.
https://youtu.be/gyyxKrv45bQ
[Video] Office Hours: While Stack Overflow is Down
Stack Overflow was down, so y'all posted questions at https://pollgab.com/room/brento and I gave 'em my best shot.
https://youtu.be/V_V245PaIYc
[Video] Office Hours at Sea: SQL Server Q&A
En route from San Francisco to Juneau aboard the Ruby Princess, I stopped to hit a few of your questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=o4P50_U-zUA
[Video] Office Hours: Nine Minutes of Answers
Post your questions at https://pollgab.com/room/brento and upvote the ones you'd like to see me discuss during my live streams. This week, I took a break from working on my PASS Summit sessions in order to chat:
https://youtu.be/95e5CBDI2Ck
[Video] Office Hours: Six-Minute Speed Round
The morning after the Data TLV Summit in Tel Aviv, I stood out on the balcony and answered a few of your questions from https://pollgab.com/room/brento, rapid-fire style:
https://youtu.be/M7h5Rm09ujY
[Video] Office Hours in Tel Aviv, Israel
Before speaking at the Data TLV Summit, I sat by the Mediterranean Sea and discussed the top-voted questions you posted at https://pollgab.com/room/brento.
https://youtu.be/YI5HeS_Nvjs
[Video] Office Hours: Long Answers Edition
This time on Office Hours, I let a few questions piled up at https://pollgab.com/room/brento that required in-depth answers to really do 'em justice. In particular, there was a statistics question that needed demos.
https://www.youtube.com/watch?v=d0CX1j0nKrA
[Video] Office Hours: Ask Me Anything About SQL Server at Vestrahorn
The beach at Vestrahorn mountain looks different every time we've visited: high tide, low tide, sun, clouds. Always a fun side trip when we're out in the southeast of Iceland. I took a break to answer the questions you upvoted here.
https://www.youtube.com/watch?v=TE2y8nKioG8
Performance Tuning
Changing Statistics Cause Longer Compilation Times
I need to be up front with you, dear reader, and tell you that you're probably never going to need to know this. I try to blog about stuff people need to know to get their job done - things that will be genuinely useful in your day-to-day performance tuning and management of SQL Server.…
Performance Tuning
Updating Statistics Causes Parameter Sniffing.
In my free How to Think Like the Engine class, I explain that SQL Server builds execution plans based on statistics. The contents of your tables inform the decisions it makes about which indexes to use, whether to do seeks or scans, how many CPU cores to allocate, how much memory to grant, and much more.
Production DBA
DBA Training Plan 10: Managing Index Fragmentation
In the last episode, we looked at your index designs with the output of sp_BlitzIndex. You might have gotten a little overwhelmed what with all the different warnings (and all the learning resources!)
You might have thought, "Is there an easier way?"
Does the Rowmodctr Update for Non-Updating Updates?
Update May 20 - make sure to read to the end for an update.
Okay, look, it's a mouthful of a blog post title, and there are only gonna be maybe six of us in the world who get excited enough to check this kind of thing, but if you're in that intimate group, then the title's already got you interested in the demo. (Shout out to Riddhi P. for asking this cool question in class.)
