SQL Server index Operations April 19, 2018 by Ahmad Yaseen In the previous articles of this series, we described the structure of the SQL Server tables and indexes, the main concepts that are used to describe the index and the basics and guidelines that are used to design the proper index. In this article, we will go through the operations that can be performed on the SQL Server indexes. Creating Indexes Before creating an index, it is better to follow the index design guidelines and best practices that are described in the previous article, to determine the columns that will participate in the index, the type of the created index, the suitable index options, such as the FillFactor or Sort in TempDB, and the storage location of that index. Read more »
Understanding SQL Server Backup Types April 19, 2018 by Prashanth Jayaram SQL Server backups, in itself, is a vast subject; so vast, there are multiple books written about them. In this article, however, we are going to focus on the types of backups that are available to us, and understand how to pick what we need, and what aspects we base that decision on. This understanding would, in turn, help us decide our backup-and-restore strategy. Following are the most common types of backups available in SQL Server: Full Differential Transaction log Tail Log backup Read more »
Batch Sort and Nested Loops April 18, 2018 by Dmitry Piliugin Continuing my blog post series after 24HOP Russia “Query Processor Internals – Joins”. In this (and the next one) blog post, we will talk about the Nested Loops Post Optimization Rewrite optimizations. Some of you may know that Nested Loops join algorithm preserves order of the outer table. Read more »
How to connect and perform a SQL Server database restore from Azure BLOB storage April 18, 2018 by Istvan Martinka Having things in the cloud should make life simpler but I have experienced it’s not that straightforward. Once all access / configuration is sorted out then yes, of course! But in the meantime it can be tedious (even frustrating) and the end result is something that could have been achieved with a different method. I think a significant chunk of what Azure offers is easier to do with the more conventional methods but that separates us from the advanced Azure features that a company would like to utilize in the shorter / longer term future. So there are reasons to spend some time / effort in getting things right up there. In my case the task I needed to accomplish sounded simple enough: get a SQL Server database backup (.bak file) from Azure blob storage copy it to our own environment and restore it to a SQL Server instance on an Azure Windows VM or skip the copy step and restore straight from blob storage Read more »
How to identify and monitor unused indexes in SQL Server April 17, 2018 by Nikola Dimitrijevic SQL Server indexes are essentially copies of the data that already exist in the table, ordered and filtered in different ways to improve the performance of executed queries. Seeks, scans and lookups operators are used to access SQL Server indexes. Read more »
Hash Join Execution Internals April 17, 2018 by Dmitry Piliugin Some time ago, on the 24HOP Russia I was talking about the Query Processor internals and joins. Despite I had three hours, I felt the lack of time, and something left behind, because it is a huge topic, if you try to cover it in different aspects in details. With the few next articles, I’ll try to describe some interesting parts of my talk in more details. I will start with Hash Join execution internals. The Hash Match algorithm is one of the three available algorithms for joining two tables together. However, it is not only about joining. You may observe a complete list of the logical operations that Hash Match supports in the documentation: Read more »
Yet another X-Ray for the QP April 16, 2018 by Dmitry Piliugin This post is for the “optimizer” fan people. For those who are not afraid when they hear: “query transformation rules”. It this is you and you are interested in the optimizer internals – welcome. Consider the following DB – opt. And the following query: Read more »
What’s new in SSMS 17.5; Data Discovery and Classification and more April 16, 2018 by Ahmad Yaseen SQL Server Management Studio aka SSMS is the default integrated graphical user interface tool that is used to access, administrate, configure, manage, query and monitor the different SQL Server instances that are hosted locally at the user machine, remotely on a server or anywhere in the cloud. It is a comprehensive environment that allows us to edit, debug and deploy scripts written in different languages such as T-SQL, DAX, MDX, XML and more. In my previous articles, we followed the enhancements to the existing SSMS features, the fixes to the bugs found and reported in the previous SSMS versions and the new features added to each new release. These new features that are introduced in the previous versions include the Connections dialog box new options, the embedded Performance Dashboard, Showplan node search, Import Flat File Wizard, the XEvent Profiler and Always On Availability Group dashboard new additions, which are described deeply in these articles. Read more »
FORCESCAN and Partitioned table April 13, 2018 by Dmitry Piliugin I would like to share one curios case that I recently came across. Long story short: This bug may lead to incorrect results if you use a partitioned table and the FORCESCAN hint. Bug Consider the following example, let’s keep it simple. Read more »
How to configure OData SSIS Connection for SharePoint Online April 13, 2018 by Sifiso W. Ndlovu As data warehouse developers, we often have to extract data from a myriad of source systems. Thus, whilst some source systems readily integrate with our ETL tools there are instances whereby we need to install additional drivers and software addons in order to successfully connect and extract data from other source systems. Microsoft SharePoint Online is one such source system that I recently had to extract data from and its connectors are by default not part of the standard SQL Server Integration Services (SSIS) package template. As SSIS developers we often don’t have solid background on environments such as SharePoint, thus figuring out which version of SharePoint Software Development Kit to install in order to enable successful connection from SSIS can sometimes be a frustrating experience. In this article, I try to alleviate some of that frustration by sharing some of my recent experiences relating to getting data out of a SharePoint list using SSIS. Read more »
NOLOCK and Top Optimization April 12, 2018 by Dmitry Piliugin Sometimes people use nolock hint as a “turbo” button for their queries, assuming that not taking locks will speed up the query execution. There are many good articles describing all the dangerous moments of this approach, because of the read uncommitted isolation level. However, the focus of this article is a performance problem that you may encounter using nolock hint in some cases. Let us setup some test data first. Read more »
A concrete example of migration between an Oracle Database and SQL Server using Microsoft Data Migration Assistant April 12, 2018 by Jefferson Elias Introduction Intended audience This document is intended for application developers and database administrators who plan to migrate an Oracle Database to Microsoft SQL Server. Context In the previous article, we saw how to install and prepare to migrate one or more Oracle Schema(s) and their objects to SQL Server. Now it’s time to go through the migration process… Read more »
How to enable High-DPI in SSMS (SQL Server Management Studio) – Tips, tricks and issues April 11, 2018 by Bojan Petrovic SQL Server Management Studio (SSMS) is getting better and better with each new release but Microsoft seems to ignore one big issue affecting a group of users that grows rapidly as the technology moves forward and we move on to modern workstations and laptops. Read more »
Understanding SQL Server database recovery models April 10, 2018 by Prashanth Jayaram A recovery model is a database configuration option that determines the type of backup that one could perform, and provides the ability to restore the data or recover it from a failure. The recovery model decides how the transaction log of a database should be maintained and protects the data changes in a specific sequence, which may later be used for a database restore operation. Read more »
MTVF and CE Model Variation April 5, 2018 by Dmitry Piliugin This is a note about multi-statement table valued functions (MTVF) and how their cardinality is estimated in the new CE framework. In the old CE framework the MTVF had fixed estimate of one row, in the new one the estimate is still fixed, however, now it is 100 rows. That’s the whole story. =) Read more »
SQL Server Index Design Basics and Guidelines April 5, 2018 by Ahmad Yaseen In the previous article of this series, SQL Server Index Structure and Concepts, we described, in detail, the anatomy of QL Server indexes, the B-Tree structure, the properties of the indexes, the main types of indexes and the advantages of using the indexes for performance tuning. Read more »
Ascending Key and CE Model Variation April 5, 2018 by Dmitry Piliugin In this note, I’m going to discuss one of the most useful and helpful cardinality estimator enhancements – the Ascending Key estimation. We should start with defining the problem with the ascending keys and then move to the solution, provided by the new CE. Read more »
Overpopulated Primary Key and CE Model Variation April 4, 2018 by Dmitry Piliugin In this blog post, we are going to talk about another cardinality estimation model enhancement in SQL Server 2014 – Overpopulated Primary Key (OPK). Consider a fact table that contains information about some sales, for example, and a date dimension table. Usually, a fact table contains the data about the current year and past years, but a dimension table usually contains the data for the next few years also. Read more »
The end is nigh! (For SQL Server 2008 and SQL Server 2008 R2) April 4, 2018 by Minette Steynberg Introduction As with everything else, all good things must come to an end. After hanging around for a good decade SQL Server 2008 and SQL Server 2008 R2 has started the countdown to its End of Life. D-day for SQL Server 2008 and SQL Server 2008 R2 is set to the 19th of July 2019. Which is approximately a year from now, give or take a couple of months. If you are still running SQL Server 2008 or SQL Server 2008 R2, now is definitely the time to upgrade. In this article; I am going to tell you why you would want to upgrade to a newer version of SQL Server (2016 or 2017) or Azure SQL Database. I will also discuss some of the tools that you might use to make the upgrade as painless as possible. Read more »
Join Containment Assumption and CE Model Variation April 3, 2018 by Dmitry Piliugin In this post we are going to talk about one of the model assumptions, that was changed in the new cardinality estimation mechanism in SQL Server 2014 – Join Containment Assumption. You may find some information about this assumption in the Ian Jose’s blog post: Query Processor Modelling Extensions in SQL Server 2005 SP1, there you may find the description of the so-called simple assumption and base assumption. Another source of available information is a white paper from Joseph Sack Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator. Read more »