SQL NAV 2009 SP1 Issue

dareme76

Hi,

I would appreciate some insight to the scenario below.
A client is experiencing performance issues in NAV 2009 SP1 Build 32029 for the RTC Calc and Post Vat Settlement.

The environment is as follows:
1. O.S. Windows server 2008 R2 Standard Server 64 Bit
2. SQL 2008 R2 Standard
3. RAM 32gb (SQL limited to use 27gb)
4. Data Disks RAID 10, Operating system RAID 1.
5. No Virualisation
6. Two Processors (recent)
7. Dedicated server hosting only SQL Server and NST middle Tier
8. Size of database 30gb
9. SQL Optimisation routines are scheduled regularly
10. The Calv VAT has not been touched thus is standard nav functionality.

Strangely enough running the Calv VAT without posting on Classic Client takes 4 min, whilst using RTC taken 30 min.

Yesterday evening we restared the server and re-ran the Calc vat from RTC and it took 5 minutes which is good BUT this morning its taking 15 minutes.

Could the issue be with the NAV Service Tier?

I thank you for your comments.
Regards,
Darren

stryk

Well, I'm afraid there's a gazillion of reasons for degraded performance ... thus, there are alo plenty of possible solutions ...
Without any specific details about the problems, it's sher impossible to give any advice.

For a start, maybe you could check ou this one here: http://dynamicsuser.net/blogs/stryk/default.aspx

davmac1

Since you are running the service tier on the same server as the SQL Server and it runs much faster after you reboot, then I recommend you run the task manager first to look at your memory usage and CPU usage. Then check the resource monitor to see how the system resources are being used.
Perfmon also has a variety of performance measures.

If you can, move the service tier to a different server. It is usually best to have SQL server with its own dedicated server unless you have a low-volume environment.
The service tier is doing all the work all the Classic clients do except for the presentation layer, so by having the service tier on the same server as the SQL Server means you are adding most of the client load - CPU and RAM - to the same Windows Server the SQL Server is using.

dareme76

Good Afternoon,

I would like to thank you for your comments.

I have continued trying to trace the issue and the following is happening.

I run the Calc VAt statement (not posting) for period 01/07/2012 - 30/09/2012 1st time, 82000 records processed in 4.5 minutes.

I run the Calc VAt statement (not posting) for period 01/07/2012 - 30/09/2012 2nd time, 82000 records processed in 9 minutes.

I run the Calc VAt statement (not posting) for period 01/07/2012 - 30/09/2012 3rd time, 82000 records processed in 18 minutes.

BUT when i restart the Navision Service Tier and run the Calc Vat Report again the timing goes down to 4.5 minutes.

My hunch is that the issue is with some setting with the Service Tier / VAT Report, namely it seems that the report is somehow storing the previous runs (not posting) till when the Service Tier is restarted from control pannel Services.msc.

Is there a way to force an Navision report to flush any previous calculations?
I would think of putting such a command in a If ISSERVICETIER Then.

Please not classic nav alc vat is really fast.

I thank you in advance.