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…)

In Search of Fast Local Storage

Recently, I was involved in the development of the functionality that required a fast and frequent transfer of large volumes of data to disc. In addition, this data was supposed to be read from disk from time to time. Therefore, I was destined to find out the place, the way and the means for storing this data. In this article, I will briefly review the task, as well as investigate and compare solutions for completion of this task.

Context of the task: I work in a team that develops tools for relative database development (SQL Server, MySQL, Oracle). The tool range includes both, standalone tools, and add-ins for MS SSMS.

Task: Restoring documents that were opened at the moment of IDE closing at the next start of IDE. (more…)

Common Examples of SSIS Expressions and Variables

Introduction

This article helps to enrich the functionality of SSIS. It is possible for example to set properties in tasks using expressions and we can assign values and create code to enrich the SSIS functionality.

This article is oriented to SSIS developers who have experience with basic tasks, but are not familiar with variables, scripts to enrich the functionality of the SSIS and to provide dynamic functionality.

This time we will talk about SSIS expressions, variables, and loops in SSIS. We will use expressions to set values in tasks using variables. We will also learn to create Script code in C#.

We will first talk about SSIS expressions and then we will use SSIS with the script task. Finally, we will work with Loop containers and the script task.

(more…)

Everything you should know about SQL Server JOINS

The focus of this article is going to be on utilizing JOINs. We will start off by talking a bit about how JOINs are going to happen and why you need to JOIN data. Then we will take a look at the JOIN types that we have available to us and how to use them.

JOIN BASICS

JOINs in TSQL are typically going to be done on the FROM line.

JOIN-Starts-Here

Before we are getting to anything else, the real big question becomes — “Why do we have to do JOINs, and how are we actually going to perform our JOINs?” (more…)

Extended Events for SSAS

Cubes require frequent monitoring since their productivity decreases quite often (slowdowns during query building, processing time increment). To find out the reason of decrease, we need to monitor our system. For this, we use SQL Server Profiler. However, Microsoft is planning to exclude this SQL tracing tool in subsequent versions. The main disadvantage of the tool is resource intensity, and it should be run on a production server carefully, since it may cause a critical system productivity loss.

Thus, Extended Events is a general event-handling system for server systems. This system supports the correlation of data from SQL Server which allows getting SQL Server state events. (more…)

SQL Server Security Ponderings – Part 1 | DMI – Bobby Returns

Nowadays security and data privacy are in special focus. When I deliver a training, I always refer to a DBA as the “guardian of the data.” There are two aspects of being a guardian.

The first one is integrity. It includes tasks like checking database consistency, creating backups and in case of problems being prepared to fix the database by having well designed, comprehensive DR plan.

(more…)

Deploying Database from Source Control

These days, development teams start new projects from choosing a version control system. There are many benefits of source control systems in coordinating the efforts of the development team. It ensures a complete audit trail of all changes to the code and allows the team to reproduce any specific revision or build. Along with the programming code, databases can and should be placed under source control. In this article, we will have a look at how to deploy a database that is in the remote repository. (more…)