April 10, 2019 at 1:20 pm
Have a 3rd party sql app for the most part 10-20 users, runs great but twice no 3-4 standard statements on the app go off the rails and bury the cpu. Yes stats and index rebuilds done 3 times a week (db only 60 gig), look at query store and plan variation is off the chart, in one instance 2700 reads another one 749999 reads. Forced plan and that seems to have cured it for no. Ran this app on 2014 last year no problems ever this has now happened twice. Any one else on 2016 seen this?
April 10, 2019 at 1:36 pm
When we migrated from 2008 R2 to 2016 we saw some cases of queries that ran smooth in 2008 R2 and in 2016 they would take minutes!
To fix the issue temporarily what we did was to change the database Compatibility Level back to 2008 (100) (in your case i guess 2014) when we identified what had changed between these versions we modified the queries at the application level and swapped back.
If you do this you'll sacrifice many features of 2016 until you solve the query issues of the app.
OR
We have a SQL Server some users use for statistics using a copy of production, they have VERY BAD queries (SERIOUSLY QUERIES TAKING 2-3 HOURS), our TEMPDB disk is always full and our CPU in the skies, tired of the monitoring department alerts about CPU we took a look at Resource Governor, hell we even turned off the SQL alerts since our email would cry.
What we did is we tried Resource Governor, and its been working SMOOTHLY, so smoothly that they haven't noticed of the changes don't ask me how but i have seen their queries running even more efficiently in the way that i don't see queries taking 2-3 hours now, the server is now safe 🙂
OR
Forgot to add another option that we didn't tried but Microsoft Support told us about, if you want you could try to use the Compatibility level to 2016 and below use the "Legacy Cardinality Estimation" in your database.
Greetings.
April 10, 2019 at 5:57 pm
On those occasions the query produces a skewed execution plan, see if you can correlate the point in time with your index and statistics maintenance window. I know that maintenance can flush the plan cache, and it may also temporarily produce a bad plan if the statistics are in process of rebuilding.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 10, 2019 at 6:29 pm
tried that already index rebuild happened 2 days before. Bad plan all of a sudden chose wrong index went from a few thousand to over 600K reads. This happened a month ago. Checking with our VMWARE guy to see if host moved. This is perplexing, just out of the blue chooses god awful plan in fact it chose a different index that has nothing to do with statement. Starting to love query store, all I had to do was look at regressed queries and there it was. Ugh
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply