[HELP]Everyday performance issue

xina_man

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!

kine

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.

xina_man

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!

davmac1

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

stryk

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

xina_man

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_man

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