Can’t help the engine to choose the correct execution plan

The stuff are pretty complex to share the original code (a lot of routines, a lot of tables), so I will try to summarize.

Environment:

  • SQL Server 2016
  • standard edition

Objects:

  • wide table with the following columns:

    ID BIGINT PK IDENTITY Filter01  Filter02  Filter03  .. and many columns    
  • stored procedure returning visible ID from the given table depending on filter parameters

  • the table has the following indexes:

    PK on ID NCI on Filter01 INCLUDE(Filter02, Filter03) NCI on Filter02 INCLUDE(Filter01, Filter03) 

Basically, in the routine I am creating three temporary tables – each holding current filtering values and then join them with the main table. In some cases, Filter02 values are not specified (so the join with this table is skipped) – the other tables are always joined. So, I have something like this:

SELECT * FROM maintable  INNER JOIN #Filter01Values -- always exists INNER JOIN #Filter02Values -- sometimes skipped INNER JOIN #Filter03Values -- always exists 

So, how the IDs are distributed – in 99% of the cases it will be best to filter by Filter02Value and I guess, because of this, the engine is using the NCI on Filter02 INCLUDE(Filter01, Filter03) index.

The issue is that in the rest 1% the query fails badly:

enter image description here

In green is the Filter02 values table and you can see that filtering on this does not reduce the read rows at all. Then when the filtering by Filter01 is done (in red) about 100 rows are returned.

So, this is happening only when the stored procedure is executed. If I execute its code with these parameters I nice execution plan:

enter image description here

In such case, the engine is filtering by Filter01 first and Filter02 third.

I am building and executing dynamic T-SQL statement and I add OPTION(RECOMPILE) at at the end, but it does not change anything. If I add WITH RECOMPILE on the stored procedure level, everything is fine.

Note, the values in the temporary tables for filtering are not populating in the dynamic-tsql statement. The tables are defined, populated and then the statement is built.

So, my questions are:

  • is the engine building a new plan for my dynamic statement as I have OPTION(recompile) – if yes, why is wrong
  • is the engine using the values populated in my filter02 temporary table to build the initial plan – maybe yes, that’s why it is choosing the wrong plan
  • using recompile on procedure level feels very hard/lazy fix – do you have any ideas how I can assist the engine further and skip this option – new indexes for examples (I have try a lot)

Why can’t OS’s keep audio and video recording in sync?

It seems every program that purports to record webcams fails to keep audio and video in sync. Moreover, there are probably man millenia being spent by people trying to correct for this during editing.

While it is understandable that OS’s may have to buffer incoming raw audio and video prior to compression, if necessary taking gigabytes of RAM, it is less understandable that they can’t keep the two in sync during recording.

This points to an architectural flaw in common with the major OSs. What is the problem?

I cant find a solution for a question of an array about prefix sum?

if I was given the shuffled array of a combination of prefix sum array and suffix sum array as Input. How to find out the number of initial array possible which can produce input array (combination prefix and suffix sum arrays).

How will we find whether an input array is valid as sum of prefix sum array and suffix sum array

When a monster doesn’t use an AoO, does it mean it can’t take one later on?

I’m surrounded by melee monsters and I want to cast a spell, but I’m afraid that I would fail the concentration either when casting defensively or when taking damage during spellcasting.

Casting the spell is more important for me than not taking damage, and I would prefer to stay in my current position if possible.

I’m thinking of this tactic: move one square (using the Move action), then

  • If all the monsters attack me, that’s great, their AoO is spent, I can return to my original position and cast my spell safely without making a concentration check.
  • If they don’t, then that means that they chose to not take the opportunity and I can use the rest of my Move action to safely move through any squares that they control without triggering another AoO from them, perhaps moving to a square that they don’t threaten and cast my spell from there.

Does this work?

Can you dispel a magic effect you can’t perceive?

Discussions about Dispel Magic seem to imply that as long as you can perceive a magic effect in range of the spell, you can try and dispel it.

See Can the Dispel Magic spell end a Darkness spell? and this quote from @Miniman:

knowing where it is means that you know where it is. This allows you to use spells like Dispel Magic to remove its invisibility.

But what if you can’t perceive it?

Two scenarios come in mind:

  1. In combat, an evil wizard casts Invisibility on himself, walks away from the fray and uses the hide action on his next turn. Since PCs know/assume/deduce he’s invisible and certainly in range of the spell, can one use Dispel Magic to end the spell?

  2. PCs enter a 80’×80′ room. Can one declare: “I dispel any Invisibility spell in range”, thus revealing one (the closest?) invisible enemy in the room?

What’s enough to enable a caster to cast Dispel Magic on a magic effect:

  • perceiving (seeing, hearing, smelling, etc.) it?
  • knowing/assuming/deducing it’s there?
  • or merely suspect/predict it?

When attacking with Spiritual Weapon, what can’t be seen by the attacker for me to gain advantage?

Spiritual weapon claims that you make a melee spell attack against a target adjacent to your spiritual weapon. The rules for attacking while being unseen state that if the target cannot see you, you gain advantage on your attack against them.

Does this mean that if the enemy can’t see you (such as if you were in complete darkness), but they can see your spiritual weapon, you gain advantage to attack them with the spiritual weapon bonus-action attack?

WotC Pre-rolled Level 1 Bard Character – Can’t get it right in D&D Beyond

I’m trying to build the nifty 5E Half-elf Bard in D&D Beyond as shared on WotC’s set of pregen character sheets.

However, I just can’t seem to get the ability score values and the skill values to align to what their character sheet PDF says and it’s a real bummer.

Per their character sheet: STR 8 (-1), DEX 16 (+3), CON 14 (+2), INT 12 (+1), WIS 10 (0), and CHA 16 (+3).

And the Character Sheet skills are listed as: Arcana +3, Acrobatics +5, Deception +5, Insight +2, Perception +2, Performance +5 and Persuasion +5.

However, after entering in the ability scores they called out, my Deception is only +3 and my Insight is only 0. I got the rest to align though.

I’ve been at this for 90 minutes but to no avail. Any suggestions on how to make this match the sheet? Trying to make this one for my daughter as a surprise.

Can’t tell If The OpenVPN Server I Setup Is Encrypted using Wireshark?

This might sound dumb, but I set up an OpenVPN server on a Synology NAS. When I connect via the client at home to the server, everything works for.

I know you can read packets in Wireshark. If I start sniffing on My WiFi that I’m connected to at home, packets show for the OpenVPN protocol and there are few lines that say Synology (I believe this is a handshake) but everything else, every line of data is gibberish so I’m assuming it’s encrypted?

That also said, when I connect to the VPN another local area connection appears in Wireshark as an adapter to sniff. If I sniff the new local network this is NOT encrypted, but isn’t that normal?

Essentially you’re sniffing the new adapter itself locally as soon as you connect via the client? Or is that NOT normal? Why does it show gibberish like it’s encrypted on the WiFi via Wireshark, but not the new connection created upon connection to the VPN called Ethernet 2?