When you are runnning Dynamics-NAV on Sql Server, there are a few things to keep in mind, so that your ERP application stays as performant as possible.

When you are running Dynamics-NAV on Sql Server, the design of the objects (like tables) will still remain in Dynamics-NAV. But every table in Dynamics-NAV will be translated into a similar table, hosted in the Sql Server database.

A Key in a table in Dynamics-NAV will be translated into an Index on the corresponding table in Sql Server. (The primary Key will become a unique clustered index and the secondary keys will become unique non clustered indexes on Sql Server.)

Why do we needs Keys in a database ?

Well, keys in a database are like indexes in an encyclopedia. They speed up the retrieval of data. So by creating lots of keys on a table, data (rows) of that table will be able to be retrieved faster.

So, why not create as much keys as possible on a table ?

Well, because keys also have disadvantages. Every time an insert/update/modify occurs on the table, alls of the keys of that table will need to be updated. Keys are kept in what is called a B-tree. Summarized, they contain nodes (pointers) to be able to retrieve date more quickly and avoid having to do a full table scan.

So, the more keys you have on a table, the more overhead you get every time you do an insert/modify/delete on a table.

Why do we usually needs keys in Dynamics-NAV ?

• 1) for Searching (quickly retrieving the resultset of a query)
• 2) for Sorting: if you want to sort a table, then Dynamics-NAV needs a key to do this
• 3) for SIFT (SumIndexFieldsTechnology)

Why do we usually needs indexes in Sql Server ?

• 1) for Searching  (quickly retrieving the resultset of a query)

Sql Server does not need an index on a table to be able to sort his resultset !

That means that all of the keys in Dynamics-NAV that are only there for sorting purposes, are obselete in Sql Server, and so are only causing overhead and performance decrease on inserts/updates/deletes !

As a solution for this, in Dynamics-NAV when you have a look at the properties of a key, there is a boolean property called: MaintainSqlIndex. (Yes or No)

By default it is put to Yes, this means that the key will be translated as an index on Sql Server. By changing this property to No, the index on Sql Server for this key in Dynamics-NAV will no longer be created & maintained.

So if you put the MaintainSqlIndex property to No for all of the keys that are only ment for sorting purposes, you will get a performance increase of your Dynamics-NAV application, when it is running on Sql Server !

There are also other interesting properties regarding performance, I'll be writing about them in another blog article very soon, so stay tuned...

Comments

[waldo]

I found a very interesting article about index hinting:
http://dynamicsusers.net/blogs/waldo/archive/2007/08/21/making-nav-use-a-certain-index-on-sql-server.aspx

[vibe]

I guess " an new expertise" is a well choosen word!

[waldo]

It is really a wonderful and relative new area of expertise (If I might call it that :) ).

We created a statement (quite a large one) that is going to give you the indexes that you can delete. The statement is based on the dm views in SQL 2005.

In this statement, we incorperated the SQLPerform tools in that way that the statement gives us Dynamics NAV info (table No., table name, company name, key fields, field nos, etc. ).

Like Steven already mentioned is that there is much info in these views, but keep in mind that these statistics are lost when restarting server/service or when deleting the cache. And you really should have a couple of months info in the views before starting to base your deletion of keys on this. That's also the reason why I will always do tuning a couple of months after the go-live.

About the pre-tuning. We did this very succesfully at a customer so I can only recommend this. In 5.0, the objects are already pretuned by Hynek, and when you transfer this tuning to a 4.0 database, you can have some positive effects.

Regards!

[Steven]

Yes, that's correct.

[vibe]

So the SQL tuning is different in each implementation and needs to be tuned based on fields needs for functionality. Although I guess a number of standard functionalities could be tuned in advance and final tuning in the implementation itself.

[Steven]

That is indeed the question, but the answer depends on a few things.
Sql Server keeps statistical information about the indexes on tables on how and when they are used and uses this statistical information when he's building his query execution plan.
You can consult this statistical information to see if the indexes that were created are used by Sql Server or not, and if they are never used you can remove them with the MaintainSqlIndex property of the key in Nav.
Soon I will post a blog entry explaining how you can consult and interprete this statistical information on Sql Server.

But, the way that indexes are used in a database, will depend on the kind of queries that are executed on a database, and so can be different for each implementation of the same database.

[vibe]

Ok, thx Steven. The question is : how can we identify which are the indexes that are necessary for SQL and those that are not really necessary and create overhead?

[Steven]

Sql Server does not need an index on a table in order for it to sort the data, it is able to do sorting without having any index on a table.
But indexes can speed up the process.

The problem is that having many indexes on tables might speed up the retrieval of data but creates a big overhead on inserts/updates/deletes.

And even for the retrieval of data Sql Server will only use a few indexes, so most of the indexes that are created by NAV on Sql Server only create overhead, espacially those that are only created as Keys in NAV for sorting.

[vibe]

Does SQL server needs keys for sorting yes or no? Or is it sometimes yes and sometimes no?

[Steven]

Yes, you are right about this. But in fact usually on tables in DYNAMICS-NAV there are a lot of Keys that will be translated into Indexes on Sql Server.
Usually Sql Server will only use a few of them when executing queries.
Most of them are obsolete (never used) on Sql Server, especially those who are only there for sorting purposes in Dynamics-NAV.
Sql Server also keeps statistical information about the indexes that exist on tables.
Soon I will post a blog article that will explain how you can see this statistical information and how it can help you in desciding which indexes are no longer needed on Sql Server.

[waldo]

Dear all,
Be careful with the statement:"Sql Server does not need an index on a table to be able to sort his resultset !".

In a way, you're right, but we notices that sometimes it's better to create a key just for sorting on SQL.