Blog - Brent Ozar

Execution Plans, Locking, Blocking, and Isolation Levels

Legendreary

In the last post, I looked at some issues with implementing foreign keys with cascading actions. Namely that, well, it fell apart pretty quickly just trying to set up.

I didn’t even get to the point where I wanted to test all those relationships.

But there’s an even worse surprise waiting for you if you do implement foreign keys with cascading actions.

Locking From Hell

Let’s pretend that we gave up on stuff getting deleted from other tables if we canned a post. Let’s just get things set up so that if a user bids us adieu, we’ll get rid of their badges, comments, and posts. It’s, like, GDPR, or something.

ALTER TABLE dbo.Badges 
    ADD CONSTRAINT fk_badges_users_id FOREIGN KEY (UserId) 
        REFERENCES dbo.Users(Id) ON DELETE CASCADE;


ALTER TABLE dbo.Comments
    ADD CONSTRAINT fk_comments_users_id FOREIGN KEY (UserId)
        REFERENCES dbo.Users(Id) ON DELETE CASCADE;


ALTER TABLE dbo.Posts
    ADD CONSTRAINT fk_posts_users_id FOREIGN KEY (OwnerUserId)
        REFERENCES dbo.Users (Id) ON DELETE CASCADE;

ALTER TABLE dbo.Badges

ADD CONSTRAINT fk_badges_users_id FOREIGN KEY (UserId)

REFERENCES dbo.Users(Id) ON DELETE CASCADE;

ALTER TABLE dbo.Comments

ADD CONSTRAINT fk_comments_users_id FOREIGN KEY (UserId)

REFERENCES dbo.Users(Id) ON DELETE CASCADE;

ALTER TABLE dbo.Posts

ADD CONSTRAINT fk_posts_users_id FOREIGN KEY (OwnerUserId)

REFERENCES dbo.Users (Id) ON DELETE CASCADE;

These all get added without a problem.

What if we delete some data?

Harken Back, Ye Merry Reader

I’m gonna use some code from a previous post about locks taken during indexed view creation.

SELECT    dtl.request_mode,
          CASE dtl.resource_type 
               WHEN 'OBJECT' 
		       THEN OBJECT_NAME(dtl.resource_associated_entity_id) 
			   ELSE OBJECT_NAME(p.object_id) 
		  END AS locked_object,
          dtl.resource_type,
          COUNT_BIG(*) AS total_locks
FROM      sys.dm_tran_locks AS dtl
LEFT JOIN sys.partitions AS p
    ON p.hobt_id = dtl.resource_associated_entity_id
WHERE     1=1
--AND       dtl.request_session_id = @@SPID
AND       dtl.resource_type <> 'DATABASE'
GROUP BY  CASE dtl.resource_type 
               WHEN 'OBJECT' 
		       THEN OBJECT_NAME(dtl.resource_associated_entity_id) 
			   ELSE OBJECT_NAME(p.object_id) 
		  END, 
		  dtl.resource_type, 
		  dtl.request_mode;

SELECT dtl.request_mode,

CASE dtl.resource_type

WHEN 'OBJECT'

THEN OBJECT_NAME(dtl.resource_associated_entity_id)

ELSE OBJECT_NAME(p.object_id)

END AS locked_object,

dtl.resource_type,

COUNT_BIG(*) AS total_locks

FROM sys.dm_tran_locks AS dtl

LEFT JOIN sys.partitions AS p

ON p.hobt_id = dtl.resource_associated_entity_id

WHERE 1=1

--AND dtl.request_session_id = @@SPID

AND dtl.resource_type <> 'DATABASE'

GROUP BY CASE dtl.resource_type

WHEN 'OBJECT'

THEN OBJECT_NAME(dtl.resource_associated_entity_id)

ELSE OBJECT_NAME(p.object_id)

END,

dtl.resource_type,

dtl.request_mode;

When we run a delete for a single user, we log a Whole Mess® of locks.

What comes back is pretty nasty.

My Own Little Nasty World

If you’ve ever watched locks for modification queries (and who doesn’t spend weekend doing that, really?), most of these will look normal to you.

These locks change based on how many rows we need to get rid of.

For example, User Id 1 is hardly anything, but for the rows we need to delete, we take serializable locks – RANGEX-X.

Seriously Serializable

This type of lock upgrade may come as a surprise to you, but this is how SQL Server guarantees that whole referential integrity thing so that it can trust the foreign keys you’ve got on there. Lucky you!

Let’s think about a perfect storm, where…

You’ve got lots of cascading actions firing off (we don’t)
Your foreign keys aren’t indexed well (ours are, we’ll look at the query plan in a minute)
You need to cascade down large amounts of data (this only hit about 400 rows at most)
Maybe there’s a begin tran or something in there for good measure (I did that to get the locking info, ha ha ha)

You could end up with really bad locking if any one or two of those things is true.

Let’s say we need to delete Jon Skeet. Because he’s a wonderful piece of outlier data.

SQL Server is all like “object locks immediately.” And, yeah, X locks on an object (table) are a lot like serializable locks on a range, just, you know, bigger.

BIG HUGS

You Could Learn A Lot From A Query Plan

While query plans don’t show us locking per se, they will show us needing to go out to each table that references Users.

I’ll make you a believer

And we can see a delete run on all three referencing tables for both the clustered index, and the nonclustered indexes we have to support the foreign keys.

But hey, at least I got some index seeks. My Friend Forrest® didn’t have as much luck.

Lining Bottoms

Be really careful with how you set up foreign keys. If you have cascading actions on them, you could be causing yourself a whole lot of locking headaches.

We do warn about these in sp_BlitzIndex, if you’re curious about the state of your database.

Next up, we’ll look at different tactics for indexing foreign keys.

We’re going to stay far away from cascading actions, though.

Thanks for reading!

Adventures In Foreign Keys 2: Common Foreign Key Errors

November 13, 2018

Execution Plans

This week, we’re all about foreign keys. Yesterday’s post covered scripts to set up the Stack Overflow database to get ready, eliminating data that would violate FK relationships.

You Had Two Jobs!

Let’s say I wanted to implement Foreign Keys to do two things

If a user deletes their account, all of their badges, comments, and posts will also get deleted
If a user deletes their post, all of the comments and votes will also get deleted

Remember that the data dump is cleaned so user voting isn’t recorded, so we’ll steer clear of that. I also have no idea how to simply implement the removal of badges someone may have gotten from an associated post, so I’m leaving that out. The badge stays in the picture!

For the post and vote deletion — in real life, this would also be really complicated. We’d have to adjust user reputation, and info we maintain about how many up and down votes the user has cast. We’d also have to restore reputation to users if an answer they downvoted was deleted. This is well beyond what foreign keys can do well, or easily. That’s implementing business logic, which would be a better fit for a trigger, if we’re sticking to doing it in the database.

What can foreign keys do for us, then?

Foreign Keys offer you the option to have updates and deletes cascade from the parent (reference) table to the child table. We’ll look at how exactly what works in the next post, but for now let’s just try to get the foreign keys set up.

Issues, Tissues

In order to make testing this easier, I added some code to our stored procedure that drops indexes.

Now it can also drop foreign key, unique, and check constraints.

You can thank me later.

First, let’s get badges deleted if a user self-immolates.

ALTER TABLE dbo.Badges 
    ADD CONSTRAINT fk_badges_users_id FOREIGN KEY (UserId) 
        REFERENCES dbo.Users(Id) ON DELETE CASCADE;

ALTER TABLE dbo.Badges

ADD CONSTRAINT fk_badges_users_id FOREIGN KEY (UserId)

REFERENCES dbo.Users(Id) ON DELETE CASCADE;

Everything looks good so far! Let’s get comments deleted, too.

ALTER TABLE dbo.Comments
    ADD CONSTRAINT fk_comments_users_id FOREIGN KEY (UserId)
        REFERENCES dbo.Users(Id) ON DELETE CASCADE;


ALTER TABLE dbo.Comments
    ADD CONSTRAINT fk_comments_posts_id FOREIGN KEY (PostId)
        REFERENCES dbo.Posts(Id) ON DELETE CASCADE;

ALTER TABLE dbo.Comments

ADD CONSTRAINT fk_comments_users_id FOREIGN KEY (UserId)

REFERENCES dbo.Users(Id) ON DELETE CASCADE;

ALTER TABLE dbo.Comments

ADD CONSTRAINT fk_comments_posts_id FOREIGN KEY (PostId)

REFERENCES dbo.Posts(Id) ON DELETE CASCADE;

Also no problem. Okay, now let’s get votes deleted if a post goes away.

ALTER TABLE dbo.Posts
    ADD CONSTRAINT fk_posts_users_id FOREIGN KEY (OwnerUserId)
        REFERENCES dbo.Users (Id) ON DELETE CASCADE;

ALTER TABLE dbo.Posts

ADD CONSTRAINT fk_posts_users_id FOREIGN KEY (OwnerUserId)

REFERENCES dbo.Users (Id) ON DELETE CASCADE;

Alright, now let’s get posts deleted if a user cashes out.

ALTER TABLE dbo.Posts
    ADD CONSTRAINT fk_posts_users_id FOREIGN KEY (OwnerUserId)
        REFERENCES dbo.Users (Id) ON DELETE CASCADE;

ALTER TABLE dbo.Posts

ADD CONSTRAINT fk_posts_users_id FOREIGN KEY (OwnerUserId)

REFERENCES dbo.Users (Id) ON DELETE CASCADE;

This goes less well.

Msg 1785, Level 16, State 0, Line 190
Introducing FOREIGN KEY constraint 'fk_posts_users_id' on table 'Posts' may cause cycles or multiple cascade paths. 
Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 1, Line 190
Could not create constraint or index. See previous errors.

Msg 1785, Level 16, State 0, Line 190

Introducing FOREIGN KEY constraint 'fk_posts_users_id' on table 'Posts' may cause cycles or multiple cascade paths.

Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Msg 1750, Level 16, State 1, Line 190

Could not create constraint or index. See previous errors.

Orderly

If you’ve ever hit this error, you’ve no doubt landed on The Least Helpful Page On The Internet®

If you haven’t, I’ll summarize it for you: DON’T DO WHAT YOU’RE TRYING TO DO

Now, when I was testing this, I found that I’d hit that error in different places if I created things in a different order.

As a for-instance, if I start from the bottom up, I hit it trying to add a constraint from comments to posts.

Msg 1785, Level 16, State 0, Line 143
Introducing FOREIGN KEY constraint 'fk_comments_posts_id' on table 'Comments' may cause cycles or multiple cascade paths. 
Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 1, Line 143
Could not create constraint or index. See previous errors.

Msg 1785, Level 16, State 0, Line 143

Introducing FOREIGN KEY constraint 'fk_comments_posts_id' on table 'Comments' may cause cycles or multiple cascade paths.

Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Msg 1750, Level 16, State 1, Line 143

Could not create constraint or index. See previous errors.

It seems that I don’t have a straightforward way of getting both things to happen. I could try chaining things differently…

ALTER TABLE dbo.Comments
    ADD CONSTRAINT fk_comments_posts_id FOREIGN KEY (PostId)
        REFERENCES dbo.Votes(PostId) ON DELETE CASCADE;

ALTER TABLE dbo.Comments

ADD CONSTRAINT fk_comments_posts_id FOREIGN KEY (PostId)

REFERENCES dbo.Votes(PostId) ON DELETE CASCADE;

But the PostId column in Votes isn’t unique — the only truly unique columns in Stack tables are the Id columns. I’ll get this error.

Msg 1776, Level 16, State 0, Line 155
There are no primary or candidate keys in the referenced table 'dbo.Votes' 
that match the referencing column list in the foreign key 'fk_comments_posts_id'.
Msg 1750, Level 16, State 1, Line 155
Could not create constraint or index. See previous errors.

Msg 1776, Level 16, State 0, Line 155

There are no primary or candidate keys in the referenced table 'dbo.Votes'

that match the referencing column list in the foreign key 'fk_comments_posts_id'.

Msg 1750, Level 16, State 1, Line 155

Could not create constraint or index. See previous errors.

I can’t create a unique index or constraint just on PostId anywhere, and even the combination of PostId and other columns can be non-unique.

I can make a unique index on UserId and Id, though.

CREATE UNIQUE NONCLUSTERED INDEX ix_ennui ON dbo.Votes (PostId, Id)

1	CREATE UNIQUE NONCLUSTERED INDEX ix_ennui ON dbo.Votes (PostId, Id)

But now I’m dealing with a multi-key foreign key, which’ll have some drawbacks that we’ll explore in a later post.

ALTER TABLE dbo.Comments
    ADD CONSTRAINT fk_comments_posts_id FOREIGN KEY (PostId, Id)
        REFERENCES dbo.Votes(PostId, Id) ON DELETE CASCADE;

ALTER TABLE dbo.Comments

ADD CONSTRAINT fk_comments_posts_id FOREIGN KEY (PostId, Id)

REFERENCES dbo.Votes(PostId, Id) ON DELETE CASCADE;

More locally, we’ll run into this error:

Msg 1788, Level 16, State 0, Line 180
Cascading foreign key 'fk_comments_posts_id' cannot be created 
where the referencing column 'Comments.Id' is an identity column.
Msg 1750, Level 16, State 1, Line 180
Could not create constraint or index. See previous errors.

Msg 1788, Level 16, State 0, Line 180

Cascading foreign key 'fk_comments_posts_id' cannot be created

where the referencing column 'Comments.Id' is an identity column.

Msg 1750, Level 16, State 1, Line 180

Could not create constraint or index. See previous errors.

No foreign keys allowed on identity columns.

You Ain’t Got No Alibi

By now, we’re starting to ask ourselves if these cascading actions are worth it. If we don’t implement them, we can have referential integrity out the wazoo. And those trigger thingamajigs are starting to look mighty attractive. They don’t have all these “rules” to deal with, and we can offload the deletions there. Heck, if we’re using a stored procedure, we don’t even need triggers. We can just fire off deletes to any tables we care about from there.

Foreign Keys with cascading actions: Strike One. In the next post, we’ll learn why I’d give them a lifetime ban from baseball. Thanks for reading!

Adventures In Foreign Keys 1: Setting Up Foreign Keys in Stack Overflow

November 12, 2018

Brent Ozar Unlimited Team

Execution Plans

In A Foreign Key, In A Foreign Table

Much of what people want from foreign keys, like referential integrity and join elimination, are only as guaranteed as much as SQL Server can trust your constraints (and even then…). The same goes for check constraints, too.

Thankfully, things like Primary Keys and Unique Constraints are sort of self-trusting, in that there’s really no way to add them without checking the underlying data. Sure, you can ignore dupe keys (if SQLblog is down again, try the Wayback Machine version), but that’s a different sort of trick.

This week, I’ve got 5 posts about foreign keys to look at various aspects of implementing them, and their behavior once they’re in place. If you think I missed something, or didn’t adequately cover it, hang tight. It’ll probably show up eventually.

Pretty much maybe.

Bookworm

When you read books about relational design — Louis Davidson has an excellent one — the importance of things like this are emphasized quite a bit. If you’re starting an app from scratch, you’d be wise to heed the advice in them, especially if it’s an OLTP app. If you take care of it here, you don’t have to worry about enforcing it in your data warehouse.

If you ever want to feel poor design pain, try adding, enforcing, or loading large amounts of data into tables with foreign keys. While you do that, start learning a new language.

But what if you’ve got a database that’s been around for a while. Can you trust the data? Can you trust that no developer ever removed or disabled a constraint to “fix a problem” or “add some custom data” and then forgot to turn them back on for a week or two?

Didn’t think so.

Keying Time Had Come

Continuing to use Stack Overflow as a demo data set, let’s look at if the newest dump is foreign key-able.

Before we get started, I just want to mention that I don’t expect it to be. As far as I can tell, if a user deletes their account, their actions are mostly preserved.

In order to test our data, we’d be wise to create some indexes on important columns.

CREATE INDEX ix_whatever ON dbo.Badges(UserId);
CREATE INDEX ix_whatever ON dbo.Comments(UserId);
CREATE INDEX ix_apathy ON dbo.Comments(PostId);
CREATE INDEX ix_whatever ON dbo.Posts(OwnerUserId);
CREATE INDEX ix_whatever ON dbo.Votes(UserId);
CREATE INDEX ix_apathy ON dbo.Votes(PostId);

CREATE INDEX ix_whatever ON dbo.Badges(UserId);

CREATE INDEX ix_whatever ON dbo.Comments(UserId);

CREATE INDEX ix_apathy ON dbo.Comments(PostId);

CREATE INDEX ix_whatever ON dbo.Posts(OwnerUserId);

CREATE INDEX ix_whatever ON dbo.Votes(UserId);

CREATE INDEX ix_apathy ON dbo.Votes(PostId);

Next, let’s see what kind of nonsense we’ve got to clean up. We’re going to run some selects to get a count of rows in our “action” tables (Badges, Comments, Posts, Votes) to see which ones have orphaned Users or Posts. Since it’s long and repetitive, I’m going to put these at the end of the post. For now, let’s just look at the results.

/*
Badges With No User
----------
3

Comments With No User
----------
337,603

Comments With No Post
----------
5

Posts With No User
----------
227,634

Votes With No User
----------
49,346,043

Votes With No Post
----------
8,109,424
*/

Badges With No User

----------

Comments With No User

----------

337,603

Comments With No Post

----------

Posts With No User

----------

227,634

Votes With No User

----------

49,346,043

Votes With No Post

----------

8,109,424

Votes with no User sticks out, because that data is cleaned from the dump. If they published how people voted, it would be kind of terrible. Brent would find out what I really think about his Answers.

The rest are… Odd. I’m really not sure what Stack Overflow does to remove User or Post actions when they’ve been deleted.

IRL

You’ll probably know what processes do (or don’t do) this kind of clean up work, which might make deciding what to do with dirty data easier.

You have a few options that I can think of quickly:

Delete it
Associate it with dummy Ids
Move it to separate tables for reference

I’ll give you $100 if you guess which one I picked before I tell you that I deleted it. The delete scripts are at the end, too.

You were so close.

Deciding what to do with it (or if you’re going to do anything with it at all) is a business decision. It’s their data. If they decide to follow you on your crazy quest to implement referential integrity, it’s your job as a data person to work with them on the best way to end up with data that complies with your Utopian vision. If they want to keep it in some form, you have to respect that.

If they’re cool with deleting it, well..

Be Kind, Batchwind

Just read this post from Michael J. Swart about batching modifications.

Excellent Adventures

I’m going to do some more of these posts, and talk about the difficulties with implementing foreign keys, cascading actions, indexing, and join elimination.

Thanks for reading!

Scripts

Checking for bad data:

/*Badges without a User*/
SELECT FORMAT(COUNT(*), 'N0') AS BNU
FROM dbo.Badges AS b
WHERE NOT EXISTS
(
    SELECT 1/0
    FROM dbo.Users AS u
    WHERE b.UserId = u.Id
);

/*Comments without a User*/
SELECT FORMAT(COUNT(*), 'N0') CNU
FROM dbo.Comments AS c
WHERE NOT EXISTS 
(
    SELECT 1/0
    FROM dbo.Users AS u
    WHERE c.UserId = u.Id
);

/*Comments without a Post*/
SELECT FORMAT(COUNT(*), 'N0') CNP
FROM dbo.Comments AS c
WHERE NOT EXISTS 
(
    SELECT 1/0
    FROM dbo.Posts AS p
    WHERE c.PostId = p.Id
);

/*Posts without a User*/
SELECT FORMAT(COUNT(*), 'N0') PNU
FROM dbo.Posts AS p
WHERE NOT EXISTS 
(
    SELECT 1/0
    FROM dbo.Users AS u
    WHERE p.OwnerUserId = u.Id
);

/*Votes without a User*/
/*This is no good, because it gets cleaned...*/
SELECT FORMAT(COUNT(*), 'N0') VNU
FROM dbo.Votes AS v
WHERE NOT EXISTS
(
    SELECT 1/0
    FROM dbo.Users AS u
    WHERE v.UserId = u.Id
)
--AND v.UserId IS NOT NULL; -- Toggle, big difference

/*Votes without a Post*/
SELECT FORMAT(COUNT(*), 'N0') VNP
FROM dbo.Votes AS v
WHERE NOT EXISTS
(
    SELECT 1/0
    FROM dbo.Posts AS p
    WHERE v.PostId = p.Id
);

/*Badges without a User*/

SELECT FORMAT(COUNT(*), 'N0') AS BNU

FROM dbo.Badges AS b

WHERE NOT EXISTS

(

SELECT 1/0

FROM dbo.Users AS u

WHERE b.UserId = u.Id

);

/*Comments without a User*/

SELECT FORMAT(COUNT(*), 'N0') CNU

FROM dbo.Comments AS c

WHERE NOT EXISTS

(

SELECT 1/0

FROM dbo.Users AS u

WHERE c.UserId = u.Id

);

/*Comments without a Post*/

SELECT FORMAT(COUNT(*), 'N0') CNP

FROM dbo.Comments AS c

WHERE NOT EXISTS

(

SELECT 1/0

FROM dbo.Posts AS p

WHERE c.PostId = p.Id

);

/*Posts without a User*/

SELECT FORMAT(COUNT(*), 'N0') PNU

FROM dbo.Posts AS p

WHERE NOT EXISTS

(

SELECT 1/0

FROM dbo.Users AS u

WHERE p.OwnerUserId = u.Id

);

/*Votes without a User*/

/*This is no good, because it gets cleaned...*/

SELECT FORMAT(COUNT(*), 'N0') VNU

FROM dbo.Votes AS v

WHERE NOT EXISTS

(

SELECT 1/0

FROM dbo.Users AS u

WHERE v.UserId = u.Id

)

--AND v.UserId IS NOT NULL; -- Toggle, big difference

/*Votes without a Post*/

SELECT FORMAT(COUNT(*), 'N0') VNP

FROM dbo.Votes AS v

WHERE NOT EXISTS

(

SELECT 1/0

FROM dbo.Posts AS p

WHERE v.PostId = p.Id

);

Deleting bad data:

/*Badges without a User*/
DELETE b
FROM dbo.Badges AS b
WHERE NOT EXISTS
(
    SELECT 1/0
    FROM dbo.Users AS u
    WHERE b.UserId = u.Id
);


/*Comments without a User*/
DELETE c
FROM dbo.Comments AS c
WHERE NOT EXISTS 
(
    SELECT 1/0
    FROM dbo.Users AS u
    WHERE c.UserId = u.Id
);



/*Comments without a Post*/
DELETE c
FROM dbo.Comments AS c
WHERE NOT EXISTS 
(
    SELECT 1/0
    FROM dbo.Posts AS p
    WHERE c.PostId = p.Id
);



/*Posts without a User*/
DELETE p
FROM dbo.Posts AS p
WHERE NOT EXISTS 
(
    SELECT 1/0
    FROM dbo.Users AS u
    WHERE p.OwnerUserId = u.Id
);

/*Votes without a Post*/
DELETE v
FROM dbo.Votes AS v
WHERE NOT EXISTS
(
    SELECT 1/0
    FROM dbo.Posts AS p
    WHERE v.PostId = p.Id
);

/*Badges without a User*/

DELETE b

FROM dbo.Badges AS b

WHERE NOT EXISTS

(

SELECT 1/0

FROM dbo.Users AS u

WHERE b.UserId = u.Id

);

/*Comments without a User*/

DELETE c

FROM dbo.Comments AS c

WHERE NOT EXISTS

(

SELECT 1/0

FROM dbo.Users AS u

WHERE c.UserId = u.Id

);

/*Comments without a Post*/

DELETE c

FROM dbo.Comments AS c

WHERE NOT EXISTS

(

SELECT 1/0

FROM dbo.Posts AS p

WHERE c.PostId = p.Id

);

/*Posts without a User*/

DELETE p

FROM dbo.Posts AS p

WHERE NOT EXISTS

(

SELECT 1/0

FROM dbo.Users AS u

WHERE p.OwnerUserId = u.Id

);

/*Votes without a Post*/

DELETE v

FROM dbo.Votes AS v

WHERE NOT EXISTS

(

SELECT 1/0

FROM dbo.Posts AS p

WHERE v.PostId = p.Id

);

[Video] Office Hours 2018/11/7 (With Transcriptions)

November 11, 2018

Videos

Here’s the video on YouTube:

This week, Tara and Richie discuss offline file database and log backup solutions, VSS database backups, monitoring products, parameterization, page latch waits, SQL ConstantCare™, and Availability Groups on 2017 Standard Edition.

You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go.

If you prefer to listen to the audio:

Podcast: Play in new window | Download

Enjoy the Podcast?

Don’t miss an episode, subscribe via iTunes, Stitcher or RSS.
Leave us a review in iTunes

Office Hours 11-7-2018

Should I use VSS snapshot backups?

Tara Kizer: Alright, Gary has a question, “What are your thoughts on offline file database and log backup solutions versus VSS snapshot backups? We’ve used Redgate for years. It works great and it’s my preference. But our company switched to StorageCraft VSS snapshot backups based on our MSD’s recommendation. VSS solutions seem to be difficult to manage with the mounting of the drives to get to the MDF and LDF file.”

I am not familiar with StorageCraft. I don’t care how your full backups are happening, especially on larger databases. It’s very challenging to do SQL Native backups or Redgate backups, or any of those types of software, because of how long they take. So I do like snapshots, like a VM snapshot or a SAN snapshot. I don’t know if StorageCraft is doing that. but you have to ensure that it’s freezing the I/O for your database files so that your data files and log files are at the same point in time, you know, it’s quiescing them. And so you’ll see those messages in the SQL Server error log. SQL Server will notice I/O is being frozen. Then you’ll see resume messages. And that’s what you want to see if these are good backups. Otherwise, they’re – I forget what they’re called but basically dirty backups that you may not be able to recover from. So you have to freeze I/O and resume it with the solution that you picked. I just forget what that’s called.

Log backups, I don’t know. If I’m a DBA – I don’t know if Gary’s a DBA or a sysadmin, but if I’m a DBA, I’m going to have control over those differentials and log backups – log backups at least. I need to be able to restore quickly. That’s one of my jobs in the case of if an issue happens. Sysadmins I can see wanting to not use SQL Server technology, so I don’t know. It just depends on the situation. But the full backups, I can see why people need to use other solutions if you’ve got large databases.

A database mail job has been rolling back for days…

Tara Kizer: Alright, Christopher asks, “We have a 2012 Availability Group and a database mail job was run and took over 24 hours before someone killed it. It’s been in rollback since Tuesday with wait type blah, blah, blah… According to our database tools, which is SolarWinds, it doesn’t seem to be locking or blocking.”

I’ve never seen this situation. I would recommend that you post the question out on Stack Exchange, dba.stackexchange.com – and someone may have encountered the issue. It doesn’t seem to be locking or blocking. If it’s not causing a problem, just leave it there. Are you able to still use database mail for whatever you’re using it for? And just wait for a restart to occur down the road, and hopefully you’re doing patching every now and then so the restarts aren’t that far off.

Which recovery model should I use?

Tara Kizer: Alright, is this a different Gary? Same Gary, alright, “Secondary question regarding VSS database backups – should the database be in full or simple recovery model…” Gary, I love it, you said recovery model instead of recovery mode, “Since there’s no log backups occurring? I assume the point in time recover comes from mounting the drive…”

So, if you are using full or bulk log recover models, you are required to do log backups. And if nobody’s doing that, you’re going to, eventually, blow out – the transaction log is going to grow and grow and grow and fill up that drive. It just depends if you need point in time recovery or not. Is it okay if you restore to the last full backup or differential? If it is, then go ahead and use simple recovery model. If it’s not okay, you must use full or bulk log, and I’ve never even used bulk log because point in time recovery is so critical and you lose recovery points with bulk log. So full, and then do log backups per your RPO level, your data-loss. So it’s very common these days to see log backup jobs running every five minutes. Sometimes they’re even every minute. You must add log backups through whatever tool you want to use for that.

Can you explain the offside rule in soccer?

Tara Kizer: Joseph says, “Can you explain the offside rule in soccer?”

Richie Rump: Yeah, actually I could. I could really do that.

Tara Kizer: Even our kids could.

Richie Rump: Since Brent’s not here maybe I should probably do it. It’s really simple. The offensive player, at the point of a pass, needs to be equal to or in front of the last two defenders. That’s it.

Tara Kizer: Last two defenders or last defender?

Richie Rump: Last two defenders, because the goalie’s included in that and sometimes the goalie is up. So it’s got to be the last two defenders.

Tara Kizer: I was like, wait a minute, does Florida do it different?

Richie Rump: No, and that’s why I say the last two defenders. It’s usually just the one because the goalie’s behind, but if the goalie has come up for some reason, then it’s the – so the rules say the last two defenders. There you go.

Which monitoring tool do you prefer?

Tara Kizer: Alright, Joseph asks, “What’s your latest preference on a monitoring product? Different products seem to do different things better than others. What do you like currently and why?”

A lot of the monitoring tools are really great. They’re expensive for a reason, and they just leapfrog each other as far as features. One product will have a new release and add a new feature, and then another competing product will get that feature, plus they’ll have other new features. So they leapfrog each other. I’ve used Sentry One’s Performance Advisor and Quest’s Spotlight and Foglight tools in the past. We’ve got clients using IDERA Diagnostics Manager and they seem to be happy. Some people are using SolarWinds. That doesn’t seem to provide the level of features of those other three companies that I mentioned. Some people are using New Relic. I’d say it’s all over the place and we don’t really say, here’s the products we recommend. You guys see which product provides you the monitoring for that system.

Richie Rump: Yeah, and the tools we provide are not monitoring either, so just to throw that out there.

Should I use forced parameterization or optimize for ad hoc?

Tara Kizer: Ron asks a good question, “What are your thoughts on parameterization simple force and optimize for ad hoc workloads?” So I like optimize for ad how workloads to be enabled just as a best practice. Just get that out of the way. There’s only like one edge case where it may not work for you, and that’s if you always had two execution plans. So I enable it, basically, anytime I install SQL Server on a production box. The parameterization I like also, but there’s a downside to it.

So let me describe what forced parameterization is. So, the default for databases is simple parameterization. Any ad hoc queries that are coming in, you’re going to get ad hoc query plans and very little plan reuse of those for-ad hoc queries. They’d have to be an exact match, even for the values being passed to your search predicates; your where clause join conditions. So where column one equals 10, where column one equals two, those would generate a different execution plan if they were ad hoc.

Now, forced parameterization was introduced, I think, SQL 2012, maybe 2008. It’s not that old of a feature. You can enable that at the database level for SQL Server to convert your ad hoc queries to be parameterized. But there are some limitations to it. There’s an MSDN article that you can read about the limitations, but there’s a downside to it. And the downside is, now those ad hoc queries are vulnerable to parameter sniffing, which they were not before because it was optimizing each time, optimizing and compiling an execution plan each time it needed to create a new query plan. But now, you’re going to be reusing query plans, just like you would with parameterized queries, such a stored procedures, prepared statements, things like that, so that they are now vulnerable to parameter sniffing.

I’ve had a client who had over 90% CPU utilization basically at all times during their busy hours, and they had ad hoc query plan issues. I mean, just thousands and thousands and thousands of ad hoc query plans. They had high compilations per second and we enabled forced parameterization on the fly during the day, which we usually don’t do as part of our critical care. Very occasionally we’ll make a change because this one is low risk. And immediately, their CPU utilization dropped from 90% down to 60%. Now, there were other changes that they could make to the system, but that gave them some breathing room. They no longer had the extreme CPU pressure.

How do I fix PAGEIOLATCH waits?

Tara Kizer: Alright, Daryl says, “According to sp_BlitzFirst, our server is experiencing page I/O latch waits due to slow reads. If our VMware host and SSD SAN is not reporting any problems, what else should I be looking at?” Based upon my experience, the problem is not always at the disk level or somewhere in between. It can be on the Windows box itself. And so one particular instance where we had severe I/O issues, I opened a case with Microsoft, the SAN team opened up a case with their vendor, and we even had another vendor; I think HP was also involved. Microsoft had us update the I/O subsystem on Windows, the firmware drivers and bios and [store port] file. Anything I/O related on the Windows box itself got updated and that resolved the performance problem.

Microsoft, if you open a case with them, that may be where they tell you to start. Now, this was about 10 years ago, the support case with them. But you may want to start there because it can be an easy win, and that may be what Microsoft tells you to start with first. They can run tools to help you diagnose where the problem is. I don’t know if they’ve renamed the tool, but there used to be a tool called ETW Trace that you can run with the help of them to analyze that data. And it will tell you, is the I/O problem on the server itself or external to the server. And SSD SAN, you know, that doesn’t give you the best performance out of SSDs, you know, there’s bottlenecks there. And you are asking about a VMware virtualized system, so you update the I/O subsystem on the guest, the virtual machine itself, and the host. There’s two layers there, so making sure everything’s up to date, firmware, drivers, and bios. I’m actually working with a client this week that is experiencing that same alert in sp_BlitzFirst and I’m telling them to start with the I/O subsystem on the Windows box and the host server.

Tara Kizer: Steve says, “Richie, thanks for the Python recommendation a few weeks ago, it’s great.” Alright.

Richie Rump: He’ll love it.

How is SQL ConstantCare licensed?

Tara Kizer: Christopher asks, “Is SQL ConstantCare licensed per user or per SQL Server? I’ve several SQL Servers and only one of me.” I’ll let Richie answer that. Constant care is his baby.

Richie Rump: Per user – we get lots of servers from people and they send in all their stuff and we send them all recommendations every day.

Tara Kizer: And I think that Brent hasn’t set a limit on the number of servers yet, but he may in the future.

Richie Rump: I don’t think so yet. He does a lot of the business stuff and he says, you go make this work, and then I just go off and…

Tara Kizer: Exactly, he does that type of stuff, or makes those rules.

Richie Rump: Yeah, and then I’m like, I don’t know, wait, yeah, I could do that, yeah. A week or two weeks later, it comes back.

Tara Kizer: Yeah, and I mean, if there’s no limit, if you’ve got a lot of SQL Servers, sign up. It’s a very cost effective solution to help you with your environment. When I worked at Qualcomm many years ago, not that many years ago, but we had 700 SQL Servers that we supported. There were several DBAs to support that, but I could have signed up for an account to ConstantCare and got mentoring advice on all 700 servers. Not that I’d want to…

Richie Rump: 700, [crosstalk] conversation there…

Tara Kizer: Well, that’s when Brent was like, maybe we will restrict it…

Richie Rump: Yeah, I think at that point, we’re like, well…

Tara Kizer: Yeah, I think … I don’t think anything that high…

Richie Rump: No, no, no.

Are Basic Availability Groups worth it?

Tara Kizer: Gary says, “Availability Groups on 2017 Standard Edition seems like a pain. Given the offline status of the secondaries and the one database per group limitation, is it worth it?” I mean, it’s worth it – so we’re talking about basic Availability Groups, since this is Standard Edition. Sure, there’s some pains, but what’s your alternative for whatever you’re using the Availability Group for? So it’s a solution for HA or disaster recovery – both of those are available with basic Availability Groups, whereas database mirroring in order to get the DR support with an async mirror, that was an Enterprise Edition feature.

Basic Availability Groups gives you synchronous and asynchronous. So it is a solution for HA and DR. it is one database per group and you don’t get to use a readable secondary, but hardly anybody, out of the people who are using Availability Groups, regardless if it’s Standard or Enterprise, most people are not using the readable secondary replica, at least from what we’ve seen with our clients. Some people are, for sure, but not a lot. Most people are using it for HADR.

Alright, that’s all of the questions, so we’re going to end here. If you had any questions and just didn’t realize that we would run out of your questions, next week, get your questions in earlier. So we are out of questions. Have a good one.

Richie Rump: And it’s lunchtime.

PASS Summit 2018 Keynote Day 2: Live Blog

November 8, 2018

#SQLPass

Today is the second day of the 2018 PASS Summit, the largest annual gathering of Microsoft data professionals. Yesterday, we got the marketing keynote that caught attendees up with the current state of SQL Server 2019 and Azure services. The attendees I talked to yesterday were impressed with the demo amounts & topics, too.

Today is the day 2 keynote. You can watch it live starting at 8:15AM Pacific on PASSTV on PASS.org.

The abstract:

Two Decades of Data Innovation: Celebrate the Evolution of the Data Platform and See into the Future with Extreme Cloud-Based Data Solutions

Twenty years of PASS Summit and twenty-five years of SQL Server; together we’ve come a very long way. Join SQL Server team past and present as they take a journey through the evolution of the Microsoft data platform into the broad ecosystem you see today. You will hear from many of your familiar friends: Conor Cunningham, Bob Ward, Lara Rubbelke, Mark Souza and a few other surprises.

Then buckle-up for a deep dive with Microsoft Data Platform CTO Raghu Ramakrishnan on the internals of our next evolution in engine architecture which will form the foundation for the next 25 years of the Microsoft data platform. However you interact with data – be the first to look under the hood and see the future of data straight from the Azure Data engineering team.

What to expect: a new approach to a day 2 keynote. In the last several years, the day 2 keynotes have been much better for us technical geeks than the marketing-focused day 1 keynotes because:

Day 1 usually hopped around from topic to topic, only spending a few minutes on each, never really getting technical, just showing off lots of brochure-style demos
Day 2 was for the geeks: it had one speaker (recently Dr. David DeWitt or Dr. Rimma Nehme) diving very, very deeply into one specific topic, taking people from near-zero to near-hero, albeit often losing people along the way because they go so deep

Looking at today’s abstract, this looks like a really cool hybrid of the two, and the scope is ambitious for 90 minutes. (Day 2 keynotes also involve PASS talking about the state of the ~~union~~ non-profit organization.)

What to expect in this blog post: I’m sitting at the Blogger’s Table in the middle of the keynote room, taking notes. Every few minutes, I’ll update this blog post. The page won’t refresh itself, so if you’re following along at home and you want to see my latest takes, hit refresh.

Today will conclude the Summit 2018 keynote coverage, by the way. There’s no keynote on day 3 of the conference.

The keynote is scheduled to start at 8:15AM Pacific. Here we go!

Bloggers setting up for the #PASSsummit keynote. pic.twitter.com/td6Oa6bswB
— Brent Ozar (@BrentO) November 8, 2018

8:12AM – you’re likely to see a lot of guys in kilts around today – that’s a grassroots movement to support the Women in Technology luncheon on Thursdays at Summit. (I’ll see ya there!)

8:15AM – Video introducing the #SQLfamily movement. It’s so much fun recognizing all these faces up on the video. I love this community.

8:19AM – PASS VP of Finance (and excellent Chicagoan) Wendy Pastrick takes the stage. Traditionally on day 2, PASS talks about the state of the organization, how the finances are doing, event turnout, etc. First, though, she’s talking about the networking first – that’s the real value of being here, I think. (That’s why today, I’m going to be doing what we call “the hallway track” – walking around, catching up with a lot of folks from around the world that I only get to see at conferences.)

8:20AM – 185 countries, 300,000 members at PASS.

8:21AM – Wendy’s showing how the PASS organization uses Power BI to make data-driven decisions about where they should be spending the community’s money.

8:22AM – Wendy just covered the financial slides BY SINGING I WILL SURVIVE! Oh my God, Wendy is my new hero. I doubt any of us will remember the numbers (other than the finishing note that the budget is balanced), but we will ALWAYS remember that moment.

.?@wendy_dance? serenading and educating us at the same time for the second #PASSSummit2018 #v20 keynote pic.twitter.com/ndJNHAIieD
— ??Kellyn Command Line ?? (@DBAKevlar) November 8, 2018

Starting off the Day 02 Keynote, @wendy_dance talks (or rather, PERFORMS!) finance pic.twitter.com/orEMkyUutt
— PASS (@sqlpass) November 8, 2018

8:23AM – PASS VP of Marketing (and excellent former-Michigander, now Pacific Northwesterner) Tim Ford takes the stage. 40% of attendees this year are first-timers.

8:26AM – Taking a moment of silence to remember those no longer with us.

About the PASS members we’ve lost this year, @sqlstudent144 recapped our community losses: https://t.co/0J0qCTJGFr #PASSsummit
— Brent Ozar (@BrentO) November 8, 2018

8:28AM – Tim explains how Summit’s content has adapted over the years to reflect changes in the Microsoft data platform and things the community is interested in. (Some of this is to remind Microsoft that hey, PASS wants sponsorship money because we’re hip, we’re cool. There are some tough discussions happening around the budget & Microsoft’s sponsorships going forward, especially with Ignite conflicting with the Summit dates next year. Microsoft will have some tough decisions as to which team members go to which events.)

Next to the stage, @sqlagentman discusses the #sqlpass community, ways to stay involved, and the evolution of our diverse community over 20 #PASSsummit events. pic.twitter.com/hrvNVQNEI5
— PASS (@sqlpass) November 8, 2018

8:31AM – Microsoft’s Mark Souza takes the stage, credits PASS for running 20 Summits, and he’s attended all of them. Now joining him are Ron Soukup, Paul Flessner, Ted Kummert, and Rohan Kumar, folks who have been running the SQL Server program since 1989! It’s an incredible amount of historical knowledge about the data platform up onstage.

#sqlServer managers through the history #sqlpass #passSummit pic.twitter.com/EP2m3tUYaq
— Niko Neugebauer (@NikoNeugebauer) November 8, 2018

8:33AM – Ron Soukup joined the team in 1989 when there were only 5 folks, ran it on 2MB of RAM, and shipped it on floppies. It focused on OS/2 initially, but OS/2 was a sales flop, so then they pivoted over to running it on Microsoft Windows instead.

8:39AM – A group of 17 folks brought SQL Server from the OS/2 release over to Windows NT, getting it from 4.2 to 6.5. Amazing to think about the knowledge encapsulated in those 17 folks. It’d be neat to read interviews from ‘em.

8:40AM – Video talking about the SQL 2000-2005 days, the Slammer virus in 2003, and how Conor Cunningham closed his office doors for a couple of weeks and created DMVs. The guy is a visionary – same thing with Query Store, something that really has the potential to change the way people do performance tuning. (People, you should be using it. Although maybe if we get to the point where it’s on by default in all databases, that might change things a lot.)

I'd like to take this opportunity to congratulate either #PASSSummit and/or the Washington Convention Center – the WiFi works GREAT this year!
— MidnightDBA (@MidnightDBA) November 8, 2018

8:42AM – I totally concur. I’m on an iPad today, no wired Ethernet, and it’s been great.

8:43AM – Paul Flessner talking about how SQL Server would make a run at Oracle by upping their game and beating Oracle on total cost of ownership.

8:45AM – Paul explaining how SQL Server 7 was such a groundbreaking release in the way it automatically updated statistics. However, he got a series of voicemails from Pennzoil about how performance was initially terrible, then better, then exceeding all expectations, all within the span of 3 hours. I think we’re going to face some of those same kinds of horrible-then-delightful reactions with SQL Server 2019 with things like adaptive memory grants, batch mode in rowstore.

8:47AM – Buck Woody says via video, “I really miss the R2 designation, and I’m lobbying to get that back.” The crowd laughs, and he gives a knowing grin in the video. Now there’s a guy who knows his audience.

8:48AM – Ted Kummert managed the SQL Server business from 2005-2014. “If someone asks you if you want to manage the world’s most successful commercial database, you should say yes.” Like Ron & Paul, he seems happily laid back about reminiscing about his time here. “You gotta be great at the craft of engineering.”

Probably my favorite panel session ever. #PASSsummit pic.twitter.com/8GtT7q4kQl
— Brent Ozar (@BrentO) November 8, 2018

8:51AM – Video of everyone recapping “The cloud: we’re all in.” They did own that phrase big time, really drumming it into every Microsoft person you talked to during the early 2010s. Got a little old.

8:53AM – Heh heh heh, Ted, I see that you read yesterday’s keynote recap, hahaha. I’m honored. It’s great seeing you onstage again.

8:55AM – Rohan talks just a little, but in fairness, he gets a lot of stage time (rightfully!) these days. It’ll be interesting to hear him look back in several years about his tenure here. Managing the data platform in 2018 has to be spectacularly challenging with so many competitors, distractions, and customer requirements. A lot of tough choices to make. (And I’m happy with where things are going, too – I was talking with Kendra and a couple of first-timers at breakfast this morning, and I just couldn’t be happier with the improvements in SQL Server 2019. Sure, I may not use all the features – I’m probably never going to touch big data clusters – but there is just so dang much good stuff in the 2019 box.)

8:57AM – Raghu Ramakrishnan @raghurwi, CTO for Data, takes the stage to talk about a future-looking vision for data management and how that becomes real in Azure SQL DB Hyperscale.

And now for the #PASSsummit Day 2 Keynote, @raghurwi begins his address! pic.twitter.com/plvB9VoY6y
— PASS (@sqlpass) November 8, 2018

9:02AM – The cloud gives us elastic compute & storage, but these come with problems: as data grows, size-of-data operations are slow. (For example, try updating a column across all rows in a table.) The solution would be figuring out how to make them faster while masking network latencies (slow speed between nodes and their storage.) He’s starting with a big picture vision similar to how Dr. DeWitt and Dr. Nehme introduced very challenging concepts in a way that everyone in the audience can understand.

#sqlpass #PASSSummit pic.twitter.com/hgENKFgP9d
— Niko Neugebauer (@NikoNeugebauer) November 8, 2018

9:05AM – Paraphrasing what he’s showing above: when you have to build a bunch of database servers in an Availability Group (or something newer), even if they have local SSDs, you’re going to have to deal with latency when the primary writes to its secondaries in the same data center, and even more when you write to other data centers. There’s (currently) no way around those kinds of latencies, even with fast SSDs and networks.

9:07AM – Today, each of your database servers is a silo: your web site OLTP front end, the accounting system, the data warehouse, then flat files in data lakes. “There isn’t hope to build one system to do it all well.” Good honesty there, especially looking back in the rear view mirror at features like filestream. “So can we break data free from silos?” This is where Hyperscale and Big Data Clusters start to come in – separating compute from storage.

9:09AM – Basically what he’s describing is taking what AWS Aurora MySQL and PostgreSQL does with separating compute & storage, but then “can we open access to other heads?” Meaning, like can you use other query engines, reporting tools, dashboards, etc to connect directly to the data files that are stored on Amazon S3? (AWS wasn’t the first to do this with relational databases either, just putting it in terms that I work with and understand.) If they can execute on this, then it’s beyond what AWS Aurora is doing today. (I don’t have any forward-looking info on what Aurora’s doing in this space either.)

9:11AM – Design targets are infinite max database size, 99.999% availability, <5 minutes for right-sizing replicas, recovery in under 10 seconds, under 200 microsecond commit latency (but currently at 2 milliseconds). These are obviously forward-looking goals, but here’s the thing: you simply can’t build systems like this yourself in your own data center. If they can pull this off, then you’d be an idiot to host high-performance databases yourself.

9:13AM – Announcing that Socrates was the project name for Hyperscale because “he was a database! He went around asking queries!” HAHAHA, nicely done.

9:14AM – While talking about separating compute from storage, he points out, “Our competitors do it.” OK, good, I like that honesty. AWS Aurora MySQL & PostgreSQL are indeed doing this, but AWS was able to jump into this commodity database by taking an open source database and rolling their own improvements in the storage engine. However, that comes with a drawback: they’re limited to MySQL & PostgreSQL’s query optimizer (at least for now, although we’re seeing them improve with things like automatic parallelization of read-only queries across multiple nodes.) Microsoft has a big leg up here when they eventually deliver Hyperscale to general availability: they’re building atop SQL Server, which – and I’m not being a mindless Microsoft shill here – is better engine for a lot of use cases. They’re going to charge for it, obviously – they can’t use free code like MySQL and PostgreSQL do – but their objective is to make sure it’s worth it.

9:18AM – When separating compute & storage, you need fast caching locally to compute nodes (since network latency to the storage is higher when compute and storage are separated.) To solve that, they’re building atop the Buffer Pool Extensions code. (I have passionate feelings about that particular feature, but that doesn’t mean the next version has to be that bad.)

9:22AM – He’s explaining MVCC and the new in-database Persistent Version Store (PVS). These aren’t easy concepts to explain to non-database-administrators. I like how he gives just enough information to make the DBAs happy, but not so much that it stays in the weeds. He was maybe on that slide for one minute. (Not that the next slide is easy to grep for non-sysadmins! Gotta be a master jack of all trades in here to keep up.)

9:24AM – Not taking a lot of notes here because it’s extremely similar to AWS Aurora MySQL and PostgreSQL. If you want to learn more about how this stuff works, AWS has published a lot of re:Invent sessions about Aurora internals on YouTube. It’s not that this material here isn’t good – it is – but given time limits, he can’t really explain it in depth. This is probably a good starting point:

9:33AM – I’m not going to try to recap the technical details of this – you really need to watch the recorded version to see the slides at high resolution – but it’s neat to think about the fact that this could not have been built 5 years ago, let alone 10. The transaction logging at scale only works with today’s new hybrid memory and solid state storage. He mentions that you can get another copy of your transaction log in two Availability Zones at the price of another 1-2 milliseconds of latency, for example – that’s ambitious stuff. (Again, the same thing that Amazon’s doing, but that’s not a bad thing – it’s a good thing that Microsoft understands the competition.)

It’s wonderful for all of us that they have a product offering that keeps the competition strong.
— r@ndol.ph (@_randolph_west) November 8, 2018

9:37AM – Now he’s getting to the new fun stuff that AWS doesn’t do: letting other applications share the same database storage. Here’s where Microsoft has a really, really big edge: since they control other engines (like Azure SQL DW), they can make those engines read the exact same data & log structures that your OLTP servers are using. “This doesn’t mean you’ll never have copies – you already have copies in the sense that a non clustered index is a copy of a clustered index. This is the same design trade off.” Great example!

Seriously, people, this is a big hint. A very big hint.

If they can deliver this, then they will have a phenomenal sales story 3-5-7 years from now.

And that’s a wrap! Ooo, I’m really excited about that last hint. I can’t wait to see how this unfolds!

PASS Summit 2018 Keynote Day 1: Live Blog

November 7, 2018

#SQLPass

Today is the opening day of the 2018 PASS Summit, the largest annual gathering of Microsoft data professionals. Every year, thousands of us gather at the Seattle Convention Center to connect, learn, and share. On this first day of the conference, all the attendees go to the same room to watch the keynote speech.

To watch it live, free, starting at 8:15AM Pacific: PASS.org.

The abstract:

SQL Server and Azure Data Services: Harness the future with the ultimate hybrid platform for data and AI

In today’s world, the forces of Cloud, Data and AI are driving innovation and ushering in the era of the intelligent cloud and intelligent edge. Microsoft’s goal is to bring you the best products and tools to tackle these new opportunities—helping you build a data infrastructure that supports your organization now and into the future.

Join Rohan Kumar, Corporate Vice President of Azure Data, as he demonstrates the latest advances from Microsoft across SQL Server, Azure Data Services, Business Analytics and AI. Preview new products and features and see the innovations that make Microsoft the best data partner of any provider– on-premises or at cloud scale.

What to expect: 100-200 level tech details on Azure & SQL Server 2019. Keynotes are general marketing about features because the audience experience level is so broad. Here at the Summit, you’ve got database administrators, developers, report writers, BI professionals, data analysts, managers, you name it. Microsoft aims for the lowest common denominator here, talking about features and customer stories in a way that 99% of the audience will be able to grasp. For example, when they talk about an Azure feature, they want you to be able to follow along even if you don’t use Azure. To see what to expect, check out my live blog from the 2017 day 1 keynote.

What not to expect: truly groundbreaking announcements. The much-larger Microsoft Ignite just finished up recently (like 30K attendees to Summit’s 3K). Microsoft tends to show off the really big stuff there, like the first downloads for SQL Server 2019. This keynote is likely going to be more about fleshing out feature details, telling customer stories, and sharing delighter features that might not move licenses, but will move the excitement needle. For example, take this tweet from the Power BI CTO:

November is just around the corner, and it’s a HUGE month for Power BI.

The volume and magnitude of features about to be released is absolutely staggering.

This is going to be the greatest Power BI update since its introduction. Period.
— Amir Netz (@AmirNetz) October 30, 2018

The day 1 keynote is a good way to get a fast overview of what’s new lately, and to help shape which conference sessions you want to attend.

What to expect in this blog post: I’m sitting at the Blogger’s Table in the middle of the keynote room, armed with Sony’s latest superzoom camera, taking notes. Every few minutes, I’ll update this blog post. The page won’t refresh itself, so if you’re following along at home and you want to see my latest takes, hit refresh. The keynote is scheduled to end at 9:45AM Pacific, at which point I’ll sign off, pack up my gear, and head off to watch sessions.

The keynote is scheduled to start at 8:15AM Pacific. Here we go!

8:11AM – folks filing in

8:13AM – surrounded by awesome folks at the blogger’s table.

8:18 – room goes dark, and they’re playing a welcome video. “PASS is a community like no other.” Amen to that.

8:20 – PASS President Grant Fritchey welcoming folks to Summit v20.

PASS President Grant Fritchey

8:25 – thanking the PASS Passion Award Winner for 2018: Michael Johnson.

PASS PASSION Award Winner Michael Johnson

8:28AM – Video showing past attendees & Microsoft staff talking about how the product and the event have changed over time.

8:30AM – Rohan Kumar, Corporate Vice President of Azure Data, taking the stage to start the demo party. He says Azure has made a big difference in helping Microsoft understand how customers use the product in real time.

Rohan Kumar

8:33AM – “Hybrid is the way of the future, and this is exactly how it should be.” Good message for this crowd. “Hybrid enables comprehensive AI and analytics.” AI and analytics are bursty workloads and unfamiliar tools for most customers, and that’s a place where cloud services work so well.

8:36AM – SQL Server 2008 and 2008 R2 end of extended support on July 9, 2019, and Rohan’s suggesting that you should move to SQL Server 2017 and Azure Managed Instances. Huh – why didn’t he suggest SQL Server 2019?

8:41AM – Microsoft’s Argenis Fernandez has the answer, and I agree 100%.

That’s easy – migrations need to happen now, and not wait for GA of 2019.
— Argenis Fernandez (@DBArgenis) November 7, 2018

Sadly, I don’t think enough people are aware of how big migrations are, and how they need to start hustling now.

8:42AM – Rohan says these are the same four pillars they’ve been focusing on for the last several releases.

The Four Pillars™

8:44AM – “We think this will be huge for ISV developers because you can write applications in Java and not worry about security.” Oh, so many jokes here.

8:46AM – We’ll get a new CTP every month. Last night’s 2.1 added scalar UDF inlining, buffer pool in persistent memory, graph improvements, R and Python apps in big data clusters, etc.

8:47AM – Bob Ward and Conor Cunningham to show a Dell/EMC R940 with 4 sockets, 96 cores, unspecified amount of RAM, SC9000 storage with 48×1.92TB SSDs. Showing a new SQL Server 2019 build with less TempDB contention. Conor: “This is just a prototype, but we’re replacing some system tables with Hekaton tables. It’s not in CTP 2.1, but it will be in SQL Server 2019.”

8:52AM – That TempDB improvement is just sweet. That’s the kind of real world improvement that will make a difference. People have been struggling with TempDB contention issues and latch contention issues they can’t solve. This is why I love Azure so much – now that Microsoft has to host SQL Server themselves, they learn so much at scale.

8:55AM – Brief coverage of Polybase, Big Data Clusters.

8:56AM – “Our vision for Azure Data Studio is a single pane of glass that manages all your data platform assets.” Well, that’s what SSMS was – think SSIS/SSAS/etc – but now we’re starting again, now with even more platforms. It’s really ambitious to have one pane of glass to do all of this. I like ambitious.

Not just Microsoft databases, either – Microsoft’s Vicky Harp points out that PostgreSQL is on this slide:

Announcement of upcoming support for non relational and OSS databases, on prem and in cloud, in AzureDataStudio @RohanKData #PASSsummit pic.twitter.com/7IZL1SRbPU
— Vicky Harp (@vickyharp) November 7, 2018

9:00AM – Demoing querying unstructured data stored in HDFS, queried with Spark in Azure Data Studio.

9:01AM – “Let’s finish up demoing one of my favorite features, Java support in SQL Server.” Talking about how it eases migration from competitive platforms. Translation: “LOOK OUT, ORACLE, WE’RE COMIN’ FOR YOU.”

9:02AM – Rohan back onstage talking about Azure’s support for other database platforms, Azure Database Migration Service, near-zero downtime, and migration at scale. I keep picturing Azure as a giant vacuum cleaner, determined to suck up your databases into the cloud and get you hooked. (That’s not a bad thing, either – they’re going to do a better job of managing that kind of data than you are, and it’s a race between Microsoft, Amazon, and Google to see who can take over more on-premises workloads.)

9:06AM – Azure SQL DB Managed Instance’s new Business Critical instances (Availability Groups) will be in General Availability starting December 1st! That’s fantastic. Right now only the single-instance ones are in GA.

9:08AM – Recapping the Ignite public previews of Accelerated Database Recovery and Machine Learning Services in Azure SQL DB, and Azure SQL DB Hyperscale. So many announcements in the data platform. Niko Neugebauer burst out clapping at the Machine Learning Services announcement here at the blogger table, hahaha.

9:12AM – Lindsey Allen up to do 4 minutes of demos – showing quick columnstore indexes, but the sizes are bloating up fast, so she’s enabling Azure SQL DB Hyperscale. “Most customers don’t like dealing with partition keys, and they’re really hard to get right.” Lots of laughs – it’s true.

Lindsey Allen

9:13AM – “With Azure SQL DB Hyperscale, restores are really fast because they’re from snapshots. But I don’t have time, so I can’t demo it.” Chuckles. But seriously, snapshot restores will be mind-blowing for folks who haven’t tried that with SAN snapshots before. (And SAN snapshots are an intimidating feature for new shops.)

9:16AM – Accelerated Database Recovery because slow rollbacks and restores are bad. “We sure experienced those pains in Azure. Sleep deprivation is a very powerful motivator.” Showing how with ADR, the transaction log size stops growing at a certain size because it does aggressive truncation. (Has a lot more tricks than that, too – ADR just looks spectacular as a new feature. Really looking forward to digging into that.)

Here’s more info about Accelerated Database Recovery.

9:18AM – “We want to make Azure the best place to run any database,” referring to open-source databases. Recapping recent Azure Cosmos DB announcements. Microsoft has so many different data platform tools now that it’s not really effective to do demos across all of them. Even talking quickly, Rohan can’t cover all of the announcements – he’s spending only tens of seconds talking about things that took millions of dollars of investments. Microsoft’s investing a lot in data, and I bet most attendees don’t realize how wide-ranging this work is.

Demoing Cosmos DB multi master replication with a drawing app. You just know they’re not gonna share the URL for that, hahaha. #PASSsummit pic.twitter.com/QSzqN4GzTj
— Brent Ozar (@BrentO) November 7, 2018

9:24AM – Talking about Cosmos DB’s choices of different isolation levels. I get that it’s extremely technically hard to build, but we’re talking to an audience that has been used to changing isolation levels on the fly for decades. I’m personally excited about how Cosmos DB delivers that stuff, though.

https://t.co/N5NPuWaOiz
— rimma nehme (@rimmanehme) November 7, 2018

Alright, dear readers, you know what to do: DRAW STUFF.

9:30AM – Tons of improvements for Azure SQL Data Warehouse: row level security, Accelerated Database Recovery, virtual networks. Sponsored comparisons say Redshift is more expensive and slower, and Rohan says the secret is local caching on SSDs. John Macintyre coming onstage to demo it. Processing over 1 trillion rows per second.

9:32AM – Thinking big picture for a second: Microsoft’s challenge with data warehouse implementations has never been their side of the delivery. They did a great job of building reference architectures, then working with hardware vendors to design reference systems. The challenge was that customers didn’t build it out right, either buying the wrong hardware, or not buying enough of it, or configuring it wrong. Azure SQL Data Warehouse’s wonderful speed is due to multiple factors: better architecture, but also taking customers’ grubby fingers out of the equation so they can’t screw up the hardware. You pay based on the capacity and speed you want, and Microsoft takes care of the rest.

9:37AM – Fast coverage of HDinsight 4.0, Azure Event Hubs for Kafka, Azure Data Explorer – which looks AMAZING, by the way – I love love love that, Ariel Pisetzky of Taboola coming onstage to talk about it.

9:40AM – I don’t blog about these tools, but based on what I’ve seen from it, Azure Data Explorer is the tool your analysts really want. Everything from the pricing, the speed of setup, the flexibility in querying – it looks awesome to me. I haven’t put my hands on it myself – I just don’t have the need for it – but I’d strongly recommend just watching a few demos of it so you can talk about it to your analysts. Try this one:

9:42AM – “There is no keynote complete without Power BI.” Recapping the Dataflows feature (doing ETL pulling from data lakes to into Power BI), SSRS Reports in Power BI public preview.

9:45AM – Patrick LeBlanc coming onstage to talk about SSRS reports in Power BI. The guy is a fantastic presenter, and people are excited to see him liven things up. The blogger table cheered at his name.

Big whoops for @patrickdba. #PASSsummit pic.twitter.com/AK4b3Ag8Zo
— Brent Ozar (@BrentO) November 7, 2018

The SSRS experience in Power BI looks fantastic, including the printing integration.

9:47AM – Folks on Twitter starting to pass judgment on the keynote, and it’s a happy camp:

There was definitely less marketing talk and slogans and more announcements and demos than in previous years in this keynote. Good job by Microsoft! #PASSsummit2018 #PASSSummit
— Glenn Berry (@GlennAlanBerry) November 7, 2018

This IS the most intensive demo-and-announcement-based #Keynote ever!
Wow!#sqlpass #passSummit
— Niko Neugebauer (@NikoNeugebauer) November 7, 2018

Just watched how fantastically dynamic a speaker @patrickdba has become. I can say I knew him when. Makes me so happy to see how much he loves what he does. It’s infectious. #PASSSummit #powerbi
— Mindy Curnutt (@sqlgirl) November 7, 2018

9:49AM – Deepsha Menghani demoing Shell’s prototype of incident detection with Azure. “I’m going to do an image search for a gas station on fire.” O_O Using machine learning to detect problems by holding up a fake cigarette up to a webcam so that when it detects a potential problem, it can shut off the pumps.

And that’s a wrap! We’re done. Have fun at Summit!

What’s New in SQL Server 2019 CTP 2.1: Faster Functions

November 7, 2018

SQL Server 2019, T-SQL

A while back, we talked you through a public whitepaper about how Microsoft was working on making user-defined functions go faster. Now that the next preview (CTP2.1) of SQL Server 2019 is out, you can start getting your hands on Froid, the performance-boosting feature. Here’s the documentation on it – let’s see how it works.

Using the StackOverflow2010 database, say our company has a scalar user-defined function that we use to calculate how many badges a user has earned:

CREATE OR ALTER FUNCTION dbo.ScalarFunction ( @uid INT )
RETURNS BIGINT
    WITH RETURNS NULL ON NULL INPUT, SCHEMABINDING
AS
    BEGIN
        DECLARE @BCount BIGINT;
        SELECT  @BCount = COUNT_BIG(*)
        FROM    dbo.Badges AS b
        WHERE   b.UserId = @uid
        GROUP BY b.UserId;
        RETURN @BCount;
    END;
GO

CREATE OR ALTER FUNCTION dbo.ScalarFunction ( @uid INT )

RETURNS BIGINT

WITH RETURNS NULL ON NULL INPUT, SCHEMABINDING

BEGIN

DECLARE @BCount BIGINT;

SELECT @BCount = COUNT_BIG(*)

FROM dbo.Badges AS b

WHERE b.UserId = @uid

GROUP BY b.UserId;

RETURN @BCount;

END;

In this past, performance for this has really sucked. If I grab 1,000 users, and call the function to get their badge counts:

SELECT TOP 1000 
u.DisplayName, 
dbo.ScalarFunction(u.Id)
FROM dbo.Users AS u
GO

SELECT TOP 1000

u.DisplayName,

dbo.ScalarFunction(u.Id)

FROM dbo.Users AS u

The execution plan looks simple:

2017 plan that hides the scalar’s work

But it’s actually terrible:

It doesn’t show all the stuff the scalar function does
It wildly underestimates the work involved (scalars have fixed tiny costs regardless of the work they do)
It doesn’t show the logical reads performed by the function
And of course, it calls that function 1,000 times – once for each user we return

Metrics:

Runtime: 17 seconds
CPU time: 56 seconds
Logical reads: 6,643,089

Now let’s try in SQL Server 2019.

My database has to be in 2019 compat mode to enable Froid, the function-inlining magic. Run the same query again, and the metrics are wildly different:

Runtime: 4 seconds
CPU time: 4 seconds
Logical reads: 3,247,991 (which still sounds bad, but bear with me)

The execution plan looks worse:

2019 plan showing the inlined function’s terribility

Your first thought is probably, “Mother Nature, that plan looks like more work,” but the point is that it’s now showing the work involved in the function. Before, you had to use tools like sp_BlitzCache to figure out which functions were getting called, and how often.

Now, you can see that SQL Server is building a spool, or as Erik likes to say, passively-aggressively building its own missing index on the fly without bothering to tell you that it needs one (note that there’s no missing index request on the plan):

Spoolin’ up

That’s great! That means I can easily fix that just by doing index tuning myself. Granted, I have to know how to do that level of index tuning – but that’s a piece of cake when suddenly the plan makes it more obvious as to what it’s really doing.

How to find out if your functions will go faster

The documentation lists a lot of T-SQL constructs that will or won’t go inline:

From the documentation

But reading that would require you to open the code in your user-defined functions, too, and that’s one of the top causes for data professional suicide. I need all the readers I can get.

So instead, just go download the SQL Server 2019 preview, install it on a test VM, restore your database into it, and run:

SELECT * FROM sys.sql_modules;

1	SELECT * FROM sys.sql_modules;

There’s a new is_inlineable column that tells you which of your functions can be inlined:

sys.sql_modules

Then to find out which of your functions are actually getting called the most often in production today, use sp_BlitzCache:

sp_BlitzCache @SortOrder = 'executions';

1	sp_BlitzCache @SortOrder = 'executions';

Make an inventory of those, and while you’re in there looking at functions, try CPU, Reads, or Memory Grant as different sort orders. Keep an eye out for table variables, high memory grants, or low grants that lead to spills, because SQL Server 2019 improves those too:

That’ll help you build a business case to management. You’ll be able to explain which of your queries are going to magically run faster as soon as you upgrade, and how much of an impact that’ll have on your SQL Server overall – all without changing code. That’s especially powerful for third party apps where you can’t change the code, and you need a performance boost.

Picking #PASSsummit Sessions to Attend

November 5, 2018

#SQLPass

Brent Ozar Unlimited Team

So you’re going to the PASS Summit in Seattle this week, and you’re overwhelmed with the number of session choices. You’re not alone – it overwhelms me too. There are some slots where I wish I could clone myself and attend several sessions at once.

Here’s how I do it.

First priority: paying back your sponsor. If your manager is picking up the tab for this conference, think about the top 2-3 sessions that will make the biggest difference for them. (You can even ask ’em outright – what are the top 2-3 questions you want me to answer when I’m at PASS?)

Put those sessions in your personal calendar so that they show up on your phone/watch/laptop/whatever, and get reminders on those. You wanna make sure you have your butt in a seat, take good notes, and be ready to go back to the office to do a lunch & learn about what you learned from those sessions. That makes you more likely to get the registration paid for next year.

Next priority: paying your career forward. Regardless of what you’re doing today, you’re probably looking at that session list and thinking, “Uh, there are a lot of techniques and technologies that are new to me.” You can’t possibly master ’em all – the data platform is too wide these days. You’re also going to hear a lot of marketing hype about why “YOU MUST ABSOLUTELY LEARN TECHNOLOGY X OR YOU’RE A DINOSAUR!!!!1!1!ELEVEN!”

You gotta keep in mind that the people saying that have an agenda: they want you to be an early adopter of their particular technology/feature/product/brand. They need users for their work to succeed. However, just because they have a marketing budget doesn’t mean you have a future career in that.

Take some time to yourself to think about what you really enjoy about your job. Love managing infrastructure? Get all excited about crafting queries? Passionate about visualization? The parts of your job that make you excited, those are the sessions to attend. No matter what database niche you can imagine, there are great-paying careers in that specialty. If you really love doing something, then you can probably get paid for doing it. Go attend sessions about it, but more importantly, meet the speaker. Ask to take them to coffee after the session, and spend half an hour getting to know them. They might be your future coworker or manager.

The above stuff make up your must-attends. After that, don’t even try to schedule anything – wing it. Let yourself be open to a few cool discoveries:

Attend the hallway track. If you meet someone that you want to talk to, and they’ve got time to talk to you, do it. Don’t hesitate to spend half an hour talking to someone if the right opportunity presents itself. Some of my favorite relationships were built by chance when I just happened to run into someone in a conference hallway and strike up a conversation.

Visit the vendor exhibitor hall. I don’t say that to be polite to sponsors – it really is a great use of your time. Do a speed walking pass through, looking at what tools are out there, and then go back and do a slow walk, taking your time to stop and talk to vendors. Ask for a 5-minute demo. Find out what interests you. Don’t worry, they’re not going to ask for your credit card.

Keep in mind that some vendors want to keep you there as long as possible in anticipation that you’ll become a customer. Set yourself a five-minute mental timer, and when it’s up, thank the vendor and move on, saying that you’ve got other places you have to visit too. They’ll understand – the conference is a zoo.

Expose yourself to something really crazy. No, not in the bathrooms – pick a session or two about something you would never dream of using, something that seems utterly outside of your comfort zone.

And if you’re not going this year, let’s figure out how we can get you to a conference next year. Going to one of the major conferences is positively life-changing: I can’t tell you what a difference it made for me, meeting and getting into the community. Here are the conferences I’ve got planned so far for 2019:

March 16 – SQLSaturday Iceland
April 11 – SQLSaturday Israel
May 21-22 – Techorama Belgium
Summer – SQL Intersection (dates & location coming soon)
Nov 5-8 – I can’t be there due to a vacation, but you can: PASS Summit 2019
Winter – SQL Intersection (dates & location coming soon)

Let’s get together! I really love meeting y’all and sharing the crazy experience of what we do.

[Video] Office Hours 2018/10/31 (With Transcriptions)

November 3, 2018

Videos

This week, Brent, Tara, Erik, and Richie discuss load balancing, preventing auto-updates, compatibility modes, capturing deadlock chains without tracing or extended events, blocking, which user should own databases, asynchronous processing, compressing databases, reindexing on Always On AGs, and “Making a Murderer” spoilers (not really).

Here’s the video on YouTube:

You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go.

If you prefer to listen to the audio:

Podcast: Play in new window | Download

Enjoy the Podcast?

Don’t miss an episode, subscribe via iTunes, Stitcher or RSS.
Leave us a review in iTunes

Office Hours Webcast – 2018-10-31

Should I use a load balancer with my AG replicas?

Brent Ozar: Jim asks, “Hey, y’all, do y’all have any tips or suggestions related to load balancing of SQL Servers? We’re currently testing putting read-only AGs behind a load balancer, not to use the built-in AG load balancer. What would you prefer?” I don’t think any of us have put an AG behind a load balancer.

Erik Darling: Nope.

Tara Kizer: I wonder if it’s even needed. Are you really needing to load balance your reads? It’s not usually – I’m not even considering load balancing the reads. I might be talking about a completely different architecture of the system if I’m having to go down that route.

Brent Ozar: The thing I’d really worry about is, does your load balancer have the ability to run DMV queries to see how overloaded each replica is and then prioritize them? If all it’s doing is spraying round robin requests, then I don’t know how useful that’s going to be. You really have to have some kind of load gage in there.

Erik Darling: I wonder, because inside some SQL – like, whatever CPU DMV, there’s the node wait and exactly how much stuff is going on, on each CPU, and it would be really interesting if someone could figure out how to get, like, timely information out of that and balance things on that. But even that, like, that would be really advanced and really difficult. So I would kind of question the wisdom there of even attempting the load balance. It’s like load balance based on what, I guess?

Brent Ozar: That’s hard. And then the other thing you’ve got to think about too is how far behind each replica is. What happens if the plumbing breaks on one of them? So y’all send in another diagnostic query to check that to know when to pull a replica out of the pool.

Erik Darling: If you’re just basing it purely on, like, number of queries, then you’re in trouble if three or four big queries end up on one and a few small queries go through on another. It’s not going to…

Tara Kizer: And why not just go for bigger hardware on the one?

Can I pick my SQL updates in Windows 2016?

Brent Ozar: And Lee asks, “We’ve got Windows Server 2016 and you have to install all of the updates; there’s no picking and choosing which ones. So if a Cumulative Update comes out for SQL Server, you cannot stop it from installing. I don’t know how to prevent it installing on my production servers. How should I get around doing that?”

Tara Kizer: Does Windows 2016 really do that, all or none?

Brent Ozar: I think it does for Windows, but not for SQL Server for the Cumulative Updates.

Erik Darling: Yeah, I mean, I don’t see mine doing that. I have to patch mine manually. I have, from my whatever Visual Basic net thing, I have a Windows Server 16 thing on my desktop and I don’t have that same issue.

Brent Ozar: You know what it is? I bet it’s his systems administrators have put it in group policy. Your sysadmin should have a list of exclusions based on server name or…

Erik Darling: Ooh, or unplug the Ethernet cable…

Tara Kizer: That’ll work. I don’t let SQL Server Service Packs or Cumulative Update patches happen on my production servers through some automated process. That is all turned off.

Brent Ozar: There’s a good reason for why too. Man, this just hit this morning. I’m going to go to SQL Server Updates. So we just recently got 2016 Cumulative Update three, and when you go to read the KB article now at the top, “Important, due to a known issue with the…”

Tara Kizer: It’s never good when there’s a yellow section [crosstalk]…

Brent Ozar: “If you use the auditing feature, do not do production deployment of the present CU. If you’ve deployed already and are hitting any issues, please contact Microsoft support.” Whoops.

Tara Kizer: I wouldn’t even install it then. I mean, I don’t ever use an auditing feature, but I’m like, nope, unsubscribe.

Brent Ozar: It’s okay. But, Tara, look it says right here, SQL Server CUs are certified to the same level of Service Packs and should be installed in the same level…

Tara Kizer: Are they really? I mean, isn’t that the whole point of them moving away from Service Packs is because it took them too much time to test them?

Brent Ozar: See, that’s what I thought. And I think what they really did is they just stopped testing service packs, and that’s how they say they’re now at the same level. Just as good…

Does SQL 2014 have 2005 compat level?

Brent Ozar: Glen asks, “Can I run a SQL Server 2005 database in compatibility mode on a 2014 or newer server?”

Tara Kizer: I mean, for sure not on 2017. I think the lowest on 2017 is 2008. But 2014 maybe, but why? What do you have in there that’s going to break – can you go up to 2012 compatibility level? Because many people on 3014 have to stick on a lower compatibility level due to cardinality estimator performance issues. I just wonder why you’re stuck on 2005. Is it a vendor thing that they’re requiring 2005? And if it is, you’re not allowed to upgrade SQL Server to 2014 anyway.

Erik Darling: Yeah, depending – if it is a cardinality estimator thing and you have any control of the code, Microsoft did just add a bunch of use hints that allow you to use the cardinality estimator, or use the cardinality estimator from a specific version. That’s not helpful to a lot of people because you’re either a vendor who controls the code and cares that much, and you have to change your code to use the hints, or you’re you and you don’t control the vendor code and that just doesn’t help you at all.

Can I capture deadlocks without tracing or Extended Events?

Brent Ozar: Ouch, next up, Matt says, “Is there a way to capture deadlock chains without tracing or Extended Events?”

Erik Darling: Yes.

Brent Ozar: It just so happens there is. I’ll fire it up and you talk about it, Erik.

Erik Darling: Yes, since, I want to say, SQL Server 2008, SQL Server has, behind the scenes, been running something called the system health extended event. So it’s not anything you have to set up and look at. It’s not as good as a prebaked Extended Events session. Specifically, they capture deadlocks because it doesn’t keep a good full history in there or doesn’t persist terribly long. So if you are the type of person who has a lot of stuff going on in that background Extended Events session, then your deadlock information could very quickly run out of there. There’s a perfmon counter that tracks deadlocks, or some counter that tracks deadlocks in there.

And there are lots of times where, with a client, we run sp_Blitz and it’s like, wow, you had 500 deadlocks a day. But then we go to run at the system health session and it’s kind of unfortunate that they’re all gone from there. There’s other stuff filling that thing up. I wrote – while I was drunk on a plane last year going to PASS – a stored procedure called sp_BlitzLock. And sp_BlitzLock will examine either the system health Extended Events session, if you have your own Extended Events session spun up to do it. It will examine that on SQL Server 2012 and up.

SQL Server before 2012, the deadlock XML was kind of garbage and not very helpful, but the 2012 and up one is a lot better, has a lot more information in it. So when you go look in there, you can get a whole lot of information about deadlocks from there, that XML…

Tara Kizer: What I like the most out of sp_BlitzLock is it gives me an easy way to check the isolation level, because sometimes, for clients, they’re running serializable and they don’t even realize it. There’s just no concurrency on that. And then second of all, you may be able to quickly see in one of the columns that it’s a select versus an update; I’m like, oh hurray, I get to recommend RCSI. You know, if it’s between two writes, it’s a little bit more challenging. It’s almost never between two writes though.

Erik Darling: Yeah, sometimes it’s tough to get stuff into that Extended Events session. I remember when I was setting up – when I was trying to set up deadlocks to test BlitzLock with I was like, wait a minute. I would have to run things like five or six times before it would accurately register and get in there. It’s kind of a pain in the butt, man.

Tara Kizer: Was it just not in the system health session yet?

Erik Darling: Yeah, it took a while to flush out to there from wherever it lives and sometimes it took a few tries before it would go in there. I don’t know, it’s a whole thing…

Tara Kizer: That’s probably what Brent’s experiencing.

Erik Darling: Yes.

Tara Kizer: You need to do it 10 more times.

Erik Darling: Either that or Brent’s most recent pull request to remove columns broke everything.

Brent Ozar: It’s very possible

Tara Kizer: Maybe he has the system health, you know, disabled.

Brent Ozar: Anything is possible on my crappy system. For y’all who are watching the video or watching it live, what I’m doing is I’m reproducing deadlocks like crazy. And then, in an effort to show sp_BlitzLock, showing it in the history, and… yes…

Tara Kizer: Finally.

Brent Ozar: Alright, thank god…

Tara Kizer: And there they all are too.

Erik Darling: Yeah, they all go in at once. It takes them a while to flush out. It’s aggravating as hell.

Tara Kizer: That makes me suspicious about Extended Events sessions. What kind of delay is happening there? Is it just due to volume, you know, because there’s no load here?

Erik Darling: No, it’s just it takes a little while for stuff to just make it into the session, for the XML to refresh or whatever. It’s sort of like how when you go and look at the CPU usage in system health, like the ring buffer. It’s like every minute, but sometimes it’s like five minutes behind and you’re like, wait a minute [crosstalk].

Brent Ozar: Yeah, so that’s really slick. You don’t have to turn anything on to get it. It’s already built in by default; 2012 and newer, so very slick stuff.

Erik Darling: So it gives you all that deadlock info up top, tells you where the transaction is. Down the bottom, we try to do some, like, metadata analysis and tell you which stored procedures, tables, indexes you have the most stuff happening in. It gives you commands with BlitzCache and BlitzIndex to take a closer look at those, maybe see, oh there’s an index missing, I need to do something else. There’s something weird in the query plan, who knows? I don’t know, I was drunk on a plane when I wrote that.

Brent Ozar: And it works pretty well.

Erik Darling: I’m going to be really helpful if I’m going to be drunk on a plane, and here we are.

Richie Rump: Those first class Brent Ozar cross-country flights.

Erik Darling: When you can really stretch that back…

Brent Ozar: We try to only fly first class because we just end up getting drunk on planes and writing T-SQL that y’all can use, so it’s kind of like a tax write-off.

Erik Darling: You’re welcome.

Brent Ozar: Mike pipes in with, he just wanted to say that, “The First Responder Kit with the PowerBI piece has been great for recording and validating app query issues. Keep up the good work.” Great, glad you like it.

Brent Ozar: Jim says, “Our blocking monitor occasionally shows system processes blocking each other…” Oh god, I’ve got to read these before I start talking, “It started shortly after we introduced Service Broker. How can I [crosstalk] to see if the blocking is coming from the activation proc we’re using in Service Broker?”

Richie Rump: Don’t use Service Broker. Problem fixed.

Erik Darling: A-plus.

Tara Kizer: Just fix the blocking monitor – the [update carrier], that two system processes are blocking each other. It’s going to resolve itself, I mean, so just fix the blocking monitor to exclude blocking between SPIDs less than 50.

Brent Ozar: Makes perfect sense.

Why don’t all my queries show clearly in the plan cache?

Brent Ozar: Pablo says, “What does it mean when I look in the plan cache and there are executions but some of them show the SQL text as empty?”

Tara Kizer: SQL text – I mean, the execution plan might be missing if you’re using BlitzCache, but the SQL text is never empty for us. Maybe he needs to use a better tool, like BlitzCache.

Erik Darling: So when I’ve seen that happen, it’s been, like, encrypted procs or sometimes what’s a real son of a gun about it is if there’s restricted stuff in there like passwords, sometimes SQL Server screens that stuff out.

Tara Kizer: But just a portion of the query, right? Not the – I mean, the entire query…

Erik Darling: Yeah, but if that’s all the query is, who knows? I don’t know what these crazy people do with their SQL Servers.

Tara Kizer: Use sp_BlitzCache and see if it happens there.

Erik Darling: That would be my vote too.

Richie Rump: They use Service Broker…

Which users should own databases?

Brent Ozar: Let’s see, next up – I swear I’m going to start reading these questions first. Alright, I’ll take this one…

Erik Darling: You say that every week…

Brent Ozar: Jeremy asks, “Is there a best practice for what users should own databases?”

Tara Kizer: SA.

Brent Ozar: And why? Why SA?

Tara Kizer: I don’t even remember the reason anymore, but I know that I’ve encountered an issue with .NET CLR objects when the owner was not SA and we moved servers and something happened there. SA fixes everything; job owners and database owners. Even if you don’t use SA, they count SA, have SA be the owner.

Erik Darling: It’s not a big deal if you have an agent job owned by someone else and that account gets disabled and it might stop working or whatever. But for me, it’s just that if someone else owns – like, if a different user owns a database, they have all the SA level privs on there, I might not want that. I might not want that person to own the database just because they happen to restore it that day. So I just like to keep everything under SA.

How should I do async processing without Service Broker?

Brent Ozar: Jim follows up with, “I know y’all don’t like Service Broker and you recommend not to use it, but do you have another suggestion to handle asynchronous processing. We’re using Service Broker to do asynchronous data processing instead of triggers.” Richie, how do you like doing asynchronous data processing?

Richie Rump: In the cloud.

Brent Ozar: In the cloud? And do you do it in the relational database, or where do you do it?

Richie Rump: I do because Brent told me I had to.

Brent Ozar: No you don’t. We use a queue… Service Broker is a queue, right, and we’re totally down with queues, we just don’t like doing them in your $7000 core relational database.

Richie Rump: Yeah, and there’s a lot of free open source queueing systems out there.

Tara Kizer: I’ve worked with a lot of Java developers who love TIBCO for cueing.

Erik Darling: What was it Jeremiah used to like, RabbitMQ?

Brent Ozar: Yeah.

Should I use row or page compression?

Brent Ozar: Let’s see, Marvel asks, “We’re looking at implementing compression on some of our largest databases, however, I can’t find information on which level of compression is best. The clearest article I’ve found is this one article that tries to tell me when to use row or when to use page.” Really, the easy thing to do there is just test. There are stored procedures you can use to test and what they do is create a compressed copy of the table over in tempdb. So then, you can check and see which one is better for you.

Tara Kizer: And it’s built-in. And there are some handy scripts that can help you interpret that data too. I forget who the person was, but it’s a female that’s got a really good script. It’s been years since I’ve looked at it…

Richie Rump: Erika Darling?

Tara Kizer: Maybe Michelle Ufford, I can’t remember though.

Erik Darling: Yeah, I know exactly what you’re talking about too. It’s a shame that SQLBlog isn’t really such a thing anymore because [Linchie Shay] used to have a bunch of great posts on compression about, like, how it affected reads versus writes. You have to go to like archive.org to try get that, it’s a pain. If you just go to the website, it’s like login failed or something.

Tara Kizer: Really?

Erik Darling: Yeah, it’s terrible. But it might not even be up there anymore. It doesn’t come up in search results.

Brent Ozar: I bet you’re right.

Erik Darling: If you went to archive.org, you can track old copies of them down, but those posts were great. And these days though, if I’m seriously thinking about tables that really extra need compression then I’m considering column store because the compression is so much better than page or row compression. But you know, there’s just as many gotchas and guidelines to follow there too.

Tara Kizer: But not for OLTP systems, right? Or at least a table that has lots of changes happening…

Erik Darling: Yeah, yeah, I mean, you know, you could make an argument for throwing a non-clustered column store index on there and just, you know, a place where maintenance actually matters with column store indexes, and then just performing fairly regular maintenance on it. But if you’re talking about, you know, a clustered column store, then it’s kind of a whole different ballgame. I would avoid that for OLTP – not be your friend…

I don’t have a lot of space left in this data file.

Brent Ozar: Thomas says, “Sometimes, when I click on the properties in my database, it shows the space available as being something I am not comfortable with, like only 4GB. However, there’s a ton of space available on the hard drive.” So it shows not a lot of space in the database, lots of space available on the hard drive, “Should I be concerned?”

Erik Darling: Leave it alone. It will grow.

Tara Kizer: I like that too. I mean, I’ve always let auto-growth just kick in, as long as I have instant file initialization in place for the data files. I know that some DBAs have scripted this out though. They’ll check for free space, and if it’s below their threshold, they automatically, later on, during a low period of the day, they will grow out that file to add additional space. But that’s too much work. I don’t care about auto-growths happening during the day, as long as I have instant file initialization enabled and the auto-growths are for the data files.

Erik Darling: Yeah, I mean, the one place where I do care about that is with log files, because they don’t get instant file initialization. So those ones, I’m okay with having empty space in and growing those out a little bit further than maybe some people would want. Some like some percentage size of the database just to make sure that I’m not waiting on those things to grow into the error log space, because who knows – you set that to a percent, you know, the bigger your file gets, the bigger that percent gets, the bigger that growth gets, the more zeros you have to write, stuff waits around. It sucks. It’s not fun. So log files, grow them out.

How can I rebuild indexes without hitting the log file?

Brent Ozar: Gus says, “What’s the best way to reindex on an Always On Availability Group database without running out of space on a transaction log?”

Tara Kizer: I thought for sure you were going to skip that question because we’re just going to make fun of the question.

Brent Ozar: It’s great. I love it.

Tara Kizer: I’m not going to answer it.

Brent Ozar: So you can’t. Rebuilding an index is a logged operation, and it’s not like you get the choice to do it in small phases. It’s a big honking transaction. So the classic example is, we had one client who had large databases, but almost all the database is one table. It was like a big, say, you’d have a 1TB database but 800GB of it would just be in one table. Man, you want to rebuild that thing. You better buckle up and take some time. It’s going to be a while and it’s going to need the whole space of the transaction log. So if something goes wrong, you’ve got to be able to rollback.

Erik Darling: Tempdb’s going to suffer. Everything’s going to suffer. A lot of people will just not do that kind of maintenance on an AG anymore just because it gets so difficult. It’s not like you have the option to switch the database into simple or bulk-logged and then run it and then not deal with that. But that doesn’t even help that much because you’re doing it all in one go. So you can’t do that with an AG anyway. I don’t know, the whole thing is just rough; not fun. And when you own an AG, you have to change the way that you think about the way you do things with data.

And it’s not just index rebuilds, like doing large modifications. You have to start batching things really smartly if you’re going to use an AG, not try to update entire tables at once or insert a bajillion rows all at once. You have to change the way that you handle and manage data because if those things fall behind, they get very, very unhappy, very unhappy.

Brent Ozar: Theoretically, there’s a new feature out in SQL Server 2019 and in Azure that will help you with that. I don’t know if it works in Always on Availability Groups or not yet, but it’s accelerated database recovery.

Erik Darling: It works with everything, according to my friend who did stuff with it.

Brent Ozar: Yes, I like that. You can sign up for the preview now. It’s available in the 2019 preview. You just need a magic secret, I would guess trace flag, in order to turn it on…

Erik Darling: Memory bit…

Brent Ozar: The debugger, yeah.

Erik Darling: Hold a magnet next to the…

Brent Ozar: Just flip the debugger, how hard can it be?

Erik Darling: Yeah, and to follow up a little bit on Thomas’s thing about the index rebuilds, it’s like, you better be solving a really, really serious problem with that index rebuild that you can’t solve with something a little bit easier on the server, like update stats or, you know, even a re-org or something, if it’s a smaller table, because re-orgs will choke on big tables. But I would make sure that I’m solving a really, really big problem if I’m taking that much of a chance with my AG falling behind or tipping over or whatever awful things happen when you throw the smallest grain of sand into the cogs of an AG.

Brent Ozar: Gus follows up with, “Do sort results in tempdb help with the space on transaction log?” Not at all, unfortunately.

Erik Darling: Nope, it does help with the space that you would need in the data file to sort the results, but it does not help with the logging of the transaction.

Does sp_BlitzCache have a divide by zero error?

Brent Ozar: And then Thomas asks, “Sometimes I run sp_BlitzCache and I get a divide by zero error. Any thoughts there?”

Erik Darling: Yes, use the latest version where that error was fixed.

Brent Ozar: We fix a lot of stuff. I mean, holy cow, we have a lot that goes through that First Responder Kit. It’s insane how fast we run through fixes in there.

Richie Rump: Well I think it’s all the unit tests you have against those things…

Brent Ozar: Yeah, you catch so many things…

Erik Darling: User tests, user tests. You people are our units and you test things in units for us. Thank you for that, we appreciate it. But yeah, stuff like this, it just makes you really kind of paranoid about when you need to start being more defensive in the way that you write code, you know. Anytime there’s division, make sure you have some protection on that divider side about zeros. If you’re using, like, substring, be really careful about when you might have a negative value passed in that will make that throw an error.

Set yourself up with some protection there so that you don’t just run into these crazy ephemeral errors that are impossible to reproduce, or even to conjure up, to think, like what bad thing could happen here? Because the error he’s talking about was when I added stuff to look at compile memory for a query plan and I was like, there’s no way anyone can have zero compile memory. It’s impossible. You can’t possibly have zero memory to compile a plan with. But you people, you people found a way…

Richie Rump: And that’s when Erik earned his senior developer wings.

Brent Ozar: God, people are creative. Alright, well thanks, everybody, for hanging out with us this week at Office Hours. And next week will be weird. Next week is the PASS Summit. We’ll see if Richie and Tara are around to see if they want to do one. I think Erik’s going to be in-flight or running around Seattle. I’ll be out in Seattle attending the Summit, so…

Erik Darling: I don’t know if I can say where I’m going to be on Wednesday. Is it Public knowledge yet?

Brent Ozar: That means I don’t know where you’re going to be on Wednesday. Oh no, yeah, sure…

Erik Darling: So lovely Claudia Coleman from Quest has sponsored a book signing. So she bought the first and only copy of my book, and she bought a bunch of those. I’m going to be sitting in the vendor hall signing those. So if you’re at PASS and you are too cheap to spend $30 on my book from Amazon, you can get a free copy with my signature, if you want it. No selfies, but you can get a signature and a copy of the book for free.

Tara Kizer: No selfies? What are you, Erika?

Richie Rump: It is a requirement that you take a selfie with Erik if you take his book. You have to take a selfie.

Erik Darling: I will punch you if you selfie in my general area.

Richie Rump: You know what you do? If he won’t take a selfie with you, then you just kind of angle your way out. As you’re walking back with the book, click-click…

Erik Darling: I’m going to wear my Richie mask so no one recognizes me.

Richie Rump: Horrifying. Straight horrifying. That is not attractive.

Erik Darling: Good times.

Brent Ozar: That will be us next week so see y’all later, adios.

Email*
Name*
First Last
Things I want*
- Jan 25 - How to Think Like the Engine (8AM Pacific, 11AM Eastern)
- Feb 1 - Getting Started with the Consulting Toolkit (8AM Pacific, 11AM Eastern)
- Feb 8 - An Introduction to GitHub for DBAs (8AM Pacific, 11AM Eastern)
- Feb 15 - How to Capture Baselines with sp_BlitzFirst (8AM Pacific, 11AM Eastern)
- First Responder Kit (scripts & guides)
- Subscribe to email updates (3-4 per week)
- I would like a pony

The Curse of Cursor Options

November 2, 2018

Execution Plans

Red Skies At Night

I know it’s hard to believe, but I still see a lot of people using cursors when they shouldn’t. Other times, there’s some scary dungeon part of the code that someone wrote eons ago that no one wants to go anywhere near to fix.

Sometimes there’s a decent reason, something like: We have a query that generates a really big result, and we need to update another table with it. When we do it without the cursor, there’s bad locking, it runs for a long time, the transaction log gets huge, etc.

Not bad reasons! I still think there are better ways to do that. But hey.

Not So Fast Forward

When you write cursor code, there are a bunch of options you can choose. One of them is FAST_FORWARD. It’s documented, ahem, thusly:

FAST_FORWARD
Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified.

Friends, Lulu Romans, Countryhams.

OPTIMIZATIONS ARE ENABLED!

We don’t know what they are, but they’re there.

Feed Me, Seymour

Let’s say we have a pretty “big” query to feed our cursor. When we write it and validate the logic, it looks like this:

SELECT u.Id,u.DisplayName, MAX(p.Score) AS TopScore
FROM dbo.Users AS u
JOIN dbo.Posts AS p
    ON u.Id = p.OwnerUserId
WHERE p.PostTypeId = 1
AND   p.Score > 0
AND   u.Reputation > 1000
GROUP BY u.Id, u.DisplayName;

SELECT u.Id,u.DisplayName, MAX(p.Score) AS TopScore

FROM dbo.Users AS u

JOIN dbo.Posts AS p

ON u.Id = p.OwnerUserId

WHERE p.PostTypeId = 1

AND p.Score > 0

AND u.Reputation > 1000

GROUP BY u.Id, u.DisplayName;

And the query plan looks like this:

I’m not an animal

It goes parallel, as we’d expect a “big” query to do, and runs for 3 seconds. I’m not saying there’s nothing we could do here, but let’s roll with it for now.

Let’s Write Some Code!

First, we need to introduce a new table. It’s gonna hold our high scores.

CREATE TABLE dbo.HighQuestionScores
(
    Id INT PRIMARY KEY CLUSTERED,
    DisplayName NVARCHAR(40),
    Score BIGINT
);
GO

CREATE TABLE dbo.HighQuestionScores

(

Id INT PRIMARY KEY CLUSTERED,

DisplayName NVARCHAR(40),

Score BIGINT

);

Now we’re gonna write a stored procedure that’s gonna do a few things.

First Thing

We’re gonna check our high score table for any new Users with a Reputation over 1000

CREATE OR ALTER PROCEDURE dbo.curses
AS 
BEGIN
SET NOCOUNT ON;

INSERT dbo.HighQuestionScores (Id)
SELECT u.Id
FROM dbo.Users AS u
WHERE u.Reputation > 1000
AND NOT EXISTS
(
    SELECT 1/0
    FROM dbo.HighQuestionScores AS h
    WHERE h.Id = u.Id
);

CREATE OR ALTER PROCEDURE dbo.curses

BEGIN

SET NOCOUNT ON;

INSERT dbo.HighQuestionScores (Id)

SELECT u.Id

FROM dbo.Users AS u

WHERE u.Reputation > 1000

AND NOT EXISTS

(

SELECT 1/0

FROM dbo.HighQuestionScores AS h

WHERE h.Id = u.Id

);

Second Thing

We’re going to declare our variables for the variable gods, and open a cursor with OPTIMIZATIONS ENABLED to make sure our optimizations are optimized.

DECLARE @Id INT;
DECLARE @DisplayName NVARCHAR(40);
DECLARE @Score BIGINT;
 
DECLARE crap CURSOR FAST_FORWARD READ_ONLY
FOR

    SELECT u.Id, u.DisplayName, MAX(p.Score) AS TopScore
    FROM dbo.Users AS u
    JOIN dbo.Posts AS p
        ON u.Id = p.OwnerUserId
    WHERE p.PostTypeId = 1
    AND   p.Score > 0
    AND   u.Reputation > 1000
    GROUP BY u.Id, u.DisplayName;
 
OPEN crap;

FETCH crap INTO @Id, @DisplayName, @Score;

DECLARE @Id INT;

DECLARE @DisplayName NVARCHAR(40);

DECLARE @Score BIGINT;

DECLARE crap CURSOR FAST_FORWARD READ_ONLY

FOR

SELECT u.Id, u.DisplayName, MAX(p.Score) AS TopScore

FROM dbo.Users AS u

JOIN dbo.Posts AS p

ON u.Id = p.OwnerUserId

WHERE p.PostTypeId = 1

AND p.Score > 0

AND u.Reputation > 1000

GROUP BY u.Id, u.DisplayName;

OPEN crap;

FETCH crap INTO @Id, @DisplayName, @Score;

Third Thing

We’re gonna update the high score table with new information. This looks a little silly, but people can change their names on the site.

FETCH crap INTO @Id, @DisplayName, @Score;
 
WHILE @@FETCH_STATUS = 0
BEGIN
  
  UPDATE h
      SET h.DisplayName = @DisplayName,
          h.Score = @Score          
  FROM dbo.HighQuestionScores AS h
  WHERE h.Id = @Id

  FETCH crap INTO @Id, @DisplayName, @Score;
END;
 
CLOSE crap;
DEALLOCATE crap;

END
GO

FETCH crap INTO @Id, @DisplayName, @Score;

WHILE @@FETCH_STATUS = 0

BEGIN

UPDATE h

SET h.DisplayName = @DisplayName,

h.Score = @Score

FROM dbo.HighQuestionScores AS h

WHERE h.Id = @Id

FETCH crap INTO @Id, @DisplayName, @Score;

END;

CLOSE crap;

DEALLOCATE crap;

END

Runtime!

Now we’ll run our stored procedure. We’re very cool people, after all.

EXEC dbo.curses;

1	EXEC dbo.curses;

The first thing we’ll notice is that it runs for about 23 seconds.

If we then, as very cool people do, examine the query plan for our procedure, we’ll notice some oddities.

EXEC sp_BlitzCache @StoredProcName = 'curses'

1	EXEC sp_BlitzCache @StoredProcName = 'curses'

The first is that, well, our “big” query to feed the cursor doesn’t go parallel anymore.

Unlovable

Warnings

sp_BlitzCache warns us about this in two ways.

This warning comes from the XML, which is also pretty explicit:

NonParallelPlanReason="NoParallelFastForwardCursor"

1	NonParallelPlanReason="NoParallelFastForwardCursor"

If we go and run our feeder query at MAXDOP 1…

SELECT u.Id,u.DisplayName, MAX(p.Score) AS TopScore
FROM dbo.Users AS u
JOIN dbo.Posts AS p
    ON u.Id = p.OwnerUserId
WHERE p.PostTypeId = 1
AND   p.Score > 0
AND   u.Reputation > 1000
GROUP BY u.Id, u.DisplayName
OPTION(MAXDOP 1);

SELECT u.Id,u.DisplayName, MAX(p.Score) AS TopScore

FROM dbo.Users AS u

JOIN dbo.Posts AS p

ON u.Id = p.OwnerUserId

WHERE p.PostTypeId = 1

AND p.Score > 0

AND u.Reputation > 1000

GROUP BY u.Id, u.DisplayName

OPTION(MAXDOP 1);

We’ll be saddened to learn that our Three Second Masterpiece® has turned into an Eleven Second Turd®

 SQL Server Execution Times:
   CPU time = 10891 ms,  elapsed time = 11459 ms.

1 2	SQL Server Execution Times: CPU time = 10891 ms, elapsed time = 11459 ms.

Better Options?

Cursor options like FORWARD_ONLY, STATIC, and KEYSET can all produce parallel plans, and reduce the total proc runtime to about 15 seconds — remember that it was around 23 seconds with the FAST_FORWARD cursor, with OPTIMIZATIONS ENABLED!

Some optimizations, there, pal.

Thanks for reading!

Save 50-75% Now: The 2018 Black Friday Sale is On!

November 1, 2018

Company News

Five years ago, I had a crazy thought. I liked bargain shopping during Black Friday sales, picking out a new TV or holiday vacation. Why not run a Black Friday sale here on our stuff, too? Over the years, it’s turned into one of my favorite times of the year.

We used to only run it on Black Friday itself, but we got a lot of requests from folks who have slow-moving companies that need time to get approvals lined up. So these days, we run the sale all November long, starting right now:

Take 75% off our recorded video classes. Normally $29-$89, now just $7.25-$22.25! The Recorded Class Season Pass is 75% off too, down from $395 to just $98.75 for your first year.

Take 75% off SQL ConstantCare® – one year of email mentoring from us, plus access to our Recorded Class Season Pass. Normally $895, now $223.75 for your first year.

Save $2,997.50!

Take 50% off many of our live classes – including our legendary Live Class Season Pass, which is down to just $2,997.50! Attend all of Brent & Erik’s live online classes for a year, including Mastering Index Tuning, Mastering Query Tuning, and Mastering Server Tuning.

And save 50% on The Team Membership – that gets you live quarterly meetings with us, email mentoring, recorded training, and Live Class Season Passes for everybody signed up in your team.

New this year: check, invoice, or purchase order payments are welcome. We heard from a lot of folks that their purchasing departments didn’t want to use credit cards, so there’s a check/invoice/PO method you can use during checkout. However, 2 gotchas: we must receive your check before the Black Friday is over, and no, we’re not filling out any paperwork to become a vendor. (You can contact us for a W9, though.)

Enjoy, and happy holidays!

How Check Constraints MIGHT Improve Your Queries and Missing Index Requests

October 31, 2018

Execution Plans, Indexing

The more SQL Server knows about your data, the better your query plans can get.

Say you’ve got an app that’s designed to store multiple companies in a single database – but you don’t actually use it that way. All of the data in a given database is actually for the same company.

We’ll use the Stack Overflow 2010 (10GB) database as an example – say the Posts table (questions & answers) has a CompanyCode column that would let us store multiple companies in there. Here’s the setup script. The end result is a Posts_MultiTenant table with a leading field called CompanyCode, and all of the rows have ‘BOU’ as our company:

All of our app’s queries have CompanyCode in their WHERE clauses – even though that doesn’t really do any filtering, but the app is a 3rd party app that always filters for just the company you’re logged into:

SELECT Id
  FROM dbo.Posts_MultiTenant
  WHERE OwnerUserId = 26837
    AND PostTypeId = 1
    AND CompanyCode = 'BOU';

SELECT Id
  FROM dbo.Posts_MultiTenant
  WHERE PostTypeId = 3
    AND LastEditorUserId = 15639
    AND CompanyCode = 'BOU';

SELECT TOP 100 Id
  FROM dbo.Posts_MultiTenant
  WHERE LastActivityDate >= '2018-08-01'
    AND CompanyCode = 'BOU'
  ORDER BY FavoriteCount DESC;

SELECT Id

FROM dbo.Posts_MultiTenant

WHERE OwnerUserId = 26837

AND PostTypeId = 1

AND CompanyCode = 'BOU';

SELECT Id

FROM dbo.Posts_MultiTenant

WHERE PostTypeId = 3

AND LastEditorUserId = 15639

AND CompanyCode = 'BOU';

SELECT TOP 100 Id

FROM dbo.Posts_MultiTenant

WHERE LastActivityDate >= '2018-08-01'

AND CompanyCode = 'BOU'

ORDER BY FavoriteCount DESC;

Their actual execution plans get missing index requests, which at first seems like a good thing:

Until you read the missing index requests:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Posts_MultiTenant] ([CompanyCode],[OwnerUserId],[PostTypeId])
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Posts_MultiTenant] ([CompanyCode],[LastEditorUserId],[PostTypeId])
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Posts_MultiTenant] ([CompanyCode],[LastActivityDate])
INCLUDE ([FavoriteCount])
GO

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON [dbo].[Posts_MultiTenant] ([CompanyCode],[OwnerUserId],[PostTypeId])

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON [dbo].[Posts_MultiTenant] ([CompanyCode],[LastEditorUserId],[PostTypeId])

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON [dbo].[Posts_MultiTenant] ([CompanyCode],[LastActivityDate])

INCLUDE ([FavoriteCount])

They all lead with the least useful field because CompanyCode happened to be one of the first fields in our table – that’s how missing indexes are generated, based on field order.

That creates a few different problems:

The indexes are larger than necessary (since they include a useless field)
The statistics are nearly worthless (since they’re based on a leading field that isn’t selective)
The indexes can be less useful for searching, depending on your field order and sorting

That’s where check constraints come in.

Tell SQL Server more about your data by building a constraint, a business rule about your data:

ALTER TABLE dbo.Posts_MultiTenant
WITH CHECK ADD CONSTRAINT CK_CompanyCode_BOU
CHECK (CompanyCode = 'BOU');

ALTER TABLE dbo.Posts_MultiTenant

WITH CHECK ADD CONSTRAINT CK_CompanyCode_BOU

CHECK (CompanyCode = 'BOU');

And suddenly SQL Server levels up. If I look for non-BOU users, SQL Server knows there can’t possibly be any data:

So it bails out with a constant scan, not even doing any logical reads. Woohoo, faster queries!

Now run our 3 selects again and check out the actual plans:

And the index recommendations are different this time – SQL Server knows there’s simply no purpose in leading with CompanyCode since it’s not selective:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Posts_MultiTenant] ([CompanyCode],[OwnerUserId],[PostTypeId])
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Posts_MultiTenant] ([CompanyCode],[LastEditorUserId],[PostTypeId])
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Posts_MultiTenant] ([LastActivityDate])
INCLUDE ([FavoriteCount])
GO

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON [dbo].[Posts_MultiTenant] ([CompanyCode],[OwnerUserId],[PostTypeId])

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON [dbo].[Posts_MultiTenant] ([CompanyCode],[LastEditorUserId],[PostTypeId])

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON [dbo].[Posts_MultiTenant] ([LastActivityDate])

INCLUDE ([FavoriteCount])

Er…well…maybe not. The first two index recommendations still lead with CompanyCode, but at least the third one doesn’t! Query tuning geeks will be interested to see the actual plans and note that all 3 got full optimization with no early timeouts.

Clippy’s recommendations are rarely perfect.

But on the bright side, you would never use Clippy’s recommended indexes anyway – if you’ve been to Mastering Index Tuning, you know how to hand-craft index recommendations based on the query plan. You might create these instead:

CREATE NONCLUSTERED INDEX IX_OwnerUserId_PostTypeId
ON [dbo].[Posts_MultiTenant] ([OwnerUserId],[PostTypeId])
GO
CREATE NONCLUSTERED INDEX IX_LastEditorUserId_PostTypeId
ON [dbo].[Posts_MultiTenant] ([LastEditorUserId],[PostTypeId])
GO
CREATE NONCLUSTERED INDEX IX_LastActivityDate_FavoriteCount
ON [dbo].[Posts_MultiTenant] ([LastActivityDate],[FavoriteCount])
GO

CREATE NONCLUSTERED INDEX IX_OwnerUserId_PostTypeId

ON [dbo].[Posts_MultiTenant] ([OwnerUserId],[PostTypeId])

CREATE NONCLUSTERED INDEX IX_LastEditorUserId_PostTypeId

ON [dbo].[Posts_MultiTenant] ([LastEditorUserId],[PostTypeId])

CREATE NONCLUSTERED INDEX IX_LastActivityDate_FavoriteCount

ON [dbo].[Posts_MultiTenant] ([LastActivityDate],[FavoriteCount])

Note that none of those indexes have CompanyCode in them. I shouldn’t need it – I have a constraint, right? So let’s run our three selects again, and see if SQL Server uses these covering indexes, or does a key lookup to fetch the CompanyCode:

The first two queries still do an index seek plus a key lookup, but the third one wakes up, pays attention to the constraint, and knows it doesn’t need to check CompanyCode.

Why are 2 of the 3 plans bad?

Keen readers of the actual query plans will note that the first two queries were autoparameterized – meaning, SQL Server turned this:

WHERE CompanyCode = 'BOU'

1	WHERE CompanyCode = 'BOU'

Into this:

WHERE CompanyCode = @SomeVariableThatCouldBeAbsolutelyAnything

1	WHERE CompanyCode = @SomeVariableThatCouldBeAbsolutelyAnything

Because it believed it didn’t matter which string I use for CompanyCode. It made this decision early in the query optimization process – if you dive into the properties for the plan, you’ll notice that on the first two plans, SQL Server left the office early to go drinking:

SQL Server believed it’d found a good enough query plan, and called time on optimization. In fairness, these are really fast queries – in most scenarios, they’d be good enough.

But if I happen to get plan reuse & parameter sniffing, and I run those for different parameters – like, say, the most popular IDs in this table:

SELECT Id
  FROM dbo.Posts_MultiTenant
  WHERE OwnerUserId = 0
    AND PostTypeId = 1
    AND CompanyCode = 'BOU';

SELECT Id
  FROM dbo.Posts_MultiTenant
  WHERE PostTypeId = 1
    AND LastEditorUserId = 0
    AND CompanyCode = 'BOU';

SELECT Id

FROM dbo.Posts_MultiTenant

WHERE OwnerUserId = 0

AND PostTypeId = 1

AND CompanyCode = 'BOU';

SELECT Id

FROM dbo.Posts_MultiTenant

WHERE PostTypeId = 1

AND LastEditorUserId = 0

AND CompanyCode = 'BOU';

Then that autoparameterization comes back to haunt me in the actual plans. SQL Server ignores the constraint, does a ton of key lookups, and hilariously, still asks for the missing index starting with CompanyCode!

Constraints help, but they’re not perfect.

The more expensive SQL Server thinks your queries are,
the further it goes into optimization,
and the more likely it is to consider things like constraints.

It won’t suggest constraints to you because it doesn’t know what you plan to put into the database in the first place. That’s your job. Implement constraints, and your tougher queries may – but also may not – take those constraints into account for missing index requests and execution plans. When they don’t, it’s time to roll up your sleeves and master index & query tuning by hand.

SUM, AVG, and arithmetic overflow

October 30, 2018

T-SQL

You Shoulda Brought A Bigger Int

Sometimes you run a query, and everything goes fine.

For a while. For example, if I run this query in the 2010 copy of Stack Overflow, it finishes pretty quickly, and without error.

SELECT u.Id, 
       u.DisplayName, 
	   SUM(p.Score) AS SumPostScore,
	   AVG(c.Score) AS SumCommentScore
FROM dbo.Users AS u
JOIN dbo.Posts AS p
    ON u.Id = p.OwnerUserId
JOIN dbo.Comments AS c
    ON u.Id = c.UserId
WHERE u.Reputation >= 10000
AND   p.PostTypeId = 2
AND   p.Score >= 10
AND   c.Score >= 1
GROUP BY u.Id, 
         u.DisplayName

SELECT u.Id,

u.DisplayName,

SUM(p.Score) AS SumPostScore,

AVG(c.Score) AS SumCommentScore

FROM dbo.Users AS u

JOIN dbo.Posts AS p

ON u.Id = p.OwnerUserId

JOIN dbo.Comments AS c

ON u.Id = c.UserId

WHERE u.Reputation >= 10000

AND p.PostTypeId = 2

AND p.Score >= 10

AND c.Score >= 1

GROUP BY u.Id,

u.DisplayName

If I run this query in the full version, it runs for a minute and a half, and then returns an error:

Msg 8115, Level 16, State 2, Line 4
Arithmetic overflow error converting expression to data type int.

1 2	Msg 8115, Level 16, State 2, Line 4 Arithmetic overflow error converting expression to data type int.

They Shoot Horses, Don’t They?

Now, we’ve had COUNT_BIG for ages. Heck, we even need to use it in indexed views when they perform an aggregation. More recently, we got DATEDIFF_BIG. These functions allow for larger integers to be handled without additional math that I hate.

If one day you got an email that this query suddenly started failing, and you had to track down the error message, what would you do?

I’d probably wish I wore my dark pants and start running DBCC CHECKDB.

I kid, I kid.

There are no pants here.

Anyway, it’s easy enough to fix.

SELECT u.Id, 
       u.DisplayName, 
	   SUM(CONVERT(BIGINT, p.Score)) AS SumPostScore,
	   AVG(CONVERT(BIGINT, c.Score)) AS SumCommentScore
FROM dbo.Users AS u
JOIN dbo.Posts AS p
    ON u.Id = p.OwnerUserId
JOIN dbo.Comments AS c
    ON u.Id = c.UserId
WHERE u.Reputation >= 10000
AND   p.PostTypeId = 2
AND   p.Score >= 10
AND   c.Score >= 1
GROUP BY u.Id, 
         u.DisplayName

SELECT u.Id,

u.DisplayName,

SUM(CONVERT(BIGINT, p.Score)) AS SumPostScore,

AVG(CONVERT(BIGINT, c.Score)) AS SumCommentScore

FROM dbo.Users AS u

JOIN dbo.Posts AS p

ON u.Id = p.OwnerUserId

JOIN dbo.Comments AS c

ON u.Id = c.UserId

WHERE u.Reputation >= 10000

AND p.PostTypeId = 2

AND p.Score >= 10

AND c.Score >= 1

GROUP BY u.Id,

u.DisplayName

You just have to lob a convert to bigint in there.

Data Grows, Query Breaks

This is a pretty terrible situation to have to deal with, especially if you have a lot of queries that perform aggregations like this.

Tracking them all down, and adding in fixes is arduous, and there’s not a solution out there that doesn’t require changing code.

I was going to open a User Voice item about this, but I wanted to get reader feedback, first, because there are a few different ways to address this that I can think of quickly.

Add _BIG functions for SUM, AVG, etc.
Add cultures similar to CONVERT to indicate int, bigint, or decimal
Change the functions to automatically convert when necessary
Make the default output a BIGINT

There are arguments for and against all of these, but leave a comment with which you think would be best, or your own.

Thanks for reading!

You read reviews before you buy training, right?

October 29, 2018

Company News

Brent Ozar Unlimited Team

Confession time: I love buying stuff.

It’s not so much the buying as it is the comparing. I go to The Wirecutter, I read every review on Amazon, and I make little Excel spreadsheets to show differences between products.

So what about when you buy database training? Do you read the reviews? If you’ve been considering classes at a local center, do yourself a favor and read their Yelp reviews.

Customers tell me that they’ve been to training classes at local companies only to find out:

The instructor was just reading from a book somebody else wrote
The material was outdated
The instructor couldn’t answer detailed questions because they had never done the job they were trying to teach
The class focused on passing certification tests that didn’t match up to real-world problems

Our training is way ahead.

We’re proud that we know our stuff, we do these jobs for a living, and we love answering questions from students. That’s why we encourage past students to post reviews on our training classes, and they say things like:

“What an fun, fact filled, enjoyable class. I’ve been avoiding PowerShell for years. Wish now I hadn’t. The way Drew steps through things is really helpful. Everything builds on something else and ends up with a very practical application of the tool being used with SQL Server.” – Jay Falck about PowerShell for Database Administrators

“As they say the proof is in the pudding. Using techniques and queries from this class I was able to increase performance 3 fold, decrease the database size 2 fold. The primary lesson learned was throw out everything you thought you knew about indexing, (start from scratch..delete all your existing indexes..ugh) and most pointedly forget anything about index profiling you “learned” from Microsoft. These are paramount concepts in database indexing and are a must for every DBA or software developer.” – Carl Bondeson about Practical Real World Performance Tuning

“Great class! I knew practically nothing about Power BI before the class other than how to use the FirstResponderKit Power BI template. Lab section was constructed in a way that everyone could go at their own speed and while I was able to complete in the allotted time, I will be repeating the process several more times to gain proficiency. I would like to request a follow up class where we could present our work with specific questions and/or an Advanced class to build on what we learned. Well worth the expense! Many thanks to Steph and Oz and also to Brent for making it possible.” – Greg Smith about Dashboard in a Day

“As a self employed BI professional in small projects, I tend to figure it out all by myself. I used this course to assert my own build, integrate and deploy solution. Alex Yates is a great teacher and he inspired me to adapt to best practices. As a bonus, you learn to appreciate the Red Gate SQL Server tools.” – Frans van der Geer about Database DevOps

“Brent is a truly amazing instructor. I always find myself glued to my monitor during class in awe at how knowledgeable he is and how he can explain things so clearly and make each subject that he teaches so interesting. He always knows how to deliver the material in a fun and unique way that helps to more easily grasp the ideas and concepts. Erik and Brent are a great team and I found myself learning so much from them along with laughing out loud throughout the training which makes these classes not only full of great information but delivered in an exciting and entertaining way. These classes are NEVER dry, dull, or boring.” – Heather Hahn about Mastering Query Tuning

And here’s what Bert Wagner said:

I love our classes.

But don’t take my word for it. Go read the reviews.

[Video] Office Hours 2018/10/24 (With Transcriptions)

October 28, 2018

Videos

Here’s the video on YouTube:

This week, Brent, Tara, and Richie discuss increasing a table’s varchar field from 1 to 2, predicting how long CHECKDB should take, SQL Server configuration options, reporting, reducing high waits, analysis services for SQL Azure, advice to first time PASS attendees, compatibility levels, cardinality estimator, PowerShell, and Tara’s hairdo.

You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go.

If you prefer to listen to the audio:

Podcast: Play in new window | Download

Enjoy the Podcast?

Don’t miss an episode, subscribe via iTunes, Stitcher or RSS.
Leave us a review in iTunes

Office Hours Webcast – 2018-10-24

Should I use SSIS to manage AG backups?

Brent Ozar: Let’s see, Josh asks, “We have a suggestion for backup jobs to use an SSIS package around to move the jobs to whatever node is the primary in an Availability Group. Have you seen this setup and can it work well?”

Tara Kizer: I haven’t seen that for the Availability Groups that I’ve supported. We had two different methods at three companies that I had Availability Groups. But at the last one, we just had a server that would run the backups and it would just point to the listener name. So all of our backups, index maintenance, anything that needed to connect to a specific replica, we would just put the jobs on that server instead and it would be a SQL CMD and it would point to the listener name. And we did that for the backups because we wanted the backups to be on the primary replica. I don’t necessarily agree with offloading such a critical task.

Brent Ozar: And why not?

Tara Kizer: Well even on a synchronous replica, it’s not completely up to date. So that is just a good idea if your RPO goal is so low.

Brent Ozar: Between that advice too, and if you think about an SSIS package that would move stuff around, there’s going to be a delay there. Like, your backups won’t be up to date until the SSIS package moves a job around. And if that job process breaks, you’re not getting backups. I’d be like, I can’t unsubscribe fast enough from that plan. What I would do there instead is Ola Hallengren’s stuff. Ola Hallengren’s backup scripts will automatically run on every replica and just backup wherever you tell it to; like, if you want to prefer a secondary or if you want to prefer the primary. Configuration’s not super simple on Always On Availability Groups, but it works.

I need to go from VARCHAR(1) to VARCHAR(2)…

Brent Ozar: Pablo says, “Hello, friends…” Hello Pablo, “I have an 800GB table that needs to increase the size of a varchar field from one to two. What way would you recommend doing that to reduce impact?” Isn’t that an instant change?

Tara Kizer: Yeah, I was going to say, I don’t know that this is going to take a while. Run that on a test box.

Brent Ozar: Yeah, I’m pretty sure that that’s going to be instant.

Tara Kizer: Yeah, and if it isn’t, I would probably just move the column to another table. I would cheat because your table’s just so big. Remove the column – I realize that there’s a huge impact there, but…

Brent Ozar: Somebody had a really good post just recently about a switching cups way to do it. What you do is add a new column with the new data type that you want, but with a different name. You put in a trigger so that whenever an update happens, it sets the new column over to the old value’s name. Then you go through and roll through in batches of like 4000 rows at a time, gradually updating the new column. Then you switch column names.

Tara Kizer: Oh, I like it. [crosstalk] I mean, it’s 800GB. I mean, we don’t even know the row count, because maybe it’s 800GB because of data, you know, the size of a row.

Brent Ozar: Yeah, let’s try it and see. And I’m just going to grab, out of the Stack Overflow post table, I’m just going to grab one of the columns post-only because – oh, you know what I’ll do, find out if votes has a varchar in it. No, it doesn’t. Posts has a varchar and posts has a decent number of rows in this. I’m going to change title from nvarchar 250 to nvarchar251. Alter column, and then I’m going to freeze because I get a spinning beach ball. Oh, that’s excellent, c-c-c-c-column. It’s like some kind of pop song.

What did I say I was going to do? Title nvarchar 251, and let’s see if that happens instantly. So the next thing I’m going to do is switch into the right database, then I’m going to run it. There you go. Yeah, that’s a metadata change. It should happen instantly, regardless of the number of rows in the table. Love it when we can give people good news instead of bad news.

Richie Rump: It doesn’t happen often.

Brent Ozar: It does not happen often.

How long will CHECKDB take?

Brent Ozar: So next up, Brian says – Brian, this is a great question, “Is there a good way to predict how long CHECKDB should take? I’ve tried to space out my various jobs, but sometimes they step on each other’s toes with my jobs running into business hours on the next morning.” Any ideas, Tara, Richie?

Tara Kizer: I mean, you know, CHECKDB, I don’t even like to run that on a production server. I offload that task to another box which, obviously, you have to license that other box. But I like a box that maybe doesn’t have a lot of hardware, but it can churn through just running CHECKDB in all the databases. Eventually it completes, and maybe it’s doing the work for more than one server, but it might have lesser cores for licensing reasons, because you do need to license it.

Richie Rump: I mean, do you need to license that?

Tara Kizer: You do.

Richie Rump: Wouldn’t the development work?

Tara Kizer: No, because you’re offloading a production task. That’s the key there.

Richie Rump: Semantics.

Brent Ozar: If you just happened to be restoring it every night into your development environment and you wanted to make sure that your development environment was okay, that’s totally still not legal, but there we go.

Brent Ozar: Marci says, “Hey, did you get my question?” Yes.

What should my setup defaults be?

Brent Ozar: Jason says, “When you helped us configure our SQL Server 2012 database a few years ago, you recommended that we start with MAXDOP 8, cost threshold at 50, and eight tempdb data files and then tweak from there. We’re not switching to 2016. Do you recommend we start those same settings?”

Tara Kizer: Yeah.

Brent Ozar: Assuming you’re going to the same size or bigger of a box. Heaven forbid you’re going to a two core box or something like that.

Tara Kizer: I don’t know if this is in our set of checklists. I don’t think it is. But on 2014 and lower, it’s recommended to have trace flags 1117 and 1118, but on 2016 and greater, you don’t need that anymore because it’s on by default for tempdb specifically. The trace flag on older versions would apply to all databases, unfortunately, but still recommended. But yeah, I don’t know if Jason would have that in place anyway, but that would be the one change if I’m setting up a new 2016 or 2017 server; I’m just not going to be putting those trace flags in place like I would on older versions.

Is the color right on my monitor?

Brent Ozar: Michael says he is, “In shock and awe of Tara’s new hairdo.”

Tara Kizer: Me too. It’s going away this evening. I have an appointment. Not all of it, it’s just too much red, you know. It needs to have a little bit of brown here because when I’m on these calls with my clients, I see the webcam picture and it’s driving me just bat-shit crazy.

Brent Ozar: Really? Oh…

Tara Kizer: Yeah, because you don’t have to look at yourself all the time in your normal day. You look at yourself in the mirror when you’re in the bathroom and that’s about it. But the webcam is like, oh my god, my hair is so red.

Richie Rump: But we do have to look at Brent all the time. It’s that required photo of Brent on our desk.

Brent Ozar: It’s got the little googly eyes on it, they follow you around the room.

Richie Rump: Oh really, I thought those were cameras and you were just watching us all the time.

Brent Ozar: I don’t want to see you people. You don’t even put on clothes for your job. You’re still wearing your PJs from yesterday. I know how this works.

Tara Kizer: I did actually shower this morning. I don’t have my PJs on today. I normally do. My PJ shirt clashes with the hair because it’s like a pinkish color, so it’s like oh my god, I can’t wear that.

We have a corrupt SSAS cube….

Brent Ozar: Marci says, “We have an SSAS cube whose refresh job is in SQL Server agent and it failed with physical file corruption…” why don’t I read these questions before? Marci… “I have job notifications turned on so I was aware of the failure, but I want other email recipients to see the job log as well without having to log into SSMS. SQL Server doesn’t appear to have an easy button to include the agent job log in an email notification, or am I missing something?”

Tara Kizer: You could set up something fancier for the failed jobs, but the failed notifications, it’s not going to send that out to you. You would need to go into SSMS. But how often are jobs failing? You know, this type of job…

Brent Ozar: Wow, that’s – okay, now I’m going to get really fancy and terrible. You could do something like, since you’re using SSIS already, you could look in MSDB’s agent job history tables and if there’s a failed job, you could go look at the file, pick it up, and email it to somebody. That, I think, would be relatively trivial as an SSIS job, not an agent job, to not T-SQL code. I wouldn’t want to do it in T-SQL code; like trying to pull XP command shell and get a file. But I love the idea, that’s really cool.

Richie Rump: I’m sure you could do it in PowerShell with some salt or Salt Bae or something.

Brent Ozar: And if you need any help with that, it’s Richie@BrentOzar.com…

Richie Rump: Actually, I just changed my name to Erik Darling…

Brent Ozar: It’s been a while since we’ve had Erik do anything with PowerShell. He’s not on the call. We should probably assign him something.

Richie Rump: Totally.

Tara Kizer: I’m the only one in the company that likes PowerShell, so you can send it to me. And I don’t really necessarily like it, I just don’t hate it. You guys hate it.

Richie Rump: I don’t hate it. I just don’t think it’s the right tool for every job. I mean, I take a look at it from a programmatic standpoint and a developer standpoint and it’s like, why do I want to do everything in this? That doesn’t make any sort of sense, which is what a lot of DBAs kind of force. Oh look, here’s my hammer; the PowerShell hammer, everything’s a nail.

Brent Ozar: And we’ve had that same feeling about all golden hammers. We had a question a while ago from someone who wanted to use T-SQL to do bulk copy inserts from files with changing columns and all this. And we were like, no, there are different hammers for different purposes. SSIS is a great hammer for that…

Richie Rump: Well I mean, it even works for SQL Server features, right, cues and things, let me go in and just – what is that silly file thing that they had a few years ago?

Brent Ozar: Well there were two; file stream and file table…

Richie Rump: yeah, file stream, it was like, what? No, no, stop, just stop.

Brent Ozar: Great moments and bad ideas. I say that casually and then this is when all my Microsoft friends come out with knives and they’re like, what do you mean the feature I implemented is a bad idea? Well it just wasn’t a great idea. It wasn’t your best idea.

Richie Rump: Well I mean, the use case is a very thin and narrow use case where it is a good idea. And then people take a look at that and say, oh it’s a new feature, then I can use it for everything. And that’s when we start getting problems and they start calling us and…

Brent Ozar: And it seems like Books Online never says, this is a bad idea; this particular feature is a bad idea in these circumstances. So I was thinking the other day, the pages for scalar functions, table variables, et cetera, in Books Online, they should really have a disclaimer at the top; hey, bud, before you use this feature, here are just things to be aware of. And I thought, well it’s also open source. I could go write that and check that in as a pull request. And I think we all know how that story would end.

Tara Kizer: The thing is about Books Online, MSDN, is that at least for my usage, I don’t even look at the top of the page. I immediately scroll down to the examples. So I’m going to miss that it’s a bad idea anyway. I am only going out there to look for examples.

Brent Ozar: Yeah, so the other thing we should do is, down in the code examples, we should put in code that specifically doesn’t work. Like, make it break so people go, I copy pasted it and it didn’t work… then they won’t read the directions and they’ll just stop.

Can I ignore CLR_SEMAPHORE waits?

Brent Ozar: Joe says, “I’m seeing very high waits for CLR_SEMAPHORE; 300 seconds in a 30 second sample. Are these truly safe to ignore? Is there anything I need to do to reduce these?” For this one, check Paul Randal’s wait stats library. Anytime someone asks a wait stats question, I always want to make sure that they know about that. So if you fire open the web browser – and usually, even if you just search for the wait type – Richie is moving his webcam.

Richie Rump: Yeah, it was crooked and now I’m trying to fix it, and then I’m just like, that’s a kind of cool…

Tara Kizer: Now he’s on a cruise ship…

Brent Ozar: Walking around drunk.

Tara Kizer: This one’s on his ignore list, I’m pretty sure. Yeah, filter out. So we had it filtered out, but we added it back in so it was viewable because I had one client where they were having really bad CLR waits that they needed to address. I didn’t even notice them because it was on the ignore list. And I believe it was the Redgate free monitoring tool that they were using, which was showing that there was an issue here. And they’re like, yeah we use CLR. I was like, oh… So we added it back in because of this one specific client. So anytime I see it as a high wait – because I do see it a lot on client machines as a high wait – I’m like, are you using CLR? And almost always, the answer is no. And if that’s the case, this is all built-in SQL Server stuff, you know, so contact Microsoft if there’s a lot of waiting time. But as Paul says, it’s mostly ignorable.

Richie Rump: So CLR is another one of those, hey it works great in these narrow use cases, and then people start using it for all these other things and it’s like…

Brent Ozar: Yeah, absolutely.

Does Power BI need SSAS or Azure?

Brent Ozar: Steve says, “Here’s a stupid question. In PowerBI, do you need an Analysis Services on-premises or SQL Azure to have your users start using this? Everywhere I’ve read says it uses SSAS or Azure.” When it first got started, it was really focused on those databases, but they’ve very rapidly added compatibility with lots of other databases, for example, I use it with Postgres and Amazon RDS. So you definitely can throw all kinds of data into it. You may need an on-premises gateway server, which is just a VM that the PowerBI services will connect to your gateway server, and then go pull the databases that you want. For example, Microsoft can’t directly query Postgres. They have to go through the gateway server to go do it. But yeah, you don’t have to have analysis services or SQL Azure.

I’m a first time PASS Summit attendee. Now what?

Brent Ozar: Colin asks a great question that we’ll all have to switch around and answer differently. Colin says, “What advice would ya’ll give a first time PASS attendee?” Richie, you’re making faces. What would be the first thing that you would…

Richie Rump: Oh, go to all the parties. Don’t worry about tickets. Got to every party. I wouldn’t even worry about attending all the sessions either. I would star the things I’m really curious about or really interested in and go to those. And as you’ll see, as you go on through the conference, you’ll hear things or you’ll talk to people, oh I was really doing this, or you’ll meet speakers, oh I’m really talking about this… And then, fill your schedule up with that stuff. Also, don’t forget the hallway track. The hallway track is just you wandering around meeting people, talking with them, because they’re skipping stuff like you are and they have time and you could find some really good stuff.

I remember, I was at the Microsoft booth and I got this one on one conversation with one of the Microsoftees about parallel data warehousing. I knew zero, and when I walked out of there, I’m like, I know exactly what it’s doing. That thing is costly but kind of cool.

Brent Ozar: Tara, how about you?

Tara Kizer: I agree. Make sure you go to all the parties. I mean, PASS, you’re going to learn some stuff in the presentations that you pick, but a lot of PASS is networking and meeting people and hearing about their environments, and socializing, non-SQL Server stuff. I think PASS still has the newbie – I think it’s like Tuesday evening. The main conference starts on Wednesday. So on Tuesday evening, they have a newbie mixer type thing and generally it’s all first-timers there. So you’ll meet first-timers. And if you ever go to PASS again, you might see some of the same people because a lot of people, once they go to PASS, they want to keep going to PASS. It is a fun conference and, you know, your company can pay for it because you are learning as well.

Brent Ozar: And if your company does pay, one thing to do is go to your boss with the schedule and say, can you pick two sessions or three sessions that you want me in, that when I’ll come back, I’ll debrief the rest of the team on. So that way, it makes the manager feel like they’re getting money’s worth out of the conference. Then, go sit in those sessions, take notes, and when you come back, go kind of give like a five minute, here are the things that I learned and here’s where to go to learn more, recap. Because that way, then your boss is more likely to send you to the conference next year instead of somebody else who came back and didn’t share any knowledge.

Richie Rump: The other thing is, don’t be afraid to bail on sessions. If in the first ten minutes, you’re not getting out what you thought you were getting out of this thing, dip. Leave, go to something else. Don’t waste your time there on something that maybe it wasn’t fleshed out correctly in the description. Maybe it was a kind of bait and switch type thing with the title. I mean, there’s a lot of things that go on. Don’t be afraid just to get up and walk out. No one’s going to be offended. And if they are, then it’s Buck Woody, and you sit your butt back down and you listen to Buck.

Tara Kizer: And it’s awkward though if it’s not a highly attended session, because they’re going to see you.

Brent Ozar: And they’re wailing as you’re walking out the door…

Tara Kizer: One of my 20 people have left…

Should I change my compatibility levels?

Brent Ozar: Brian says, “I’ve noticed on our server that we have a ton of databases with different compatibility levels. I guess they retained the original compatibility level from earlier. Should I change this? Is it a good practice to just change compatibility levels when I upgrade the database, other than the whole cardinality estimator thing in 2014?”

Tara Kizer: Oh, he knows about the cardinality estimator. So that is the big risk for me. Microsoft has a whitepaper on that cardinality estimator topic, and it says do not change your CE to 2014 or greater because you need to spend the time testing performance outside of production. It says, if you do not have time to do a full test, then do not change the CE. This is a Microsoft whitepaper. It’s a fairly recent whitepaper I think, so there’s risks there. You’re saying except for the cardinality estimator thing, well, you do need a test. Depending on how old the SQL Server compatibility level, you already could have some T-SQL errors, so definitely advised to test that out.

What logins should I use for Power BI reports?

Brent Ozar: Nika says, “We have a client wanting to connect PowerBI to our production instance for reporting. Would you use a SQL login or a Windows login, and what difference does that make as far as security?” generally, I would use a Windows login. I would use either at-person’s login, so you can identify who’s running the crappy queries; assuming it’s the PowerBI desktop that they’re connecting in with. And then that way, you can shut off their logins when they leave. You’ll also hear people say, if it’s groups of people using PowerBI, you’re better off with a Windows group, like an active directory group, so then your helpdesk can control those permissions without getting you involved every time.

Is Brent wearing PJs?

Brent Ozar: Marci says, “I was wondering if Brent is wearing red pajamas because his red doesn’t show.” I do have both red and blue pajamas, but I’m not wearing my pajamas today at work. I have a huge wall of windows right over there, so it would be awkward for me to be wearing my PJs where all of downtown San Diego can see me. Not that it would stop me, but it would be…

Richie Rump: That confused me. I’m like, why?

Brent Ozar: Not that it would stop me.

When would filestream be good?

Brent Ozar: Ron asks a tricky question, “When would filestream be good?”

Tara Kizer: I’ve no idea. I’ve never looked into it.

Brent Ozar: The one thing that I’ve seen – the use case that people will say is, when you absolutely have to have transactional consistency on a group of files that must be restored to the same point in time as the database. But frick, man, when you start putting files in the database, there go your backup sizes. So just be really careful. Yeah, so many bad things there.

Can I run Ola Hallengren’s scripts from another server?

Brent Ozar: Brain says, following up on his question, he’s using Ola Hallengren’s scripts for database … “Can that be set to run from a different server?” Dude, so here’s the thing; when you call scripts across servers, you’ve got to make doggone sure that that call is running every time. Because if that call stops working and you don’t get any job failure emails, you’re going to assume everything’s okay when it’s not. So can things be called from another server? Absolutely, you can set up agents centrally on one server to call jobs across other places. You can do linked server calls. There’s lots of tricks you can do. But just make sure that whenever you do it, that it’s bulletproof reliable.

Can I export query results to Excel with PowerShell?

Brent Ozar: Cee asks, “Speaking of PowerShell, is there a quick way to export query results to an Excel worksheet?” I don’t know…

Richie Rump: Copy paste?

Tara Kizer: there is because PowerShell supports the CSV output type and Excel can automatically read CSV.

Brent Ozar: So we’ll Google real fast because I have a feeling that the dbatools have it. Export query results to Excel… there’s a script. There you go, there’s a script gallery to do it. I’m surprised that there’s not a thing in dbatools to do it.

Tara KIzer: I think if you had switched that to CSV, it might have shown up.

Brent Ozar: That’s probably true…

Richie Rump: PowerShell hammers.

Tara Kizer: Maybe … is not good on their website.

Brent Ozar: On dbatools, maybe? But there’s other scripts to do it, so that’s cool. Oh, export CSV, that’s kind of cool.

Tara Kizer: Yeah, I was going to say, I think that is part of the language.

Brent Ozar: Makes sense.

How do I configure Ola Hallengren’s scripts?

Brent Ozar: And then last but not least, J.H. says, “I downloaded Ola Hallengren’s scripts and I want to do a test run on just one database. The script is quite lengthy and a bit confusing. Where in the script do I tweak this?” It’s in the call parameters, so when the stored procedure has parameters for CHECKDB or index optimize, whichever one you want to do, and there’s a databases list and you can pass in databases by name, you can pass in exclusions, all kinds of stuff, so very easy to do there.

Richie Rump: I mean, going back to the Excel thing, it’s a lot more complex than just a CSV. I mean, if you want different queries on different tabs and if you want [crosstalk] adding and if you want – I mean, there’s a ton of stuff going on inside of a zip file, which we would call an Excel SX file.

So I don’t know why I would have any knowledge of any of that around here. Someone around here thought it was a good idea to output stuff into Excel. I’ve never heard of such…

Tara Kizer: I can’t do my job without that file, by the way. So if Brent ever fires me for whatever reason, I have to still use that.

Brent Ozar: When Richie first started, when we first brought him on, I said, okay here’s the thing that I need you to do. I don’t want you to spend more than one to three months or something on it, but it needs to run all of our diagnostic scripts and put the script results in different tabs. So it would have sp_Blitz on one tab, sp_Blitzcache on another tab. But as soon as you say export stuff to Excel, not only is it complicated in terms of dumping out the results, but there’s some things that won’t work in Excel. For example, query plans. And so Richie wrote this tool that will loop through query plans. Also, if the query plan is in the column for the output, it will go dump that into a separate file and all that.

And I know what y’all are going to ask; can we have that utility? And the answer is no. When we first did it, I was like, this is going to get us by for two years, maybe three years worth of consulting, because next, I’m going to have you build this thing called ConstantCare. It’s going to take a lot longer, but eventually, we’re going to switch over to using that. And I promise, you won’t have to support this. It will be legacy code. I won’t ask for a lot of work. And Richie knocked it out of the park.

Tara Kizer: And the reason why we use Excel is because we’re not always connected to our client’s machine. We’re connected for a few hours, and then that’s it. So this is just an easy way to have all the data collected in one place.

Brent Ozar: And before we connect to them too.

Richie Rump: Yeah, and one thing I didn’t tell you, Brent, is that I have dumped stuff into Excel programmatically for my entire career. So I mean, okay, we could do this damn thing again…

Tara Kizer: I like that Excel, you just need to have the file with the format you want and Excel just uses that format. So you have a template format.

Richie Rump: Yeah, it will just dump it to the cell if there’s a format there, but we could programmatically overwrite that format if people so desire, which we don’t because it’s just easier to format the page.

Brent Ozar: Alright, well thanks everybody for hanging out with us this week and we will see y’all next week at Office Hours; adios.

Email*
Name*
First Last
Things I want*
- Jan 25 - How to Think Like the Engine (8AM Pacific, 11AM Eastern)
- Feb 1 - Getting Started with the Consulting Toolkit (8AM Pacific, 11AM Eastern)
- Feb 8 - An Introduction to GitHub for DBAs (8AM Pacific, 11AM Eastern)
- Feb 15 - How to Capture Baselines with sp_BlitzFirst (8AM Pacific, 11AM Eastern)
- First Responder Kit (scripts & guides)
- Subscribe to email updates (3-4 per week)
- I would like a pony

Filtered Indexes vs Parameterization (Again)

October 26, 2018

Execution Plans, Indexing

At First I Was Like…

This won’t work at all, because parameters are a known enemy of filtered indexes. But it turns out that some filtered indexes can be used for parameterized queries, whether they’re from stored procedures, dynamic SQL, or in databases with forced parameterization enabled.

Unfortunately, it seems limited to filtering out NULLs rather than exact values.

Easy Example

If I set up this proc and index, and then run it, it’ll use the filtered index even though it seems like it maybe shouldn’t.

CREATE OR ALTER PROCEDURE dbo.filter_check (@age INT, @creation_date DATETIME)
AS
BEGIN
    SELECT COUNT(*) AS records
    FROM dbo.Users AS u
    WHERE u.Age >= @age
    AND u.CreationDate >= @creation_date;
END

CREATE INDEX ix_whatever ON dbo.Users(CreationDate) INCLUDE(Age) WHERE Age IS NOT NULL;

EXEC dbo.filter_check @age = 18, @creation_date = '2017-10-18';

CREATE OR ALTER PROCEDURE dbo.filter_check (@age INT, @creation_date DATETIME)

BEGIN

SELECT COUNT(*) AS records

FROM dbo.Users AS u

WHERE u.Age >= @age

AND u.CreationDate >= @creation_date;

END

CREATE INDEX ix_whatever ON dbo.Users(CreationDate) INCLUDE(Age) WHERE Age IS NOT NULL;

EXEC dbo.filter_check @age = 18, @creation_date = '2017-10-18';

wakka wakka

Even if I pass in a NULL for @age, it works. If I were searching for IS NULL or IS NOT NULL, well, that wouldn’t work, but that’s also pretty tough to parameterize. Heh.

Horse Water

This really only helps if you have a parameterized predicate on a column with a good chunk of NULL values in it. You can bypass indexing, and queries needing to navigate all those NULL values, with a filtered index.

But if you want to do something more specific, things don’t work as well. Using a slightly different example, with forced parameterization, this doesn’t work.

ALTER DATABASE StackOverflow2013 SET PARAMETERIZATION FORCED;

CREATE INDEX ix_votes_filtered_too
    ON dbo.Votes(VoteTypeId) 
        WHERE VoteTypeId = 9;

SELECT COUNT(*)
FROM dbo.Votes AS v
WHERE v.VoteTypeId = 9;

ALTER DATABASE StackOverflow2013 SET PARAMETERIZATION FORCED;

CREATE INDEX ix_votes_filtered_too

ON dbo.Votes(VoteTypeId)

WHERE VoteTypeId = 9;

SELECT COUNT(*)

FROM dbo.Votes AS v

WHERE v.VoteTypeId = 9;

The query plan will skip our filtered index, and warn about it in the select operator properties — just hit F4 to bring those up.

Letdown

Other People’s F4

Neck Pain

If you’re one of those crazy kids who lets NULLs walk all over their data, you might find some performance gains by filtering them out of your indexes, but only if they make up a good chunk of your data.

This is also a bummer for people who rely on forced parameterization to help deal with other issues — it takes a potentially really powerful tool out of your arsenal for query and index tuning.

Thanks for reading!

Batch Mode For Row Store: Does It Fix Parameter Sniffing?

October 25, 2018

Batch Mode, Execution Plans, Indexing

Snorting The Future

SQL Server 2019 introduced batch mode over row store, which allows for batch mode processing to kick in on queries when the optimizer deems it cost effective to do so, and also to open up row store queries to the possibility of Adaptive Joins, and Memory Grant Feedback.

These optimizer tricks have the potential to help with parameter sniffing, since the optimizer can change its mind about join strategies at run time, and adjust memory grant issues between query executions.

But of course, the plan that compiles initially has to qualify to begin with. In a way, that just makes parameter sniffing even more frustrating.

I Hate Graphic Tees

But I like this demo. It’s got some neat stuff going on in the plan, and that neat stuff changes depending on what you look for.

I also like it because it returns a small number of rows overall. I’ve gotten complaints in the past that queries that return lots of rows are unrealistic.

Moving on.

Here’s The Procedure

	CREATE OR ALTER PROCEDURE dbo.DisplayNameSearch
	     (
	         @DisplayName NVARCHAR(40)
	     )
	AS
	    BEGIN
	
			SELECT TOP 1000
				   u.Id,
				   u.DisplayName,
				   SUM(u.Reputation) AS Reputation,
				   SUM(CASE WHEN p.PostTypeId = 1 THEN p.Score END ) AS QuestionsScore,
				   SUM(CASE WHEN p.PostTypeId = 2 THEN p.Score END ) AS AnswersScore 
			FROM dbo.Users AS u
			JOIN dbo.Posts AS p
			ON p.OwnerUserId = u.Id
			WHERE u.DisplayName LIKE @DisplayName
			AND EXISTS (SELECT 1/0 FROM dbo.Badges AS b WHERE b.UserId = p.OwnerUserId)
			GROUP BY u.Id, u.DisplayName
			ORDER BY Reputation DESC
	
	    END;
	GO

CREATE OR ALTER PROCEDURE dbo.DisplayNameSearch

(

@DisplayName NVARCHAR(40)

)

BEGIN

SELECT TOP 1000

u.Id,

u.DisplayName,

SUM(u.Reputation) AS Reputation,

SUM(CASE WHEN p.PostTypeId = 1 THEN p.Score END ) AS QuestionsScore,

SUM(CASE WHEN p.PostTypeId = 2 THEN p.Score END ) AS AnswersScore

FROM dbo.Users AS u

JOIN dbo.Posts AS p

ON p.OwnerUserId = u.Id

WHERE u.DisplayName LIKE @DisplayName

AND EXISTS (SELECT 1/0 FROM dbo.Badges AS b WHERE b.UserId = p.OwnerUserId)

GROUP BY u.Id, u.DisplayName

ORDER BY Reputation DESC

END;

Here’s The Indexes

CREATE INDEX ix_posts_helper ON dbo.Posts (OwnerUserId, Score) WITH (SORT_IN_TEMPDB = ON);

CREATE INDEX ix_users_helper ON dbo.Users (DisplayName, Reputation) INCLUDE (PostTypeId) WITH (SORT_IN_TEMPDB = ON);

CREATE INDEX ix_badges_helper ON dbo.Badges (UserId) WITH (SORT_IN_TEMPDB = ON);

CREATE INDEX ix_posts_helper ON dbo.Posts (OwnerUserId, Score) WITH (SORT_IN_TEMPDB = ON);

CREATE INDEX ix_users_helper ON dbo.Users (DisplayName, Reputation) INCLUDE (PostTypeId) WITH (SORT_IN_TEMPDB = ON);

CREATE INDEX ix_badges_helper ON dbo.Badges (UserId) WITH (SORT_IN_TEMPDB = ON);

Parameter Sniffing Problem

My favorite user in the Users table is Eggs McLaren. If I wanted to find Eggs, and users like Eggs, I could run my proc like this:

EXEC dbo.DisplayNameSearch @DisplayName = N'Eggs%'

1	EXEC dbo.DisplayNameSearch @DisplayName = N'Eggs%'

It finishes pretty quickly.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Posts'. Scan count 11, logical reads 48, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Badges'. Scan count 17, logical reads 72, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Users'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Posts'. Scan count 11, logical reads 48, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Badges'. Scan count 17, logical reads 72, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Users'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 2 ms.

The plan for it is what you’d expect for a small number of rows. Nested Loops. Kinda boring. Though it has a Batch Mode operator way at the end, for some reason.

Flight of the Navigator

I say “for some reason” because I’m not sure why batch mode is a good option for one batch of 9 rows. It might just be in there as a safeguard for memory adjustments.

But hey, I’m just a bouncer.

If the next thing we look at is for users who didn’t register a proper handle on the site, we can run this query:

EXEC dbo.DisplayNameSearch @DisplayName = N'user[1-2]%'

1	EXEC dbo.DisplayNameSearch @DisplayName = N'user[1-2]%'

We might even find this guy:

Hello, new friend!

The plan doesn’t change a whole lot, except that now we have a couple spills, and they’re not even that bad. If we run the users query a couple more times, the memory grant stuff will iron out. Kind of. We’ll look at that in a minute.

Tripped, fell

The metrics for this one are way different, though. We do a ton more reads, because we get stuck in Nested Loops hell processing way more rows with them.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 6249, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Posts'. Scan count 200450, logical reads 641628, physical reads 0, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Badges'. Scan count 507356, logical reads 1619439, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Users'. Scan count 1, logical reads 2526, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 3593 ms,  elapsed time = 5051 ms.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 6249, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Posts'. Scan count 200450, logical reads 641628, physical reads 0, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Badges'. Scan count 507356, logical reads 1619439, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Users'. Scan count 1, logical reads 2526, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 3593 ms, elapsed time = 5051 ms.

Five Seconds Doesn’t Seem Bad

To most people, it wouldn’t be, but it depends on your expectations, SLAs, and of course, which set of parameters more closely resembles typical user results.

Especially because, if we recompile and run it for users first, we do much better. Far fewer reads, and we trade 30ms of CPU time for about 3.5 seconds of elapsed time.

Table 'Users'. Scan count 1, logical reads 2526, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Badges'. Scan count 5, logical reads 43941, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Posts'. Scan count 5, logical reads 105873, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 3626 ms,  elapsed time = 1601 ms.

Table 'Users'. Scan count 1, logical reads 2526, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Badges'. Scan count 5, logical reads 43941, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Posts'. Scan count 5, logical reads 105873, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 3626 ms, elapsed time = 1601 ms.

Even Eggs is in okay shape using the ‘big’ plan. Yes, metrics are up a bit compared to the small plan, but I still consider a quarter of a second pretty fast, especially since we do so much better with the users plan.

Table 'Users'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Badges'. Scan count 5, logical reads 43941, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Posts'. Scan count 17, logical reads 94, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 985 ms,  elapsed time = 261 ms.

Table 'Users'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Badges'. Scan count 5, logical reads 43941, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Posts'. Scan count 17, logical reads 94, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 985 ms, elapsed time = 261 ms.

Most of the reason for this is the adaptive join between Users and Posts.

Avedsay Atinlay

See, in the old days, we didn’t get that adaptive join, and when Eggs used the users plan, it would take about 2 seconds. There was a static hash join, and the startup cost and hashing process really stunk for a small number of rows.

This is a point in favor of batch mode, and adaptive joins, I think. But like I said, this is also what makes it more frustrating — if parameterized plans were treated with a little more suspicion, this post would have been over hours ago.

Is It All Sunshine And Daisies?

No, not at all. Batch Mode Memory Grant Feedback could still use some help.

See, when there are big swings, it has a hard time figuring out a good middle ground. It tries too hard to make each correction perfect for the last execution.

Here’s an example using the big plan for users.

The first time it runs, there are no spills, but it requests 1.1 GB of memory, and only uses 112 MB of memory.

Good times, that.

Running it again for users, we see a downward adjustment in requested memory.

This is good; there are still no spills. Our request dropped to 167 MB.

Proud of you, kiddo!

But if we run it for Eggs a few times now, the memory grant will swing way lower.

This ain’t gonna be good, Jim.

We’re down to a 34 MB memory grant, which of course means when we run users again…

Spanish Fly

We spill in four different places, and elapsed time shoots up to 16 seconds.

Table 'Users'. Scan count 1, logical reads 2526, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Badges'. Scan count 5, logical reads 43941, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Posts'. Scan count 5, logical reads 105525, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 3547 ms,  elapsed time = 16135 ms.

Table 'Users'. Scan count 1, logical reads 2526, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Badges'. Scan count 5, logical reads 43941, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Posts'. Scan count 5, logical reads 105525, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 3547 ms, elapsed time = 16135 ms.

Though I do wanna take a moment to thank whomever added Adaptive Join spill information to query plans. I don’t think that was there before.

I appreciate you.

But if we keep running those procs, eventually this will happen (32 oscillations is the number I’ve been given for calling it quits). We’re back to the original grant, and feedback won’t attempt to adjust it again, unless the plan leaves the cache for some reason.

I can quit you.

What Can We Do About Memory Grant Feedback?

The Memory Grant for this never quite corrects to a “good value” — it’s either a spill, or an excessive warning. The execution metrics end up taking a bad hit on the upswing.

Since the corrective process is asynchronous, perhaps more successful exploration could be had by looking at available memory and wait stats to decide which side to error on: spill vs. excessive, and just how much adjustment really needs to be made.

Perhaps it’s okay if the small query uses extra memory if we have a lot of memory, and we’re not waiting on RESOURCE_SEMAPHORE. After all, the users query asks for 167 MB. The Eggs query eventually adjusts down to 35 MB. Do we care about ~130 MB of memory grant if we’ve got > 100 GB of memory? Likely not.

Remember kids, Standard Edition of SQL server can use 128 GB of memory just for the buffer pool.

If you stick 196 GB of memory in there, you can give Windows it’s 10% tithe, and still have 48 GB of memory for all sorts of caches, memory grants, etc.

The rest of the caches in the SQL Server memory (procedure cache, thread stack, backup buffers etc) consumes memory outside buffer pool memory. The memory consumed by caches outside buffer pool is not restricted by above memory limits and can grow up to limits defined by “max server memory”. This is not specific to SQL Server 2016 SP1 and is also applicable to earlier releases of SQL Server as well.

This has been true since SQL Server 2012. Though, you know, only with 64 GB for the buffer pool, back then.

Batch Mode vs Parameter Sniffing

There is definitely some improvement here, via adaptive joins, but memory grant feedback still needs work.

I used to really hate hints like optimize for unknown, or for a value, but it may be worth exploring in these cases to always get a medium or large plan. Getting the bigger plan with the Adaptive Join saves us trouble switching between large and small.

For instance, adding this stabilizes the plan to the large one:

OPTION(OPTIMIZE FOR (@DisplayName = N'user[1-2]%'));

1	OPTION(OPTIMIZE FOR (@DisplayName = N'user[1-2]%'));

But the memory grant still swings wildly. In order to make that more stable, we have to do something like this:

OPTION
    (
    OPTIMIZE FOR (@DisplayName = N'user[1-2]%'),
    MIN_GRANT_PERCENT = 1.0,
    MAX_GRANT_PERCENT = 1.5
    );

OPTION

(

OPTIMIZE FOR (@DisplayName = N'user[1-2]%'),

MIN_GRANT_PERCENT = 1.0,

MAX_GRANT_PERCENT = 1.5

);

But if we’re going to play tricks with knobs like this, we’d better know our data, and our queries, very well.

Here at Brent Ozar Unlimited, we love us some good knobs.

Heck, some of our best friends are knobs.

Our last employee of the month was a knob.

But the more knobs we have, the more chances we have to screw stuff up. Just ask the knobs at Oracle.

Thanks for reading!

Batch Mode For Row Store: What Does It Help With?

October 24, 2018

Batch Mode, Memory Grants

SQL Server 2019

Introduces something pretty cool: the ability for row store indexes to be processed in batch mode. Batch mode is, of course, the preferred processing realm of column store indexes.

If you’re still in the dark about column store, you might wanna start by reading up on Niko Neugebauer and Joe Obbish’s blogs about it.

In a nutshell, it can be really helpful to reporting-style, and analytic queries, where large amounts of data are processed. Is processed?

Whatever.

There’s a Great Post® by Dima Pilugin that explains some of the things that the optimizer considers when making the choice to use batch mode for row store. Actually, all of Dima’s posts are great. He’s like the Russian Paul White. Or maybe Paul White is like the New Zealandish Dima Pilugin. I don’t know.

I’m just a bouncer, after all.

Why Batch Mode For Row Store?

Well, column store indexes are tricky. If you have a data warehouse where everything is one big shove to get data in, and then queries run, some of the trickiness is alleviated. But if you have data slowly trickling in and out via inserts, updates, deletes, and (forgive my language) merges, they can all play some nasty tricks on column store indexes. It’s sort of like how row store index fragmentation can impact large

read ahead scans, except, you know, it’s not just a problem that you saw in a demo where fill factor was set to 1%.

While we spend a lot of time having a giggle at The Cult Of Rebuild for row store indexes, column store indexes are a bit different. If data isn’t loaded in optimally, or it gets altered later, performance can really suffer. If you wanna learn a whole lot about that, and you’re going to PASS Summit, definitely attend this session. From, again, Joe Obbish. Funny how the same names keep popping up.

All of this makes adding column store indexes to existing OLTP applications tough. You probably can’t outright replace your current clustered indexes with clustered column store indexes, and adding nonclustered columnstore indexes can be confusing.

Should I add some of the columns?
NO REALLY IT IS
All of the Columns?
Does column order matter?
What impact will this have on locking?
How do I maintain them? (Ola’s scripts don’t support column store yet)
Which queries will use my column store index?
Will batch mode even help me?
Will it cause problems for existing workload queries?

Now you can worry about that stuff a bit less.

Important Numbers and Opportunities

Dima notes in his post the hard coded value 131,072 as a built-in magical number (at least as of CTP 2 — who knows if this’ll change by another CTP, or RTM). That’s the minimum number of rows that a query will have to process before batch mode for row store will kick in, among other considerations.

You guessed it — most OLTP queries don’t touch that many rows, unless you’re running a report, or you’ve done the worst possible job indexing for your queries. There are also some query constructs that aren’t supported for batch mode, not all operators can run in batch mode, and some don’t run well in batch mode (I’m looking at you, Sort).

However, this opens up more queries to a couple new optimizer tricks introduced in SQL Server 2017: Adaptive Joins, and Memory Grant Feedback. Definitely hit the links for more details, but now you just might see the optimizer changing run-time join types based on row thresholds, and memory grants being altered between query executions.

Load Work

This is definitely cool for people running reports, though much like the new Cardinality Estimator introduced in 2014, it’s not an across the board improvement. You’ll still have to compare queries to make sure batch mode is an improvement.

But what about other query types?

What about stored procedures that do varying amounts of work?
What about queries with implicit conversions, non-SARGable predicates, functions, etc?
What about queries with good, bad, or no indexes?
What about mistakes made during cardinality estimation?

In short, will batch mode for row store eat bad T-SQL for breakfast?

Stay tuned!

How to See Execution Plans in Azure Data Studio

October 23, 2018