Reading operators, properties, plan shape, and actual-versus-estimated behavior.
92 associated posts86 primary posts
Microsoft has been quietly making some amazing improvements for performance tuners in SQL Server 2012, 2014, and 2016. This week, we're going to introduce you to just how awesome they are. (They being the improvements, not Microsoft. You already knew they were awesome.)
Using the freely available StackOverflow database, let's start with a simple query - SELECT * FROM Users:
Everyone knows Implicit Conversion is bad
It can ruin SARGability, defeat index usage, and burn up your CPU like it needs some Valtrex. But what about explicit conversion? Is there any overhead? Turns out, SQL is just as happy with explicit conversion as it is with passing in the correct datatype in the first place.
This week, Brent, Erik, Jessica, Richie, and Tara discuss database modeling tools, how to learn about database corruption, the new cardinality estimator, and the only question that will be on our certification exams.
Here's the video on YouTube:
This isn't about performance
If you're interested in performance tests, you can get in the way back machine and read a couple posts by Adam Machanic here and here. I'm also not talking about the difference between them. There are a million articles about that. They're obviously different, but how does SQL handle that internally?
I love stuff like this!
Even though it's not on my list of dream features, it's pretty neat. Getting new views into what SQL is doing when queries execute is pretty cool. You can read the short and gory details at the KB here: Improved diagnostics for query execution plans that involve residual predicate pushdown in SQL Server 2012
Brent buys lunch for the ladies The purpose of this post is to show a bit of syntax that often gets overlooked in favor of using query hints to force joins to occur in a particular order. We'll start by creating three tables. One for employees, one for orders, and one for items in the…
Let’s say you want to have an addition built on your house. You contact a contractor, who comes to your house, looks at your lot, looks at what exists, and asks you questions about exactly what you want. He then gives you an estimate of the work – approximately how long he thinks it will take his crew, what supplies will be needed, and the cost of those supplies.
While chatting with attendees before my Don’t Fear the Execution Plan webcast, a question was posed: “As a DBA who mainly worries about backups, index fragmentation and checkdb, should I also be looking at execution plans?”
YES! Here’s why.
https://www.youtube.com/watch?v=yW-Re4TJcJ0
If you want to learn more about the different join algorithms, consult Wikipedia for an introduction to the basic material and links to additional information:
My gauge of an amazing book is simple: if I've got a question, and I reach for the book BEFORE I search the web, then it's an amazing book. Several times in the last two weeks, I reached for this book first.
StackOverflow users often ask, "I've got this query that runs slow - can you help?" Here's a few tips to get better, faster answers, including getting the execution plan and relevant Perfmon data.
I've mentioned Jeff Atwood of CodingHorror.com a few times over the years here, and it bears repeating: he writes a great blog for developers, and DBAs need to read it too. I also follow him on Twitter, and a couple of weeks ago he mentioned he was having problems deciphering execution plans: Holy cow -…
