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. (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.
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.
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…)
I noticed that very few people understand how indexes work in SQL Server, especially Included Columns. Nevertheless, indexes are the great way to optimize queries. At first, I also did not get the idea of the included columns, but my experiments showed that they are very useful. (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.
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.
I’ve recently encountered a problem – SVN went down on ubuntu server. I develop for Windows and I do not have much experience with Linux. I googled the error — without success. The error turned out to be the most typical one (the server unexpectedly closed the connection) and does not indicate anything. Therefore, it was necessary to go deeper and analyze logs/settings/rights/etc.
Finally, I figured out the mistake and found everything I needed, but I spent a lot of time. After solving this problem, I thought about how to reduce the uselessly spent hours and decided to write an article that will help people quickly get the understanding of the unfamiliar software.
There is an information system that I administer. The system consists of the following components:
1. MS SQL Server database
2. Server application
3. Client applications
These information systems are installed on several objects. The information system is used actively 24 hours a day by 2 to 20 users at once on each object. Therefore, you cannot perform routine maintenance all at once. So, I have to «spread» SQL Server index defragmentation throughout the day, rather than defragmenting all the necessary fragmented indexes at one stroke. This applies to other operations as well.
In this post, I’d like to take a brief look at the Query Performance Insight — SQL Azure tool which will help you to identify the most expensive queries in your database.
Query Performance Insights was announced in early October 2015. To understand what it is, let’s think about how do you usually learn that the database performance got down? Probably, you are receiving emails from your clients or it takes an hour to create a weekly report instead of a several minutes, or maybe, your application starts throwing exceptions. (more…)