In the relentless pursuit of performance, database administrators and developers continually seek strategies to make applications faster and more efficient. SQL Server’s In-Memory Online Transaction Processing (In-Memory OLTP) feature is a significant stride in this quest. This blog post will dive into the world of In-Memory OLTP, exploring what it is, its benefits, how to get started, best practices, and conclude with some final thoughts.
Month: December 2023
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.
Balancing Workloads in SQL Server with Resource Governor
Today’s database landscape requires strategies for ensuring optimal performance and resource allocation for your workloads. SQL Server’s Resource Governor is a feature that often flies under the radar but can be essential for fine-tuning the performance of your SQL Server environment. This blog post aims to demystify the Resource Governor, explaining its functionality, utility, and walking you through a practical example of setting it up for different user groups.
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.
PowerShell for SQL Server: A Practical Introduction to Automation
Welcome to the wonderful world of automation with PowerShell for SQL Server! Whether you’re a database administrator or a developer, automating routine tasks can significantly enhance efficiency and accuracy. This guide is designed for beginners who are familiar with SQL Server but new to PowerShell. We’ll cover the basics and provide practical examples to help you get started with automating SQL Server tasks.
SQL Server AGs: Tackling Missing and Stale Statistics for Readonly Secondaries
In SQL Server’s Always On Availability Groups, understanding the behavior of statistics on secondary replicas is key to maintaining query performance. This post explores the challenges and solutions for managing statistics in read-only secondary databases and snapshots. We delve into two critical scenarios: the creation of statistics in secondary replicas when they are missing on the primary, and the handling of stale statistics due to differences in primary and secondary workloads.
Contained Databases in SQL Server: Improving Database Portability and Security
Contained databases in SQL Server signify a paradigm shift in the world of database management, marking a move towards more self-contained and autonomous database systems. This approach redefines the traditional relationship between databases and the SQL Server instances they reside in, bringing a multitude of benefits in terms of management, portability, and isolation.
A Faster Future: SQL Server 2022’s Parallel Buffer Scan
The new parallel buffer scan feature in SQL Server 2022 improves the performance of Buffer Pool scan operations on large-memory machines by utilizing multiple CPU cores. Customers running SQL Server on large-memory machines (e.g. TBs of memory) can see up to 4 ~ 20 times faster executions on some scenarios which has been slow due to Buffer Pool scan. Those scenarios include creating a new database, backup/restore operations, AlwaysOn failover, file drop, and DBCC check operations. Internal operations (e.g. checkpoint) that requires Buffer Pool scan will also get the benefits. The parallel scan feature also improves the Buffer Pool scan performance of small databases residing on large-memory machines.