How to improve the performance of Track-It! 11.xx

Knowledge Article

How to improve the performance of Track-It! 11.xx
How to improve the performance of Track-It! 11.xx
Track-It!
Track-It!
Track-It! 11.xx
What can I do to help improve the performance of the Track-It! Technician Client application?
NOTE:
  • Make sure you have the applied the latest update for Track-It!, the most recent version out there being 11.4 SP2. Many times performance improvements are made to new releases to make the product as efficient as possible. Feel free to open a support ticket for assistance with Upgrading Track-It!.
  • If running Technician Client on Windows 8/8.1, try stopping and then re-starting the Base Filtering Engine service as this has been known to improve the performance of the application.

Track-It! Hardware
 
  • The Track-It! application can be very SQL intensive at times depending on the number of connected users and the amount of activity. Therefore, it is very important to make sure that the database server is not overburdened.
  • If Track-It! server and its database are installed on the same machine, a multi-core CPU will help with parallel processing.
  • Ideally a physical database server should be used instead of a virtual machine. There are no specific issues related to a virtual machine environment, however with a virtual environment a drop in performance will be noticed compared to an actual server. This is because with virtual machines, the processor, drives, memory, bus speed, and other components are being shared, and cannot be dedicated to one specific virtual session.

    Additional information on using virtual servers with Track-It! can be found here:

    Running Track-It! on virtual servers
  • Check the free space on all partitions of the database server. When a partition is over 95% full, performance is severely degraded.
  • When running SQL Server, check the Autogrowth increment (1 MB by default) on the Data File, especially when the database has grown to over 2 GB in size. If a data file has grown to 5 GB, with a 1 MB increment for example, the file will be scattered across the hard drive in 5000 pieces. Such severe fragmentation will have an impact on performance as the data file grows larger and larger. Changing the growth increment to 10% or more is recommended. However, to fix the existing fragmentation the SQL service must be stopped, and defrag must be run on the drive where the data file resides. Simply changing the growth increment by itself will not change the fact that the file is already severely fragmented, and if the SQL service is not stopped the defrag process will be unable to defragment the files.
  • When using a seemingly strong server, Track-It! could still perform relatively poorly if there are many other databases and/or applications using the same machine. It is better to have Track-It! installed on a strong desktop class machine than to have it installed on a very strong server that is hosting several databases and applications.
  • Ideally the Track-It! database and application server should be local to the technicians that will be accessing it. If the database server is in another location across a WAN link for example, performance will undoubtedly suffer. If you have technicians in remote locations, consider publishing the Technician Client on a local Citrix server or have them run the application through Terminal Services.

    Information on using Track-It! with Citrix can be found here:

    How to setup Citrix to work with Track-It!

Audits and Merging

If Scheduled Audits are being run during the day, the process that merges the resulting Audit data into Inventory can be very CPU intensive. The same applies to running the Audit Merge process automatically. In either case, while the Audit Merge process is running, the SQL server's CPU usage can run higher than normal, which could greatly affect the Technician Client's software's performance.

To check to see whether Scheduled Audits or Audit Merges may be contributing to the slow performance, check the following:
 
  1. In Technician Client click Tools -> Administration Console.
  2. Under Configuration -> Inventory -> Merging, click on Schedule.
  3. If it isn't absolutely necessary to automatically merge Audit files, select the "Never Merge Automatically" option. If you must automatically merge Audit files, try having it scheduled to only occur once per day after business hours. It is recommended that this process not be run several times per day if not absolutely necessary.
  4. Next, click the Performance node above Schedule. If you are automatically merging Audit files, make sure the Maxiumum CPU Utilization is set to its default value of 25%.
  5. Next, in the Administration Console under Configuration -> Inventory -> Auditing, click on Scheduled Audits.
  6. If this process is enabled, make sure it is schedule to run after business hours.

Database Administration

There are several steps that can be taken to try and improve performance. However, before beginning please ensure that you have a working backup of the database.

Create a Backup:
  1. Have everyone close out of the application (it is also recommended that all Track-It! services are stopped on the Track-It! application server):

    NOTE: If you do not have SQL Server Enterprise Manager, SQL 2005 SQL Management Studio, or SQL 2008 SQL Management Studio and their query tools, download SQL Server 2005 Management Studio Express or SQL Server 2008 R2 Management Studio Express, depending on your installed version. The installation will provide these tools and will allow you to manage any MSDE, SQL Server 2008, 2005 Express, SQL Server 2005, or SQL Server 2000 database. If you download these tools, follow the backup steps under the "Track-It! running against SQL Server 2005/2008" section below.

    Track-It! running against SQL Server 2000:
  2. Open SQL Enterprise Manager on the existing Track-It! SQL 2000 Server.
  3. Expand Microsoft SQL Servers, SQL Server Group and the name of the SQL Server.
  4. Right click the Track-It! database and select All Tasks -> Backup Database...
  5. Click the Add button.
  6. Click the Browse button (...), browse to the location where the database backup will be saved, and give it a file name.
  7. Click Ok, and Ok again.
  8. Back at the SQL Server Backup window select Overwrite existing media.
  9. Click Ok to begin the backup process.
  10. Click Ok once the backup is complete.

Track-It! running against SQL Server 2005/2008:
 
  1. Open SQL Server Management Studio on the existing Track-It! SQL 2005 database server.
  2. Login as SA or an SA equivalent account.
  3. Expand the Databases folder.
  4. Right-click the database that you would like to backup and select Tasks -> Back Up.
  5. If a file path already appears in the "Destination" window at the bottom, take note of the path and name of the backup file that will be created and skip down to step i.

    If a file path does not exist in the window, click the Add button and continue with the steps below. 
  6. Click the ellipse button to the right of the "File name" field.
  7. In the "File name" field at the bottom of the "Locate Database Files" window, enter the desired backup file name. This will be the name of the backup file once the backup is complete. Take note of the path where the backup file will be created.
  8. Click Ok Locate Database Files window and then click Ok to close the "Select Backup Destination" window.
  9. Click Ok to begin the backup.
  10. Click Ok once the backup is completed successfully.

Setting SQL Server Maximum Server Memory

There are several different methods for determining the Maximum Server Memory that you should set for SQL server, however, the most common and simplest method is explained below.

NOTE: The following is only a guideline for determining the recommended Maximum server memory that should be set for SQL server. The only way to know for sure whether this value works for you is to use Windows Performance Monitor to analyze the performance of the SQL Server Buffer Manager and the server's available memory.
 
  1. Start with the total system memory (ex. 16 GB).
  2. Subtract 2 GB for the operating system (ex. 16 - 2 = 14 GB).
  3. For a 64-bit OS subtract an additional 1 GB for the SQL Server worker threads (Subtract 256 MB if running on a 32-bit server). (ex. 14 - 1 = 13 GB) 
  4. Finally, subtract at least 1 GB of memory for other programs installed on the SQL server such as antivirus (ex. 13 - 1 (min) = 12 GB).
  5. Enter the remaining amount of memory (in MB) into the SQL Server properties, under the Memory page's Maximum server memory field (ex. 12,000 MB).

Rebuild the database indexes and update statistics:

NOTE: It is recommended that this procedure be run at a slower time of the day, or even after hours since the re-building of the indexes could take several minutes to complete.
 
  1. Download the file TIA03326.zip from the Attachments section at the foot of this article. It contains a SQL query that can be used to re-build the indexes of all Track-It! tables whose index fragmentation is over 30%, in addition to updating the statistics of all tables in the database.
  2. Extract the file in the zip to the Track-It! SQL server.
  3. Run SQL Server Query Analyzer (SQL 2000) or SQL Server Management Studio (SQL 2005, 2008, or 2012).

    NOTE: If you do not have SQL Server Enterprise Manager, or SQL Server Management Studio and their query tools, download SQL Server 2005 Management Studio Express or SQL Server 2008 R2 Management Studio Express, depending on your installed version. The installation will provide these tools and will allow you to manage any MSDE, SQL Server 2008, 2005 Express, SQL Server 2005, or SQL Server 2000 database.
  4. Login as an SA equivalent account and open a new query window.
  5. Copy the SQL script from the file that was extracted earlier into the new query window.
  6. Make sure the correct Track-It! database is selected at the top, and then press F5 or click the play button (SQL 2000) or "Execute" button in SQL 2005/2008) at the top to execute the script.
  7. Once the script completes, which could take several minutes, close the query tool.

Backup the SQL Transaction log (Full Recovery Model):
  1. On the SQL Server, or a machine that has the database administration tools installed, start SQL Server Management Studio.
  2. Connect to the Track-It! database server and login with an SA equivalent account.
  3. Right-click the database and select Tasks -> Back Up.
  4. Take note of the recovery model of the database. If it is set to FULL or Bulk-Logged, select "Transaction Log" for the Backup Type. If the recovery model of the database is set to SIMPLE, this will not be available.
  5. Click Ok to begin the transaction log backup.
    NOTE: A full backup must have been done prior to backing up the transaction log, or the backup will fail.
  6. Once the backup is complete, click Ok.
Track-It!
Track-It!
Track-It! 11.xx
Track-It!
Track-It!
Track-It! 11.xx
Attachment(s):
TIA03326.zip