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:
Execution Strategies and Runtime Behavior
Joins, parallel execution, memory grants, spills, and adaptive execution.
93 associated posts89 primary posts
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: 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: Ask Me Anything About SQL Server
You posted questions at https://pollgab.com/room/brento, upvoted the ones you'd like to see me cover, and I did the needful:
https://www.youtube.com/watch?v=PFjWYjZbES0
[Video] Office Hours: Palm Springs, California Edition
On a road trip from San Diego to Vegas, I stopped in Palm Springs and answered your highly-upvoted questions from https://pollgab.com/room/brento.
https://youtu.be/1P_1DFXy-h0
T-SQL & Development
Don’t Treat SQL Like a Programming or Scripting Language.
In a programming language like C# or Java, you tell the computer what to do, in order. Get the customers from California, then get their invoices, then sum the total. SQL, on the other hand, is a declarative language where you declare the shape of your result set: Get the total sales from Californian customers.…
[Video] Office Hours: Q&A on the Beach at Sunset
You posted and voted on questions at https://pollgab.com/room/brento and I answer 'em on the beach. It's not your eyes: the camera's hunting for focus a lot in this video.
https://www.youtube.com/watch?v=nSfJmaY5sL8
Performance Tuning
5 Ways to Measure a Query
In my free How to Think Like the Engine class, we start out by measuring query performance using logical reads. That's the number of 8KB data pages that SQL Server has to read in order to find your query's results. That's the measure I use the most because generally speaking, the less data your server has to read, the faster your query will finish.
[Video] Office Hours: Ask Me Anything About SQL Server at Hallgrímskirkja
I hang out by Hallgrímskirkja, the modern church in Reykjavik, and take your highly-voted questions from https://pollgab.com/room/brento. Here's what we covered:
https://www.youtube.com/watch?v=slku6V9mWnw
[Video] Office Hours at the Eldfell Volcano Crater
You posted and upvoted questions at https://pollgab.com/room/brento, and I ascended - well, drove into - the Eldfell Volcano in the Westman Islands to answer 'em:
https://youtu.be/dnD3qAuhBUE
[Video] Office Hours: Ask Me Anything About SQL Server
You've got questions, I've got answers. Welcome to another edition of Office Hours, where you post your questions here, upvote the ones you'd like to see me talk through, and I do your bidding:
https://www.youtube.com/watch?v=Akg7fguhz2I
T-SQL & Development
Why Are Linked Server Queries So Bad?
Remember when you were in school, and you had a crush on someone? You would write a note asking them to be your valentine, and you'd ask a mutual friend to pass the note over to them.
The adult equivalent is linked server queries.
T-SQL & Development
How Scalar User-Defined Functions Slow Down Queries
When your query has a scalar user-defined function in it, SQL Server may not parallelize it and may hide the work that it's doing in your execution plan.
To show it, I'll run a simple query against the Users table in the Stack Overflow database.
[crayon-6a6ef5ddd02ca499384117/]
I don't have an index on Reputation, so SQL Server has to sort all of the Users by their Reputation. That's a CPU-intensive operation, so SQL Server automatically parallelizes it across multiple CPU cores:
Performance Tuning
MAXDOP Isn’t Really MAXDOP. It’s More Like DOP.
Here's how Books Online describes the Max Degree of Parallelism setting: You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. And here's what the SQL Server 2019 setup screen says: When an instance of SQL Server runs on a computer that has more…
T-SQL & Development
Why Full Text’s CONTAINS Queries Are So Slow
SQL Server's full text search is amazing. Well, it amazes me at least - it has so many cool capabilities: looking for prefixes, words near each other, different verb tenses, and even thesaurus searches. However, that's not how I see most people using it: I've seen so many shops using it for matching specific strings, thinking it's going to be faster than LIKE '%mysearch%'. That works at small scale, but as your data grows, you run into a query plan performance problem.
Performance Tuning
“UPDATE, INSERT, and DELETE are not normally processed in parallel”
Years ago, when troubleshooting performance, I stumbled across this Microsoft documentation on parallel query processing that says: Certain types of statements cannot be processed in parallel unless they contain clauses, however. For example, UPDATE, INSERT, and DELETE are not normally processed in parallel even if the related query meets the criteria. But if the UPDATE…
T-SQL & Development
How Do I Know If My Query Is Good Enough for Production?
When I ask that question, here are some of the things I think about: How many times am I going to run it? Is it a one-off task, or is it going to run thousands of times per second on the front page of a popular web site? What time of the day/week will it…
Bad Idea Jeans: Building Big Query Plans
When you build a monitoring tool that sends advice via email, you discover two things:
Some people have really, really big execution plans
Email servers have reasonable limits on file attachment sizes
T-SQL & Development
Cool Query Hints
The SQL Server documentation has a pretty cool list of query hints:
Yeah, I surf in dark mode. I'm a dark kinda guy. But wait - what's that colored box? ENHANCE!
Performance Tuning
Where Should You Tune Queries: Production, Staging, or Development?
The #1 fastest way to tune queries is in the production database, on the production server. I know. Put the knife down. You're not happy about that, but hear me out: I'm specifically talking about the fastest way to tune queries. In production, you can guarantee that you're looking at the same data, hosted on the…
