Managing Relational and
Non-Relational Data
SQL Server – Locking, concurrency and performance
Post-Graduation in Enterprise Data Science & Analytics
Agenda
• SQL Server Indexes
• Lock and Blocking
• SQL Server Isolation Modes (Additional Resources)
• DeadLocks (Additional Resources)
• Query Plans (Additional Resources)
• SQL Server Top New Features (Additional Resources)
2
Indexes Overview
• Index Considerations
• Can dramatically increase query performance
• Adds overhead for index maintenance
• Best Practices
• Base design on real-world workloads
• SQL Profiler, DMV’s, Execution Plans
• Scenarios:
• Retrieving ranges of data
• Retrieving specific values
3
SQL Server Index Types
• Clustered index
• Controls the physical order of rows
• Does not require disk space
• One per table (may include multiple columns)
• Created by default on tables’ Primary Key column
• Non-Clustered Index
• Physical data structures that facilitate data retrieval
• Can have many indexes
• Indexes may include many columns
• Uses cluster key or RID of a heap
4
Clustered Index vs Non-Clustered Index
5
SQL Server Statistics
• Information about the distribution of the data
• Created on index keys
• Created on columns
• Created manually
• Cardinality
• By default, created automatically
• By default, maintained automatically
• Automatic maintenance might not enough
6
SQL Server Statistics
• DBCC SHOW_STATISTICS(table, target)
• Header
• Density
• Histogram
7
Demo
Demo 1 – Clustered and Non-Clustered Indexes
8
Locks and Blocking
• SQL Server uses locks, an internal memory structure, to provide
transactional integrity between transactions.
• When locks are held for a long period of time, they cause blocking
• One process must wait for the other process to finish with the data and release the lock
before the second process can continue
• Lock types
• Shared - Used for read operations that do not change or update data, such as a SELECT
statement
• Update - Used on resources that can be updated.
• Exclusive -Used for data-modification operations, such as INSERT, UPDATE, or DELETE
• Intent - Used to establish a lock hierarchy
9
Lock Hierarchy
10
SQL Server – Row Update
USE AdventureWorks
GO
BEGIN TRANSACTION
UPDATE [Person].[Address]
SET AddressLine1 = 'Lisbon, Portugal'
WHERE AddressID = 2
-- COMMIT TRANSACTION
11
SQL Server – Lock Escalation
• When a single Transact-SQL (T-
SQL) statement acquires at least
5,000 locks on a single table or
index.
• When the number of locks in an
instance of the Database Engine
exceeds memory or configuration
thresholds.
• If locks cannot be escalated
because of lock conflicts, the
Database Engine periodically
triggers lock escalation at every
1,250 new locks acquired.
12
Viewing Blocking by Using DMVs
• sys.dm_tran_locks
• Replaces syslockinfo and sp_lock
• Each row has information about both the resource and the request
• request_status = WAIT implies blocking
• sys.dm_os_waiting_tasks
• blocking_session_id > 0
• sys.dm_exec_requests
• status = suspended
• blocking_session_id > 0
• sys.dm_exec_sessions
• Join with each of the above for session details (join on session_id)
13
Demo
Demo 2 – Locks and Blocking
14
Additional Resources
15
Concurrency Concepts
• Concurrency: The ability for multiple processes to access or change shared
data at the same time
• Isolation: Database property that determines how transaction integrity is
visible to other users or processes
• Usually, concurrency decreases with more restrictive isolation levels
ISOLATION
CONCURRENCY
16
Concurrency Issues
• Blocking
• Deadlock
• Dirty Read
• Non-Repeatable Read
• Phantom Read
17
SERIALIZABLE Isolation Level
• Equivalent to (HOLDLOCK) table hint
• Shared locks on read rows are held until the end of the transaction
• Guarantees that all reads are repeatable, and that no new data can be
inserted until the transaction completes
• To prevent phantom reads, key range locks are used
• On tables with index support, locks are acquired on all keys spanning the query predicate
plus the next key value
• On heaps, table locks are acquired
• Best used for replicating stored procedure calls
• Use with caution – this option will cause blocking
18
REPEATABLE READ Isolation Level
• Shared locks on read rows are held until the end of the transaction
• Guarantees that data rows previously read within a transaction will remain consistent
until the transaction ends
• Unlike SERIALIZABLE isolation level, shared locks are acquired only on rows read
• If a transaction reads a table multiple times, and another transaction inserts new rows to that table,
query results may be different
• Does not protect against phantom reads
• Best used when performing data changes while preventing changes to read data
rows
• Use with caution – this option will cause blocking
19
READ UNCOMMITED Isolation Level
• Provides minimal concurrency controls
• Exclusive locks are acquired for modified rows
• No shared locks are acquired on read rows
• When attempting to read a row with an exclusive lock on it, query will read uncommitted data (dirty read)
• Equivalent to (NOLOCK) table hint
• Ideal choice for
• Historical reports
• Troubleshooting issues on PROD databases
• Queries where data accuracy is not critical
• READ UNCOMMITED and (NOLOCK) are not silver bullets to solve blocking issues.
Use as needed and understand their side effects
20
READ COMMITED Isolation Level
• Default isolation level in SQL Server
• Exclusive locks are acquired for modified rows
• Concurrency controls on read rows vary depending on the READ_COMMITTED_SNAPSHOT database
option
• When OFF, shared locks are acquired while reading data rows and released when the read is complete
• When ON, no shared locks are acquired and instead row versioning is used for concurrency control
• Best general-purpose isolation level
• Avoid long running transaction
• Keep explicit transaction as short as possible
• Supplement with (NOLOCK) table hints to minimize blocking
21
SQL Server Isolation Levels Overview
Isolation level Dirty read Nonrepeatable read Phantom
Read uncommitted Yes Yes Yes
Read committed No Yes Yes
Repeatable read No No Yes
Serializable No No No
22
Demo
Demo 3 – Isolation Levels
23
Deadlock
• Two or more processes holding resources in a circular locking chain
• SQL Server will detect deadlocks (not just lock deadlocks)
• How to identify
• Client error message
• Profiler trace
• Xevent
• Trace Flag 1222
• Most likely a design issue
• Different processes accessing resources in different orders
• Unnecessarily long transactions
• Poorly tuned queries
24
Deadlock
25
Deadlock Graph – Profile Trace
26
Demo
Demo 4 – DeadLocks
27
Query Optimizer cost
SELECT * FROM
Sales.SalesOrderDetail
WHERE ProductID = 750
ORDER BY ProductID;
28
Demo
Demo 5 – Execution Plans
29
SSMS Reports
• CPU bottlenecks (and what queries are consuming the most CPU)
• IO bottlenecks (and what queries are performing the most IO)
• Index recommendations generated by the query optimizer
(missing indexes)
• Blocking
• Latch contention
30
Demo
Demo 6 – SSMS Performance Dashboard
31
SQL Server 2014 – Top New Features
• In-Memory OLTP Engine
• Memory optimization of selected tables and stored procedures (up to 30x
faster)
• AlwaysOn Enhancements
• Secondary replicas from four to eight.
• Asynchronous secondary replicas in Windows Azure.
• Offload reporting activity to Azure
32
SQL Server 2014 – Top New Features
• Updateable Columnstore Indexes
• Delayed durability
• Buffer Pool Extension (using SSD)
• SQL Server Managed Backup to Windows Azure
33
SQL Server 2016 – Top New Features
• Query Store
• Maintains a history of query execution plans with their performance data
• Polybase
• SQL Server connector to Hadoop (and Azure Blob Storage)
• Stretch Database (SQL Azure)
• JSON Support
34
SQL Server 2016 - SQL Server Query Store
• Tracks:
• Query plans
• Runtime statistics
• Queries/plans history
• Helps find regressing queries
• Quickly find new queries with multiple plans
• Identify un-efficient plans so you can force a better plan
35
SQL Server 2016 - SQL Server Polybase - Hadoop
36
SQL Server 2016 – Top New Features
• In-Memory tables improvements
• Supporting foreign keys, check and unique constraints and parallelism.
• Column store indexes support
• Dynamic Data Masking(DDM)
• R Comes to SQL Server
37
SQL Server 2017 – Top New Features
• Python Support
• Scaling Out Integration Services
• Graph Database
• A Graph database allows you to store nodes and edges.
• Adaptive Query Plans
• Power BI reports in a SSRS instance
• SQL Server Runs on Windows, Linux and Docker Containers
38
SQL Server 2019 – New Features
• SQL Server Database Engine
• Java language programmability extension
• SQL Server Machine Learning Services failover clusters
• New Polybase connectors
• New connectors for SQL Server, Oracle, Teradata, and MongoDB
• SQL Server on Linux
• Machine Learning on Linux
• Replication support
• Support for the Microsoft Distributed Transaction Coordinator (MSDTC)
• Always On Availability Group on Docker containers with Kubernetes
39
SQL Server 2019 – New Features
• SQL Server 2019 Big Data Clusters
• Deploy a Big Data cluster with SQL Server and Spark Linux containers on
Kubernetes
• Access your big data from HDFS
• Run Advanced analytics and machine learning with Spark
• Use Spark streaming to data to SQL data pools
40
SQL Server 2019 – New Polybase connectors
41
SQL Server 2019
42
SQL Server 2019
43
SQL Server 2022
Azure Purview integration Seamless analytics over on-prem
operational data
Business continuity through Azure
44
Summary
• SQL Server Indexes
• Lock and Blocking
• SQL Server Isolation Modes
• DeadLocks
• Query Plans (Additional Resources)
• SQL Server Top New Features (Additional Resources)
45
Obrigado!
Morada: Campus de Campolide, 1070-312 Lisboa, Portugal
Tel: +351 213 828 610 | Fax: +351 213 828 611
