September 26, 2017 at 3:44 pm
GilaMonster - Tuesday, September 26, 2017 3:09 PMJeff Moden - Tuesday, September 26, 2017 2:53 PMIt turns out that most of the queries that we previously identified as "needs rework for performance" are the ones that took the biggest hits. Our problem is that there is no time to rework the queries because half of them are for monster imports and the other half are what the bloody ORMs have produced, which we can't even get to to apply the Trace Flag at the query level and certainly don't have the time to do a fix and the related regression testing (the queries have a broad impact throughout the GUI).Use Query Store.
Drop the compat mode down to SQL 2012's compat mode. Run the query. Use the Query Store plan forcing to force that plan, then turn the compat mode back to 2016. The forced plan will still be forced, and will remain forced unless you do something that makes it invalid (like drop an index)
Thanks Gail. Will that work with ORM code that causes a recompile every time that it's used because it can't actually find a match in the cache? I'm also not so sure that I'd want the proverbial "Sword of Damocles" hanging over my head praying that someone does drop an index or similar. I believe that we'll continue with the original plan before the upgrade to 2016... fix the resource intensive code. 😀 It does mean that we'll need to keep the Trace Flag active for now and may act as an accelerant for finally fixing the previously identified resource intensive code. (Always gotta look for that "silver lining" when such a thing happens :w00t:)
--Jeff Moden
Change is inevitable... Change for the better is not.
September 26, 2017 at 4:07 pm
Jeff Moden - Tuesday, September 26, 2017 3:33 PMLynn Pettis - Tuesday, September 26, 2017 3:04 PMMust have missed that warning about the "illegal" form of UPDATE.It's a JOINed update where the target table isn't in the FROM clause and has, instead, had aliases (like you might use in a correlated subquery but not in a subquery) that make the implied "join" work. It frequently works correctly. When it doesn't, it will eat your server. It usually takes the following forms...
UPDATE dbo.SomeTable st
SET SomeColA = ot.SomeColA
FROM db.OtherTable ot
WHERE st.SomePK = ot.SomePK
;
UPDATE SomeTable
SET SomeColA = OtherTable.SomeColA
FROM OtherTable
WHERE SomeTable.SomePK = OtherTable.SomePK
;You won't find such examples in BOL.
Heh... how ironic it would be if the new CE in 2016 would allow that junk. :sick:
Okay, think I ran into that myself years ago but had issues and don't do that anymore. Didn't realize people still do that.
September 26, 2017 at 6:10 pm
GilaMonster - Tuesday, September 26, 2017 3:09 PMJeff Moden - Tuesday, September 26, 2017 2:53 PMIt turns out that most of the queries that we previously identified as "needs rework for performance" are the ones that took the biggest hits. Our problem is that there is no time to rework the queries because half of them are for monster imports and the other half are what the bloody ORMs have produced, which we can't even get to to apply the Trace Flag at the query level and certainly don't have the time to do a fix and the related regression testing (the queries have a broad impact throughout the GUI).Use Query Store.
Drop the compat mode down to SQL 2012's compat mode. Run the query. Use the Query Store plan forcing to force that plan, then turn the compat mode back to 2016. The forced plan will still be forced, and will remain forced unless you do something that makes it invalid (like drop an index)
"I know nothing" ~ Jon (Snow)
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
September 26, 2017 at 8:47 pm
I just wanted to give everyone, Tom, Jeff, Hugo, Wayne & Steve a huge THANK YOU for providing your input, sharing your experiences and providing links to additional resources...
I'm glad to know that my instincts weren't too far off base on the whole ORM thing.
This really is a fantastic community. I really appreciate you guys. 🙂
September 26, 2017 at 8:59 pm
Beatrix Kiddo - Tuesday, September 26, 2017 2:45 AMoSounds like it's below your skillset, Jason.Sorry to ask, but is anybody here able to help me with my Availability Group puzzler here please?
We do use AO/AG but I my direct involvement with the implementation has been pretty minimal to date, I was brought in after the fact to create a solution to keep CDC up and running in the event of a fail-over from primary to secondary...
Assuming, tomorrow has a more relaxed pace than today, I'll I'll dig into the backup process and see what we're doing with AG.
September 27, 2017 at 2:08 am
jonathan.crawford - Tuesday, September 26, 2017 6:10 PM"I know nothing" ~ Jon (Snow)
"I know nothing" - Manuel, Fawlty Towers
Thomas Rushton
blog: https://thelonedba.wordpress.com
September 27, 2017 at 5:56 am
ThomasRushton - Wednesday, September 27, 2017 2:08 AMjonathan.crawford - Tuesday, September 26, 2017 6:10 PM"I know nothing" ~ Jon (Snow)
"I know nothing" - Manuel, Fawlty Towers
"I know notink!" - Sergeant Schultz, Hogan's Heroes
September 27, 2017 at 6:23 am
ThomasRushton - Wednesday, September 27, 2017 2:08 AMjonathan.crawford - Tuesday, September 26, 2017 6:10 PM"I know nothing" ~ Jon (Snow)
"I know nothing" - Manuel, Fawlty Towers
Or me, after being asked anything about SQL replication. Or SharePoint.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 27, 2017 at 7:18 am
Jason A. Long - Tuesday, September 26, 2017 8:59 PMBeatrix Kiddo - Tuesday, September 26, 2017 2:45 AMoSounds like it's below your skillset, Jason.Sorry to ask, but is anybody here able to help me with my Availability Group puzzler here please?
We do use AO/AG but I my direct involvement with the implementation has been pretty minimal to date, I was brought in after the fact to create a solution to keep CDC up and running in the event of a fail-over from primary to secondary...
Assuming, tomorrow has a more relaxed pace than today, I'll I'll dig into the backup process and see what we're doing with AG.
We had a guy in here a few years ago who wrote a new system using Entity Framework. He had heard that I was pretty good with sql and optimizing queries so he asked me if I could help him with a query that was running through EF. I of course told him I would happy to help. He then delivered a query that was somewhere around 1,000 lines and had no less than around 70-80 subqueries. He was kind of frustrated that I couldn't make his query faster. I told him I could but it would require starting from scratch and an explanation of what the query was attempting to do because the abomination that EF generated was never going to work. I doubt any automated code generating tool will ever be as good as human. The whole thing about coding is that is almost as much art as it is science. You can write code to handle the science part but that art part requires a human brain to process.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 27, 2017 at 8:16 am
Sean Lange - Wednesday, September 27, 2017 7:18 AMWe had a guy in here a few years ago who wrote a new system using Entity Framework. He had heard that I was pretty good with sql and optimizing queries so he asked me if I could help him with a query that was running through EF. I of course told him I would happy to help. He then delivered a query that was somewhere around 1,000 lines and had no less than around 70-80 subqueries. He was kind of frustrated that I couldn't make his query faster. I told him I could but it would require starting from scratch and an explanation of what the query was attempting to do because the abomination that EF generated was never going to work. I doubt any automated code generating tool will ever be as good as human. The whole thing about coding is that is almost as much art as it is science. You can write code to handle the science part but that art part requires a human brain to process.
It's just like the chess simulators. They can choose the best move based on all the possibilities for the next N moves (I'm not sure how many). Good chess players won't care about all the possibilities, they'll just check the best options and try to predict some moves based on their opponent.
September 27, 2017 at 8:33 am
Luis Cazares - Wednesday, September 27, 2017 8:16 AMSean Lange - Wednesday, September 27, 2017 7:18 AMWe had a guy in here a few years ago who wrote a new system using Entity Framework. He had heard that I was pretty good with sql and optimizing queries so he asked me if I could help him with a query that was running through EF. I of course told him I would happy to help. He then delivered a query that was somewhere around 1,000 lines and had no less than around 70-80 subqueries. He was kind of frustrated that I couldn't make his query faster. I told him I could but it would require starting from scratch and an explanation of what the query was attempting to do because the abomination that EF generated was never going to work. I doubt any automated code generating tool will ever be as good as human. The whole thing about coding is that is almost as much art as it is science. You can write code to handle the science part but that art part requires a human brain to process.
It's just like the chess simulators. They can choose the best move based on all the possibilities for the next N moves (I'm not sure how many). Good chess players won't care about all the possibilities, they'll just check the best options and try to predict some moves based on their opponent.
I am so bad at chess that back in high school I was playing against a chess program then and it predicted all my moves 100% of the time. Still like the game just don't play very often.
September 27, 2017 at 8:58 am
Jason A. Long - Tuesday, September 26, 2017 8:47 PMI just wanted to give everyone, Tom, Jeff, Hugo, Wayne & Steve a huge THANK YOU for providing your input, sharing your experiences and providing links to additional resources...
I'm glad to know that my instincts weren't too far off base on the whole ORM thing.
This really is a fantastic community. I really appreciate you guys. 🙂
Cheers
September 27, 2017 at 9:16 am
Sean Lange - Wednesday, September 27, 2017 7:18 AMJason A. Long - Tuesday, September 26, 2017 8:59 PMBeatrix Kiddo - Tuesday, September 26, 2017 2:45 AMoSounds like it's below your skillset, Jason.Sorry to ask, but is anybody here able to help me with my Availability Group puzzler here please?
We do use AO/AG but I my direct involvement with the implementation has been pretty minimal to date, I was brought in after the fact to create a solution to keep CDC up and running in the event of a fail-over from primary to secondary...
Assuming, tomorrow has a more relaxed pace than today, I'll I'll dig into the backup process and see what we're doing with AG.We had a guy in here a few years ago who wrote a new system using Entity Framework. He had heard that I was pretty good with sql and optimizing queries so he asked me if I could help him with a query that was running through EF. I of course told him I would happy to help. He then delivered a query that was somewhere around 1,000 lines and had no less than around 70-80 subqueries. He was kind of frustrated that I couldn't make his query faster. I told him I could but it would require starting from scratch and an explanation of what the query was attempting to do because the abomination that EF generated was never going to work. I doubt any automated code generating tool will ever be as good as human. The whole thing about coding is that is almost as much art as it is science. You can write code to handle the science part but that art part requires a human brain to process.
I couldn't agree more, Sean. Going back to the VB5 days and continuing through the View Designer :sick: in SSMS, I've never seen a code generator or wizard generate good code that I'd consider using in production. That's not to say that one doesn't exist or couldn't be written; just that I've never seen one.
September 27, 2017 at 9:50 am
Jeff Moden - Tuesday, September 26, 2017 3:33 PMLynn Pettis - Tuesday, September 26, 2017 3:04 PMMust have missed that warning about the "illegal" form of UPDATE.It's a JOINed update where the target table isn't in the FROM clause and has, instead, had aliases (like you might use in a correlated subquery but not in a subquery) that make the implied "join" work. It frequently works correctly. When it doesn't, it will eat your server. It usually takes the following forms...
UPDATE dbo.SomeTable st
SET SomeColA = ot.SomeColA
FROM db.OtherTable ot
WHERE st.SomePK = ot.SomePK
;
UPDATE SomeTable
SET SomeColA = OtherTable.SomeColA
FROM OtherTable
WHERE SomeTable.SomePK = OtherTable.SomePK
;You won't find such examples in BOL.
Heh... how ironic it would be if the new CE in 2016 would allow that junk. :sick:
The first form won't even parse (in 2014). Aliases can only be defined in the FROM clause, not the UPDATE clause. Perhaps you are confusing it with this form:UPDATE dbo.SomeTable
SET SomeColA = ot.SomeColA
FROM dbo.SomeTable st
INNER JOIN OtherTable ot
ON st.SomePK = ot.SomePK
In this form, they are referencing SomeTable twice: once unaliased in the UPDATE clause and once aliased in the FROM clause.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 27, 2017 at 9:56 am
drew.allen - Wednesday, September 27, 2017 9:50 AMThe first form won't even parse (in 2014). Aliases can only be defined in the FROM clause, not the UPDATE clause. Perhaps you are confusing it with this form:UPDATE dbo.SomeTable
SET SomeColA = ot.SomeColA
FROM dbo.SomeTable st
INNER JOIN OtherTable ot
ON st.SomePK = ot.SomePKIn this form, they are referencing SomeTable twice: once unaliased in the UPDATE clause and once aliased in the FROM clause.
Drew
I have had issues at times with this form, had to replace the unaliased table name after the UPDATE statement with the alias from the table in the FROM clause.
Viewing 15 posts - 60,001 through 60,015 (of 66,571 total)
You must be logged in to reply to this topic. Login to reply