T-SQL & Development
Slow “Having” Query? Try Pre-Filtering.
I was helping a client with a query, and I'm going to rework the example to use the Stack Overflow database for easier storytelling.
Say we need to:
Indexing and Statistics
Designing, evaluating, maintaining, and troubleshooting indexes and statistics.
568 associated posts270 primary posts
Office Hours: Read This, Watch This Edition
This week, a lot of the questions you posted at https://pollgab.com/room/brento just need pointers to resources at other places. Welcome to the Read This, Watch This series of answers.
Don't Blame Anthony: Our developers prefer writing TSQL queries directly in their .NET code rather than writing sprocs and calling them. What are the pros / cons to this approach?
[Video] Office Hours: Sponsored By Black Friday Edition
Y'all post questions at https://pollgab.com/room/brento, upvote the ones you'd like to see me cover, and then I talk through 'em.
https://youtu.be/aEe-uz2Ikvc
[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
Production DBA
Office Hours: One-Word Answers Edition
Some of the questions y'all post at https://pollgab.com/room/brento are pretty straightforward. Let's get 'er done. LetTheDbaHandleIt: My friend needs to track who has accessed what data. This is easy at OS and app level, but how can my friend verify that those who have direct DB access are not snooping on data inappropriately? e.g. payroll or…
[Video] Office Hours Live: Ask Me Anything
Got a question for me? Post your questions at https://pollgab.com/room/brento and upvote the ones you'd like to see me discuss. Before heading to NYC for vacation, I recorded a live Office Hours on my Twitch channel.
https://youtu.be/iuv-HOqlKBw
[Video] Office Hours: Ask Me Anything at the Mediterranean Sea
On the last day of my Israel trip, I went through the highly upvoted questions from https://pollgab.com/room/brento and answered 'em, standing in front of the beach.
https://www.youtube.com/watch?v=Rc5aIuLo2tA
[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: 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
[Video] Office Hours: Hotel Balcony Edition, Part 2
Post your questions at https://pollgab.com/room/brento and upvote the ones you'd like to see me cover. In today's episode, I sit down on a quiet hotel balcony and talk through a bunch of interesting questions:
https://www.youtube.com/watch?v=VJAwgkhq_Z0
[Video] Fragmentation Explained in 20 Minutes at SQLBits
What does fragmentation mean? How does it happen? Can you fix it with fill factor? Should you rebuild your indexes to fix it?
At the SQLBits conference, I tried a new way of explaining it using markers and pieces of whiteboard paper as database pages, and doing inserts live on the fly.
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 ?
[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
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: Thomas Franz: When I have a very large table with no statistics (or not on a specific column in the…
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
Why Adding Some Memory Doesn’t Fix All Caching Problems
It seems obvious: add some memory, and SQL Server should be able to cache data, thereby making queries run faster ... right?
Well, if you can't cache everything you need for a query, you might be surprised to find that SQL Server may still read the entire table up from disk, regardless of what parts of the table are currently cached in memory.
Performance Tuning
How to Add Invisible Indexes in SQL Server Enterprise Edition
Every now and then, a client says, "We want to add indexes to this third party application, but if we do, we'll lose support."
No problem - enter indexed views.
Columnstore Indexes are Finally Sorted in SQL Server 2022.
There's a widespread misconception that SQL Server's columnstore indexes are like an index on every column.
I debunk that myth in the first 30 minutes of my Fundamentals of Columnstore class, where I explain that a better way to think of them is that your table is broken up into groups of rows (1M rows or less per group), and in each group, there's an index on every column.
Performance Tuning
I Wish SQL Server Warned About Hard-Coded Estimates.
I wish we got a yellow bang on execution plans when SQL Server was making up an estimate out of thin air.
I'll give you an example: if you compare two columns on the same table, looking to find rows where they're equal, SQL Server has a hard-coded estimate that 10% of the rows will match.
