Navision with SQL option - update statistics

lzr

When I read through the manual it says first that SQL server updates these statistics (for SQL Query Analyzer) on a regular basis, then at the end it says you have to schedule it yourself, which one is it?

Also is this the same as the navision optimize which update statistics for the info? database->info->tables->optimize ?

kriki

You can create a DB maintenance plan to do that. If you do this, SQL does it on a regular basis

It is not the same. Optimizing in Navision for example deletes 0-values from SIFT-tables and some other things to optimize.

Update statistics is done for another thing.
E.g.
Table 32: you have an index "Item No." and another index "Document No."
You have table T32 and you have a filter on "Item No." and another on "Document No.". So which index is the best? This depends on the data in the table. If you have a document no. that goes down to 1000 records and "Item No." that only has 10 records, index "Item No." is better. If it is the other way around (mostly it is), index "Document No." is better.
With the SQL-statistics, SQL knows what possibly is the best index to use. So it is necessary to update it regularly.

bbrown

The rules for rebuilding statistics are the same as for rebuilding indexes. The more often the table data is changed, the more often they should be updated.

lzr

Ok, I understand it needs to be done on a regular basis then, I read somewhere on forum that once a week would be good.

I am getting a bit confused about the keys in Navision SQL option though. I read somewhere that boolean and options are fields you do not want as keys in a Navision SQL option environment since they SQL want a field that is more or less unique.

However, if SQL Query analyzer decides what index to use, it can't matter much if I use these cause SQL Query analyzer will decide whether to include these or not? Maybe this with option and boolean only applies if I use MaintainSQLIndex on the selected key?

bbrown

Yes, SQL decides which index to use. However, the keys available on the table, along with your selected sort order and filters set, will influence SQL.

DenSter

The performance impact is not so much that there is some 'unused data' in the indexes. If that were the whole extent then it wouldn't really matter. It's updating those indexes that causes a lot of performance issues, especially if there are sumindexfields involved.

When you program navision to use a certain key, SQL Server interprets that as that it needs to order the dataset in the order of the fields in the key. This is separate from retrieving the data. SQL Server will analyze the filters and decide how to retrieve the data most efficiently.

lzr

Ok, think I get it now. I will try to have a short key, which preferably does not contain any boolean and option since this will not really impact on how fast the data is retrieved.

Is there a way to schedule the Navision optimization too?

bbrown

As stated above, SQL will determine the best way to retrieve the data and then sort it (if needed) to the order requested. This sorting is not free, it consumes cpu cycles and time. The impact can be sizable when dealing with large datasets. If you can structure the queries to limit the amount of sorting you will improve performance. You will probably never eliminate all sorting required.

SQL chooses the index for data retrieval based on selectivity.

Selectivity is the percentage of rows returned by a query using a given index. The higher percentage means a lower selectivity. A query is considered to have high selectivity if it returns less than 5% of the rows in the table. The query optimizer will most likely use this index. When selectivity is between 5% and 10% the query optimizer may or may not use the index. When the selectivity is above 10% the query will rarely use the index. If there is not a better index available a table scan will be done.

DenSter

Which means that when you create an index with very low selectivity, SQL Server has to maintain that index with every insert into the table, but it will never use the index for data retrieval. You end up with paying twice for the same index, which is absolutely useless.

In this case you should maintain SQL indexes for all high selectivity keys, and for your keys in Navision with low selectivity you uncheck the MaintainSQLIndex checkbox. That way your Navision code will still recognize the key, and SQL Server will return the recordset ordered in the right order.

This is not exact science, you will probably try it a few times before you get it right.

lzr

I think I get it now. Very nice comments. Thanks a lot you guys

Have a nice weekend!

bbrown

I find the Navision_SQL_Resource_Kit very useful in sorting out index issues with SQL.

lzr

Sounds great. Would be really handy with some more info on this topic. All I got is the installation & configuration manual which only breefly describes this.

Could you send it to me please? I'll pm you my email.

bbrown

The resource kit is available on the Partner website.

lzr

You have a link by any chance?

kriki

Microsoft Navision SQL Resource Kit
But you have to log in.

lzr

Thanks! I find the microsoft partner page pretty hard to navigate through

kriki

lzr wrote:

Thanks! I find the microsoft partner page pretty hard to navigate through

That is the reason I used the internal search-function

dsatria

kriki wrote:

Microsoft Navision SQL Resource Kit
But you have to log in.

The company I work for is using Navision but currently have no maintenance contract with any NSC.
How do we get the resource kit?

dsatria

kriki wrote:

You can create a DB maintenance plan to do that. If you do this, SQL does it on a regular basis

Can't we just activate the "Auto update statistics" option on database properties on EM ?

lzr

I guess so, but then SQL server can start optimizing at a bad time.

kriki

dsatria wrote:

kriki wrote:

Microsoft Navision SQL Resource Kit
But you have to log in.

The company I work for is using Navision but currently have no maintenance contract with any NSC.
How do we get the resource kit?

I'll ask Luc to put it in the download section. But I don't know if he is allowed to do that. Anyway:keep an eye on the download section.

DenSter

dsatria wrote:

Can't we just activate the "Auto update statistics" option on database properties on EM ?

Anything 'auto' will take some performance out of your system. If you have a very sophisticated server for few users, this will probably not make much of an impact. But autoupdate statistics, autoshrink, autogrow, all use system resources, and take away performance from your Navision database. I'm not saying not to use it (although that is recommended), but those are the first things you should consider disabling when you start getting performance problems. Besides, it's really easy to put the update statistics into a maintenance schedule (read books online for more information).

kriki

dsatria wrote:

kriki wrote:

Microsoft Navision SQL Resource Kit
But you have to log in.

The company I work for is using Navision but currently have no maintenance contract with any NSC.
How do we get the resource kit?

You can find the resource kit here.

dsatria

DenSter wrote:

Anything 'auto' will take some performance out of your system. If you have a very sophisticated server for few users, this will probably not make much of an impact. But autoupdate statistics, autoshrink, autogrow, all use system resources, and take away performance from your Navision database. I'm not saying not to use it (although that is recommended), but those are the first things you should consider disabling when you start getting performance problems. Besides, it's really easy to put the update statistics into a maintenance schedule (read books online for more information).

DenSter, thank you for drawing those aspects to my attention

dsatria

kriki wrote:

You can find the resource kit here.

Thank you for your direction...i'm downloading it now...