[Video] Office Hours in Bulgaria
I went through your top-voted questions from https://pollgab.com/room/brento while in front of the National Gallery for Foreign Art in Sofia.
https://www.youtube.com/watch?v=12E_6VWKb_4
Query Optimization and Execution Plans
How SQL Server compiles, optimizes, and executes individual queries.
573 associated posts292 primary posts
[Video] Office Hours in Sofia, Bulgaria
I was honored to speak at the Present to Succeed conference in Sofia, Bulgaria, run by a former SQL Server MCM. Sofia is a beautiful city, and the gorgeous Patriarchal Cathedral of St. Alexander Nevsky (Wikipedia) was close to my hotel, so I dragged my tripod over there for an Office Hours session.
https://youtu.be/xn2afGkPcmo
[Video] Office Hours: Database Answers from a Hotel Room
I went through your top-voted questions from https://pollgab.com/room/brento before heading out to PGConf.dev in Vancouver.
https://youtu.be/sd6yO-nHDx0
[Video] Office Hours in a Hotel Room
While in Nashville for a creators conference, I went through your top-voted questions from https://pollgab.com/room/brento.
https://youtu.be/lDdQqlsyGN8
[Video] Office Hours: 16 Questions
Like Sixteen Candles, but different: I go through your top-voted questions from https://pollgab.com/room/brento. Strangely, my video and audio is ever so slightly out of sync in this episode.
https://www.youtube.com/watch?v=mmward6JyOw
T-SQL & Development
Coming in Entity Framework 9: Better Query Parameterization
Hallelujah. With current versions of Entity Framework, when developers add a mix of parameters and specific values to their query like this: [crayon-6a71f8e80f7ed862643737/] See how part of the filter is hard-coded (".NET Blog") while the other part of the filter is dynamically generated, an ID the user is looking for? That causes Entity Framework to…
[Video] Office Hours: Ask Me Anything About Azure and Microsoft Databases
Back at home in the office, time to settle in with a nice caffeine-free Diet Coke and go through your top-voted questions from https://pollgab.com/room/brento. Why caffeine-free? Because I slug multiple coffees first thing in the morning when I wake up (usually around 3am-4am), and by the time I stream with y'all, I don't need any more go juice.
[Video] Office Hours: The Long One
You posted a lot of great questions at https://pollgab.com/room/brento and I spent almost an hour covering these:
https://youtu.be/hBoAhZd6M4s
Performance Tuning
Does Your GROUP BY Order Matter?
Sometimes when you do GROUP BY, the order of the columns does matter. For example, these two SELECT queries produce different results:
[crayon-6a71f8e822492143538699/]
Their actual execution plans are wildly different:
They both use the index to retrieve their data, sure, but:
T-SQL & Development
Option Recompile is a Magic Turbo Button That Actually Works.
I didn't say that - Guy Glantser did.
Guy Glantser is an Israeli SQL Server guru with a ton of great presentations on YouTube. I've had the privilege of hanging out with him in person a bunch of times over the year, and I'll always get excited to do it again. He's not just smart, but he's friendly and funny as hell.
Performance Tuning
What Happens When Multiple Queries Compile at Once?
An interesting question came in on PollGab. DBAmusing asked: If a query takes 5-7s to calculate the execution plan (then executes <500ms) if multiple SPIDS all submit that query (different param values) when there's no plan at start, does each SPID calc the execution plan, one after the other after waiting for the prior SPID…
Performance Tuning
Find Recent Superstars: Answers & Discussion
Your query exercise for this week was to write a query to find users created in the last 90 days, with a reputation higher than 50 points, from highest reputation to lowest. Because everyone's Stack Overflow database might be slightly different, we had to start by finding the "end date" for our query. I'm working with the 2018-06 export that I use in my training classes, so here's my end date:
Performance Tuning
Finding Long Values Faster: Answers & Discussion
In last week's Query Exercise, our developers had a query that wasn't going as fast as they'd like:
[crayon-6a71f8e823097557876337/]
The query had an index, but SQL Server was refusing to use the index - even though the query would do way less logical reads if it used the index. You had 3 questions to answer:
Performance Tuning
Query Exercise: Finding Long Values Faster
Our developers have come to us with a problem query that isn't as fast as they'd like. Using any Stack Overflow database:
[crayon-6a71f8e82352d832450302/]
It has an index, but SQL Server refuses to use that index in the execution plan:
If we force the index with a query hint, we do indeed get dramatically lower logical reads. In my particular database's case, the clustered index scan is 141,573 logical reads - but scanning the DisplayName index alone is just 38,641 logical reads.
Performance Tuning
Improving Cardinality Estimation: Answers & Discussion
Your challenge for last week was to take this Stack Overflow database query to show the top-ranking users in the most popular location:
[crayon-6a71f8e8243b4083870444/]
And make it read less pages only by tuning the query? You weren't allowed to make index or server changes, and you weren't allowed to hard code the location in the query since it might change over time.
The Core of the Problem
The main problem is that when we run a statement (like SELECT), SQL Server:
Performance Tuning
Query Exercise: Improving Cardinality Estimation
Your challenge for this week is to tune a query. Say Stack Overflow has a dashboard that shows the top-ranking users in their most popular location. It's even got an index to support it:
[crayon-6a71f8e824bf8985250582/]
You can test it with any version of the Stack Overflow database. To test it, we'll turn on a couple of tuning options:
[crayon-6a71f8e824c03308439652/]
The actual execution plan does use our index - not just once, but twice:
Performance Tuning
Finding Tagged Questions Faster: Answers & Discussion
Your query exercise was to take this Stack Overflow query to find the top-voted questions for any given tag:
[crayon-6a71f8e82581c926180361/]
That's currently using this index in its execution plan:
[crayon-6a71f8e82582a773102921/]
And answer 3 questions:
What kinds of tags will perform worse than others for this query?
Could you change the query to perform better?
Could you change the indexes to perform better, without changing the table structure?
Free Video Training: Estimation and Statistics by Dave Ballantyne
It's hard to find good 400-500 level SQL Server training at any price, let alone free. Dave Ballantyne's past sessions from SQLBits are great examples of those.
Today, I'm going to review Dave's most recent session on Estimation and Statistics. It's from 2015, but it's still very relevant today.
[Video] Office Hours: The Question is Not the Problem Edition
I answer your top-voted Azure SQL DB and SQL Server questions from https://pollgab.com/room/brento and lose my temper about what you're doing.
https://youtu.be/64e2r7sn130
[Video] Office Hours: Ask Me Anything About the Microsoft Data Platform
Forgive the hoarse voice - I'm recovering from something COVID-ish. I went through your top-voted questions from https://pollgab.com/room/brento and did my best:
https://youtu.be/x-x4j_VND-s
