In this article, we will examine Database Scoped Configurations and SQL Server 2017 Automatic Plan Correction. Microsoft added new features to SQL Server 2017 that improved the query performance. Read More
In this article, we will focus on the demonstration of table partitioning. The simplest explanation of table partitioning can be called as dividing large tables into small ones. This topic provides scalability and manageability.
Why do we need table partitioning?
Assume that we have a table and it grows day by day. In this case, the table can cause some problems which need to be solved by the steps defined below:
- Maintain this table. It will take a long time and consume more resources (CPU, IO etc.).
- Back up.
- Lock problems.
In this article, we will discover some best practices of T-SQL queries. Badly written queries can cause performance and I/O problems. For this reason, we should pay attention to keep some rules in our mind when writing T-SQL queries.
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
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
In this article, we’ll look at how an index can improve the query performance.
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.
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.
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.
In this article, we are going to touch upon the topic of performance of table variables. In SQL Server, we can create variables that will operate as complete tables. Perhaps, other databases have the same capabilities, however, I used such variables only in MS SQL Server.
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.