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.
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]
WHERE referenced_object_id = OBJECT_ID('Person.Person');
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.
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.