Session 03: Dr. DMV: How to Use DMVs to Diagnose Performance Problems

Audience Questions

 * Q: I have 2 long running select queries that intersect each other at mid-night, and sometimes 1 becomes deadlock victim and gets killed by SQL Server. These 2 queries are only queries running at this time (no other insert/update/delete going on). I don't understand why SELECGT query (shared lock) causes deadlock. Is there any way I can use DMV to get more info on the deadlock that already occurred? Thanks.
 * A: You won't be able to do that from the DMVs. You can get that info from other means depending on your version of SQL Server. If you are running 2008 or R2, I recommend you alter the system_health Extended Event session to add a file target. If 2012 or later, then the file target is there. You can pull the deadlock info more reliably for historical deadlocks from the file target in that session.


 * Q: will the examples and scripts presented by Glenn be shared for our test and practice?
 * A: The DMV Scripts are currently available on his Blog. Demos are generally made available after the event concludes.


 * Q: Will the slides of the session available?
 * A: Slides and demos should be made available after 24HOP concludes. Here is a link to the queries themselves. http://www.sqlskills.com/blogs/glenn/sql-server-diagnostic-information-queries-for-september-2016/


 * Q: Can you please share Glenn's blog link
 * A: http://www.sqlskills.com/blogs/glenn/sql-server-diagnostic-information-queries-for-september-2016/


 * Jason Brimhall (to All - Entire Audience):
 * 10:19 AM: Powerpoint for Glenns Session is available here http://www.sqlpass.org/24hours/2016/summitpreview/Sessions/Details.aspx?sid=53724

http://www.sqlskills.com/blogs/glenn/sql-server-diagnostic-information-queries-for-september-2016/ Powerpoint http://www.sqlpass.org/24hours/2016/summitpreview/Sessions/Details.aspx?sid=53724
 * Q: Where can we get the TSQL queries that Glenn is using?
 * A: DMV Scripts