Performance issues or database deadlock errors in TIBCO Spotfire when interacting with the TIBCO Spotfire Server application database

Performance issues or database deadlock errors in TIBCO Spotfire when interacting with the TIBCO Spotfire Server application database

When using TIBCO Spotfire 7.9 or higher configured to use a Microsoft SQL Server as the application database, you may experience various performance issues or database deadlock errors in TIBCO Spotfire when performing actions which interact with the TIBCO Spotfire Server application database. This may manifest in a few ways, but some common scenarios are:

• Slowness when opening or using the Library browser in the TIBCO Spotfire Analyst client or web interface
• Slowness when opening and using the Information Designer, Administration Manager, or Library Administration tools from the TIBCO Spotfire Analyst client
• Long delays in startup of the TIBCO Spotfire Server
• Continuous database deadlock errors in the logs preventing users from opening analysis files stored in the library
• Inability to save analysis files to the library

The effects of this issue are seen most on installations with large Spotfire libraries (large numbers or users, groups, files stored in the database)

While there may be many other contributors to poor performance (insufficient resources, incorrect configuration, network bandwidth or latency issues, etc), this article describes one known defect originally introduced in version 7.9 which causes the connection-properties settings for the Spotfire database data source in the TIBCO Spotfire Server configuration to not be used. These settings from the configuration.xml are ignored:

<connection-properties>
        ...
        <connection-property>
          <key>sendStringParametersAsUnicode</key>
          <value>false</value>
        </connection-property>
        <connection-property>
          <key>StringInputParameterType</key>
          <value>varchar</value>
        </connection-property>
      </connection-properties>

Most Java drivers pass string parameters to SQL Server as Unicode, by default. The problem here is, with that setting ignored, the Unicode parameters reference the VARCHAR key columns in an index, and the SQL Server engine will not use the appropriate index for query resolution, thereby increasing the unwanted table scans.

When these database connection properties 'StringInputParameterType=varchar' and sendStringParametersAsUnicode=false' are ignored, this can be seen in server.log TRACE entries like below, where it says "properties={}" which means the properties are empty (whereas the expected result is that the configured properties are listed towards the end after "properties=").

TRACE 2018-08-14T00:47:00,298-0700 [*Watchdog*] util.sql.PoolingDataSource: Created new database connection for data source server.default[driverClass=tibcosoftwareinc.jdbc.sqlserver.SQLServerDriver, url='jdbc:tibcosoftwareinc:sqlserver://tpawinappp027.enterprisenet.org:1433;DatabaseName=Spotfire_76_intdev', username='sp712int', password=[NOT SHOWN], kerberosLoginContextName=null, refreshTGT=false, initialized=true, destroyed=false, active=true, size=8, idleCount=1, activeCount=7, mostActiveCount=7, errorCount=0, timeoutCount=0, minConnections=5, maxConnections=60, connectionTimeout=6000, poolingScheme=WAIT, loginTimeout=40, autoCommit=true, readOnly=false, mBeanEnabled=true, properties={}]

To resolve:

If using Spotfire versions 7.11 or 10.0, apply TIBCO Spotfire Server hotfix TSS 7.11 HF-011 or TSS 10.0 HF-001 respectively. See List of hotfixes for TIBCO Spotfire Server.

Otherwise, the database connection properties need to be added to the bootstrap.xml. Follow the below instructions:

1. Log on to the TIBCO Spotfire Server machine as an Administrator
2. Take a backup of the existing bootstrap.xml from "<installation dir>\tomcat\webapps\spotfire\WEB-INF"
3. Launch command prompt as an Administrator and cd to the folder "<installation dir>\tomcat\bin"
4. Update the bootstrap by executing the following command, depending on your operating system (please refer to the screenshot for reference):

For Windows:

<installation dir>\tomcat\bin>config.bat update-bootstrap -CsendStringParametersAsUnicode=false -CStringInputParameterType=varchar

For Linux:

<installation dir>/tomcat/bin>./config.sh update-bootstrap -CsendStringParametersAsUnicode=false -CStringInputParameterType=varchar

Example screenshot:  


5. Once the bootstrap has been successfully updated, you can verify the change by opening the bootstrap.xml has the following properties:

connection-properties>
      <connection-property>
        <key>StringInputParameterType</key>
        <value>varchar</value>
      </connection-property>
      <connection-property>
        <key>sendStringParametersAsUnicode</key>
        <value>false</value>
      </connection-property>
    </connection-properties>

6. Restart the TIBCO Spotfire Server service

After updating the bootstrap and restarting the Spotfire Server, check if the same performance issues remain. If you see any further issues then please open a support case with TIBCO Support via https://support.tibco.com for further assistance

Note: Only the sendStringParametersAsUnicode=false parameter is used by the native Microsoft SQL Server driver (com.microsoft.sqlserver.jdbc.SQLServerDriver), while only the StringInputParameterType=varchar parameter is used by the TIBCO Data Direct SQL driver (tibcosoftwareinc.jdbc.sqlserver.SQLServerDriver). Wiki: List of hotfixes for TIBCO Spotfire Server

• https://community.tibco.com/wiki/list-hotfixes-tibco-spotfire-server

Doc: Update bootstrap

• https://docs.tibco.com/pub/spotfire_server/7.11.1/doc/html/TIB_sfire_server_tsas_admin_help/GUID-E594DFDE-2732-49C1-A748-D25B049E977A.html