The answer, I believe, really is "it depends".
Remember that, in 5e, speak with dead has limitations:
-
Answers are usually brief, cryptic, or repetitive
-
the corpse is under no compulsion to offer a truthful answer if you are hostile to it or it recognizes you as an enemy.
-
the corpse can't learn new information, doesn't comprehend anything that has happened since it died, and can't speculate about future events.
#1 means that even a seemingly-straightforward question like "who killed you" or "where did you die" might have less-than-helpful answers. To the first: is "Sam" the butler or the secret mistress; is "the one I love most" a spouse, a child, a sibling, etc.? To the second: answers like "at home" or "on a bed" could be similarly unhelpful.
#2, of course, means that it's possible the corpse's "animating spirit" wants to mislead the investigator. This GM finds that to be more likely if the deceased was involved in illegal activity.
#3 - depending on the GM's interpretation - may mean that questions that are standard fare for police procedurals may not work. My immediate thought when reading the question is "who might have wanted to hurt you?", which may mislead investigators if the motive involves information not known to the deceased before their passing; or "why would someone want you dead?", which may require the corpse to "speculate about future events" (this is definitely "YMMV" territory).
And, of course, a good investigator will go where the facts lead them. If the answer to "who killed you?" is "my wife", they're unlikely to ask "who may have wanted you dead" (even though "my wife" may refer to an ex-wife or other secret unknown to the investigator).
Speak with dead is also a 3rd level spell, which (IME) is not exactly cheap/easy to get cast by an NPC in many game worlds, making it a go-to part of a murder investigation questionable in those worlds (and: whether your city's police force can assume they have access to Xth-level spells more-or-less on-demand is a great bit of world-building!).
Given its limitations, I submit that speak with dead is better used later in the investigatory process, once more tailored questions can be asked and other avenues of investigation have been ... if not "exhausted" then at least "explored".
That said, one stand-out use of the spell is when a "John Doe" is found: speak with dead requires that
The corpse must still have a mouth and can't be undead.
... so, "who are you?" and "where are you from?" are, to my mind, more common questions to ask at the beginning of an investigation. They have all of the same limitations of other questions, but even evasive answers might be helpful in identifying found remains. And, of course, it's a pretty simple undead detector: if the spell fails today, assume the target is undead and take reasonable precautions until the spell can be re-cast 10 days later; if the spell fails again, the target is undead (: or somebody really wants you to think they are, and they've infiltrated lock-up to cast the spell).
All I'm saying is that there are ways to make this work and there are ways to make this not work too.
– biziclop Apr 03 '23 at 15:09