In this article, we will learn how to monitor SQL Server blocking issues with different methods.
Read more »
Locking
Modes of Transactions in SQL Server
February 17, 2021In this article, we are going to talk about the modes of transactions in SQL Server.
Read more »
Transactions in SQL Server for beginners
February 10, 2021In this article, we will talk about fundamental details of the transactions in SQL Server.
Read more »
Explore the SQL query table hint READPAST
November 14, 2020SQL Server creates an optimized execution plan based on the available inputs such as statistics, indexes. By default, it chooses a cost-optimized execution plan and executes the query. Sometimes, we use SQL queries table hints to override the default mechanism. Developers popularly use WITH (NOLOCK) query hint in a Select statement to avoid blocking issues.
Read more »
Dirty Reads and the Read Uncommitted Isolation Level
June 22, 2020In this article, we will discuss the Dirty Read concurrency issue and also learn the details of the Read Uncommitted Isolation Level.
Read more »
An overview of sp_getapplock and sp_releaseapplock stored procedures
January 9, 2020This article explains about the sp_getapplock and sp_releaselock stored procedures and their usage with example.
Read more »
Resolve and Troubleshoot SQL Blocking chains with root sessions
November 5, 2019In this article, we will study how to recognize and resolve the SQL blocking chain by determining and troubleshooting the root cause.
Read more »
SQL Server NOLOCK and Top Optimization
April 12, 2018Sometimes people use nolock hint as a “turbo” button for their queries, assuming that not taking locks will speed up the query execution. There are many good articles describing all the dangerous moments of this approach, because of the read uncommitted isolation level. However, the focus of this article is a performance problem that you may encounter using nolock hint in some cases.
Let us setup some test data first.
Read more »
How to control online Index Rebuild Locking using SQL Server 2014 Managed Lock Priority
July 18, 2017When you perform a SQL Server Online Index Rebuild operation, introduced for the first time in SQL Server 2005, the index will not be taken down. But at a specific point, in which the new index new is built and switched from the old structure of the index, a special kind of lock, Schema Modification (SCH-M), will be granted. This lock may cause blocking if your database server is busy.
Read more »
All about locking in SQL Server
June 16, 2017Locking is essential to successful SQL Server transactions processing and it is designed to allow SQL Server to work seamlessly in a multi-user environment. Locking is the way that SQL Server manages transaction concurrency. Essentially, locks are in-memory structures which have owners, types, and the hash of the resource that it should protect. A lock as an in-memory structure is 96 bytes in size.
Read more »