Performance Tweaks

Like all software, PTS 5 is not immune to occasional temporary performance issues.

Symptoms

Various functions of the PTS application take an unsatisfactory amount of time to load or to process.

Common causes

1. The database has grown large and the data has become fragmented.
2. Printer settings are not optimal.
3. Prescriptions are not being cleared from users homepages and the server cannot cope with the large volume of them.
5. Other software on a shared server is hogging resources.
6. An unoptimised or expensive third party query is hitting the PTS database.
7. A Power BI (or similar) report is hammering the database server with network traffic.
8. The server/s only meet the bare minimum hardware requirements.
9. PTS is trying to reach tmsinsight.com for support contract and software licensing notifications, but your PTS server has no access to the internet.
10. You have used PTS for a long time, and have lots of prescriptions in the database that you don't need any more.
11. Server anti-virus software is not configured properly.

Solutions

1. Run the Database Maintenance Script. In addition, performance can be degraded if the physical file size of the database does not fit entirely in system memory. As a very high transaction database we recommend PTS's database be set to a simple recovery model (assuming backups are handled in another way), and you can also consider shrinking it to reduce its file size.
2. Turn off bi-directional support. This is a setting under the Ports tab in the printer properties of each of your label printers. Similarly, the booking in screen can freeze if printing is being blocked by security, e.g. a service account being deactivated.
   Please do not underestimate the performance impact of bi-directional support even if you have never heard of it before
3. Consider whether your prescription types are set up correctly so that they clear automatically when finished. Also see how to clear lots of prescriptions at once.
   We very frequently see PTS configured in such a way that prescription types are marked to require collection, but "collection" is never actually recorded. If and when a user view is configured to return completed, but uncollected prescriptions, this results in PTS continually trying to load a list of ever growing uncollected prescriptions until eventually the server cannot cope with the load. See this page for more.
4. Use a resource monitor or the SQL performance dashboard to identify the application at fault and then contact the manufacturer.
5. Use SQL performance dashboard to identify the expensive query and have IT or the manufacturer optimise it and/or reduce its frequency.
   This issue seems to be becoming more common, please test your queries for performance at scale before rollout
6. This issue in particular seems to slow down Booking In, and other database inserts. While we encourage the use of external analysis tools, be sure to monitor and minimise the impact on network traffic on the PTS server/s.
7. Consider upgrading your server hardware to the recommended PTS 5 specification.
8. In Setup > Application Settings, locate the "skip checking if PTS is online" setting (OFFLINE) and set to Yes. This tells PTS not to attempt any remote connections.
9. To cull old prescriptions from the database, we have a SQL script available here that will delete prescriptions that belong to a deactivated prescription type. This will also allow you to subsequently delete the old prescription type entirely.
10. Third party anti-virus software tends to consume a lot of resources in general - some more than others - and you should consider whether your server is specced properly to handle that in addition to PTS's hardware requirements (PTS's stated minimum spec assumes that Windows Defender is in use). Furthermore the following folders and processes should be excluded:

%SystemRoot%\IIS Temporary Compressed Files
%SystemDrive%\inetpub\temp\IIS Temporary Compressed Files
%SystemDrive%\inetpub\temp\ASP Compiled Templates
%systemDrive%\inetpub\logs
%systemDrive%\inetpub\wwwroot

%SystemRoot%\system32\inetsrv\w3wp.exe
%SystemRoot%\SysWOW64\inetsrv\w3wp.exe
%SystemDrive%\PHP5433\php-cgi.exe

SQL Query Stats Script

A DBA might find this useful for tracking down troublesome or expensive queries.

WITH QueryStats AS (
    SELECT 
        qs.sql_handle,
        qs.plan_handle,
        qs.creation_time,
        qs.last_execution_time,
        qs.execution_count,
        qs.total_worker_time / 1000 AS TotalCPUTimeMS, -- CPU time in ms
        qs.total_elapsed_time / 1000 AS TotalElapsedTimeMS, -- Elapsed time in ms
        qs.total_logical_reads AS TotalLogicalReads,
        qs.total_physical_reads AS TotalPhysicalReads,
        qs.total_logical_writes AS TotalLogicalWrites,
        qs.max_worker_time / 1000 AS MaxCPUTimeMS,
        qs.max_elapsed_time / 1000 AS MaxElapsedTimeMS,
        qs.max_logical_reads AS MaxLogicalReads,
        qs.max_physical_reads AS MaxPhysicalReads,
        qs.max_logical_writes AS MaxLogicalWrites
    FROM sys.dm_exec_query_stats qs
)
SELECT 
    qs.execution_count,
    qs.TotalCPUTimeMS,
    qs.TotalElapsedTimeMS,
    qs.TotalLogicalReads,
    qs.TotalPhysicalReads,
    qs.TotalLogicalWrites,
    qs.MaxCPUTimeMS,
    qs.MaxElapsedTimeMS,
    qs.MaxLogicalReads,
    qs.MaxPhysicalReads,
    qs.MaxLogicalWrites,
    st.text AS QueryText,
    DB_NAME(er.database_id) AS DatabaseName,
    sp.program_name AS ApplicationName,
    sp.host_name AS HostName,
    sp.login_name AS LoginName,
    qp.query_plan AS QueryPlanXML,
    qs.creation_time AS CreationTime,
    qs.last_execution_time AS LastExecutionTime
FROM QueryStats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
LEFT JOIN sys.dm_exec_requests er
    ON er.plan_handle = qs.plan_handle
LEFT JOIN sys.dm_exec_sessions sp
    ON sp.session_id = er.session_id
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.TotalCPUTimeMS DESC;