Identify the cause of SQL Server blocking

In my previous article (Different techniques to identify blocking in SQL Server) on MSSQLTips.com, I discussed about locks and blocks, and presented you with an overview on how to troubleshoot and resolve blocks using dynamic management views and Activity Monitor. After I wrote this article, I received several emails from readers asking how they can use the information returned by these dynamic management views (DMVs) to identify SPIDs and other useful information about the processes that are actually causing blocking on a SQL Server instance.

My Book

SQL Server 2014 Development Essentials (ISBN: 978-1782172550) is an easy-to-follow yet comprehensive guide that is full of hands-on examples. This book will provide you with all the skills you need to successfully design, build, and deploy databases using SQL Server 2014. Starting from the beginning, this book gives you an insight into the key stages of the SQL Server database process, provides you with an in-depth knowledge of the SQL Server database architecture, and shares tips to help you design the new database.

By sequentially working through the steps in each chapter, you will gain hands-on experience in designing, creating, and deploying SQL Server databases and objects. You will learn how to use SQL Server 2014 Management Studio and the advanced Transact-SQL queries to retrieve data from the SQL Server database. You will also learn how to add, modify, and delete data stored within a database. You will use Transact-SQL statements to create and manage advanced database objects that include scalar and table-valued functions, views, stored procedures, and triggers. Finally, you will learn about how the SQL Server 2014 relation engine works, how indexes and statistics improve query performance, and the new SQL Server 2014 in-memory technologies.

Email Subscription

Click to subscribe to this blog and receive notifications of new posts by email.

Join 570 other followers

Copyright

All of the entries on this blog are copyright by Basit Farooq. The full content of any post (a post is an entry on the blog) may not be published elsewhere without prior permission from the copyright holder. Excerpts from blog posts may be posted elsewhere providing they are short (around 10% of the article) and are attributed to me by name and with a link back to this blog, currently located at the following address: http://basitaalishan.com.

The following sites currently have full permission to publish complete copies of posts that are published on this web blog:

Disclaimer

This is personal blog and opinions expressed here represent my own thoughts and not those of my employer. For accuracy and official references, refer to MSDN, Microsoft TechNet, Books Online. I or my employer do not endorse any of the tools / applications / books / concepts mentioned here on my blog. I've simply documented my personal experiences on this blog.