How to analyze query performance characteristics in SQL Server

Introduction

There is a maxim that comes from the world of Python programming: “There should be one – and preferably only one – obvious way to do it.” (See The Zen of Python) in the references. While that is a good goal for any language, it is a difficult goal to achieve. T-SQL is no exception here!

Consider a simple problem: You have a customer transaction table with dated rows. You are asked to produce a list of the top transactions per day. “Top” could be defined as the one with the most items, the highest value, the most important customer or a variety of other criteria. However, you define it, at some point, you will want the maximum (or minimum) value of one of the columns. Then you will want to output the entire row (or rows, in the case of a tie) matching that value. Sounds simple, right? Well, it actually is simple, but the number of ways to do it may surprise you!

November 22, 2017

How to execute an unattended best practices review against a database

One of the many tasks of the DBA in charge on a development project is to make sure the team stays consistent in their code. Routine checks for SQL best practices such as naming conventions, right data types usage, problematic things like cursors, potentially fatal things like Deletes without Where clauses can cause a lot of headaches.

November 10, 2017

How to Detect SQL Server Performance Issues Caused by Incorrect Clustered Indexes

Description

A clustered index is critical to performance on any table. Not only does it dictate the logical storage of data for the entire table, but it also is the index that all others will reference when additional data is required. A poorly chosen clustered index can greatly hamper performance, causing latency, contention, and excessive IO. In this article, we will explore clustered indexes, and use ApexSQL Tools to assist in identifying those that could be improved upon.

November 9, 2017

How to create Copy-Only backups in SQL Server

Creating a foolproof disaster recovery solution is imperative for every business. After the Recovery Point Objective (RPO) and Recovery Time Objective (RTO) are defined, a database backup plan should be created to match these objectives. Most DBAs tend to automate the majority of tasks related to database backup plans. Regular database backup schedules are set in order to create continuous backup chains, that can later be used to recover a database in the case of a disastrous event. By setting the backup schedule, the continuity of the backup process is ensured, and most of the job is performed automatically on a regular basis.

November 7, 2017

How to version control SQL scripts alongside SQL database objects

This article explains how SQL database source control can help in version control SQL scripts alongside SQL database objects. Developing a software sometimes requires versioning not only database objects, but custom SQL scripts for migration, configuration, automation or other purposes

November 3, 2017

“What broke my database?” Using SQL database source control for forensic auditing and database troubleshooting

Besides the auditing trail of all the changes made against a database and having the information of who committed what and when, the main benefit of having a database under version control is the fact that the history of all the previously committed versions of any object (or a group of objects) can be used to find the exact version that broke the database at any point. Assuming that a SQL database under source control is being deployed on a daily basis or even more frequently, it is essential to ensure that only tested changes are committed so that a SQL database can be deployed in a working state, without any issues.

November 1, 2017

Using SQL source control to track database changes

The goal of this article is to explain how SQL database source control can help in auditing database changes. Tracking a change by itself is not the only task required for successful implementation of SQL source control, as we also want to know who made the change, when and why. After all of these inputs are known, any problems and be diagnosed and be fixed efficiently. We’ll see how having a SQL database under version control can help in keeping the auditing trail with a possibility to revert back to any of the previous version of an object.

October 31, 2017

Fatal action guard: guarding against inadvertent execution of code that may damage or destroy data in SQL Server

There are many opportunities, before, during and even after to guard against potentially fatal actions that may damage or destroy data, like executing an Update or Delete statement without a Where clause. Such a fatal action guard can be implemented in different ways and at different times, relative to the incident, for example, before it to prevent it from happening in the first place, or after, to reverse an action that has already been executed.

October 24, 2017

How to make SQL database version control safer with branches

The goal of this article is to explain how to use branches in order to diverge from the main development line aka master branch and leave it unaffected by all the committed changesets that are not yet tested and confirmed as good ones. As long as a separate branch is used to commit changes, a master branch can be used to deploy a working copy of a database from source control for any purposes.

October 23, 2017

How to set different connection colors in SSMS

Microsoft introduced a neat feature in SQL Server Management Studio (SSMS) in SQL Server 2008 that helps users quickly determine which connection a tab is currently using by setting custom connection colors in SSMS for different SQL Server nodes.

For database administrators who work on a multiple SQL Servers simultaneously, this feature is a time-saver that allows users to quickly determine which SQL Server are currently used by looking at a color defined for the associated server.

October 18, 2017

General Data Protection Regulation (GDPR) compliance for SQL Server

On April 27, 2016, the European Parliament and the council of the European Union adopted a new standard, the General Data Protection Regulation on the protection of natural persons with regard to the processing of personal data and on the free movement of such data. The GDPR takes effect on May 25, 2018 and introduces numerous security and compliance regulations and obligations to all organizations worldwide, that handle, process, collect or store personal information of EU citizens. This means that organizations, both the data processors and data controllers, will have to elevate security measures and auditing mechanisms when handling personal identifiable information (PII) of EU citizens and be able to demonstrate compliance with GDPR standard at all time, not only on regular basis (monthly, yearly), but also on demand.

October 13, 2017

SQL Server performance monitoring – identifying problems using wait statistics and query wait analysis

In aggregate, SQL Server Wait statistics are complex, comprising hundreds of wait types registered with every execution of the query. So, to better understand how and why the wait statistic, in general, is the most important for detecting and troubleshooting SQL Server performance issues, it is important to understand the mechanism of how a query executes on SQL Server, since SQL Server performance problems are the ones that most often affect end users.

October 6, 2017

How to export SQL Server data to a CSV file

This article will explain different ways of exporting data from SQL Server to the CSV file. This article will cover the following methods:

  1. Export SQL Server data to CSV by using the SQL Server export wizard
  2. Export SQL Server data to CSV by using the bcp Utility
  3. Export SQL Server data to CSV by using SQL Server Reporting Services (SSRS) in SQL Server Data Tools (SSDT) within Visual Studio
  4. Export SQL Server data to CSV by using the ApexSQL Complete Copy results as CSV option
October 4, 2017

Multi server PowerShell script to backup SQL Server databases automatically

Eighty-two percent of the enterprises expect the number of databases to increase over the next twelve months. An increase in data volumes can have negative effects on the performance of databases. Think about the storage requirement and backup strategy to meet the Recovery Time Objective and the Recovery Point Objective. RPO and RTO are two of the most important parameters of a disaster recovery or data protection plan.

September 27, 2017

How to use source control with SQL Server Management Studio (SSMS)

Introduction

The growing adoption of Continuous Integration development practice implies that developers have to work in a collaborative manner by storing and sharing their source code into a central repository. For SQL Server Business Intelligence (SQL Server BI) developers, the collaboration is usually made possible by the Team Explorer plug-in for Visual Studio which provides an interface for integrating SSRS, SSIS and SSAS solutions into source control environments such as Team Foundation Version Control (TFVC) or Git. However, several aspects of SQL Server BI solutions involve T-SQL development (i.e. defining of SSRS datasets, SSAS data source views, as well as SSIS source data flow tasks).

September 25, 2017

Creating and automating Tableau Site documentation

One of many Tableau administrator responsibilities is to be familiar with the server configuration, managing users, automated server job process management, knowing Tableau metadata and being familiar with migration and scheduling. To stay updated and keep everything under control, good Tableau documentation can come in handy. Such documentation can be created using ApexSQL Doc, a tool for creating SQL Server, BI, SharePoint as well as Tableau site documentation, which will be covered in this article.

September 22, 2017

How to create and use DML triggers to audit data changes

A DML trigger is a special kind of procedural code programmed to fire when a data manipulation language (DML) event occurs in the database server. DML events include UPDATE, INSERT, or DELETE statements issued against a table or view. SQL Server has three types of triggers: DML triggers, DDL triggers, and logon triggers. For the purpose of this article, only DML triggers will be used to demonstrate and achieve a specific goal.

September 21, 2017

How to customize policies for automatically defragmenting SQL Server indexes

This article explains how to create customized policies for index defragmentation jobs for SQL Server

Introduction

When creating indexes, database administrators should look for the best settings to ensure minimal performance impact and degradation. However, over time, indexes will get fragmented, which can severely impact server performance.

September 18, 2017

How to Import and Export SQL Server data to an Excel file

There are several ways to Export/Import SQL Server data to an Excel file. In this article, a couple of ways to import and export SQL Server data to an Excel file will be explained:

  • Export SQL Server data to an Excel file using
    • the SQL Server Import and Export Wizard
    • T-SQL code
    • Export to Excel from ApexSQL Complete
  • Import SQL Server data into Excel using the Data Connection Wizard
September 11, 2017

SQL query performance tuning with I/O statistics and execution plans

Introduction

In a busy system, many things can cause a SQL query to run more slowly than expected or desired. Sometimes, getting a handle on the root cause can involve digging into wait statistics, CPU, memory or network pressure, ferreting out problems in the I/O subsystem, looking for issues in a virtual machine configuration and many other things. Often though, the problem comes down to a simple symptom: the query is doing too much I/O.

August 31, 2017