How using SQLIndex property can affect which index NAV uses

The usual rule for specifying a key in NAV is, that it will chose the first key that matches all the fields specified by SETCURRENTKEY, RunformLink, etc. Example:Table 21 “Cust. Ledger Entry” has the following keys (not all of them listed):   – Entry No.  – Customer No.,Posting Date,Currency Code  – Customer No.,Open,Positive,Due Date,Currency Code  -…


Table Information including Index information (Usage, Blocks and Reads)

  The query below combines these three queries into one:Index Usage QueryRecent Bocking HistoryTable Information Query   It can be used to just see the number of records in each table. But also by just changing “ORDER BY”, it can be used to see which index cause most blocking / wait time / updates or…


Index Usage Information Tool

Attached at the end of this post is a set of NAV objects that collects index usage information, and displays it in a NAV client so that you can sort tables by no. of Indexes / Index Reads / (Index) Block wait time, etc.:   The tool is using these three SQL Queries / DMVs:…


How to read a SQL Profiler trace file into a SQL table

Just a small tip that I use often when I have a large SQL Profiler trace. Rather than reading through trace files line by line, you can read it into a table and then query it. This syntax creates a new table in SQL Server and loads your trace file into it: SELECT * INTO…


SQL Query to show tables, their indexes and index usage

The SQL query below shows a list of tables sorted by the largest tables first. For each table, the indexes in the table are shown. For each index it shows when the index was last used. The query is designed to collect various pieces of information in one place, and give an overview to help supporting decisions when doing…


Beware the SQL Index property on NAV 5.0 SP1

The discussion of the changes starting with Microsoft Dynamics NAV version 5 regarding the use of Dynamic cursors has already been addressed in the article titled “Cursor Types” on the SE Blog – http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/ However, we are seeing more and more cases where the use of the SQL Index property in NAV 5.0 SP1 is causing performance…

4

Basic SQL - Restoring a SQL Server backup

This post is part of “Overview of NAV-specific SQL features for application consultants”. You can back up your Microsoft Dynamics NAV database either from a NAV client or from SQL Server Management Studio. To restore a backup made from SQL Server, follow these steps: 1)     Open SQL Server Management Studio 2)    You don’t need to…


Basic SQL - Creating users in NAV on SQL

This post is part of “Overview of NAV-specific SQL features for application consultants”. Before you can set up a user in Microsoft Dynamics NAV on SQL Server, you must create the user on SQL Server first. If you try to create a user which does not exist on SQL Server, you will get this error…


Basic SQL - How to attach a database to SQL Server

This post is part of “Overview of NAV-specific SQL features for application consultants”. If you receive a SQL Server database file (.MDF), then you must attach this to your SQL Server before you can access it. This is how to do that: 1)     Copy the Database file (.MDF), for example “Demo Database NAV (6-0)_Data.MDF” into…


Basic SQL - How to create a new NAV database for restoring a NAV backup

This post is part of “Overview of NAV-specific SQL features for application consultants”.    This post describes the steps needed to create a new NAV database from a NAV backup (.fbk) file. All the steps are done from a NAV client: 1)     Start a NAV client (finsql.exe), then go to File -> Database -> New….