Renaming Indexes with sp_rename Procedure

Total: 2 Average: 5

Not so long ago, I was working on a project where we needed to change the data type of a table. The table had millions of rows, and we decided to create a separate table using the existing table definition and insert the rows into that table. After exporting data into the new table, we renamed the old table, constraint, and indexes using the sp_rename stored procedure.

The current article is dedicated to the ways of renaming indexes. 

Read More

Create a Database Diagram Using dbForge Database ER Diagram Tool for SQL Server

Total: 1 Average: 5

SQL Server database diagram is a graphical representation of database tables. It visualizes the database structure and allows you to understand the overall database schema and relations. It lets you also manage the database schema from the diagram page by creating and modifying database tables, columns, dependencies, indexes, constraints, and keys.

Read More

SQL Server Indexes Management Using Index Manager for SQL Server

Total: 1 Average: 5

SQL Server Index Overview

When talking about SQL Server performance tuning and queries enhancement, the first thing to consider is the SQL Server Index. It serves to accelerate reading data from underlying tables by providing quick access to the requested rows. Thus, it won’t need to scan all the table’s records.

The SQL Server index provides those fast search capabilities due to the B-Tree structure of the index. This structure makes it possible to move through the table rows based on the index key quickly and retrieve the requested records at once. It won’t need to read the whole table.

Read More

Tutorial on SQL (DDL, DML) on the example of MS SQL Server dialect

Total: 4 Average: 4.3

Introduction

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

Query Store: Showing the Impact of Indexes on Inserts

Total: 7 Average: 4.1

Introduction

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

How to Proactively Gather SQL Server Indexes Fragmentation Information

Total: 11 Average: 3.8

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.

Read More

Identifying and Fixing Forwarded Records Performance Issue

Total: 5 Average: 3.4

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.

Read More