Lesson learned from an Availability Group performance case

Writer: Simon Su Technical Reviewer: Pam Lahoud, Sourabh Agarwal, Tejas Shah  Problem description  One of my customers implemented a very high workload synchronous AG (Availability Group) solution and he needs 10k transactions/sec in AG databases. With the in-memory technology, this 10K/sec goal was achieved but they found a very strange behavior in transaction processing of…

2

Troubleshooting SQL Server Scheduling and Yielding

Writer: Simon Su Technical Reviewer: Pam Lahoud, Sourabh Agarwal, Tejas Shah    Scheduling and Yielding Knowledge Recap  We all know that SQL server is a multi-threads and multi-tasks system and it has its own thread scheduling mechanism which is a small part of job of what we call SQLOS. If you are not familiar with…

0

Troubleshooting data movement latency between synchronous-commit AlwaysOn Availability Groups

Writer: Simon Su Technical Reviewer: Pam Lahoud, Sourabh Agarwal, Tejas Shah Applies to: SQL Server 2014 SP2, SQL Server 2016 SP1, SQL Server 2017 RTM  In synchronous-commit mode AG nodes sometimes you may observe your transactions are pending on HADR_SYNC_COMMIT waits. HADR_SYNC_COMMIT waits indicate that SQL server is waiting for the signal from the remote…

0

Installation of SQL Server 2017 failing with ‘VS Shell installation has failed with exit code 1638’

Dear all, Depending on which products were installed on the server beforehand, a SQL Server 2017 setup may fail with the following error : TITLE: Microsoft SQL Server 2017 Setup —————————— The following error has occurred: VS Shell installation has failed with exit code 1638. Note that it could happen even after SQL Server 2017…


Uniqueifier considerations and error 666

This post is intended to shed some light around uniqueifiers and table design that rely on its usage. First a quick information about the subject. A uniqueifier (or uniquifier as reported by SQL Server internal tools) has been used in the engine for a long time (since SQL Server 7.0), and even being known to…

0

SQL Setup ToolSuite Introduction (3) – SQL Registry Viewer Version 2.2

You may want to know what registry keys will be added to system for a SQL server installation. If you use some registry snapshot tool to compare the window registries change of before and after the SQL installation you will find there are 40000~60000 modifications happening. However if you study the modifications carefully you will…

1

SQL Setup ToolSuite Introduction (2) – Product Browser

Oftentimes when I worked on a setup case I hope I have a simple tool to tell me the detailed installed product properties and patch information. In Windows control panel I can find all installed products and its update but it doesn’t provide detailed information like the package name of a product, the product code…

0

SQL Setup ToolSuite Introduction (1) –FixMissingMSI

If you have encountered missing cached MSI/MSP issues during SQL server setup, you may have knew below article: How to restore the missing Windows Installer cache files and resolve problems that occur during a SQL Server update https://support.microsoft.com/en-us/help/969052/how-to-restore-the-missing-windows-installer-cache-files-and-resolve-p In above link it provides a VB script to help you find out those missing MSI/MSP files…

1

Changes to hashing algorithm for self-signed certificate in SQL Server 2017

Starting with SQL Server 2005, a self-signed certificate is created automatically during the startup to be used for channel encryption. By default, credentials in the login packet that are transmitted when a client application connects to SQL Server are always encrypted using this certificate if a certificate has not been explicitly provisioned for SSL/TLS. Optionally,…


How to safeguard SQL Server on Linux from OOM-Killer

On a Windows based server, when all the available memory including the page file is consumed, the server’s performance becomes sluggish and out of memory errors are logged to the event logs. On Linux systems, the behavior is slightly different. When the server is running low on memory, the Linux kernel will choose a process…