0

I'm using sp_BlitzCache and it's very helpful, I ran this command:

Sp_BlitzCache @SortOrder = 'duration';
GO

How do you relate the query with a SPID as I found the query with the longest duration? So I would know the culprit.

1 Answer 1

4

spidless

SPIDs are not cached with the query plan because that query has finished running.

At the point the plan is cached, that information is entirely irrelevant, and multiple SPIDs may have executed the query.

Things like login name, host, and other session specific context information aren't cached with a query plan, because it would be far less reusable in the future if it relied on all that being matched.

It seems like you might be trying to find queries that are currently long running, which you'd wanna use sp_WhoIsActive, or sp_BlitzWho for.

3
  • Thanks Erik, I understand that SPIDS are current and many could have issued that query. I believe in our case, it's just one global user as it's vendor based. It seems that all of the columns returned are query plan pertinent for sp_BlitzCache. I will look into the sp_BlitzWho, but the queries happened in the past.
    – Dennis M.
    Sep 16, 2021 at 20:03
  • 1
    @DennisM. It doesn't matter if it's the same user, on the same computer, and at the same time ran the query twice. Almost certainly the SPID would be different in both instances of that query running by that user. The SPID stands for Server Process ID and is literally a unique ID of a currently running process in SQL Server. Think of it like when you run a program on a computer in Windows, and the Task Manger gives that running program a process ID which is unique and temporary for only while that process is running and will be different the next time it's ran.
    – J.D.
    Sep 17, 2021 at 12:27
  • @J.D., Thanks. Sorry, I kept saying SPID, I'm actually looking for the user that executed the slow query, so if this user executes/cancels multiple times, yes, it would generate different SPIDs. sp_WhoisActive or sp_BlitzWho is a modified version of sp_who/sp_who2. It's just that tickets are submitted after the fact and if it would be nice if it's actually occurring so I can use these tools.
    – Dennis M.
    Sep 17, 2021 at 17:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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