Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Monitoring Blocking Issues Expand / Collapse
Author
Message
Posted Thursday, July 4, 2013 9:27 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 8:56 AM
Points: 28, Visits: 163
Hi Everyone,

Firstly I'd like to wish you all a very happy 4th of July I hope you're all having a good day. I was wondering if anyone could help me with a problem I'm having with blocking on my servers. We have a constant stream of data coming in per minute from multiple gateways and at random times the data flow is blocked. The problem is that it resolves itself by the time I can get onto the system to run sp_who2 and see what's causing the problem. Its been driving me crazy The only reason I notice it happens is because I see no current data on the reports I have running on the screen in the office

Is there any type of monitoring approach someone could recommend that would find the root cause be it a bad trigger or query that is causing this? Would something like DBWarden help?

Thanks everyone
Kind Regards,
Craig
Post #1470469
Posted Thursday, July 4, 2013 9:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:17 AM
Points: 5,230, Visits: 9,457
Craig

Blocking shouldn't cause your reports to show the wrong (or no) data... unless there is a timeout on whatever application runs the reports. If that's the case, you might want to increase the timeout period. To investigate blocking, sp_who2 is a good place to start. If that doesn't give you enough information, you can go deeper into the internals with DMVs such as sys.dm_os_waiting_tasks. Adam Machanic's sp_who_is_active is also worth looking at.

John
Post #1470471
Posted Friday, July 5, 2013 2:21 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 8:56 AM
Points: 28, Visits: 163
Good Morning John,

Thanks for the quick reply I think the report is showing no current data because the alter table, insert into or updates are being blocked when the data first comes into my system. When the data comes in, it goes to the main database which is mirrored. It then gets replicated to another server where it is transferred to the data source for my reports. All of the records are time stamped and the report will show a record from each of my gateways with its associated value. I can see that the gateways are sending the data every minute and replication is working fine so I thought the best place to start was blocking as recently the amount of data we are managing has grown quite a bit

sys.dm_os_waiting_tasks sounds like a great place to look for some info after the fact, which is exactly what I was looking for. I'm also going to look into Adam Machanic's sp_who_is_active today when I get a chance

Thanks for your help, I really appreciate it I'll post an update when I get a chance in case anyone else is having a similar issue.

Kind Regards,
Craig
Post #1470624
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse