0

summary

We have an Object-Oriented database schema that we query with Hibernate with:

  • 1 parent table (NAL_Actions)
  • 7 child tables (NAL_*)

If I want to query a couple of these "action"-objects we have to join all 1+7 tables together (because we don't know what type of child object we need) which isn't great but because of the query execution plan generated by SQL Server results in very slow queries (index scans with 2,5 million rows).

details

The parent table also has a foreign key (actionGroups) that according to the fullscan statistics results in 1.15 "action"-rows on average.

This query plan - edit, now also on: paste-the-plan is over estimating the number of rows for each subsequent table that is joined which I and statistics know is never more than two rows as all tables are joined on their (common) distinct primary key, which is also the clustered index, which means that the number of estimated rows cannot increase, as can be seen from this definition of one of the Child-tables:

USE [NAL_PRD]
GO

/****** Object:  Table [dbo].[NAL_Unblocks]    Script Date: 6/24/2020 6:04:40 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[NAL_Unblocks](
    [actionId] [bigint] NOT NULL,
    [unblockingAmount] [decimal](5, 2) NOT NULL,
    [productSequenceNumber] [int] NULL,
 CONSTRAINT [PK_NAL_UNBLOCKS] PRIMARY KEY CLUSTERED 
(
    [actionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[NAL_Unblocks]  WITH CHECK ADD  CONSTRAINT [fk_unblocks_actions] FOREIGN KEY([actionId])
REFERENCES [dbo].[NAL_Actions] ([id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[NAL_Unblocks] CHECK CONSTRAINT [fk_unblocks_actions]
GO

As SQL Server falsely assumes the number of rows exponentially increases with each joined table it changes the join strategy for the last 3 tables:

  • from: "clustered index seek + nested loops"
  • to: "clustered index scan + merge join"

This means that we need to scan the clustered index of a table of 2,500,000 rows instead of 1 or 2 index seeks

what I've tried so far

  1. hints work, e.g.: WITH(FORCESEEK) for every join, or: OPTION (LOOP JOIN) for the entire query, (but I cannot change the application)
  2. this leads me to suggest plan guides as the next option (for SQL Server 2016 - 13.0.5102.14) but although that works fine with sp_executesql the plan guide does not seem to take immediate effect in the application, although I took the SQL from the Query Store and updated the statistics (but maybe updating statistics was a false assumption so maybe I need to restart the application and/or drop the plan - but how without DBCC FREEPROCCACHE as I am limited to db_owner permissions, so not VIEW/ALTER SERVER STATE permissions? Maybe I should switch the application / the JDBC driver to use sp_executesql with prepareSQL=2 instead of the default (sp_prepare + sp_execute)?.

What I have not yet tried:

  • upgrading to SQL Server 2017 to try adaptive query processing, should that fix this?
  • 2
    It's easier if you upload the plan to pastetheplan.com. I haven't looked at it yet, but once you mentioned Hibernate and 8 tables, I can tell you that it is very likely any of your settings changes are not going to fix the awful query Hibernate has created. Are you able to introduce stored procedures, views, etc. if you can't change the code? Do you have proper FKs in place? – LowlyDBA Jun 23 at 20:23
  • Please post the index definition for [NAL_PRD].[dbo].[NAL_Unblocks].[PK_NAL_UNBLOCKS], one of the joins that's increasing the estimated row count. – David Browne - Microsoft Jun 24 at 3:06
  • thank you for your suggestions, I've updated the question accordingly – JohannesB Jun 24 at 4:19
  • I don't see an exponential increase based on joins. One of the tables has a cardinality of about 2.4 million rows. The final estimate is 3.4 million. That's easily explained by all the other data. How many actual rows are returned? 263,000. You're returning more data than can be realistically filtered in an index (usually a very low number like .1% of the index, not 25%) so you're seeing scans. Filter the data more. – Grant Fritchey Jun 24 at 10:15
  • 1
    Have you tried running under old cardinality estimator or with ENABLE_QUERY_OPTIMIZER_HOTFIXES enabled/disabled? Did you try update statistics with fullscan? I think those are your only options, if you cannot change the query. Though I very much doubt those will help. I saw very similar issues and always ended up rewriting the SQL – rois Jun 24 at 17:45
0

As you're using hibernate this is going to be a bit cumbersome. You can 'guide' the optimizer by telling him how to do the joins. For example select * from a inner hash join b on a.x = b.x will tell the optimizer to do a hash join. It could also be a merge join or a loop hint.

This might result into writing the sql queries manually. Using hints should also be one of the last things to try. Your tables might change over time and the hints might not apply/be good for the performance at a later stage (mostly when switching from development to production).

| improve this answer | |
0

To avoid having to change the application in all the different places where we use this type of queries and because of high urgency the customer agreed to enable the old cardinality estimator after which all problems were solved instantly:

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY CARDINALITY ESTIMATION = ON;

I want to thank everyone for contributing their valuable time and knowledge to this question.

| improve this answer | |

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.