Cardinality estimation (CE) is how the optimizer predicts the number of rows that will flow through each operator in a plan. Those estimates drive cost, join choices, memory grants, and ultimately latency and resource usage. SQL Server has shipped multiple CE models over time. The pre-2014 model—commonly called the legacy CE—dates back to SQL Server 7.0. Starting in SQL Server 2014, Microsoft introduced a new CE and has continued refining it in later releases, including SQL Server 2022. Keeping the legacy CE turned on in SQL Server 2022 is usually the wrong long-term choice.
Category: Database Configuration
Understanding Compatibility Levels in Azure SQL Database
When working with Azure SQL Database, you’re operating on a fully managed SQL Server engine that is continually updated behind the scenes. While Microsoft manages the infrastructure, there are still settings and metadata you control that directly affect how your database behaves and how your applications interact with it. We tell you all about it in this blog post.
Understanding and Managing the Model, MSDB, and Master Databases in SQL Server
It’s easy to focus on user databases and overlook the system databases at the core of every SQL Server instance. However, the master, msdb, and model databases form the backbone of your environment. Understanding each database’s specific responsibilities, how to safely customize them, and how to protect them from data loss or corruption is important for maintaining a stable server. In this post we take a look at all three databases to better understand their purposes and proper maintenance.
Handling Compatibility Level Changes During SQL Server Upgrades
Compatibility levels determine how certain features of SQL Server behave, allowing databases to maintain functionality even as the server itself evolves. However, changing compatibility levels requires careful consideration to balance the benefits of new features against the potential risks of disrupting existing applications. This blog explores the importance of handling compatibility level changes thoughtfully during SQL Server upgrades, highlighting the risks of making changes too hastily and the advantages of adopting new compatibility levels within a reasonable timeframe.
Understanding SQL Server’s TRUSTWORTHY Database Setting
In SQL Server, the TRUSTWORTHY database setting can grant elevated permissions across a database boundary, influencing how certain modules execute, particularly those using WITH EXECUTE AS or unsafe CLR assemblies. Enabling TRUSTWORTHY allows SQL Server to “trust” that the database owner and associated users won’t misuse elevated permissions to perform unauthorized actions. However, this setting comes with significant security risks if used improperly.
Understanding the Timeline of Query Optimization Improvements in SQL Server 2022
Intelligent Query Processing (IQP) is a suite of advanced features introduced in SQL Server 2017 and enhanced in subsequent versions, including SQL Server 2022. By upgrading to SQL Server 2022 and setting your database compatibility level to 160, you enable capabilities that dynamically optimize query performance. Understanding when these performance improvements take effect can help database administrators and developers plan and manage their optimization strategies effectively. The journey toward maximum performance improvement is gradual and influenced by factors such as workload characteristics and system configurations.
Managing SQL Server Database Growth with Files and Filegroups
As SQL Server databases expand over time, managing their growth becomes an important task. If not handled properly, this growth can lead to performance issues and maintenance headaches. One effective way to manage database growth is by using filegroups and data files thoughtfully. With proper planning, you can distribute your data across different storage devices, enhance performance, and keep your database scalable.
Using Query Hints to Solve SQL Server Performance Issues
As professional DBAs, we’re often tasked with balancing query performance against resource consumption. While SQL Server’s query optimizer generally does an excellent job at selecting execution plans, there are times when we need more control. This is where query hints come into play. These hints provide a powerful way to override the default behavior of the query optimizer, allowing us to directly influence how queries are executed to solve performance bottlenecks. In this post, we will explore how query hints can be leveraged to address common performance issues and fine-tune your SQL Server workloads.
Enhancing Availability with SQL Server AlwaysOn Database-Level Health Detection
AlwaysOn Database-Level Health Detection is an optional configuration that enhances the resilience of availability group databases. It monitors database transactions and, if an issue is detected, can automatically trigger a failover to another replica. This proactive measure helps maintain application availability even in the event of hardware or I/O failures.
Strategies for Optimizing Version Ghost Record Cleanup in SQL Server
This blog explores the challenges and strategies for managing version ghost records in SQL Server. It discusses issues like single-threaded cleanup tasks, long-running transactions, and locking levels that impact the cleanup process. The post provides actionable strategies such as updating SQL Server, utilizing trace flags, and regular index maintenance to improve ghost record cleanup efficiency.