• State Suggested Answer
  • Replies 3 replies
  • Answers 1 answer
  • Subscribers 60 subscribers
  • Views 1085 views
  • Users 0 members are here
Options
Related

Why does Foglight kill SPIDS

gmoss
4 months ago

I noticed that SPIDS were being killed on a regular basis although the timing was Intermittent. There appears a record in the SQL Server errorlog which reads as follows: Process ID 121 was killed by hostname xxxxx-SPOTL-APP, host process ID 0.

After some investigation I was able to see the process being killed was the "SQL Server Performance Investigator".

Question #1 Why is the application killing its threads/queries? Should the application not manage its connections and queries through the API rather than forcibly killing a SPID?

Question #2 It looks like the input buffer shows that the Foglight application is running queries that may have been written for SQL Server 2005/2008. Has Foglight updated their queries to adjust for DMV's and other changes that have occurred from the 2005 version to 2019 version, as that is the version of SQL Server we are running?

Question #3 What algorithm is used to determine how, when and why a SPID is killed? 

Question #4 Is this documented anywhere??

any help is appreciated!!!

Thanks,
Greg Moss


  • 0 4 months ago

    Hi Greg,

    This is an odd one and first I've heard of it. I added "killed" to my error log match list for the instance the has the Foglight repository DB, and sure enough...

    Is the host "xxxxx-SPOTL-APP" the agent manager by chance?
    I can see the spid that was killed (121) was blocked just before the kill.. I wonder if it's the watchdog process that is doing it. But, I would log a support case to investigate. I don't have a good answer right now.

  • 0 4 months ago

    Hi Greg,

    Foglight killing sessions in the monitored host is a normal process. The database agent will periodically clean up its own idle sessions. DBSSSessionCleanerProcessor is the process that does this work, and should be listed with the session ID in the database agent log file.

    There are also scenarios in which duplicate monitoring can kill sessions or they can be manually killed by users using the Sessions panel.

    Knowledgebase article  4373370 details each scenario. Based on your description though, it seems like the Session Cleaner processing is happening with the agent.

    John

  • 0 2 months ago

    I saw this too on one SQL Server instance, and was intrigued.  

    The KILLing of processes is something I do with a lot of care and not something that should be done a regular basis.  Just wait till it causes a dump.  It's not something I would consider normal.

    This is what runs right before the kill:

    --    SQL Server 2000

    /********************************************************************************************************************/
    /*    Functionality:        This SQL returns a record set of the SQL Server instance open sessions that share the        */
    /*                      current session's program name and host name.                                               */
    /*                      The purpose is to fetch all the sessions that were opened by the monitored DB agent.        */
    /********************************************************************************************************************/

    Select  T2.spid
    From    sysprocesses As T1 INNER JOIN sysprocesses As T2
            ON T1.program_name = T2.program_name
            And T1.hostname = T2.hostname
            And T1.spid = @@SPID



    I agree with all the questions Greg has above.

    Addiditonally, if the monitoring tool was given the least privledge, then it should not have the power to run a KILL