We currently have a 4.0 SPIII SQL database with index hinting off (IndexHint=No).
It seems that once I upgrade the database even though I have index hinting turned off, it doesn’t seem to be acting that way. Client monitor traces and user filtering seem to prove this theory. Has something changed in 5.0 or do I still stick a record in the dbo.$ndo$dbconfig table - IndexHint=No?
Have you upgraded from SQL Server 2000 to 2005? SQL 2005 have a different behavior when selecting correct index.
Yes we are on SQL 2005 and have been for 6 months.
I have found out a littel more since I first posted this. Here is an example. If I go to the customer card from anywhere in the system (menusuite, navigating etc…) and then hit ctrl F5 to go to Cust. Ledger I get stuck for about 3 mintues. Client monitor traces look good (the correct key is being used).
If I enter the Customer card from the following method then I don’t get the 3 minute wait:
Create a button that calls the following code (CustCard is a form variable to Customer Card):
CLEAR(Customer);
Customer.SETFILTER(“No.”,“No.”);
Customer.GET(“No.”);
CLEAR(CustCard);
CustCard.SETTABLEVIEW(Customer);
CustCard.SETRECORD(Customer);
CustCard.RUNMODAL;
Click the button which brings you into the customer card, then hit ctrl f5 to go the cust. ledger. This method works without any delay.
Is this weird or what?
Apparently this is to do with SQL 2005 incorrectly re-using the wrong query plan. There are a few MS KB articles on this and some posts on the Sustained Engineering Teams Blog.
In simple terms SQL 2005 will re-use the query plan for a select statement rather than re-compiling it which to my understanding is something that did not happen in SQL 2000. Sometimes this is disaterous.
I was given a work around that involved installing cumulative update 4 for SQL 2005 SP2 and upgrading the NAV 5.0 to the latest hotfix. This gives you the option to create Query Plan Guides that tell the query optimizer to always recompile using the RECOMPILE hint.
It sounds crazy doesn’t it? But it worked. There is a query you can execute that tells you which select statements are the most expensive and you can copy and paste the select statement to build the query plan guide.
I guess I could dig out the actual URLs in a minute for you - but I was given them by logging the issue with Microsoft through PartnerSource. You will need to do this to get the latest NAV 5.0 hotfix anyway.
The Cumualtive update for SQL is required because without it, the query plan guides cause NAV to report that another user has changed the database since the operation started. This CU allows the recompile hint to be added to the select statement without causing NAV to think the database has changed.