Side-by-side migration in the context of SQL Server using Backup and restore
This blog is to learn and share SQL DBA and Azure SQL knowledge among people by Bandaru Ajeyudu & Sudheer Thota
Capture all SQL and Stored Proc calls using Extended Events in SQL Server
-- Capture all SQL and Stored Proc calls
-- Part of the SQL Server DBA Toolbox at https://github.com/DavidSchanzer/Sql-Server-DBA-Toolbox
-- This script creates an Extended Events session called "CaptureAllSQLAndStoredProcCalls" that includes the following events:
-- error_reported (for severity 20 and above, as well as certain error numbers)
-- existing_connection
-- login
-- logout
-- rpc_completed
-- sql_batch_completed
CREATE EVENT SESSION [CaptureAllSQLAndStoredProcCalls]
ON SERVER
ADD EVENT sqlserver.error_reported
(ACTION
(
package0.callstack,
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_name,
sqlserver.nt_username,
sqlserver.session_id,
sqlserver.session_nt_username,
sqlserver.sql_text,
sqlserver.tsql_stack,
sqlserver.username
)
WHERE (
[severity] >= (20)
OR
(
[error_number] = (17803)
OR [error_number] = (701)
OR [error_number] = (802)
OR [error_number] = (8645)
OR [error_number] = (8651)
OR [error_number] = (8657)
OR [error_number] = (8902)
OR [error_number] = (41354)
OR [error_number] = (41355)
OR [error_number] = (41367)
OR [error_number] = (41384)
OR [error_number] = (41336)
OR [error_number] = (41309)
OR [error_number] = (41312)
OR [error_number] = (41313)
)
)
),
ADD EVENT sqlserver.existing_connection
(ACTION
(
package0.event_sequence,
sqlserver.client_hostname,
sqlserver.session_id
)
),
ADD EVENT sqlserver.login
(SET collect_options_text = (1)
ACTION
(
package0.event_sequence,
sqlserver.client_hostname,
sqlserver.session_id
)
),
ADD EVENT sqlserver.logout
(ACTION
(
package0.event_sequence,
sqlserver.session_id
)
),
ADD EVENT sqlserver.rpc_completed
(SET collect_statement = (1)
ACTION
(
package0.event_sequence,
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_name,
sqlserver.nt_username,
sqlserver.session_id,
sqlserver.session_nt_username,
sqlserver.sql_text,
sqlserver.tsql_stack,
sqlserver.username
)
WHERE ([package0].[equal_boolean]([sqlserver].[is_system], (0)))
),
ADD EVENT sqlserver.sql_batch_completed
(ACTION
(
package0.event_sequence,
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_name,
sqlserver.nt_username,
sqlserver.session_id,
sqlserver.session_nt_username,
sqlserver.sql_text,
sqlserver.tsql_stack,
sqlserver.username
)
WHERE ([package0].[equal_boolean]([sqlserver].[is_system], (0)))
)
ADD TARGET package0.event_file
(SET filename = N'c:\temp\CaptureAllSQLAndStoredProcCalls.xel')
WITH
(
MAX_MEMORY = 16384KB,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 5 SECONDS,
MAX_EVENT_SIZE = 0KB,
MEMORY_PARTITION_MODE = PER_CPU,
TRACK_CAUSALITY = ON,
STARTUP_STATE = OFF
);
GO
Here are key points about the buffer cache in SQL Server:
The buffer cache in SQL Server is a part of the SQL Server memory architecture that is responsible for caching database pages in memory. When SQL Server reads data from a disk, it stores a copy of that data in the buffer cache. Subsequent queries that need the same data can then be served from the in-memory buffer rather than reading from disk, which is significantly faster.
Here are key points about the buffer cache in SQL Server:
Buffer Pool:
The buffer cache is often referred to as the "buffer pool" or "data cache." It is a region in the SQL Server memory space dedicated to storing data pages.
Pages and Extents:
SQL Server divides its storage into fixed-size pages (usually 8 KB). These pages are grouped into larger structures called extents. The buffer cache holds these pages in memory.
Data Access:
When a query needs data, SQL Server first checks if the required pages are already in the buffer cache. If the data is present, it's called a "cache hit," and the data can be retrieved quickly from memory. If not, it's a "cache miss," and the data must be read from disk.
Read-Ahead Mechanism:
SQL Server uses a read-ahead mechanism to anticipate and pre-fetch pages into the buffer cache before they are needed. This helps to minimize the impact of physical I/O on query performance.
LRU (Least Recently Used) Algorithm:
The buffer cache uses an LRU algorithm to manage the contents of the cache. When the cache becomes full, pages that haven't been used recently are candidates for removal to make room for new pages.
Dirty Pages and Checkpoints:
When modifications are made to data in the buffer cache, the modified pages become "dirty." SQL Server periodically writes these dirty pages back to the data files during a process called a checkpoint. This ensures that changes are persisted to disk.
Monitoring Buffer Cache:
Performance monitoring tools and DMVs (Dynamic Management Views) can be used to monitor the state of the buffer cache. For example, the sys.dm_os_buffer_descriptors view provides information about the pages currently in the buffer cache.
SELECT * FROM sys.dm_os_buffer_descriptors;
Configuring Buffer Cache:
SQL Server provides configuration options for the size and behavior of the buffer cache. The "max server memory" configuration option limits the amount of memory that SQL Server can use for the buffer cache.
sp_configure 'max server memory', <value>;
The buffer cache plays a crucial role in optimizing SQL Server performance by reducing the need to perform expensive disk I/O operations. Properly configuring and monitoring the buffer cache is important for maintaining optimal database performance.
Here are common SQL Server performance issues and potential solutions:
SQL Server performance issues can arise for various reasons, and resolving them often involves identifying bottlenecks, optimizing queries, and configuring the server appropriately. Here are common SQL Server performance issues and potential solutions:
High CPU Usage:
Issue: Excessive CPU utilization.
Solutions:
Identify and optimize poorly performing queries.
Consider adding indexes to improve query performance.
Scale up resources (CPU, memory).
Review and adjust the SQL Server configuration for parallelism.
Memory Pressure:
Issue: Insufficient available memory for SQL Server.
Solutions:
Configure SQL Server memory settings appropriately.
Identify and optimize memory-consuming queries.
Monitor and adjust memory-related configuration settings.
I/O Bottlenecks:
Issue: Slow disk I/O affecting query performance.
Solutions:
Optimize queries to reduce I/O load.
Consider adding more/faster disks.
Use storage with higher IOPS capabilities.
Review and optimize file placement, such as database and log files.
Blocking and Deadlocks:
Issue: Transactions waiting on locks, leading to performance degradation.
Solutions:
Optimize queries and transactions.
Use appropriate isolation levels.
Monitor and identify blocking using tools like SQL Server Profiler.
Implement proper indexing.
Inefficient Query Plans:
Issue: SQL Server generates suboptimal query execution plans.
Solutions:
Update statistics to ensure accurate query plans.
Use index hints to force specific indexes.
Rewrite queries to improve performance.
Indexing Issues:
Issue: Missing or poorly designed indexes.
Solutions:
Regularly analyze and create missing indexes.
Remove unnecessary indexes to improve write performance.
Use the Database Engine Tuning Advisor (DTA) to recommend index changes.
TempDB Contention:
Issue: High contention in the TempDB database.
Solutions:
Split TempDB data files equally across multiple disks.
Adjust the number of TempDB files based on CPU cores.
Monitor and optimize queries that heavily use TempDB.
Out-of-date Statistics:
Issue: Query optimizer relies on outdated statistics.
Solutions:
Regularly update statistics on tables and indexes.
Consider enabling the AUTO_UPDATE_STATISTICS database option.
Network Latency:
Issue: Slow communication between the application and the database.
Solutions:
Optimize network infrastructure.
Use the appropriate network protocols.
Consider deploying closer Azure regions for Azure SQL Database.
Fragmented Indexes:
Issue: Fragmentation affecting index scan/seek performance.
Solutions:
Regularly rebuild or reorganize fragmented indexes.
Monitor index fragmentation using DMVs.
Long-running Queries:
Issue: Queries taking too long to execute.
Solutions:
Optimize queries using proper indexing.
Use execution plans to identify and address performance bottlenecks.
Insufficient Server Resources:
Issue: Not enough CPU, memory, or disk resources.
Solutions:
Consider upgrading hardware or moving to a larger VM size.
Optimize queries to use resources more efficiently.
Regular monitoring, proper configuration, and ongoing performance tuning are essential for maintaining optimal SQL Server performance. It's often helpful to use tools like SQL Server Profiler, SQL Server Management Studio (SSMS), and dynamic management views (DMVs) to diagnose and address performance issues. Additionally, regularly reviewing and implementing best practices for SQL Server performance can help prevent and mitigate potential problems.
Version control in SQL Server
Version control in SQL Server refers to the practice of managing and tracking changes to database objects, such as tables, views, stored procedures, and functions, over time. Using version control helps in maintaining a history of changes, collaborating with multiple developers, and rolling back to previous states if needed. Here are common approaches and tools for version control in SQL Server:
- Scripting and Source
Control Systems:
- Manual Scripting: Developers manually create and
maintain SQL scripts for database objects. These scripts are then stored
in a version control system such as Git.
- Source Control Integration: Many version
control systems offer integrations with SQL Server Management Studio
(SSMS) or other database development tools. Developers can directly
commit changes to version control from within the tool.
- Database Projects in
Visual Studio:
- SQL Server Data Tools (SSDT): Visual Studio
includes a project type known as SQL Server Data Tools, which allows
developers to create and manage database projects. These projects can be
version-controlled using Git, TFS (Team Foundation Server), or other
source control systems.
- Migrations and Change
Tracking:
- Database Migrations: Tools like
FluentMigrator, DbUp, or Entity Framework Migrations can be used to
create scripts that represent changes to the database schema. These
scripts can be version-controlled and applied in a structured manner.
- Change Tracking: SQL Server has built-in
features like Change Data Capture (CDC) and Change Tracking that can help
track changes to data. While not a complete version control solution,
these features complement version control practices.
- Third-Party Tools:
- Redgate SQL Source Control: This tool
integrates with SSMS and supports popular version control systems. It
allows developers to link databases to version control repositories and
track changes.
- Liquibase and Flyway: These are
database migration tools that support version control for databases. They
use scripts or configurations to manage changes and can be integrated
with source control systems.
- Git Hooks and
Database CI/CD:
- Git Hooks: Pre-commit and post-commit
hooks in Git can be used to automate checks and tasks related to version
control, such as running tests, enforcing coding standards, or triggering
continuous integration (CI) builds.
- Database CI/CD: Implementing a continuous
integration and continuous delivery (CI/CD) pipeline for databases helps
automate the process of deploying database changes from version control
to different environments.
When implementing
version control for SQL Server, it's essential to establish best practices,
including documentation, naming conventions, and a clear process for branching
and merging. Regularly syncing the database schema with version control and
ensuring that changes are traceable are critical aspects of effective version
control practices.
Isolation levels in SQL Server
Isolation levels in SQL Server define the degree to which one transaction must be isolated from resource or data modifications made by other transactions. SQL Server supports several isolation levels, each providing a different level of consistency, concurrency, and isolation. The isolation levels in SQL Server are defined by the SQL standard and include the following:
- READ UNCOMMITTED:
- Description: Allows a transaction to read
data that is being modified by another transaction without waiting for
the other transaction to complete.
- Issues: Non-repeatable reads, dirty
reads, and phantom reads are possible.
- READ COMMITTED:
- Description: Ensures that a transaction
reads only committed data. It prevents dirty reads but still allows
non-repeatable reads and phantom reads.
- Issues: Non-repeatable reads and
phantom reads are possible.
- REPEATABLE READ:
- Description: Ensures that if a transaction
reads a value, it will get the same value if it reads it again within the
same transaction. It prevents dirty reads and non-repeatable reads but
allows phantom reads.
- Issues: Phantom reads are possible.
- SNAPSHOT:
- Description: Allows a transaction to read a
version of data as it existed at the start of the transaction. This
provides a consistent snapshot of the data for the duration of the
transaction.
- Issues: Avoids dirty reads,
non-repeatable reads, and phantom reads.
- SERIALIZABLE:
- Description: Provides the highest level of
isolation. It ensures that transactions are completely isolated from one
another. It prevents dirty reads, non-repeatable reads, and phantom
reads.
- Issues: Increased contention and
potential for slower performance due to locks.
The isolation level can
be set for a session using the SET TRANSACTION ISOLATION
LEVEL statement. For example:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
it's
important to note that selecting a higher isolation level typically comes with
an increased risk of performance issues, such as blocking and decreased
concurrency. Developers and database administrators need to carefully choose
the appropriate isolation level based on the requirements of the application
and the specific trade-offs they are willing to make in terms of consistency
and performance.
Common SQL Server wait types
In SQL
Server, wait types are events or conditions that cause a task (such as a query
or a process) to wait for a specific resource or event to be available before
it can continue processing. Monitoring and analyzing wait types can help
identify performance bottlenecks and optimize the database system.
Here are some common SQL
Server wait types:
- PAGEIOLATCH_XX:
- Description: Indicates that a process is
waiting for a data page to be read from disk into memory.
- Possible Causes: Slow I/O subsystem, high disk
latency.
- CXPACKET:
- Description: Related to parallel query
execution. Indicates that a parallel query is waiting for another thread
to complete its work.
- Possible Causes: Overloaded parallelism, uneven
workload distribution.
- LCK_XX:
- Description: Indicates a process is waiting
to acquire a lock on a resource.
- Possible Causes: Contentious locks due to high
concurrency.
- ASYNC_NETWORK_IO:
- Description: Indicates a task is waiting for
network packets to be sent or received.
- Possible Causes: Slow or congested network.
- WRITELOG:
- Description: Indicates a process is waiting
for a log flush to complete.
- Possible Causes: High transaction log activity,
slow disk write performance.
- SOS_SCHEDULER_YIELD:
- Description: Indicates that a task
voluntarily yielded the scheduler to let other tasks run.
- Possible Causes: High CPU usage, resource
contention.
- PAGE_VERIFY:
- Description: Indicates a task is waiting for
a page verification operation to complete.
- Possible Causes: Configuring database option
CHECKSUM and experiencing high I/O.
- OLEDB:
- Description: Indicates a task is waiting for
an OLE DB operation to complete.
- Possible Causes: Issues with external data
source or linked server.
- WAITFOR:
- Description: Indicates a task is waiting for
a specified amount of time to elapse.
- Possible Causes: Delays introduced in queries
using the WAITFOR statement.
Monitoring and analyzing
wait types can be done using dynamic management views (DMVs) such as sys.dm_os_wait_stats. By querying these views, you can
identify which wait types are causing the most contention and focus on
optimizing those areas for better performance. Additionally, tools like SQL
Server Profiler and Extended Events can be used for more in-depth analysis of
wait statistics.
What is Lazy Writer in SQL Server ?
The Lazy Writer is a background process responsible for managing the buffer pool's clean pages. The buffer pool is an area of memory where SQL Server caches data pages in order to reduce the need to read data from disk repeatedly.
The Lazy Writer works by moving aged and less frequently accessed data pages from the buffer pool to disk, ensuring that the buffer pool remains efficient and has space for new data pages that are more actively used. This process is essential for maintaining the overall performance of the SQL Server database.