Lock Escalation in the SQL Engine

Locking is an essential part of upholding data integrity. Locks are 96 byte in-memory structures that are especially essential when it comes to the isolation requirement in ACID compliancy.

However there is a draw back to having transactions doing too many locks, and that is it can be quite memory intensive if a single query is acquiring many locks. In order to reduce system overhead, SQL Server goes and does what is called lock escalation.

What is Lock Escalation?

Lock escalation is the process of converting many fine-grained low level locks (e.g row/page locks) into table level locks. This reduces system overhead but increases the possibility for concurrency contention, so it’s a bit of a double edged sword in that we reduce memory pressure but now we may have to deal with resource contention.

Lock escalation applies to SQL Server, Azure SQL Database, Azure SQL Managed Instance and Azure Synapse Analytics.

Advertisement

How to find all the Foreign Keys that are referencing a Table?

At some point you might find yourself in a situation where you need to figure out how many foreign keys are referencing a certain table.

In this blog post I discuss two ways to achieve this:
1. Querying the sys.foreign_keys system table
2. Executing the sp_fkeys system stored procedure

Querying the sys.foreign_keys system table

As the name implies, the sys.foreign_keys table contains information regarding foreign key constraints. The table contains a column named ‘referenced_object_id’ that is useful to query to find which foreign key constraints are referencing a certain table.

Using AdventureWorks2019 database as an example, I query the sys.foreign_keys table to find all the foreign keys that reference the table ‘Person.Person’.

SELECT name AS [Foreign Key],
SCHEMA_NAME(schema_id) AS [Schema],
OBJECT_NAME(parent_object_id) AS [Table]
FROM sys.foreign_keys
WHERE referenced_object_id = OBJECT_ID('Person.Person');

How to Identify Unused Indexes

Indexes are database objects that are useful for when it comes to improving your SQL Server performance. The more indexes you have, the more overhead you add for maintaining those indexes.

Part of doing a SQL Server health check, is to identify if there are any unused indexes. Having those indexes around is likely doing more harm than good when it comes to performance.

To find out if an index is not used in our environment, we need to check if there have been any SCANS, SEEKS or LOOKUPS on the index since the SQL Server started. We can use the Dynamic Management View (DMV) named sys.dm_db_index_usage_stats to help us here.

I created the script below to find all unused non-clustered indexes in a SQL Server database.

Viewing Execution Plans in SSMS

When doing query tuning, interrogating the execution plan for the query can be useful for getting insights into improving the query.

There is actually different types of execution plans and a number of ways to retrieve those plans.

We can get plans from SQL Server Management Studio (SSMS) interface, the plan cache, the query store, the profiler and/or extended events. However, in this article we are going to focus on how to retrieve execution plans from the SSMS interface.

Difference between UNION and UNION ALL

This week it’s going to be a short blog post, touching on the difference between using UNION and UNION ALL operators in SQL Server.

UNION and UNION ALL are set operators that we use to concatenate two data sets together.

To showcase their differences I created two tables, Table1 and Table2, naming was never my strong point. These tables hold random first and last names.

SELECT *
FROM Table1