[HELP]Everyday performance issue

xina_man
Member Posts: 116
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!
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!
0
Comments
-
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.0
-
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 -
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 usersDavid Machanick
http://mibuso.com/blogs/davidmachanick/0 -
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.htmlJörg A. Stryk (MVP - Dynamics NAV)
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool0 -
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
Thanks0 -
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 you0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions