asked on

Severe performance issues on new server with MS SQL Server R2 Standard /Sage 200

Hi,

We have a network that we have just rolled out and we have installed Sage 200 which users MS SQL R2 Standard edition.

All seemed to go well although the users have reported Sage is VERY slow and I can confirm. Therefore if they flick between pages in the application, any page that required data can take about 50 seconds to load.
More or less unusable.

We have for moments having it running normal speed which is lovely then for no reason it goes back and in the last 18 hours we have not had a time when it run OK.

We have done lots of things like remote AV from the server and workstation, but we still get the issue when running SAGE directly on the server with a keyboard and mouse plugged in..

We have a guy from Sage with us now who is reasonably technical but not a deep knowledge of Sage so we are unsure where to do.

What could cause such performance issues. Note that the server is well spec'd with 24GB, SAS drives dual processer - brand new and no more then 10 users (only 4 of Sage).

How can we diagnose this problem. Is there logs we can run to see what is happening on each page change and why it takes so long.
Although not directly our responsibility as another company installed Sage as we look after the network we want to be certain we have everything solid.

Thanks in advance to anyone who can help

ASKER CERTIFIED SOLUTION

JestersGrind

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

jogos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

afflik1923

ASKER

OK will try. Note the the server resouces is nothing. Does not even go above 1% useage during the slowness. 24GB of memory is not consumed. There is still free memory.
But as much input as possible on what else I could try when I do so later on would be useful.

SOLUTION

JestersGrind

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

jogos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

afflik1923

ASKER

Bearing in mind I have no experiance of MSSQL (We are just taking responsibilty because the network is ours) can you point out how I run a

"profiler trace with the duration template"

Also if we are running the Sage applicaiton direclty on the server, could network aspects like DNS still be causing an issue.

afflik1923

ASKER

Also remember the actual applicaiton is a Sage application. WE did not build the datbaase so I imagine in my limited knowledge that side of things is best left to the Sage applicaiton.
The sage people who installed the application, as a test tried installing SQL Express as a test in case soemthing had gone wrong but found the same performance issues and they say soemthing wrong with the network, but I Cannot see what when even locally run the same issues are present.

SOLUTION

JestersGrind

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

jogos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

afflik1923

ASKER

OK thanks so far.
"Checked recovery-model (exec sp_helpdb) and backup-schedule yet? "
I think the backup scheuld was set to backup each day. Don't know what recovery model is. Looks like this might be beyond me without learning a lot just for this issue.

I will add a bit more background. BAscailly the other company performed the MSSQL isntallation (as they are the SAge specialists).
There was another application that needed to be installed that uses MSSQL on the same server, however tha makers of that software installed it directly and there were some necessary steps including installing:

"SQL Server 2005 backwards compatibility pack"

and some registry additions needed to be made.

I'm assuming there is nothing wrong with the actual database tables as they were working fine on the previous version of Sage from where they have been ported. I'm thinking at a more infrastructure level of MSSQL configuration issue.
However as I have little knowledge on MSSQL, it's making it tough.

So knowing that any more input? Or am I going ot have to learn very much about MSSQL to monitor where the bottle neck is?

afflik1923

ASKER

Also onte that we tried unplugging cables from network on the server and running Sage direclty. Still no joy.
If running Sage direclty on the server (which hosts the MSSQL) with no network available, is it possible that network settings on the server are affecting the DB?

The sage guys said our DNS settings coudl be causing it, but how could DNS be when I run it directly?

Also there was at first on one workstaion we removed the ESET Anti virus. Maybe by coincidence, but that workstation ran fine for 20 mins, and at that point we though we had solved, it however after 20 mins was slow again and so we were confused. We still removed ESET from server and workstation and it is not there now and still goes slow.

The speed is for example within the application if you move from say "Purchases" to "Invoice" and both of these pages show info from the DB it can take a minute to load sometimes, even with a small amount of data.
If you go to a screen which does not have data to load, it takea few seconds (still not instant)

Any more ideas welcomed

SOLUTION

jogos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

afflik1923

ASKER

OK thanks. I think because theSage guy told me he has set it to backup once a day. I guess really the Sage guy should be sorting this issue out, but as we just put the network in I feel responsible and it's out of my area, but I will digest all info so far and try when I have more time to read it all (weekend)

jogos

" theSage guy told me he has set it to backup once a day". Classical case off consulting ... and who is constantly checking on the good execution? Backup to where?

About the question I put my money on a network timeout ( but also not my area).

afflik1923

ASKER

So if it were a network timeout, would that mean the issue exists even if running locally on the server itself with the netwrok cable unattached?

It is likelty that the Sage application still goes through some networking protocol even the the SQL server is local?

AS for backup I will be checking once things have settled for sure.

afflik1923

ASKER

Can't get on a client yet but see the following below for server.

C:\Users\Administrator>ipconfig /all

Windows IP Configuration

   Host Name . . . . . . . . . . . . : SERVER-S01
   Primary Dns Suffix  . . . . . . . : serveruk.local
   Node Type . . . . . . . . . . . . : Mixed
   IP Routing Enabled. . . . . . . . : No
   WINS Proxy Enabled. . . . . . . . : No
   DNS Suffix Search List. . . . . . : serveruk.local

Ethernet adapter Local Area Connection 2:

   Connection-specific DNS Suffix  . :
   Description . . . . . . . . . . . : Broadcom BCM5709C NetXtreme II GigE (NDIS
 VBD Client) #33
   Physical Address. . . . . . . . . : 78-2B-CB-4F-3E-73
   DHCP Enabled. . . . . . . . . . . : No
   Autoconfiguration Enabled . . . . : Yes
   Link-local IPv6 Address . . . . . : fe80::cdcb:5a66:71cb:b68%13(Preferred)
   IPv4 Address. . . . . . . . . . . : 172.168.20.250(Preferred)
   Subnet Mask . . . . . . . . . . . : 255.255.255.0
   Default Gateway . . . . . . . . . : 172.168.20.1
   DHCPv6 IAID . . . . . . . . . . . : 309865419
   DHCPv6 Client DUID. . . . . . . . : 00-01-00-01-15-FC-54-FE-78-2B-CB-4F-3E-75

   DNS Servers . . . . . . . . . . . : 172.168.20.250
   NetBIOS over Tcpip. . . . . . . . : Enabled

Ethernet adapter Local Area Connection:

   Media State . . . . . . . . . . . : Media disconnected
   Connection-specific DNS Suffix  . :
   Description . . . . . . . . . . . : Broadcom BCM5709C NetXtreme II GigE (NDIS
 VBD Client) #31
   Physical Address. . . . . . . . . : 78-2B-CB-4F-3E-75
   DHCP Enabled. . . . . . . . . . . : No
   Autoconfiguration Enabled . . . . : Yes

Tunnel adapter isatap.{D6304D8A-3A60-421A-B5F4-1D0F371AF79C}:

   Media State . . . . . . . . . . . : Media disconnected
   Connection-specific DNS Suffix  . :
   Description . . . . . . . . . . . : Microsoft ISATAP Adapter
   Physical Address. . . . . . . . . : 00-00-00-00-00-00-00-E0
   DHCP Enabled. . . . . . . . . . . : No
   Autoconfiguration Enabled . . . . : Yes

Tunnel adapter isatap.{103A7BC3-83A1-4111-B709-90D0C8DB72D2}:

   Media State . . . . . . . . . . . : Media disconnected
   Connection-specific DNS Suffix  . :
   Description . . . . . . . . . . . : Microsoft ISATAP Adapter #2
   Physical Address. . . . . . . . . : 00-00-00-00-00-00-00-E0
   DHCP Enabled. . . . . . . . . . . : No
   Autoconfiguration Enabled . . . . : Yes

Tunnel adapter Local Area Connection* 11:

   Media State . . . . . . . . . . . : Media disconnected
   Connection-specific DNS Suffix  . :
   Description . . . . . . . . . . . : Teredo Tunneling Pseudo-Interface
   Physical Address. . . . . . . . . : 00-00-00-00-00-00-00-E0
   DHCP Enabled. . . . . . . . . . . : No
   Autoconfiguration Enabled . . . . : Yes

Open in new window

afflik1923

ASKER

Thanks for all the input. In the end this was identified as an issue with the sage install due to a missing index table.
Many thanks