Beautifully explained! This is the first time i saw your videos and i must say that whole arrangement of keeping the query editor window in focus and talking alongside does help in making it easier to understand. Thank you for making this video.
Neat new features - but it looks like to get predictable performance, I'm still going to be extensively hinting all of my important queries to never get a pathological plan for a query that covers many rows (1e7 and more). I'm more than willing to sacrifice performance on small results to get consistently acceptable performance on large results.
Great lessons, cheers. Microsoft adding new features! That make it even more confusing!! And actually not providing much benefit. Sounds like projects we do! A bit put off by the adaptive joins, so is the solution always to recompile...? That's what it seemed like.
One thing would be to write 'inner hash join' instead of 'inner join' in places where you know the tables have many rows, to avoid sql choosing badly for you. It's too unpredictable otherwise. Clients and bosses don't want to know about sql inner workings, they want results. It seems MS is doing a bad job at optimizing the query engine. It's a complex problem, needs more work. Like warmup script from which it should learn, some hints to teach it typical queries, adaptive plans based on changes in used tables etc. Hints to tell it minimum memory to use, parallel or not, expect lots of results or not...
Never look a gift horse in the mouth: Looking at the mouth is a good way to tell the horse's overall health. Thus you're checking to see if the horse you were just given is in good health or possibly not. Probably not a good thing to do when someone just gifted you a horse!
After hassling with performance issues and parameter sniffing and trying various workarounds which kind of worked, I found the obvious solution: ie: when last was "exec sp_updatestats" run on my db? The DBA's hadn't done any! My stored proc went from running in 5 hours to 5 minutes!
Instead of telling the Admin to listen outside the exec's door, you could separate it into different procedures for ByCountry, ByState or ByCity, etc. and then SQL could optimize each accordingly. More procedures to manage is more work for maintenance, and this requires access to any code of course, but billable hours are good for the economy.
In Italy, somebody talk about "Shooting on your own balls"... your fantasy will help you about what "balls" are... suggestions: not tennis balls, not cannon balls... and so on...
The developers at Microsoft still borrows the GUI from Visual Studio 2010, for real? The GUI from SSMS 2008 is still prettier though, I mean why don't they use the GUI from their latest IDE, like VS 2019 or VSCODE that supports dark mode and all?
.........very insightful........to the point........:) .......and extremely sad at the same time, Microsoft server 2019 is for Big Data.........just as BigQuery for Google......Hey I will keep watching Brent Ozar................:) .............bye
Hi Brent, What is your call on SQL server 2017 over 2016 in terms of performance when you are dealing with SP (approx 1000 odd)in your database which are equipped with complex joins over tables, views, udfs and also having SORT and ORDERBY clauses in many of the SPs
Great overview. QUESTION: I downloaded the Stack Exchange 2013 (10 GB) from your website. On my system at home, I have SSMS v18.8 and SQL Server EXPRESS 2017 (about to upgrade to 2019 Express) Can I attach this database to EXPRESS ? thx.Joe
@@BrentOzarUnlimited Hi Brent ....appreciate the response. Actually, I'm not concerned with performance tuning. I was just wonder IF ... the Stack Exchange db would attach to SQL Server Express ... such that I have another db besides AdventureWorks to play around with on my home system. THX.
There are a lot of problems with RECOMPILE hints, starting with additional CPU consumption to compile every new plan. I talk about the pros & cons of each of the solutions in my Mastering Query Tuning class.
'And it's stable, just like me!'
I felt that in my soul.
What a great explanation. Brent, I watch your presentations for over a decade now. Day by day the value of your presentations goes higher and higher.
Thanks, I appreciate the kind words!
Beautifully explained! This is the first time i saw your videos and i must say that whole arrangement of keeping the query editor window in focus and talking alongside does help in making it easier to understand. Thank you for making this video.
Thanks Brent, this kind of videos really helps
Great video! Thanks for always share your knowledge.
Neat new features - but it looks like to get predictable performance, I'm still going to be extensively hinting all of my important queries to never get a pathological plan for a query that covers many rows (1e7 and more). I'm more than willing to sacrifice performance on small results to get consistently acceptable performance on large results.
That was a fantastic summary.. Thanks! Some really exciting stuff.
Lol such an entertaining video on sql, good info
Awesome 👍 Very well explained.
Thank you Sir Brent for posting such amazing videos on SQL Server.
Excellent explanation. Thank you!
Great lessons, cheers. Microsoft adding new features! That make it even more confusing!! And actually not providing much benefit. Sounds like projects we do!
A bit put off by the adaptive joins, so is the solution always to recompile...? That's what it seemed like.
One thing would be to write 'inner hash join' instead of 'inner join' in places where you know the tables have many rows, to avoid sql choosing badly for you. It's too unpredictable otherwise. Clients and bosses don't want to know about sql inner workings, they want results. It seems MS is doing a bad job at optimizing the query engine.
It's a complex problem, needs more work. Like warmup script from which it should learn, some hints to teach it typical queries, adaptive plans based on changes in used tables etc.
Hints to tell it minimum memory to use, parallel or not, expect lots of results or not...
Awesome as usual, as always!
Never look a gift horse in the mouth: Looking at the mouth is a good way to tell the horse's overall health. Thus you're checking to see if the horse you were just given is in good health or possibly not. Probably not a good thing to do when someone just gifted you a horse!
After hassling with performance issues and parameter sniffing and trying various workarounds which kind of worked, I found the obvious solution: ie: when last was "exec sp_updatestats" run on my db? The DBA's hadn't done any! My stored proc went from running in 5 hours to 5 minutes!
Ooo, bad news, that's not doing what you think. To learn more, watch www.brentozar.com/go/sniff
"stable like me" LOL, you are awesome!
would have been cool if you could have shown the query store saving the day on what I am now calling "The Reputation Incident!"
Thanks Brent. Always objective, always thorough. Parameter Sniffing is an old problem, right?
Yep, but you know, it just still keeps coming up. For us consultants, it's the gift that keeps on giving.
Brilliant Video - thanks for putting it together
Excellent info, Thanks
LOL! so the best way to run a fast query now is to throw my ear at the exective's door!
Instead of telling the Admin to listen outside the exec's door, you could separate it into different procedures for ByCountry, ByState or ByCity, etc. and then SQL could optimize each accordingly. More procedures to manage is more work for maintenance, and this requires access to any code of course, but billable hours are good for the economy.
Data Juggler good point, because all cities and countries are the same size. Oh, wait...
@@TheBrentOzar You had a subscriber for a whole hour. Good videos though.
In Italy, somebody talk about "Shooting on your own balls"... your fantasy will help you about what "balls" are... suggestions: not tennis balls, not cannon balls... and so on...
The developers at Microsoft still borrows the GUI from Visual Studio 2010, for real? The GUI from SSMS 2008 is still prettier though, I mean why don't they use the GUI from their latest IDE, like VS 2019 or VSCODE that supports dark mode and all?
Thanks Brent. Great video.
ALRIGHTY!!
I liked it!
.........very insightful........to the point........:) .......and extremely sad at the same time, Microsoft server 2019 is for Big Data.........just as BigQuery for Google......Hey I will keep watching Brent Ozar................:) .............bye
Thank u very much for this vedio
Most welcome 😊
Very good
Hi Brent,
What is your call on SQL server 2017 over 2016 in terms of performance when you are dealing with SP (approx 1000 odd)in your database which are equipped with complex joins over tables, views, udfs and also having SORT and ORDERBY clauses in many of the SPs
That's kinda beyond the scope of this video - you'll be best off asking that at a Q&A site.
Great overview.
QUESTION:
I downloaded the Stack Exchange 2013 (10 GB) from your website.
On my system at home, I have SSMS v18.8 and SQL Server EXPRESS 2017 (about to upgrade to 2019 Express)
Can I attach this database to EXPRESS ?
thx.Joe
We don't recommend trying to use Express for learning performance tuning. It's limited to 1 CPU core and 1GB RAM.
@@BrentOzarUnlimited Hi Brent ....appreciate the response. Actually, I'm not concerned with performance tuning. I was just wonder IF ... the Stack Exchange db would attach to SQL Server Express ... such that I have another db besides AdventureWorks to play around with on my home system. THX.
Great video
Brent, what laptop do you use for this demo?
An Apple MacBook Pro.
@@TheBrentOzar I wouldn't have guessed. I wish running SQL Server on Docker on Mac gets sorted out soon.
Will RECOMPILE get around this wandering memory grant problem? How likely is it that would be worth it?
There are a lot of problems with RECOMPILE hints, starting with additional CPU consumption to compile every new plan. I talk about the pros & cons of each of the solutions in my Mastering Query Tuning class.
@@TheBrentOzar Woah, that reply was faster than my database after I used sp_BlitzIndex to trim the fat.
@@tsilb HAHAHA