Query runs for 40 secs instead of < 1 after upgrade.
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
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:
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.