NAV5 SP1 SQLindex property - all entries removed from NA

davmac1

When I first downloaded NAV 5 SP1, there were almost 220 SQLindex entries (where NAV had changed the SQL index from the NAV fields).
I complained to an ISV that there release had removed the SQLindex entries. They explained to me that Navision had removed them. I downloaded the release again for North America and all the SQLindex entries are gone.

Does anyone know the reason why? I thought the whole idea of SQL Server tuning was to provide better keys for SQL Server to use. This seems like a big step backwars to me.
I know key optimization varies by individual site, but starting off with a base set of changes seemed like a better way to start than starting with nothing.

kine

Yes, here were some changes which leaded to removing that... hard to say if it is good or bad... :-k

davmac1

Do you know why they removed them? Were there specific problems they were addressing when the SQLindex property was used to create different SQL keys?

kine

I think that they have find out that the optimalization is too specific and that the performance in some situation went down... I cannot remember the real reason... :-k

Marije_Brummel

Microsofts statement is really simple: The solotion should work good enough for all scenario's. Not optimised for one and bad for another.

The indexes in 5.0 were perfect fore some (most) scenario's but turned out to be terrible in some (few) scenario's.

That is why they were turned off.

I am pretty sure more improvements will come in version 7 but we are not allowed to communicate about that. (In fact I am affraid this is already over the edge)

davmac1

I know they will have improvements in each version - no secret there. The specifics are still a secret. The big issue is a SQL Server only release.

Can you shed some light on which scenarios are bad for the SQLindex changes, since I plan to turn them all back on for a high volume customer, except for those that cause problems.
So if you know what scenarios caused problems and can let me know, I would appreciate it.

ara3n

here is the blog on why they removed it.
http://blogs.msdn.com/microsoft_dynamic ... perty.aspx

stryk

... and please regard the comments, there I briefly describe how you could use the "SQL Index" property (almost) "fail-safe", giving you still lots of improvements!

davmac1

Is the problem really using hints in conjunction with SQLindex - have you tried SQLindex without hints on SQL Server 2005?
It appears we need different tuning tips for each SQL Server release.

With the base functionality changes getting larger between Standard and Enterprise, this is just going to get worse.

Marije_Brummel

IMHO it is just not possible anymore for small or medium sized NAV partners to understand everything.

For Sharepoint, SQL, hardware, exchange etc you need to work together with experts.

A lot of partners work together with hardware specialists. That is really common. This trend will also move to other fields.

garak

in this case, i agree with Mark

David_Singleton

Mark Brummel wrote:

IMHO it is just not possible anymore for small or medium sized NAV partners to understand everything.

For Sharepoint, SQL, hardware, exchange etc you need to work together with experts.

A lot of partners work together with hardware specialists. That is really common. This trend will also move to other fields.

=D> Well said.

davmac1

I know SQL Server and am certified in the last 3 releases.
What I am frustrated by is Navision's constantly changing methods and the lack of beta testing with real customer databases.
If expertise is so important, then why does Navision as part of Microsoft have so many problems?
Our battle is to work with whatever scheme Navision has come up with on each service pack and sometimes patch level.
There needs to be better information available on why they are making changes and where they are seeing performance difficulties.
There should be some benefits to running a package compared to custom software.

davmac1

Another thing I would like to point out is that NAV is supporting 3 databases - native, SQL Server 2000, and SQL Server 2005.
Soon they will be adding another - SQL Server 2008.
All the SQL Servers offer 32 bit and 64 bit editions. All the versions have differences in query behavior. A performance problem in 2000 may not be in 2005, but it introduced a different set of behaviors and problems.
NAV has multi language support. Maybe NAV needs to add packs to be loaded for SQL Server edition support.

DenSter

davmac1 wrote:

Maybe NAV needs to add packs to be loaded for SQL Server edition support.

Now THAT makes sense to me. I am at a point where I just don't know anymore which version works well with what edition, with what hotfix, critical update, patch or service packs. It all just doesn't make sense anymore. We should really have a better overview what NAV version goes with what version/edition of SQL Server.

David_Singleton

DenSter wrote:

davmac1 wrote:

Maybe NAV needs to add packs to be loaded for SQL Server edition support.

Now THAT makes sense to me. I am at a point where I just don't know anymore which version works well with what edition, with what hotfix, critical update, patch or service packs. It all just doesn't make sense anymore. We should really have a better overview what NAV version goes with what version/edition of SQL Server.

Navision used to display date and time in the top right hand corner. When they released the UNIX version, they had to deactivate this, since it mean a lot of unnecessary network traffic going to the server and back to get the time, so after some time (I think 15 seconds), it stopped updating the time until you actually started working again.

Well they then did the same in the DOS version. When asked why they said They wanted the UNIX and DOS versions to look identical.

its the same now with this "one code base" issue. They do FAR FAR more work maintaining one code base that supports multiple systems than they ever would if they just supported at least a C/SIDE and a SQL code base separately.

its just the Navision way that is bean for 20 years. ](*,)

kriki

DenSter wrote:

davmac1 wrote:

Maybe NAV needs to add packs to be loaded for SQL Server edition support.

Now THAT makes sense to me. I am at a point where I just don't know anymore which version works well with what edition, with what hotfix, critical update, patch or service packs. It all just doesn't make sense anymore. We should really have a better overview what NAV version goes with what version/edition of SQL Server.

Maybe something for the blog of Waldo. :whistle:

Waldo

kriki wrote:

DenSter wrote:

davmac1 wrote:

Maybe NAV needs to add packs to be loaded for SQL Server edition support.

Now THAT makes sense to me. I am at a point where I just don't know anymore which version works well with what edition, with what hotfix, critical update, patch or service packs. It all just doesn't make sense anymore. We should really have a better overview what NAV version goes with what version/edition of SQL Server.

Maybe something for the blog of Waldo. :whistle:

:shock:
hm, good idea, but difficult, as this is not really "official material", is it?

Anyway, I follow Mark in his reasoning. He is a good example of specialisation by the way (SQL Server performance).