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)

How do you choose Spells Known when creating a new character above 1st level?

It’s not clear to me how to choose Spells Known when creating a new character at a higher level.

Let’s take the Bard as a working example first:

Bard level 2:

  • Spells Known: 5
  • Slots per Spell level: 1st × 3

Bard level 3:

  • Spells Known: 6
  • Slots per Spell level: 1st × 4, 2nd × 2

I’m interpreting this to mean that, if an existing 2nd-level Bard gains a new level (level 3), he’s gaining 1 (one) new spell known. He can then choose the new spell known to be either 1st or 2nd, correct?

What I don’t understand then, is the case of creating a new character starting at a higher level; say, creating a new Bard at level 3.

In that case you’re forced to choose 6 spells, which must be from the list of 1st- and 2nd-level spells, correct?

When making this 3rd-level Bard from scratch, though, can I select 2nd level spells for all 6 spells known?

As another example, say you’re creating a very high level Bard from scratch, starting at level 15. The book says your Spells Known is 19, and your spell slots are 1st × 4, 2nd × 3, 3rd × 3, 4th × 3, 5th × 2, 6th × 1, 7th × 1, and 8th × 1.

So, for those 19 Spells Known, you can choose any spells from 1st through 8th spell level. Am I free then to select as many spells as I can at 8th spell level (5 in number), working backwards, and in the end ignoring the 1st, 2nd, 3rd level spells entirely?

As another example, the Ranger doesn’t know any spells at 1st level, nor have any spell slots, but the book says “You know two 1st-level spells of your choice from the ranger spell list.” Does it mean at 2nd level and above?

Long story short: When choosing Spells Known for a spellcasting character created above 1st level, am I allowed to choose my Spells Known at whatever spell level I like, just so long as I have at least one spell slot of that level?

I’m working on a D&D 5e character generator and need to clarify this.

What are the limitations of the ‘circumstance’ I can choose to trigger the Contingency spell?

The contingency spell description states:

Instead, it takes effect when a certain circumstance occurs. You describe that circumstance when you cast the two spells.

There is nothing written that requires the circumstance is perceivable, in contrast with how you can Ready an action. It also does not limit the distance. It also does not limit the details you could come up with (since 10 minutes is pretty long time). Possible scenario on my mind:

When the King Leo XIII died. I wouldn’t have known since I’m so far away, but when the Message cantrip takes effect, whispering my previous Message, now I know he is dead.

I’ve just realized that this spell is insanely powerful and can be abused if not properly limited. What are the limitations, anyway?

What would you choose to run your database 2 x NVME 1920 Raid 1 or 4 x SSD 960 RAID 10

I am trying to move my server from one provider to another. Currently, I do have 4 SSD Raid 10 but I am intrigued by NVME drives. I would like more IO but I am not sure how long they last and if one goes done replacing it would mean more downtime. Your recommendation. Also are hourly backups enough or you recommend more often.

This is a production environment and on heavy loads time, few queries get a lot of data returned not I don’t see the struggle with the 4 SSD right now.

Choose 1Z0-932 Exam Dumps for Your Better Results

How to Get Started with 1Z0-932 Exam Dumps

Oracle 1Z0-932 Exam Preparation Material presents you with everything you will need to take your Oracle Cloud Infrastructure 2018 Architect Associate examination. Oracle 1Z0-932 exam info is researched and produced through Professional Certification Experts who are constantly the use of industry revel in to produce precise, and logical. You might also get the actual questions from different web websites or books, however, good judgment is the key. Oracle 1Z0-932 exam dumps 2020 substances will help you bypass the 1Z0-932 test in your first try, and also keep your precious time. Oracle Cloud reserves the right to take action against any candidate involved in fraudulent activities, including, however no longer confined to, fraudulent use of vouchers, promotional codes, reselling examination reductions and vouchers, dishonest on an examination, alteration of score reports, alteration completion certificates, violation of exam retake guidelines or other sports deemed fraudulent by means of Oracle.

Our panel of IT experts and specialists updates the Oracle 1Z0-932 examination regularly on the way to provide candidates of the 1Z0-932 exam these days updated, relevant and correct materials. Oracle 1Z0-932 exam dumps are given with special description which now not most effective helps you to pass your 1Z0-932 exam however certification strengthens the employment potentialities and opens up myriads of possibilities for them. The online 1Z0-932 practice is famous and effective considering the time price as it will offer you all 1Z0-932 solutions in a way you could fully understand and you will never neglect these answers on your Exam 1Z0-932 tests. One extra place you could turn to for greater 1Z0-932 practice questions is the look at the forum. Success isn’t always a long way from you now. Take a hazard and make it manifest with our schooling materials. So to make your hazard final, 1Z0-932 examination preparation materials are the most effective promised manner.

Quick Tips to Help You Find the Best 1Z0-932 Exam Dumps

Having through the net certification examination of 1Z0-932, you ought to acquire some depended on helping materials. For becoming effective in the 1Z0-932 study material examination, you have to make use of the assisting tools which will truly make you comfortable for your preparation. The study equipment and guides readily available on several internet websites for online certification exams are produced by using very licensed and skilled experts in the IT sector. We have numerous Oracle 1Z0-932 Practice Exam and other exam preparation materials providing you with consolidation in the ways of your hobby and ease. Oracle 1Z0-932 PDF Download, Term Papers, Exam Answers, and other materials come up with targeted and logical insurance of Oracle 1Z0-932 exam targets and provide you with the real exam environment as these merchandises are built through IT examiners so you enjoy the real examination features in our merchandise.

Our Oracle 1Z0-932 sample check is constantly updated to provide you oracle 1Z0-932 PDF Download the maximum actual information to be had and this is executed with the assistance of our professional’s group of certification experts, technical staff, and complete language masters who are continually in-contact with the changes within the Oracle 1Z0-932 exam. This is your premier source of 1Z0-932 test training. With our Oracle 1Z0-932 practice tests, no other dealer will be capable of examining to the exceptional 1Z0-932 examine guide. Our Oracle 1Z0-932 sample check is continually up to date to provide you the most actual statistics to be had and this is accomplished with the help of our professional’s crew of certification experts, technical staff, and complete language masters who are constantly in touch with the changes inside the Oracle 1Z0-932 examination.

When casting the Glyph of Warding spell, how many spell slots are used if you choose the Spell Glyph option?

The description of the spell states you can store a spell of 3rd level or lower by casting it as part of the Glyph of Warding.

Does this require the use of one spell slot (because you are casting it as ‘part’ of casting the glyph), or two spell slots (one for the glyph and one for the stored spell)?

Also if it does expend two spell slots, when is the second slot expended? Is it expended upon storing it? Or upon the glyph’s activation, which then casts the stored spell?

Why did browsers choose to implement HSTS with Preload over checking custom DNS information?

Browsers and standards bodies favor HSTS with Preload because it avoids ever sending an http request to a website that supports https. This is good, because cleartext http requests can be intercepted to set up Man in The Middle attacks.

But a number of websites explain that a centralized Preload list doesn’t scale up well to the mostly https web that has been proposed by W3C, the EFF, and others. Managing one centralized list creates a bottleneck for looking up, adding, and deleting list items.

Yet this technology has been implemented rather than, say, to use DNS, which is already nicely distributed and is already used by browsers to lookup URL domain names.

Of course, the DNS is not yet secure, and proposals to make it secure are controversial. But why would the DNS have to be secure to hold one more bit of information (whether the domain can support https–and ONLY https–or not)?

In the worst case, a malicious MiTM attack could make it seem that a website is insecure when it is actually secure. But in this case, an insecure connection would simply fail. This failure would deny the malicious user any advantage.

So naturally I’m wondering why a centralized HSTS with Preload is preferred over adding a new flag to DNS zones for indicating that the domain supports https connections.

What spells can an Arcane Trickster Rogue choose from when gaining levels?

Trying to understand how an Arcane Trickster Rogue works with regards to spell selection. In the Player’s Handbook, page 98, it says (emphasis mine):

You know three 1st-level wizard spells of your choice, two of which you must choose from the enchantment and illusion spells on the wizard spell list.

Then it goes on to say:

The spells known column of the arcane trickster spell casting table shows when you learn more wizard spells of 1st level or higher. Each of these spells must be either an enchantment or illusion spell of your choice.

Can I have spells of the other types starting at level 3 or not?

If I can only cast spells from any school at 8th level, what are the acceptable types of spells that I start out with (besides mage hand)?