9 Replies Latest reply on Jan 21, 2015 1:29 AM by spaceman

    Blocking is not showing correctly

    cwong


      I see in the performance monitor multiple blocked sessions.  I click on Current and see the currently blocked sessions have a number. I click on the link show blocked sessions but when it goes to that page it shows that no sessions are blocking.

       

      When I go to SSMS and check, I find that there are indeed blocked sessions, but DPA is not displaying the information under sessions.

       

       

      blocked sessions.png

       

      current.png

       

      sessions.png

        • Re: Blocking is not showing correctly

          I just spent a bit talking to support and architecture.  Don't have a sure answer yet.  Could you open a ticket with support so we can start gathering more info?

           

          Thanks!

            • Re: Blocking is not showing correctly

              One theory... Since you're able to identify the blocking processes in SSMS, are all of the SPIDs under 50?  If so, those are system spids which are filtered from some views.  That might explain this.

              • Re: Blocking is not showing correctly
                spaceman

                Hi, We're seeing a similar issue..

                1.bmp

                2.bmp

                In our case whenever this occurs the cause appears spids blocked by themselves (parallel processing threads). It appears to be inconsistent in the GUI if this is case - and (personally) i'd rather they were ignored on the currently blocked sessions area of Current activity as it's normal behaviour.

                (i think the self blocking spids are also included in Blocked Sessions graph)

                  • Re: Blocking is not showing correctly

                    Hey spaceman & cwong,

                     

                    I spoke to our support team manager.  He says he thinks these things can be timing/refresh issues.  The currently blocked sessions don't update instantly.  It only updates when the current page refreshes.  So it could say 2 blocking sessions for the interval between refreshes when in reality when they click on blocking, it will do a real-time ad hoc query to look, then see nothing.

                     

                    If you feel this is not what's happening for your case, I encourage you to open a support case.  We'd love to get a look at this and it may require more information than we should share on a public forum.

                      • Re: Blocking is not showing correctly
                        spaceman

                        Thanks Brian, i've run a script checking what i consider to be blockers polling every second for a minute and refreshed DPA several times in the same window... DPA showed various numbers blocked but these weren't reflected by my script.

                         

                        I'll raise a case

                          • Re: Blocking is not showing correctly
                            spaceman

                            Turns out the issue was only affecting a SQL2000 instance (yes, we still have one! )

                            No point in addressing it in DPA for such an old version so we've just added a custom metric instead and disabled the blocked sessions graph. It doesn't solve it in the Current activity view but we can live with it as we have the info from the custom metric in the sessions history.

                             

                            Custom metric SQL;

                             

                             

                            SELECT count (distinct p1.SPID) AS BlockedCount

                             

                            FROM master.dbo.sysprocesses p1 WITH (nolock)

                             

                             

                            WHERE p1.spid <> p1.blocked

                             

                            AND p1.blocked <> 0

                              • Re: Blocking is not showing correctly

                                Ahh!  Thanks for letting us know.  Not surprised you still have a SQL 2K.  I was reading a blog the other day that many chimed in on about why they still have a SQL 2K instance running.  It was a solid platform and some solutions built on it just can't justify a re-write for so many reasons which all stem from cost/benefit ratio equations.  One guy even said it had a behavior that was changed in 2005+ and his application requires that behavior to perform well.  A couple people even said they still have a SQL 7 and SQL 6.5 running.  WOW, right?!  Makes me wonder... if we look hard enough, could we find a Commodore 64 playing Summer Games.  ;-)