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 »
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 »
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 »
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 »
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 »
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 »
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 »
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 »
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 »
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 »
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 »
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 »
In this article, we will explore Nested and Merge SQL Loops in the SQL Execution plan from a performance tuning view.
Read more »
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 »
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.
The algorithms responsible for performing the cardinality estimation in SQL Server 2014 are implemented in the classes called Calculators.
Read more »
In my previous article, Introduction to data science, data understanding and preparation, I showed how to make an overview of the distribution of a discrete SQL variable. I analyzed the NumberCarsOwned variable from the dbo.vTargetMail view that you can find in the AdventureWorksDW2016 demo database. The data science 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 SQL variable from that view, for the Education variable. I am starting with R, as you can see from the following code.
Read more »
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.
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 »
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 »
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.
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 »
This document is intended for application developers and database administrators who plan to migrate an Oracle Database to Microsoft SQL Server.
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 »
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 »
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:
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 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 »
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:
© Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy