1

I am trying to optimize two views but i don't know enough about SQL to make them faster.

The first one is this one : https://www.brentozar.com/pastetheplan/?id=BJVtbZJQM

and the second one that uses the first one : https://www.brentozar.com/pastetheplan/?id=SyoZX-1XG

Could it be possible to know where are the bottlenecks from the plan ? Thank you!

1
  • Include the query IN the question. VTC
    – paparazzo
    Commented Dec 26, 2017 at 14:14

1 Answer 1

1

One of the things I would recommend you look into would be to update your statistics as from the plans you have pasted they look like they could be out of date.

For example, if you look at what is happening with the index ExclusionProjectConfigurationIdx2 on table [fundhiveAvivaStaging].[dbo].[ExclusionProjectConfiguration]. The operation is a seek which is great, but when you look at what is happening you can see that the Estimated Number of Rows is 1.17062 but the Actual Number of Rows is 34607. The seek on this index counts for 20% of the total execution time.

That isn't the only index that looks like the statistics are out of date, if you look near the bottom of the plan the [LanguageCountrySC] index is used more than once and is 17% of total execution time per use. Again, the stats on this need to be updated.

These two indexes alone account for 54% of your total execution time.

I would start by running DBCC SHOW_STATISTICS on these indexes and compare the output with the number of rows that are actually contained within the table - if there is a huge variance then I recommend you run UPDATE STATISTICS on the offending indexes.

I fear that this may not be the entire solution but it is definitely something that you should look into.

2
  • Look number of executions for those two operators. The estimates aren't that far off. Commented Dec 26, 2017 at 13:12
  • The estimated number of rows for a seek on the inner side of a nested loop is per execution. The actual number of rows for the same operator is the total number of rows returned. What you pointed out here is not a sign that statistics need to be updated.
    – Joe Obbish
    Commented Dec 27, 2017 at 3:47

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