Using Indexes in SQL Server Memory-Optimized Tables

Introduction

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.

Read 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.

Read 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. Read 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.

Read More

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

Ive 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 errorwithout 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.

Read More

Troubleshooting long running queries in MS SQL Server

Preface

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.

Read More

Query Performance Insight: Discovering What Consumes Resources of Your Azure SQL Database?

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. Read More