Real-Time Operational Analytics and Non-Clustered Column Store Index

Real-Time Operational Analytics and Non-Clustered Column Store Index
3.5 (70%) 2 votes

In this article, we will focus on real time operational analytics and how to apply this approach to an OLTP database. When we look at the traditional analytical model, we can see OLTP and analytic environments are separate structures. First of all, the traditional analytic model environments need to create ETL (Extract, Transform and Load) tasks. Because we need to transfer transactional data to the data warehouse. These types of architecture have some disadvantages. They are cost, complexity and data latency. In order to eliminate these disadvantages, we need a different approach.  Read More

SQL Server Triggers: Understanding and Alternatives

SQL Server Triggers: Understanding and Alternatives
4.1 (81.38%) 29 votes

The SQL Server trigger is a special type of stored procedures that is automatically executed when an event occurs in a specific database server. SQL Server provides us with two main types of triggers: the DML Triggers and the DDL triggers. The DDL triggers will be fired in response to different Data Definition Language (DDL) events, such as executing CREATE, ALTER, DROP, GRANT, DENY, and REVOKE T-SQL statements. The DDL trigger can respond to the DDL actions by preventing these changes from affecting the database, perform another action in response to these DDL actions or recording these changes that are executed against the database. Read More

Example of Improving Query Performance with Indexes

Example of Improving Query Performance with Indexes
4.5 (90%) 2 votes

In this article, we’ll look at how an index can improve the query performance.

Introduction 

Indexes in Oracle and other databases are objects that store references to data in other tables. They are used to improve the query performance, most often the SELECT statement.

They aren’t a “silver bullet” – they don’t always solve performance problems with SELECT statements. However, they can certainly help.

Let’s consider this on a particular example.

Read More

Implementing a Common MS SQL Server Performance Indicator

Implementing a Common MS SQL Server Performance Indicator
Rate this post

Introduction

There is often a need to create a performance indicator that would show database activity related to the previous period or specific day. In the article titled “Implementing SQL Server Performance Indicator for Queries, Stored Procedures, and Triggers”, we provided an example of implementing this indicator.

In this article, we are going to describe another simple way to track how and how long the query execution takes, as well as how to retrieve execution plans for each time point. 

This method is especially useful in the cases when you need to generate daily reports, so you can not only automate the method but also add it to the report with minimum technical details.

In this article, we will explore an example of implementing this common performance indicator where Total Elapsed Time will serve as a metric.

Read More

Missing Indexes in MS SQL or Optimization in no Time

Missing Indexes in MS SQL or Optimization in no Time
Rate this post

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 3

Query Optimization in PostgreSQL. EXPLAIN Basics – Part 3
4.5 (90%) 2 votes

I continue a series of articles on the basics of EXPLAIN in PostgreSQL, which is a short review of Understanding EXPLAIN by Guillaume Lelarge.
To better understand the issue, I highly recommend reviewing the original “Understanding EXPLAIN” by Guillaume Lelarge and read my first and second articles.

Read More

Query Optimization in PostgreSQL. EXPLAIN Basics – Part 2

Query Optimization in PostgreSQL. EXPLAIN Basics – Part 2
5 (100%) 2 votes

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

Query Optimization in PostgreSQL. EXPLAIN Basics – Part 1
4 (80%) 2 votes

 

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

Why Multiple JOINs are bad for Query or Do Not Get in the Way of Optimizer

Why Multiple JOINs are bad for Query or Do Not Get in the Way of Optimizer
Rate this post

Recently, I came across an application that generated DB queries. I understand that there is nothing new about that, but when application began running slow and I had to find out the reason of the slowdown, I was amazed to find these queries. Here is what SQL Server sometimes has to deal with: Read More