SQL Server Replication Enhancement – Dynamic Reloading of Agent Profile Parameters

With SQL Server 2017 Cumulative Update 3, we introduced an improvement to SQL Server Replication wherein, changes to the Replication agent parameters can be reloaded dynamically, without having to restart the agent. This improvement will be available in upcoming updates for SQL Server 2016 and SQL Server 2014. Dynamic Reloading of Agent Parameters Replication uses…

2

More Showplan enhancements – UDFs

As I mentioned on yesterday’s post, with the recent release of SQL Server 2017 CU3, we released yet more showplan enhancements: you can see other posts related to showplan enhancements here. In this article I’ll talk about the second showplan improvement we worked on, to assist in the discoverability of UDF usage impact on query…

0

More Showplan enhancements – Row Goal

As I shared before, we have been working for over a year to make showplan the one-stop-shop for query performance analysis and troubleshooting (as much as possible). With the recent release of SQL Server 2017 CU3, we released yet more showplan enhancements: you can see other posts related to showplan enhancements here. In this article…

0

Analyze Synchronous Commit Impact on High Commit Rate Workloads

Recently the SQL Server Always On Support team at Microsoft CSS published a blog on analyzing the performance impact of Synchronous Commit AG’s with workloads that perform a large number of small transactions. For Synchronous AG’s every transaction commit needs to be hardened on the transaction log of the secondary replica and the hardening has…

1

New in SSMS – Always On Availability Group Latency Reports

With SQL Server 2012 we introduced Always On Availability Groups, and the Always On Availability Group Dashboard in SQL Server Management Studio (SSMS). This dashboard can be utilized by database administrators to view the current health of an Availability Group and its availability replicas and databases. While the dashboard can be configured to provide information…

14

Upgrade your SQL Server to scale before adding new hardware

In SQL Server Tiger team, we closely partner with our customers and partners to ensure they can successfully run and scale their Tier 1 mission critical workloads and applications on SQL Server. Based on our experience and interactions with customers, we found that most customers running older releases of SQL Server and experiencing performance and…


How we made backups faster with SQL Server 2017

In my previous blog post on enhancements in SQL Server 2017, we briefly introduced improved backup performance for smaller databases in SQL Server 2017 on high end servers. In the recent PASS Summit 2017, Pedro and I delivered a session on Intelligent diagnostics where we explained how we leveraged the indirect checkpoint heuristics to enable…


Perfect statistics histogram in just few steps

A similar question about statistics came to the team twice this week: why does updating with fullscan result in fewer histogram steps than when doing a sampled scan? The answer is: the number of histogram steps can be fewer than the number of distinct values. For a histogram with perfect distribution (frequency* = 1), the…

0

Making parallelism waits actionable

During PASS we announced changes to how users will be able to make parallelism waits more actionable – specifically the “infamous” CXPACKET. This change effectively splits CXPACKET waits into an actionable wait (CXPACKET) and a negligible wait (CXCONSUMER). This change will be effective starting with SQL Server 2017 CU3 and upcoming SQL Server 2016 SP2…

5