Drop or modify index in SQL-Server, but not in Navision

Elessar · 2012-11-25

Hey,

I have a question regarding the dependencies between Navision and SQL-Server. What would happen, if I for example would drop an index in sql, but would not deactivate it in Navision or if I add a new field to an existing index only on the SQL-Server, but not in Navision? Would this be a problem for Navision? My first reaction would be no, because Navision fires up the SQL-Statement and the SQL-Server does the rest. A problem could be, that when the the table in Navision gets updated or recompiled, the indices or better to say the modifications get dropped. I read a lot about this things also "The NAV/SQL Performance Field Guide" by Jörg Stryk (a very good book btw.), but I could not find a statement on that. Can you tell me something about this?

Best regards!

Marije_Brummel · 2012-11-25

why would you want to do this?

Elessar · 2012-11-25

Hello Mark,

because I first want to test it and if no problems occur (after a period of time), I would finally change it in Navision. Also on big tables it is faster if I change these things via SQL, because a change in Navision would lead to locks when I recompile the table. Another point is that I don't have access to all tables, so I first want to test it and then say our service provider to do this. Thats the basic points I want to do this,

Best regards!

kine · 2012-11-25

because a change in Navision would lead to locks when I recompile the table

BUt it will just do "CREATE INDEX" as you will do through SQL. It means same locking... I do not see any gain in this way. Only possible problems.

Elessar · 2012-11-25

Hello Kine,

what possible problems do you see or do you think could occur? As I already mentioned the gain for me would be, that I can test the new setting and if everything is ok, I could let it set in navision.
I thought stryk mentioned the problem regarding the longer blocking times in his book or maybe in a presentation, but maybe I am wrong.

Best regards!

Marije_Brummel · 2012-11-25

Create a new index and see if this works. If it does you can change the index in NAV and drop yours.

Just be careful that changing an index in NAV might accidentally change business logic too if the index is used in code and the sorting is specifically meant to be in a certain way.

Elessar · 2012-11-26

Hello Mark,

thanks for your answer! My hope was that someone already tried this, but this doesn't seem to be the case, so I have to try it on my own.

Just a little question to your notice. Can you please explain, why the index in Nav could have an impact on the business logic. As far as I know you can not say you want to use a special key in Nav. Isn't it the way that the SETCURRENTKEY-Function just defines the Order by part in the Sql statement? So if I for example deleted the key 'No., Type' in Navision and fire up a function/statement, which use SETCURRENTKEY(No., Type), the business logic should work like before, but now the sql server does an index scan, because there is not the right index on the server.
The same should happen, if I changed the order of the index to'Type, No.'. Navision should fire up the statement, can not find a right sorted index, use another index or maybe the 'Type, No.' index and do a resort. Am I totally wrong with this or what do you mean?

Best regards and thanks for your help!

Marije_Brummel · 2012-11-26

Let's say you have a key in NAV

"No.", "Type"

And for some reason you want to add "Location Code".

In this case the PK (CI) is "Document No.", "Line No."

Now the SETCURRENTKEY("No.", "Type") will sort on Line No. since that is added to the key. (index).

If you add "Location Code" to the key in NAV, secretly NAV will start to do SETCURRENTKEY("No.", Type, "Location Code") and no longer sort on Line No. since lines can have different location codes.

NAV will do this even WITHOUT you changing the C/AL code. SETCURRENTKEY will just browse the Key table for the first key that starts with these fields!

The same goes when you add a Key to the ILE with just Item No. as the second key in the list. It will mess up your Item Revaluation.

Hope this makes sense...

stryk · 2012-11-27

Uhm ... following this thread I read my name somewhat often ... I'm not sure if there's any misunderstanding ... :?

So let me put it like this:
Never fiddle with NAV indexes on SQL Server site. This was necessary with NAV 3.70 (or older), since NAV 4.0 we have all the properties we need inside C/SIDE. Using those properties grants 100% compatibility beween NAV Key/Index definition - the perception - and the SQL reality.
(Don't get me wrong: you still need to know IF and WHEN to use WHICH property, and you need to know what hell will break loose if you are doing anything wrong :twisted: )
I know there are 3rd party tools around which tweak NAV indexes on SQL Server, but - honestly - sooner or later (mostly sooner) people deeply regret this ... ](*,)

What I say is, that if you have evidence (e.g. by SQL Profiling) that you need another index, then it could be smart (for various reasons I won't explain in detail here) to add that index on SQL site - here it is most important to NOT interfere with NAV!

If you violate these "guidelines", then this could happen:

Indexes changed on SQL; NAV not "knowing" this: bad performance, any Dynamic Cursor could run berserk, since there's no index matching to the NAV key
Index deleted on SQL; NAV not "knowing" this: errors within NAV development, if you try to modify the related NAV key, NAV will try to DROP and re-CREATE that SQL index; if the index does not exist, the DROP command will fail ...

Elessar · 2012-11-28

Hello Mark, hello stryk,

at first: A very big thank you for your answers!

@Mark: Ok, now I understand it and know what you mean. I also took again a look into the documentation of the SETCURRENTKEY- function and yes, now I understand how it works. So thanks, I learned a new important part of Navision!

@stryk: Ok, this helped me alot! But I have another question on that. As far as I know there is no way in Navision e.g. to set the fill factor of an index or am I wrong here? If I have to modify it (because of a lot of page splits etc.), can or better to say do I have to do this via sql or what is the'right' way for this? Or is it just a fact that you should never touch a Navision-site index via sql?

Best regards and many thanks!

stryk · 2012-11-29

FlSch wrote:

@stryk: Ok, this helped me alot! But I have another question on that. As far as I know there is no way in Navision e.g. to set the fill factor of an index or am I wrong here? If I have to modify it (because of a lot of page splits etc.), can or better to say do I have to do this via sql or what is the'right' way for this? Or is it just a fact that you should never touch a Navision-site index via sql?

Well ... as I am developing some toolset for NAV/SQL Performance Optimization I'm using this one to do an intelligent index maintenance incl. adjusting optimal fill-factors ...

If you want to do index maintenance using "Maintenance Plans" I suggest to use the REBUILD task, assigning some Free Space of 5 or 10% - that's kind of "best practice" - so giving a Fillfactor og 90 or 95%.
(Have in mind that within MP you set the "Free Space" - not the "Fillfactor"!!! I'm not counting anymore how often I came accross a "Free Space" of 90% ... :shock: ](*,) )

DenSter · 2012-11-29

stryk wrote:

I'm not counting anymore how often I came accross a "Free Space" of 90% ... :shock: ](*,) )

Guilty

David_Singleton · 2012-11-29

We have all done it once right :oops:

Elessar · 2012-11-30

Hey,
thanks for all your tips and answers!I have learned a lot but I also learned, that there is much more I have to learn

. And now I know the secret about the maintenance plan

Best regards and many thanks!

stryk · 2012-11-30

David Singleton wrote:

We have all done it once right :oops:

Indeed - I remember #@&$§! SQL Server 2005 Build 1399 which had a severe bug: if you defined "free space" of 10% you actually got a "fillfactor" of 10% :whistle:

Elessar · 2013-01-08

Hey,

sorry that I open this topic again, but I have another question regarding the fill factor. I know that I can set the fill factor of an index via the maintenance plan. Before I set the fill factor for an index in the maintenance plan, I want to test, if another fill factor really has the impact on performance that I think.

The question is, if I can first rebuild the index with a specific fill factor to test it and if it works well, I can put this fill factor in the maintenance plan? I would test it by rebuild an existing index like this and drop the existing one:

CREATE NONCLUSTERED INDEX[$2] ON [dbo].[CRONUS$Item] ([Item No_], [Item Type])
WITH FILLFACTOR = 95
DROP EXISTING
GO

Would this be a problem for Navision or is it ok this way?

Best regards!

stryk · 2013-01-09

FlSch wrote:
I would test it by rebuild an existing index like this and drop the existing one:
CREATE NONCLUSTERED INDEX[$2] ON [dbo].[CRONUS$Item] ([Item No_], [Item Type])
WITH FILLFACTOR = 95
DROP EXISTING
GO
Would this be a problem for Navision or is it ok this way?

Well, technically this will not be a problem, but CREATE WITH DROP_EXISTING is not the IMHO recommended way to accomplish this; I'd rather use the ALTER INDEX command (this would be alos used by the MP):

ALTER INDEX [$2] ON [dbo].[CRONUS$Item] REBUILD 
WITH (MAXDOP = 64, ONLINE = OFF, FILLFACTOR = 95)
GO

Elessar · 2013-01-09

Hey stryk,

thanks a lot, it works like a charm. Now I can test the new settings and implement them in the maintenance plan.

Best regards!

Howdy, Stranger!

Quick Links

Categories

Drop or modify index in SQL-Server, but not in Navision

Comments