Automatic Deletion of Stuck Processes in MS SQL Server

Introduction

There are situations when applications keep the database connection for a long period of time. It seems to be not important. However, if this application makes many connections or there are several applications with such behavior — things are getting worse.

This article is not a tutorial. It describes possible solutions to this issue. As usual, I will be glad to hear any alternative solutions.

(more…)

Comparing Objects by Value. Part 6: Structure Equality Implementation

We have already analyzed peculiarities of structs of the .NET framework that represent Value Types when comparing objects by value – instance of structs.

Now, I am going to describe this process on a particular example to check whether it will allow us to determine the use of the object comparison by value in general and thus, to simplify a sample of comparing objects by value – class instances that represent reference types.

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

(more…)

Comparing Objects by Value. Part 4: Inheritance & Comparison Operators

In the previous article, we analyzed how to compare objects by value on a particular example with the Person class that includes:

(more…)

Implementing SQL Server Performance Indicator for Queries, Stored Procedures and Triggers

Preface

Sooner or later, a DB administrator would like to have a performance indicator for SQL Server queries. As we all know, running Profiler for 24 hours will lead to a considerable system load and therefore, it cannot be considered an optimal solution for databases used in the 24/7 mode.

So, how can we detect the state of SQL Server queries? How can we run trace for detected query-related problems without the human input?

In this article, I will provide an implementation of the SQL Server performance indicator for queries, stored procedures and triggers, as well as its usage for the trace run. (more…)

Automating Index Defragmentation in MS SQL Server Database

Preface

The World Wide Web offers a bunch of information on SQL Server index defragmentation or SQL Server  index rebuild. However, most of the recommendations refer to databases that have minimum load time (mostly, at night).

And what about databases that are used for both, data modification, and retrieving information on a 24/7 basis?

In this article, I will provide a mechanism for automating SQL Server index defragmentation implemented in a database used in the company I work for. This mechanism allows defragmenting required indexes on a regular basis since index fragmentation takes place constantly in the 24/7 system. Often, this is not enough to perform index defragmentation once a day. (more…)

Insight into SQL Server Unique Constraints

What are unique key constraints?

A unique constraint is a rule that restricts column entries to unique. In other words, this type of constraints prevents inserting duplicates into a column. A unique constraint is one of the instruments to enforce data integrity in an SQL Server database. Since a table can have only one primary key, you can use a unique constraint to enforce the uniqueness of a column or a combination of columns that do not constitute a primary key.

Creating a unique constraint on a column automatically creates a unique index. This way SQL Server implements the integrity requirement of the unique constraint. Therefore, when attempting to insert a duplicate value into a column, on which a unique constraint is defined, the Database Engine will detect the unique constraint violation and issue a corresponding error. As a result, the row with the duplicate values will not be added to a table.

(more…)

Comparing Objects by Value. Part 3: Type-specific Equals and Equality Operators

In Part 1 and Part 2, we have analyzed how to modify a class to compare objects by value.

Now, we will explore a type-specific implementation of how to compare objects by value including the IEquatable(Of T) generic interface and overload of “==” and “!=” operators.

Type-specific comparison of objects by value allows achieving:

  • a more stable, scalable and mnemonic (readable) code through overloaded operators;
  • higher performance.

(more…)