Query Optimization in PostgreSQL. EXPLAIN Basics – Part 2

In my previous article, we started to describe the basics of the EXPLAIN command and analyzed what happens in PostgreSQL when executing a query.

I am going to continue writing about the basics of EXPLAIN in PostgreSQL. The information is a short review of Understanding EXPLAIN by Guillaume Lelarge. I highly recommend reading the original since some information is missed out.

Read More

Query Optimization in PostgreSQL. EXPLAIN Basics – Part 1

 

Why does it take so much time to execute a query? Why are there no indexes? Chances are you’ve heard about EXPLAIN in PostgreSQL. However, there are still many people who have no idea how to use it. I hope this article will help users tackle with this great tool.

This article is the author revision of Understanding EXPLAIN by Guillaume Lelarge. Since I have missed out some information, I highly recommend you get acquainted with the original.

Read More

What Can Query Plan Tell?

Introduction

SQL query describes the expected result, not the way to get the result. The set of specific steps the server must take to return the result is called the query execution plan. The plan is built by the optimizer. Selection of a plan affects execution speed, what makes it one of the most important elements of the query performance problem analysis.

Execution plan comprises operators and their properties that are interrelated with each other in the form of the tree structure. Each operator is responsible for a separate logical or physical operation. All together, they ensure the result described in the query text. Inside the tree, operators are represented by the class objects in the memory of SQL Server. Server users (that is, you and me) see the description generated in XML format with a specific schema, that is displayed graphically by the SQL Server Management Studio (SSMS) environment.

There are many various plan operators and even more properties. Besides, new ones emerge from time to time. This article does not dare to describe all possible variety of operators. Instead, I would like to share the most interesting additions in this subject and to remind some old but useful elements. Read More

Optimizing Overlapping Queries Part 1: Introduction & Enhanced T-SQL Solution

Databases that serve business applications should often support temporal data. For example, suppose a contract with a supplier is valid for a limited time only. It can be valid from a specific point in time onward, or it can be valid for a specific time interval—from a starting time point to an ending time point. In addition, many times you need to audit all changes in one or more tables. You might also need to be able to show the state at a specific point in time or all changes made to a table in a specific period of time. From the data integrity perspective, you might need to implement many additional temporal specific constraints.

Read More

Implementing SQL Server Performance Indicator for Queries, Stored Procedures and Triggers

Preface

Sooner or later, a DB administrator would like to have a performance indicator for SQL Server queries. As we all know, running Profiler for 24 hours will lead to a considerable system load and therefore, it cannot be considered an optimal solution for databases used in the 24/7 mode.

So, how can we detect the state of SQL Server queries? How can we run trace for detected query-related problems without the human input?

In this article, I will provide an implementation of the SQL Server performance indicator for queries, stored procedures and triggers, as well as its usage for the trace run. Read More