SQL Server data compression using the SSMS Data Compression Wizard April 3, 2018 by Marko Zivkovic Every DBA knows that database grows over time and that the growth never stops. The more data is in the database, the more time (work) SQL Server is needed in order to deal with it. From SQL Server 2008 on, new tools are available in order to help DBAs to reduce the size of database. This article will describe SQL Server data compression and the SSMS Data Compression Wizard will be explained. Read more »
Filtered Stats and CE Model Variation April 2, 2018 by Dmitry Piliugin In this blog post, we are going to view some interesting model variation, that I’ve found while exploring the new CE. A model variation is a new concept in the cardinality estimation framework 2014, that allows easily turn on and off some model assumptions and cardinality estimation algorithms. Model variations are based on a mechanism of pluggable heuristics and may be used in special cases. I think they are left for Microsoft support to be able to address some client’s CE issues pointwise. Today we are going to view some interesting model variation, that creates filtered statistics on-the-fly. I should give a disclaimer here. Read more »
Performance tuning – Nested and Merge Loops with Execution Plans April 2, 2018 by Thomas LeBlanc Even though reading execution is technical, it is more an art than science. The main iterator used when joining tables is a Loop. Nested and Merge loops are 2 of the most common. A plan can even have a loop without joining tables when a Seek needs a Lookup to find additional columns. This art of reading execution plan loops can help with performance tuning and debugging T-SQL. Once over the hump of reading a plan, going from beginner to intermediate is simple. Read more »
Cardinality Estimation Framework Version Control March 30, 2018 by Dmitry Piliugin This is a small post about how you may control the cardinality estimator version and determine which version was used to build a plan. The version of the cardinality framework is determined by the query database context, where the database has a specific compatibility level. When you create a database in SQL Server 2014 it has the latest compatibility level equals 120 by default. If you issue a query in that database context, the new cardinality version will be used. You may verify this by inspecting the plan property “CardinalityEstimationModelVersion” of the root language element (the one with the green icon), SELECT, for example. Read more »
Cardinality Estimation Process March 29, 2018 by Dmitry Piliugin In this post, we are going to take a deeper look at the cardinality estimation process. We will use SQL Server 2014, the main concepts might also be applied to the earlier versions, however, the process details are different. Calculators The algorithms responsible for performing the cardinality estimation in SQL Server 2014 are implemented in the classes called Calculators. Read more »
Data science, data understanding and preparation – ordinal variables and dummies March 29, 2018 by Dejan Sarka In my previous article, Introduction to data science, data understanding and preparation, I showed how to make an overview of a distribution of a discrete variable. I analyzed the NumberCarsOwned variable from the dbo.vTargetMail view that you can find in the AdventureWorksDW2016 demo database. The graphs I created in R and Python and the histogram created with T-SQL were all very nice. Now let me try to create a histogram for another variable from that view, for the Education variable. I am starting with R, as you can see from the following code. Read more »
Cardinality Estimation Concepts March 28, 2018 by Dmitry Piliugin In this blog post we are going to talk about the principles and the main concepts which are used by the optimizer to perform an estimation, and also, we will do a little bit math, so be prepared. Base Statistics A cardinality estimation mechanism, as a first step, usually uses base statistics to estimate the expected number of rows that should be returned from the base table. You may look at these statistics using DBCC command – DBCC SHOW_STATISTICS. Read more »
Getting started building applications using SQL Server DevOps Tools March 28, 2018 by Prashanth Jayaram This article is part of the SQL Server Tools series, aimed at giving you an idea of the available tools and techniques to build applications using SQL Server tools. The first article of the series covers the rudimentary DevOps concepts and discusses on a high level, the overview of SQL Server tools that can be piped for DevOps operations. Read more »
Cardinality Estimation Place in the Optimization Process March 27, 2018 by Dmitry Piliugin In this blog post, I’m going to look at the place of the Cardinality Estimation Process in the whole Optimization Process. We’ll see some internals, that will show us, why the Query Optimizer is so sensitive to the cardinality estimation. To understand that we should observe the main steps that a server performs when the query is sent for execution. Plan Construction Process Below you may see the picture of the general plan-building process in SQL Server from the moment when a server receives a query till the storage engine is ready to retrieve the data. Take a quick look first, and next I’ll provide explanations. Read more »
Migrating an Oracle Database to SQL Server with Microsoft Data Migration Assistant – Installation Process and Short Overview March 27, 2018 by Jefferson Elias Intended audience This document is intended for application developers and database administrators who plan to migrate an Oracle Database to Microsoft SQL Server. Context Nowadays, most IT professionals are aware that Oracle Database is a very good database solution, but it’s not inexpensive, to say the least. If you want to partition a table in Oracle, it’s an option that you must pay for, which is the same for advanced security options like Database Encryption or Dynamic Data Masking. In contrast, SQL Server comes with these options out of the box when you use the Enterprise Edition. Read more »
Cardinality Estimation Role March 26, 2018 by Dmitry Piliugin This post opens a series of blog posts dedicated to my observations of the new cardinality estimator in SQL Server 2014. But, before we jump to the new features, I’d like to provide some background, to make the next posts clearer. We’ll start by discussing the role of Cardinality Estimation in SQL Server, trying to answer – what is it and why it is needed. Read more »
SQL Server Reporting Service: how to handle common end-user requirements with Report Builder March 26, 2018 by Esat Erkec In this article, we will discuss the SQL Server Reporting Service Report Builder and look at how to handle common client, end user requests using Report Builder. Report Builder is a very powerful tool to create a report for the SQL Server Reporting Service. Advantages of the Report Builder is that it: exists as a Stand-alone installation can use different data from a lot of data sources can easily deploy reports to SQL Server Reporting Service supports full capabilities of SQL Server Reporting Service provides a productive report-authoring environment Read more »
How to migrate SQL Server 2017 Master Data Services Models into another server March 23, 2018 by Sifiso W. Ndlovu 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 »
Sequence Objects in SQL Server March 22, 2018 by Ben Richardson Sequence objects are used to sequentially generate numeric values. They were introduced in SQL Server 2012. Sequence objects are similar to the IDENTITY column in any SQL table. However, unlike the IDENTITY column, they are independent and are not attached to any table. Sequence objects are used both independently and within the DML statements i.e. INSERT, UPDATE and DELETE. This article will take a detailed look at sequence objects. Read more »
Understanding the SQL Server Data Management Life Cycle March 21, 2018 by Prashanth Jayaram This is the second article in the “Backup and Restore (or Recovery) in SQL Server” stairway series (see the full TOC below). This article deals with the different phases of data management life cycle and it encompasses the following topics: Introduction to data corruption Defining data corruption and its causes Discussion on the impact of data corruption Explaining data prevention mechanisms Data protection And more… Read more »
Time Intelligence in Analysis Services (SSAS) Tabular Models March 20, 2018 by Thomas LeBlanc In the analytical world, time is an important slicer. The ability to view data over time helps analyze measures in actionable steps. An Analysis Service (SSAS) database can help accomplish this important step. Business users are going to want the ability to see measures a like year to date as well as the period over period comparisons. SSAS enables Time Intelligence with 2 features: the date table and DAX functions. Read more »
SQL Server Index Structure and Concepts March 19, 2018 by Ahmad Yaseen In my previous article, SQL Server Table Structure Overview, we described, in detail, the difference between Heap table structure, in which the data pages are not sorted in any ordering criteria and the pages itself are not sorted or linked between each other, and Clustered tables, in which the data is sorted within the data pages and the pages will be also linked in a double linked list, based on the index key. In this article, we will go through the structure of the SQL Server index, itself. Read more »
An overview of SQL Server database migration tools provided by Microsoft March 16, 2018 by Jefferson Elias Database migration is part of the DBA job. We can’t avoid it, and there are multiple options that can be taken: From SQL Server to SQL Server From another platform (Oracle Database, MySQL, PosgreSQL…) to SQL Server From SQL Server to another platform (Oracle Database, MySQL, PosgreSQL…) Read more »
Introduction to data science, data understanding and preparation March 14, 2018 by Dejan Sarka Data science, machine learning, data mining, advanced analytics, or however you want to name it, is a hot topic these days. Many people would like to start some project in this area. However, very soon after the start you realize you have a huge problem: your data. Your data might come from your line of business applications, data warehouses, or even external sources. Typically, it is not prepared for applying advanced analytical algorithms on it straight out of the source. In addition, you have to understand your data thoroughly, otherwise you might feed the algorithms with inappropriate variables. Soon you learn the fact that is well known to seasoned data scientists: you spend around 70-80% of the time dedicated to a data science project on data preparation and understanding. Read more »
Automate documentation of SQL Server Analysis Server Tabular Model March 13, 2018 by Divya Agrawal There has been an ever-growing discomfort on documenting things especially when it’s very dynamic in nature and I was at one time undergoing the same. We have been developing SQL Server Analysis Tabular Model’s, which were quite a many in numbers and documenting 50 or 60 models manually is a big effort. In addition, development always demands change or enhancements that incurs changes on the documentation that is a continuous process. Adding the changes on the documentation is a time consuming process and sometimes loses track or remains inconsistent with the code built which is a critical issue when the system is in production for years. I decided to automate the documentation of the SSAS model, to ease the manual effort here as well as save time. In addition, help the end users to see and review the latest change on the models quickly without even bothering the development team. Read more »