Maintenance plans in SQL Server give us an easy way to organize, configure, and schedule tasks that ensure that the database engine and the databases that are hosted therein are kept in shape.Read More
This tutorial includes information about SQL (DDL, DML) that I have gathered during my professional life. This is the minimum you need to know while working with databases. If there is a need to use complex SQL constructions, then usually I surf the MSDN library, which can be easily found on the internet. To my mind, it is very difficult to keep everything in your head and, by the way, there is no need for this. I recommend that you should know all the main constructions used in most relational databases such as Oracle, MySQL, and Firebird. Still, they may differ in data types. For example, to create objects (tables, constraints, indexes, etc.), you may simply use integrated development environment (IDE) to work with databases and there is no need in studying visual tools for a particular database type (MS SQL, Oracle, MySQL, Firebird, etc.). This is convenient because you can see the whole text, and you do not need to look through numerous tabs to create, for example, an index or a constraint. If you are constantly working with databases, creating, modifying, and especially rebuilding an object using scripts is much faster than in a visual mode. Besides, in my opinion, in the script mode (with due precision), it is easier to specify and control rules for naming objects. In addition, it is convenient to use scripts when you need to transfer database changes from a test database to a production database. Read More
It is common knowledge in database circles that indexes improve query performance either by satisfying the required result set entirely (Covering Indexes) or acting as lookups which easily direct the Query Engine to the exact location of the required data set. However, as experienced DBAs know, one should not be too enthusiastic about creating indexes in OLTP environments without understanding the nature of the workload. Using Query Store in SQL Server 2019 instance (Query Store was introduced in SQL Server 2016), it is quite easy to show the effect of an index on inserts. Read More
Introduction to SQL Server Indexes
Microsoft SQL Server is considered as one of the relational database management systems (RDBMS), in which the data is logically organized into rows and columns that are stored in data containers called tables. Physically, the tables are stored as 8 KB pages that can be organized into Heap or B-Tree Clustered tables. In the Heap table, there is no sorting order that controls the order of the data inside the data pages and the sequence of pages within that table, as there is no Clustered index defined on that table to enforce the sorting mechanism. If a Clustered index is defined on one column of the group of table columns, the data will be sorted inside the data pages based on the values of the Clustered index key columns, and the pages will be linked together based on these index key values. This sorted table is called a Clustered table.
Before going through the Forwarded Records performance issue and resolving it, we need to review the structure of the SQL Server tables.
Table Structure Overview
In SQL Server, the fundamental unit of the data storage is the 8-KB Pages. Each page starts with a 96-byte header that stores the system information about that page. Then, the table rows will be stored on the data pages serially after the header. At the end of the page, the row offset table, that contains one entry for each row, will be stored opposite to the sequence of the rows in the page. This row offset entry shows how far the first byte of that row is located from the start of the page.
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
In this article, we will discuss how different types of indexes in SQL Server memory-optimized tables affect performance. We will examine examples of how different index types can affect the performance of memory-optimized tables.
To make the topic discussion easier, we will make use of a rather large example. For the purposes of simplicity, this example will feature different replicas of a single table, against which we will run different queries. These replicas will use different indexes, or no indexes at all (except, of course, the primary keys – PKs).
Note, that the actual purpose of this article is not to compare performance between disk-based and memory-optimized tables in SQL Server per se. Its purpose is to examine how indexes affect performance in memory-optimized tables. However, in order to have a full picture of the experiments, timings are also provided for the corresponding disk-based table queries and the speedups are calculated using the most optimal configuration of disk-based tables as baselines.
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.
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.