Automatic Data Collection about Completed Tasks in MS SQL Server

Automatic Data Collection about Completed Tasks in MS SQL Server
Rate this post

Introduction

It is important for a database administrator to know what tasks and how have they been completed. To simplify this process, it is better to automate it, rather than perform it manually.

In this article, I will analyze on a particular example how to automatically collect data about completed tasks of the SQL Server Agent.

Read More

Optimizing Overlapping Queries Part 1: Introduction & Enhanced T-SQL Solution

Optimizing Overlapping Queries Part 1: Introduction & Enhanced T-SQL Solution
Rate this post

Databases that serve business applications should often support temporal data. For example, suppose a contract with a supplier is valid for a limited time only. It can be valid from a specific point in time onward, or it can be valid for a specific time interval—from a starting time point to an ending time point. In addition, many times you need to audit all changes in one or more tables. You might also need to be able to show the state at a specific point in time or all changes made to a table in a specific period of time. From the data integrity perspective, you might need to implement many additional temporal specific constraints.

Read More

Automatic Deletion of Stuck Processes in MS SQL Server

Automatic Deletion of Stuck Processes in MS SQL Server
Rate this post

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.

Read More

Comparing Objects by Value. Part 6: Structure Equality Implementation

Comparing Objects by Value. Part 6: Structure Equality Implementation
Rate this post

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.

Read More

Comparing Objects by Value. Part 5: Structure Equality Issue

Comparing Objects by Value. Part 5: Structure Equality Issue
4 (80%) 1 vote

In my previous publication, I described the fullest and the most correct way to compare objects by value – class instances that represent reference types in the .NET framework.

Now, I am going to determine how it is possible to modify the proposed method to compare instances of the structs that represent value types.

Read More

Troubleshooting long running queries in MS SQL Server

Troubleshooting long running queries in MS SQL Server
3.4 (68.57%) 7 votes

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

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

Comparing Objects by Value. Part 4: Inheritance & Comparison Operators
3 (60%) 6 votes

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

Read More

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

Implementing SQL Server Performance Indicator for Queries, Stored Procedures and Triggers
4.3 (86.67%) 3 votes

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