Query runs for 40 secs instead of < 1 after upgrade.

RichardM 2014-06-18 06:44:01

We have a query being generated by document management system, TRIM, that is running for nearly 40 seconds after an upgrade when it used to take less than 1 second.

The query being generated is actually different after the upgrade but I am bemused by what SQL is up to.

In the query in the attached plan, if I replace

OR uri IN (SELECT rkRecUri1
FROM TSRECLINK WITH ( NOLOCK )
WHERE ( rkRecUri2 = 4226274
AND linkType = '8'
))

with the following which is what the select rkRecUri1… actually returns it goes back to less than a second.

OR uri IN ( 4201769, 5728890 )

Something seems to be forcing it to do a clustered index scan which loads over 8 million rows in to a temporary table when the 'select rkRecUri1' is part of the query.

Slow planlink text

Fast planlink text

This is running on SQL 2008 R2 SP2 but also happens on SQL 2012 SP1.

Any input on what is going on would be much appreciated.

Table/index/statistics scripts – link text

Output of DBCC Show_statistics – link text

Statistics with histograms – link text

—- 19/06/2014 —-

Query Plan with Filtered Stats – link text

Query Plan with Filtered Stats and Indexes – link text

—- 20/06/2014 —-

QP with new TSRECORD Index – link text

QP with FORCESEEK – link text

QP with all 3 indexes – link text

QP forced index use – link text

QP with Plan Guide – link text

SQLkiwi 2014-06-18 07:52:52
There are a number of things going on here. It's hard to gauge your potential level of interest in all the details, so I'll just mention a couple of things.

First, the query is a tricky one for the optimizer. There are all sorts of hard-to-estimate constructs, meaning estimated row counts vary widely from the values actually encountered. As a result, there is a fair element of luck involved in the plan turning out to be be quite reasonable in the 'fast' case. That's the background.

Second, the optimizer has more luck coming up with a ballpark-reasonable estimation where two specific uri values are specified in the query, and it can use a simple index intersection. When this list is replaced with a nested SELECT, the optimizer's estimate is horribly inaccurate. When it expects a much larger number of uris (944,753) it decides that building an on-the-fly temporary index will be worthwhile. The trade-off is between seeking 944,753 times into the clustered index with the enormous residual versus building a better index and seeking on that. For the large number of expected iterations, the optimizer's cost model assesses the temporary index as the cheaper option. Plan fragment in estimated mode shown below:

Plan Fragment

Recommendations

Actually this is rather a tricky one to fix without a full copy of the database to work with, for a whole bunch of reasons that are more my problem than yours.

The workaround involves adding a hint to the query. You can't change the query itself, so this has to be done with a Plan Guide. Two plan guides, in fact, one to specify a parameterized template, and the other to apply the hint to the parameterized form. Create the plan guides with this script:

DECLARE 
    @querytext nvarchar(MAX),
    @templatetext nvarchar(MAX),
    @|/4/>parameters nvarchar(MAX);
 
SET @querytext =
N'SELECT  uri ,
        regDateTime ,
        fullRecordId
FROM    TSRECORD WITH ( NOLOCK )
WHERE   ( ( rcContainerUri = 4226274
            OR uri IN ( SELECT  rkRecUri1
                        FROM    TSRECLINK WITH ( NOLOCK )
                        WHERE   ( rkRecUri2 = 4226274
                                  AND linkType = ''8''
                                ) )
          )
          AND uri > 0
        )
        AND ( TSRECORD.rcSecLevel <= 10
              AND TSRECORD.rc1CaveatKey = 1
              AND TSRECORD.rc2CaveatKey = 1
              AND TSRECORD.rc3CaveatKey = 1
              AND TSRECORD.rc4CaveatKey = 1
              AND TSRECORD.rc5CaveatKey = 1
            )
        AND ( ( TSRECORD.rcAccessLoc = 0
                OR EXISTS ( SELECT  axLocUri
                            FROM    TSaccessCo WITH ( NOLOCK )
                            WHERE   TSRECORD.uri = axObjectUri
                                    AND axObject = 6
                                    AND axType = 2
                                    AND ( axLocUri IN ( 0, 44736, 109872,
                                                        92536 ) ) )
              )
              AND ( TSRECORD.rcRecTypeSecFilter NOT  IN ( 10, 14, 37, 1022 ) )
              AND ( TSRECORD.rcAccessLoc > 0
                    OR TSRECORD.rcAccessIsCont = ''F''
                    OR TSRECORD.rcContainerUri = 0
                    OR EXISTS ( SELECT  ContTest.uri
                                FROM    TSrecord ContTest WITH ( NOLOCK )
                                WHERE   ContTest.uri = TSRECORD.rcContainerUri
                                        AND ( ContTest.rcSecLevel <= 10
                                              AND ContTest.rc1CaveatKey = 1
                                              AND ContTest.rc2CaveatKey = 1
                                              AND ContTest.rc3CaveatKey = 1
                                              AND ContTest.rc4CaveatKey = 1
                                              AND ContTest.rc5CaveatKey = 1
                                            )
                                        AND ( ( ContTest.rcAccessLoc = 0
                                                OR EXISTS ( SELECT
                                                              axLocUri
                                                            FROM
                                                              TSaccessCo WITH ( NOLOCK )
                                                            WHERE
                                                              ContTest.uri = axObjectUri
                                                              AND axObject = 6
                                                              AND axType = 2
                                                              AND ( axLocUri IN (
                                                              0, 44736, 109872,
                                                              92536 ) ) )
                                              )
                                              AND ( ContTest.rcRecTypeSecFilter NOT  IN (
                                                    10, 14, 37, 1022 ) )
                                            ) )
                  )
            )
ORDER BY regDateTime DESC ,
        fullRecordId ASC';
 
EXECUTE sys.sp_get_query_template
   @querytext, 
   @templatetext OUTPUT, 
   @|/4/>parameters OUTPUT;
 
EXECUTE sys.sp_create_plan_guide
    @name = N'PGTemplate1',
    @stmt = @templatetext,
    @type = N'TEMPLATE',
    @module_or_batch = NULL,
    @params = @|/4/>parameters,
    @hints = N'OPTION ( PARAMETERIZATION FORCED )';
 
EXECUTE sys.sp_create_plan_guide
    @name = N'PGSQL1',
    @stmt = @templatetext,
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = @|/4/>parameters,
    @hints = N'OPTION ( TABLE HINT (ContTest, NOLOCK, FORCESEEK) )';

Please try this and upload the plan you get. It will be a little more complex-looking than previously due to the parameterization, but this is harmless.

If you need to drop the plan guides at any point:

EXECUTE sys.sp_control_plan_guide 
    @operation = N'drop',
    @name = N'PGTemplate1';
 
EXECUTE sys.sp_control_plan_guide 
    @operation = N'drop',
    @name = N'PGSQL1';

These indexes are also probably required to get a good query plan:

CREATE INDEX ix1 ON dbo.TSRECLINK (rkRecUri2, linkType, rkRecUri1);
CREATE INDEX ix1 ON dbo.TSACCESSCO (axObjectUri, axObject, axType, axLocUri);

These can be added or existing indexes modified, depending on how that fits with your workload/overall knowledge of the system. Some of the existing indexes that include unique keys should also be rebuilt to be explicitly UNIQUE. This information is generally helpful to the optimizer.

Plan with successful plan guide