Category: Learn

Science FTW!
SQL Server Performance

Planning to Increase Cost Threshold for Parallelism – Like a Smart Person!

When administrating a SQL Server instance with multiple CPU cores and heavy workload, it’s common to see SQL Server creating and using execution plans with parallelism. The instance configuration “cost threshold for parallelism” is what determines for SQL Server the minimum sub-tree cost before it starts considering to create a parallelism plan. The default “out-of-the-box” value of this configuration is 5.

 However, in some cases, we would want to increase the default configuration of “cost threshold for parallelism” to something higher than the Microsoft default of 5, thus decreasing the frequency in which SQL Server creates parallelism plans.

This is something we would usually want to do when we see too much CPU workload as a result of parallelism plans, and we would want to reduce it. 

But how can we know which new value we should use? Do we just throw a randomly high number such as 50 and see what happens (as is often recommended in most blogs)?

Well, no, we don’t actually have to do that.

We can do it like smart people.

Being smart is a good thing. Continue reading Planning to Increase Cost Threshold for Parallelism – Like a Smart Person!

Eitan BluminLeave a comment
Joins
SQL Server Database Engine, SQL Server Performance

The LOOP, HASH and MERGE Join Types

Today I’ll talk about the available JOIN operator types in SQL Server (Nested Loops, Hash and Merge Joins), their differences, best practices and complexity. For the samples in this post, we’ll use the free AdventureWorks database sample available here: http://msftdbprodsamples.codeplex.com/releases/view/4004 Introduction: What are Join Operators? A join operator is a type of an algorithm which the SQL Server Optimizer chooses in order to implement logical … Continue reading The LOOP, HASH and MERGE Join Types

Eitan BluminLeave a comment
These are not the duplicates you're looking for
SQL Server Database Engine

The "IGNORE_DUP_KEY" option in Primary Keys and Unique Indexes

Unlike foreign keys, primary keys and unique indexes cannot be “untrusted”. There’s no “NOCHECK” option for unique constraints. The closest there is, is the “ignore_dup_key” option. “Hmm… That sounds interesting”, you must be thinking. But let me stop you right there, buddy. Read this before you go off doing something potentially dangerous… Very, VERY dangerous. But it does NOT mean that you can create such … Continue reading The "IGNORE_DUP_KEY" option in Primary Keys and Unique Indexes

Eitan BluminLeave a comment
T-SQL

Let SQL Server Write Code for You

Many times I happen to find myself in a situation where, as a DBA, I need to write some long and cumbersome code (usually for maintenance purposes). For example: Write a script to copy a bunch of tables from one database to another. Import the tables from one database to another database. Rebuild a bunch of indexes. Update a bunch of statistics. Write scripts for … Continue reading Let SQL Server Write Code for You

Eitan BluminLeave a comment
SQL Server Performance, T-SQL

Efficiently UPDATE and DELETE using TOP and ORDER BY

Since SQL Server 2005, Microsoft had a nice addition to the common DML statements, UPDATE and DELETE, by allowing the use of the TOP keyword. Well, we’re now in the year 2018 and SQL Server 2019 is just around the corner, and yet unfortunately for many of us, SQL Server still doesn’t support the use of the TOP keyword in direct conjunction with the ORDER … Continue reading Efficiently UPDATE and DELETE using TOP and ORDER BY

Eitan BluminLeave a comment
SQL Server Database Engine, SQL Server Performance

The Curious Case of Collations and Performance in SQL Server

One fateful night, cold and dreary, I’ve stumbled upon an apparently little known fact about SQL Server – specifically SQL Server collations and how they affect performance… Yes, really!

While normalizing a table in their database, one of my clients had weird and inconsistent performance problems and couldn’t find a solution for it for quite a while. Continue reading The Curious Case of Collations and Performance in SQL Server

Eitan BluminLeave a comment
T-SQL

Remove the IDENTITY property from an existing column

How to remove the IDENTITY property from an existing column using T-SQL (without changing the order of the columns or moving the data into a new table) Recently I had a scenario at a client where I needed to remove the IDENTITY property of a column in an existing table. The two common methods to do this were quite problematic and were no good for … Continue reading Remove the IDENTITY property from an existing column

Eitan BluminLeave a comment