Troubleshooting Performance issues Like Microsoft Engineers Part 4


In the previous parts, we explained How we can Troubleshooting the Performance issue using built-in reports in SQL Server and How we can collect SQL Server logs using PSSDIAG and BPCheck then we explained How we can analyze and analyze the output of the PSSDIAG tool using SQL Nexus and PAL tool, check the previous parts from here  (Part 1, Part 2Part 3)

Today in Part 4 we will talk about Some of the cases that can lead us for AG latency Yes (SQL Server Always on Latency) one of the most common performance issues is AG latency especially when you implemented and configure the SQL Server Always on between multi-subnet, at this time you can see (HADR_SYNC_COMMIT) wait are very high or if you found the log send queue size is high at this time the SQL Server Database log files will keep increased in the primary and not reduced because of the Log Queue size on the secondary are very high and this meaning something is blocking the log Queue on the secondary, let us see what is HADR_SYNC_COMMIT and compression on AG Log transport

Check this Article to access all of the Parts of Troubleshooting Performance issues Like Microsoft Engineers

PART 4
Continue reading “Troubleshooting Performance issues Like Microsoft Engineers Part 4”

Troubleshooting Performance issues Like Microsoft Engineers


IF you are a Database administrator and managing SQL Server Database so this article for you, this article is a parent for 3 parts for How to troubleshoot the performance issues on SQL Servers such as Microsoft engineers and support team.

Part 1

In the first part, I explained the built tools and reports in SQL Server and some other scripts free you can use it for troubleshooting performance issues.

✅Check the first part for here 👉 https://mostafaelmasry.com/2020/09/09/troubleshooting-performance-issues-like-Microsoft-engineers-part-1/

Part 2

In the second part, I explained How we can collect all of the SQL Server logs during the performance issues using the PSSDIAG tool.

✅Check the second part for here 👉 https://mostafaelmasry.com/2020/09/09/troubleshooting-performance-issue-like-Microsoft-engineers-part-2/

Part 3

In the third part, I explained How we can load and analyze the output of the PSSDIAG tool using the SQL Nexus tool and Performance Analysis of Logs (PAL) tool.

✅Check the third part for here 👉 https://mostafaelmasry.com/2020/09/09/troubleshooting-performance-issues-like-Microsoft-engineers-part-3/

PART 4

In this part we will talk about Some of the cases that can lead us for AG latency

✅Check PART 4 for here 👉 https://mostafaelmasry.com/2020/09/10/troubleshooting-performance-issues-like-microsoft-engineers-part-4/

Keep Following

Troubleshooting Performance issues like Microsoft Engineers Part 3


If you don’t read the previous parts you can read it from here (PART1, PART2), in this part of today we will explain how we can analyze the output of PSSDIAG using:

  1. SQL Nexus
  2. SQL-Nexus-and-ReadTrace-Analysis-Scripts
  3. Performance Analysis of Logs (PAL)

in the previous part we explained how we can collect the most sensitive information and logs during the performance issue using PSSDIAG and today we will take the output of PSSIDAG and we will analyze it using SQL Nexus

All Parts (Part 1, Part 2, Part 3, Part 4)

PART 3
Continue reading “Troubleshooting Performance issues like Microsoft Engineers Part 3”

Troubleshooting Performance issue like Microsoft Engineers Part 2


In the first PART we talked about the built-in tools in SQL Server management studio and some other custom packages scripts can help us in troubleshooting performance issues, but what we can do if these tools did not support us by what we expecting and we need more information for more investigation, Most of us in the critical cases we will open case wit Microsoft support, and the first thing Microsoft engineer do it, is collecting the data during the issue, to be able to analysis it and today in this part we will cover the tools that are used by Microsoft engineers for collecting the data such as PSSDIAG tool and the upcoming part No 3 we will talk about the tools used for analyzing the output results of PSSDIAG tool such as (SQL Nexus, Performance Analysis of Logs (PAL))

All Parts (Part 1, Part 2, Part 3, Part 4)

This image has an empty alt attribute; its file name is image.png
Part 2
Continue reading “Troubleshooting Performance issue like Microsoft Engineers Part 2”

Query Store for Solving Query Performance Regressions


Query performance is a very important area of SQL server. We always have badly performance queries around.

Query store is the newest tool for tracking and resolving performance problems in SQL server.

In this article, we are going to have a look at some practical uses of SQL server Query store.

What is Query Store?

The query store has been described by Microsoft as a ‘flight data recorder’ for SQL server queries. It tracks the queries run against the database, recording the details of the queries and their plans and their runtime characteristics. Query store is per database feature and runs automatically. Once turned on, nothing further needs to be done to get it to track data. It runs in the background collecting data and storing it for later analysis.

Query store is available in SQL Server 2016 and later, and Azure SQLDB v12 and later. It is available in all editions of SQL server, even in Express edition.

How is Query store different from other tracking options?

We have had query performance tracking for some time though in the form of dynamic management views. Mostly, sys.dm_exec_query_stats and sys.dm_exec_query_plan and tracing tools like SQL server profiler and extended events.

So, what makes Query Store different? Let me start answering that by describing a scenario that I encountered a couple of years ago.

A particular critical system was suddenly performing badly. It had been fine the previous week and there have been no extended events sessions or profiler traces running historically. The admin had restarted the server when the performance problem started, just to make sure it was not something related to a pending reboot.

As such, there was no historical performance data at all and solving the problem of what happened, why the query performance is different this week was extremely difficult.

1

Continue reading “Query Store for Solving Query Performance Regressions”