In this article, we will talk about two query optimization myths and these myths can be changed according to the characteristics of the queries. Every query can exhibit different behaviors according to data distribution, SQL Server version, database settings, and other tons of parameters therefore the fixed ideas may not help to overcome the query performance issues. In the next part of the article, we will focus on the following most known discourse:
Read more »
In this article, we will focus on how we can detect the parameter sniffing issues with different techniques.
Read more »
In this article, we will learn what is plan regression and how we can fix this issue with help of the Automatic Plan Correction feature.
Read more »
In this article, we will discuss how to read the SQL Server execution plan (query plan) with all aspects through an example, so we will gain some practical experience that helps to solve query performance issues. Interpreting query plans correctly is the first and major principle to troubleshoot query performance issues. When we try to find an answer to the “why is this query running slow?” question, the best starting point would be to analyze the query plan.
Read more »
In this article, we will explore how the ORDER BY statement affects the query performance and we will also learn some performance tips related to sorting operations in SQL Server.
Read more »
The SQL Server execution plan (query plan) is a set of instructions that describes which process steps are performed while a query is executed by the database engine. The query plans are generated by the query optimizer and its essential goal is to generate the most efficient (optimum) and economical query plan. Some query plans that are created by the query optimizer contain some interesting characteristics. In this article, we will go into details of these interesting query plans.
Read more »
Gaining experience in SQL query tuning can be very difficult and complicated for database developers or administrators. For this reason, in this article, we will work on a case study and we are going to learn how we can tune its performance step by step. In this fashion, we will understand well how to approach query performance issues practically.
Read more »
Troubleshooting using Wait Stats in SQL Server is an important perspective when it comes to managing databases. As a database professional, you might have come across situations, where your end-users are not happy with reports being slower. It will leave you to find the reason for the report slowness.
Read more »
In this article, we will talk about the query tuning features that were announced with SQL Server 2019.
Read more »
In this article, we will uncover some secrets about the SQL Server tempdb database. Tempdb is a system database and it is used for various internal and user operations. Besides this, the tempdb has many unique characteristics, unlike the other databases. When we take into account all of these features of the tempdb, there is no doubt that it is an essential part of the SQL Server.
Read more »
In this article, we will explore, how we can increase SQL Server performance with the help of the wait statistics. Wait statistics are one of the most important indicators to identify performance issues in SQL Server. When we want to troubleshoot any performance issue, at first we need to diagnose the problem correctly because correctly diagnosing problems help with half of the solution. Now, let’s learn wait statistics which helps to identify performance issues properly.
Read more »
In this article, we will learn the SQL Server clustered index concept and some internal details. Indexes are the database objects that accelerate the performance of data accessing when are designed properly. A clustered index is one of the main index types in SQL Server and the working principle is a bit complicated but in the next sections of this article, we are going to simply learn the clustered index working principle and uncover the secrets.
Read more »
In this article, we will learn how to use Apache JMeter to perform a load test on SQL Server. This test type enables us to measure the application behaviors under specific conditions so that it enables us to observe a variety of resource consumptions (CPU, memory, latency, response times, etc) and it also helps to detect the performance bottlenecks. Database testing can help to find out and identify the problem when the database working under high workloads with multiple users. The database people can identify and fix the performance issues before publishing the database into the production through these tests.
Read more »
In this article, we will learn the Delayed Durability feature that helps to improve transaction log file write throughput in SQL Server.
Read more »
In this article, we will explore a new SQL Server 2019 feature which is Scalar UDF (scalar user-defined) inlining. Scalar UDF inlining is a member of the intelligent query processing family and helps to improve the performance of the scalar-valued user-defined functions without any code changing.
Read more »
In this article, we will learn some basic tips for SQL Server performance tuning. Tuning the SQL Server performance will help to access data faster so the applications data interaction performances will enhance.
Read more »
In this article, we will learn how to test our storage subsystems performance using Diskspd. The storage subsystem is one of the key performance factors for SQL Server because SQL Server storage engine stores database objects, tables, and indexes on the physical files. Therefore, the storage engine always interacts with the disk subsystem because of data processing. In this context, when a bottleneck occurs on the storage subsystems, it causes a negative impact on SQL Server performance. It would be the right approach to measure the performance of the disks to be used before the SQL Server installation based on their usage purposes. For example, OLTP databases have to complete delete, insert, and update processes in a short time but OLAP databases handle a huge amount of batch data. In this case, the storage requirement of these two database systems should differ from each other. In short, it is a best practice to test and analyze the performance of the storage subsystems according to their usage purposes so that we can eliminate the I/O problems in advance.
Read more »
In this article, we will talk about the deadlocks in SQL Server, and then we will analyze a real deadlock scenario and discover the troubleshooting steps.
Read more »
In this article, we will discuss the Dirty Read concurrency issue and also learn the details of the Read Uncommitted Isolation Level.
Read more »
When dealing with SQL Server performance tuning waits, we may see RESOURCE_SEMAPHORE waits along with other related monitoring that indicates memory as a possible pain point for our server (such as the below image that shows memory being one of the top waits overall).
Read more »
In this article, we will analyze a simple T-SQL query execution plan with different aspects. This will help us to improve our practical skills instead of discussing theoretical knowledge.
Read more »
In this article, we will learn the basics of Parallel Execution Plans, and we will also figure out how the query optimizer decides to generate a parallel query plan for the queries.
Read more »
In this article, we will explain batch mode on rowstore feature, which was announced with SQL Server 2019. The main benefit of this feature is that it improves the performance of analytical queries, and it also reduces the CPU utilization of these types of queries. Behind the scene, this performance enhancement uses the batch mode query processing feature for the data, which is stored in row format. Also, this feature has been using by columnstored indexes for a long time.
Read more »
In this article, we will learn how to identify and resolve Index Fragmentation in SQL Server. Index fragmentation identification and index maintenance are important parts of the database maintenance task. Microsoft SQL Server keeps updating the index statistics with the Insert, Update or Delete activity over the table. The index fragmentation is the index performance value in percentage, which can be fetched by SQL Server DMV. According to the index performance value, users can take the indexes in maintenance by revising the fragmentation percentage with the help of Rebuild or Reorganize operation.
Read more »
In this article, we will continue to learn essential techniques of the SQL Server query tuning with practical examples.
Read more »© Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy