Monitoring Memory Clerk and Buffer Pool Allocations in SQL Server

February 20, 2017 by David Alcock

The following article applies to SQL Server versions 2008 +

Adequate memory is one of the most important factors for a well-functioning instance of SQL Server. By design SQL Server manages its own memory allocations via the SQLOS rather than having the servers Operating System perform this task.

$C:\Users\Marko\AppData\Local\Microsoft\Windows\INetCacheContent.Word\14.png$

How to query data in a System-Versioned Temporal Tables in SQL Server

February 20, 2017 by Marko Zivkovic

This article will cover the querying of temporal tables in SQL Server by using the FOR SYSTEM_TIME clause and its four sub clauses AS OF, FROM TO, BETWEEN AND, CONTAINED IN. Also, we’ll cover how to clean up the history table to keep it a manageable size.

Functions and stored procedures comparisons in SQL Server

February 20, 2017 by Daniel Calbimonte

Introduction

In a previous article, Functions vs stored procedures in SQL Server, we compared Functions vs stored procedures across various attributes. In this article, we will continue the discussion. We will talk also about Table-valued functions and compare performance with stored procedures with table valued functions and scalar functions.

Top 5 Deprecated Features in Reporting Services 2016

February 17, 2017 by Sifiso Ndlovu

It’s not often that I write negative articles surrounding SQL Server’s latest release but ever since we upgraded one of our BI boxes to run SQL Server Reporting Services 2016 (SSRS 2016), I have picked up on some frustrations from my team when using the upgraded Report Manager portal due to the unavailability of features that used to exist in versions prior to SSRS 2016. I have since realized that in spite of the many exciting features and improvements introduced in SSRS 2016, there is a downside to this latest version of reporting services that is likely to leave many administrators frustrated.

Sanitizing Inputs: Avoiding Security and Usability Disasters

February 17, 2017 by Ed Pollack

Introduction

In any application, we will likely have some need to control input data, either altering, filtering or otherwise changing text to fit our application’s needs.

Top 5 new features in SQL Server for developers

February 15, 2017 by Mustafa EL-Masry

Introduction:

Microsoft SQL Server 2012 introduces many features that help database administrators, database developers, and BI developers.

In this article, I will cover some of the new features for database developers in these main points:

SQL Server performance – measure Disk Response Time

February 14, 2017 by Musab Umair

Introduction

As DBAs, we all get to the point where we are asked to setup a new server for a specific environment. Setting up a new server is not a big thing but giving the answer to the question that “how well it will work” might be tricky.

Functions vs stored procedures in SQL Server

February 14, 2017 by Daniel Calbimonte

Introduction

Usually DBAs prefer stored procedures in SQL instead of functions in SQL Server. Is this a good practice?

SQL Server Database Recovery Process Internals – database STARTUP Command

February 14, 2017 by Prashanth Jayaram

A database recovery process is an essential requirement for database systems, It can be a tedious job and the process of recovery varies on lot of scenarios. The desire to improve recovery results has resulted in various procedures, but understood by few and prone to errors. In this article, I’ll illustrate the impact of stopping the database instance in the middle of a large transaction that was running and discuss several techniques and tools that are available for faster and successful recovery.

SSRS Failed Subscription Alerting

February 10, 2017 by Craig Porteous

Even now, with SQL Server 2016 SP1 released only a few months ago, it baffles me that there is still no built-in functionality to alert admins or users of failed subscriptions in Reporting Services. We still rely on scripts like the one I’m about to describe or report recipients contacting administrators/helpdesks when their report emails don’t arrive or fail to appear in file shares. This is something that people have had to work around for years. There is some documentation that Microsoft provides to help you get started with monitoring subscriptions from the log files with direction on using PowerShell but it is not by any means a complete solution. (Monitor Reporting Services Subscriptions)

How to Import / Export CSV Files with R in SQL Server 2016

February 9, 2017 by Jeffrey Yao

Introduction

Importing and exporting CSV files is a common task to DBAs from time to time.

Introducing SQL Server Incremental Statistics for Partitioned Tables

February 3, 2017 by Simon Liew

If you are maintaining a very large database, you might be well aware of the pain to perform update statistics on a very large table.

This article introduces incremental statistics which is available from SQL Server 2014 highly simplifies statistics management on very large partitioned tables.

How to install, configure and use SQL Server Distributed Replay

February 1, 2017 by Vitor Montalvão

Introduction

The Microsoft SQL Server Distributed Replay feature has been provided by Microsoft since SQL Server 2012 and it is used to replay captured traces against an upgraded test environment.

SQL Server database continuous integration (CI) Best practices and how to implement them – Testing, processing and automation

January 31, 2017 by Nemanja Popovic

Testing

Test databases should be processed with unit tests

In many shops code is unit tested at the point of commit. For databases, I prefer running all unit tests at once and in sequence against a QA database, vs development, as part of a Test step, in my continuous integration workflow pipeline. Yes, issues would be caught later than at check-in, but continuous integration largely solves this with frequent iterations, including at a commit itself. So the difference between on-check in, unit testing and unit testing a build created on-commit, is simply that the unit tests will be run against a fully re-constituted QA database, vs Development. The previous article in this series is SQL Server database continuous integration (CI) Best practices and how to implement them – Source control. Read more »

SQL Server database continuous integration (CI) Best practices and how to implement them – Source control

January 31, 2017 by Nemanja Popovic

This article provides for a roadmap to continuous integration and delivery best practices, and along the way demonstrates how to apply these with ApexSQL tools and technologies. In some sections this article is aspirational, as no solution yet exists, but demonstrates our plan, direction and roadmap. As the tools that apply these best practices are released this article will be updated accordingly.

How to resolve Excel Timeline Filter Errors caused by SQL Server OLAP Data Sources

January 31, 2017 by Sifiso Ndlovu

Over the course of my career, I have developed and deployed to production several business intelligence solutions. These solutions have been consumed using numerous data visualisation tools. One of those data visualisation tools, is Microsoft Excel – which remains a popular data visualisations tool for many of my end users. Like many software products, newer version releases of Excel usually introduce new features. One such feature is the Timeline filter control which was first introduced in Microsoft Office 2013. The Timeline filter control enables end users to visually filter Excel PivotTables using fields defined as dates. The Timeline filter has several benefits compared to the traditional Excel dropdown filter. Figure 1 illustrates one of those benefits whereby the Timeline filter allows end-users to easily apply a filter (i.e. using a scroll bar) again a PivotTable. It also has useful annotations that describes what you have filtered on – in this case, we have chosen sales period between February and March 2011. Read more »

Indexing SQL Server temp tables

January 31, 2017 by Ahmad Yaseen

SQL Server temp tables are a special type of tables that are written to the TempDB database and act like regular tables, providing a suitable workplace for intermediate data processing before saving the result to a regular table, as it can live only for the age of the database connection.

Options for Partitioned Tables and Indexes in SQL Server

January 31, 2017 by Gerald Britton

Introduction

I work for a large, multinational financial institution. Like most companies in this field, ours is conservative and subject to much regulatory oversight. The first has meant that we’re slow to adopt new technologies. We need to be really, really, really sure things won’t break, customer accounts won’t vanish or lose their balances and that the regulators will not raise any red flags. Everything we do is subject to the scrutiny of our internal auditors, even something that seems simple, like rerunning a production job that failed because the host compute was down. I really mean everything!

How to create a simple SQL Server database login dialog using PowerShell

January 30, 2017 by Nemanja Popovic

In this article, we’ll be creating a SQL Server login form, as shown in the example below, using PowerShell to accept user input. We’ll show the code behind the form, how to capture the user input and utilize it:

Integration Services Performance Best Practices – Writing to the Destination

January 26, 2017 by Koen Verbeeck

With this article, we continue part 1 of common best practices to optimize the performance of Integration Services packages. As mentioned in the previous article “Integration Services (SSIS) Performance Best Practices – Data Flow Optimization“, it’s not an exhaustive list of all possible performance improvements for SSIS packages. It merely represents a set of best practices that will guide you through the most common development patterns.

Using Variables in Dynamic SQL

January 26, 2017 by Aamir Syed

Before we delve into these SQL concepts, note that I like to do all my development in SQL Management Studio. So if you want to follow along go ahead and open that up.

How to generate random SQL Server test data using T-SQL

January 26, 2017 by Daniel Calbimonte

Introduction

In this article, we will talk about generating random values for testing purposes.

I once had a customer with software that worked fine in the demo with 30 rows, but after some months, the software had more than a million rows and it became very slow. The problem was not SQL Server, the problem was the application, which was not designed for tables with millions of rows. The customer sued to the software provider and lawyers were needed to create a resolution. If the provider had tested the software with millions of rows, this problem would have never happened.

Integration Services Performance Best Practices – Data Flow Optimization

January 26, 2017 by Koen Verbeeck

In this article, we’ll present a couple of common best practices regarding the performance of Integration Services (SSIS) packages. These are general guidelines for package design and development which guarantee a good performance in most use cases. However, there are always exceptional cases and outliers. The mantra of “measure twice, cut once” also applies here. Thoroughly test any changes to your packages to conclude that a change made a positive effect. This means not only running the in the designer, but also on the server. Also, this article is not an exhaustive list of all possible performance improvements for SSIS packages. It merely represents a set of best practices that will guide you through the most common development patterns.

Are SQL Server database triggers evil?

January 25, 2017 by Daniel Calbimonte

Introduction

There is a lot of talk about how bad triggers are, how you should never use them, etc. I wanted to spend some time reviewing fact vs fiction and do an objective analysis of SQL Server database triggers (both DDL and DML), warts and all. We will review alternatives and compare them with triggers to determine advantages vs disadvantages of each approach.

Using Memory-Optimized Tables to Replace SQL Temp Tables and Table Variables

January 24, 2017 by Ahmad Yaseen

TempDB usage can be considered as a performance bottleneck for workloads that use SQL temp tables and table variables intensively resulted in heavy IO usage. A valuable alternatives for the SQL temp table and table variable are SCHEMA_ONLY Memory-Optimized tables and the Memory-optimized Table Variable, where the data will be completely stored in the memory without the need to touch the TempDB database, providing the best data access performance. SCHEMA_ONLY Memory-Optimized table and the Memory-optimized Table Variable are stored only in the memory with no related component in the disk. It involves no IO activity or TempDB utilization. It can also participate in the transactions without the need to log the transactions.