NAV 4.0 SP3 Database is getting too big and slow

davmac1

How much RAM do you have on the server?

How are the clients accessing NAV? Is it from their own PCs or thru remote desktop?

Have you run any performance analysis on SQL Server? There are SQL Server supplied tools and Windows Server supplied tools.
Have you identified and isolated any specific slow running processes?

Has anybody made SQL Server changes to the database outside of Navision? If the keys in SQL Server do not match thos in Navision, that could cause major performance. problems.

cfeghali

davmac1 wrote:

How much RAM do you have on the server?

How are the clients accessing NAV? Is it from their own PCs or thru remote desktop?

Have you run any performance analysis on SQL Server? There are SQL Server supplied tools and Windows Server supplied tools.
Have you identified and isolated any specific slow running processes?

Has anybody made SQL Server changes to the database outside of Navision? If the keys in SQL Server do not match thos in Navision, that could cause major performance. problems.

- 16GB RAM
- All users access NAV on remote desktop.
- we've run SQL performance tools, and it shows a lot of different issues, some i listed above. bad indexes, indexes out of date, deadlocks and so on. we rebuild the indexes daily thou.
- no, all changes are done using the nav client.

I hope this helps and thank you all for your help and recommendations.

kriki

David Singleton wrote:

IN an SSD environment

:oops: Didn't notice the SSD....

Basically: what I said is correct, but it applies for HD and less for SSD.

Give this a run to see on what the server is waiting most of the time:

-- top wait stats
-- Isolate top waits for server instance since last restart or statistics clear
-- http://blog.sqlauthority.com/2011/02/01/sql-server-introduction-to-wait-stats-and-wait-types-wait-type-day-1-of-28/

WITH  Waits
        AS ( SELECT wait_type ,
                    wait_time_ms / 1000. AS wait_time_s ,
                    100. * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS pct ,
                                                              ROW_NUMBER() OVER ( ORDER BY wait_time_ms DESC ) AS rn
             FROM                                             sys.dm_os_wait_stats
             WHERE                                            wait_type NOT IN (
                                                              'CLR_SEMAPHORE',
                                                              'LAZYWRITER_SLEEP',
                                                              'RESOURCE_QUEUE',
                                                              'SLEEP_TASK',
                                                              'SLEEP_SYSTEMTASK',
                                                              'SQLTRACE_BUFFER_FLUSH',
                                                              'WAITFOR',
                                                              'LOGMGR_QUEUE',
                                                              'CHECKPOINT_QUEUE',
                                                              'REQUEST_FOR_DEADLOCK_SEARCH',
                                                              'XE_TIMER_EVENT',
                                                              'BROKER_TO_FLUSH',
                                                              'BROKER_TASK_STOP',
                                                              'CLR_MANUAL_EVENT',
                                                              'CLR_AUTO_EVENT',
                                                              'DISPATCHER_QUEUE_SEMAPHORE',
                                                              'FT_IFTS_SCHEDULER_IDLE_WAIT',
                                                              'XE_DISPATCHER_WAIT',
                                                              'XE_DISPATCHER_JOIN',
                                                              'SQLTRACE_INCREMENTAL_FLUSH_SLEEP' )
           )
  SELECT  W1.wait_type ,
          CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s ,
          CAST(W1.pct AS DECIMAL(12, 2)) AS pct ,
          CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
  FROM    Waits AS W1
          INNER JOIN Waits AS W2 ON W2.rn <= W1.rn
  GROUP BY W1.rn ,
          W1.wait_type ,
          W1.wait_time_s ,
          W1.pct
  HAVING  SUM(W2.pct) - W1.pct < 99
OPTION  ( RECOMPILE ) ; -- percentage threshold
GO

davmac1

Since all users are running using remote desktop, have you checked the perfomance and resource monitoring on the server hosting the sessions?
If the terminal server is having to swap active sessions out of RAM, then that could cause a lot of blocking problems.
I have also seen HP print drivers get replicated a huge number of times.
Make sure your terminal server is operating efficiently.
There are also some general guidelines on sessions per CPU and RAM per session plus base RAM.

How many sessions are you running on your server(s) and how much RAM?
Is the resource monitor showing any bottlenecks?

cfeghali

Terminal Servers are Windows 2003 SP2 VMs with 4GB ram. they have 20people connected in average per server.
I don't see anything unusual with the servers. they run very well.


Kriki, i will run your query and let you know the results.

thank you,
Cesar

cfeghali

that was quick, does this work?

ASYNC_NETWORK_IO 8477.09 35.49 35.49
PAGEIOLATCH_SH 7569.48 31.69 67.18
MSQL_XP 1863.36 7.80 74.98
CXPACKET 1366.52 5.72 80.71
IO_COMPLETION 1329.05 5.56 86.27
LCK_M_S 777.64 3.26 89.53
PAGEIOLATCH_EX 677.44 2.84 92.36
WRITELOG 429.50 1.80 94.16
LCK_M_RS_U 373.89 1.57 95.73
LATCH_EX 270.66 1.13 96.86
SOS_SCHEDULER_YIELD 244.72 1.02 97.88
SLEEP_BPOOL_FLUSH 239.72 1.00 98.89
LCK_M_IS 86.02 0.36 99.25

bbrown

cfeghali wrote:

Terminal Servers are Windows 2003 SP2 VMs with 4GB ram. they have 20people connected in average per server.
I don't see anything unusual with the servers. they run very well.


Kriki, i will run your query and let you know the results.

thank you,
Cesar

Take a look at the paging activity on your terminal servers. I suspect that with that many sessions and that little memory, that you have some fairly high paging activity. Which can have an impact on NAV performance.

cfeghali

there is a lot of spikes in when I look at Memory - Pages/s

is this the report from perfmon you mean?

in regards to the server, it's 32bit standard, wouldn't help to give more RAM.
We've had issues with the client on 64bit Windows 2008R2. Mostly from custom modules (dlls) and other things that we've added.

davmac1

You may need more terminal servers then.

kriki

Apart from the terminal servers, you might also look into this:

ASYNC_NETWORK_IO,PAGEIOLATCH_SH together make up +- 70% of waits.

The ASYNC_NETWORK_IO means network waits. Your terminal servers are running on VM's but how are they connected to the SQL server? Is your network fast enough? You need at least 1 Gbit network between them. Remember also that the network card(s) in the physical machine(s) on which your VM's are running are virtualized and divided between all VM's. It might also mean some network card of router is not functioning correctly.

The PAGEIOLATCH_SH means disk performance (and you have SSD's...). Check your tempDB (if I am correct, it is NOT on the SSD's but on a RAID5!) to see if this is slowing down everything. It might also mean your SSD's are malfunctioning (http://blog.sqlauthority.com/2011/02/09/sql-server-pageiolatch_dt-pageiolatch_ex-pageiolatch_kp-pageiolatch_sh-pageiolatch_up-wait-type-day-9-of-28/). I bet on temp-DB performance problems here.