[HELP]Everyday performance issue

Comments

• kine Member Posts: 12,562

  2010-06-19

  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.

  0
• xina_man Member Posts: 116

  2010-07-07

  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!

  0
• davmac1 Member Posts: 1,283

  2010-07-07

  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

  David Machanick
  http://mibuso.com/blogs/davidmachanick/

  0
• stryk Member Posts: 645

  2010-07-07

  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

  0
• xina_man Member Posts: 116

  2010-07-09

  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

  0
• xina_man Member Posts: 116

  2010-07-09

  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

  0