KB -experience slow performance when you perform specific processes in Microsoft Dynamics GP

Independent troubleshooting

Service packs

• Make sure that the latest service pack for Microsoft SQL Server is installed. For more information about the latest service pack for Microsoft SQL Server that is compatible with Microsoft Dynamics GP, visit the following Microsoft Web site:                     https://mbs.microsoft.com/customersource/northamerica/GP/learning/documentation/system-requirements
• On the System Requirements page, select the version of Microsoft Dynamics GP that you are using in the Version box. Then, click Refresh List.
• Make sure that the latest service pack for Microsoft Dynamics GP is installed. For more information about the latest service pack, visit the following Microsoft Web site:
  https://mbs.microsoft.com/customersource/northamerica/GP/downloads/service-packs
  On the "Service Packs/Product Releases" page, select the version of Microsoft Dynamics GP that you are using in the Version box. Then, click Refresh List.
• For a consolidated view of resources for each Microsoft Dynamics GP version, visit the RESOURCE page. 

Posting problems

• If posting is slow or hanging, open the Dynamics.set file from the Dynamics GP code folder with Notepad.  Review the installed products to see if Payment Document Managment (2150) is installed.  This module is typically not installed on a U.S. installation and changes behavior in payment windows, so may cause performance issues.  Un-install this module if you are not using it. 
• If you experience performance issues when you post, run the following SELECT statement on the PJOURNAL table. Run the statement against all the company databases.

SELECT * FROM PJOURNAL

DELETE PJOURNAL

• If you are using Microsoft SQL Server Management Studio for Microsoft SQL Server (any version), click Start, point to Programs, point to Microsoft SQL Server 20XX (XX=your version), and then click SQL Server Management Studio.
• The PJOURNAL table is a temporary table and does not affect data.
• All users must exit Microsoft Dynamics GP before you run the DELETE statement.
• A PJOURNAL job is also created when you install Microsoft Business Solutions - Great Plains 7.5 and Microsoft Business Solutions - Great Plains 8.0. The PJOURNAL job must be turned on manually for the job to manage the PJOURNAL table so that the table does not grow larger and affect performance.
  
  For more information, click the following article number to view the article in the Microsoft Knowledge Base:
   
  862342 How to use Enterprise Manager to enable the Delete PJournal job that is created during the installation of Microsoft Dynamics GP
   

Printer settings that affect Microsoft Dynamics GP performance

Performance issues that occur when you open windows

1. Open the user preferences. To do this, follow these steps:
   • In Microsoft Dynamics GP 10.0 and later versions, click Microsoft Dynamics GP, and then click User Preferences.
2. Click AutoComplete.
3. Click to clear the Show AutoComplete Suggestions check box, and then click OK.
4. In Windows Explorer, delete the AutoCmpl.dat file and the AutoCmpl.idx file. These files are in the following folder:
   • In Microsoft Dynamics GP 10.0 and later versions, the files are in the following location:
     Document and Settings\username\Application Data\Microsoft Business Solutions\Microsoft Dynamics GP\dbname\
5. Repeat step 1 through step 5 for each user.

Performance issues that occur when you log on to Microsoft Dynamics GP

1. The location of the modified Reports.dic file and the modified Forms.dic file may affect the logon performance. If the modified dictionaries are on a network share, copy the dictionaries to the local Microsoft Dynamics GP folder, and then try to log on. To do this, follow these steps
2.  Certain SmartList reminders may cause logon issues, depending on the homepage role for that user. To verify the reminders for a user, use the following:
   • In Microsoft Dynamics GP 10.0 and later versions, click Microsoft Dynamics GP, and then click Reminders. Click Change Reminder Preferences, and then remove the reminders in the Custom Reminder section at the bottom of the window.
3. You may have shortcuts to network locations that are no longer mapped or available. Microsoft Dynamics GP will try to validate those locations. However, if they are not reachable, the process will time-out after several seconds.
   • In Microsoft Dynamics GP 10.0 and later versions, expand the Shortcuts folder in the Navigation pane on the left side of the Home Page. If you notice any unnecessary or invalid shortcuts, remove them.
   • You can also validate the links that are stored in the Shortcut Bar Master (SY01990) table. Run the following script in Query Analyzer or in SQL Server Management Studio:

     SELECT * FROM DYNAMICS..SY01990
     

     If you see any network paths in the ScbTargetStringOne column that are no longer valid, delete the associated shortcut. You can delete the shortcut in Microsoft Dynamics GP or from the table.
4. For Microsoft Dynamics GP performance, an OLE Notes path in the Dex.ini file that is local instead of on a network is recommended. If the OLE path has to be a network path, verify that it is a valid path and that there is good bandwidth.
5. Verify that the SQL database options AutoClose and AutoShrink are set to FALSE.
   • If you use SQL Server Management Studio, follow these steps:
     1. Click Start, point to All Programs, point to Microsoft SQL Server 2005 or Microsoft SQL Server 2008, and then click SQL Server Management Studio.
     2. In the Connect to Server window, follow these steps:
        1. In the Server Name box, type the name of the server that is running SQL Server.
        2. In the Authentication box, click SQL Authentication.
        3. In the Login box, type sa.
        4. In the Password box, type the password for the sa user, and then click Connect.
     3. In the Object Explorer window, under the SQL Server instance, expand Databases.
     4. Right-click the DYNAMICS database, and then click Properties.
     5. In the Database Properties window, click the Options page.
     6. Under the Automatic options, verify that both the Auto Close and the Auto Shrink fields are set to FALSE.
        
        If one of these fields is set to TRUE, click True and then click FALSE in the drop-down window.
     7. Click OK to save changes.
     8. Repeat steps d through g for all Dynamics GP company databases.
   • If you use SQL Enterprise Manager, follow these steps:
     1. Click Start, point to All Programs, point to Microsoft SQL Server 2000, and then click Enterprise Manager.
     2. Expand Microsoft SQL Servers, expand SQL Server Group, and then expand the name of the server that is running SQL Server.
     3. Expand Databases.
     4. Right-click the DYNAMICS database, and then click Properties.
     5. In the Properties window, click the Options tab.
     6. Under Settings, click to clear the Auto Close check box and the Auto Shrink check box.
     7. Click OK to save changes.
     8. Repeat steps d through g for all Dynamics GP company databases.
   • If you use the Support Administrator Console, follow these steps:
     1. Click Start, point to All Programs, point to Microsoft Support Administrator Console, and then click Support Administrator Console.
     2. In the Connect to SQL Server window, follow these steps:
        1. In the SQL Server box, type the name of the new server.
        2. In the Login Name box, type sa.
        3. In the Password box, type the password for the sa user, and then click OK.
     3. Copy the following script to the New Query 1 window:

        ALTER DATABASE DYNAMICS
        SET AUTO_SHRINK OFF, AUTO_CLOSE OFF
        

     4. Press F5 or click Execute to run the script.
     5. Run this script against each Dynamics GP company database by changing the DYNAMICS database name to the name of the company database.
6. Verify that the ODBC System DSN data source connection on each computer tests successfully for connectivity with the SQL Server holding the Dynamics GP or the Great Plains databases:
   1. Click Start, click Control Panel, click Administrative Tools, and then click Data Sources (ODBC).
   2. Click the System DSN tab, select the data source that is being used to start Microsoft Dynamics GP or Microsoft Business Solutions-Great Plains, and then click Configure.
   3. Verify that the Server name shows the correct SQL Server instance, and then click Next.
   4. Type the password for the sa login ID, and then click Next.
   5. Verify that the Change the default database to, Attach database filename, Use ANSI quoted identifiers, and Use ANSI nulls, paddings and warnings options are not selected, and then click Next.
   6. Make sure that none of the options in the next window are selected, and then click Finish.
   7. Click Test Data Source and verify that you receive the following message:
      TESTS COMPLETED SUCCESSFULLY!
   8. Click OK to exit all windows.

Virus scanner setup

• .cnk
  These files are used when service packs are installed. These files should not be in a directory for a significant time.
• .dic and .chm
  These files are the Help files for Microsoft Dynamics GP.
• .set
  These files are the start files. These files contain information about the products that are installed and where the products are located.
• .ini
  These files are the configuration files. These files hold information about which user logged on most recently, what data source is used, and the paths.
• .dat
  These files are ctree .dat files that are used with an SQL database.
• .idx
  These files are ctree index files that are used with an SQL database.
• .vba
  These files are used if there are Microsoft Visual Basic for Applications (VBA) modifications for Microsoft Dynamics GP.
• .log
  These files are used by a Dexsql.log file if you use a Dexsql.log file to troubleshoot an error message.

Performance problems that occur on the home page in Microsoft Dynamics GP 10.0 and later versions

1. On the home page, click the Customize this page link.
2. In the Mark content to display section, click to clear each area.
3. To apply the changes, click OK.
4. In the "Customize your home page" window, add items back to the home page. To do this, click to select the check box next to an item. To test how long the page takes to load, refresh the home page after you add each item.

• The Outlook integration
  Latency with the Microsoft Exchange server can cause performance problems on the home page in Microsoft Dynamics GP.
• The SmartList favorites in the Reminders area of the To Do home page section
  Reminders that are linked to the SmartList favorites that have many returned records may cause performance problems on the home page in Microsoft Dynamics GP. For example, this problem may occur if there are more than 1,000 returned records.

Technical support-assisted troubleshooting

1. What are the steps that let you reproduce the performance issue?
2. If you experience performance issues when you post, answer the following questions:
   • What module or modules are you posting?
   • How many transactions are in the batch?
   • How long does the posting process last?
   • How long did the posting process last before you began to experience the performance issue? Or, has the posting process always been slow?
3. Can you easily reproduce the performance issue, or does the performance issue occur randomly?
    
4. Can you reproduce the performance issue on all the computers?
5. Specifically, can you reproduce the performance issue when you are sitting directly at the computer that is running Microsoft SQL Server?
6. When you experience the performance issue, do you receive an error message? Or, does the process merely stop responding?
7. What additional products or third-party products do you use together with Microsoft Dynamics GP?
   
   
   
   Note Obtain this information from the Dynamics.set file on the computer that is experiencing the performance issue.
8. Are you using any customizations in Microsoft Dynamics GP?
    
9. Have any changes or problems occurred in the network? These changes and problems might include the following:
   • New hardware
   • New software such as antivirus software
   • Recent server crashes
10. Is the computer that is running SQL Server a dedicated server for Microsoft Dynamics GP? Are there any other processes that are running on the computer that is running SQL Server, such as integrations or replications?
11. How many users are logged on to the system when the performance issue occurs?
     
12. Are there any physical symptoms on the computer that is running SQL Server? For example, is processor usage at 100 percent? Is the processor light on?
     

Sources of additional troubleshooting information

The System Information tool

• The computer that is running SQL Server
• The client workstations
• The computer that is running Terminal Server

1. Click Start, click Run, type msinfo32, and then click OK.
2. Click File, and then click Save to save this information to a file.

Trace logs

DBCC Traceon (1204,3605 -1)

The SQLDIAG tool

The Application log

1. Click Start, point to Administrative Tools, and then click Event Viewer.
2. In Event Viewer, click Application, click Action, and then click Save Log File As.

Additional


For further information to help optimize performance please review the Performance White Paper:

https://mbs.microsoft.com/customersource/northamerica/GP/learning/documentation/white-papers/MDGP2010_WhitePaper_Performance

Also refer to this Blog article for Performance issues. 


Contact information