July 2, 2014 at 1:26 pm
App developers are complaining that a blocking report I sent them today does not have enough info to help them troubleshoot and fix the root cause of the blocking.
(I used SQL Sentry to get the report and send to the users, but I think my question is more fundamental than just using one 3rd-party tool.)
The reason for the complaints is that the top blocker is a SELECT stmt on TableA that is not the Wait Resource of the blocking (TableB).
I explained that this is likely because the "blocking" SELECT stmt on TableA is part of a larger explicit tran that includes TableB, and that they need to look through their code to identify the SQL stmt that is the root cause of the blocking.
They would like that root statement identified in the blocking report, but I don't think it is possible to get to that through DMVs.
Any additional insights into this would be appreciated.
Thanks,
Marios
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 2, 2014 at 3:15 pm
Try sp_whoisactive. Be sure to look at all of it's parameters. INCREDIBLY powerful, and FREE!! Find it on SQLBlog.com, including a 30-day blog post series by creator Adam Machanic.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 3, 2014 at 3:55 am
Thank you, I know about sp_whoisactive, and I have used it in the past.
To be honest, I think this is more of a limitation of what info current DMVs (in SQL 2012) can provide, but I will try it in the next similar situation I encounter to see if that gives me more info.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 3, 2014 at 5:20 am
Perhaps this link has the answer:
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 3, 2014 at 7:17 am
I wouldn't be surprised to see that sp_WhoIsActive has all the relevant information from that post exposed. @get_full_inner_text, @get_outer_command, @get_locks, @get_additional_info, @find_block_leaders, @get_transaction_info. But if you have another solution crafted from that blog post you should be good to go!
You may want to consider getting with SQL Sentry on any functional lack their stuff has. They are really good about feedback and improvements, and then everyone could benefit from your recommendations! And if it truly is a limitation in the DMVs, send that over the wall to Microsoft via a Connect item.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 3, 2014 at 10:06 am
Thanks, I'll schedule a live session with SQL Sentry to get set up to better be able to capture these.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply