Subscribe: - The SQL Server blog spot on the web
Added By: Feedage Forager Feedage Grade A rated
Language: English
athena  aws  azure data  azure  blog  data lake  data  microsoft  new  query  read  server  services  sql server  sql 
Rate this Feed
Rate this feedRate this feedRate this feedRate this feedRate this feed
Rate this feed 1 starRate this feed 2 starRate this feed 3 starRate this feed 4 starRate this feed 5 star

Comments (0)

Feed Details and Statistics Feed Statistics
Preview: - The SQL Server blog spot on the web - The SQL Server blog spot on the web

THE SQL Server Blog Spot on the Web


PASS Marathon GDPR Edition: Recordings now available!

Sat, 17 Mar 2018 18:09:00 GMT

You have certainly already heard about the new European Regulation 679/2016, better known as GDPR, on the protection of personal data.
GDPR is the acronym of General Data Protection Regulation, companies and professionals are required, by May 25 (2018) to comply with the new rules on personal data protection.

"The aim of the GDPR is to protect all EU citizens from privacy and data breaches in an increasingly data-driven world that is vastly different from the time in which the 1995 directive was established. Although the key principles of data privacy still hold true to the previous directive, many changes have been proposed to the regulatory policies; the key points of the GDPR as well as information on the impacts it will have on business can be found below" - 

Were you unable to attend the PASS Marathon in partnership with Redgate to understand what the GDPR entails? Don't worry, the sessions of the event PASS Marathon GDPR Edition (held the last March 2018 on 13th) were recorded and now they are available for online streaming!

If you have missed one session or the entire event, you can view or review your preferred talk.
Each video is available on the schedule page of the event.
Enjoy the videos! 

My #AWSsummit keynote liveblog

Wed, 14 Mar 2018 20:49:00 GMT

The company I work for, Perion, chose Amazon Web Services as the main cloud provider for managing and operating our applications. These days I am learning how to manage databases and data related flows in the AWS cloud. Which is the reason I have attended the AWS summit in Tel Aviv today. Today’s AWS summit was the first from the series of AWS summits held in major cities around the world. There were ~ 5000 attendees today, 25 technical sessions and it was sponsored by 33 AWS partners. Most sessions which I have attended today had a lot of sponsor content mixed with technical not-too-deep dive into various AWS services.  Sessions were divided into 5 tracks, led by AWS Solution Architects and Sponsors representatives: Big data & Analytics sessions on data ingestion, data transformation and data analysis using AWS services AI and Deep Learning – sessions on AWS offerings for data enrichment and learning models Innovate – Rapid Application Development services Cloud operations – sessions on DevOps methodologies and tools Let’s start - for those who starts their journey into the AWS cloud I really enjoyed the keynote by Dr. Werner Vogels, Amazon CTO. The keynote hall was already fully packed when I arrived, and I had to watch the keynote video stream from the second hall. The sound was awesome but the person who was operating the camera wasn’t well at his job. Half of the slides could not be seen; the focus was all the time on the speaker. Dr. Werner have talked about Amazon revenue run rate, which is near $20 billion dollars  ( this article says $16 billion, Microsoft has $16.7 billion, IBM $15.8 billion). AWS have millions of active customers and their pace of innovations (amount of the unique services per year) grows at very high pace: 24 during 2008 48 during 2009 61 during 2010 82 during 2011 159 during 2012 280 during 2013 516 during 2014 722 during 2015 1017 during 2016 1403 during 2017 Amazon for 7 consecutive years named the Leader in Gartner’s Infrastructure as a Service (IaaS) Magic Quadrant. Dr. Werner have talked about how infrastructure and software vendors used to tell us how to develop software. These days, in the cloud, we have as many tools as we need to make our development tailored for our needs. Modern applications are continuously evolving. They have more than web content, they are using micro services architecture and are api-based. Everything is programmable, many of them have a smart connectivity, push notifications and responsive design. We used to have 1 deployment a week, now we have 50 deployments a day. There were many awesome examples of the AWS customers during the keynote. One of them was the cloud infrastructure behind the iRobot:   (Image source : Dr. Werner mentioned how Netflix are making use of Machine Learning to build smarter applications. 75% of the Netflix videos were viewed after the recommendation made by the recommendation engine. He has also mentioned the Pinterest, one of the world’s largest visual bookmarking tools, where all images go through Machine Learning process to get a better grip on visual search. Another cool examples were the boxing gloves that check the punch speed and the punch intensity, the helmet that analyses the pilot’s emotions and soccer and baseball video ingestion that uses Machine Learning to help coaches to understand how the team can do better at the next game. Machine Learning can be used in many more use-cases: Shopping advises Customer problem detection Product modeling Production substitutes Customer adoption models Sales lead ranking Dr. Werner have talked about the modern data architectures and their 10 main characteristics: reliable data ingesti[...]

Low Latency Memory

Thu, 08 Mar 2018 18:48:00 GMT

For the last twenty years, the standard practice has been to scale server performance with multi-processor (MP) systems. What is not widely understood is that after processors integrated the memory controller, the initial step from single processor to 2-way has only moderate scaling due to the introduction of non-uniform memory access (NUMA) in the MP system architecture. Scaling versus cores within a single processor however, can be excellent. The new practice should now be to employ single processor...(read more)(image)


Fri, 23 Feb 2018 23:20:00 GMT

this is my extended version of sp_helpindex sp_helpindex2 lets face it, sp_helpindex is old and has not been updated for all the new features of SQL Server introduced since the beginning. sp_helpindex provides a list of indexes, information on: the type (clustered, unique, primary key, statistics no recompute), and file group, and the key columns What additional information would we like? the included column list is one, and also if it is a filtered index, and the filter definition. Size information...(read more)(image)


Fri, 23 Feb 2018 23:08:00 GMT

this is my version of sp_spaceused. Instead of just overall spaceusage, all tables are shown with extended information. -- sp_spaceused2 USE master -- skip this for Azure GO IF EXISTS ( SELECT * FROM sys . procedures WHERE object_id = OBJECT_ID ( 'sp_spaceused2' ) ) DROP procedure [dbo] . sp_spaceused2 GO CREATE PROCEDURE [dbo] . [sp_spaceused2] @objname nvarchar ( 776 ) = NULL, @minrow int = 0 AS DECLARE @objid int , @dbname sysname -- Check to see that the object names are local to the current...(read more)(image)

Asymmetric Processor Cores

Tue, 20 Feb 2018 18:50:00 GMT

Most Intel processors use one of two cores, the main-line core and the Atom core. Desktop processors might use the newer Kaby Lake or Coffee Lake micro-architecture cores, while the Xeon SP lines uses the previous generation Skylake micro-architecture. All of these are part of the main core line. The Atom core is used in Xeon Phi, Atom and some Pentium or Celeron processors. In simple terms, the main-line is a big core and the Atom is a small core. The obvious question is which is better: very many...(read more)(image)

Developing product recommendations on WideWorldImporters using SQL Graph

Fri, 09 Feb 2018 22:18:00 GMT

SQL Server has always provided tools to manage hierarchies and relationships, facilitating query execution on hierarchical data, but sometimes relationships can become complex. Think about many-to-many relationships, relational databases don't have a native solution for many-to-many associations. A common approach to realize many-to-many associations is to introduce a table that holds such relationships.

SQL Server 2017, thanks to the Graph Database, can express certain kinds of queries more easily than a relational database by transforming complex relationships into graphs.

The graphs theory is not new in the computer world, the first commercial proposals are dating in the year of 2000 by Neo4J, OrientDB, Oracle Spatial, Graph, etc. Recently, thanks to some of the world's largest internet companies and to the new data processing and analysis requirements, this new class of Object-Oriented Database Management Systems (ODBMS) is receiving a strong impulse.

Today we need to navigate through segments of data quickly and describe how each piece of data is connected to each other by an interconnected logic. A Graph Database is a collection of Nodes and Edges where each node represents an entity and each edge represents a relationship between two nodes with a direction (for example from Node A to Node B).

A few days ago, Microsoft has merged the pull request I have done on the repository Microsoft/sql-server-samples on GitHub. "This repository contains code samples that demonstrate how to use Microsoft's SQL products including SQL Server, Azure SQL Database, and Azure SQL Data Warehouse. Each sample includes a README file that explains how to run and use the sample".

My sample explains what Graph Database is and which scenarios it solves easily, they are available here.


As you can see, the publishment consists of three demos. In the first one, I shown how to create graph objects such as Nodes and Edges. The second demo allows you to do a first look to the T-SQL MATCH clause used to perform some query on Nodes and Edges created in the first demo. In the last demo I shown how to build a recommendation system for sales offers that is able to determine the recommended products based on the product a customer is purchasing.

Have you heard about the Graph Database and want to know more? These examples are a good place to start!

Enjoy the SQL Graph! 


Rethink Server Sizing II

Thu, 08 Feb 2018 16:15:00 GMT

It is almost universally accepted without question that the server is a multi-processor system. Long ago, there were valid reasons for this. Over time, the reasons changed but were still valid. When the memory controller became integrated into the processor, the very low memory latency of a single processor system was an interesting but non-actionable tidbit. The greater compute and memory of the multi-processor was then more important. In recent years, the high core count and memory capacity of...(read more)(image)

The new Data Modeling for Power BI course

Wed, 07 Feb 2018 14:06:00 GMT

I recently published a new blog post on SQLBI.

You can read it at this link:


T-SQL Tuesday #99 : Dealer's Choice

Tue, 06 Feb 2018 14:38:00 GMT

T-SQL Tuesday #99 invitation......(read more)(image)

Power Pivot Utilities 1.09

Thu, 01 Feb 2018 17:05:00 GMT

I recently published a new blog post on SQLBI.

You can read it at this link:


Why I attend conferences and why I speak at conferences

Fri, 26 Jan 2018 17:08:00 GMT

I recently published a new blog post on SQLBI.

You can read it at this link:


SQL Server Cumulative Updates (January 2018)

Fri, 19 Jan 2018 21:01:00 GMT

Microsoft has released the following Cumulative Updates for SQL Server:

Cumulative Update 3 for SQL Server 2017 RTM

Cumulative Update 7 for SQL Server 2016 SP1

Cumulative Update 10 for SQL Server 2014 SP2



A new home for my SQLBlog content...

Fri, 19 Jan 2018 12:50:00 GMT

I've moved my content over to . Read more details......(read more)(image)

SQL Saturday Pordenone is back! Discover the 2018 edition!

Fri, 12 Jan 2018 08:45:00 GMT

Another SQL Saturday has been scheduled in Pordenone (Italy), it will be the SQL Saturday #707, as last year it will be an international event with international speakers and several sessions in English language!
SQL Saturday Pordenone 2018 will be on February, Saturday 17 at Consorzio Universitario di Pordenone, Via Prasecco 3/a, Pordenone (Italy).
The agenda of the day is divided in four track that will deliver a total of 24 hours of free training on SQL Server, Business Intelligence, Microsoft Azure and Data Science! You will learn more about topics that you use every day rather than learn something about technologies that you don't use yet.
Thanks to our Sponsors the event will be free of charge for you, but the registration is mandatory.
If you are around Pordenone at the end of February, or if you want to come in Italy for a weekend of training on Microsoft Data Platform with friends, #sqlfamily and good food :) you are welcome!
Registration is available here and the official Twitter hashtag is #sqlsat707.
See you in Pordenone!

Quick performance check with latest patches

Thu, 11 Jan 2018 17:07:00 GMT

there is some degree of press hysteria, lawsuits etc. concerning the kernel vulnerabilities of Intel processors with out-of-order execution (which originates to Pentium Pro), so I did a quick check. Earlier, I posted on testing with the TPC-H database and queries at scale factor 100 (in conditions that do not meet official TPC-H standards). The test system is a single socket Xeon E5 2630 v4, 10 core, 64GB memory, SQL Server limited to 32GB, 4 Intel P750 NVMe SSDs for storage. The baseline test was...(read more)(image)

This is my most difficult SQLBlog post ever…and almost certainly, my last

Mon, 08 Jan 2018 23:24:00 GMT

The pain of parting is nothing to the joy of meeting again - Charles Dickens It amazes me, but I have blogged here for over 11 years. My first post was about some new functionality in SQL Server 2005, Service pack 2. It was cross posted to, where my original blog was hosted. No introduction post, just right into things. Back then things were still new, and I was just getting started. I have hundreds of blogs hosted here on SQLBlog, and I will miss being a part of this community...(read more)(image)

Blog moved

Fri, 05 Jan 2018 14:55:31 GMT

Blog has been moved to:

Follow me on twitter:


SQL Saturday Nashville: I'm Speaking

Thu, 04 Jan 2018 05:03:00 GMT

Going home y'all. On January 13th, I will be speaking at the Nashville SQL Saturday. I moved from Nashville less than 1 year ago, and I always look forward to making a trip up (and not just for hot chicken at Big Shakes , though it does figure into it.) Tamera Clark and team always do a great job, and the list of speakers that I get to hear and hang out with is pretty awesome. My topic and abstract are: How to Implement a Hierarchy in SQL Server One of the most common structures you will come across...(read more)(image)

Azure Data Lake Store library for Delphi

Wed, 03 Jan 2018 23:06:00 GMT

From Microsoft-Docs on GitHub: Azure Data Lake Store is an enterprise-wide hyper-scale repository for big data analytic workloads. Azure Data Lake enables you to capture data of any size, type, and ingestion speed in one single place for operational and exploratory analytics.

Because I'm a Delphi developer, Microsoft Data Platform MVP since 2010 and because (in my opinion) there are poor documentation about the integration between the Delphi programming language and Microsoft Azure services, I decided to do something for help Delphi developers to use Microsoft Services and Products. I developed a REST sample library to connect and manage files into an instance of Azure Data Lake Store.

The sample library is intended for Delphi developers those want to build applications and services that are able to connect to Azure Data Lake Store. At the moment the libraty shows how to:

  • Retrieve an access token using REST Client components
  • Retrieve folders list
  • Upload files to a specific folder

The first version of my ADLSLibrary4Delphi, with source code, is available on my GitHub repository named azure-data-lake-store-for-delphi.

If you want to contribute in the project, you are welcome!



Simulating Bad Networks to Test SQL Server Replication

Tue, 02 Jan 2018 01:27:00 GMT

SQL Server Replication usually works just fine when testing in a development environment where there’s low latency and high bandwidth. Real world conditions aren’t always like that. I’ve published instructions on how to use the free WANem network emulator to simulate poor network conditions such as when SQL Server has to replicate over a satellite link. See my post at ....(read more)(image)

What has happened in the DAX world in 2017

Wed, 27 Dec 2017 17:09:00 GMT

I recently published a new blog post on SQLBI.

You can read it at this link:


Rethink Server Sizing

Tue, 26 Dec 2017 19:00:00 GMT

Standardizing on 2 and 4 sockets systems for servers has been an established practice going back to 1996. There were very good reasons for this, but it was so long ago that they have been forgotten. Yet the practice continues unquestioned, almost as a reflex action ingrained in our subconscious. Back then, 2-way was the baseline, and a 4-way was for databases. Now, the large majority of standard systems are 2-way (2S) with 4S for exceptional requirements. It might seem that the 2-socket system continues...(read more)(image)

Query big data, stored in a csv or parquet files, from any stored procedure

Sun, 17 Dec 2017 13:28:00 GMT

Every cloud provider these days have a serverless interactive query service that uses standard SQL for data analysis. As for the biggest cloud providers, we have Azure Data Lake analytics, Google BigQuery and Amazon Athena. Due to the fact that my company is using only Amazon cloud, I have some experience with Athena service and I must say that it is awesome. I will show you today how you can use Management Studio or any stored procedure to query the data, stored in a csv file, located on S3 storage. I am using CSV file format as an example here, columnar PARQUET gives much better performance.I am going to: 1. Put a simple CSV file on S3 storage 2. Create External table in Athena service over the data file bucket        3. Create linked server to Athena inside SQL Server          4. Use OPENQUERY to query the data.   Athena service is built on the top of Presto, distributed SQL engine and also uses Apache Hive to create, alter and drop tables. You can run ANSI SQL statements in the Athena query editor, launching it from the AWS web services UI. You can use complex joins, window functions and many other great SQL language features. Using Athena eliminates need for ETL because it projects your schema on the data files at the time of the query. Let’s create database in Athena query editor. As a next step I will put this csv file on S3. By the way, Athena supports JSON format, tsv, csv, PARQUET and AVRO formats. Uploading the below file to S3 bucket (don’t put columns header in file): Going back to Athena to create external table over the S3 folder.  You can later add more files to the same folder and your queries will return the new data immidiately. Now I can query the data: As a next step I will set up a linked server from my SQL Server instance because I would like to offload the big data querying to Athena. Of course, I am using in this example tiny data file but in real life we are querying sometimes 300GB of data files in the single query and it takes a few seconds. Athena has ODBC driver, I will install it on SQL Server machine ( AWS EC2 instance for this example). Here is an installation link: Setting up the ODBC connection. Important, click on the Authentication Option and fill in AccessKey and SecretKey that have permissions to access S3 bucket. S3 output location below will hold csv files with results from your queries. Remember to clean the output files from time to time. What is left is to set up Linked Server inside Management Studio using OLEDB provider for ODBC. EXEC master.dbo.sp_addlinkedserver @server = N'DWH_ATHENA', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'DWH_ATHENA' GO EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DWH_ATHENA',@useself=N'False',@locallogin=NULL,@rmtuser=N'*******',@rmtpassword='*********' GO Replace @rmtuser and @rmtpassword with AWS access key and secret key and now we can query the data files from any script or stored procedure. There is one very important thing that you need to know. Regular SQL Server ODBC destinations querying behavior of is to send “select *” to linked server and do filtering inside SQL Server. This is very bad for us since we wanted to offload all work to Athena and we do not want to receive all data.  The way to overcome this is [...]

So long, farewell, auf Wiedersehen, adieu

Thu, 14 Dec 2017 23:07:00 GMT

On an otherwise unremarkable day, almost twelve years ago (early 2006 to be exact), I got an email from Adam Machanic . I don’t have it anymore, but I do remember the management summary of the contents: “Hey Hugo, you should start blogging. Peter DeBetta and I have a site where we can host you”. Until then, I had never even considered blogging. Adam’s email got me thinking. The rest, as they say, is history. My first post here was made in July 2006. And now, December 2017, it is time for my last...(read more)(image)