Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 305 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Is Navision to stupid or is SQL to smart
Marije_Brummel
Member, Moderators Design Patterns Posts: 4,262
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.
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.
0
Comments
I'm waiting for Navision to solve it.
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
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. :?
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
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.
Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
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.
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
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.
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
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.
Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
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
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
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
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.
Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
RIS Plus, LLC
Yes, I also have some very happy customers with good performance. That's the funny part. It is not all customers that have issues. :?
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.
http://mibuso.com/blogs/davidmachanick/
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.
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.
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.
RIS Plus, LLC
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.
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).
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.
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
Nobody is disputing the fact that NAV is severely inefficient on SQL Server
RIS Plus, LLC
This have helped us a lot at our custumoprs!
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.
They should. It is there for a while now.
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.
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.
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.
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
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
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.
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.