SQL Server Triggers: Understanding and Alternatives

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. (more…)

Example of Improving Query Performance with Indexes

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.

(more…)

Implementing a Common MS SQL Server Performance Indicator

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.

(more…)

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.

(more…)

SQL Server Index Backward Scan: Understanding, Tuning

Table indexing strategy is one of the most important performance tuning and optimization keys. In SQL Server, the indexes (both, clustered and nonclustered) are created using a B-tree structure, in which each page acts as a doubly linked list node, having an information about the previous and the next pages. This B-tree structure, called Forward Scan,  makes it easier to read the rows from the index by scanning or seeking its pages from the beginning to the end. Although the forward scan is the default and heavily known index scanning method, SQL Server provides us with the ability to scan the index rows within the B-tree structure from the end to the beginning. This ability is called the Backward Scan. In this article, we will see how this happens and what are the pros and cons of the Backward scanning method. (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.

(more…)

Analysis of MS SQL Server for those who see it for the first time – Part 2

Analysis of MS SQL Server for those who see it for the first time – Part 1

We continue to analyze what is happening on our MS SQL Server. In this article, we are going to explore how to retrieve information about user performance: who makes what, and how much resources are consumed.

I think the second part will be interesting for both database administrators and developers who need to understand what is wrong with the requests on the production server that used to work fine on the test server.

(more…)