July 4, 2013 at 9:27 am
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 :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 :ermm:
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
July 4, 2013 at 9:42 am
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
July 5, 2013 at 2:21 am
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 :ermm:
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply