[Video] Office Hours: Great Questions, Part 2
Every time I think, "There can't be any more SQL Server questions left," y'all post more great ones at https://pollgab.com/room/brento!
https://www.youtube.com/watch?v=ZcF2sG5J4sk
Index Design and Access Paths
Clustered and nonclustered indexes, keys, included columns, and access paths.
113 associated posts109 primary posts
[Video] Office Hours: Ask Me Anything About SQL Server and Azure
Got questions about the Microsoft data platform? Post 'em at https://pollgab.com/room/brento and upvote the ones you'd like to see me cover. Today's episode finds me in my home office in Vegas:
https://youtu.be/QRRFAW7rgVo
Production DBA
Office Hours Speed Round, Text Edition
Got questions for me? Post 'em at https://pollgab.com/room/brento and upvote the ones you'd like to see me cover. I filter out the ones that are too short for video answers, and here's the latest batch:
Corrupted: Should DBCC CheckDB be run on secondary replicas in AG as well ? Is it recommended to attach corrupted database to Prod server, to check how your integrity check job will react ?
Office Hours: Dodging Work Edition
Post your questions at https://pollgab.com/room/brento and upvote the ones you'd like to see me cover. Today, I'm dodging work, so I went through your questions while I waited for the coffee shop to open:
https://youtu.be/xBxvb6vy97U
Performance Tuning
When Should You Use DESC in Indexes?
The short answer is that if your query orders columns by a mix of ascending and descending order, back to back, then the index usually needs to match that same alternating order. Now, for the long answer. When you create indexes, you can either create them in ascending order - which is the default: [crayon-6a6ece8c5ab9d946850757/] Or…
[Video] Office Hours: Ask Me Anything About SQL Server
Let's pick up right where we left off yesterday. I've got more time and champagne, so let's keep going through your top-voted questions from PollGab.com/room/brento.
https://www.youtube.com/watch?v=raWyoVlW0lc
[Video] Office Hours: Ask Me Anything About SQL Server
I'm back in San Diego, so let's sit out on the balcony, enjoy a tasty beverage, and go through your top-voted questions from PollGab.com/room/brento.
https://www.youtube.com/watch?v=Z7xa7H2kqkQ
[Video] Office Hours in Cabo: Ask Me Anything About SQL Server
Let's get together at sunrise in Cabo San Lucas, Mexico and talk through your highest-upvoted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=sw2v_TInvac
[Video] Office Hours at Silver Strand State Beach: Ask Me Anything About SQL Server
It's a beach day! Let's hang out at Silver Strand State Beach and I'll take your highly voted questions from https://pollgab.com/rooms/brento.
https://www.youtube.com/watch?v=qTocbvnQgFs
[Video] Office Hours at the Seltjarnarnes Lighthouse
You posted and upvoted SQL Server questions at https://pollgab.com/room/brento, and I went out for a walk on the Seltjarnarnes peninsula to the lighthouse to answer 'em.
https://youtu.be/IB2EH2irD6s
[Video] Office Hours: Arctic Circle Puffins Edition
Erika and I are on the road in Iceland, roaming around the countryside, and I'm taking you with us. We visited Grímsey, a tiny island on the Arctic Circle, home to gazillions of sea birds like puffins and arctic terns. I took about half an hour to answer your most highly-voted questions from here:
Performance Tuning
When Do I Need to Use DESC in Indexes?
If I take the Users table from any Stack Overflow database, put an index on Reputation, and write a query to find the top 100 users sorted by reputation, descending:
[crayon-6a6ece8c5b169895136389/]
It doesn't matter whether the index is sorted ascending or descending. SQL Server goes to the end of the index and starts scanning backwards:
Unindexed Foreign Keys Can Make Performance Worse.
I know. You, dear reader, saw that title and you came in here because you're furious. You want foreign key relationships configured in all of your tables to prevent bad data from getting in.
But you gotta make sure to index them, too.
Performance Tuning
[Video] How to Think Like Clippy (Subtitle: Watch Brent Wear Another Costume)
Remember Clippy, the Microsoft Office assistant from the late 1990s? He would pop up at the slightest provocation and offer to help you do something - usually completely unrelated to the task you were trying to accomplish. Sadly, the Office team told Clippy that he didn't make the stack rankings cut, so he relocated over…
[Video] How to Think Like the SQL Server Engine: All-Demo Edition
You’ve heard of my free How to Think Like the Engine class, and maybe you even started watching it, but…it has slides, and you hate slides.
Wanna see me do the whole thing in Management Studio, starting with an empty query window and writing the whole thing out from scratch live? This session is for you.
Performance Tuning
Things to Consider When SQL Server Asks for an Index
One of the things I love about SQL Server is that during query plan compilation, it takes a moment to consider whether an index would help the query you're running. Regular blog readers will know that I make a lot of jokes about the quality of these recommendations - they're often incredibly bad - but even bad suggestions can be useful if you examine 'em more closely.
Performance Tuning
WHERE GETDATE() BETWEEN StartDate AND EndDate Is Hard to Tune.
Say you've got a memberships (or policies) table, and each membership has start & end dates:
[crayon-6a6ece8c5cbdd601985465/]
If all you need to do is look up the memberships for a specific UserId, and you know the UserId, then it's a piece of cake. You put a nonclustered index on UserId, and call it a day.
Performance Tuning
Building SQL ConstantCare: Let’s Tune an Index in Postgres.
This week, our SQL ConstantCare® back end services started having some query timeout issues. Hey, we're database people - we can do this, right? Granted, I work with Microsoft SQL Server most of the time, but we host our data in Amazon Aurora Postgres - is a query just a query and an index just…
Performance Tuning
The Two Ways to Fix Non-Sargable Queries
In the database world, when we say something isn't sargable, what we mean is that SQL Server is having a tough time with your Search ARGuments. What a sargable query looks like In a perfect world, when you search for something, SQL Server can do an index seek to jump directly to the data you…
Performance Tuning
How to Think Like the Engine: Index Column Order Matters a LOT.
We've been working with the clustered index of the Users table, which is on the Identity column - starts at 1 and goes up to a bajillion:
And in a recent episode, we added a wider nonclustered index on LastAccessDate, Id, DisplayName, and Age:
[crayon-6a6ece8c5ecc6573827247/]
Whose leaf pages look like this:
