0

Recently, we've moved with our database from MySQL to Microsoft SQL Server (software is compatible with both engines). Overall performance after migration is quite good, but we've got problem with some queries. One of queries we're investigating looks like this:

select parent from Features WITH(ROWLOCK)
where (ParentType=N'DokHandlowe' and Parent=3023658)
or (ParentType=N'PozycjeDokHan' and Parent in (25302310, 25302312,
25302313, 25302314, 25302315, [.... list of about 200 numbers ....])

we've got 2 indexes on that table:

ALTER TABLE [dbo].[Features] ADD  CONSTRAINT [PK_Feature] PRIMARY KEY CLUSTERED 
(
    [Parent] ASC,
    [ParentType] ASC,
    [Name] ASC,
    [Lp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [Features_Data_INDEX] ON [dbo].[Features]
(
    [ParentType] ASC,
    [Name] ASC,
    [DataKey] ASC,
    [Lp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

Almost the same query (as below) gives me result in few milliseconds:

select parent from Features WITH(ROWLOCK)
where (ParentType=N'DokHandlowe' and Parent=3023658)
UNION
select parent from Features WITH(ROWLOCK)
where (ParentType=N'PozycjeDokHan' and Parent in (25302310, 25302312,
25302313, 25302314, 25302315, [.... list of about 200 numbers ....])

If we reduce the list of numbers from ~200 to let's say ~20 it's also incredibly fast. Also if I omit the condition ParentType=N'PozycjeDokHan' it's fast again.

I'm attaching 2 query plans:

  1. first query
  2. second query

What we've already tried:

  1. Adding some more indexes (filtered and not)
  2. Rebuilding existing indexes
  3. Creating additional statistics and updating existing ones (with FULL SCAN option)

but nothing works.

We cannot change the queries themselves - it's from software we've bought and can't edit.

What else can we try?

The table contains about 160 million records and these queries return just few of them (or even an empty set in some tests)

I've tried one more select:

select parent from Features WITH(ROWLOCK)
where (ParentType=N'DokHandlowe' and Parent=3023658)
or (ParentType = 'PozycjeDokHan'  AND Parent in (SELECT id from pozycjedokhan where dokument=3023658 )) 

This subselect returns exactly the same list of numbers like in previous ones and this time milliseconds of waiting again.

We're running SQL Server Standard in version 15.0.2000.5. Database is on compatibility level 150 (we've tried to lower that just to see if execution plan is changing, but we've got the same results each time). About 120 million records have this ParentType.

0
2

This is not an easy problem to solve.

It is tractable, but please read the following very carefully, and all the way through (including links).

I would suggest testing with the database set to use forced parameterization:

ALTER DATABASE CURRENT SET PARAMETERIZATION FORCED;

Drop all the indexes on the source table except the primary key.

You should then get an execution plan of the following form:

execution plan with merge interval

The plan is cropped because there will be one Constant Scan per item in the IN list.

Forcing parameterization means the optimizer has to de-duplicate possibly overlapping ranges at runtime. When literals are used, the optimizer can tell they do not overlap, and can de-duplicate the values at compile time.

You need to carefully test the impact of the forced parameterization change on other queries in the workload.


Forced parameterization and additional indexing

The above only works with forced parameterization and just the primary key index shown in the question. If you need additional indexes on the table or you cannot enable forced parameterization on the whole database, read on:

If you are not able to set the whole database to forced parameterization, you can force parameterization for the individual queries using a plan guide template.

You would need one of these per distinct number of IN list parameters, and possibly one per data type within that, as noted in the documentation:

ℹ Important

As part of parameterizing a query, SQL Server assigns a data type to the parameters that replace the literal values, depending on the value and size of the literal. The same process occurs to the value of the constant literals passed to the @stmt output parameter of sp_get_query_template. Because the data type specified in the @params argument of sp_create_plan_guide must match that of the query as it is parameterized by SQL Server, you may have to create more than one plan guide to cover the complete range of possible parameter values for the query.

In any case, if you need additional nonclustered indexes on the base table, you may find the optimizer no longer chooses the optimal plan shown above.

To address that, you would need a second plan guide to use the specific plan shape on the parameterized form(s) of the query. Capture the desired (forced param'd) XML showplan from the plan cache and use that to create the second SQL plan guide as described in that link.

With a TEMPLATE guide set up to parameterize the query, and a SQL guide set to force the plan shape, you will get the desired plan even after creating additional indexes on the base table.

You can check you have everything set up correctly by requesting an estimated plan for the raw query (i.e. as submitted by the application) in SSMS.

The resulting root plan properties will show both plan guides being applied:

plan properties with plan guides applied successfully

A fair amount of work, and not ideal, but the situation as presented doesn't admit many other options. If you have a relatively stable number of items in the IN list, and not too many possible data types, this may be a workable solution. Remember, you need a template and SQL guide for each different number of IN list parameters at least.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.