Missing Indexes in MS SQL or Optimization in no Time

When executing a query, the SQL Server optimizer tries to find the best query plan based on existing indexes and available latest statistics for a reasonable time, of course, if this plan is not already stored in the server cache. If no, the query is executed according to this plan, and the plan is stored in the server cache. If the plan has already been built for this query, the query is executed according to the existing plan.

We are interested in the following issue:

During compilation of a query plan, when sorting possible indexes, if the server does not find the best index, the missing index is marked in the query plan, and the server keeps statistics on such indexes: how many times the server would use this index and how much this query would cost.

Read More

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