What can cause higher CPU time and duration for a given set of queries in trace(s) ran on two separate environments?

I’m troubleshooting a performance issue in a SQL Server DR environment for a customer. They are running queries that consistently take longer in their environment than our QA environment. After analyzing traces that were performed in both environments with the same parameters/filters and with the same version of SQL Server (2016 SP2) and the exact same database, we observed that both environment were picking the same execution plan(s) for the queries in question, and the number of reads/writes were close in both environments, however the total duration of the process in question and the CPU time logged in the trace were significantly higher in the customer environment. Duration of all processes in our QA environment was around 18 seconds, the customer was over 80 seconds, our CPU time was close to 10 seconds, theirs was also over 80 seconds. Also worth mentioning, both environments are currently configured to MAXDOP 1.

The customer has less memory (~100GB vs 120GB), and slower disks (10k HHD vs SSD) than our QA environment, but but more CPUs. Both environments are dedicated to this activity and should have little/no external load that wouldn’t match. I don’t have all the details on CPU architecture they are using, waiting for some of that information now. The customer has confirmed they have excluded SQL Server and the data/log files from their virus scanning. Obviously there could be a ton of issues in the hardware configuration.

I’m currently waiting to see a recent snapshot of their wait stats and system DMVs, the data we originally received, didn’t appear to have any major CPU, memory or Disk latency pressure. I recently asked them to check to see if the windows power setting was in performance or balanced mode, however I’m not certain that would have the impact we’re seeing or not if the CPUs were being throttled.

My question is, what factors can affect CPU time and ultimately total duration? Is CPU time, as shown in a sql trace, based primarily on the speed of the processors or are their other factors I should be taking in to consideration. The fact that both are generating the same query plans and all other things being as close as possible to equal, makes me think it’s related to the hardware SQL is installed on.

Can a targetted Dispel Magic end a spell with a duration of concentration?

There are only a handful of spells with a duration of concentration and most of them.

While these spells are active, can the caster be subject to a Targeted version of Dispel Magic that does not aim at a spell, but rather the creature (presumably with the intention of ending all spells on that creature). Ostensibly, is the Concentration spell considered to be ‘on’ the caster?

For example, the Implosion spell has a duration of Concentration, which suggests that the caster is actively maintaining a magical effect (which they’re using to smush others into goo). Given that others may be averse to being smushed into goo, could another character target the caster of Implosion with Dispel Magic to end that spell along with any other beneficial spells that the caster may’ve set up?

Is a Rusting Grasp touch attack a standard action and do you have to hold the charge for the duration of the spell?

The druid spell Rusting Grasp (PH 273) says

You may employ rusting grasp in combat with a successful melee touch attack.

and

The spell lasts for 1 round per level, and you can make one melee touch attack per round.

Most touch spells have one "charge", you cast them and then discharge them with a successful touch attack. There are also some spells which grant more than one charge (like Chill Touch (PH 209) which can be used "up to one time per level").

A touch spell that lasts for a fixed duration limiting the number of attacks to one per round seems to be very unusual.

My two questions are:

  • Can you make just a single melee touch attack per round – as a standard action? Or can you make more than one attack (if, for instance, the druid uses Wild Shape and has a couple of natural attacks) but only one of your attacks can deliver the spell?
  • Do you have to hold the charge* for the duration of the spell (and lose the spell if you cast another one) or does the spell simply last for one round/level giving you a "new charge" every round until it expires?

*Touch Spells and Holding the Charge: In most cases, if you don’t discharge a touch spell on the round you cast it, you can hold the charge (postpone the discharge of the spell) indefinitely. You can make touch attacks round after round. If you cast another spell, the touch spell dissipates.

Compute duration of an event based on consecutive values in another column

I need to compute the sum of durations of multiple events based on consecutive values in another column.

             ts             | w ----------------------------+---  2020-07-27 15:40:04.045+00 | t  2020-07-27 15:41:04.045+00 | t  2020-07-27 15:41:14.045+00 | f  2020-07-27 15:42:14.045+00 | t  2020-07-27 15:43:14.045+00 | t 

The event is considered being active as long as the column w has a consecutive value of true.

The duration of the first event would be 60 seconds. '2020-07-27 15:41:04.045+00' - 2020-07-27 15:40:04.045+00. The second event has the same duration. The sum of both would be 120 seconds.

What would be the best/most performant approach to computing these values? The longest time range we’ll be looking at will probably be half a year involving about 30 million rows.

I wrote a custom aggregate function that computes the duration but it takes about 16 seconds for only 1.5 million rows.

 Aggregate  (cost=444090.03..444090.04 rows=1 width=4) (actual time=16290.826..16290.828 rows=1 loops=1)    ->  Seq Scan on discriminator0  (cost=0.00..57289.03 rows=1547203 width=9) (actual time=0.016..1723.178 rows=1547229 loops=1)  Planning Time: 0.196 ms  JIT:    Functions: 3    Options: Inlining false, Optimization false, Expressions true, Deforming true    Timing: Generation 0.889 ms, Inlining 0.000 ms, Optimization 0.508 ms, Emission 6.472 ms, Total 7.870 ms  Execution Time: 16291.836 ms 

I’m new to SQL and basically just got this working through trial and error, so I’m sure there is room for improvement. Maybe even a whole different approach. Here’s a fiddle with the aggregate function.

I’m not sure if i should include the code because it’s quite long

How can someone work around a Sha’ir’s spell duration restrictions?

The Sha’ir (Dragon Magazine 315) is a infamously fiddly and obtuse class to use, made worse by a dispute in how it’s even meant to work. First i feel like i need to point out the Raw issues here. The Sha’ir keeps the ability to cast a prepared spell for a number of hours equal to it’s class level. The rules say that the power to cast the spell remains in the Sha’ir’s mind until the spell is cast or until a number of hours pass at which point the power to cast the spell "dissipates harmlessly".

People can’t seem to agree as to if the spell slot can be refilled afterwards or if it’s wasted and disappears as if you had cast it. The argument for the "Sha’ir Shuffle" is that the spells don’t follow the typical preparation rules and never explicitly make a connection between the number of spells that can be cast per day and how many spells can be retrieved. The argument for people that say that spells disappear point out that the retrieved spells section makes reference to how a wizard’s prepared spells work and thus should disappear.

Needless to say that spells vanishing and being unusable makes the class nearly worthless at low levels and a worse wizard at high ones. It means that you need to stay in the class for 8 levels or more before jumping into a prestige class, and that you’ll probably never be able to use all of your spells during the day. Granted the versatility of the class makes it ideal for dungeon delving gameplay, but being unable to keep spells for any extended period of time means that any other kind of game will render you into a commoner most of the time.

While i would be interested to hear any arguments for which version is more mechanically accurate, i’m mostly trying to find a way to extend the usefulness of spells past their expiration date. Assuming that spells do vanish as if they were cast, what official means are available to salvage this class’s spell casting mechanics?

Some methods i had considered are the Echoing Spell feat (SoX) which causes a cast spell to return to you 1 hour after casting at the cost of being 2 Caster Levels lower. While it’s a bit ambiguous as to what would happen to it, i would assume that this would mean that you could cast a spell that’s about to expire and get it back 1 hour later for the same duration, though this likely runs afoul of the Retrieved vs Preparation issue. Meaning that this feat possibly couldn’t be applied to Sha’ir spells at all.

I had also considered the Runesmith (Races of Stone) Prestige Class as an option. The sha’ir makes mention of spells remaining set in the Sha’ir’s mind until cast or until a number of hours pass. While the section explaining how spells vanish only makes mention of how spells can only be used for a number of hours before dissipating, one could argue that storing a spell through a alternative method, such as in a rune, would allow you to dodge this. Again this has problems. The Sha’ir arguably doesn’t prepare spells, and even if they did its not obvious that this would prolong the spell’s shelf life. Though my next consideration might help.

The Alacritous cogitation (complete mage) and Arcane Preparation (PGtF) feats allow you to effectivly count as both a prepared and a spontaneous caster. Alacritous cogitation, though only usable once a day, allows you to cast a spell you know with a empty spell slot. Arcane preparation allows you to use spell slots to prepare spells, and taking Alacritous cogitation lets you qualify. This debatably lets you prepare your known spells ahead of time in a way that dodges the expiration while letting you leave some open to spontaneously cast spells once a day and send a Gen to fetch the situational ones. This in conjunction with Runesmith could make for a powerful combination, assuming that any of my assumptions work and let you bypass the restrictions of the class. However this is pretty steep, this strategy effectively costs 3 feats and a dip into a martial class as well as using a race that has a -2 in your casting stat to function at all, and that same effort could make a wizard that’s substantially more powerful.

So i’m left wondering if there’s any way to salvage this class short of begging to use the "Sha’ir Shuffle". This class has a lot of interesting mechanics but to even be usable you need to spend way to long in the class proper and can’t take advantage of how it synergies with a lot of interesting prestige classes.

How can a Sha’ir avoid spells vanishing so soon after preparation?

What would be the effects of changing the duration of a round from 6 seconds to 5 seconds?

I’m introducing the following house rule:

A round lasts 5 seconds.

What would be the effects of such a house rule? Would there be any? Would this break the game?

Out of the top of my head, I cannot think of any actions/spells/mechanics that rely on the duration of a round.


Disclaimer: The original question was a bit broader and was closed twice as opinion-based/off-topic.

How many rounds does a spell with a duration of 1 minute last?

If a concentration spell lasts for one minute (60 seconds, 10 rounds), when does one start counting down how much time it has left? Does the round you cast it in count towards how long it stays active?

First example, what if I use Aura of Vitality, which uses an action to cast then a bonus action can be taken to heal a target? The spell lasts for 1 minute, or 10 rounds. So on the turn I cast it I can use a bonus action to immediately use it, then do I get 9 more rounds or 10 more?

What about spells like Moonbeam, which don’t take effect on your turn? Moonbeam activates when a creature enters it or starts its turn there. If you’re first to go in a round then you’ll get 10 uses out of it, but if you’re last does that mean you only now get 9 uses out of it since it acts during an opponents turn?

Is there a difference between “one round” and “until the end of your turn” in terms of duration?

I’m mainly concerned about the effects that last “one round” and “until the end of turn” (like Stone Bones ToB p.84 and Inferno Blade ToB p. 54, respectively). My guess is that “until end of turn” effects end after you’ve taken your “standard” actions (one swift action, one move action, one standard action, or whatever the duration equivalent would be, like a full-round action), so the reactions you take on that round are not affected by that effect. On the contrary “one round” effects would be in effect until it’s that PC’s turn again.

However, my DM insists that both effects would last until that PC’s turn comes again, having no distinction, even if it has different wording. So, really, what’s a round and what’s a turn in this case? Are they the same?

Are the instructions given in the “Planar Binding” spell part of the casting time, or can instructions be amended during the spell duration?

The Planar Binding spell is a 5th level abjuration with verbal, somatic and material components and a casting time of 1 hour that allows the target a Charisma saving throw. If the target fails the save, it “must follow your instructions to the best of its ability”.

Are the instructions specified as part of casting the spell? Or are the instructions given after the spell has finished casting? Can instructions be updated/amended throughout the duration of the spell, or is there a one-time period during which the instructions must be fully specified?

Although the duration for the 5th level version of this spell is 24 hours, the duration increases as higher spell slots are used, lasting a year plus a day when a 9th level spell slot is used. I mention this because the longer the spell lasts, the more important the answer to this question becomes.