[HELP]Everyday performance issue

xina_manxina_man Member Posts: 116
edited 2010-07-09 in SQL Performance
Hello!

Everyday I face performance issues on Navision. I've read some posts about fragmentation and tried to look for it on the SQL Server Navision Database.
My major issues are on the Warehouse entries and Item ledger entries tables. Every single day most of all keys have fragmentations over 70% or 80%, and for what i've read that is way over normal.
To get Navision a bit faster I make table Optimization, on my Navision client, and right after that the fagmentation goes below 1%, but if I wait for 2 or 3 hours the fragmentation of those tables goes up to 50% again.

Is there anything I can do to get solve this?

Thank you in advance!

Comments

  • kinekine Member Posts: 12,562
    I am sure that if you have read all the posts, you know that the Optimize function in NAV doesn't help to much. You need to set your SQL jobs to maintain you DB healthy. And of course ooptimize the indexes, statistics etc.
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • xina_manxina_man Member Posts: 116
    Thank you for your reply!

    I took some time working on SQL Maintenance plans and now it is working for a while. There was no reorganize and rebuild index plans, but I've created it...
    My problem now is that users still complaining about performance.

    Right now these are the maintenance scheduled plans:
    Every day - Backup and organize indexes;
    Weekend - Backup and re-build indexes;

    Is there anything else I can do to get higher performances???
    Thank you!
  • davmac1davmac1 Member Posts: 1,283
    What are the specs of your SQL Server?
    Is it a dedicated server?
    32 or 64 bit
    RAM
    CPUs
    separate spindles for OS, log, data, etc.
    RAID type
    number of users
  • strykstryk Member Posts: 645
    Well, there are plenty of reasons for degraded performance, thus, there are plenty of options to fix this ... Please search MIBUSO for "SQL Performance" and you'll get gazillions of advices ...

    Regarding Index Maintenance:
    Check out this:
    http://dynamicsuser.net/blogs/stryk/archive/2010/05/20/directions-emea-2010-nav-sql-performance-indexes.aspx
    http://dynamicsuser.net/blogs/stryk/archive/2010/02/10/technical-airlift-2009-munich-nav-sql-performance-optimization-indexes.aspx

    To defrag indexes better than with standard MP you should look into "Books Online", about "sys.dm_db_index_physical_stats", Example D.:
    -- Ensure a USE <databasename> statement has been executed first.
    SET NOCOUNT ON;
    DECLARE @objectid int;
    DECLARE @indexid int;
    DECLARE @partitioncount bigint;
    DECLARE @schemaname nvarchar(130); 
    DECLARE @objectname nvarchar(130); 
    DECLARE @indexname nvarchar(130); 
    DECLARE @partitionnum bigint;
    DECLARE @partitions bigint;
    DECLARE @frag float;
    DECLARE @command nvarchar(4000); 
    -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
    -- and convert object and index IDs to names.
    SELECT
        object_id AS objectid,
        index_id AS indexid,
        partition_number AS partitionnum,
        avg_fragmentation_in_percent AS frag
    INTO #work_to_do
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
    WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
    
    -- Declare the cursor for the list of partitions to be processed.
    DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
    
    -- Open the cursor.
    OPEN partitions;
    
    -- Loop through the partitions.
    WHILE (1=1)
        BEGIN;
            FETCH NEXT
               FROM partitions
               INTO @objectid, @indexid, @partitionnum, @frag;
            IF @@FETCH_STATUS < 0 BREAK;
            SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
            FROM sys.objects AS o
            JOIN sys.schemas as s ON s.schema_id = o.schema_id
            WHERE o.object_id = @objectid;
            SELECT @indexname = QUOTENAME(name)
            FROM sys.indexes
            WHERE  object_id = @objectid AND index_id = @indexid;
            SELECT @partitioncount = count (*)
            FROM sys.partitions
            WHERE object_id = @objectid AND index_id = @indexid;
    
    -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
            IF @frag < 30.0
                SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
            IF @frag >= 30.0
                SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
            IF @partitioncount > 1
                SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
            EXEC (@command);
            PRINT N'Executed: ' + @command;
        END;
    
    -- Close and deallocate the cursor.
    CLOSE partitions;
    DEALLOCATE partitions;
    
    -- Drop the temporary table.
    DROP TABLE #work_to_do;
    GO
    

    This script is not brilliant, but better than MP. Last but not least some hint: http://www.stryk.info/english/toolbox.html
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • xina_manxina_man Member Posts: 116
    davmac1 wrote:
    What are the specs of your SQL Server?
    Is it a dedicated server?
    32 or 64 bit
    RAM
    CPUs
    separate spindles for OS, log, data, etc.
    RAID type
    number of users

    Hello

    The Server Specs are:

    Xeon CPU 4 5160 @ 3000Ghz
    64 bit
    8 GB RAM
    Windows Server 2003 x64
    log and data are in same path.
    average of 45 online users from 8AM until 20PM

    Thanks
  • xina_manxina_man Member Posts: 116
    stryk wrote:
    Well, there are plenty of reasons for degraded performance, thus, there are plenty of options to fix this ... Please search MIBUSO for "SQL Performance" and you'll get gazillions of advices ...

    Thank you stryk,

    I will look at this and keep you guys updated.

    Thank you
Sign In or Register to comment.