Fine-tuning the SQL Server database for Dynamics NAV

Exclusive offer: get 50% off this eBook here
Microsoft Dynamics NAV Administration

Microsoft Dynamics NAV Administration — Save 50%

A quick book and eBook guide to installing, configuring, deploying, and administering Microsoft Dynamics NAV with ease

$23.99    $12.00
by Amit Sachdev Sharan Oberoi | September 2010 | Enterprise Articles Microsoft

Before we discuss about fine-tuning the performance of the database for Dynamics NAV, it is important to discuss some key features of the Dynamics NAV application that comprise the core architecture of the application, and the majority of performance issues could be attributed to improper use or misuse of these features.

In this article, by Sharan Oberoi & Amit Sachdev, authors of Microsoft Dynamics NAV Administration, we will cover:

  • SIFT
  • Using indexes/keys in Dynamics NAV
  • Investigating the performance of the database
  • Updating statistics

 

Microsoft Dynamics NAV Administration

Microsoft Dynamics NAV Administration

A quick guide to install, configure, deploy, and administer Dynamics NAV with ease

  • Install, configure, deploy and administer Dynamics NAV with ease
  • Install Dynamics NAV Classic Client (Dynamics NAV C/SIDE), Dynamics NAV Role Tailored Client (RTC), and Dynamics NAV Classic Database Server on your computer to manage enterprise data
  • Connect Dynamics NAV clients to the Database Server in the earlier versions and also the latest Dynamics NAV 2009 version
  • A step-by-step guide filled with examples to help you to accomplish administrative tasks such as securing and maintaining databases using Dynamics NAV

 

Read more about this book

(For more resources on Microsoft see here.)

SIFT

In the Classic database server option, SIFT (Sum Index Flow Technology) is used to make the calculation of balances, sums, and so on. In any other database, this is normally done through calculations and could be a time-consuming process if there are thousands of records in the table. The SIFT data is stored in indexes, which are also called secondary keys in the Classic database server option. The balances based on the "secondary keys" are stored in separate indexes in the database. The programmer can define which fields need calculation by defining the SumIndexFields for the keys, as represented in the following screenshot. Therefore, the retrieval time for things such as account balances and such others is minimal, making the application extremely fast:

What gives a boost to SIFT is the ability to filter on the underlying values that make up SIFT balances. This technology is also known as the flow filter technology, which complements the basic SIFT technology in a significant way.

When SIFT was initially implemented on the Microsoft SQL Server database option for Dynamics NAV, it was done by storing these SIFT columns in summarized tables called SIFT tables that were continuously updated through SQL triggers. Thus, the procedure was expensive and took a toll on the performance when a table containing SIFT indexes was updated.

From the Dynamics NAV 5.0 Service Pack 1 version, Microsoft decided to use indexed views in SQL Server instead of SIFT tables. Dynamics NAV creates one SQL indexed view per key, regardless of how many sum indexed fields there are in that key. Having too many SIFT indexes can adversely affect the performance of the application.

Having too many fields in the SIFT indexes is also not advisable.

The MaintainSIFTIndex property of the index in the base table could be used to optimally design the SIFT indexes. If there is the possibility of the base table not growing so rapidly, it is recommended to keep the MaintainSIFTIndex property to No.

For more information about SQL indexed views, we can refer to the Microsoft SQL library.

Using indexes/keys in Dynamics NAV

Maintaining indexes for Microsoft Dynamics NAV has also been seen as a big performance issue. This is one of the major reasons for performance issues in the Dynamics NAV Classic database. The Microsoft SQL Server is clever enough to sort the data without any index, if the dataset being sorted is not huge.

To access these properties of the keys, go to Object Designer, highlight the table in which the key needs to be modified, click on Design to open the list of fields in the table, and go to View | Keys. A window similar to the following screenshot opens, showing the list of keys:

To open the properties of a particular key, highlight the key and then click on View| Properties, as shown in the following screenshot:

These indexes or keys could be used optimally by using the following properties of the keys:

  • Enabled: This is a property to enable or disable an individual key. A lot of times, developers create a one-off key to be used in a particular report or another piece of customization. This one-off key can be disabled or enabled based on the utilization or frequency of the use of customization.
  • SumIndexFields: This property is used to define the SumIndexFields (whose sum needs to be maintained in the SQL indexed views). A maximum of 20 SumIndexedFields can be selected.
  • KeyGroups: This is a method of combining the various keys together so that they could be enabled or disabled together.

    For example, in the following screenshot we see that the, Key Group property is defined as Consol, which is one of the key groups in the database.

    Keys can be combined together based on the nature of the use of keys or a particular application area.

    To enable or disable a key group, go to File | Database | Information. Click on the Key Group button in the bottom of the Database Information form to open a list of key groups defined in the database.

  • MaintainSQLIndex: Microsoft SQL Server is clever enough to sort the data without an index, though to have the SQL Server sort faster, an index of the fields to be sorted can be created. Any writes to the table will be slower, as the number of indexes in a table grows, as those indexes will have to be updated along with each write transaction.
  • MaintainSIFTIndex:We need to set this property to No if the data to be maintained in the SIFT base tables is less.
  • Clustered: This is where we define whether or not the index is clustered. Clustered indexes are used to specify the sorting of data as it pertains to the storage in the table.

    In Dynamics NAV and also in a SQL Server database, a primary key is a clustered index by default.

  • SQLIndex: We can specify here the actual list of fields that need to be a part of the SQL index.

Investigating the performance of the database

There are a few key performance troubleshooting techniques that are effective in identifying the bottlenecks in the performance of the database.

Microsoft Dynamics NAV Administration A quick book and eBook guide to installing, configuring, deploying, and administering Microsoft Dynamics NAV with ease
Published: September 2010
eBook Price: $23.99
Book Price: $39.99
See more
Select your format and quantity:

Read more about this book

(For more resources on Microsoft see here.)

Updating statistics

There are several tools available from various Microsoft Dynamics ISV(s) that provide effective tools for monitoring the performance of a Dynamics NAV database. We will limit ourselves to the SQL Resource Kit provided by Microsoft in this section of the article.

The Index Defrag tool

The Index Defrag tool is a Dynamics NAV tool that comes along with the Microsoft Dynamics NAV SQL Resource Kit, and helps in identification of the keys that need to be defragmented:

  1. Look for the index defrag tool.fob file in the Dynamics NAV SQL resource toolkit. Import the object file into the database and run the 50090 form, Index Defrag Card.
  2. Start with filling the setup form from the Defrag menu in the bottom as shown in the following screenshot:
  3. Once we have set up and tested the connection to the SQL Server, the next step is to specify the file locations for the scripts that this tool would generate. Specify that in the File Locations Card.
  4. On the Execute tab on the File Locations form, specify the isqlw.exe filename and the path of the file for the SQL Server, if it is not on the same machine.
  5. The tool runs DBCC ShowContig in the background when we click on the Process button on the form, and gathers information about various indexes and also more information about fragmented indexes.
  6. We can also view the recommendations made by the tool and the suggested indexes for defrag or rebuild. Click on the Recommend button at the bottom of the form and click Generate on that form to see a list of indexes. Additional check marks can be placed on the rightmost columns for Index Defrag and Database ReIndex. The ones that are check marked already are the recommendations by the tool. Use the functions listed in the Recommend menu button at the bottom to execute the SQL queries generated in the process.
  7. The tool also creates the SQL scripts and stores them in the specified folders.

    It is worth noting that a defrag will try to reorganize the index more effectively, and the rebuild just drops the index completely and rebuilds it from scratch.

The Key Information tool

Use the Key Information Tool.fob to use the Key Information tool and gather additional information about various keys in the tables of the database as follows:

  1. Run the form 50070, Key Information. The initial server setup is similar to the Index Defrag tool mentioned earlier. Also, it can be accessed from the menu button Key info | Setup SQL Connection.
  2. An additional setup needs to be done to specify what percentage of empty SIFT(s) and key fields is the threshold for the tool to change the color of the data. This can be accessed from Key Info | Key Information Setup. The default values are 80 percent.

  3. Export the tables from the database (as a .txt file) and provide the path of the .txt file by clicking the Load Text Objects option at the bottom of the form.
  4. The tool now reads all the key information from tables and presents that in an easily navigated format.
  5. To investigate a particular table, scroll left or right to the appropriate table and select a key that we want to know about. In the following example, we are looking at the Item Ledger Entry table and the following key: Item No., Open, Variant Code, Positive, Location Code, Posting Date, Expiration Date, Lot No., Serial No. 13. SIFT levels are enabled for this key and we can scroll down to see the SIFT levels.

It is worth noting that Date and Text types in SIFT are not recommended due to the way the bucket tables are created and can cause performance issues and large bucket tables.

Session Monitor

The Session Monitor tool for Dynamics NAV Classic database server and for Dynamics NAV SQL database can be used to find out which sessions/users are causing the locks/blocks, to monitor I/O CPU usage, and many more.

Optimizing tables from the Dynamics NAV interface

The Dynamics NAV client for SQL Server provides an optimization tool that can be used to optimize tables.

For each table in Dynamics NAV, all SQL indexes, except the primary key, are rebuilt using the following SQL statement (this gets executed in the background when we click Optimize):

CREATE …. INDEX …. WITH DROP_EXISTING

To use the "optimize" feature in Dynamics NAV, follow these steps:

  1. Go to File | Database | Information.
  2. In the Database Information window, highlight the table(s) that we need to optimize, and click on the Optimize button at the bottom of the form as shown in the following screenshot:
  3. A progress bar takes us through the entire optimization process for the selected tables.

The main advantages of optimizing the tables include improved performance as a result of improved layout and defragmentation of indexes. It is also to be noted that SIFT index views are cleared of any zero values to free up additional space and improve SIFT performance.

Summary

In the above article we have covered:

  • SIFT
  • Using indexes/keys in Dynamics NAV
  • Investigating the performance of the database
  • Updating statistics

Further resources on this subject:


Microsoft Dynamics NAV Administration A quick book and eBook guide to installing, configuring, deploying, and administering Microsoft Dynamics NAV with ease
Published: September 2010
eBook Price: $23.99
Book Price: $39.99
See more
Select your format and quantity:

About the Author :


Amit Sachdev

Amit Sachdev works as a Technology Advisor for Dynamics products and the lead for SureStep methodology programs at Microsoft Canada. He is responsible for “Dynamics” product awareness, strategic engagement with Microsoft partners, building successful Dynamics practices, and ensuring use of best principles for partners around delivery and implementation methodologies.

Amit has diverse professional experience and in the past has worked in various capacities including advisory consulting, management, designing and implementing business solutions in many countries, spanning across various industries and market segments.

He holds an Engineering degree in Electronics, various awards and certifications in both Microsoft and non-Microsoft technologies. Apart from his eminence as a seasoned “Dynamics” professional, Amit has also been instrumental in providing strategic direction to various start-ups and sits on the board and advisory panels of various non IT and IT related companies.

Sharan Oberoi

Sharan Oberoi is a seasoned Microsoft Dynamics professional and has more than 11 years of experience working as an architect, consultant, and business leader for Microsoft Dynamics products. He works for Tectura in an advisory role.

He has helped various organizations with large scale, global, successful implementations of Microsoft Dynamics NAV. Sharan has also built and grown high performing, culturally diversified, and geographically dispersed consulting teams. At the start of his Microsoft Dynamics career, Sharan was an instrumental startup team member for Microsoft Dynamics NAV (Navision) in India. He led a small team to localize and release Microsoft Dynamics NAV in India and subsequently evangelize Microsoft Dynamics NAV across the newly established partner channel in India.

Sharan worked as a consultant for a few US end clients before moving to New Zealand. He gained his big four consulting experience while working for Ernst & Young in New Zealand for a few years. In 2007, he moved to Vancouver, Canada with his family and started working as a team leader for Tectura. Whilst at Tectura, Sharan has handled diversified roles and has been involved in various product teams and global clients, with, complex, high-risk product implementations and application roll outs.

Having worked for organizations like Tectura, Ernst & Young, and Navision (now Microsoft) Sharan has worked in a dozen or more countries, touching almost every continent. He has worked with clients from diversified industries including Ports, Financial services, Agri businesses, Energy and Power Generation, Shipping, and more.

Books From Packt


Microsoft Dynamics GP 2010 Cookbook
Microsoft Dynamics GP 2010 Cookbook

Microsoft Dynamics NAV 2009 Application Design
Microsoft Dynamics NAV 2009 Application Design

Microsoft Silverlight 4 and SharePoint 2010 Integration
Microsoft Silverlight 4 and SharePoint 2010 Integration

NHibernate 3.0 Cookbook
NHibernate 3.0 Cookbook

Microsoft Silverlight 4 Data and Services Cookbook
Microsoft Silverlight 4 Data and Services Cookbook

Microsoft Silverlight 4 Business Application Development: Beginner’s Guide
Microsoft Silverlight 4 Business Application Development: Beginner’s Guide

Microsoft Azure: Enterprise Application Development
Microsoft Azure: Enterprise Application Development

Microsoft Dynamics AX 2009 Administration
Microsoft Dynamics AX 2009 Administration


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
K
1
4
m
w
4
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software