Let’s talk about trace flags

One of the most confusing aspects of SQL Server configuration is often trace flags. There are lots of trace flags out there, and while many of them are documented, when to use them and when not to is not always clear. The primary reason for this post is to introduce a new article from Microsoft…

3

Developer’s Choice: Hinting Query Execution model

Over the years you have read a number of blogs advocating for or against trace flags that influence SQL Server’s query execution model. You can see a number of query execution related trace flags are documented at http://aka.ms/traceflags. However, trace flags are deemed a hacky way of influencing SQL Server’s behaviors – they’re named trace…

0

TEMPDB – Files and Trace Flags and Updates, Oh My!

TL;DR – Update to the latest CU, create multiple tempdb files, if you’re on SQL 2014 or earlier enable TF 1117 and 1118, if you’re on SQL 2016 enable TF 3427. And now it’s time for everyone’s favorite SQL Server topic – tempdb! In this article, I’d like to cover some recent changes that you…

1

Optimizing Replication Agent profile parameters for better performance

When SQL Server replication is used on environments with high traffic OLTP systems, users often need to adjust the agent profile parameters to increase the throughput of the log reader and distribution agents to keep up with the workload. We recently performed a series of tests to measure the performance of log reader and distribution…

0

Replication Enhancement – Improved Distribution Database Cleanup

Replication Distribution database is the perhaps the most important entity in the entire replication topology. Apart from storing the replication metadata and history for replication agents, the distribution database, in case of transactional replication, also provides intermediate storage for the transactions and commands that will be shipped to the subscribers. These transactions and commands stored…

0

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

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

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…


New in SSMS: Performance Dashboard built-in

Back in 2007, we released the Microsoft SQL Server 2005 Performance Dashboard Reports, which were designed to provide fast insight into performance issues from some newly created system views – DMFs (Dynamic Management Views). These were updated for SQL Server 2008 and later to SQL Server 2012, and while being very helpful they had a…


Correlating xEvents with DMVs

Since the inception of xEvents there has been the need to occasionally correlate data from xEvent collections and DMVs. Specifically, using the query_hash and query_plan_hash actions and correlate with DMVs such as sys.dm_exec_requests and sys.dm_exec_query_stats for example. The blog post on Correlating XE query_hash and query_plan_hash to sys.dm_exec_query_stats in order to retrieve Execution plans for…