Category: SQL Server

Blitzing

This comes up enough that I figured I’d write it down with a quick example. Foreign keys, which are next to useless in SQL Server, can become untrusted for a variety of reasons. Why? Because they don’t help with cardinality estimation, and join elimination only happens for inner joins when you don’t select any columns from one table.

How often do you write queries like that?

Becoming Untrusted

Probably the most common reasons for foreign keys become not trusted are someone using BULK INSERT without specifying the CHECK_CONSTRAINTS option, or disabling them to make loading data via another method easier, and then re-enabling them with the “wrong” syntax.

This comes up pretty often when looking at sp_BlitzIndex results with clients, who are deeply distrustful of their data.

Let’s look at an easy example.

CREATE TABLE dbo.child(child_id INT PRIMARY KEY, parent_id INT NOT NULL);
CREATE TABLE dbo.parent(parent_id INT PRIMARY KEY);

Here are two tables, which we’ll add a foreign key to. There are all sorts of fun questions we can ask about which way the relationship should go:

• Do all parents need a child?
• Do all children need a parent?

If one if designing a school database, you may have kids with no parent listed for any number of reasons, from death to emancipation. Likewise, you may have parents of children who graduate, but getting rid of them means adding them back if they have another kid go into the school. This is already too complicated for me.

“Pretending”

Let’s say we finally settle on which way the relationship should go, and add a foreign key.

--Add a FK
ALTER TABLE dbo.child ADD CONSTRAINT parental_advisory FOREIGN KEY (parent_id) REFERENCES dbo.parent(parent_id);

This means we can have a row in parent that doesn’t exist in child:

INSERT dbo.parent ( parent_id )
VALUES ( 1 );

But we can’t have a row in child that doesn’t exist in parent:

INSERT dbo.child ( child_id, parent_id )
VALUES ( 1, 2 );

We’ll get an error for trying this!

Msg 547, Level 16, State 0, Line 42
The INSERT statement conflicted with the FOREIGN KEY constraint "parental_advisory". The conflict occurred in database "Crap", table "dbo.parent", column 'parent_id'.

Unless…

Call A Lawyer

--Disable it
ALTER TABLE dbo.child NOCHECK CONSTRAINT parental_advisory;

If we disable the foreign key, we can put whatever data in we want, including data that violates the foreign key.

We can even re-enable the foreign key without validating data we put into the table:

--Re-enable the foreign key without validating data
ALTER TABLE dbo.child CHECK CONSTRAINT parental_advisory;

If we check on the status of the foreign key, we’ll see that it’s enabled; it’s just not trusted.

SELECT fk.name,
       fk.is_disabled,
       fk.is_not_trusted
FROM sys.foreign_keys AS fk 
WHERE fk.name = 'parental_advisory';

But just having it enabled will prevent bad new data from coming in:

INSERT dbo.child ( child_id, parent_id )
VALUES ( 2, 3 );

We’ll get the same error as above.

If you want to enable the foreign key and validate current data, you need to run the command like this:

--Re-enable the foreign key validating data
ALTER TABLE dbo.child WITH CHECK CHECK CONSTRAINT parental_advisory;

But this will fail if there’s any bad data:

Msg 547, Level 16, State 0, Line 52
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "parental_advisory". The conflict occurred in database "Crap", table "dbo.parent", column 'parent_id'.

Tracking It Down

When you have foreign key validation fail, you’ll need to clean data up that doesn’t fit. You can use queries that look something like this to do it.

SELECT * 
FROM dbo.child AS c
WHERE NOT EXISTS
(
    SELECT 1/0
    FROM dbo.parent AS p
    WHERE p.parent_id = c.parent_id
);

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

It’s All A Ruse

Back at the end of 2016, a Service Pack got released along with a promise: SQL Server’s programmability surface area would be equal across all editions.

Meaning that features which were previously paywalled behind $7k cores could now be used on less expensive editions.

A lot of people were happy about this, because

• You could use features without checking edition first, which cut down on code and potential errors
• Developers were less likely to use something in developer edition, which later couldn’t be rolled out to standard edition

In other words, the “developer edition” issues were solved.

Except they weren’t, because performance is still far from equal.

Sing Along With Me Now

Let’s check out RDBMS scalability and performance for some gotchas that can sneak up on you if you’re using Developer Edition to Develop, and Standard Edition in Production.

Here’s a list of stuff that you might turn on, or benefit from when performance tuning in your development environment, that wouldn’t translate to Standard Edition:

That’s the list of stuff that’s a straight up “no”. On top of that, there are some things that aren’t quite a “no”, but aren’t quite a “yes” either. Batch Mode queries have a MAXDOP of 2 in Standard Edition, and lack SIMD support. That’s bitten me in the past when doing performance tuning work.

My Fair Lady

Some of these features are opt-in, so if you turn them on in Development and they don’t work in Production, that is your fault and your fault alone.

Others have feature switches, many of which are database scoped configurations, so you can disable them for an entire workload and not have to go mangling a bunch of queries.

But then there are others which you have no control over:

• Batch Mode DOP 2 limit
• Large page allocations
• Read aheads
• Merry go round scans (Advanced scanning)

Those are all things that seem slight, or easy to avoid, until you have performance gaps between Dev and Prod, and figuring out why takes seriously in-depth, and often frustrating trouble shooting steps.

Calls to have a “Standard Edition” switch for Developer Edition have been around, I’m told, forever and a day. I’m not going to open a new User Voice item in dev/null asking for it. Just be aware that your performance test results may not pan out when you deploy from Developer Edition to Standard Edition.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Vanilla

Features are new. Features are sexy. Features do things for you automatically.

But they often come with a lot of baggage.

• They may not work at all with other features
• They may not support full capabilities across the board
• They may require you to make architectural changes to use them
• They may never progress beyond V1 because of low adoption or shifting priorities

Once you get past that stuff, consider that things change and get added so fast that the documentation can’t keep up. You might dive headlong into a feature and have to build your own documentation about what works and what doesn’t along the way. If you’re an early adopter, there’s a pretty good chance it’s up to you to find bugs and other issues, and work with the nice folks at Microsoft to fix them.

That’s not always pleasant for databases, where changes — especially for data of a certain size — can often mean downtime, or weeks to months of preparation.

Manila

Some features require things, too. Think of the number of features that rely on tables having a Primary Key.

If you wanted to make changes to a Primary Key in order to incorporate another feature like Partitioning, you may find yourself at the at the unfortunate end of an Ouroboros of error messages, turning things like Replication or Change Data Capture or Temporal Tables off to get something working, and then finding yourself waiting a very long time for them to turn back on. And that assumes that turning them back on doesn’t require other changes.

I can hear a lot of you saying that this is the value of hiring someone with the experience to implement things correctly the first time, but that gets harder to do as you incorporate more features to accomplish database side-quests (take Auditing, for example), and new features get added into the mix that no one really has practical experience with on their own, never-you-mind when you mix them up with other features you might be using.

Gorilla

The 800lb gorilla in the room is testing space. Without that, it’s impossible to fit, retrofit, or even scope the amount of work it would take to get a new thing working in your current environment.

If you can test the process, great. But a lot of people have a hard time testing features under load, which is where many snags get hit.

At the heart of it is that there’s no such thing as a free feature. When you turn something on that requires your database to do more work on top of processing transactions, it’s up to you to figure out how to compensate for that load. You also have to make peace with the fact that certain things might be impossible, or become much harder as you introduce features.

Make sure you understand what you’re signing up for.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

The Tenant

In part 1, we designed a table for our process to work off of. Now we’re going to look at how we can write a procedure that works off of the table effectively.

For the table, our main priorities were indexing to make sure we can find work easily, and not allowing duplicate items.

For our worker procedure, the main goals are going to be

• Looping until we run out of work
• Finding and reserving work with minimal locking
• Making that process atomic

Thankfully, this is a lot easier than it sounds. The table design does a lot of the work for us.

Queue Ball

I’m going to step through the code, and then show you the fully assembled babby at the end.

First up, making a loop happen:

WHILE EXISTS
(
  SELECT 1/0
  FROM dbo.four_queue AS fq WITH(READPAST)
  WHERE fq.in_process = 0
)

While any rows that aren’t in process exist in the table, we want this to keep picking up work. If you need an eternal loop, just use 1 = 1 or something.

We’re using readpast here, because we won’t want to hit any blocking every time we check to see if we need to go back into the loop.

Second up, finding and reserving work

WITH q4 AS
(
    SELECT TOP (1) fq.*
    FROM dbo.four_queue AS fq WITH(READPAST, ROWLOCK, UPDLOCK)
    WHERE fq.in_process = 0
    ORDER BY fq.id
)
UPDATE q4
    SET q4.in_process = 1,
        q4.start_date = SYSDATETIME(),
        @id = q4.id,
        @reputation = q4.reputation
FROM q4;

This is where all the cool stuff is, to be honest.

In our CTE, we isolate a single row. The TOP (1) with an ORDER BY gives us the lowest id that hasn’t been processed yet.

With the SELECT, we’re using some locking hints:

• Readpast: Means we can skip over locked rows, but not ignore locks like NOLOCK would do
• Rowlock: We nicely ask the optimizer to only lock a single row
• Updlock: Even though this is a SELECT, take an UPDATE lock (LCK_M_U)

The UPDATE that occurs outside of the SELECT is pretty nifty, too. I tried to get OUTPUT to work here, but the variable assignment seemed to disagree with it.

A lot of people don’t know that you can assign a value to a variable like this, but anyway.

• Mark the row as being processed
• Get the processing start time
• Assign the table id to a parameter
• Assign the reputation we want to find to a parameter

Thirdly and thusly

DECLARE @sql NVARCHAR(MAX) = N'
SELECT COUNT_BIG(*) AS records
FROM dbo.Users AS u
WHERE u.Reputation = @i_reputation;
';

RAISERROR(@sql, 0, 1) WITH NOWAIT;
EXEC sys.sp_executesql @sql, N'@i_reputation int', @reputation;

We’ll pass the @reputation to some dynamic SQL and execute it. Like I mentioned before, this is just to keep the example brief. There’s absolutely no need to do this.

A fifth of fifthly

UPDATE fq
    SET fq.end_date = SYSDATETIME()
FROM dbo.four_queue AS fq
WHERE id = @id
AND   in_process = 1;

We’ll use the id parameter to update the end time, so we can find any processes that took a long time and look into them.

Out Of The Way

In real life, I’d probably want some error handling in here, but for a quick blog post example, this is good enough.

Now I can use a tool like SQL Query Stress to throw a bunch of workers at it and see if I run into any blocking or deadlocking.

We know there aren’t any deadlocks, because SQS doesn’t have any errors in it.

All ~20k rows get processed in about 26 seconds.

We can even use sp UNDERSCORE HumanEvents to look at wait stats for the duration.

EXEC sp_HumanEvents @event_type = 'waits', @seconds_sample = 30, @database_name = 'StackOverflow2013';

Not too shabby, eh?

CREATE OR ALTER PROCEDURE dbo.queue_four(@id INT, @reputation INT)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;

WHILE EXISTS
(
  SELECT 1/0
  FROM dbo.four_queue AS fq WITH(READPAST)
  WHERE fq.in_process = 0
)
    BEGIN
    
        WITH q4 AS
        (
            SELECT TOP (1) fq.*
            FROM dbo.four_queue AS fq WITH(READPAST, ROWLOCK, UPDLOCK)
            WHERE fq.in_process = 0
            ORDER BY fq.id
        )
        UPDATE q4
            SET q4.in_process = 1,
                q4.start_date = SYSDATETIME(),
                @id = q4.id,
                @reputation = q4.reputation
        FROM q4;
        
        DECLARE @sql NVARCHAR(MAX) = N'
        SELECT COUNT_BIG(*) AS records
        FROM dbo.Users AS u
        WHERE u.Reputation = @i_reputation;
        ';
        
        RAISERROR(@sql, 0, 1) WITH NOWAIT;
        EXEC sys.sp_executesql @sql, N'@i_reputation int', @reputation;
    
        UPDATE fq
            SET fq.end_date = SYSDATETIME()
        FROM dbo.four_queue AS fq
        WHERE id = @id
        AND   in_process = 1;
    
    END;

END;

Particular Needs

I’ve run into a few situations where developers needed to the queue table to act as a job scheduler of sorts, where certain applications may connect, look for work, and need to retry.

This would change the basic table design to look like this:

CREATE TABLE 
    dbo.four_queue
(
    id bigint IDENTITY NOT NULL,
    in_process bit NOT NULL
        DEFAULT 0,
    reputation int NOT NULL,
    scheduled_start_date datetime NULL
        DEFAULT GETDATE(),
    start_date datetime NULL
        DEFAULT GETDATE(),
    end_date datetime NULL
        DEFAULT GETDATE() + 0.00001,
    duration_milliseconds AS (DATEDIFF(MILLISECOND, start_date, end_date)),
    app_server sysname NULL,
    attempts tinyint NOT NULL
        DEFAULT 0,
    CONSTRAINT big_idea
        PRIMARY KEY CLUSTERED (in_process, id),
    INDEX fq_uq_r UNIQUE NONCLUSTERED (reputation)
        WITH (IGNORE_DUP_KEY = ON)
);

The way you index the table is going to depend on which columns you add and what your query logic ultimately ends up looking like, but you should keep the same priorities in mind: make it as easy as possible to seek to the rows you care about, and provide data in the order required to move things along quickly. Tables acting in this extended capacity tend to get a lot more rows added to them than a normal FIFO, FILO, LIFO, or FAFO queue table.

Handling Retries and Multiple App Servers

Our queue table has 3 columns that we haven’t used so far: scheduled_start_date, app_server, and attempts. If we make our fetch query a little more complicated by leveraging these columns, we can solve a few more typical queue challenges:

• An app server can pick up any of its own unfinished tasks again if it restarts
• Any available app server can pick up anybody’s unfinished task if it’s sat around for too long (like if the original app server is locked up, or permanently deleted)
• If a task has been retried 3 times, don’t try again

Here’s our new query to fetch the next available task. Note that it will need an @app_server_name parameter – pass in whatever app server is working on the task.

WITH 
    q4 AS
(
    SELECT TOP (1) 
        fq.*
    FROM dbo.four_queue AS fq 
        WITH(READPAST, ROWLOCK, UPDLOCK)
    WHERE fq.scheduled_start_date <= SYSDATETIME()
    AND 
    (
      /* pick up work no one has started yet */
      fq.in_process = 0
      
      /* pick up my own unfinished business */
      OR 
        (
          fq.in_process = 1 
          AND end_date IS NULL 
          AND app_server = @app_server_name 
          AND attempts < 3
        ) 
      
      /* pick up stuff from app servers that died */
      OR 
        (
          fq.in_process = 1 
          AND end_date IS NULL 
          AND start_date < DATEADD(MINUTE, -10, SYSDATETIME())) 
          AND attempts < 3 
    )	
    ORDER BY 
        fq.id
)
UPDATE 
   q4
SET 
    q4.in_process = 1,
    q4.start_date = SYSDATETIME(),
    q4.app_server_name = @app_server_name
    q4.attempts = q4.attempts + 1,
    @id = q4.id,
    @reputation = q4.reputation
FROM q4;

You may find for some use cases, you need separate queries, dynamic SQL, or a UNION ALL of the OR clause elements to get it to perform correctly and get the plan shape you’re after.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

How Long Must I Stay On This Stuff?

One thing about queues in databases: hey kinda suck. But with a little finesse, there are ways to make them function pretty well.

Before you ask about Service Broker: it’s usually overkill, and unnecessary XML in these scenarios.

When you’re building queues, there are lots of different things to consider.

• Do I want to reuse it?
• Can I just dequeue (delete) items?
• Do I need to process things in any order?
• Is there a “pending” element to the processing?

An example of a pending queue is in sp_AllNightLog, where there’s a list of databases that worker Agent jobs hit to take log backups repeatedly.

Four Queue

Let’s gin up a simple scenario, and say we wanna stick a list of unique Reputations from the Users table into another table, and then have a whole bunch of queries work off of it to do something.

The something in this case is getting a simple count, which is just to be pithy, and probably a bad use of a queue IRL.

Here’s what my ideal table would look like:

CREATE TABLE dbo.four_queue
(
    id BIGINT IDENTITY NOT NULL,
    in_process BIT NOT NULL DEFAULT 0,
    reputation INT NOT NULL,
    start_date DATETIME NULL DEFAULT GETDATE(),
    end_date DATETIME NULL DEFAULT GETDATE() + 0.00001,
    duration_milliseconds AS (DATEDIFF(MILLISECOND, start_date, end_date)),
    CONSTRAINT big_idea PRIMARY KEY CLUSTERED(in_process, id),
    INDEX fq_uq_r UNIQUE NONCLUSTERED (reputation) WITH(IGNORE_DUP_KEY = ON)
);

A little explanation of the table: this is good for a reusable, ordered queue, that’ll give us some feedback on how long things took. It could also be used if there were a pending element, but we’d probably wanna change the indexing so that we could find either the last start time, or the last end time efficiently.

The thing that probably needs the most explanation here is the indexing and constraints. Because I know you, and you’re staring at the way I have my primary key, and you’re getting an itch and a twitch. That’s okay, you’ve been conditioned for years to put the most selective column first. Unfortunately, that won’t work here.

It’ll make a little more sense in part 2 when we take a closer look at the queries, but the gist of it is that when we’re searching for rows to process (in_process = 0), we want to be able to find them quickly, which is harder to do when it’s the second column in the index and we’re not searching on the id column.

We’ve also got a fun constraint on there to make sure we only have unique values for Reputation, but we’re just going to silently throw away duplicates without throwing an error. This is there just in case someone tries to be sneaky and insert a row on their own to thwart our Perfect Queue Process™

Data Dumping

The cool part about the way the table is set up is that we don’t need to do anything special to get data into it.

INSERT dbo.four_queue ( reputation )
SELECT u.Reputation
FROM dbo.Users AS u;

Fair warning here, IGNORE_DUP_KEY does fun stuff to our identity column:

Since the identity doesn’t have any meaning beyond the order that rows were inserted in, it doesn’t matter that it’s not sequential. Order is order, even with gaps.

If you need it to be sequential for some in(s)ane reason, don’t use an identity and use ROW_NUMBER instead.

Totally Tabular

We’ve got a table, and we’ve got data in it.

In Part 2, we’ll look at designing a procedure to work off the queue.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

We All Hear What We Want To Hear

When Kendra Little (b|t) announced the #BESTWORSTCODE contest, I thought we’d be showing off bad and quirky code that we’d seen or written.

So I went digging through my personal archives, did a little updating, and proudly came up with the below examples.

It turns out that wasn’t at all what was going to happen! Kendra, of course, had much better ideas. And now I have the pubs database probably forever.

Anyway, presented without comment is what I was going to submit.

Thanks for reading!

/*We don't really have to name anything*/
DECLARE @ TABLE(_ INT DEFAULT 0);
INSERT @ (_)
DEFAULT VALUES;

/*Do we ever divide?*/
SELECT ROW_NUMBER() OVER(ORDER BY 1/0) AS [1/0]
FROM @ #@
WHERE EXISTS
(
    SELECT 1/0 AS [1/0]
    FROM @ AS ##@
    WHERE #@._ = ##@._
);


/* Highly illogical. ??? */
IF (1 + 2 = 4)
    BEGIN
        DECLARE @ TINYINT;
    END;
SELECT @ = 1;
SELECT @ AS [?];
GO

IF (2 + 1 = 4)
    BEGIN
        DECLARE @ TINYINT = 1/0;
    END;
SELECT @ AS [?];
GO


/*Conventional Naming.*/
DROP TABLE IF EXISTS #;

CREATE TABLE # (int INT, INDEX xml CLUSTERED (int));
INSERT # ( int )
SELECT varbinary.sysname
FROM 
(
    SELECT TOP (1000 * 1000) 1e0 AS sysname
    FROM sys.messages AS smallmoney
    CROSS JOIN sys.messages AS money
) AS varbinary;


/*This is fine.*/
SELECT TOP (2-1) sql_variant.int
FROM # AS sql_variant;

/*This is also fine.*/
ALTER TABLE # ADD real AS int / 0e0;

/*How many errors should I get?*/
SELECT SUM(ROWCOUNT_BIG()) AS float
FROM # AS sql_variant
GROUP BY ();


/*Oh, now you care.*/
CREATE INDEX columnstore ON #(real);

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.