In Dev DB (refreshed a few weeks ago): 1.2 seconds
Prod and Dev are on the same machine
I even exported the (“slow”) Prod BAQ and imported it into Dev and it still runs faster (1.3 seconds) in Dev!
It’s just this one that is doing this, and only today (so far).
For example...
Another BAQ (analyzes PartDtl)
In Production: 47 seconds
In Dev DB: 55 seconds
Those are normal times; there are a lot of factors there, but point is that the times are similar in both environments
So what is it about this BAQ that makes it so slow in Prod?
I did the SQL plan for each one. I know the pic is uber-small, but clearly they actually use a different flow to get there. WHY?
I see the bit about adding an index, and I have seen some chatter on that here @Noffie@ERPSysAdmin
Specifically it says:
/*
Missing Index Details from Rev_PROD.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 11.2492%.
*/
/*
USE [Production]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [Erp].[PartRev] ([Approved],[Company])
INCLUDE ([PartNum],[EffectiveDate],[Plant])
GO
*/
So I guess (a) where do I see what indexes do exist, and (b) do you really think that’s the problem?
I would suggest you run a quick statiscs update on all tables involved in this BAQ in Production and see what happens. 9/10 times it flies right after. I’m not sure how you are doing maintainence on your DB but statistics are as important if not more than indexes sometimes.
Try this (this takes a few seconds to run even on the largest of tables and it shouldn’t hurt much maybe a small performance hit for a little while but perfectly fine in my eyes.
UPDATE STATISTICS Erp.PartRev WITH FULLSCAN
UPDATE STATISTICS Erp.<YourOtherTables> WITH FULLSCAN
UPDATE STATISTICS Erp.<YourOtherTables> WITH FULLSCAN
.
.
.
Ask @jgiese.wci what a difference a statistics update can make. We’ve both been blow out of the water before. Also you may have old / cached plans on this BAQ in SQL you can change that by adding an OPTION(RECOMPILE) at the bottom of the query. This is kind of tricky to do, but if you don’t have an order by clause then go go
This forces SQL to ditch its old Plan and generate a new one. You pay for it a little by having to generate a new plan but the cost is small compared to the 100X you are seeing now.
Try one and then the other if the first one doesn’t work I’m curious as to what you find. Adding an index is fine but those suggestions are query specific and those indexes though will help your one query, may hurt other areas of the system so be careful.
Both of those should be fine to do right now, you may experience a brief performance hit.
Try the option recompile first since that only affects the BAQ itself.
But the Statistics is fine I do it all the time as long as you keep to the few tables in taht BAQ. You should be doing a full statistics update pretty frequently as part of your maintenance.
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
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.
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.)
@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?
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
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
) if it’ll make it easier to push it through