Microsoft released an update to CU6 (link) for SQL Server 2014 SP1; this update was to address an issue where even having NOLOCK hint in queries was leading to blocking and deadlocks in the default SQL Server lock-based isolation level or high levels. From the KB article:
Executing a parallelized SELECT (…) INTO Table FROM SourceTable statement, and specifically using the NOLOCK hint, under the default SQL Server lock-based isolation level or higher. In this scenario, other queries that try to access SourceTable will be blocked.
While one transaction is holding an exclusive lock on an object (for example, an ongoing table update), another transaction is executing parallelized SELECT (…) FROM SourceTable by using the NOLOCK hint. In this scenario, the SELECT query that is trying to access SourceTable will be blocked.
Reference: CU6 for SQL 2014 SP1 (Deprecated) [Link].
Therefore if you have the older build installed (12.0.4449) please update to newer build (12.0.4457).
This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.
Mohit,
You should probably blog about this for SQL2016, especially if you aren’t connected to the internet when you install https://blogs.msdn.microsoft.com/bobsql/2016/06/15/sql-2016install-msvc-patch-required/
Thanks Chris :).