Derik Hammer

Measuring Availability Group synchronization lag

August 9, 2016 by

With all of the high-availability (HA) and disaster recovery (DR) features, the database administrator must understand how much data loss and downtime is possible under the worst case scenarios. Data loss affects your ability to meet recovery point objectives (RPO) and downtime affects your recovery time objectives (RTO). When using Availability Groups (AGs), your RTO and RPO rely upon the replication of transaction log records between at least two replicas to be extremely fast. The worse the performance, the more potential data loss will occur and the longer it can take for a failed over database to come back online.

Read more »
Steve Simon

Reporting in SQL Server – Combine three reports into one using SQL Server Data Tools

August 4, 2016 by

Introduction

In our last “fireside chat” we discussed a few of the challenges that the HR Manager of a major hardware chain was experiencing. Mary Smith, the HR manager has since approached us to modify her existing reports to function more efficiently and effectively by utilizing her existing data, yet reduce the total number of reports.

Read more »
Sifiso Ndlovu

Top 5 T-SQL functions introduced in SQL Server 2016

August 1, 2016 by

One of the exciting things to look out for in a release of SQL Server are the new built-in T-SQL functions that are usually introduced. Some of the reasons for the new functions may be because SQL Server is catching up to other competitors, attempting to stay ahead of the competition or simply responding to SQL Server feature-requests from the Microsoft Connect site. In this article, I take you through my top 5 T-SQL functions released in SQL Server 2016.

Read more »
Ahmad Yaseen

Force query execution plan using SQL Server 2016 Query store

July 29, 2016 by

SQL Server Query Store is a new feature introduced in SQL Server 2016 that is used to automatically and asynchronously capture query execution history, statistics and plans, with minimal impact to overall SQL Server Performance. The Query Store feature makes performance problem troubleshooting simple; you can view the query execution plans changes and compare its performance to decide which execution plan the SQL Server Query Optimizer should use for that query.

Read more »
Jean-Pierre Voogt

Running with running totals in SQL Server

July 29, 2016 by

Background

Running totals have long been the core of most financial systems, be statements or even balance calculations at a given point in time. Now it’s not the hardest thing to do in SQL Server but it is definitely not the fastest thing in the world either as each record has to be evaluated separately. Prior to SQL Server 2012, you have to manually define the window/subset in which you want to calculate you running total, normally we would define a row number with a window on a specific order or a customer depending on the requirements at hand.

Read more »
Steve Simon

Reporting in SQL Server – Combine T-SQL and DAX queries to produce effective reports

July 27, 2016 by

Introduction

With today’s challenging economic times it has become more and more important to manage and rectify changing sales patterns and trends.

In today’s “get together” we shall be expanding our outlook by creating efficient and effective reports utilizing SQL Server Reporting Service 2016 and T-SQL, together with the DAX code that we created in our last “fire side chat”.

Read more »
Daniel Tikvicki

SSRS ReportServer: Service performance counters guide

July 27, 2016 by

SSRS performance counters

Measurements of the Reporting Services service (SSRS) monitoring cycle show which resources the reporting process consumes, and also, specific sets of counters show the particular type of the reporting process deployment in use, Native and SharePoint mode. The entire reports processing occurs in the Report Server, which is the core element of SSRS architecture, and among all features, collaboration with SharePoint platform is the most crucial, because of advantages of report processing and generating reports for SharePoint components.

Read more »
Jean-Pierre Voogt

The new SQL Server 2016 sample database

July 22, 2016 by

Background

We have all learned to love and hate the trusty Bike shop database. Almost every demo or presentation pertaining to SQL Server we do, we use the AdventureWorks sample database. Almost every code sample on books online references AdventureWorks for illustrations and practical explanations of a feature. When SQL Server 2005 was released Microsoft replaced the old Pubs & Northwind Sample databases with a more complete and more feature rich database called AdventureWorks. Ever since we have been using this new sample database for almost everything we want to test. Microsoft has now given us an even better sample database called WorldWideImporters, which utilises almost every SQL Server feature I can think of from Temporal Tables to In-Memory table.

Read more »
Steve Simon

SQL Server and BI – How to document your Tabular model with Reporting Services 2016

July 20, 2016 by

Introduction

A few weeks back I had been working on an interesting proof of concept for a client within the food/grocery industry. The objectives were to be able to provide the client with information on sales patterns, seasonal trends and location profitability. In our previous “get-together” we discussed how to create a tabular model project and how to create efficient and effective reports utilizing Excel.

Read more »
Ahmad Yaseen

The SQL Server 2014 Resource Governor

July 15, 2016 by

SQL Server Resource Governor was introduced in SQL Server 2008. This feature is used to control the consumption of the available resources, by limiting the amount of the CPU, Memory and IOPS used by the incoming sessions, preventing performance issues that are caused by resources high consumption.

Read more »
Daniel Tikvicki

SSIS Pipeline performance counters guide

July 14, 2016 by

SSIS Pipeline performance counters

SSIS Pipeline performance counters monitor the processes which are related to the execution of packages and the Data flow engine’s the most crucial feature, the (Data) Pipeline. Their measurements reveal in which way the memory resources are acquired during the execution of the SSIS packages, and also, show the amount of memory used during that events. The proficient monitoring of the SSIS Pipeline memory usage can mitigate the potential issues of memory and data leakage, data transformation interruptions and overall, avoid data integration damage.

Read more »
Kaloyan Kosev

How-to: Multiserver administration with master and target SQL Agent jobs

July 13, 2016 by

Have you ever been in a situation where you had to manage hundres of MSSQL Servers? Well, I am right on track here and believe me it does not get easier, even minor tasks take quite the time to do in large enterprise enviornments. One of the solutions I like and I’m using to ease the administration of standard maintenance jobs across my servers was ‘master – target’ jobs. Within the article to follow I will introduce you to the concept, the prerequisites and the limitations they have.

Read more »
Daniel Calbimonte

Using Power BI Desktop to connect to SQL Server and Facebook

July 12, 2016 by

Introduction

Power BI is an extremely popular new Microsoft Tool to create reports. In a previous article, we talked about Power BI Cloud based services. In that article, we connected from a SQL Azure Data Warehouse database to the Power BI Cloud services. In this new article, we will work with the Power BI Desktop Tool from zero. The Power BI Desktop is a reporting software that can be installed in your Windows machine to create reports locally and connect to different data sources like Azure, SharePoint, Active Directory, Exchange, Facebook, Google Analytics and many other data sources. We will create Reports from a SQL Server database and finally we will extract information from Facebook. With Power BI, you can easily access to maps at street level around the world.

Read more »
Sifiso Ndlovu

Convert SQL Server results into JSON

July 12, 2016 by

In my article, Warehousing JSON Formatted Data in SQL Server 2016, we had a look at available T-SQL options for converting JSON data into rows and columns for the purposes of populating a SQL Server based data warehouse. The increased popularity of JSON in modern web applications may create a requirement for data teams to expose some of their data to client applications (i.e. reporting tools, web services etc.) in a JSON format. In this article we take a look at how such a requirement can be implemented by data teams using SQL Server 2016 FOR JSON clause

Read more »
Steve Simon

SQL Server and BI – How to document your Tabular model with Excel

July 12, 2016 by

Introduction

A few weeks back I had been working on an interesting proof of concept for a client within the food / grocery industry. The objectives were to be able to provide the client with information on sales patterns, seasonal trends and location profitability. The client was an accountant and was therefore comfortable utilizing spreadsheets. This said, I felt that this was a super opportunity to build our proof of concept utilizing a SQL Server Tabular Solution and by exploiting the capabilities of Excel and Power Reporting for the front end.

Read more »
Nikola Dimitrijevic

Handling excessive SQL Server PAGEIOLATCH_SH wait types

July 5, 2016 by

One of the most common wait type seen on SQL Server and definitely one that causes a lot of troubles to less experienced database administrators is the PAGEIOLATCH_SH wait type. This is one of those wait types that clearly indicates one thing, but which background and potential causes are much subtler and may lead to erroneous conclusions and worse, incorrect solutions

Read more »