Plataan

Keys/Indexes & Performance: MaintainSqlIndex property

When you are runnning Dynamics-NAV on Sql Server, there are a few things to keep in mind, so that your ERP application stays as performant as possible.

When you are running Dynamics-NAV on Sql Server, the design of the objects (like tables) will still remain in Dynamics-NAV. But every table in Dynamics-NAV will be translated into a similar table, hosted in the Sql Server database.

A Key in a table in Dynamics-NAV will be translated into an Index on the corresponding table in Sql Server. (The primary Key will become a unique clustered index and the secondary keys will become unique non clustered indexes on Sql Server.)

Why do we needs Keys in a database ?

Well, keys in a database are like indexes in an encyclopedia. They speed up the retrieval of data. So by creating lots of keys on a table, data (rows) of that table will be able to be retrieved faster.

So, why not create as much keys as possible on a table ?

Well, because keys also have disadvantages. Every time an insert/update/modify occurs on the table, alls of the keys of that table will need to be updated. Keys are kept in what is called a B-tree. Summarized, they contain nodes (pointers) to be able to retrieve date more quickly and avoid having to do a full table scan.

So, the more keys you have on a table, the more overhead you get every time you do an insert/modify/delete on a table.

Why do we usually needs keys in Dynamics-NAV ?

• 1) for Searching (quickly retrieving the resultset of a query)
• 2) for Sorting: if you want to sort a table, then Dynamics-NAV needs a key to do this
• 3) for SIFT (SumIndexFieldsTechnology)

Why do we usually needs indexes in Sql Server ?

• 1) for Searching  (quickly retrieving the resultset of a query)

Sql Server does not need an index on a table to be able to sort his resultset !

That means that all of the keys in Dynamics-NAV that are only there for sorting purposes, are obselete in Sql Server, and so are only causing overhead and performance decrease on inserts/updates/deletes !

As a solution for this, in Dynamics-NAV when you have a look at the properties of a key, there is a boolean property called: MaintainSqlIndex. (Yes or No)

By default it is put to Yes, this means that the key will be translated as an index on Sql Server. By changing this property to No, the index on Sql Server for this key in Dynamics-NAV will no longer be created & maintained.

So if you put the MaintainSqlIndex property to No for all of the keys that are only ment for sorting purposes, you will get a performance increase of your Dynamics-NAV application, when it is running on Sql Server !

There are also other interesting properties regarding performance, I'll be writing about them in another blog article very soon, so stay tuned...

Microsoft World Partner Conference | Office PerformancePoint Server 2007

At WPC, one can do self paced labs. This gives the possibility to explore MS products by means of a VPC and a manual, this at your own pace. One of the self paed labs I followed was HOL167: Microsoft Office Performance Point Server 2007 - Monitoring and Analytics.

PerformancePoint Server is a performance management solution that allows for continuous business improvement through planning, monitoring and analyzing your organisation.

The monitoring and analytics for PerformancePoint Server 2007 provides the ability to evaluate what is happening and why, by bringing data from a variety of data sources and allowing that data to be displayed. The steps in this lab were:
- Open a scorecard builder and connectto a data source
- Create a scorecard and make necessary adjustments
- Publish content to Monitoring Server
- Create a dashboard and publish to office sharepoint server 2007

So build in possibility to export your dashboard to sharepoint server.

Microsoft Dynamics Entrepeneur Solution

Denver, Tuesday July 10 2007

Microsoft announced the launch of the Microsoft Dynamics Entrepreneur Solution.

The Dynamics Entrepreneur Solution is an out-of-the box light ERP solution, built on Microsoft Dynamics NAV with focus on small business up to 5 users.  A wizard driven setup and limited customizations makes this a fast to implement and easy to use solution.

Users will see Dynamics NAV, but of course with limited functionalities.
For small businesses with only a few users, this could mean a first step into the Dynamics NAV world.
Once the number of users goes over 5, an upgrade from the Entrepreneur Solution to Dynamics NAV is the most logical (but also obligated) step.

And what will this cost? Microsoft launches the Entrepreneur Solution at a very competitive price per concurrent user (up to 5 users).

There will be a global phased roll-out with The Netherlands, Germany, Spain and The UK as the first countries.

Koen Stox

Microsoft World Partner Conference 2007 | Denver Colorado US

Monday 09 July 2007: Pre-conference day

During the Microsoft Dynamics pre-conference day at WPC2007 a number of new things re Micrsoft Dynamics NAV 5.0 and 5.1 releases were announced and a number of topics were stressed.

Speakers and audience focussed on documentation.New content and tools were announced to make it easier to customize documentation. As of 5.0 release documentation is also available re localisation features and this into the core objects ( so code comments explaining the localisation).

A new Microsoft Dynamics 5.0 VPC was announced. The VPC is available for partners in the demo center of the partner launch portal of partnersource. With this VPC partners can easily demo features as:
- document approval with email notification
- office integration
- outlook integration
- online maps
- business analytics
- employee portal
- reporting services
- ...
It was recommended to use this VPC as a server, so that you can connect with VISTA images. Microsoft Dynamics NAV demo with vista images, how cool is that! Guidelines on how to connect with VISTA images can be found on whitepaper on partnersource.

Together with the new VPC, several new demo scripts for 5.0 have been released.

The session was followed with a demo illustrating the stability and possibilities of the new 5.0 VPC.
During this demo the new outlook integration was shown. Ok, I know, we all have seen that and it still is realy nice to see this great integration possibility. But what was shown now was how to build a new form in outlook and integrate with this form. In casu a form was build showing item information from NAV. This was done through EXTRA - FORMS - FORM DESIGN in outlook. Realy cool and great opportunity to make the ultimate demo.

Also shown was the Style Sheet Tool for Microsoft Dynamics NAV 5.0. We have already blogged about this tool (see blog item in history) but again what a tool. It is now realy easy to make your own documents in word wich are executed and populated from Microsoft Dynamics NAV 5.0. And this without any knowledge on C/AL or XSLT ... .Also, we can now work with ML captions in word!!. This means we can make multilanguage documents in word. This is the first version of the style sheet tool and at this point it is only available for Word, not yet for Excel.

Another strong focus point during the Microsoft Dynamics NAV pre-conference day was the value of upgrade. Upgrades allow customers to maximise the efficiency of their business and upgrades avoid that partners need to support multiple versions for their clients. To overcome the resource problem that partners suffer to do upgrades, Microsoft has launched the "upgrade service center".
This is a new competency for partners who focus on upgrading Microsoft Dynamics NAV versions. These upgrade service centers do not work directly but only for Microsoft partners.

During the key note, it was stressed that  rearding "project green" or the merge of the different products within the dynamics suite no revolution but continuous evolution is the goal. Microsoft is working towards "shared technology" for all these products but each product will still have a 5 year support window for each release. This means that for example a NAV release in 2010 will be supported untill 2015, no doubt about that.

A new Microsoft Dynamics product was announced: "Microsoft Dynamics Entrepeneur Solution".
This product is build on Microsoft Dynamics NAV and addresses small businesses up to 5 concurrent users. In first phase it will be released in UK, NL, DE an ES.

Also announced is the new version of the Microsoft Dynamics screensaver. This new version will be released at EMEA Convergence 2007 in Copenhagen. It will include the opportunity to customise this screensaver with partner logo and partner specific information which can be made available to the enduser organisation.

Software as Service (SAAS) and the Service Provider License agreement (SPLA) were also communcicated to the audience. This is about hosted Micrsoft Dynamics applications. A market were up to 30% of growth is signed up. This market knows multiple drivers. In EMEA one of the drivers is BPO: business process outsourcing. To make this work a new licence agreement was created: SPLA. This is subscription based licensing. The "Microsoft SPLA Partner" must pay for what his customers use. This cost is based on a software and infrastucture cost. All info can be fond on partnersource.

Microsoft also announced the new high standard for partner developed software: "Certified for Microsoft Dynamics". ISV/partner solutions that want to have this certification need to meet with a number of criteria:
- pass software solution test
- have 10 customer references
- partner must have a service plan
- partner must be gold certified

And finaly we saw a live demo on Microsoft Dynamics NAV 5.1 !!!
As we all know: 3-tier solution with new roll-based interface and standard out of the box webservices and SQL reporting services reports.Some features that were shown:
- fasttabs
- customise action menus
- customise navigation pane
- freeze pane
- choose factboxes
- pages
- lots of sharepoint capabilities !!! add NAV webparts to standard sharepoint (no employee portal requiered)
- Webservices used with visual studio: all standard pages and codeunits can be exposed as webservice

Last but not least, a new mobile development framework for Microsoft Dynamics was announced. This is also available as of Microsoft Dynamics 5.1 version. It allows to develop mobile applications connected to Microsoft Dynamics.

As a general conclusion we can say that it was a busy day with lots of information and one big conclusion: Microsoft is investing big time in Microsoft Dynamics NAV! How cool is that ?

From your MCT reporter in Denver
Vincent Bellefroid 

Performance Dashboard for Microsoft SQL Server

The SQL Server 2005 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature introduced in the SQL Server 2005 SP2 release of SQL Server Management Studio.

The reports allow a you to quickly identify whether there is a current bottleneck on your system, and capture additional diagnostic data that may be necessary to resolve the problem. For example, if the system is experiencing waits for disk IO the dashboard allows the user to quickly see which sessions are performing the most IO, what query is running on each session and the query plan for each statement.

Common performance problems that the dashboard reports may help to resolve include:
- CPU bottlenecks (and what queries are consuming the most CPU)
- IO bottlenecks (and what queries are performing the most IO).
- Index recommendations generated by the query optimizer (missing indexes)
- Blocking/Locking

The information captured in the reports is retrieved from SQL Server's dynamic management views. There is no additional tracing or data capture required, which means the information is always available and this is a very inexpensive means of monitoring your server.

Check out these reports and experiment with them to troubleshoot your performance problems. You can find the reports at: Sql Server Performance Dashboard

Style Sheet Tool for Dynamics NAV 5.0

Office Integration
The release of Microsoft Dynamics NAV 5.0 introduced the option to export any form (apart from matrix forms) from Microsoft Dynamics NAV to Microsoft® Office Word® and Microsoft® Office Excel®.
By simply clicking a button, Microsoft Dynamics NAV will generate an XML document that works in conjunction with an XML style sheet that passes the requested information to either Word or Excel.

Seven style sheets have been included in Microsoft Dynamics NAV 5.0. You can read about these in the White Paper, Creating XML Style Sheets for Data Export from Microsoft Dynamics NAV 5.0.

Create more customized stylesheet?
If you want to create more style sheets or modify an existing one, you need to have knowledge of XML and C/AL programming in order to add the relevant data elements.

Simplified customization with a style sheet tool

To simplify this process, a style sheet tool has been created that does not require programming knowledge. The first version of this tool will support the export of data to Word, and this means that the only skill required to create additional style sheets is some end user knowledge of Word.

In general, all you need to do is set up a definition in Microsoft Dynamics NAV by selecting the fields that you want to be visible in the final Word document, convert the information to Word and format the style sheet template as needed.

This new tool is just released on Partnersource . Go and check it out :-)

Breakpoint table

As a developper the NAV-debugger is a very nice tool to use when you need to debug your code. With the F9 function key you are able to set/remove/disable/enable breakpoints in your code or directly in the debugger. When debugging you can debug your code step by step or from one breakpoint to the next (F8, Ctr-F8, F5,...)

All breakpoints you set in the code, are saved, so the next time you dive into the code, they are still there to be used. But you have to do this, manually, inside each object you want to debug.

In fact, in Dynamics-NAV all breakpoints are maintained in the "Breakpoint" system table. When you run this table you can see all the breakpoints you have set.

For example, if you go into the Form-designer and create a new form, using the wizard, choose the Breakpoint table as your source table:

Then select all the available fields and click on the Finish (or Preview) button. You now have a form that shows you all breakpoints in your database:

 

And further more, you are able to set new breakpoints, remove breakpoints, enable/disable breakpoints ! This can be a very usefull form to have, for Dynamics-NAV developpers.