Category: SQL Server

Identifying and Fixing Forwarded Records Performance Issue

Ahmad Yaseen Leave a comment

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.

(more…)

Implementing Failover in MS SQL Server 2017 Standard

Evgeniy Gribkov Leave a comment

Introduction

Often, we need to ensure fault tolerance of MS SQL Server DBMS, especially, when there is no Enterprise edition, but only the Standard one.

We would like to note that we are not going to examine the Express edition because there are significant restrictions to this instance. Sure, we can bypass some of them. For example, to resolve the issue with the database size of 10 GB, we can split a large database into smaller ones. To do this, we can create a new database based on a certain property, and combine the selections from the same tables of different databases in the views in the principal database. However, fault tolerance in the Express edition will be performed either by a system administrator or by using your own or third-party software.

In this article, we are going to explore all existing standard fault-tolerance technologies for MS SQL Server 2017 and an example of implementing the most suitable unified standard of fault-tolerance in the Standard edition.

(more…)

Getting Started with SQL Server 2017 on Linux in the Azure portal

Prashanth Jayaram Leave a comment

SQL Server 2017 now is considered as a hybrid database enterprise solution as it expands its market and is ported to other operating system platforms. It also includes mainstream support for Linux machines. The Cloud makes the life of administrator much easier, now it’s no longer daunting task to configure the SQL Server instance. The easiest way to explore SQL Server on Linux is to provision a virtual machine through Microsoft Azure portal – portal.azure.com. The Linux azure virtual machine will come pre-configured with Linux and SQL Server 2017.

(more…)

How to Use Default and Custom Widgets in SQL Server Operations Studio

Prashanth Jayaram Leave a comment

There are already a number of articles and blog posts that reveal the benefits of the SQL Operations Studio tool. The demand created across the software and the related tools are relatively unimaginable than ever before. The trend and growth will continue to increase in the upcoming days.

The graphical representation is in the vogue today. Visualizing data helps us to better understand this and to make decisions. It’s no wonder that data visualization continues to attract a growing number of users. The development of any toolset could provide opportunities to speed up the software development lifecycle process.

This article demonstrates the advantages of using custom SQL queries or complex T-SQL to provide a great insight into the database and explains how one can use this insight to build custom widgets. In this case, the SQL Operations Studio Widget is a customized piece of the code to personalize the SQL Server Dashboard for effective management of SQL instances.

(more…)

The Art of Aggregating Data in SQL from Simple to Sliding Aggregations

Haroon Ashraf Leave a comment

Let us start our SQL journey to understand aggregating data in SQL and types of aggregations including simple and sliding aggregations.

Before we jump to the aggregations, it is worth considering interesting facts often missed by some developers when it comes to SQL in general and the aggregation in particular.

In this article, SQL refers to T-SQL which is the Microsoft version of SQL and has more features than the standard SQL. (more…)

Understanding the Importance of Memory Setting in SQL Server

Prashanth Jayaram Leave a comment

Memory is one among the resources forming the performance triangle—CPU and storage being the other two. If one is hit, the other two take the load to try to bring the performance to acceptable levels, but there’s always the trade-off. Whatever transactions cannot be committed to the memory they would be forwarded to the disk subsystem by SQL Server. This causes a performance bottleneck. Therefore, the wait statistics can help identify performance issues on a SQL Server.

In this article, the following topics are discussed:

  1. Understanding internals of SQL Server memory setting and configuration
  2. The SQL Server memory and its impact on the database and application performance
  3. Discuss various SQL Server components that contribute to the memory usage
  4. Best practices and recommendation for memory sizing
  5. Multi-server memory report
  6. And more…

(more…)

SQL Server Table Partitioning

Esat Erkeç Leave a comment

Problem

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.

(more…)