Category: SQL Server

Common Indexing Questions

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

A Gentle Introduction To Query Tuning

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Choose Your Columns Carefully

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Workin’

If the Gods of post scheduling cooperate, this should publish in October. That means you’ve got a little more than a month to buy tickets and book travel to PASS.

And of course, if you’re going to go, I’d love it you came to my precon: The Professional Performance Tuning Blueprint.

Searching the internet for every problem isn’t cutting it.

You need to be more proactive and efficient when it comes to finding and solving database performance fires. I work with consulting customers around the world to put out SQL Server performance fires.

In this day of learning, I will teach you how to find and fix your worst SQL Server problems using the same modern tools and techniques which I use every week.

You’ll learn tons of new and effective approaches to common performance problems, how to figure out what’s going on in your query plans, and how indexes really work to make your queries faster.

Together, we’ll tackle query rewrites, batch mode, how to design indexes, and how to gather all the information you need to analyze performance.

This day of learning will teach you cutting edge techniques which you can’t find in training by folks who don’t spend time in the real world tuning performance.

Performance tuning mysteries can easily leave you stumbling through your work week, unsure if you’re focusing on the right things.

You’ll walk out of this class confident in your abilities to fix performance issues once and for all.

If you want to put out SQL Server performance fires, this is the precon you need to attend. Anyone can have a plan, it takes a professional to have a blueprint.

All attendees will get free access to my performance tuning training library, which also covers the material covered in the precon.

In-person attendees will also get a cool t-shirt. Arguably the coolest t-shirt ever made for a SQL Server conference.

Dates And Times

The PASS Data Community Summit is taking place in Seattle November 15-18, 2022 and online.

You can register here, to attend online or in-person. I’ll be there in all my fleshy goodness, and I hope to see you there too!

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Searching For Data With Queries

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

SQL Server Query Plans

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

SQL Server Indexes

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Query Execution

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Workin’

If the Gods of post scheduling cooperate, this should publish in October. That means you’ve got a little more than a month to buy tickets and book travel to PASS.

And of course, if you’re going to go, I’d love it you came to my precon: The Professional Performance Tuning Blueprint.

Searching the internet for every problem isn’t cutting it.

You need to be more proactive and efficient when it comes to finding and solving database performance fires. I work with consulting customers around the world to put out SQL Server performance fires.

In this day of learning, I will teach you how to find and fix your worst SQL Server problems using the same modern tools and techniques which I use every week.

You’ll learn tons of new and effective approaches to common performance problems, how to figure out what’s going on in your query plans, and how indexes really work to make your queries faster.

Together, we’ll tackle query rewrites, batch mode, how to design indexes, and how to gather all the information you need to analyze performance.

This day of learning will teach you cutting edge techniques which you can’t find in training by folks who don’t spend time in the real world tuning performance.

Performance tuning mysteries can easily leave you stumbling through your work week, unsure if you’re focusing on the right things.

You’ll walk out of this class confident in your abilities to fix performance issues once and for all.

If you want to put out SQL Server performance fires, this is the precon you need to attend. Anyone can have a plan, it takes a professional to have a blueprint.

All attendees will get free access to my performance tuning training library, which also covers the material covered in the precon.

In-person attendees will also get a cool t-shirt. Arguably the coolest t-shirt ever made for a SQL Server conference.

Dates And Times

The PASS Data Community Summit is taking place in Seattle November 15-18, 2022 and online.

You can register here, to attend online or in-person. I’ll be there in all my fleshy goodness, and I hope to see you there too!

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Quickly, man!

I’m still looking into this a bit, but I ran into this issue when helping set up a process to migrate data to a table because the original table was running out of integer identity values.

The process looks something like:

• Create a new table with the right definition
• Write a loop to backfill existing table data
• Create a trigger to keep updated data synchronized
• Eventually swap the tables during a short maintenance window

All well and good! Except… You can end up with a really weird execution plan for the backfill process, and some pretty gnarly memory grants for wide tables.

Demonstrating with the Votes table:

CREATE TABLE 
    dbo.Votes_bigint
(
    Id bigint IDENTITY NOT NULL,
    PostId int NOT NULL,
    UserId int NULL,
    BountyAmount int NULL,
    VoteTypeId int NOT NULL,
    CreationDate datetime NOT NULL,
    CONSTRAINT PK_Votes_bigint_Id 
        PRIMARY KEY CLUSTERED (Id ASC)
) 
GO

INSERT
    dbo.Votes_bigint
(
    Id,
    PostId,
    UserId,
    BountyAmount,
    VoteTypeId,
    CreationDate
)
SELECT TOP (100000)
    v.Id,
    v.PostId,
    v.UserId,
    v.BountyAmount,
    v.VoteTypeId,
    v.CreationDate
FROM dbo.Votes AS v
ORDER BY v.Id;

The query plan has a Sort in it, which is weird because… Both tables are primary key/clustered index on the same column. Why re-sort that data?

I’m showing both serial and parallel plans:

The Sort is always on this expression:

Which in the XML looks like this:

[StackOverflow2013].[dbo].[Votes_bigint].[Id] = [Expr1003]

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.