IBM Support

Query runs slowly on Microsoft SQL Server (MS-SQL Server) 2014 causing poor performance when using IBM Rational DOORS Next Generation (RDNG)

Troubleshooting


Problem

One query runs slowly on MS-SQL Server 2014 causing poor performance in Rational DOORS Next Generation. The issue can appear when using DNG directly or when linking to DNG artifacts from other applications due to a problem with the MS-SQL Server query optimizer.

Symptom

  • Poor performance navigating artifacts in Rational DOORS Next Generation
  • Poor performance when linking test cases or change requests to Rational DOORS Next Generation artifacts

Cause

The following queries run slowly on MS-SQL 2014:


select t1.ITEM_ID, t1.STATE_ID, t1.JZ_DISCRIMINATOR
from RESOURCE.RESOURCE t1
where ((t1.MODIFIED >= 1439223526995) and (t1.MODIFIED <= 1439227964021)
and (t1.URI like REPLACE(N'storage/%', N'[', N'[[') ESCAPE N'['
or t1.URI like REPLACE(N'baselines/%', N'[', N'[[') ESCAPE N'['
or t1.URI like REPLACE(N'reindex/%', N'[', N'[[') ESCAPE N'['))
order by t1.MODIFIED asc

select COUNT(*) from RESOURCE.RESOURCE t1
where ((t1.MODIFIED >= 1507049147346)
and (t1.MODIFIED <= 1507049184944)
and (t1.URI like REPLACE('storage/%', '[', '[[') ESCAPE '[' or t1.URI like REPLACE('baselines/%', '[', '[[') ESCAPE '[' or t1.URI like REPLACE('reindex/%', '[', '[[') ESCAPE '['))


Environment

CLM using Microsoft SQL Server 2014 or higher

Diagnosing The Problem

Microsoft SQL Server "SQL Profiler" can monitor query performance as well as the built-in "querystats" capability of CLM's repodebug.

The queries above should complete very quickly (within the 1ms - 10ms range). If the queries are shown to be running for longer than a few seconds, we suggest reverting to the SQL Server 2012 optimizer. When running SQL Server 2014, trace flag 9481 on the query forces the SQL Server to use the SQL Server 2012 cardinality estimator that performs much better for these queries. This can be done at the query level or at the database level.

Resolving The Problem

To resolve this issue:

  1. Set the database compatibility mode to 110 (SQL Server 2012) and restart DNG
  2. Enable the trace flag 9481 on the poor performing queries
  3. Contact Microsoft support to determine the root cause of the 2014 optimizer performing much worse than the 2012 optimizer. IBM has not been able to reproduce this problem with in-house data on SQL Server 2014 and therefore cannot pursue a solution with Microsoft directly. So far no customers have opted to pursue this path, but if you do, please contact us if you are presented with an alternative solution that is not mentioned here.
  4. If the above solutions are not desired, you might consider adding the following index on the DNG database, however please note this has proven to have limited success and might regress when SQL Server automatically gathers statistics.


    CREATE NONCLUSTERED INDEX [RESOURCE_RESOURCE_MODIFD_DX] ON [RESOURCE].
    [RESOURCE]
    ([MODIFIED] ASC)
    INCLUDE ([STATE_ID],
    [ITEM_ID],
    [URI],
    [JZ_DISCRIMINATOR])"

Disclaimer

All source code and/or binaries attached to this document are referred to here as "the Program". IBM is not providing program services of any kind for the Program. IBM is providing the Program on an "AS IS" basis without warranty of any kind. IBM WILL NOT BE LIABLE FOR ANY ACTUAL, DIRECT, SPECIAL, INCIDENTAL, OR INDIRECT DAMAGES OR FOR ANY ECONOMIC CONSEQUENTIAL DAMAGES (INCLUDING LOST PROFITS OR SAVINGS), EVEN IF IBM, OR ITS RESELLER, HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

[{"Product":{"code":"SSUVV6","label":"IBM Engineering Test Management"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"WebUI","Platform":[{"code":"PF033","label":"Windows"}],"Version":"6.0;6.0.1;6.0.2;6.0.3;6.0.4","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]

Product Synonym

Rational Quality Manager

Document Information

Modified date:
17 June 2018

UID

swg22000848

Page Feedback