Monitoring databases for optimal query performance, creating and maintaining required indexes, and dropping rarely-used, unused or expensive indexes is a common database administration task. As administrators, we’ve all wished, at some point, that these tasks were simpler to handle.Read more »
SQL Server 2017 is considered a major release in the history of the SQL Server life cycle for various reasons. From my personal point of view, SQL Server 2017 is indeed an interesting release. After writing lot about it and testing various features of SQL Server 2017, I’d like to walk you through some of its interesting features.Read more »
In this 18th article of the series, we will discuss the concepts of database backup-and-restore of SQL Server Docker containers using Azure Data Studio. Before proceeding, you need to have Docker engine installed and Azure Data Studio configured on your host machine.
This article covers the following topics:
- Overview of Azure Data Studio (ADS)
- How to use Azure Data Studio integrated terminal
- Definition of Docker containers
- Step by step instructions to initiate backup-and-restore of SQL Server 2017 Docker containers using the Azure Data Studio interface
- And more…
I’ve always been in favor of an orthodox strategy when it comes to applying SQL Server updates which often goes like:
- Instead of installing SQL Server Cumulative Updates, wait for release of service packs
- When a service pack is released, install it in phases starting from the non-production environment (i.e. DEV, UAT) to eventually roll it out on production
The new SQL Server 2017 comes with new features in the installation. It now supports Machine Learning Services that support R and Python. It also includes SSIS Scale Out Master and Scale Out Worker. It also includes scale out options in PolyBase.Read more »
So far, we’ve discussed several phases of backup that starts with planning, creating, strategizing and implementing. In this article, we are going to see how database administrators can define the strategy to improve backup performance and efficiently manage backups in SQL Server 2017. The following are the topics of discussion:
- Discuss checkpoints
- Discuss the enhancements made in the Dynamic Management View (DMV) sys.dm_db_file_space_usage for smart differential backups
- Discuss the enhancements made for the Dynamic Management function (DMF) sys.dm_db_log_stats for smart transactional log backup
- Understand the functioning of smart differential backup and its internals
- Understand the Smart transaction log backup process and its internals
- T-SQL scripts
- And more…
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.
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 »
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 »
This article explores SQL Sort, Spill, Memory and Adaptive Memory Grant Feedback mechanism in SQL Server.Read more »
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 »
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 »
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 »
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 »
In this post, we will continue to look at the cardinality estimation changes in SQL Server 2016. This time we will talk about scalar UDF estimation. Scalar UDFs (sUDF) in SQL Server have quite bad performance and I encourage you try to avoid them in general, however, a lot of systems still use them.
Scalar UDF Estimation Change
I’ll use Microsoft sample DB AdventureworksDW2016CTP3 and write the following simple scalar function, it always returns 1, regardless of the input parameter. I run my queries against Microsoft SQL Server 2016 (SP1) (KB3182545) – 13.0.4001.0 (X64)Read more »
Often as consultants, we don’t get to work onsite alongside our clients instead we are given copies of clients’ production environment and work on proposed solutions back at our offices. Once development has been completed, we then deploy and integrate our solution back to the client’s production environment. I’ve recently had to adopt a similar offsite development approach whilst working on a project that included development and configuration of master data services. In this article, I will demonstrate how a SQL Server 2017 Master Data Services (MDS) model can be exported from one environment (i.e. MDS Dev) and deployed into another environment (i.e. MDS Prod).Read more »
Just like in Santa’s Bag of Goodies, every release of SQL Server often has something for everyone – be it enhancements to DMVs for the DBAs, new functions for T-SQL developers or new SSIS control tasks for ETL developers. Likewise, the ability to effectively support many-to-many relationships type in SQL Graph has ensured that there is indeed something in it for the data warehouse developers in SQL Server 2017. In this article, we take you through the challenges of modelling many-to-many relationships in relational data warehouse environments and later demonstrate how data warehouse teams can take advantage of the many-to-many relationship feature in SQL Server 2017 Graph Database to effectively model and support their data warehouse solutions.Read more »
Every seasoned SQL Server developer will tell you that no matter how hard you try, there are just operations in SQL Server better implemented elsewhere than relying on native Transact-SQL language (T-SQL). Operations such as performing complex calculations, implementing regular expression checks and accessing external web service applications can easily lead to your SQL Server instance incurring significant performance overhead. Thankfully, through its common language runtime (CLR) feature, SQL Server provides developers with a platform to address some of the inconveniences of native T-SQL by supporting an import of assembly files produced from projects written in. Net programming languages (i.e. C#, VB.NET). I have personally found CLR to be very useful when it comes to splitting string characters into multiple delimited lines.Read more »
Every DBA, even a beginner, may walk through the SQL Server backup screen multiple times per day. It is mandatory that you know every single detail of every single option you have in the most repeatable task you could do as a DBA.
In this article, I will be discussing every option available in full backup screen of SQL Server 2016.Read more »
This article will explain the main features in SQL Server 2017, 2016, 2015, 2014, 2012, 2008, 2005, 2000, 7, 6.5, 6.0, 4.2, 1.1 and 1.0.
In the past, the first SQL Server versions supported OS/2 (an operative system created by Microsoft and IBM) and Windows.
Now, the new versions of SQL Server (vNext and SQL Server 2017) can be installed in Linux. 15 years ago, it was impossible to think that. Linux and Microsoft were just like oil in water and now, Microsoft loves Linux.
Also, we now enjoy full integration with Azure, Tabular Databases, SSIS, SSAS and more. In this article, we will talk about all these changes and improvements.Read more »
Dynamic management views (DMVs) and dynamic management functions (DMFs) are system views and system functions that return metadata of the system state. On querying the related system objects, database administrators can understand the internals of SQL Server. It allows us to monitor the performance of the SQL Server instance, and diagnose issues with it.
SQL Server 2017 ships with a number of new and enhanced dynamic management views and dynamic management functions that will help DBAs monitor the health and performance of SQL Server instances. A few existing DMV’s such as sys.dm_os_sys_info and sys.dm_db_file_space_usage have been enhanced. Some have also been newly built and available only for SQL Server 2017.Read more »
In the article How to plot a SQL Server 2017 graph database using SQL Server R, I highlighted the lack of built-in graph data visualisation as one major limitation of the SQL Server 2017 graph database feature. In the same article, I went on to suggest making use of SQL Server R as one workaround that could be utilised in order to successfully plot and visualise diagrams out of SQL Server 2017 graph database objects. However, whilst 3rd party graph database vendors such as Neo4j provide an interactive and hyperlinked graph diagrams that allows you to – amongst other things – easily drilldown and identify node-relationships as indicated in Figure 1, the graph plotted using SQL Server R is not very interactive in fact it is simply a static image file as shown in Figure 2.Read more »
One of the new features announced with SQL Server 2017 is support for the Python language. This is big! In SQL Server 2016, Microsoft announced support for the R language – an open source language ideally suited for statistical analysis and machine learning (ML). Recognizing that many data scientists use Python with ML libraries, the easy-to-learn-hard-to-forget language has now been added to the SQL Server ML suite.
There’s a big difference between R and Python though: R is a domain-specific language while Python is general purpose. That means that the full power of Python is available within SQL Server. This article leaves ML aside for the moment and explores a few of the other possibilities.Read more »
A few years ago, one common business case I came across in my professional career that required modelling of data into a many-to-many entity relationship type was the representation of a consultants and their projects. Such a business case became a many-to-many entity relationship type because whilst each project can be undertaken by several consultants, consultants can in turn be involved in many different projects. When it came to storing such data in a relational database engine, it meant that we had to make use of bridging tables and also make use of several self-joins to successfully query the data.Read more »
Every data warehouse developer is likely to appreciate the significance of having surrogate keys as part of derived fields in your facts and dimension tables. Surrogate keys make it easy to define constraints, create and maintain indexes, as well as define relationships between tables. This is where the Identity property in SQL Server becomes very useful because it allows us to automatically generate and increment our surrogate key values in data warehouse tables. Unfortunately, the generating and incrementing of surrogate keys in versions of SQL Server prior to SQL Server 2017 was at times challenging and inconsistent by causing huge gaps between identity values. In this article, we take a look at one improvement made in SQL Server 2017 to reduce the creation of gaps between identity values.Read more »