?
Solved

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

Posted on 2011-10-04
17
Medium Priority
?
1,064 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:afflik1923
  • 9
  • 5
  • 3
17 Comments
 
LVL 21

Accepted Solution

by:
JestersGrind earned 856 total points
ID: 36909458
Run a profiler trace with the duration template.  Set the duration filter to something like 5000.  That's in milliseconds.  You will capture every query that takes longer than 5 seconds.  Look for the same query recurring frequently to get the most bang for your buck.  Take some of the long running queries that are not modifying data and run them in SSMS with include actual execution plan turned on.  The nice thing about SQL 2008 is that the execution plans will tell you if you have missing indexes.  Look for green text.  Right click on the execution plan and select the missing index option.  It will give you the script to create the index.  Change the name of the index and run it.  Repeat for each missing index that you find.

Greg

0
 
LVL 25

Assisted Solution

by:jogos
jogos earned 1144 total points
ID: 36909472
Look what is consuming the resources. Both in sql (trace) as on server CPU/memory/disk.
0
 

Author Comment

by:afflik1923
ID: 36909838
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.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 21

Assisted Solution

by:JestersGrind
JestersGrind earned 856 total points
ID: 36910080
Actually in SQL Server 2008, there are DMVs that you can use to find missing indexes.  Check out the query in this link.  It will recommends indexes and writes the script to use.  The higher the impact, the more likely the query will improve performance.

http://sqlserverpedia.com/wiki/Find_Missing_Indexes

Greg

0
 
LVL 25

Assisted Solution

by:jogos
jogos earned 1144 total points
ID: 36915821
See no response on the sql trace, there you can see what is passing: a lot off small queries, some heavy ones or are there just small queries constantly waiting for locks to get freed.  It's perfectly possible to see what in that 50 seconds is happening in SQL.

With the DMV's you have a direct access to information about for instance missing indexes, memory usage, io_wait ..   http://www.mssqltips.com/sql-server-tip-category/31/dynamic-management-views-and-functions/.  But don't start creating new indexes when you don't know if they are responsible for a large number off seconds off your 50 seconds response time.

Newly installed
 -> what are the recovery models for the databases and are they appropriate for the backups you are taking. For instance no transactionlog backup can slow down access and finaly blows up your transaction log for db's with recovery model FULL.
-> could also be network configuration so you are wating on a network timeout before actualy going to get access to your database

It just can be everything so don't forget to look at eventviewer, server logs to find anything usefull.
0
 

Author Comment

by:afflik1923
ID: 36919559
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.
0
 

Author Comment

by:afflik1923
ID: 36919649
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.
0
 
LVL 21

Assisted Solution

by:JestersGrind
JestersGrind earned 856 total points
ID: 36919735
That's unfortunate that their application support isn't being very helpful.  I'm hesitant to suggest too much because I don't want you to violate any support agreements that you have with them.  Using profiler is a bad idea for someone not very familiar with SQL.  

Below is a query that you can run against the database that is having performance issues.  The query is taken from the link I posted above.  It will show a list of potential indexes that can be applied in order of the positive impact that they should have on your system.  Make sure that the database has been running for a while before you run it because SQL Server resets the statistics that this reads when it is restarted.

The nice thing about this query is that it gives you the code to add the index in question.  Adding indexes is not changing data, but you should probably check with Sage to make sure they are ok with you adding indexes to their database.

Greg


SELECT  sys.objects.name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact
,  'CREATE NONCLUSTERED INDEX ix_IndexName ON ' + sys.objects.name COLLATE DATABASE_DEFAULT + ' ( ' + IsNull(mid.equality_columns, '') + CASE WHEN mid.inequality_columns IS NULL 
                THEN ''  
    ELSE CASE WHEN mid.equality_columns IS NULL 
                    THEN ''  
        ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL 
                THEN ''  
    ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS CreateIndexStatement
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns 
    FROM sys.dm_db_missing_index_group_stats AS migs 
            INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle 
            INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle AND mid.database_id = DB_ID() 
            INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID 
    WHERE     (migs.group_handle IN 
        ( 
        SELECT     TOP (500) group_handle 
            FROM          sys.dm_db_missing_index_group_stats WITH (nolock) 
            ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))  
        AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1 
    ORDER BY 2 DESC , 3 DESC

Open in new window

0
 
LVL 25

Assisted Solution

by:jogos
jogos earned 1144 total points
ID: 36920735
Checked recovery-model (exec sp_helpdb) and backup-schedule yet?

Again there will surely given indexes that will improve your performance. But first find where 80% off that 50 seconds response time is wasted.


Profiler trace http://msdn.microsoft.com/en-us/library/ms187929.aspx.  And a very short  introduction on how to find where 80% of the time is used.

The thing here is to launch a request from somewhere (a), see when it's comming into your database (b) ('started evends'), what it's doing there and what resources sql needed for that(c) (completed events) and when the result is arives to the enduser (d)

Long time from a to b -> timeout on network
b to c -> db-related problem, so see the numbers in the completed events , many reads, many writes, high CPU or just long duration . Low numbers but long duration -> could be locks so try to see what else is busy and restart with monitoring also lock-events. High numbers on 'reads' -> now it could be that index-tuning can be helpfull
Or just could be 'a lot' off low impact queries.

c to d: what is the process 'rendering' the result.  
0
 

Author Comment

by:afflik1923
ID: 36923526
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?
0
 

Author Comment

by:afflik1923
ID: 36923544
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
0
 
LVL 25

Assisted Solution

by:jogos
jogos earned 1144 total points
ID: 36923807
Nobody can help you more if you can't help us at least with where most of the time is spend to a-b, b-c or c-d?
Why did the SAGE guy's say it could be DNS?

Recovery model/Backup: please check if in management studio in a sql window you execute
exec sp_helpdb
you will see for each database a row returned somewhere there is mentioned 'FULL' or 'SIMPLE'. When 'FULL' there must be a complete backup and and at least one transaction log backup.

You think backup is set every night. Who knows? Who checks state?
0
 

Author Comment

by:afflik1923
ID: 36928001
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)
0
 
LVL 25

Expert Comment

by:jogos
ID: 36929066
" 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).
0
 

Author Comment

by:afflik1923
ID: 36929667
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.
0
 

Author Comment

by:afflik1923
ID: 36932014
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

0
 

Author Closing Comment

by:afflik1923
ID: 37267421
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
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question