I'm writing this post because I'm observing that who is responsible to
administer a Microsoft Navision database on SQL Server sometimes lacks on some
concepts that must be clear to have good performances.
Sometimes when the database is heavy load, your queries (expecially when you
have to join multiple tables) could become slow. What's the problem? Has your
Navision developer not written an efficient code?
Sometimes this could be true ( 
) but in many cases it's only a problem
that can be solved by optimizing your SQL Server. Here I want to expose one of
the task that every database administrator must have to do to check the DB
efficiency: check index fragmentation (Index fragmentation can
affect a lot performances of your queries in Navision).
The operations needed to monitor your index efficiency are really simple and
here I show an example.
I want to use the Northwind database and I want to monitor the
efficiency of Order Details indexes after lots of operations on the
db.
First, open SQL Query Analizer and examine the indexes of the
table you want to monitor (here, Order Details table). Launch this
command:
USE Northwind
GO
sp_helpindex 'Order
Details'
The output will be something like this:
You can see that this table has a primary clustered index used on a lot of
queries for joins.
Now, we want to monitor the efficiency of these indexes to check
fragmentation. Here the command we've to launch:
DBCC SHOWCONTIG ('Order Details') WITH
ALL_INDEXES
GO
The output of the command will be something like this:
You can see the values of the two fragmentation fields, Logical Scan
Fragmentation and Extent Scan Fragmentation (I'm sorry
for the Italian language on the images but on this machine I've an Italian
version of SQL Server installed).
The value of Logical Scan Fragmentation field and (less important)
Extent Scan Fragmentation field must be always between 0% to
10% (Extent Scan Fragmentation could be highest if the index is on
more files).
On my database used for this example, you can see that index 4 and 5 are
highest than the recommended range of values and they will require soon a
defragmentation. Index can be rebuilt by using the DBCC
REINDEX command.
Index Fragmentation is an important factor to monitor if your Navision SQL
Server Database is under heavy loads. Too often I see fragmentation values
around 80% and this is absolutely to avoid. Monitoring these values requires
only few minutes of time and advantages on performances could be a lot. Be
careful... 