NAV/SQL Performance - My Two Cents

Jörg Stryk (MVP - Dynamics NAV) about Performance Optimization & Troubleshooting with MS Dynamics NAV and MS SQL Server

Recently in DynamicsUsers.net some "Wiki" was posted showing how to audit Object changes in NAV with SQL site Triggers; see http://dynamicsuser.net/wikis/navdev/auditing-who-changes-an-object-sql-only.aspx
This actually describes how to add some fields to the NAV Object table and to save the User Name etc. who has changed an Objects.
Also, on German NAV Developer's BLOG recently was shown how to generate a SQL Report about "Schema Changes"; see http://blogs.msdn.com/german_nav_developer/archive/2009/05/08/struktur-nderungen-von-sql-nav-tabellen-einfach-berwachen.aspx (German only).

Adding to this – inspired by my MSDynamics.de pal Armin – I would like to show another way of Object Auditing using SQL features!

The basic idea is to write a kind of log – automatically, of course - whenever an NAV Object is inserted, updated or deleted – something like a "Change Log". This log should keep track of various Object versions, ideally so that it is possible to restore (!) any previous version if necessary!

Caution: The following is addressed to experienced NAV/SQL developers. If you do something wrong, you could screw up your database!
The code is provided AS IS, no warranty, no guarantee, no support – USE AT OWN RISK. If you wreck your database, don't blame me.

1. Creating log table to store Object Auditing / Versions

USE [NAV] -- change DB Name here
GO
-- Object Auditing Table
IF EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(N'[dbo].[Object_Auditing]'))
DROP TABLE [dbo].[Object_Auditing]
GO
CREATE TABLE [dbo].[Object_Auditing]
    [Entry No_] bigint IDENTITY
    [Type] int NOT NULL, 
    [Company Name] varchar(30) COLLATE DATABASE_DEFAULT NOT NULL, 
    [ID] int NOT NULL, 
    [Name] varchar(30) COLLATE DATABASE_DEFAULT NOT NULL, 
    [Modified] tinyint NOT NULL, 
    [Compiled] tinyint NOT NULL, 
    [BLOB Reference] image NULL, 
    [BLOB Size] int NOT NULL, 
    [Date] datetime NOT NULL, 
    [Time] datetime NOT NULL, 
    [Version List] varchar(80) COLLATE DATABASE_DEFAULT NOT NULL,
    [Changed by User] varchar(128) COLLATE DATABASE_DEFAULT NOT NULL,
   
[Changed by Host Name] varchar(128) COLLATE DATABASE_DEFAULT NOT NULL,
   
[Changed DateTime] datetime NOT NULL,
   
[Change Type] varchar(30) COLLATE DATABASE_DEFAULT NOT NULL,
CONSTRAINT [Object_Auditing$0] PRIMARY KEY CLUSTERED ([Entry No_])
)
GO
GRANT INSERT, UPDATE, DELETE, SELECT ON [dbo].[Object_Auditing] TO PUBLIC
GO
CREATE INDEX [$1] ON [dbo].[Object_Auditing]
([Type], [ID])
GO
CREATE INDEX [$2] ON [dbo].[Object_Auditing]
([Changed by User], [Changed DateTime])
GO

So this table is actually a copy of the standard "Object" table plus several fields for the "Auditing". The Primary Key is a simple "Entry No." (AutoIncrement, so to speak) so there can be multiple versions of an objects.
(The naming conventions match the default NAV conventions so that this "Object_Auditing" table could be integrated via "View & Linked Object" into the NAV application – this will not be described herewith)

To automatically react to any changes within the Object table, a SQL site trigger has to be created:

2. Create Trigger on "Object" table

USE [NAV] -- change DB Name here
GO
-- Object Version Trigger
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TG_ObjectAuditing]'))
DROP TRIGGER [dbo].[TG_ObjectAuditing]
GO
CREATE TRIGGER [TG_ObjectAuditing] ON [dbo].[Object]
AFTER INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON 
 
DECLARE @ins_count int
DECLARE @del_count int
SELECT @ins_count = COUNT(*) FROM inserted
SELECT @del_count = COUNT(*) FROM deleted 
 
IF (@ins_count <> 0) BEGIN
  IF (@del_count = 0)
    -- Object Inserted 
    INSERT INTO [dbo].[Object_Auditing] 
    (   [Type]
        [Company Name]
        [ID]
        [Name]
        [Modified]
        [Compiled]
        [BLOB Reference]
        [BLOB Size]
        [Date]
        [Time]
        [Version List]
        [Changed by User]
        [Changed by Host Name]
        [Changed DateTime],
        [Change Type]
 

    SELECT 
        ins.[Type]
        ins.[Company Name]
        ins.[ID]
        ins.[Name]
        ins.[Modified]
        ins.[Compiled]
        obj.[BLOB Reference]
        ins.[BLOB Size]
        ins.[Date]
        ins.[Time]
        ins.[Version List]
        SUSER_NAME(), 
        HOST_NAME(), 
        GETDATE(),
       
'INSERT' 
    FROM inserted ins 
        LEFT JOIN [dbo].[Object] obj 
         ON obj.[Type] = ins.[Type] 
         AND obj.[Company Name] = ins.[Company Name] 
         AND obj.[ID] = ins.[ID] 
    WHERE ins.[Type] <>
 
ELSE

   
-- Object Updated 
    INSERT INTO [dbo].[Object_Auditing] 
    (   [Type]
        [Company Name]
        [ID]
        [Name]
        [Modified]
        [Compiled]
        [BLOB Reference]
        [BLOB Size]
        [Date]
        [Time]
        [Version List]
        [Changed by User]
        [Changed by Host Name]
        [Changed DateTime],
        [Change Type]
 

    SELECT 
        ins.[Type]
        ins.[Company Name]
        ins.[ID]
        ins.[Name]
        ins.[Modified]
        ins.[Compiled]
        obj.[BLOB Reference]
        ins.[BLOB Size]
        ins.[Date]
        ins.[Time]
        ins.[Version List]
        SUSER_NAME(), 
        HOST_NAME(), 
        GETDATE(),
       
'UPDATE' 
    FROM inserted ins 
        LEFT JOIN [dbo].[Object] obj 
         ON obj.[Type] = ins.[Type] 
         AND obj.[Company Name] = ins.[Company Name] 
         AND obj.[ID] = ins.[ID] 
    WHERE ins.[Type] <>
 
END ELSE 
 
IF (@del_count <> 0
 
-- Object Deleted 
  INSERT INTO [dbo].[Object_Auditing] 
  (     [Type]
        [Company Name]
        [ID]
        [Name]
        [Modified]
        [Compiled]
        [BLOB Reference]
        [BLOB Size]
        [Date]
        [Time]
        [Version List]
        [Changed by User]
        [Changed by Host Name]
        [Changed DateTime],
       
[Change Type]
   

    SELECT 
        del.[Type]
        del.[Company Name]
        del.[ID]
        del.[Name]
        del.[Modified]
        del.[Compiled]
        obj.[BLOB Reference]
        del.[BLOB Size]
        del.[Date]
        del.[Time]
        del.[Version List]
        SUSER_NAME(), 
        HOST_NAME(), 
        GETDATE(),
       
'DELETE' 
    FROM deleted del 
        LEFT JOIN [dbo].[Object] obj 
         ON obj.[Type] = del.[Type] 
         AND obj.[Company Name] = del.[Company Name] 
         AND obj.[ID] = del.[ID] 
    WHERE del.[Type] <>
 
SET NOCOUNT OFF
GO

This trigger reacts whenever a record is inserted (= new object created), updated (= object modified) or deleted (= object deleted) in the NAV "Object" table; the type of change is flagged in field "Change Type". The Trigger is defined as "AFTER", which means the code is executed only if the original Object INSERT/UPDATE/DELETE was successfully finished.

The – IMHO - smart thing here is, that also the BLOB Reference is copied from "Object" to "Object_Auditing" table, hence the log contains different object versions!
(Remark: the "BLOB Reference" field contains C/AL code compiled into C++ (< NAV 5.0) or C# (>= NAV 5.0) )

Before starting to log the changes it might be feasible to load the current Objects into the Auditing table (optional):

3. Insert initial Object records into log

 
USE [NAV] -- change DB Name here
GO
-- Initialize Object Auditing
INSERT INTO [dbo].[Object_Auditing]
(    [Type]
    [Company Name]
    [ID]
    [Name]
    [Modified]
    [Compiled]
    [BLOB Reference]
    [BLOB Size]
    [Date]
    [Time]
    [Version List]
    [Changed by User]
    [Changed by Host Name]
    [Changed DateTime]
    
[Change Type]
)
SELECT 
    [Type]
    [Company Name]
    [ID]
    [Name]
    [Modified]
    [Compiled]
    [BLOB Reference]
    [BLOB Size]
    [Date]
    [Time]
    [Version List]
    SUSER_NAME(), 
    HOST_NAME(), 
    GETDATE(),
    'INIT'
FROM [dbo].[Object] WHERE [Type] <> 0 ORDER BY [Type],[ID]
GO

Actually, that's it!

So far all is pretty fail-safe, as actually just the Object-records are copied whenever a change happens. Technically, this could slow down the process of saving Objects with "Object Designer", sorry about the inconvenience.
Now some examples to accomplish the "auditing":

-- Last 100 changes (most recent first)
SELECT TOP 100 * FROM [dbo].[Object_Auditing] (NOLOCK)
ORDER BY [Entry No_] DESC
GO

-- All changes of one Object
SELECT * FROM [dbo].[Object_Auditing] (NOLOCK)
WHERE [Type] = 2 -- Form
AND [ID] = 30 -- Item Card
ORDER BY [Entry No_]
GO

-- Changes of a specific user at a certain date
SELECT * FROM [dbo].[Object_Auditing] (NOLOCK)
WHERE [Changed by User] = 'DOMAIN\John.Doe'
AND [Changed DateTime] between '18.05.2009 00:00' and '18.05.2009 23:59'
ORDER BY [Type], [ID], [Entry No_]
GO

But as mentioned above, the cool stuff is to restore any previous version from the "Object_Auditing" table!

And again: the following should be only done by developers who are able to work with TSQL and know what they are doing. If you are doing anything wrong, you might overwrite NAV Objects unintentionally, thus doing severe damage to your application! If you have any doubts, don't do it!

-- Restore previous Object Version
UPDATE [dbo].[Object]
  SET [BLOB Reference] = ObjAud.[BLOB Reference]
  FROM [dbo].[Object_Auditing] ObjAud
  WHERE ObjAud.[Type] = [Object].[Type] AND ObjAud.[Type] = 2 -- Form
    AND ObjAud.[ID] = [Object].[ID] AND ObjAud.[ID] = 30 -- Item Card 
    AND ObjAud.[Entry No_] = 3584 -- Audit Entry/Object Version to be restored, change as required
GO

Limitation: You MUST NOT restore any table versions which have a different structure than the current Object – means, there MUST NOT be any difference regarding Fields, DataTypes, Indexes or SIFT/VSIFT!
The reason is, that overwriting the "BLOB Reference" just changes the C/AL object code, but NOT the actual table object on the SQL Server – hence, if the programmed C/AL table definition is different to the real one, there WILL be lots of very nasty errors; the object gets corrupted!


Something to have in mind: A record is written into the "Object_Auditing" log whenever an NAV Object is saved or even just recompiled! So if there is "heavy development" you might generate a lot of entries! Hence, it might be feasible to establish such an auditing process after going live with a NAV/SQL system …

To clean up the log:

-- Delete old Entries
DECLARE @keep_date datetime
SET @keep_date = DATEADD(mm, -6, GETDATE()) -- delete entries older than 6 months
SELECT @keep_date
DELETE FROM [dbo].[Object_Auditing]
WHERE [Changed DateTime] < @keep_date 
 
-- optional:
AND [Change Type] <> 'INIT' -- do not delete the inital setup

GO

(of course, this piece of code could be executed periodically via a SQL Agent Job)

Applies to: Developed on MS SQL Server 2005 and NAV 4.0 SP3; should also work with higher NAV and SQL Server versions.

So, that's "my two cents" on automatic NAV/SQL object auditing … as always, you feedback and comments are most appreciated!