Database Administrator always makes an effort to tune SQL Server query performance. The first step in tuning query performance is to analyze the execution plan of a query. Upon some conditions, SQL Server Query Optimizer can create different execution plans. At this point, I would like to add some notes about SQL Server Query Optimizer. SQL Server Query Optimizer is a cost-based optimizer that analyzes execution plans and decides the optimal execution plan for a query. The significant keyword for the SQL Server Query Optimizer is an optimal execution plan which is not necessarily the best execution plan. That’s why, if SQL Server Query Optimizer tries to find out the best execution plan for every query, it takes extra time and it causes damage to SQL Server Engine performance. Read More
Query store is a new feature, introduced in SQL Server 2016, that allows database administrators to historically review queries and their associated plans using the GUI available in SQL Server Management Studio, as well as to analyze query performance using certain Dynamic Management Views. Query Store is a database scoped configuration option and is available for use if the compatibility level of the database in question is 130.
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 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.
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.
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. Read More
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.