in

Dynamics User Group

Since 1995 - The Microsoft Dynamics Online User Community

Dynamics NAV Performance on SQL - My Two Cents

Performance Optimization & Troubleshooting with MS Dynamics NAV and MS SQL Server
  • Index Statistics

    Well, we all know that "Index Statistics" are crucial for optimized query processing in SQL Server, that we shouldn't use "Auto. Create Stats" or "Auto. Update Stats", that we should use "Jobs" or "Maintenance Plans" to keep them up to date, etc. – but sometimes it is not that easy …

     

    Preface

    "Index Statistics" – short "stats" – contain statistical information about field values, precisely their "density", which simplified tells how many different values per field are available (check with DBCC SHOW_STATISTICS). These stats are mandatory to process query optimal, as they are used to choose the best index for a query. Missing or insufficient stats will lead to wrong "decisions" of the SQL engine (Query Optimizer), actually resulting in poor performance.

    Statistics could be generated on every field (or combination of fields), but the important ones are those related to indexes.

     

    Out of the Box

    … the DB properties "Auto. Create Stats" and "Auto. Update Stats" are enabled. Thus, SQL Server generates the statistics which it "thinks" are necessary – and updates them whenever it "thinks" it is necessary. This means, that one could be never sure, if indeed all required stats are available, and they are sufficiently maintained.

    As in NAV it possible for every user to apply filters on almost every table field, and due to the fact that NAV mostly send SELECT * (asterisk = all fields) SQL Server will sooner or later automatically create a statistic for nearly every column in a table! Hence, during time a remarkable overhead of stats is generated, and as the stats are also automatically updated write transaction would perform slower day by day.

     

    What we want

    To get the stats we – actually the Db – want to have is sufficient index stats, where we know what is generated and when! Thus, the first step is to disable the "Auto." stuff in the DB options. The second step is to clean up the previously created auto stats.

    List auto stats:

    select [id], [object_name] = object_name([id]), [name] from sysindexes

    where (indexproperty([id], [name], N'IsStatistics') = 1)

    and (indexproperty([id], [name], N'IsAutoStatistics') = 1) -- Filter on Auto. Stats

    and (isnull(objectproperty([id], N'IsUserTable'),0) = 1)

    order by object_name([id])

     

    This will display a list of stats named _WA_Sys_... .

    Drop auto stats:

     

    set statistics io off

    set nocount off

     

    declare @id int, @name varchar(128), @statement nvarchar(1000)

    declare stat_cur cursor fast_forward for

    select [id], [name] from sysindexes

    where (indexproperty([id], [name], N'IsStatistics') = 1)

    and (indexproperty([id], [name], N'IsAutoStatistics') = 1) -- Filter on Auto. Stats

    and (isnull(objectproperty([id], N'IsUserTable'),0) = 1)

    order by object_name([id])

    open stat_cur

    fetch next from stat_cur into @id, @name

    while @@fetch_status = 0 begin

    set @statement = 'DROP STATISTICS [' + object_name(@id) + '].[' + @name + ']'

    begin transaction

    print @statement

    exec sp_executesql @statement

    commit transaction

    fetch next from stat_cur into @id, @name

    end

    close stat_cur

    deallocate stat_cur

     

    The third step is to generate the required index stats:

    exec sp_updatestats

    go

    exec sp_createstats 'indexonly'

     

    This will generate/update only statistics related to indexes and columns which are part of an index! Ideally, these two procedures should be executed daily – plus a weekly full statistics rebuild, by e.g. using the "Update Statistics" task of the "Maintenance Plan" feature. Display stats with

    select [id], [object_name] = object_name([id]), [name] from sysindexes

    where (indexproperty([id], [name], N'IsStatistics') = 1)

    and (indexproperty([id], [name], N'IsAutoStatistics') = 0) -- Filter on User Stats

    and (isnull(objectproperty([id], N'IsUserTable'),0) = 1)

    order by object_name([id])

     

    The statistics are named like the corresponding index (e.g. "$1") or the index column (e.g. "Item No_").

     

    So far, so good – so what?

    Well, actually now we have the stats we want and need to have. But these stats could also cause trouble – in very few cases: In NAV (C/SIDE Object Designer), if you change the definition of a field which is part of an index – thus we have our stats on it -, e.g. changing the name or data-type (also property "SQL DataType"), an error will raised, telling that the ALTER TABLE ALTER COLUMN command failed due to a related statistic.

    This happens, because neither SQL nor C/SIDE are dropping those "user statistics" before altering the column in SQL – only "auto. stats" would be automatically deleted (refer to "Books Online" about ALTER TABLE … ALTER COLUMN)!

    Hence, if this happens it is necessary to remove the "user stats" before altering the objects :

    set statistics io off

    set nocount off

     

    declare @id int, @name varchar(128), @statement nvarchar(1000)

    declare stat_cur cursor fast_forward for

    select [id], [name] from sysindexes

    where (indexproperty([id], [name], N'IsStatistics') = 1)

    and (indexproperty([id], [name], N'IsAutoStatistics') = 0) -- Filter on User Stats

    and (isnull(objectproperty([id], N'IsUserTable'),0) = 1)

    order by object_name([id])

    open stat_cur

    fetch next from stat_cur into @id, @name

    while @@fetch_status = 0 begin

    set @statement = 'DROP STATISTICS [' + object_name(@id) + '].[' + @name + ']'

    begin transaction

    print @statement

    exec sp_executesql @statement

    commit transaction

    fetch next from stat_cur into @id, @name

    end

    close stat_cur

    deallocate stat_cur

     

    Afterwards, the required stats have to be re-created using the sp_createstats 'indexonly' procedure.

     

    Again, this should happen rarely, as you won't change data type or index columns frequently …

  • Downgrading NAV

    Well, a current discussion is whether to go for NAV 5.0 Service Pack 1 or not. On the one hand, NAV 5.0 SP1 has lots of improvements regarding SQL performance (Indexed Views, Buffered Inserts, etc.), on the other hand there are other issues and problems (slow FlowFields, lacking SQL 2000 capability, etc.).

    Once the decision was taken to upgrade – and in the following I ONLY refer to a technical upgrade, means the change of the C/SIDE programs, DLL, etc. – normally there is no way back to a previous version; except when restoring a backup which was taken before the migration. If the system with the new version was online & used for a certain time, a "point of no return" will be passed, when restoring an old backup would mean non-acceptable loss of data and time …

    Restoring a FBK – a backup file created from C/SIDE client, not SQL Server – to a previous version may be possible, but with a large database this could be QUITE time consuming and may raise different issues.

    Currently I'm checking out NAV 5.0 SP1 a little bit, evaluating upgrade scenarios etc. and – according to that – potential fall-back solutions. In the following I would like to describe how it is technically possible to downgrade without restoring a backup!

    CAUTION! The following is really tricky business and is for sure NOT recommended – it just shows a technical possibility! This solution EXCLUSIVELY refers to SQL Server installations, NOT native C/SIDE databases. The method was developed in "laboratory" and has NEVER been tested under real life situations, so consider this as an EXPERIMENT only.

    This proceeding is highly risky and may cause loss of data or corrupted database integrity if unexpected problems arise!

    This BLOG is addressed to EXPERIENCED NAV/SQL "troubleshooters" who EXACTLY know what they are doing – or better not doing.

    If you want to do this with your databases, you do that at OWN RISK, of course! If you screw things up DON'T BLAME ME!

     

    Situation

    A NAV Database – NAV 4.0 SP3 Build 26565 – was technically upgraded to version NAV 5.0 SP1 Build 26084. Objects/Application have not been upgraded, they are still version 4.00.

    Database Server:    SQL Server 2005 Standard Edition, Build 3200

    Current Database:    Name: Navision_Downgrade, Objects: NAV 4.0 SP3, C/SIDE: 5.0 SP1 26084

    Target Database:    Name: Navision_Downgrade, Objects: NAV 4.0 SP3, C/SIDE: 4.0 SP3 26565

     

    Step 1 – Backup

    In NAV 5.0 all Tables (ID < 2000000000) have to be exported into a FOB-File. This is necessary to save the current table-, key-, index- and SIFT definitions.

    With SQL Server features the whole database MUST be backed up before continuing (simply to be able to rollback if everything fails).

     

    Step 2 – Deleting NAV 5.0 System Objects

    The Tables 2000000065, 2000000066, 2000000067, 2000000068 were introduced with NAV 5.0 and were added during the former technical upgrade process. Thus, before downgrading these objects have to be removed as they are not compatible with NAV 4.0.

    As these tables cannot be deleted within the "Object Designer" this has to be done via "Management Studio" and TSQL:

    USE [Navision_Downgrade]

    GO

    DELETE FROM "Object" WHERE "ID" IN (2000000065, 2000000066, 2000000067, 2000000068)

     

    Step 3 – Dropping VSIFT

    During the former technical upgrade the "SIFT Tables" from NAV 4.0 have been replaced in NAV 5.0 SP1 by "Indexed Views VSIFT". Before downgrading all VSIFT views have to be deleted. This could be done with a NAV Codeunit, executing this C/AL Code:

    Globals:

    Key        Record "Key"

    Window    Dialog

    c        Integer

    i        Integer

    Codeunit – Trigger OnRun()

    Key.SETFILTER(TableNo, '<%1', 2000000000);

    IF Key.FINDSET(TRUE) THEN BEGIN

    Window.OPEN('@1@@@@@@@@@@@@@@@@@@@@@@@@@@@@');

    c := Key.COUNT;

    REPEAT

    IF Key.MaintainSIFTIndex THEN BEGIN

    Key.VALIDATE(MaintainSIFTIndex, FALSE);

    Key.MODIFY;

    END;

    i := i + 1;

    Window.UPDATE(1, ROUND(10000 / c * i, 1));

    UNTIL Key.NEXT = 0;

    Window.CLOSE;

    END;

    Removing the flag "MaintainSIFTIndex" will delete the corresponding VSIFT view. To be able to restore the original SIFT definitions, the FOB from Step 1 is mandatory!

     

    Step 4 – Downgrade/Conversion

    Here it is necessary to understand the version management of C/SIDE. If a "technical upgrade" is performed, NAV asks if the database should be "converted". Well, in fact it is impossible to "convert" the database – it is a SQL Server 2005 database, and will remain a SQL Server 2005 database, regardless which NAV version is using this database.

    The "conversion" actually performs a re-writing of the SQL site table-triggers which run the SIFT management (aka "SIFT Trigger"); and/or completely rebuilds the SIFT or VSIFT structures.

    The C/SIDE client controls which NAV Client is allowed to access the database (or not) by checking the field "databaseversionno" in the table "$ndo$dbproperty" (not available in "Object Designer", access via SQL only).

    • With NAV 5.0 SP1 Build 26084 the "databaseversionno" is 95
    • With NAV 4.0 SP3 Build 26565 the "databaseversionno" is 63

    When logging on the C/SIDE client reads the table "$ndo$dbproperty" and compares this "databaseversionno" with an internal number. If there is no match, NAV raises an error (internal < versionno) or asks for "conversion" (internal > versionno).

    So, by setting the "databaseversionno" to a value of an older database, the database could be accessed by older C/SIDE clients!

    For example, when changing in NAV 5.0 SP1 the value from 95 to 63 the "converted" database could be accessed by a NAV 4.0 SP3 26565 client! Caution: NOT everything is possible here!

    If the "databaseversionno" is set to a lower version number than the NAV client has, C/SIDE will ask for a "conversion".

    Hence, the "databaseversionno" is to be set to 62 (sixty two, 63 minus 1).

    Now, when opening the database with the NAV 4.0 SP3 26565 client, C/SIDE will ask to "convert the database". As we have already disabled all "SIFT Indexes" in Step 3, there is not much to convert.

     

    Step 5 – Restoring the SIFT Tables

    Finally, after successful "conversion", the Table-Backup FOB from Step 1 has to be imported and the original SIFT Structures will be restored: the SIFT tables are created from the original Table definitions, saved in the FOB. This could take a LONG time (see below)!

    Have in mind that all "SIFT Levels To Maintain" are now set to "standard" and further optimization is required (reducing "Buckets" etc.).

     

    Finished. If no error was shown, the database is back on NAV 4.0 SP3 26565, databaseversionno 63.

    Once this was performed successfully (???) you have to TEST, TEST, TEST, TEST, TEST, … OF COURSE!!!

     

    Other considerations

    The "downgrade-conversion" does not just (re-)create the SIFT Tables, of course these tables are filled with records, too. And – depending on the database size & number of records – this can be MILLIONS of SIFT records, increasing the database size DRAMATICALLY! Thus, the conversion could be a real super-size transaction, putting HUGE load on the server. Therefore it is feasible to set the database's Recovery Model to SIMPLE, and to restrict the user access (sysadmin, db_owner only) to avoid conflicting user transactions. To reduce the load on the server, the tables from the FOB (from Step 1) should not be imported all at once, but in several smaller packages.

    And again: this is really tricky business! If you screw up your database, YOU TAKE THE BLAME! No support, warranty, guarantee, or anything.

     

    Good Luck!

  • „Indexed Views“ performing slower than „SIFT Tables“ ?

    Today I just ran a brief test to compare the performance of queries on "SIFT Tables" (older NAV versions) with queries on "Indexed Views" (introduced with NAV 5.00 SP1) called VSIFT.

     

    Well, in previous BLOGs or forum threads I already stated my concerns about "reading performance" on large tables with VSIFT (no question about "writing performance", this is definitely improved). As a "View" is just a pre-defined SELECT statement on a table (here supported by its own Index) this "View" is actually always gathering the data from the source table, e.g. the Ledger Entry table. With "old" SIFT Tables the required data is read from aggregated/summed records in dedicated tables, thus the data volume is compressed here.

     

    Of course, there is a remarkable difference between standard/non-tuned SIFT Tables and VSIFT, but I wanted to compare optimized SIFT with VSIFT (that's the different to common MS test-scenarios ;c) ).

     

    So please find here the tests I ran on a customer's (test-)system:

     

    Table (T380):    Detailed Vendor Ledg. Entry

    No. of Recs (T380):    1.460.800

       

    Key:    Vendor No., Initial Entry Due Date, Posting Date, Currency Code

    SumIndexFields:    Amount, Amount (LCY), Debit Amount, Credit Amount, Debit Amount (LCY), Credit Amount (LCY)

       

    Buckets:    Available 0 to 9, only 7 is enabled (Tuning!)

    Additional Index:    Covering Index on related SIFT Table "xxx$380$1" (Tuning!)

       

    CREATE INDEX ssi_CovIdx ON "xxx$380$1"

    (

    [bucket],

    [f9],

    [f20],

    [f4],

    [s7],

    [s8],

    [s16],

    [s17],

    [s18],

    [s19]

    )

       

    No. of Recs (SIFT):    223.080

       

    The original query:

       

    SELECT SUM("s7"),SUM("s8"),SUM("s16"),SUM("s17"),SUM("s18"),SUM("s19")

    FROM "xxx$380$1"

    WITH (READUNCOMMITTED)

    WHERE (bucket=7 AND f9='004792')

       

    Reads:    13 pages

    CPU:    0 milliseconds

    Duration:    1 millisecond

    Execution Plan:    Index Seek on Covering Index

       

       

    Indexed View (VSIFT):

       

    -- Indexed View to replace SIFT Table

    CREATE VIEW [dbo].[xxx$Detailed Vendor Ledg_ Entry$VSIFT$3]

    WITH SCHEMABINDING AS

    SELECT

    "Vendor No_",

    "Initial Entry Due Date",

    "Posting Date","Currency Code",

    COUNT_BIG(*) "$Cnt",

    SUM("Amount") "SUM$Amount",

    SUM("Amount (LCY)") "SUM$Amount (LCY)",

    SUM("Debit Amount") "SUM$Debit Amount",

    SUM("Credit Amount") "SUM$Credit Amount",

    SUM("Debit Amount (LCY)") "SUM$Debit Amount (LCY)",

    SUM("Credit Amount (LCY)") "SUM$Credit Amount (LCY)"

    FROM dbo."xxx$Detailed Vendor Ledg_ Entry"

    GROUP BY "Vendor No_","Initial Entry Due Date","Posting Date","Currency Code"

       

    -- Clustered Index on View

    CREATE UNIQUE CLUSTERED INDEX [VSIFTIDX] ON [dbo].[xxx$Detailed Vendor Ledg_ Entry$VSIFT$3]

    (

    [Vendor No_],

    [Initial Entry Due Date],

    [Posting Date],

    [Currency Code]

    )

       

    No. of Recs (VSIFT):    223.138

       

    Corresponding query on VSIFT:

       

    SELECT SUM("SUM$Amount"),SUM("SUM$Amount (LCY)"),SUM("SUM$Debit Amount"),SUM("SUM$Debit Amount (LCY)"),SUM("SUM$Credit Amount"),SUM("SUM$Credit Amount (LCY)")

    FROM [xxx$Detailed Vendor Ledg_ Entry$VSIFT$3]

    WITH (READUNCOMMITTED)

    WHERE ("Vendor No_"='004792')

       

    Reads:    53 pages

    CPU:    31 milliseconds

    Duration:    36 milliseconds

    Execution Plan:    Clustered Index Seek on VSIFTIDX

       

    Additional Index:    Covering Index on VSIFT (Tuning!)

       

    CREATE INDEX ssi_CovIdx ON "xxx$Detailed Vendor Ledg_ Entry$VSIFT$3"

    (

    "Vendor No_",

    "Initial Entry Due Date",

    "Posting Date","Currency Code",

    "SUM$Amount",

    "SUM$Amount (LCY)",

    "SUM$Debit Amount",

    "SUM$Credit Amount",

    "SUM$Debit Amount (LCY)",

    "SUM$Credit Amount (LCY)"

    )

       

    Reads:    10 pages

    CPU:    16 milliseconds (?)

    Duration:    9 milliseconds

    Execution Plan:    Index Seek on Covering Index

       

    Results:

     

      

    SIFT

    VSIFT (standard)

    VSIFT (tuned)

    Reads

    13

    53

    10

    CPU

    0 msec

    31 msec

    0 msec

    Duration

    1 msec

    36 msec

    5 msec

    Execution Plan

    Index Seek (Cov. Idx)

    Index Seek (Clustered Idx)

    Index Seek (Cov. Idx)

       

    Of course all queries delivered identical results. But reading from VSIFT took 40 more Page Reads (about 4 times more!) and 35 milliseconds longer (about 35 times longer). And this table T380 is actually a small one, in this table we have Ledger Entry tables containing far more records, e.g. G/L Entry (23.082.836) or Warehouse Entry (46.721.678)!

       

    After little tuning the VSIFT by also adding a "Covering Index" the difference the results are almost the same, VSIFT is reading less pages (- 23%) but taking little longer.

       

    (Just to point out: the figures show the objective measurement; the subjective user-experience will not feel any difference here – depending on the table size and number of queries executed within a process!)

       

    Well, this test for sure isn't representative, but I feel my concerns about "VSIFT performing worse than SIFT in reading transactions" are somewhat confirmed – at least VSIFT "out-of-the-box" - … and obviously still some tuning is required to optimize the performance!

       

    I would really appreciate to get your comments and especially experiences with this issue!

  • Blocks & Deadlocks in NAV with SQL Server

    Well, "Performance Optimization" in NAV & SQL is some sort of "Cornucopia of issues" – starting from Hardware Sizing, Platform Configuration, Index Tuning, SIFT Optimization, etc. … and – last but not least – the question: "How to prevent and solve blocking conflicts?"

    At the "Directions EMEA 2008" Event in Paris I tried to give some feasible, practical advices to answer this question. The following should give some further explanations, in addition to the "Session Package" one could download from my website: http://www.stryk.info/directionsemea_blockdetection.zip

    (Based on NAV 4.00 and SQL Server 2005)

    ### Changes 12.05.2008 ### >>>

    Due to the great response I got on this BLOG I could further improve the scripts, thus the processes are more reliable, especially when it is about "translating" from "waitresource" into "table name". Additionally, now the relevant Index Names are shown, too.(Many thanks to Legosz (Mark) for his inspiration and support with this!)

    ### Changes 12.05.2008 ### <<< 

     General

    "Locking" is actually some sort of reservation of a system-resource as a record, table, etc.. "Locking" is crucial to grant the consistency of data, and every database MUST lock at certain time at a certain extend. When we're talking about "locking problems" we actually mean "blocking problems"; means if two (or more) processes want to lock the same resource and a conflict is generated. Thus, we are encountering problems when a "lock" results in a "block". Hence, it is a matter of probability if/how/when such a conflict could occur. To reduce "blocking conflicts" it is necessary to reduce the probability of "locking conflicts" …

    Lock Granularity

    To minimize the risk of blocks it is necessary to keep the "lock granularity" (locking-level) as small as possible. Lock granules are for example Row, Range, Page, Index, Key, Table, etc.. The lower the granularity, the lower the probability of encountering blocks. For example, the probability that two processes at the same time want to work with the "Sales Header" table is much higher than the probability that these two processes want to work with the same record (the same document). Thus, if the "Sales Header" table would be locked, the risk of getting blocked if far higher than if just a single record was locked!

    Usually SQL Server sets the optimal lock granularity, but with NAV this could be overruled (which is mostly a bad idea). It is a common misunderstanding that the "Always Rowlock" feature in NAV is required to lock on row-level. Regardless of this setting, SQL Server will ALWAYS start locking on the lowest possible granularity, e.g. row-level. If "Always Rowlock" is enabled, then NAV will add the query hint ROWLOCK which prevents "Lock Escalation": If SQL Server recognizes that the administration of multiple Row-Locks is costly (threshold is short before 1000 row-locks) it will escalate the locking level, means it will replace the many Row-Locks by e.g. few Range-Locks or Page-Locks, etc..

    The ROWLOCK hint prevents this escalation and forces SQL Server to keep the Row-Lock granularity. Hence, "Always Rowlock" is indeed reducing blocking conflicts – due to the forced low granularity – but this could be at cost of the overall performance: all these locks have to be maintained and require a sufficient amount of RAM. I recommend to disable "Always Rowlock" and investigate and solve the arising blocking conflicts specifically, not wasting precious hardware resources.

    With NAV before version 4.00 the lock granularity could also be set:

    CREATE TABLE [$ndo$dbconfig] (config VARCHAR(1024))
    GRANT SELECT ON [$ndo$dbconfig] TO [public]
    GO
    INSERT INTO [$ndo$dbconfig] VALUES ('DefaultLockGranularity=Yes')

    (See also the "Application's Designers Guide")

    The "Lock Granularity" is also influenced by the so called "Transaction Isolation Level" – the degree of how much a transaction is isolated from other transactions. With NAV the default level is READUNCOMMITED, means even data written by transactions that have not yet committed could be read from other transactions (aka "Dirty Reads").

    With READUNCOMMITTED the risk of encountering blocks is minimal. The highest isolation level is SERIALIZABLE – here a transaction is completely isolated from others, hence it has the highest risk of generating blocking conflicts. With the NAV commands Record.LOCKTABLE or Record.FINDSET(TRUE) the TSQL statement SET TRANSACTION ISOLATION LEVEL is sent, thus the transaction is serialized. Further, after serialization NAV sends an UPDATELOCK hint which transforms the formerly set "Shared Locks" (S) into "Exclusive Locks" (X) (see "Books Online" for details).

    Hence, serializing transactions could cause trouble, so it should be only done where it is really necessary, e.g. to avoid "Dirty Reads" (which is the case in all standard posting routines when Ledger Entries are created).

    Automatic Block Detection

    Before Blocks could be resolved it is necessary to know precisely the "Who is Who" about the conflicts; means "Who is blocking whom?", "When?" and "On which Resource?" – and of course: "How often does a conflict occur?". Solving blocking conflicts basically has to be done within the C/AL source code – and this task could be very complex and time consuming! Thus it is crucial to know about this "Who is Who" to fix the worse problems first, not spending time on singular events. The following describes one solution – there might be plenty – which I consider quite simple but smart:

    Step 1 – Block Log Table

    To collect the data about blocks we need a table ("ssi_BlockLog") for storage:

    USE [Navision]
    GO CREATE TABLE [dbo].[ssi_BlockLog]
     (
      [entry_no] BIGINT IDENTITY CONSTRAINT [ssi_BlockLog$pk_ci] PRIMARY KEY CLUSTERED,
      [timestamp] DATETIME,
      [waitresource] VARCHAR(128),
      [table_name] VARCHAR(128) COLLATE database_default,
      [index_name] VARCHAR(128) COLLATE database_default,
      [waittime] BIGINT,
      [lastwaittype] VARCHAR(128),
      [spid] INT,
      [loginame] VARCHAR(128) COLLATE database_default,
      [hostname] VARCHAR(128) COLLATE database_default,
      [cmd] NVARCHAR(255) COLLATE database_default,
      [status] VARCHAR(128) COLLATE database_default,
      [cpu] BIGINT,
      [blocked by] INT,
      [loginame 2] VARCHAR(128) COLLATE database_default,
      [hostname 2] VARCHAR(128) COLLATE database_default,
      [cmd 2] NVARCHAR(255) COLLATE database_default,
      [status 2] VARCHAR(128) COLLATE database_default,
      [cpu 2] BIGINT,
      [db] VARCHAR(128) COLLATE database_default,
      )
    GO

    Step 2 – Stored Procedure to save the data 

    A "Stored Procedure" "ssi_blockdetection" is used to extract the block-information from various DMV (Dynamic Management Views) and save it into table ssi_BlockLog (from step 1).

    CREATE PROCEDURE [dbo].[ssi_blockdetection] AS
    IF EXISTS (SELECT * FROM sys.dm_exec_requests WHERE [blocking_session_id] <> 0) BEGIN
        SET STATISTICS IO OFF
        /* Saving initial data */
     INSERT INTO [dbo].[ssi_BlockLog]
     (     
         [timestamp]
        ,[waitresource]
        ,[table_name]
        ,[index_name]
        ,[waittime]
        ,[lastwaittype]
        ,[spid]
        ,[loginame]
        ,[hostname]
        ,[cmd]         
        ,[status]
        ,[cpu]
        ,[blocked by]
        ,[loginame 2]
        ,[hostname 2] 
        ,[cmd 2]  
        ,[status 2]
        ,[cpu 2]
        ,[db]
     ) 
      SELECT [timestamp] = GETDATE(),
         s1.[wait_resource],
         null,
         null  ,
         s1.[wait_time],
         s1.[last_wait_type],
         session1.[session_id],
         session1.[login_name],
         session1.[host_name],
         s1.[command],
         session1.[status],
         session1.[cpu_time],
         [blocked by] = s1.[blocking_session_id],
         [loginame 2] = session2.[login_name],
         [hostname 2] = session2.[host_name],
         [cmd 2] = s2.[command],
         [status 2] = session2.[status],
         [cpu 2] = session2.[cpu_time],
         [db] = DB_NAME(s1.[database_id])
         FROM sys.dm_exec_requests (NOLOCK) s1
         LEFT JOIN sys.dm_exec_requests (NOLOCK) s2 ON s2.[session_id] = s1.[blocking_session_id]
         LEFT JOIN sys.dm_exec_sessions (NOLOCK) session1 ON session1.[session_id] = s1.[session_id]
         LEFT JOIN sys.dm_exec_sessions (NOLOCK) session2 ON session2.[session_id] = s1.[blocking_session_id]
         WHERE s1.[blocking_session_id] <> 0

        /* Updating data, Parsing "wait_resource */
        DECLARE @waitresource VARCHAR(128), @dbid INT, @objid BIGINT, @indid INT, @partid BIGINT, @idxname VARCHAR(128)
        DECLARE @i_db INT, @i_tab INT, @i_idx INT, @i_part INT
        DECLARE waitresource_cur CURSOR FOR SELECT LTRIM(RTRIM([waitresource])) FROM [dbo].[ssi_BlockLog] WHERE [table_name] IS NULL FOR UPDATE
        OPEN waitresource_cur
        FETCH NEXT FROM waitresource_cur INTO @waitresource
        WHILE @@FETCH_STATUS = 0 BEGIN
          SET @i_db = 0
          SET @i_tab = 0
          SET @i_idx = 0
          SET @i_part = 0
          SET @i_db = CHARINDEX(':', @waitresource)
          SET @i_tab = CHARINDEX(':', @waitresource, @i_db + 1)
          IF @i_tab <> 0
            SET @i_idx = CHARINDEX(':', @waitresource, @i_tab + 1)
          IF @i_idx = 0 AND @i_tab <> 0
            SET @i_part = CHARINDEX('(', @waitresource, @i_tab + 1)
          IF @i_db <> 0 AND @i_tab <> 0
            SET @dbid = LTRIM(SUBSTRING(@waitresource, @i_db + 1, @i_tab - @i_db - 1))     
          IF @i_tab <> 0 AND @i_idx <> 0 BEGIN
            SET @objid = LTRIM(SUBSTRING(@waitresource, @i_tab + 1, @i_idx - @i_tab - 1))
            UPDATE [dbo].[ssi_BlockLog] SET [table_name] = OBJECT_NAME(@objid) WHERE CURRENT OF waitresource_cur
          END
          IF @i_idx <> 0 BEGIN
            SET @indid = LTRIM(SUBSTRING(@waitresource, @i_idx + 1, LEN(@waitresource) - @i_idx))
            SELECT @idxname = "name" FROM sys.indexes (NOLOCK) WHERE "object_id" = @objid AND "index_id" = @indid
            UPDATE [dbo].[ssi_BlockLog] SET [index_name] = @idxname WHERE CURRENT OF waitresource_cur
          END
          IF @i_tab <> 0 AND @i_part <> 0 BEGIN
            SET @partid = LTRIM(SUBSTRING(@waitresource, @i_tab + 1, @i_part - @i_tab - 1))
            SELECT @objid = "object_id", @indid = "index_id" FROM sys.partitions (NOLOCK) WHERE "partition_id" = @partid
            UPDATE [dbo].[ssi_BlockLog] SET [table_name] = OBJECT_NAME(@objid) WHERE CURRENT OF waitresource_cur
            SELECT @idxname = "name" FROM sys.indexes (NOLOCK) WHERE "object_id" = @objid AND "index_id" = @indid
            UPDATE [dbo].[ssi_BlockLog] SET [index_name] = @idxname WHERE CURRENT OF waitresource_cur
          END
          FETCH NEXT FROM waitresource_cur INTO @waitresource
        END
        CLOSE waitresource_cur
        DEALLOCATE waitresource_cur
    END

    Step 3 – Job to save the data

    A SQL Server Agent Job will then execute the procedure "ssi_blockdetection" (from step 2) .

    USE [msdb]
    GO BEGIN TRANSACTION
    DECLARE @ReturnCode INT
    SELECT @ReturnCode = 0
    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'STRYK System Improvement' AND category_class=1)
    BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'STRYK System Improvement'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    END
    DECLARE @jobId BINARY(16)
    EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'SSI: Block Detection',
            @enabled=1,
            @description=N'Automatic Block-Detection by STRYK System Improvement,
    http://www.stryk.info'
    ,
            @category_name=N'STRYK System Improvement',
            @owner_login_name=N'sa', @job_id = @jobId OUTPUT
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'blockdetection',
            @step_id=1,
            @cmdexec_success_code=0,
            @on_success_action=1,
            @on_fail_action=2,
    @subsystem=N'TSQL',
            @command=N'EXECUTE [dbo].[ssi_blockdetection]',
            @database_name=N'Navision' -- Change Database Name on demand        
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    COMMIT TRANSACTION
    GOTO EndSave
    QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
    EndSave:

    (Remark: the TSQL which extracts the data from the system-tables might be improved, it's just an example – please feel free to improve, I would appreciate your support!)

    Step 4 – Alert to monitor "Processes Blocked"

    To automatically keep track about the blocks a SQL Server Agent Alert ("SSI: Block Detection") could monitor the SQL Server Performance Counter "SQLServer::General Statistics – Processes blocked"; further it responds by executing the SQL Agent Job:

    USE [msdb]
    GO EXEC msdb.dbo.sp_add_alert @name=N'SSI: Block Detection',
            @enabled=1,
            @delay_between_responses=10,
            @include_event_description_in=0,
            @performance_condition=N'SQLServer:General Statistics|Processes blocked||>|0',
            @job_name=N'SSI: Block Detection'
    GO

    Now, if the number of "Processes blocked" raises above 0 – a block was encountered – this Alert is triggered and executes the SQL Server Agent Job ("SSI: Block Detection" from Step 2) which logs the block-information - the "Who is Who of Blocks"!

    THAT'S IT – AUTOMATIC BLOCK DETECTION ESTABLISHED!!!

    Finally we could easily analyze this data, e.g. finding out who is cause or victim of blocks, which resources are affected, how long do the blocks take, etc.. For example:

    USE [Navision]
    GO

    SELECT [waitresource], [table_name], [index_name], [blocked_login] = [loginame], [blocking_login] = [loginame 2], [count] = COUNT([entry_no]),[max_duration] = MAX([waittime])
    FROM dbo.ssi_BlockLog
    --WHERE [timestamp] BETWEEN '17.04.2008 00:00' AND '17.04.2008 23:59'
    GROUP BY [waitresource], [table_name], [index_name], [loginame], [loginame 2]
    ORDER BY COUNT([entry_no]) DESC
    GO

    SELECT * FROM ssi_BlockLog ORDER BY [entry_no] DESC
    GO

    (Remark: these examples a created for a database called "Navision" – please change on demand)

    Solving and Preventing Blocks

    Well, we have to be aware that it is impossible to solve all kinds of potential blocking conflicts, as it is impossible to predict all probably – and improbably – circumstances of conflicting processes. Means: there will always be blocks, we only can reduce the problems down to a certain degree. Due to the way how certain processes are designed in NAV – e.g. posting routines – the success is limited to the number of concurrent users running these transactions in parallel. To reduce blocks these optimizations are necessary:

    1. SIFT Optimization

    (see my BLOG http://dynamicsuser.net/blogs/stryk/archive/2007/10/28/included-columns-vs-sift-tables.aspx for further details) When SIFT data is updated (aggregation) the system has to establish tight locks to make sure only committed data is included when summing up the figures. The more aggregation levels (buckets) exist, and the higher this aggregation happens, the higher is the risk of encountering blocks. (Remark: With NAV 5.0 SP1 – Indexes Views – this is void)

    1. Index Optimization

    If the index structure in insufficient usually transactions take much longer because "Index Scans" are performed instead of "Index Seeks". "Scanning" indexes – reading the leaf node level – is time consuming, and has a high risk of getting blocked, e.g. if a write transactions locks a record within the "to-be-scanned" range. Optimizing indexes speeds up transactions and reduced blocking conflicts.

    1. Process Optimization

    It is important to optimize the "length"/duration of processes and transactions in NAV. The longer a transaction takes, the more locks are engaged (lock escalation), the higher the risk of causing/encountering blocks. Also, the longer transactions take, the more system resources are occupied. Shorting transactions with a COMMIT – at the right time and place! – will release locks and resources.

    1. Increase Processing Speed

    The faster a transaction could be processed, the lower is the risk of encountering blocks. So speeding up the system in any way (CPU, RAM, Disks, Network, etc.) could be quite feasible, too. When it is about "Index Optimization" especially the design of the Clustered Index could have major impact on blocks! The following should give few examples:

    Example 1:

    Table 357 "Document Dimension" Clustered Index (Standard): Table ID, Document Type, Document No., Line No., Dimension Code

    Looking at the physical structure of the table, the records are actually stored in this way:

    Process A

    Table ID

    Document Type

    Document No.

    Line No.

    Dimension Code

    Process B

      

    36

    Order

    100

    0

    Dim1

      

      

    36

    Order

    100

    0

    Dim2

      

      

    36

    Order

    200

    0

    Dim1

      

      

    36

    Order

    200

    0

    Dim2

      

      

    36

      

      

    37

    Order

    100

    10000

    Dim3

      

      

    37

    Order

    100

    20000

    Dim4

      

      

    37

    Order

    200

    10000

    Dim3

      

      

    37

    Order

    200

    20000

    Dim4

      

      

    37