Is Navision to stupid or is SQL to smart

Marije_Brummel

Currenly I am again fighting with SQL2005 at a customer.

Many, many reads on tables whilst there are perfect and selective indexes.

Now I've never experienced this problem on SQL2000. SQL always uses the right index for the query.

I also know Navision has not changed the communication with SQL.

What has happend to SQL2005?? What have they changed that causes this major bug!

There are plenty of workarounds like index hinting and adding the recomile option, but surely this is not a solution. It takes like forever to find the bad queries and find a way to solve them. You do not want to create a indexhint for every query, that would mean that you have Native behaviour again.

The problem seems to be (IMHO) in the clustered index. SQL2005 is much more keen on that than SQL2000. Every SQL expert loves to spend hours and hours to think about the proper clustered index for a table.

If you show the indexplan of Navision to a SQL expert they will laugh. Every entry table is custered by the entry no whilst in the SQL world it is normal to cluster on the most common selective filtered field.

So what is this field in Navision. Let's have an example.

Take the customer ledger entries for example. They could be clustered by Customer No. and Posting Date. But what about filtering on Document No.? Or External Document No.? What about Transaction No. or the Open boolean.

Maybe we should redesign this table for SQL. If Customer No. is the proper clustered index and Document No. is used by Navigate, then maybe we need Navigate Entries? This can be a small join table which is very common in relational databases.

Then we have the Open field. What a nightmare. What if we add a new table to Navision. Just for open entries? This is a small and compact table with fewer entries than your historical entry table.

Last but not least we have the apply to field. This can be very easily solved using a temporary table in a singleinstance codeunit. When a entry is selected you just copy that entry to the codeunit and you read this when applying the entries.

Will redesigning NAV solve the SQL2005 issues? Maybe, maybe not. Fact is that every day maybe hundreds of installations suffer from this issue and it starts to become a real problem for partners since "solving" costs a lot of time and is often not billable.

Any thoughts or comments are more than welcome.

ara3n

I'm seeing similar problems with Sql 2K5 with several customers.

I'm waiting for Navision to solve it.

Marije_Brummel

How do you report your cases?

I had an Microsoft engineer on the phone the other day and he wanted to reproduce it in a CRONUS database. Yeah if that will ever happen.

I report all my cases but also implement all workarounds and after that at least the customer has a more workable solution. Waiting is not an option for some customers.

BTW, my impression is that SQL2005 64 bit is better, but I do not have that many installations of it. :?

Waldo

You know that I follow you, because there are some problems that we encountered at te same customer .

Now, we have a PABS agreement with Microsoft. Thanks to this, we can add support requests, and these support requests get an escalation engineer and it gets followed up.
But I must say, I did this once for an SQL issue, and I had to give the solution myself (which I also posted on this forum somewhere). I was really disappointed in the SQL knowledge of NAV people. May be I didn't get the right support engineer, but anyway ... .

Yesterday, I read a nice "definition" of a clustered key: it's how the data is physically stored on disk (I read this on one of NavisionGirls' blogs, but I can't find the URL anymore ). Keeping this in mind, it's quite logical to pick the most common key. But then I ask myzelf why they did not changed it in 5.0, because a lot of work on index tuning has been put in there... :-k .

I also heared that there is a "caching bug" in SQL2005 ... does anyone know this is already solved? With all the updates, hot fixes and service packs ... I don't know anymore.

ara3n

We had a client recently that moved from 2k to 2k5. They had 70 companies.

They could never restore a the fbk. So they started restoring single companies.

after 30 companies the sql 2k5 would stop responding during restore.

We had the MS technical engineer on the phone and let the customer tell her pain.

After several hotfixes (this is 4.0 sp3), buying new server 64 bit server with 64 bin sql, were they able to finish the restore.

They were running for several weeks half the company on one server and half on the other.

ara3n

Another client that recently also moved from sql 2k to 2k5. (32 bit)
They have horrible performance. They have 30 gig db. and they can search on regular fields. They have requested our help, but I haven't had the time to go onsite.
Too busy.


Another client has been live on for 6 month on 64 bin 2k5. (They've grown to 30 gig)
And now they are starting to see the same performance. List of ledgers take a while to load. causing the navision to not respond.

With 6 hotfixes for Nav sp3, I'm sure Nav team is working on these performance issues with sql team.

what I've seen is that sql 2k5 is sometimes unpredictable. And yes the 64 bit version is what large db customers should be on.

Waldo

I posted this once on my blog: http://dynamicsuser.net/blogs/waldo/archive/2007/06/28/sql-server-x64-vs-x86.aspx

It explains why 64 bit is much more interesting then 32 bit.. . This explanation comes from Microsoft Dynamics NAV development team ... so I guess they also really want to push this :-k .

Our new customers always go straight to 64bit.

ara3n

Waldo wrote:

Yesterday, I read a nice "definition" of a clustered key: it's how the data is physically stored on disk (I read this on one of NavisionGirls' blogs, but I can't find the URL anymore ). Keeping this in mind, it's quite logical to pick the most common key. But then I ask myzelf why they did not changed it in 5.0, because a lot of work on index tuning has been put in there... :-k .

Clustered Index Design Guidelines

Every table should have a clustered index defined on the column, or columns, that offer the following:
# Can be used for frequently used queries.
# Provide a high degree of uniqueness.
# Can be used in range queries.

Generally, you should define the clustered index key with as few columns as possible. With following attributes:
* Are unique or contain many distinct values
* Are accessed sequentially
* Used frequently to sort the data retrieved from a table.
* Columns that DO NOT undergo frequent changes
* DO NOT use Wide keys


Link

http://msdn2.microsoft.com/en-us/library/ms190639.aspx

garak

interest. post.
I know this problems, because i've the same since cange from 2000 -> 2005
I've called with some M$ Engeneers, but noe one can give me an ultimate solution :-(

And now? What we must do :?:
I've in future to support more than 300DBs and i will not have the same problem an every DB

thaug

My experience with 2005 has been great. Of course the server I'm using is much better than my previous server that our db was running on, but everything flies. "Solved" all of my locking issues during posting, and certain other tasks that used to take forever now get completed almost immediately. However, this is all new server equipment, but only 32bit. I haven't seen the performance problems that others speak of.

However, I do remember seeing a post on here where the problems seemed to be isolated to MDAC. The MDAC version that runs on Windows 2003 works good, but the version on XP needs to be updated. Of course, MS still needs to provide the update, so who knows when that will be.

I also saw the KB article that details some back-end SQL stuff you can do to hint the right index, but that seems way to complicated and prone to problems.

Bottom line, MS needs to provide a fix, and it is NOT SQL that is the problem. My opinion at least.

Waldo

Can you provide more details about your system?

DenSter

ara3n wrote:

Another client has been live on for 6 month on 64 bin 2k5. (They've grown to 30 gig)
And now they are starting to see the same performance. List of ledgers take a while to load. causing the navision to not respond.

That's because they probably don't have any maintenance on the SQL Server, the indexes are not tuned and severely fragmented, and nobody has paid any real attention to performance before implementing the database. Have they considered the SQL Perform tools? Let me know if you need help with that

Marije_Brummel

thaug wrote:

My experience with 2005 has been great.

Yes, I also have some very happy customers with good performance. That's the funny part. It is not all customers that have issues. :?

davmac1

I have one customer on 64 bit that had major network slowdown issues. Turns out that their network card drivers were incompatible with Windows 2003 x64 SP2 and they had to roll back to SP1.

We have also found out that FRX does not support the 64 bit SQL Server 2005 for their Navision bridge product - they said most people were not using SQL Server 2005 64 bit edition. After the info at Convergence, I cannot see why any Navision SQL 2005 customer would not use the 64 bit edition.

colin_leversuch-roberts

The optimiser was changed in sql2005 which is why there are these problems - the default use of the clustered index is because of lots of reasons, many of which are caused by the navision indexes themselves. There's not enough space to explain this, sorry. But basically because navision adds the columns of the primary key to all the secondary indexes, in most cases the secondary indexes will be ignored by the optimiser as the cost to use them is too high - as an example I have a table which is 10Gb in size, there is a secondary index defined on a date column, because the PK is quite wide the secondary index is actually just under 8Gb, it makes no point ot use a 8gb index as a bookmark to a 10gb table, so the optimiser will go straight for the table.
As for clustered index choice - this is not so simple, an ascending key will offset table fragmentation, and yes clustered indexes arrange the data physically on disk - it's always been this way, right from Sybase days. A covered index will frequently outperform a clustered index and it assumes you are not executing select * ; select * negates many benefits of indexing
In the mean time - for nav 4.0 you need to go to sp3.5 to remove the FAST statements in your sql - these don't work well at all. Then you need to turn auto create and auto update stats back on, then you need to make your indexes sql indexes not nav indexes and get rid of the bloated secondary indexes. You can use plan guides to force recompiles of some queries that don't hold their plans well - although I do agree I don't think this is a good solution.
Finally, the problem is mainly the application - not the sql server.

Marije_Brummel

About the indexes.

We were told by SQL people from redmond that SQL ads the PK fields to every secondary index. This is unavoidable because of mapping from the index to the clustered index.

There is indeed a big issue especialy with customisations by partners that engineers often create a wide clustered index. This is not only wrong on SQL but also on Native.

I've seen good results of changing the pk to a integer which is autoincrement.

What is really nasty is that when you change the clustered index and not specify a SETCURRENTKEY navision will automaticaly add the NAV pk as ORDER BY instead of the clustered index.

You can create Covering indexes for NAV if you use ISEMPTY or for reports. Example: If you have a combined invoicing report which filters on Outstanding Quantity you can create a covering index and make the repory fly.

You can also create covering indexes on SIFT tables. This way you can only have one level activated and have a index on the SIFT table for another filter. Off course this in on SQL level and not on Navision. You need to know what you do.

DenSter

colin leversuch-roberts wrote:

Finally, the problem is mainly the application - not the sql server.

I'm glad you say that

FYI, you can lose the PK fields from the SQLIndex by specifying the SQLIndex in the NAV table design, in the SQLIndex property of the NAV key. If not all of the PK fields are in the SQLIndex, it will create a non unique secondary index on SQL Server, without the PK fields.

colin_leversuch-roberts

yes you sort of get a double whammy with the clustered index ( usually the PK ) prior to nav 4 I believe you couldn't support sql indexes so didn't really have much option. I'm working with our developers to move indexes to SQL indexes, however this is a slow and careful process and I've been collecting index usuage data for nearly three months now to assist me in this process. As far as secondary indexes are concerned, yes internally sql server adds a subset of the clustered index to the secondary index to route it back to the leaf data. Navision indexes I have currently ( from 3.7 I guess ) also have the physical columns of the primary key added to the index, in effect creating a wide secondary index which also has the subset added again - hence why my single datetime column index is actually 8gb in size on a table which is only 10gb in total.
Yes ascending numeric keys for the clustered index tend to make for very efficient inserts - effectively a clustered heap. I will be looking at the secondary indexes first, I suspect we will look at clustered indexes in conjunction with table partitioning. I estimate changing from navision indexes to sql indexes will reduce my database size by about 100gb.

colin_leversuch-roberts

oh sorry - there have been lots of posts stating that nav 4 at least was never really written with sql backend in mind and if I remeber correctly i was told I needed to employ a navision consultant to tell me I basically needed to re-index with a sql backend - so in effect with sql server yes the application is to blame for the poor performance - notwithstanding there's very little set based operations, an application that relies on client side cursors will never work well with sql server - as sql server just isn't designed/built for that sort of application.

thaug

Waldo wrote:

Can you provide more details about your system?

HP DL380, 8 GB RAM, two dual core procs, about 14 disks total, RAID 1+0 where applicable. All disks are DAS. Heavy transaction disks are all 15K RPM. Windows 2003 with SQL 2005, Nav 4.0 SP3. 120GB database, but much of that hold Matriks blob data, so probably about 30GB of "real" data.

And to follow up with what I and others have said about Nav being the culprit, colin also alludes to this. It is also evident in the changes throughout the years from 2.6 that have improved SQL performance with each client update (new features notwithstanding).

ara3n

DenSter wrote:

ara3n wrote:

Another client has been live on for 6 month on 64 bin 2k5. (They've grown to 30 gig)
And now they are starting to see the same performance. List of ledgers take a while to load. causing the navision to not respond.

That's because they probably don't have any maintenance on the SQL Server, the indexes are not tuned and severely fragmented, and nobody has paid any real attention to performance before implementing the database. Have they considered the SQL Perform tools? Let me know if you need help with that

They are running nightly and weekly maintenance. I've tuned the indexes on most ledger and order tables.

I just went and check the history on the jobs and they've been running everyday.

I went to the item card. Everything opens fast. looked up on qoh and it displayed fast.
Click on show all and waited 1 min for navision to referesh.
They have 2 mil records now in the item ledger.

I guess I can change the SourceTablePlacement to last if that's going to make a difference.

DenSter

colin leversuch-roberts wrote:

oh sorry - there have been lots of posts stating that nav 4 at least was never really written with sql backend in mind and if I remeber correctly i was told I needed to employ a navision consultant to tell me I basically needed to re-index with a sql backend - so in effect with sql server yes the application is to blame for the poor performance - notwithstanding there's very little set based operations, an application that relies on client side cursors will never work well with sql server - as sql server just isn't designed/built for that sort of application.

And saying all that helps how? :-k

Nobody is disputing the fact that NAV is severely inefficient on SQL Server

Wim_Mulder

Does anybody heard about the $ndo$dbconfig table to define the tables to get the hint option "RECOMPLE"?
This have helped us a lot at our custumoprs!

Marije_Brummel

ara3n wrote:

I went to the item card. Everything opens fast. looked up on qoh and it displayed fast.
Click on show all and waited 1 min for navision to referesh.
They have 2 mil records now in the item ledger.

Please, never do that and don;t let your customer do that! It is bad design in Navision and has nothing to do with SQL Server.

Marije_Brummel

Wim Mulder wrote:

Does anybody heard about the $ndo$dbconfig table to define the tables to get the hint option "RECOMPLE"?
This have helped us a lot at our custumoprs!

They should. It is there for a while now.

Marije_Brummel

colin leversuch-roberts wrote:

As far as secondary indexes are concerned, yes internally sql server adds a subset of the clustered index to the secondary index to route it back to the leaf data.

How can I check this?

If I have a table in NAV which is clustered Document Type, Document No. Line No. and add an index Document No., Customer No. and look in SQL the index is Document No., Customer No, Document Type, Line No. so SQL seems to be able to find out which fields of the clustered index are already in the secondary index and do not add's it again.

Marije_Brummel

colin leversuch-roberts wrote:

Yes ascending numeric keys for the clustered index tend to make for very efficient inserts - effectively a clustered heap.

It depends offcourse on your datastructure. This is the fastest way to have a unique index without locking.

After that you need to start about how to retrieve the data fast.

ara3n

Mark Brummel wrote:

ara3n wrote:

I went to the item card. Everything opens fast. looked up on qoh and it displayed fast.
Click on show all and waited 1 min for navision to referesh.
They have 2 mil records now in the item ledger.

Please, never do that and don;t let your customer do that! It is bad design in Navision and has nothing to do with SQL Server.

Show all is a bad design?
Are you saying that nobody should do a showall on any ledger tables?
So if they want to research something, they should do what?


Also the title should be too smart or too stupid.

David_Singleton

Wim Mulder wrote:

Does anybody heard about the $ndo$dbconfig table to define the tables to get the hint option "RECOMPLE"?
This have helped us a lot at our custumoprs!

Waldo recently posted a very interesting Blog about this here :

Making NAV use a certain index on SQL Server - Waldo's Blog Microsoft Dynamics NAV

Marije_Brummel

My fast typing english is very bad. Sorry.

The Item Ledger Entry table is clustered by Entry No. Like colin says, that is something like a clustered heap. There is no real structure. You can have Item entries from different items all acros the disk.

If you release the filter (show all) SQL will try to retrieve all records for you ordered by Item No. and posting date, because that is what the form is designed to do.

SQL2005 will often fail on that onfortunately.

That's all I know. I am not a SQL mvp.

Marije_Brummel

David Singleton wrote:

Waldo recently posted a very interesting Blog about this here :

Making NAV use a certain index on SQL Server - Waldo's Blog Microsoft Dynamics NAV

That is not the Recompile Wim is refering to. Since some Release or Hotfix (I don't exactly know what) you can also add recompile in a ndo table just like the Index Hint.