Tags : #sqlserver

11

Jun

SQL Server Automatic Tuning around the world…
SQL Server Automatic Tuning around the world…

..and in the cloudsWhen I first saw early builds of SQL Server 2017, one of the features that caught my eye immediately was Automatic Tuning with an option called Automatic Plan Correction. SQL Server 2017 was released in October of 2017 right on the heels of one of my favorites releases, SQL Server 2016. In SQL Server 2016, we brought to the product a new feature called Query Store. When Query Store is enabled for a database via ALTER DATABASE, the SQL Server engine will start collecting query performance telemetry in memory and system tables in the database. No longer do you need to poll Dynamic Management Views and store them into your own tables. This performance telemetry is collected by the SQL Server engine itself when queries are compiled and executed.

Like many features we build into SQL Server, you can use T-SQL queries to find out the details of this performance data through a series of catalog views. Query store opens up all types of cool performance insights and we have documented some of the key usage scenarios. One of these scenarios is called a query plan regression (also known as plan choice regressions).

Imagine this scenario. You

04

Jun

Clustered Columnstore Index: Massively Parallel Trickle Insert

https://blogs.msdn.microsoft.com/sql_server_team/clustered-columnstore-index-massively-parallel-trickle-insert/Source: https://blogs.msdn.microsoft.com/sql_server_team/clustered-columnstore-index-massively-parallel-trickle-insert/   A traditional scenario of loading data into CCI is a nightly load from one or more data files containing millions of rows. Recommended technique is to load the data with batchsize >= 102400 as explained https://blogs.msdn.microsoft.com/sqlserverstorageengine/2014/07/27/clustered-column-store-index-bulk-loading-the-data/. However, READ MORE

04

Jun

Microsoft releases the latest update of Analytics Platform System

Microsoft is pleased to announce that the Analytics Platform System (APS) appliance update 7 (AU7) is now generally available. APS is Microsofts scale-out Massively Parallel Processing (MPP) system based on SQL Server for data warehouse specific workloads on-premises.

Customers will get significantly improved query performance and enhanced security features with this release. APS AU7 builds on appliance update 6 (APS 2016) release as a foundation. Upgrading to APS appliance update 6 is a prerequisite to upgrade to appliance update 7.

Faster performance

APS AU7 now provides the ability to automatically create statistics and update of existing outdated statistics for improved query optimization. APS AU7 also adds support for setting multiple variables from a single select statement reducing the number of redundant round trips to the server and improving overall query and ETL performance time. Other T-SQL features include HASH and ORDER GROUP query hints to provide more control over improving query execution plans.

Better security

APS AU7 also includes latest firmware and drivers along with the hardware and software patch to address the Spectre/Meltdown vulnerability from our hardware partners.

Management enhancements

Customers already on APS2016 will experience an enhanced upgrade process to APS AU7 allowing a shorter maintenance window with

01

Jun

SQL Server 2016/2017: Availability group secondary replica redo model and performance

https://blogs.msdn.microsoft.com/sql_server_team/sql-server-20162017-availability-group-secondary-replica-redo-model-and-performance/Source: https://blogs.msdn.microsoft.com/sql_server_team/sql-server-20162017-availability-group-secondary-replica-redo-model-and-performance/   When availability group was initially released with SQL Server 2012, the transaction log redo was handled by a single redo thread for each database in an AG secondary replica. This redo model is also called as serial READ MORE

29

May

Cloud data and AI services training roundup May 2018

https://cloudblogs.microsoft.com/sqlserver/2018/05/29/cloud-data-and-ai-services-training-roundup-may-2018/Source: https://cloudblogs.microsoft.com/sqlserver/2018/05/29/cloud-data-and-ai-services-training-roundup-may-2018/     To help you stay up to date on online training opportunities, were releasing a monthly list of the latest free Data and Artificial Intelligence (AI) sessions in one convenient post. SQL Database Azure SQL Database is READ MORE

29

May

SQL Server at the Red Hat Summit 2018
SQL Server at the Red Hat Summit 2018

This blog post is authored byBob Ward, Principal Architect, Microsoft; Travis Wright, Principal Program Manager, Microsoft; and Jamie Reding, Senior Program Manager, Microsoft.

A few weeks ago, developers from around the world gathered for the Microsoft Build Conference. It was an amazing display of Microsofts products and cloud services to meet the needs of all types of applications. I missed the //build event this year because I found myself in San Francisco at the Red Hat Summit 2018. I cant even imagine five years ago someone telling me I would represent Microsoft and SQL Server at an open-source based event.

Travis Wright, Jamie Reding, and I travelled to the event to speak and show amazing demos of SQL Server running on Red Hat Enterprise Linux (RHEL) and OpenShift. We were part of the Microsoft team attending the event to continue to show the great partnership we have and are building with Red Hat. Get an overview of the Microsoft presence at the summit from Robin Ginns blog post.

This was not the first time for Travis or Jamie attending this event. Microsoft demonstrated SQL Server on Linux at the Red Hat Summit 2016. Travis and Jamie both presented at the

14

May

A new update for mssql-cli, an interactive CLI, is now available

We have released our second major update for mssql-cli since our public preview announcement in December. You can view the public preview announcement here.

mssql-cli is a new and interactive command line query tool for SQL Server. This open source tool works cross-platform and is part of the dbcli community.

See the install guide to download mssql-cli and get started.

mssql-cli auto-completion in action

In this release (v0.13.0), highlights include:

Apt-get and Linux packaging support GDPR Compliance New demo video Apt-get and Linux packaging support

One of the key issues Linux users run into when setting up mssql-cli for the first time is not having the right version of Python or having to install Python for the first time. We want to make the first experience with mssql-cli painless, thus we added apt-get support in order to package Python with your installation to help improve the acquisition experience.

For full instructions to acquire mssql-cli for each Linux distribution, please check out the Linux installation guide.

Note: For those who already installed mssql-cli via pip install, please run

sudo pip uninstall mssql-cli

Then, follow the installation instructions.

GDPR compliance

As many of us are familiar with, GDPR is

14

May

Visualize geographic spatial data from SQL Server directly in Tableau 2018.1

This is a partner post authored with the Tableau team

As part of Microsofts open and flexible platform for data and analytics, were always excited when partners add features that will expand customer options and extend business functionality. Business intelligence partner Tableau has recently introduced enhancements that will make it easier for their users to process spatial data stored in SQL Server.

The SQL Server database engine has two spatial types geometry and geography. The geography spatial type helps organize geospatial mapping data into SQL Server tables and works with several SQL-native graphing functions to answer questions like how far apart two geographic locations are, or what locations fall within a certain radius.

Before, customers had to process geography spatial data stored in SQL Server into Shapefiles before they could access it from Tableau. Now with Tableau 2018.1, customers can connect to and visualize data stored in SQL Server directly. This means Tableau will recognize spatial data in your SQL Server tables without any intermediate steps, and customers can leverage spatial operations for geography supported by SQL Server or custom queries to work with geographic data stored in SQL Server. Alongside support for native spatial data, Tableau 2018.1 also includes

09

May

SSMS 17.7 is now available
SSMS 17.7 is now available

This post is co-authored bySandy Winarko, Principal PM, SQL Server.

We are excited to announce the release of SQL Server Management Studio (SSMS) 17.7!

Download SSMS 17.7 and review the Release Notes to get started.

SSMS 17.7 provides support for almost all feature areas on SQL Server 2008 through the latest SQL Server 2017, which is now generally available.

In addition to enhancements and bug fixes, SSMS 17.7 comes with several exciting new features:

Support package scheduling in Azure-SSIS integration runtime. Support for SSIS package scheduling in SQL Agent on SQL Managed instance. It is now possible to create SQL Agent jobs to execute SSIS packages on the managed instance. Replication monitor now supports registering a listener for scenarios where publisher database and/or distributor database is part of Availability Group. So with this release of SSMS, you can monitor replication environments where publisher database and/or distribution database is part of Always On. Fixed bug fixes Scheduling SSIS Activities in ADF Pipelines via SSMS

Unlike on-premises SQL Server/Azure SQL Managed Instance (MI) that have a native/first-class SSIS job scheduler as part of SQL Server/MI Agent, Azure SQL Database does not have any. Consequently, if you use Azure SQL

08

May

The May release of SQL Operations Studio is now available

This post is co-authored by Karl Burtram, Senior Software Engineer, SQL Server.

We are excited to announce the May release of SQL Operations Studio is now available.

Download SQL Operations Studio and review the Release Notes to get started.

SQL Operations Studio is a data management tool that enables you to work with SQL Server, Azure SQL DB and SQL DW from Windows, macOS and Linux. To learn more, visit our Github.

SQL Operations Studio was announced for Public Preview on November 15th at Connect(), and this May release is the sixth major update since the announcement. If you missed it, you can view the April release announcement.

The May Public Preview release is focused on improving our Extensibility experience with the release of new extensions as well as addressing top Github issues.

Highlights for this build include the following.

Announcing Redgate SQL Search extension available in Extension Manager Community Localization available for 10 languages: German, Spanish, French, Italian, Japanese, Korean, Portuguese, Russian, Simplified Chinese and Traditional Chinese! GDPR-compliant build has reduced telemetry collection, improved opt-out experience and in-product links to Privacy Statement Extension Manager has improved Marketplace experience to easily discover community extensions SQL Agent extension Jobs and