@josecgomez BAQ thing (in Production) did this:
Wow, is all I can say.
Just wow. 120 seconds → 0.9 seconds.
3 Likes
A bad plan will ruin your day, bad plans are usually because of bad statistics. I’d wager if you do the stats update and remove the recompile thing you’ll get similar results.
BTW that option recompile is a bit of a hack I’m sure you could tell, we are “injecting” a bit of SQL magic via that Order By Clause. So I can’t guarantee it’ll work forever
–Cheers.
2 Likes
That is where you are wrong. It’s all magic to me.
Meh, maybe a little less magic as each year goes by. Still, 99% magic.
1 Like
PS, you are right, of course, no statistics plan/job in SSMS that I see. I remember stats being an issue before, and I thought I/someone had set up something for that, but guess not.
Will do!
@Patrick.Ferrington, @Edge any chance we can get an OPTION(RECOMPILE) checkbox on the BAQ to bypass bad plans… pretty please with a cherry on top.
5 Likes
PSA for future readers, check off Persist in Query as Jose’s screenshot that I borrowed below shows. Otherwise it goes away after you close the window.
If it is anything like the QueryOldCompanySecurity setting, don’t even reopen the Execution Settings window, or if you do, click cancel. (See linked post below.)
1 Like
@josecgomez , if you have the “Auto create statistics” and “Auto update statistics” properties of the database set to true, is there any added benefit of updating statistics periodically through a maintenance plan?
I assume you mean something besides the hack around with the Order By clause? we can probably figure something out. timing is bad though - you’ll forget about this before I can sneak into the next ‘current’ release.
Do you think an actual fully exposed checkbox on the main UI or its own specific execution setting rather than the order by work around?
1 Like
Either a Checkbox or an execution option would be nice the ability to control which plan / index / stat a query uses would be nice though too advanced for “generic” users. And I understand how allowin just arbitraty SQL Inject would be bad. So maybe a few pre-set options that can be selected just like we do that OrderBy Hack but with the a few (all ??) of the query hint options
If not at least the OPTION recompile one would be grand.
2 Likes
Yes
2 Likes
I can also put it in the ideas portal where it can be properly ignored like the rest of them 
(just kidding 
) if it’ll make it easier to push it through
1 Like
Nope, pretty similar.
Next version has OPTION execution setting so you can add whatever you need there. Also there will be option to get query statistics
5 Likes
Oh, and also actual execution plan will be shown, not estimated
2 Likes
Ima moving my votes…
Awesome - of course, this is useful if you knew in the first place that such a thing exists.
Us mortals would not even think to use something called “OPTION” nor have any clue what to put in it.
Maybe add something to the ICE tools guide or I don’t know, some way to spread the word.
This is a game changer.
EDIT: I searched EpicCare for OPTION RECOMPILE and got a whopping 5 Knowledge Base results, and none were about this issue (just random hits). So not only is this obscure to me, even support seems to have never heard of it. Or not documented it, anyway.
Again, please make this known somehow to the commoners. @aidacra
3 Likes
@josecgomez - I’m curious, do you do like nightly or weekly Update Statistics on your databases with a maintenance plan / tool like Ola Hallengren? Reading through this excellent thread made me want to look into our own setup - I knew I setup Ola tools / sql server agent jobs for optimizing the indexes every Sunday morning in all our databases. I just assumed statistics were handled as part of that, but now this page has me wondering:
Looks like my SQL Server Agent job might not be updating statistics?
EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES',
@LogToTable = 'Y'
Is there a best practice (or Epicor-specific best practice) for statistic updating frequency, indexes vs. columns, etc? Thanks much!
1 Like
I do use Ola’s script, I also do Nightly Statistics Update with that script.
1 Like