quote:
Without doubt the SQL implementation of Navision will perfrom less quickly than native
That’s definitely wrong! I admit, it will perform slower if you just changed the platform, ignoring the specific requirements of a SQL Server. IF the NAVISION Application (Keys/SIFTS/Filters/Requests) is modified in way that fits for SQL Server and if the Server (RAM/CPU/Disks/Net) & Database (Indexes/Statistics) is configured properly THEN a NAVISION on SQL Server will “fly”!
– removed by author –
Just to clarify my point… I would recommend finding the number and nature of problems before attempting to fix them. There can be as much impact on SQL performance from inefficent code in the applcation as from inefficent configuration. There is no point in tuning for motorway driving if you only drive in a big city. As my friend in Glasgow said “The most dangerous nut in a car is the one behind the wheel.”
Some comments.
quote:
navision seems to just love optimizer hints such as “option fast” in conjunction with “order by”. this pretty often forces sql server to choose the worst query plan
The OPTION FAST is giving the correct intention where it is used; that is to favour response time over throughput since in many cases AL is not requiring throughput but only the first record. I dont see the relationship to the ORDSER BY. The ORDER BY will not produce a sort because the order will always match a Navision key (unless it has been incorrectly disabled by a developer). The only plans to choose for these simple queries are a Clustered Index Seek, Clustered Index Scan or Index Seek with Bookmark Lookup. The OPTION FAST and ORDER BY will not influence this. The only plans where there is really a choise to make is in the Sift queries that filter on dates, because there are UNIONs involved in those.
quote:
by default, all navision base table non-clustered indexes are unique as the (missing) primary key fields are added. this does not make sense, because this only generates overhead for sql server. the clustered index fields are already included in any non-clustered index, because those form the clustering key of the table
This is a valid concern but it does not generate overhead for SQL because the duplicated values are optimized out of the index, according to the SQL team.
quote:
manual index tuning should be documented as navision changes can destroy the tuning work. sqlsunrise keeps all manual sql server indexes in mind, so that they can be identified and reapplied easily.
From 4.0 SP1 onwards the Clustering has become a Navision property and will be maintained. The FillFactor is still not, and would have to be managed externally if it is believed to be a benefit changing it, after a Navision application has been tuned within Navision.
quote:
There can be as much impact on SQL performance from inefficent code in the applcation as from inefficent configuration.
I totally agree with you! But I consider these Index improvements not just as “tuning”, in my opinion that’s inflating the wheels before driving … [:D]
quote:
This is a valid concern but it does not generate overhead for SQL because the duplicated values are optimized out of the index, according to the SQL team.
Sorry, but that also definitely wrong! All “secondary” NAVISION keys are translated into UNIQUE non-clustered indexes. Example: Table 18 Customer PK: No. SK: Search Name C/SIDE sets a UNIQUE flag to the NCI “Search Name”, this would mean, that all index values have to be unique, e.g. the Search Name “Smith” can exist just once. As this would f*** up the business logic, C/SIDE adds the PK fields to this NCI: Search Name, No. This cause two sever problems: First: With the UNIQUE flag SQL Server is forced to check whenever an index value changes, if this value is unique. I want to point out how insane this is; as the PK fields are added, these NCI are always unique - they can not be non-unique - so SQL Server doesn’t need to check those values, but he’s forced. This validation causes a remarkable administrative overhead for the SQL Server which slows it down drastically, depending on how many index values are changed! Second: With adding the PK fields the size of the NCI is expanded. This is wasting disk - and worse - precious cache space! Example: Table 32 Item Ledger Entry PK: Entry No., Integer (2 Byte) T32 has about 10 active secondary keys/NCI, thus, per record useless 10 x 2 Byte = 20 Byte are wasted (the 2 Bytes from “Entry No.” added to the index). It’s not unusual to have 10 Million records here, thus we have 10.000.000 x 20 Bytes = 200.000.000 Bytes = 190MB A SQL Server 2000 Standard Edition allocates max. 2000 MB (2GB) cache, thus in this case about 10% cache would be wasted. Well, I now, that’s a non-realistic “worst case” scenario as you hardly would deal with all 10 Mio. records incl. all indexes, but that’s just an example for one table. Imagine the impact on tables with bigger PK (e.g. Sales Price, 80 Bytes) …
Joerg, I am aware of what Navision does with the secondary keys. SQL Server uses a uniquifier on indexes that refer to the clustered index and removes fields that are duplicated because they are already present in the clustered index. This is an index layout issue, not a behavioural issue - in other words you are not aware of it (but using DBCC commands can verify it). A unique index requires no additional overhead to test for uniquness because this is implicit in the b-tree implementation. However a uniqe index gives SQL server more information about the cardinality of the values in the index than that of a non-unique index and provides for better execution plans by the optimizer. Have you experimented with setting indexes as non-unique and withdrawing the primary key fields? This has been done in benchmarks which have seen no update penalty but have had reduced query performance. Which cache are you refering to? The execution plan cahce, or data page cache, or… ?
Hi Robert! If you say “you are not aware of it” (the added PK fields) you think about it from NAVISION point of view, but nevertheless it is a “problem” (at least an issue). Out experience has shown, that just by re-creating the indexes non-unique and removing the PK fields (and adjusting the Fillfactors), most transactions (doc creation, postings, etc.) perform at least two times faster, basically due to releasing RAM, reducing physical I/O! With changing the clustered index, too we had some transactions up to 50(!!!) times faster - that’s something!
quote:
Which cache are you refering to?
When talking about the cache I mean actually the total RAM SQL Server can allocate (Standard edition), ignoring it’s internal split-up.
Well I’m glad you see an improvement, and dont suspect this is only the FillFactor. Dont know how to explain that, especially with the track back to RAM usage. I was actually referring to any SQL application not being aware of Index layout, not Navision in particular which is just a regular SQL application using ODBC - nothing special. By the way the 4.0 SP1 release contains a new Key property “Alternate SQL Index” which allows you to control the index used on SQL for a particular Navision key. It will create the index exactly as you lay out the fields for this property - if all primary key fields are present in the list (in any order) it will create a UNIQUE index; if not it will create a NON-UNIQUE index. These are maintained by Navision of course.
quote:
The OPTION FAST is giving the correct intention where it is used; that is to favour response time over throughput since in many cases AL is not requiring throughput but only the first record. I dont see the relationship to the ORDSER BY. The ORDER BY will not produce a sort because the order will always match a Navision key (unless it has been incorrectly disabled by a developer). The only plans to choose for these simple queries are a Clustered Index Seek, Clustered Index Scan or Index Seek with Bookmark Lookup. The OPTION FAST and ORDER BY will not influence this. The only plans where there is really a choise to make is in the Sift queries that filter on dates, because there are UNIONs involved in those.
The traced showplans speak a clear language: In most cases, “option fast” and “order by” culminate in a clustered Index Scan, which one could also call a full Table Scan! Without, we could have gotten an Index Seek with a Bookmark Lookup, which is a lot faster, especially on big tables. We don’t want scans accompanied by the corresponding locks on a table with more than 100000 records in an environment, where 60 other users would also like some computing time… not for the sake that this one connection selfishly favours a quicker response over throughput,… and the quicker response is still questionable. God, I have spent so many tuning hours with creating indexes that are bloody working around this pain in the bum… By the way: The engine treats UNION joined queries as two seperate queries which at the end get UNION joined 
quote:
This is a valid concern but it does not generate overhead for SQL because the duplicated values are optimized out of the index, according to the SQL team.
There is a whole bunch of disadvantages in adding the PK fields! Sorting still apllies for the PK fiels added to the index key, and thus decreases the number of effectively usable index fields as there is a limitation of 16… “3 more fields, and we could have gotten a quick covering index (sigh)”. The PK (if it is clustered) must additionally be added as the “clustering key” - which is always a physical part of an index - the pointer to the clustered index (the table), whenever the underlying table is not a heap. I wonder why during tests the query optimizer chose a different and usually more efficient plan after just eliminating the PK fields from a non-clustered index, and why the used database space decreased… the must be a difference
quote:
From 4.0 SP1 onwards the Clustering has become a Navision property and will be maintained. The FillFactor is still not, and would have to be managed externally if it is believed to be a benefit changing it, after a Navision application has been tuned within Navision.
The benefit of proper fillfactors can easily be proven by just looking at the reads column in SQL Profiler. The proper fillfactor of an index depends on the DATA that’s in its key columns and the data that is still to come. The data in the key columns can be measured in form of statistics. Assuming that the data to come will comply to these statistics, this calculation offers a far better approach than just guessing. Why should we want to reduce the information density in SQL Server’s Buffer Cache, or why should we want SQL Server to make avoidable page splits, if we know it better?
quote:
A unique index requires no additional overhead to test for uniquness because this is implicit in the b-tree implementation.
My mum always taught me: Nothing on this planet is for free. If we want SQL Server to check for uniqueness, it has to do it. Uniqueness is handled like a constraint, which means before the transaction itself. Even if the new record is attempted to be placed on the same data page where the possible duplicate would already reside, SQL Server still has to check if it is already there. Without uniqueness, SQL Server can just bloody put it there. In an evironment with over 100000 queries in just 10 minutes we want to save as much computing time as we can. I admit, the non-uniqueness gives us only a tiny bit of performance, but why ignore it, if its no effort at all to eliminate uniqueness during our regular index maintenance? I think by experience we know, that we have no performance to waste when it comes to Navision on SQL Server.
Hi Bodo,
quote:
In most cases, “option fast” and “order by” culminate in a clustered Index Scan, which one could also call a full Table Scan! Without, we could have gotten an Index Seek with a Bookmark Lookup, which is a lot faster,
What has the order by to do with option fast, and why is it only in most cases? Why should option fast cause a clustered index scan (which defeats the purpose of option fast, I would expect the opposite behaviour, to obtain a scan when you do not favour fast response), and omitting it causes the use of a seconday index with bookmark lookup? I think what you are observing is buggy behaviour here in the optimizer. Have you raised this issue with Microsoft if it is causing such a primary need to retune? Seems for sure like option fast should be avoided if it is causing this craziness, agreed, because the plan is not matching the intention if this is the case.
quote:
By the way: The engine treats UNION joined queries as two seperate queries which at the end get UNION joined
Yes, and in this case it is UNION ALL which is straight forward.
quote:
There is a whole bunch of disadvantages in adding the PK fields! Sorting still apllies for the PK fiels added to the index key, and thus decreases the number of effectively usable index fields as there is a limitation of 16… "3 more
This is not discussing what is happening with the values of the duplicated fields in the index, and I guess nothing short of the source code will show this.
quote:
The benefit of proper fillfactors can easily be proven by just looking at the reads column in SQL Profiler. The
Yes, I wouldnt dispute the value of setting a FillFactor with good knowledge of the activity on the table and how an optimal value can be set for each index, other than the default - or a subset of them. The problem here is there are very few - almost nobody - that sits and develops with Navision, who are in a position to do this if it was exposed as a Navision property. Even the ability to choose a clustered index other than the default is beyond most. This is the reason why practically all SQL properties have neen sheilded from AL developers. This does not allow SQL to be used as a more conventional SQL application (which is a problem), where a “SQL Server devloper” is setting up the tables, indexes and writing a bunch of specific SQL queries (not totally dynamic as Navision is doing) that forms the application. But Navision is an ERP system and needs to continue to be accessible by the same devloper base that have been working against the Navision Server platform for 10 years or so. Only more recently, 6 years after the release for SQL, are more SQL-specific features being opened up to control more aspects of the application (with the exception of using SQL itself in AL which is another story). Right now, a tuned SQL application on Navision requires someone with SQL skills (and hopefully also Navision application skills), and those people I guess are making good money doing this. Sorry to write a book, but this issue has history.
quote:
My mum always taught me: Nothing on this planet is for free. If we want SQL Server to check for uniqueness, it has to do it. Uniqueness is handled like a constraint, which
Yes, but I bet she wasn’t developing indexes in C was she? 
This is a slightly fickle point here, and I can’t claim to having seen the code for unique index insert/modification, but I would be surprised at the overhead on SQL; the same operations on Navision Server which always has a clustered primary key have no cost since after determining the location of the index change (e.g. insertion), which is an index read, we already know if the duplicate values are already present there. If so it is a key violation right away. As for uniqueness begin handled like a constraint isnt it the other way round? If you create a UNIQUE CONSTRAINT this is actually implemented as a UNIQUE index in order to obtain the checking for unique values using the natural bevhavior of an index. Here we are talking about what happens when we already have a unique index and no constraint.
quote:
Why should option fast cause a clustered index scan (which defeats the purpose of option fast, I would expect the opposite behaviour, to obtain a scan when you do not favour fast response), and omitting it causes the use of a seconday index with bookmark lookup?
If we quering table with SK aaa,bbb,FIELD by where FIELD=‘XXX’. Optimizer calculate cost of SK index seek->bookmark lookup->Sorting, compares with CI seek (no sorting). First mached rec of CI seek can be used by option fast. So in most cases optimizer prefer CI, if FIELD don’t have high selectivity and option fast is specified. CI seek can give faster response, indeed. Server load is ignored, because we have specified in query, that we don’t care about load - Option Fast. I think that behavior of optimizer is correct.
Well sometimes it is the Clustered index choice that would require sorting and a secondary index no sorting, depending on the order by. I dont really follow your example, Daluis, but you seem to be stating how I would expect the plan to be if a secondary index is chosen, which is fine. But that is not the example given earlier which was an index scan (or table scan when index=clustered). I dont see the server load here - the plan you state is very efficient if the WHERE is satisfied well and there is no SORT. Slight overhead with a bookmark lookup but that is not a load. I would call the table scan a load and waste of resources. I still dont see why OPTION fast should cause that.
quote:
I dont see the server load here - the plan you state is very efficient if the WHERE is satisfied well and there is no SORT. Slight overhead with a bookmark lookup but that is not a load. I would call the table scan a load and waste of resources. I still dont see why OPTION fast should cause that.
Originally posted by robertc - 2006 Feb 10 : 12:22:12
Well, go ahead and see yourself using query analyzer. If there is an “order by” followed by “option fast”, SQL Server attempts to get around costly sorting within the plan. If it finds a suitable index in the order of the “order by” clause, the optimizer uses this index - no matter if it’s being scanned or seeked. As you said earlier: response time over throughput. Got it now?
Well, no, I haven’t got it because there is a contradiction in the explanations here. I also dont see the behaviour you see. If the index matches the order by only but is bad for selection because it will cause a scan, it will not be a fast response. Whereas an index seek and bm lookup will be. I see this over and over, with or without the OPTION FAST actually, although the clustered index is usually favoured, even with a sort, because of the way Navision is usually filtering. Perhaps it depends on the WHERE clause which can have a big impact on the use of the index (of course). SQL Server has a problem whereby it is unable to skip rows of an index that will no longer match the search criteria, in cases where field values in the index are ommitted in the WHERE clause - it can cause scanning of (almost unlimited) wasted rows and run-away queries (the plan looks fine, it is the index access methods that are not parameterized sufficiently to handle this and this is buried in the optimizer). It is a known problem and is slated to be solved in the next release. It is particularly common in Navision where some feature areas are unused and therefore fields are not included in filters. On Navision Server it is not a problem because it is able to skip these index nodes and is very efficient. SQL can gives seconds/minutes queries and Navision Server is millseconds. One problem with Navision is that it is always specifying an ORDER BY in response to SETCURRENTKEY to ensure the ordering is matching the chosen key. This puts a constraint on the plan of course and if ommitted would allow slightly more freedom in index selection. In most cases in Navision it can be ommitted because the app code does not (and should not) rely on the order of return records (for text data you can never know what that order is). In future this is likely to change to ommit the ORDER BY.
quote:
Well, no, I haven’t got it because there is a contradiction in the explanations here. I also dont see the behaviour you see. If the index matches the order by only but is bad for selection because it will cause a scan, it will not be a fast response. Whereas an index seek and bm lookup will be. I see this over and over, with or without the OPTION FAST actually, although the clustered index is usually favoured, even with a sort, because of the way Navision is usually filtering.
Originally posted by robertc - 2006 Feb 11 : 12:12:35
Yeap, Robert you are right, my example wasn’t good [B)] But check it on na demo db: select [No_], [Description] From [na$Item] Where [Inventory Posting Group]=‘AAA’ Order by [No_] OPTION(FAST 1) with option fast index seek + bm lookup is used, without CI Scan. Logical reads 175 versus 24. Option fast give x7 more reads, but the posibbility to find first mach faster is higher than in CI scan. another example: select [No_], [Description] From [na$Item] Where [Inventory Posting Group]=‘AAA’ Order by [Search Description] OPTION(FAST 1) index scan + bm lookup is used / CI scan. Reads 289/24. As you see, option fast realy can cause load. I don’t know where is benefit of it, in form which shows 20 recs in 10 ms and first rec will appear at 1 ms, but all form will be frozen for 10 ms. If no option fast is used, all rec appear in 5 ms ! Yes, I agree, ORDER BY is issue. I think that would be great to have “virtual” navision tables, which can be generated by SQL query. For example: VirtTable1:= CreateVirtualTable(“select …”);[Yeah!]
I tired similar queries as sent by Navision, and then combined FAST and ORDER BY in different ways: SELECT *,DATALENGTH("Picture") FROM "EUROPE11"."dbo"."CRONUS International Ltd_$Item" WITH (READUNCOMMITTED) WHERE (("Inventory Posting Group"='RESALE')) ORDER BY "No_" OPTION (FAST 5) Reads: 176, Duration: 359 Plan: CI Scan SELECT *,DATALENGTH("Picture") FROM "EUROPE11"."dbo"."CRONUS International Ltd_$Item" WITH (READUNCOMMITTED) WHERE (("Inventory Posting Group"='RESALE')) ORDER BY "No_" Reads: 176, Duration: 350 Plan: CI Scan etc. SELECT *,DATALENGTH("Picture") FROM "EUROPE11"."dbo"."CRONUS International Ltd_$Item" WITH (READUNCOMMITTED) WHERE (("Inventory Posting Group"='RESALE')) ORDER BY "Search Description","No_" OPTION (FAST 5) Reads: 480, Duration: 304 Plan: Filter ← Nested Loops ← Index Scan, CI Scan SELECT *,DATALENGTH("Picture") FROM "EUROPE11"."dbo"."CRONUS International Ltd_$Item" WITH (READUNCOMMITTED) WHERE (("Inventory Posting Group"='RESALE')) ORDER BY "Search Description","No_" Reads: 182, Duration: 294 Plan: Sort ← CI Scan SELECT *,DATALENGTH("Picture") FROM "EUROPE11"."dbo"."CRONUS International Ltd_$Item" WITH (READUNCOMMITTED) WHERE (("Inventory Posting Group"='RESALE')) Reads: 176, Duration: 360 Plan: CI Scan I can see the same results as you do, although not with a BM lookup but merge of seconday index and CI. The CI is usually favoured and a sort performed if the ORDER BY does not match. The best results are to avoid the ORDER BY.