SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Monitoring Blocking Issues


Monitoring Blocking Issues

Author
Message
craig.dixon
craig.dixon
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 177
Hi Everyone,

Firstly I'd like to wish you all a very happy 4th of July :-D 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
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34471 Visits: 16649
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
craig.dixon
craig.dixon
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 177
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search