SQL Server table partitioning is an invaluable feature for improving database performance and management, especially for large-scale databases. This blog post provides an overview of setting up and managing partitioned tables in SQL Server, using TSQL commands. Partitioning helps manage large tables by dividing them into smaller, more manageable segments known as partitions. Each partition can be stored on a separate filegroup, enhancing query performance and simplifying maintenance tasks such as backups and index rebuilds.
Category: Storage Engine
SQL Server Performance Tuning: Dealing with Last Page Contention
Last Page Insert Contention, commonly referred to as “last page contention,” is a specific type of contention in SQL Server related to the last page of an index, typically a clustered index. This happens because SQL Server uses a mechanism where multiple sessions try to insert records on the last page of an index, leading to contention.
Understanding and Diagnosing SQL Server Wait Types
In SQL Server, wait types are important indicators of where and why delays occur during query execution, directly impacting database performance. Properly understanding and diagnosing these wait types enable database administrators to pinpoint specific performance bottlenecks. This blog post aims to provide an overview of essential wait types that demand attention, distinguish common wait types that can usually be ignored, and offer strategies for effective diagnosis and interpretation to enhance overall database performance.
SQL Server 2022 Enhancement for Accelerated Database Recovery
With its debut in SQL Server 2019, Accelerated Database Recovery (ADR) represents a paradigm shift in database recovery and performance enhancement. The primary objective of ADR is to drastically cut down the time databases require to recover from crashes, failures, or restarts. This is particularly important in environments where long-running transactions are common, ensuring that databases remain available and performant, even in the face of unexpected disruptions.
The Power of Snapshots: Exploring Copy-On-Write in SQL Server
“Copy-On-Write” (COW) is a resource management technique used in computer programming and operating systems. Its application in SQL Server, particularly in relation to snapshot technologies, is a fundamental concept behind the functioning of database snapshots and certain types of backups. Let’s delve into the detailed workings of Copy-On-Write in the context of SQL Server:
Speed, Efficiency, and VLDB Backups: Making the Right Moves
Today we will discuss VLDB backups. Handling VLDBs can be challenging for many administrators. Sometimes just completing a full backup in a reasonable amount of time is challenging, not to mention restoring a database to meet your organization’s service level agreements (SLA). There are many options to consider. Some options exist outside of the BACKUP DATABASE statement. They key to improving database backups is to adjust various configurations to increase throughput, increase concurrency, and reduce the amount of data to backup.
Implementing Row-Level Security in SQL Server
Row-level security (RLS) in SQL Server is a feature that allows database administrators to control access to rows in a database table based on the characteristics of the users accessing them. This can be particularly important in environments where data privacy and security are critical. In this blog post, we’ll explore how to implement RLS in SQL Server.
DBA to the Rescue: Mitigating the Impact of SQL Server Corruption
Let’s talk about the boogeyman of our database world – corruption. Not the kind that lands you in headlines, but the sort that can turn a perfectly tuned database into a digital wasteland overnight. It lurks in the shadow of failing hardware, creeps behind the veil of sudden power losses, and sometimes piggybacks on the most innocent-looking software bugs.
SQL Server Transaction Logs: Ensuring Consistency and Recovery
The SQL Server Transaction Log isn’t just a record of database changes; it’s the heart of SQL Server’s ability to recover from unexpected issues and failures. Each entry in the log is a testament to SQL Server’s commitment to data consistency, durability, and atomicity. This means that every action you take is not only recorded but is also designed to be reversible, allowing the system to maintain its state even under unforeseen circumstances.