SQL Server Management Studio (SSMS) 21 is now generally available (GA)
The SQL Tools team is thrilled to announce the general availability of SQL Server Management Studio (SSMS) 21. SSMS 21 GA brings a modern installation and update experience through the Visual Studio installer, 64-bit support, Git integration, and initial dark theme support.SQL Server 2025 - AI ready enterprise database from ground to cloud
The new version of SQL Server is designed to be an AI-ready enterprise database platform, integrating seamlessly from ground to cloud to Fabric. In this blog, we will explore the key features and enhancements that make SQL Server 2025 a game-changer for developers, database administrators, and organizations. The new capabilities build upon more than three decades of SQL Server innovation in performance, availability, reliability, and security, adding a host of new features that empower developers, protect data, and enable seamless analytics through the Microsoft Fabric integration. AI integration SQL Server 2025 offers features to support enterprise applications. This version integrates AI with customer data using AI capabilities within the SQL engine, ensuring that AI models remain isolated securely. The built-in vector data type allows hybrid AI vector searches, combining vectors with SQL data for efficient and accurate data retrieval. This integration facilitates AI application development and retrieval-augmented generation (RAG) patterns, and AI Agents using the familiar T-SQL syntax. The new vector data type stores vector embeddings alongside relational data, enabling semantically related searches within SQL Server. New vector functions perform operations on vectors in binary format, enabling applications to store and manipulate vectors directly within the SQL database engine. SQL Server 2025 includes T-SQL functions that provide the necessary tools for working with embeddings, without requiring detailed knowledge of their usage. Vectors enable AI models to identify similar data using the K-Nearest Neighbors (KNN) algorithm, with metrics like dot product or cosine similarity. To enhance scalability, SQL Server 2025 incorporates Approximate Vector Index and Vector Search, leveraging Approximate Nearest Neighbors (ANN) for faster, resource-efficient, and accurate results. SQL Server 2025 introduces advanced AI model management capabilities designed to enhance the efficiency and security of interacting with Azure OpenAI and other AI models. SQL Server 2025 provides options for deploying AI models either on-premises or in the cloud, with compatibility for Azure OpenAI, OpenAI endpoints, and Ollama. With all these capabilities, SQL Server 2025's hybrid search represents a paradigm shift in how organizations access and utilize data. Through a blend of keyword and vector searches, businesses can unlock deeper insights, improve customer satisfaction, and harness the full potential of their data assets. Our customer, Kramer & Crew GmbH & Co, who participated in our Early Adoption Program (EAP) aka private preview shared us below. "Joining the EAP was a great opportunity to explore the new AI, security, performance, Fabric, and Azure Arc features! With the new semantic search and RAG capabilities in SQL Server 2025, we can empower existing GenAI solutions with data embeddings to create next-generation, more intelligent AI applications. By connecting systems (e.g., ITSM, CRM, ERP, and others), we deliver a seamless, natural conversational experience across enterprise environments." Markus Angenendt, Data Platform Infrastructure Lead, Kramer & Crew GmbH & Co. KG Developer productivity SQL Server 2025 introduces several exciting developer features designed to enhance developer productivity. New GitHub Copilot: GitHub Copilot transforms coding with AI-driven suggestions, streamlining workflows and enhancing efficiency. Its agent mode proposes edits, tests, and validates changes, enabling developers to focus on complex tasks. SQL Server Management Studio (SSMS) 21: Releasing SQL Server Management Studio (SSMS) 21, for general availability (GA). SSMS 21 includes support for SQL Server 2025. The Copilot in SSMS – now available in preview. New Python Driver: The Python driver for SQL Server and Azure SQL offers efficient, asynchronous connectivity across platforms like Windows, Linux, and macOS. It's designed to simplify development and enhance performance for data-driven applications. Standard Developer Edition: SQL Server 2025 Standard Developer Edition is a free edition licensed for development and test purposes. The intent is to enable all features of SQL Server Standard Edition to facilitate the development and testing of new applications that use the Standard Edition in production. This edition complements the existing Enterprise Developer Edition. JSON data type and aggregates: SQL Server 2025 includes a native JSON data type, allowing for more efficient storage and manipulation of JSON data up to 2GB storage per JSON document. This type supports various JSON aggregate functions to facilitate the aggregation of JSON data. Queries over JSON documents can be optimized by creating a JSON index and using JSON functions and methods to modify and search data natively. Regular expressions (RegEx): SQL Server 2025 introduces support for Regular Expressions (RegEx), providing powerful tools for developers to efficiently query and manipulate text data, better matching pattern than “LIKE” operator. External REST endpoint invocation: The sp_invoke_external_rest_endpoint stored procedure allows for the native invocation of any REST endpoints directly from within T-SQL, enabling seamless integration with external web services. Change event streaming (CES): Enables real-time data integration by streaming data changes directly from SQL Server to Azure Event Hubs with Kafka compatibility, facilitating near real-time analytics and event-driven architecture based on Transaction log. Consider using Change Event Streaming for CDC as it eliminates the need for I/O operations, offering a more efficient and streamlined solution for developers. New T-SQL functions: Several new T-SQL functions introduced to simplify complex queries and increase workload performance. For example, the PRODUCT() aggregate function calculates the product of a set of values. New Chinese collations: Support for GB18030-2022 collation standard. Overall, these developer-centric enhancements in SQL Server 2025 streamline the process of building modern, AI powered and data-rich applications. They reduce the need for custom code and encourage a more declarative, in-database approach to data processing, which can lead to simpler architecture and better performance. “The introduction of the new PRODUCT() aggregate function in SQL Server 2025 has streamlined this process, reducing code complexity while improving computational efficiency by over 30%. This enhancement accelerates key economic calculations, including the computation of the U.S. Gross Domestic Product (GDP), and also strengthens organizations’ ability to deliver timely, accurate data to policymakers and to the public." -- David Rozenshtein and Sandip Mehta, IT Modernization Architects, Omnicom Consulting Group” Secure by default SQL Server 2025 delivers a range of advanced security features designed to enhance data protection, authentication, and encryption. Here are the key security enhancements. Stop using client secrets and passwords: SQL Server 2025 supports managed identity authentication enabled by Azure Arc. This feature allows secure authentication for outbound connections to Azure resources and inbound connections for external users. For example, backup to Azure Blob Storage can now use SQL Server managed identity for authentication. Stronger encryption: To protect the key material of a symmetric key SQL Server stores the key material in encrypted form. Historically, this encryption utilized PKCS#1 v1.5 padding mode; Optimized starting with SQL Server 2025, the encryption uses Optimal Asymmetric Encryption Padding (OAEP) for encryption by certificate or asymmetric key. Stronger password encryption: To store a SQL user password we use an iterated hash algorithm, RFC2898, also known as a password-based key derivation function (PBKDF). This algorithm uses SHA-512 hash but hashes the password multiple times (100,000 iterations), significantly slowing down brute-force attacks. This change enhances password protection in response to evolving security threats and helps customers comply with NIST SP 800-63b guidelines. Strict connection encryption: The implementation of Extended TDS 8.0 support and TLS 1.3 for stringent encryption protocols enhances the security of internal component communications within SQL Server 2025. Optimized security cache: When security cache entries are invalidated, only those entries belonging to the impacted login are affected. This minimizes the impact on non-cache permissions validation for unaffected login users. In summary, SQL Server 2025 continues the product’s legacy of top-notch security by incorporating modern identity and encryption practices. By embracing Azure AD, managed identities, and stronger cryptography by default, it helps organizations avoid vulnerabilities and meet compliance requirements more easily, protecting data both at rest and in motion. Mission critical database engine SQL Server 2025 introduces significant performance and reliability enhancements designed to optimize workload efficiency and reduce troubleshooting efforts. Utilize insights gained from prior executions of expressions within queries enhance the performance of future executions. Optional parameter plan optimization helps SQL Server choose the optimal execution plan based on runtime parameter values, reducing performance issues caused by parameter sniffing. Optimized locking improves concurrency by avoiding blocking and lock escalation and reduces lock memory usage. Enhancements in batch mode processing and columnstore indexes further improve SQL Server as a mission-critical database for analytical workloads. Query Store for readable secondaries allows you to monitor and adjust the performance of read-only workloads executing against secondary replicas. In SQL Server 2025 this is enabled by default. Persisted temporary statistics for readable secondaries are now saved to the primary replica, ensuring permanence and avoiding recreation after restarts, which could degrade performance. A new query hint blocks future execution of problematic queries, such as nonessential queries affecting application performance. Optimized Halloween protection reduces tempdb space consumption and improves performance of data modification queries. Tempdb space resource governance improves reliability by restricting workloads from consuming excessive tempdb space. Accelerated database recovery in tempdb provides instantaneous transaction rollback and aggressive log truncation for transactions in tempdb. Fast failover for persistent health issues: The Windows Failover Cluster (WSFC) can be configured to failover the availability group resource promptly upon detection of a persistent health issue for example long I/O . Enhancements have been made to the undo-of-redo process during disaster recovery failover to asynchronous replicas, improving synchronization performance. Internal synchronization mechanisms have been improved to reduce network saturation when the global primary and forwarder replicas are in asynchronous commit mode. Improved health check time-out diagnostics. Configure a distributed availability group between two contained availability groups. The new backup compression algorithm, ZSTD, provides significant enhancements in compression efficiency while utilizing fewer resources. You can now offload FULL, DIFFERENTIAL, and T-LOG backups to a secondary replica in an Always On Availability Group, freeing your primary replica to handle production workloads. Fabric integration and Analytics Database mirroring to Fabric can continuously replicate data from a database in a SQL Server 2025 instance, on-premises or in virtual machines. A mirrored database item is a read-only, continuously replicated copy of your SQL Server database data in OneLake. SQL Server now natively supports querying CSV, Parquet, and Delta files using OPENROWSET, CREATE EXTERNAL TABLE, or CREATE EXTERNAL TABLE commands, without needing PolyBase Query Service. SQL Server on Linux tmfs filesystem is supported for tempdb in SQL Server 2025 on Linux. This enhancement can improve performance for tempdb-heavy workloads by utilizing memory (RAM) instead of disk-based filesystems. Custom password policy enforces a custom password policy for SQL authentication logins in SQL Server on Linux. PolyBase in SQL Server for Linux can now connect to ODBC data sources. Discontinued services Data Quality Services (DQS) is discontinued in this version of SQL Server. We continue to support DQS in SQL Server 2022 (16.x) and earlier versions. Master Data Services (MDS) is discontinued in this version of SQL Server. We continue to support MDS in SQL Server 2022 (16.x) and earlier versions. Get started SQL Server 2025 is not just an iterative update; it’s a substantial upgrade that bridges the worlds of databases and AI, on-premises and cloud. It retains full support for existing applications and T-SQL code, so upgrades can be done with minimal changes. By adopting SQL Server 2025, organizations can answer new questions with their data, serve applications at a greater scale, and integrate more closely with modern data platforms – all while relying on the familiar, reliable foundation that SQL Server has provided for years. Ready to try it out? Get started today: aka.ms/getsqlserver2025. Learn more Microsoft Build 2025: SQL Server 2025: The Database Developer Reimagined Docs: aka.ms/Build/sql2025docs Announcement blog: aka.ms/sqlserver2025 SQL Server homepage: https://www.microsoft.com/en-us/sql-server MSSQL Extension for Visual Studio Code with GitHub Copilot: https://aka.ms/vscode-mssql-copilot
SQL Server 2025: introducing optimized Halloween protection
Executive summary Optimized Halloween protection, available in the public preview of SQL Server 2025 starting with the CTP 2.0 release, reduces tempdb space consumption and improves query performance by redesigning the way the database engine solves the Halloween problem. An example in the appendix shows CPU and elapsed time of a query reduced by about 50% while eliminating all tempdb space consumption. Update 2025-09-02 During public preview of SQL Server 2025, we identified a potential data integrity issue that might occur if optimized Halloween protection is enabled. While the probability of encountering this issue is low, we take data integrity seriously. Therefore, we temporarily removed optimized Halloween protection from SQL Server 2025, starting with the RC 0 release. The fix for this issue is in progress. In the coming months, we plan to make optimized Halloween protection available in Azure SQL Database and Azure SQL Managed Instance with the always-up-to-date update policy. Enabling optimized Halloween protection in a future SQL Server 2025 update is under consideration as well. The Halloween problem The Halloween problem, named so because it was discovered on Halloween in 1976, occurs when a data modification language (DML) statement changes data in such a way that the same statement unexpectedly processes the same row more than once. Traditionally, the SQL Server database engine protects DML statements from the Halloween problem by introducing a spool operator in the query plan, or by taking advantage of another blocking operator already present in the plan, such as a sort or a hash match. If a spool operator is used, it creates a temporary copy of the data to be modified before any modifications are made to the data in the table. While the protection spool avoids the Halloween problem, it comes with downsides: The spool requires extra resources: space in tempdb, disk I/O, memory, and CPU. Statement processing by the downstream query operators is blocked until the data is fully written into the spool. The spool adds query plan complexity that can cause the query optimizer to generate a less optimal plan. Optimized Halloween protection removes these downsides by making the spool operator unnecessary. How it works When accelerated database recovery (ADR) is enabled, each statement in a transaction obtains a unique statement identifier, known as nest ID. As each row is modified by a DML statement, it is stamped with the nest ID of the statement. This is required to provide the ACID transaction semantics with ADR. During DML statement processing, when the storage engine reads the data, it skips any row that has the same nest ID as the current DML statement. This means that the query processor doesn't see the rows already processed by the statement, therefore avoiding the Halloween problem. How to use optimized Halloween protection To enable optimized Halloween protection for a database, the following prerequisites are required: ADR must be enabled on the database. The database must use compatibility level 170. The OPTIMIZED_HALLOWEEN_PROTECTION database-scoped configuration must be enabled. The OPTIMIZED_HALLOWEEN_PROTECTION database-scoped configuration is enabled by default. This means that when you enable ADR for a database using compatibility level 170, it will use optimized Halloween protection. You can ensure that a database uses optimized Halloween protection by executing the following statements: ALTER DATABASE [<database-name-placeholder>] SET ACCELERATED_DATABASE_RECOVERY = ON WITH ROLLBACK IMMEDIATE; ALTER DATABASE [<database-name-placeholder>] SET COMPATIBILITY_LEVEL = 170; ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_HALLOWEEN_PROTECTION = ON; You can also enable and disable optimized Halloween protection at the query level by using the ENABLE_OPTIMIZED_HALLOWEEN_PROTECTION and DISABLE_OPTIMIZED_HALLOWEEN_PROTECTION query hints, either directly in the query, or via Query Store hints. These hints work under any compatibility level and take precedence over the OPTIMIZED_HALLOWEEN_PROTECTION database-scoped configuration. When optimized Halloween protection is used for an operator in the query plan, the OptimizedHalloweenProtectionUsed property of the operator in the XML query plan is set to True. For more details, see optimized Halloween protection in documentation. Conclusion Optimized Halloween protection is another Intelligent Query Processing feature that improves query performance and reduces resource consumption when you upgrade to SQL Server 2025, without having to make any changes to your query workloads. We are looking forward to your feedback about this and other features during the public preview of SQL Server 2025 and beyond. You can leave comments on this blog post, email us at intelligentqp@microsoft.com, or leave feedback at https://aka.ms/sqlfeedback. Appendix The following script shows how optimized Halloween protection removes the protection spool in the query plan, and reduces tempdb usage, CPU time, and duration when enabled. /* Requires the WideWorldImporters sample database. SQL Server backup: https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Full.bak Bacpac: https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Standard.bacpac */ /* Ensure that optimized Halloween protection prerequisites are in place */ ALTER DATABASE WideWorldImporters SET ACCELERATED_DATABASE_RECOVERY = ON WITH ROLLBACK IMMEDIATE; ALTER DATABASE WideWorldImporters SET COMPATIBILITY_LEVEL = 170; ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_HALLOWEEN_PROTECTION = ON; GO /* Validate configuration */ SELECT d.compatibility_level, d.is_accelerated_database_recovery_on, dsc.name, dsc.value FROM sys.database_scoped_configurations AS dsc CROSS JOIN sys.databases AS d WHERE dsc.name = 'OPTIMIZED_HALLOWEEN_PROTECTION' AND d.name = DB_NAME(); GO /* Create the test table and add data */ DROP TABLE IF EXISTS dbo.OptimizedHPDemo; BEGIN TRANSACTION; SELECT * INTO dbo.OptimizedHPDemo FROM Sales.Invoices ALTER TABLE dbo.OptimizedHPDemo ADD CONSTRAINT PK_OptimizedHPDemo PRIMARY KEY CLUSTERED (InvoiceID) ON USERDATA; COMMIT; GO /* Ensure that Query Store is enabled and is capturing all queries */ ALTER DATABASE WideWorldImporters SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = ALL); /* Empty Query Store to start with a clean slate */ ALTER DATABASE WideWorldImporters SET QUERY_STORE CLEAR; GO /* Disable optimized Halloween protection as the baseline */ ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_HALLOWEEN_PROTECTION = OFF; GO /* Insert data selecting from the same table. This requires Halloween protection so that the same row cannot be selected and inserted repeatedly. */ BEGIN TRANSACTION; INSERT INTO dbo.OptimizedHPDemo ( InvoiceID, CustomerID, BillToCustomerID, OrderID, DeliveryMethodID, ContactPersonID, AccountsPersonID, SalespersonPersonID, PackedByPersonID, InvoiceDate, CustomerPurchaseOrderNumber, IsCreditNote, CreditNoteReason, Comments, DeliveryInstructions, InternalComments, TotalDryItems, TotalChillerItems, DeliveryRun, RunPosition, ReturnedDeliveryData, ConfirmedDeliveryTime, ConfirmedReceivedBy, LastEditedBy, LastEditedWhen ) SELECT InvoiceID + 1000000 AS InvoiceID, CustomerID, BillToCustomerID, OrderID, DeliveryMethodID, ContactPersonID, AccountsPersonID, SalespersonPersonID, PackedByPersonID, InvoiceDate, CustomerPurchaseOrderNumber, IsCreditNote, CreditNoteReason, Comments, DeliveryInstructions, InternalComments, TotalDryItems, TotalChillerItems, DeliveryRun, RunPosition, ReturnedDeliveryData, ConfirmedDeliveryTime, ConfirmedReceivedBy, LastEditedBy, LastEditedWhen FROM dbo.OptimizedHPDemo; ROLLBACK; GO /* Enable optimized Halloween protection. Execute the following statement in its own batch. */ ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_HALLOWEEN_PROTECTION = ON; GO /* Execute the same query again */ BEGIN TRANSACTION; INSERT INTO dbo.OptimizedHPDemo ( InvoiceID, CustomerID, BillToCustomerID, OrderID, DeliveryMethodID, ContactPersonID, AccountsPersonID, SalespersonPersonID, PackedByPersonID, InvoiceDate, CustomerPurchaseOrderNumber, IsCreditNote, CreditNoteReason, Comments, DeliveryInstructions, InternalComments, TotalDryItems, TotalChillerItems, DeliveryRun, RunPosition, ReturnedDeliveryData, ConfirmedDeliveryTime, ConfirmedReceivedBy, LastEditedBy, LastEditedWhen ) SELECT InvoiceID + 1000000 AS InvoiceID, CustomerID, BillToCustomerID, OrderID, DeliveryMethodID, ContactPersonID, AccountsPersonID, SalespersonPersonID, PackedByPersonID, InvoiceDate, CustomerPurchaseOrderNumber, IsCreditNote, CreditNoteReason, Comments, DeliveryInstructions, InternalComments, TotalDryItems, TotalChillerItems, DeliveryRun, RunPosition, ReturnedDeliveryData, ConfirmedDeliveryTime, ConfirmedReceivedBy, LastEditedBy, LastEditedWhen FROM dbo.OptimizedHPDemo; ROLLBACK; GO /* Examine query runtime statistics and plans for the two executions of the same query. */ SELECT q.query_id, q.query_hash, qt.query_sql_text, p.plan_id, rs.count_executions, rs.avg_tempdb_space_used * 8 / 1024. AS tempdb_space_mb, FORMAT(rs.avg_cpu_time / 1000., 'N0') AS avg_cpu_time_ms, FORMAT(rs.avg_duration / 1000., 'N0') AS avg_duration_ms, TRY_CAST(p.query_plan AS xml) AS xml_query_plan FROM sys.query_store_runtime_stats AS rs INNER JOIN sys.query_store_plan AS p ON rs.plan_id = p.plan_id INNER JOIN sys.query_store_query AS q ON p.query_id = q.query_id INNER JOIN sys.query_store_query_text AS qt ON q.query_text_id = qt.query_text_id WHERE q.query_hash = 0xC6ADB023512BBCCC; /* For the second execution with optimized Halloween protection: 1. tempdb space usage is zero 2. CPU time and duration are reduced by about 50% 3. The Clustered Index Insert operator in the query plan has the OptimizedHalloweenProtection property set to True */Announcing Public Preview of DiskANN in SQL Server 2025
We are excited to announce the public preview of DiskANN in SQL Server 2025, a significant advancement in our AI capabilities. This release comes with full vector support, enabling the storing and querying of embeddings, which are essential for modern AI applications.What’s new in SQL Server 2025 CTP 2.1: Building momentum from public preview
During Microsoft Build, we announced the public preview of SQL Server 2025 (https://aka.ms/sqlserver2025), marking a significant advancement in our efforts to deliver an AI-ready enterprise database platform with superior security, performance, and availability. We are pleased to announce Community Technology Preview (CTP) 2.1. This update builds on the momentum from #MSBuild and brings new features and enhancements designed to help customers unlock more value from their data, simplify operations, and strengthen security. Efficient Vector Data & Indexing Addressed few limitations from the Vector data type and functions for streamlined usage. Improved Vector Index build performance significantly. Transmit vectors efficiently in binary format to reduce payload size and enhance AI workload performance using the updated TDS protocol and updated drivers Added sys.vector_indexes catalog view for querying vector indexes. Creating a vector index no longer locks the table with SCH-M lock, allowing full read-access during indexing. Embedding with Auto-Retry: The new enhancement introduces a built-in mechanism to automatically retry the embedding call if it fails due to temporary HTTP errors (like timeouts or service unavailability). Secure by default: SQL Server 2025 to modernize and secure internal communications across all components. In this release we extended TDS 8.0 and Transport Layer Security (TLS) 1.3 support for SQL Writer, PolyBase service and SQL CEIP our telemetry services. What’s new in SQL Server 2025 security Tempdb enhancements: Tempdb space resource governance now supports percent-based limits. Resource governance can now be defined using percentage-based thresholds of the maximum tempdb space, making it easier to scale policies across different hardware configurations. Immutable Storage for Azure Blob Backups Backups to Azure Blob Storage now support immutable storage, which prevents tampering or deletion for a defined retention period—ideal for compliance and audit scenarios. Max_message_size_kb Parameter Update The sys.sp_create_event_group_stream stored procedure now includes an updated Max_message_size_kb parameter, allowing better control over event stream message sizes. Automatic Plan Correction (APC) Behavioral Change SQL Server now automatically detects plan regressions and applies FORCE_LAST_GOOD_PLAN to correct them. The regression detection model previously enabled by Trace Flag 12618 is now on by default, making automatic tuning more proactive and effective without manual intervention. SQL Server Enabled by Azure Arc – Overview This release introduces native support for Azure Arc integration in SQL Server 2025 Preview. Azure Arc is a Microsoft service that allows you to manage on-premises, multi-cloud, and edge environments through the Azure control plane. Consolidation of reporting services: Beginning with SQL Server 2025, Microsoft will integrate all on-premises reporting services into Power BI Report Server (PBIRS). There will be no further releases of SQL Server Reporting Services (SSRS). PBIRS will serve as the default on-premises reporting solution for SQL Server. For more information, see Reporting Services consolidation FAQ Discontinued services: Purview access policies (DevOps policies and data owner policies) are discontinued in this version of SQL Server. As an alternative to the policy actions provided by Purview policies, please use Fixed server roles. Refer our documentation for details on the specific server roles to use. Get started SQL Server 2025 is a major upgrade that unites databases and AI across on-premises and cloud. It supports existing apps and T-SQL with minimal changes, enabling organizations to scale, integrate with modern data platforms, and unlock new insights—while building on SQL Server’s trusted foundation. Ready to try it out? Get started today: aka.ms/getsqlserver2025. Learn more Microsoft Build 2025: SQL Server 2025: The Database Developer Reimagined Docs: aka.ms/Build/sql2025docs Announcement blog: aka.ms/sqlserver2025 SQL Server 2025 deep dive SQL Server tech community blog SQL Server homepage: https://www.microsoft.com/en-us/sql-server MSSQL Extension for Visual Studio Code with GitHub Copilot: https://aka.ms/vscode-mssql-copilotAnnouncing SQL Server 2025 Release Candidate 0 (RC0)
We are excited to announce the availability of SQL Server 2025 Release Candidate 0 (RC0), building on the momentum of the first public preview released on May 15, 2025. This release marks a significant step forward in database innovation, introducing new development features and critical enhancements to performance, security, and analytics. Focus on Preview Features: Opt-In Innovation With RC0, SQL Server 2025 introduces a new way to experiment and adopt cutting-edge capabilities through the PREVIEW_FEATURES database scoped configuration. This opt-in mechanism empowers developers and database administrators to explore new features in a controlled, flexible manner. To learn more, please review the release notes for detailed guidance on activating and leveraging these preview features. What’s New in RC0? AI Enhancements Vector Search and Index: Easily create vector indexes and execute vector searches using the PREVIEW_FEATURES configuration. Better intra-query parallelism with hyperthreading. Enabled usage of vectors on MacOS via Rosetta 2 translation layer. Text Chunking: The AI_GENERATE_CHUNKS feature enables efficient text chunking, available via PREVIEW_FEATURES. ONNX Model Integration: CREATE EXTERNAL MODEL now supports local ONNX models hosted directly in SQL Server, enabled through PREVIEW_FEATURES. Improved Metadata: The sys.vector_indexes view now follow established naming convention and inherits columns from sys.indexes. Linux Support Broaden Your Linux Choices with Ubuntu 24.04 Support SQL Server 2025 RC0 now runs on Ubuntu 24.04 LTS, giving you more flexibility to standardize on the latest long‑term support Linux distribution. Starting in SQL Server 2025 (17.x) Preview, TLS 1.3 is enabled by default. Database Engine Improvements Time-bound extended event sessions stop automatically after the specified time elapses. Cardinality Estimation (CE) Feedback for Expressions—cache persistence: Learns from repeated query patterns to pick better row estimates and plans. With persistence, that learning will survive restarts and failovers for steadier performance and less retuning. Analytics PolyBase now supports managed identity communications to Microsoft Azure Blob Storage and Microsoft Azure Data Lake Storage. Language and Query Features JARO_WINKLER_DISTANCE returns float (previously real). JARO_WINKLER_SIMILARITY returns int (previously real). REGEXP_REPLACE and REGEXP_SUBSTR now support large object (LOB) types (varchar(max) and nvarchar(max)). REGEXP_LIKE is now SARGable, allowing index usage for faster query execution. Availability and Security SQL Server Agent, Linked Servers, defaults to encryption mandatory, using the latest security standards—TDS 8.0 and TLS 1.3. Configure TLS 1.3 encryption for Always On availability groups, failover cluster instances (FCI), replication, and log shipping with TDS 8.0 support. Replication defaults to OLEDB version 19 for inter-instance communication and enforces TLS 1.3 with Encrypt=Strict by default. Fabric mirroring Resource governance can now be used for Fabric Mirroring to isolate and manage resource consumption. Dynamic maxtrans configuration option to optimize Fabric mirroring performance Getting Started SQL Server 2025 RC0 offers a unique opportunity to explore the latest features via the PREVIEW_FEATURES configuration. These opt-in innovations are designed to foster experimentation and rapid adoption of upcoming capabilities. We encourage all users to review the release notes and take advantage of these powerful new tools. Accelerating SQL Server 2025 momentum: Announcing the first release candidate Thank you for being a part of the SQL Server community. Your feedback continues to shape the future of our platform. Download SQL Server 2025 RC0 today and unlock the next era of database excellence!SQL Server 2025: introducing tempdb space resource governance
An old problem Since the early days of SQL Server, DBAs had to contend with a common problem – running out of space in the tempdb database. It has always struck me as odd that all I need to cause an outage on an SQL Server instance is access to the server where I can create a temp table that fills up tempdb, and there is no permission to stop me. - Erland Sommarskog (website), an independent SQL Server consultant and a Data Platform MVP Because tempdb is used for a multitude of purposes, the problem can occur without any explicit user action such as creating a temporary table. For example, executing a reporting query that spills data to tempdb and fills it up can cause an outage for all workloads using that SQL Server instance. Over the years, many DBAs developed custom solutions that monitor tempdb space and take action, for example kill sessions that consume a large amount of tempdb space. But that comes with extra effort and complexity. I have spent more hours in my career than I can count building solutions to manage TempDB space. Even with immense time and effort, there were still quirks and caveats that came up that created challenges - especially in multi-tenant environments with lots of databases and the noisy-neighbor problem. - Edward Pollack (LinkedIn), Data Architect at Transfinder and a Data Platform MVP A new solution in the SQL Server engine SQL Server 2025 brings a new solution for this old problem, built directly into the database engine. Starting with the CTP 2.0 release, you can use resource governor, a feature available since SQL Server 2008, to enforce limits on tempdb space consumption. We rely on Resource Governor to isolate workloads on our SQL Server instances by controlling CPU and memory usage. It helps us ensure that the core of our trading systems remains stable and runs with predictable performance, even when other parts of the systems share the same servers. - Ola Hallengren (website), Chief Data Platforms Engineer at Saxo Bank and a Data Platform MVP Similarly, if you have multiple workloads running on your server, each workload can have its own tempdb limit, lower than the maximum available tempdb space. This way, even if one workload hits its limit, other workloads continue running. Here's an example that limits the total tempdb space consumption by queries in the default workload group to 17 GB, using just two T-SQL statements: ALTER WORKLOAD GROUP [default] WITH (GROUP_MAX_TEMPDB_DATA_MB = 17408); ALTER RESOURCE GOVERNOR RECONFIGURE; The default group is used for all queries that aren’t classified into another workload group. You can create workload groups for specific applications, users, etc. and set limits for each group. When a query attempts to increase tempdb space consumption beyond the workload group limit, it is aborted with error 1138, severity 17, Could not allocate a new page for database 'tempdb' because that would exceed the limit set for workload group 'workload-group-name'. All other queries on the server continue to execute. Setting the limits You might be asking, “How do I know the right limits for the different workloads on my servers?” No need to guess. Tempdb space usage is tracked for each workload group at all times and reported in the sys.dm_resource_governor_workload_groups DMV. Usage is tracked even if no limits are set for the workload groups. You can establish representative usage patterns for each workload over time, then set the right limits. You can reconfigure the limits over time if workload patterns change. For example, the following query lets you see the current tempdb space usage, peak usage, and the number of times queries were aborted because they would otherwise exceed the limit per workload group: SELECT group_id, name, tempdb_data_space_kb, peak_tempdb_data_space_kb, total_tempdb_data_limit_violation_count FROM sys.dm_resource_governor_workload_groups; Peak usage and the number of query aborts (limit violations) are tracked since server restart. You can reset these and other resource governor statistics to restart tracking at any time and without restarting the server by executing ALTER RESOURCE GOVERNOR RESET STATISTICS; What about the transaction log? The limits you set for each workload group apply to space in the tempdb data files. But what about the tempdb transaction log? Couldn’t a large transaction fill up the log and cause an outage? This is where another feature in SQL Server 2025 comes in. You can now enable accelerated database recovery (ADR) in tempdb to get the benefit of aggressive log truncation, and drastically reduce the possibility of running out of log space in tempdb. For more information, see ADR improvements in SQL Server 2025. Learn more For more information about tempdb space resource governance, including examples, best practices, and the details of how it works, see Tempdb space resource governance in documentation. If you haven’t used resource governor in SQL Server before, here’s a good starting point: Tutorial: Resource governor configuration examples and best practices. Conclusion SQL Server 2025 brings a new, built-in solution for the age-old problem of tempdb space management. You can now use resource governor to set limits on tempdb usage and avoid server-wide outages because tempdb ran out of space. We are looking forward to your feedback on this and other SQL Server features during the public preview of SQL Server 2025 and beyond. You can leave comments on this blog post, email us at sql-rg-feedback@microsoft.com, or leave feedback at https://aka.ms/sqlfeedback.
Smarter Parallelism: Degree of parallelism feedback in SQL Server 2025
🚀 Introduction With SQL Server 2025, we have made Degree of parallelism (DOP) feedback an on by default feature. Originally introduced in SQL Server 2022, DOP feedback is now a core part of the platform’s self-tuning capabilities, helping workloads scale more efficiently without manual tuning. The feature works with database compatibility 160 or higher. ⚙️ What Is DOP feedback? DOP feedback is part of the Intelligent Query Processing (IQP) family of features. It dynamically adjusts the number of threads (DOP) used by a query based on runtime performance metrics like CPU time and elapsed time. If a query that has generated a parallel plan consistently underperforms due to excessive parallelism, the DOP feedback feature will reduce the DOP for future executions without requiring recompilation. Currently, DOP feedback will only recommend reductions to the degree of parallelism setting on a per query plan basis. The Query Store must be enabled for every database where DOP feedback is used, and be in a "read write" state. This feedback loop is: Persistent: Stored in Query Store. Persistence is not currently available for Query Store on readable secondaries. This is subject to change in the near future, and we'll provide an update to it's status after that occurs. Adaptive: Adjusts a query’s DOP, monitors those adjustments, and reverts any changes to a previous DOP if performance regresses. This part of the system relies on Query Store being enabled as it relies on the runtime statistics captured within the Query Store. Scoped: Controlled via the DOP_FEEDBACK database-scoped configuration or at the individual query level with the use of the DISABLE_DOP_FEEDBACK query hint. 🧪 How It Works Initial Execution: SQL Server compiles and executes a query with a default or manually set DOP. Monitoring: Runtime stats are collected and compared across executions. Adjustment: If inefficiencies are detected, DOP is lowered (minimum of 2). Validation: If performance improves and is stable, the new DOP is persisted. If not, the DOP recommendation will be reverted to the previously known good DOP setting, which is typically the original setting that the feature used as a baseline. At the end of the validation period any feedback that has been persisted, regardless of its state (i.e. stabilized, reverted, no recommendation, etc.) can be viewed by querying the sys.query_store_plan_feedback system catalog view: SELECT qspf.feature_desc, qsq.query_id, qsp.plan_id, qspf.plan_feedback_id, qsqt.query_sql_text, qsp.query_plan, qspf.state_desc, qspf.feedback_data, qspf.create_time, qspf.last_updated_time FROM sys.query_store_query AS qsq INNER JOIN sys.query_store_plan AS qsp ON qsp.query_id = qsq.query_id INNER JOIN sys.query_store_query_text AS qsqt ON qsqt.query_text_id = qsq.query_text_id INNER JOIN sys.query_store_plan_feedback AS qspf ON qspf.plan_id = qsp.plan_id WHERE qspf.feature_id = 3; 🆕 What’s New in SQL Server 2025? Enabled by Default: No need to toggle the database scoped configuration on, DOP feedback is active out of the box. Improved Stability: Enhanced validation logic ensures fewer regressions. Better Integration: Works seamlessly with other IQP features like Memory Grant feedback , Cardinality Estimation feedback, and Parameter Sensitive Plan (PSP) optimization. 📊 Visualizing the Feedback Loop 🧩 How can I see if DOP feedback is something that would be beneficial for me? Without setting up an Extended Event session for deeper analysis, looking over some of the data in the Query Store can be useful in determining if DOP feedback would find interesting enough queries for it to engage. At a minimum, if your SQL Server instance is operating with parallelism enabled and has: o a MAXDOP value of 0 (not generally recommended) or a MAXDOP value greater than 2 o you observe multiple queries have execution runtimes of 10 seconds or more along with a degree of parallelism of 4 or greater o and have an execution count 15 or more according to the output from the query below SELECT TOP 20 qsq.query_id, qsrs.plan_id, [replica_type] = CASE WHEN replica_group_id = '1' THEN 'PRIMARY' WHEN replica_group_id = '2' THEN 'SECONDARY' WHEN replica_group_id = '3' THEN 'GEO SECONDARY' WHEN replica_group_id = '4' THEN 'GEO HA SECONDARY' ELSE TRY_CONVERT(NVARCHAR (200), qsrs.replica_group_id) END, AVG(qsrs.avg_dop) as dop, SUM(qsrs.count_executions) as execution_count, AVG(qsrs.avg_duration)/1000000.0 as duration_in_seconds, MIN(qsrs.min_duration)/1000000.0 as min_duration_in_seconds FROM sys.query_store_runtime_stats qsrs INNER JOIN sys.query_store_plan qsp ON qsp.plan_id = qsrs.plan_id INNER JOIN sys.query_store_query qsq ON qsq.query_id = qsp.query_id GROUP BY qsrs.plan_id, qsq.query_id, qsrs.replica_group_id ORDER BY dop desc, execution_count desc; 🧠 Behind the Scenes: How Feedback Is Evaluated DOP feedback uses a rolling window of recent executions (typically 15) to evaluate: Average CPU time Standard deviation of CPU time Adjusted elapsed time* Stability of performance across executions If the adjusted DOP consistently improves efficiency without regressing performance, it is persisted. Otherwise, the system reverts to the last known good configuration (also knows as the default dop to the system). As an example, if the dop for a query started out with a value of 8, and DOP feedback determined that a DOP of 4 was an optimal number; if over the period of the rolling window and while the query is in the validation phase, if the query performance varied more than expected, DOP feedback will undo it's change of 4 and set the query back to having a DOP of 8. 🧠 Note: The adjusted elapsed time intentionally excludes wait statistics that are not relevant to parallelism efficiency. This includes ignoring buffer latch, buffer I/O, and network I/O waits, which are external to parallel query execution. This ensures that feedback decisions are based solely on CPU and execution efficiency, not external factors like I/O or network latency. 🧭 Best Practices Enable Query Store: This is required for DOP feedback to function. Monitor DOP feedback extended events SQL Server provides a set of extended events to help you monitor and troubleshoot the DOP feedback lifecycle. Below is a sample script to create a session that captures key events, followed by a breakdown of what each event means. IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'dop_xevents') DROP EVENT SESSION [dop_xevents] ON SERVER; GO CREATE EVENT SESSION [dop_xevents] ON SERVER ADD EVENT sqlserver.dop_feedback_analysis_stopped, ADD EVENT sqlserver.dop_feedback_eligible_query, ADD EVENT sqlserver.dop_feedback_provided, ADD EVENT sqlserver.dop_feedback_reassessment_failed, ADD EVENT sqlserver.dop_feedback_reverted, ADD EVENT sqlserver.dop_feedback_stabilized -- ADD EVENT sqlserver.dop_feedback_validation WITH ( MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0 KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = OFF ); ⚠️ Note: The extended event that has been commented out (dop_feedback_validation) is part of the debug channel. Enabling it may introduce additional overhead and should be used with caution in production environments. 📋 DOP Feedback Extended Events Reference Event Name Description dop_feedback_eligible_query Fired when a query plan becomes eligible for DOP feedback. Captures initial runtime stats like CPU time and adjusted elapsed time. dop_feedback_analysis_stopped Indicates that SQL Server has stopped analyzing a query for DOP feedback. Reasons include high variance in stats or the optimal DOP has already been achieved. dop_feedback_provided Fired when SQL Server provides a new DOP recommendation for a query. Includes baseline and feedback stats. dop_feedback_reassessment_failed Indicates that a previously persisted feedback DOP was reassessed and found to be invalid, restarting the feedback cycle. dop_feedback_reverted Fired when feedback is rolled back due to performance regression. Includes baseline and feedback stats. dop_feedback_stabilized Indicates that feedback has been validated and stabilized. After stabilization, additional adjustment to the feedback can be made when the system reassesses the feedback on a periodic basis. 🔍 Understanding the feedback_data JSON in DOP feedback In the "How it works" section of this article, we had provided a sample script that showed some of the data that can be persisted within the sys.query_store_plan_feedback catalog view. When DOP feedback stabilizes, SQL Server stores a JSON payload in the feedback_data column of that view, figuring out how to interpret that data can sometimes be challenging. From a structural perspective, the feedback_data field contains a JSON object with two main sections; LastGoodFeedback and BaselineStats. As an example { "LastGoodFeedback": { "dop": "2", "avg_cpu_time_ms": "12401", "avg_adj_elapsed_time_ms": "12056", "std_cpu_time_ms": "380", "std_adj_elapsed_time_ms": "342" }, "BaselineStats": { "dop": "4", "avg_cpu_time_ms": "17843", "avg_adj_elapsed_time_ms": "13468", "std_cpu_time_ms": "333", "std_adj_elapsed_time_ms": "328" } } Section Field Description LastGoodFeedback dop The DOP value that was validated and stabilized for future executions. avg_cpu_time_ms Average CPU time (in milliseconds) for executions using the feedback DOP. avg_adj_elapsed_time_ms Adjusted elapsed time (in milliseconds), excluding irrelevant waits. std_cpu_time_ms Standard deviation of CPU time across executions. std_adj_elapsed_time_ms Standard deviation of adjusted elapsed time. BaselineStats dop The original DOP used before feedback was applied. avg_cpu_time_ms Average CPU time for the baseline executions. avg_adj_elapsed_time_ms Adjusted elapsed time for the baseline executions. std_cpu_time_ms Standard deviation of CPU time for the baseline. std_adj_elapsed_time_ms Standard deviation of adjusted elapsed time for the baseline. One method that can be used to extract this data could be to utilize the JSON_VALUE function: SELECT qspf.plan_id, qs.query_id, qt.query_sql_text, qsp.query_plan_hash, qspf.feature_desc, -- LastGoodFeedback metrics JSON_VALUE(qspf.feedback_data, '$.LastGoodFeedback.dop') AS last_good_dop, JSON_VALUE(qspf.feedback_data, '$.LastGoodFeedback.avg_cpu_time_ms') AS last_good_avg_cpu_time_ms, JSON_VALUE(qspf.feedback_data, '$.LastGoodFeedback.avg_adj_elapsed_time_ms') AS last_good_avg_adj_elapsed_time_ms, JSON_VALUE(qspf.feedback_data, '$.LastGoodFeedback.std_cpu_time_ms') AS last_good_std_cpu_time_ms, JSON_VALUE(qspf.feedback_data, '$.LastGoodFeedback.std_adj_elapsed_time_ms') AS last_good_std_adj_elapsed_time_ms, -- BaselineStats metrics JSON_VALUE(qspf.feedback_data, '$.BaselineStats.dop') AS baseline_dop, JSON_VALUE(qspf.feedback_data, '$.BaselineStats.avg_cpu_time_ms') AS baseline_avg_cpu_time_ms, JSON_VALUE(qspf.feedback_data, '$.BaselineStats.avg_adj_elapsed_time_ms') AS baseline_avg_adj_elapsed_time_ms, JSON_VALUE(qspf.feedback_data, '$.BaselineStats.std_cpu_time_ms') AS baseline_std_cpu_time_ms, JSON_VALUE(qspf.feedback_data, '$.BaselineStats.std_adj_elapsed_time_ms') AS baseline_std_adj_elapsed_time_ms FROM sys.query_store_plan_feedback AS qspf JOIN sys.query_store_plan AS qsp ON qspf.plan_id = qsp.plan_id JOIN sys.query_store_query AS qs ON qsp.query_id = qs.query_id JOIN sys.query_store_query_text AS qt ON qs.query_text_id = qt.query_text_id WHERE qspf.feature_desc = 'DOP Feedback' AND ISJSON(qspf.feedback_data) = 1; 🧪 Why This Matters This JSON structure is critical for: Debugging regressions: You can compare baseline and feedback statistics to understand if a change in DOP helped or hurt a set of queries. Telemetry and tuning: Tools can be used to parse this JSON payload to surface insights in performance dashboards. Transparency: It provides folks that care about the database visibility into how SQL Server is adapting to their workload. 📚 Learn More Intelligent Query Processing: degree of parallelism feedback Degree of parallelism (DOP) feedback Intelligent query processing in SQL databases Microsoft SQL Server
