TeleScope Web Home Page Not Loading After Login

Overview

The TSWeb home page sometimes may not load even after providing valid credentials and the page may show an error:

Service Unavailable
HTTP Error 503. The service is unavailable.

This can also happen with SSO configured. This happens due to excessive CPU load generated by the SQL Server database rendering the server unable to serve HTML pages.

Solution

If you're an OnDemand customer please raise a support ticket to have the problem rectified. The rest of the article can be followed for On-premise installations.

Pre-requisites

1. SQL Server Management Studio (SSMS)

<supportagent>

1. Information about the hosted environments of our customers is available on this page.
2. Access credentials for the instances can be obtained by raising a Task(Type: Task) on the ACLTOPS page requesting access to NorthPlains Production AD.

</supportagent>

Diagnosis

This situation can happen in case there are long-running queries running on the editorial table, especially if you have custom columns added to the table. Follow the steps below to discover similar queries.

1. Open Activity Monitor in SSMS to observe CPU utilization for SQL Server. If the Processor Time window in the Overview section shows a load nearing 100%, then the system is stuck with some long-running queries.
2. Click on the Recent Expensive Queries tab which should reveal the running queries. Check if they are running on the editorial table.

<supportagent>

Log in to the appropriate database instance depending on the OnDemand customer and you can find out the server CPU load or connect to the database to find out the stuck queries. If you don't have access to the database then raise a ticket(escalation path on this page) of type Saas Request on the PLNP board to inquire about any long-running queries on the database, especially on the editorial table.

If the Remote Desktop Protocol(RDP) session is not connecting due to excessive load, raise a ticket of type Saas Request as described above. Alerts are set up for high load situations and they might have more information as the alerts trigger.

</supportagent>

Steps To Fix

1. Check if you have a similar issue in development or any low-impact environment, and perform the following steps in that environment before applying these to production.
2. Select Recent Expensive Queries tab in Activity Monitor.
3. Right-click on any long-running query on the editorial table and select Edit Query Text and copy it.
4. Right-click on the query again, and select Show Execution Plan. The Execution Plan tab will have recommendations for any indexes you need to create on the editorial table as shown here:

   /*
   Missing Index Details from ExecutionPlan1.sqlplan
   The Query Processor estimates that implementing the following index could improve the query cost by 94.4101%.
   */
   
   /*
   USE [prod_db]
   GO
   CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
   ON [dbo].[editorial] ([column1], [column2])
   
   GO
   */

   Copy the create query, and replace [<Name of Missing Index, sysname,>] with a unique string of your choice that would be the name of the index.
5. Observe the long-running query copied earlier and evaluate if it is safe to kill it. Kill any similar queries to release locks on the locked tables before creating indexes.
6. Create the indexes as suggested.
7. Check from the Overview tab in Activity Monitor that load has dropped from peak levels.
8. Test the development environment to check if TSWeb is loading after logging in. If it works, apply the above changes to the production environment.
9. If the above steps don't work, please raise a ticket with support.

<supportagent>

1. Share information with the customer about the kind of query(obtained from the Diagnosis steps) that is running and check with the customer if it's safe to kill the query.
2. If you get approval in the above step, create a ticket of type Saas Request on PLNP as described above on this page to request to kill the long-running queries and create any suggested indexes in SSMS. The steps to do so are described above. Inform the customer when the actions in this step are done and you have confirmation and have verified that TSWeb is working as expected.
3. If the customer denies, then inquire from SaaS if they have an alert for when the load drops and use it to inform the customer when they should try accessing TSWeb again. If there is no such alert then keep checking periodically and inform the customer when the load drops and TSWeb is working as expected.

</supportagent>

Testing

Once the fixes have been applied, please reload the TeleScope login page and login with your credentials and the home page should load.