Prashanth Jayaram

SqlPackage.exe – Automate SQL Server Database Restoration using bacpac with PowerShell or Batch techniques

May 7, 2018 by

Data is the key to your organization’s future, but if it’s outdated, irrelevant, or hidden then it’s no good. Maintenance and administration of databases takes a lot of work. As database administrators, we often tend to automate most of these repetitive tasks. A database refresh is one of the most common tasks performed by most of the administrators as part of their daily routine.

Today, database refreshes are quite frequent because of Continuous Integration (CI) and Continuous Deployment (CD). In most of the cases, testing requires a separate but current production dataset to ensure the validity of the desired result.

Read more »
Ahmad Yaseen

Designing effective SQL Server non-clustered indexes

May 7, 2018 by

In the previous articles of this series (see below for the full index of articles), we went through the internal structure of SQL Server tables and indexes, listed a number of guidelines that help in designing a proper index, discussed the operations that can be performed on SQL Server indexes and finally showed how to design and create a SQL Server Clustered index to speed up data retrieval operations. In this article, we will see how to design an effective Non-clustered index that will improve the performance of frequently used queries that are not covered with a Clustered index and, in doing so, enhance the overall system performance.

Read more »
Prashanth Jayaram

Understanding Database snapshots vs Database backups in SQL Server

May 4, 2018 by

In the article, we’ll walk through the concepts to understand database snapshots, and their benefits and limitations. This article will help you decide when to use a database snapshot, and when to use a backup. In some cases, the database relying on database backup and restoration operation is not a viable option. Let’s dig into the concepts of database snapshots and see how it differs from a database backup.

Read more »
Esat Erkec

How to integrate Power BI to the Facebook Graph API

May 4, 2018 by

The challenge

With the popularity of social networking and sharing sites and their widespread availability, internet marketing techniques have also been rapidly influenced. Sharing sites with high visitor traffic offers great potential for advertisers, so much so that it is almost unheard of now that brands or products do not have a profile or group on a social networking site. Markets have the opportunity to communicate directly with consumers by creating profiles or groups in sharing sites. Thanks to the comments and forums on the pages, they are able to follow both positive and negative attitudes of the target groups about themselves and thus have more precise ideas about what to do next.

Read more »
Sifiso Ndlovu

How to use Microsoft Flow to extract SharePoint Online list data

May 3, 2018 by

Data extraction is a pivotal part of any business process particularly when it comes to running reports and facilitating business decision-making. In the article, How to configure OData SSIS Connection for SharePoint Online, I covered data extraction off a SharePoint Online list using SQL Server Integration Services (SSIS). You would have noticed in the aforementioned article that getting SSIS to successfully integrate with SharePoint Online lists can be a laborious exercise, especially if you haven’t installed the correct SharePoint SDK files. Thus, in business environments where business and Power Users have more control of data extraction processes, SSIS could get complicated for an ordinary business user to operate. Therefore, given the nature of our source data and the platform in which it resides, ETL architects and developers alike may need to find alternative ETL tools to SSIS. This brings me to Microsoft Flow which could be one possible alternative to using SSIS for data extraction. Microsoft Flow is part of Office 365 applications and just like SharePoint Online, is a cloud-based application that is freely available, easier to operate and effortlessly integrates with – amongst other applications – SharePoint Online. The aim of this article is to demonstrate the convenience of extracting data from one SharePoint Online list to another using Microsoft Flow.

Read more »
Prashanth Jayaram

Discussing Backup and Restore Automation using SQLCMD and SQL Server agent

May 3, 2018 by

Database administrators are often requested to refresh a database, mostly to create a live copy of the production database to the test or development environment. This is done so that the development or the test environment closely resembles the production environment; this way, we prevent many undesirable issues when running the application in the production environment.

Many developers, at times, are required to write and debug code on the production copy of the database, so it makes more sense to refresh the database on regular basis.

Read more »
Ahmad Yaseen

Designing effective SQL Server clustered indexes

May 3, 2018 by

In the previous articles of this series (see bottom for a full index), we described, in detail, the structure of SQL Server tables and indexes, the basics and guidelines that help us in designing a proper index and finally the list of operations that can be performed on the SQL Server indexes. In this article, we will see how we could design an effective clustered index that the SQL Server Query Optimizer will always take benefits from, in speeding up the data retrieval process, which is the main goal of building an index.

Read more »
Dmitry Piliugin

SQL Server 2017: Adaptive Join Internals

April 30, 2018 by

SQL Server 2017 brings a new query processing methods that are designed to mitigate cardinality estimation errors in query plans and adapt plan execution based on the execution results. This innovation is called Adaptive Query Processing and consist of the three features:

  • Adaptive Memory Grant Feedback;
  • Interleaved Execution;
  • Adaptive Joins.
Read more »
Dmitry Piliugin

SQL Server 2017: How to Get a Parallel Plan

April 28, 2018 by

SQL Server chooses parallel plans based on the costing (there are also some other factors that should be met for the plan that it can go parallel). Sometimes serial plan is slightly cheaper than a parallel, so it is assumed to be faster and picked by the optimizer, however, because the costing model is just a model it is not always true (for a number of reasons, enlisted in Paul’s article below) and parallel plan runs much faster.

Read more »
Dmitry Piliugin

SQL Server 2017: Statistics to Compile a Query Plan

April 28, 2018 by

While preparing the post about Adaptive Joins, I’d like to share a quick post about the hidden gem in SQL Server 2017 CTP 2.0, discovered recently. In this short post, we will look at how you can determine what statistics are used by the optimizer during a plan compilation in SQL Server 2017.

Prior to SQL Server 2017, there were two ways how you could do it, both undocumented and involving undocumented trace flags.

Read more »
SQLShack

List of top SQL Server blogs

April 27, 2018 by
This article is a collection of SQL Server blogs we have curated, ordered by Alexa rankings (at the time of posting). If you notice a blog we missed, please let us know in the comments below. Read more »
Esat Erkec

Integrating Azure SQL database alerts into Slack

April 27, 2018 by

Database administrators have a lot of tasks and responsibilities but perhaps the most challenging task is performance tuning. Because performance tuning issues can cause huge problems in the business area, database administrators have to monitor performance metrics and have to get immediate, if not sooner, notifications when the performance values cross an optimal state. Generally, this notification comes by e-mail.

Read more »
Dmitry Piliugin

SQL Server 2017: Interleaved Execution for mTVF

April 27, 2018 by

In this post, we are going to look at the new feature in SQL Server 2017 – interleaved execution. You need to install SQL Server 2017 CTP 1.3 to try it, if you are ready, let’s start.

Now, when a CTP 2.0 of SQL Server 2017 is out, you don’t need to turn on the undocumented TF described further, and the plans are also different, so the examples from this post use CTP.1.3, probably not actual at the moment (I was asked to hold this post, until the public CTP 2 is out, and interleaved execution is officially announced). However, the post demonstrates Interleaved execution details and might be still interesting.

Read more »
Bojan Petrovic

4K resolution and high DPI: What you need to know about it to have great visuals

April 26, 2018 by

4K resolution, AKA Ultra HD, refers to a horizontal screen display resolution in the order of approximately 3,840 pixels. On two-dimensional display devices such as computer monitors, it represents display resolution of 3840×2160 pixels which is four times as many pixels as regular Full High Definition (1920×1080). What this means for users is the more pixels per inch (PPI) the higher resolution on the monitor. In other words, you can be closer to the monitor and still enjoy a crisp, clear picture.

Read more »
Dmitry Piliugin

SQL Server 2017: Scalar Subquery Simplification

April 26, 2018 by

Nowadays a lot of developers use Object-Relational Mapping (ORM) frameworks. ORM is a programming technique that maps data from an object-oriented to a relational format, i.e. it allows a developer to abstract from a relational database (SQL Server, for example), use object-oriented language (C#, for example) and let an ORM to do all the “talks” to a database engine by generating query texts automatically. ORMs are not perfect, especially if they are used in a wrong way. Sometimes they generate inefficient queries, e.g. a query with redundant expressions. SQL Server has a mechanism to struggle with that inefficiency called a query simplification.

Read more »
Prashanth Jayaram

Backup and Restore (or Recovery) strategies for SQL Server database

April 26, 2018 by

One of the most important roles of a database administrator is to constantly protect the integrity of the databases and maintain the ability to recover quickly in case of a failure. In light of this, it’s critically important to have a backup-and-recovery strategy in place in order to be ready for an emergency.

A key responsibility of a database administrator is to ensure that a database is available whenever it’s needed, and prepare for various scenarios wherein the availability or the performance is impacted. Therefore, if a database, for whatever reason, gets corrupted, gets dropped, gets accidentally deleted, or goes into an unusable state, it is a database administrator’s responsibility to bring the database back up in a working state with little to no loss as per the defined service level agreements or government policies.

Read more »
Dmitry Piliugin

SQL Server 2017: Columnstore in-place updates

April 26, 2018 by

In this post, I continue the exploration of SQL Server 2017 and we will look at the nonclustered columnstore index updates.

Columnstore index has some internal structures to support updates. In 2014 it was a Delta Store – to accept newly inserted rows (when there will be enough rows in delta store, server compresses it and switches to Columnstore row groups) and a Deleted Bitmap to handle deleted rows. In 2016 there are more internal structures, Mapping Index for a clustered Columnstore index to maintain secondary nonclustered indexes and a deleted buffer to speed up deletes from a nonclustered Columnstore index.

Updates were always split into insert + delete. But that is now changed, if a row locates in a delta store, now inplace updates are possible. Another change is that it is now possible to have a per row (narrow) plan instead of per index (wide) plan.

Let’s make some experiments.

Read more »
SQLShack

Top SQL Server Books

April 25, 2018 by
This collection of content will provide an overview of top SQL Server books, with Amazon links for each book. The list is a combination of top Amazon SQL Server books (by the highest number of customer reviews) and the books included on other available ‘Top SQL Server books’ lists (see reference links at the bottom)

If you know of a book that deserves to make this list, please let us know in the comments below.

Read more »
Dmitry Piliugin

SQL Server 2017: Columnstore Indexes and Trivial Plan

April 25, 2018 by

Some time ago, SQL Server 2017 was released and issued as CTP. The most exciting release in that CTP was that SQL Server now supports Linux! This is awesome and I consider it to be great news for many people.

I am personally interested in the new features of query processing, and finally I had some time to install the SQL Server 2017 and dig a little bit into it. Currently, it is CTP 1.2 available, and I will use this version for my experiments.

While exploring new extended events, I’ve found an interesting event compilation_stage_statistics and one of the columns of this event was trivial_plan_scanning_cs_index_discarded with the following description “Number of trivial plans discarded or could have been discarded which scan Columnstore index”. That pushed me to do some investigations of the topic.

Read more »