Blog

Pop Quiz: Can You Use Your Monitoring Tool?

Last Updated February 17, 2018

In the free 30-minute intro calls I do about our SQL Critical Care®, one of the questions I ask is, “Do you have a monitoring tool? And if so, what has it told you about the root cause of the problems you’re facing?”

I either hear one of two things:

They don’t have a tool, or
They have a tool and can’t figure out what the problem is

During the engagement, I’ll sometimes ask the admins to open up the monitoring tool and then answer the following questions – not verbally, but actually do it in front of me:

Find the last time when SQL Server was slow (not from guessing or help desk tickets – use the tool)
Find the most resource-intensive queries that were running at that time, and show me their query plans

It turns out that most people can’t do that.

Monitoring tools aren’t intuitive.

There’s no built-in Clippy that pops up when you open the tool and gives you a guided walk-through of what you’re looking for. It’s up to you to read the manual, figure out how to accomplish those tasks, and then train the rest of your team to make sure they can do it, too.

Your monitoring vendor wants to help. Call their support desk or open a support ticket and ask if they offer a guided tour service. All of the monitoring vendors have support teams who can share your screen, take control, and walk you through the app. Have them answer the same two above questions, and take notes while they do it. Then, repeat the process yourself, and get good at it.

It’s not your fault that most monitoring systems are a little hard to use. However, it IS your fault that you’re not an expert at using your tools. You have to learn your monitoring tool the same way you learn SQL Server. Now get in there and sharpen those skills.

First Responder Kit Release: SQLBits was pretty awesome but now I have to do work again

Last Updated May 9, 2018

Erik Darling

First Responder Kit Updates

APRIL FOOLS! IT’S ONLY MARCH FIRST!

Tomorrow.

Butthead.

You can download the updated FirstResponderKit.zip here.

sp_Blitz Improvements

#1407: @parlevjo2 gave us a check to make sure databases are owned by an existing user. Existence is everything, so I hear.
#1416: After existence is probably location. After that is collation. @ktaranov let us know that our collation wasn’t all that. Especially if you have Unicode characters in database names. What a drag.
#1424: @rainyclouds pointed out that we missed some way out wiggy weird file names when we go looking for dangerous modules.
#1441: We updated our unsupported build list. I wish it were longer. Particularly, I wish it included everything before SQL Server 2012. Speaking of which, did you know both 2008 and 2008R2 will no longer be supported in July 2019? I was thrilled by that too.
#1447: Containers. Yeah. What? That’s so last year. Lemme know when there’s an is_serverless column or something. God.

sp_BlitzCache Improvements

#1386: Did you know that dynamic cursors can be, like, totally bad? Eh, you do now. Maybe someone might oughtta tell the Dynamics team about that.
#1397: Marbles and inquisitions. Or whatever. But look, sometimes Merge Joins do this whole Many to Many thing that makes them do a whole bunch of extra work. Check, checked.
#1402: Fixed a version checking bug for sorting by memory grants. This is why I like when things go out of support. I don’t have to do version checking anymore. Did I mention that both 2008 and 2008R2 will no longer be supported in July 2019?
#1410: We explicitly flag MSTVFs when they have a cardinality of 1 or 100, to avoid flagging the less icky ones that receive interleaved execution in 2017. I mean, sure, they could overlap. But you’ll never upgrade anyway, even though both 2008 and 2008R2 will no longer be supported in July 2019.
#1421: Math isn’t my strong point. But I fix it when I can. For instance, I have no idea how many months it is until both 2008 and 2008R2 will no longer be supported in July 2019.
#1438: Since we’re being futuristic, we’re also aggregating tempdb spills for any statements we find in the plan cache associated with a stored procedure. Like everything else. Heh heh heh.
#1445: Every time Brent is like “there’s this thing we can’t possibly do with BlitzCache, I take it as a personal insult. The latest affront was to say that I couldn’t find non-SARGable queries. A case of Laphroaig Cask Strength, 40 noise complaints, and a dead drifter later, BlitzCache will now tell you if you have a system function in your where clause, an expression in your join clause, or a like with a leading wildcard.

sp_BlitzFirst Improvements

Just like CIV, sometimes BlitzFirst can’t wait one minute more. Especially in a WAITFOR. When it can’t wait another second. Thanks to @Adedba for letting us know! Just like I can’t WAITFOR both 2008 and 2008R2 will no longer be supported in July 2019.

sp_BlitzIndex Improvements

#1449: Someone out there had a computed column definition that was so long, we had to get a bigger column. That someone was @thirster42. Check out the computed columns on that guy!

sp_BlitzWho Improvements

Nothing this time around

sp_DatabaseRestore Improvements

Nothing this time around

sp_BlitzBackups Improvements

#1425: Time and tide wait for no man, or SQL Server release. It doesn’t even matter if you’re in a weird time zone. Or Twilight Zone. It won’t even matter when both 2008 and 2008R2 will no longer be supported in July 2019. Thanks to @jfoudy for the heads up on this one! Now you’ll know which time zone your backups are happening in.

sp_BlitzQueryStore Improvements

Probably like the same stuff as BlitzCache but who can keep track honestly?

sp_AllNightLog and sp_AllNightLog_Setup Improvements

Nothing this time around

sp_foreachdb Improvements

Nothing this time around

PowerBI

Nothing this time around

sp_BlitzLock

Nothing this time around

For Support

When you have questions about how the tools work, talk with the community in the #FirstResponderKit Slack channel. If you need a free invite, hit SQLslack.com. Be patient – it’s staffed with volunteers who have day jobs, heh.
When you find a bug or want something changed, read the contributing.md file.

When you have a question about what the scripts found, first make sure you read the “More Details” URL for any warning you find. We put a lot of work into documentation, and we wouldn’t want someone to yell at you to go read the fine manual. After that, when you’ve still got questions about how something works in SQL Server, post a question at DBA.StackExchange.com and the community (that includes us!) will help. Include exact errors and any applicable screenshots, your SQL Server version number (including the build #), and the version of the tool you’re working with.

You can download the updated FirstResponderKit.zip here.

Adaptive Joins, Memory Grant Feedback, and Stored Procedures

Last Updated May 26, 2020

Erik Darling

Adaptive Joins, Execution Plans, Memory Grants, Parameter Sniffing

Not Exactly The Catchiest Name

There’s a TL;DR here, in case you don’t feel like reading the whole darn thing.

Batch mode memory grant feedback works with stored procedures
It takes several runs to adjust upwards to a final number
It seems to only adjust downwards once (in this case by about 40%)

This isn’t perfectly scientific. It’s just one example that I came up with, and may behave differently both in the future, on other systems, and for other code.

But you gotta start somewhere, eh?

The Rest Of The Thing

I have a pretty simple stored procedure here, that qualifies both for Batch Mode Memory Grant Feedback and Batch Mode Adaptive Joins.

And I thought I had a hard time naming things.

CREATE OR ALTER PROCEDURE dbo.UsersByLastAccessDate
     (
         @LastAccessDate DATETIME
     )
AS
    BEGIN
        SELECT  uc.Id, uc.Reputation, p.Score
        FROM    dbo.Users_cx AS uc
        JOIN    dbo.Posts AS p
        ON p.OwnerUserId = uc.Id
        WHERE   uc.LastAccessDate >= @LastAccessDate;
    END;

CREATE OR ALTER PROCEDURE dbo.UsersByLastAccessDate

(

@LastAccessDate DATETIME

)

BEGIN

SELECT uc.Id, uc.Reputation, p.Score

FROM dbo.Users_cx AS uc

JOIN dbo.Posts AS p

ON p.OwnerUserId = uc.Id

WHERE uc.LastAccessDate >= @LastAccessDate;

END;

For different values passed to Last Access Date, the Adaptive join changes.

This returns 622,961 rows:

EXEC dbo.UsersByLastAccessDate @LastAccessDate = '20160101'

1	EXEC dbo.UsersByLastAccessDate @LastAccessDate = '20160101'

This returns 24,803 rows:

EXEC dbo.UsersByLastAccessDate @LastAccessDate = '20161211 01:00:00.003'

1	EXEC dbo.UsersByLastAccessDate @LastAccessDate = '20161211 01:00:00.003'

Clearly processing these different amounts of data requires different amounts of memory.

It’s not clear from the plan which operator consumes the memory (there are no Memory Fraction indicators), but we can assume that it’s the Adaptive Join operator. In Adaptive Join plans, both the join types have a startup memory cost. This is a safety net to support the runtime decision.

Little Plan First

When we execute the proc with the small value first, we start off with a small memory grant, that over five iterations adjusts upwards to 14,000KB, and stops there.

In between each run, it fires off a request for more memory next time. Feedback, and all that.

I cast a pretty wide net with the stuff I was collecting while running this. Some of it caught stuff, some of it didn’t. I’m just showing you the interesting bits.

For instance, there were a bunch of rows for the spill event, but everything was NULL for them, and the spill details aren’t really pertinent here.

Whatever.

Make Profiler Great Again.

Big Plan First

When the large plan fires first, something a bit odd happens.

It asks for a much larger grant up front than it it adjusted to last time — 14MB vs 62MB:

When the smaller plans run, the memory grant cuts down by 23.5MB, and stops adjusting from there.

On each subsequent execution, there’s a plan warning about excessive memory.

You Can’t Put Your Arms Around A Memory Grant

Further executions of the large plan don’t increase the memory grant. Ticking the Last Access Date back to 2010 also doesn’t cause the grant to increase upwards.

Which Is Better?

Well, if the memory needs of the query truly are 14MB of memory overall, it would seem like starting low and ticking up is ideal. I mean, unless something awful happens and your next few runs just spill and take forever and everyone hates you.

If the grant of 38MB isn’t harming concurrency, and sets a safe bound for larger values, it might not be bad if you end up there after just one run.

In either situation, you’re much better off than you are on earlier versions of SQL that can’t swap joins at run time, or adjust memory grants between runs.

Those are two of the many things that make dealing with parameter sniffing difficult.

I really hope that (JOE SACK RULES) the QO team at Microsoft keep at this stuff. Right now, it only helps for queries running in Batch Mode, which requires the Scent Of A Column Store.

Making features like this available in Row Mode, or making aspects of Batch Mode accessible to Row Mode queries…

One can dream.

Thanks for reading!

Brent says: I feel for people out there who don’t have the luxury of spare time to read blogs. You, dear reader, are going to be able to recognize these symptoms when they show up in your server, but other folks are going to be even more mystified about why their query performance is all over the place when they swear haven’t changed the queries. Don’t get me wrong, I love this feature – but in these early iterations of it, it’ll catch people by surprise.

SET IMPLICIT_TRANSACTIONS ON Is One Hell of a Bad Idea

Last Updated October 20, 2025

Brent Ozar

Development

By default, when you run a query in SQL Server, your delete/update/insert (DUI) is finished as soon as your query finishes. That’s because the default behavior of SQL Server is IMPLICIT_TRANSACTIONS OFF – meaning, SQL Server doesn’t hold your queries open waiting for a COMMIT or ROLLBACK.

As an example, I’ll connect to the StackOverflow database and give myself some reputation points:

UPDATE dbo.Users 
  SET Reputation = 1000000000
  WHERE Id = 26837;

UPDATE dbo.Users

SET Reputation = 1000000000

WHERE Id = 26837;

Take that, Jon Skeet. Now, in another window, I’ll use sp_WhoIsActive to see what locks are being held:

sp_WhoIsActive @get_locks = 1;

1	sp_WhoIsActive @get_locks = 1;

No rows come back – because no sessions are open that are currently holding locks.

Now, let’s try that with IMPLICIT_TRANSACTIONS ON.

Change that horrific setting, and run the update:

SET IMPLICIT_TRANSACTIONS ON;
GO
UPDATE dbo.Users 
  SET Reputation = 1000000000
  WHERE Id = 26837;
GO

SET IMPLICIT_TRANSACTIONS ON;

UPDATE dbo.Users

SET Reputation = 1000000000

WHERE Id = 26837;

It still finishes instantly, and as far as that user is concerned, the behavior was no different. But now check sp_WhoIsActive again in another window:

And whaddya know, now a row shows up. Our UPDATE – even though it finished – is still hanging out. If you scroll across to the right, you’ll see open_tran_count = 1.

SQL Server started a transaction for you without you asking for one.

Click on the locks column in sp_WhoIsActive to see the locks being held by your session:

You now have an exclusive lock on that row, and the lock isn’t going away until you either roll back or commit your transaction.

How to tell if you’re having this problem

The first symptom is severe blocking issues, but at first glance, it just looks like people are doing their normal query workloads. The blocking eventually clears up on its own when the app randomly issues a COMMIT, typically for unrelated reasons.

But it’s hard to troubleshoot because the IMPLICIT_TRANSACTIONS option doesn’t show up in sys.dm_exec_plan_attributes, sys.query_context_settings, or sp_WhoIsActive’s @get_additional_info = 1. Erik filed a Microsoft request to fix that, and you can upvote it here.

For now, to catch it, your options are:

Run sp_BlitzFirst, our free live performance check script. It warns when there’s a long-running query blocking others, plus warns when live queries are using implicit transactions.
Run sp_Blitz, our free SQL Server health check script. It also warns when there are live queries using implicit transactions, so you can catch it during a regular health check as well.
Run sp_BlitzWho, our live activity checker, and look at the is_implicit_transaction column.

To track the queries over time, you can also run Profiler or use the existing_connection XE session.

Tara Says: It gets worse! If you run a SELECT, you get a transaction. Don’t forget to commit after your SELECTs.

Erik Says: In Brent’s screencap for the uncommitted transaction, it’s been running for nearly a minute. I wonder what he was doing for the minute between running the query and taking the screenshot.

Fifteen Things I Hate About ISNUMERIC

Last Updated February 26, 2018

Erik Darling

T-SQL

Yello!

	SELECT ISNUMERIC('$') AS [What]
	UNION ALL 
	SELECT ISNUMERIC('£') AS [What]
	UNION ALL 
	SELECT ISNUMERIC(',') AS [What]
	UNION ALL
	SELECT ISNUMERIC('.') AS [What]
	UNION ALL
	SELECT ISNUMERIC('0e+99') AS [What]
	UNION ALL
	SELECT ISNUMERIC('2e2') AS [What]
	UNION ALL
	SELECT ISNUMERIC('12D4') AS [What]
	UNION ALL
	SELECT ISNUMERIC(',1,1,1,1,1,1,1') AS [What]
	UNION ALL
	SELECT ISNUMERIC('-') AS [What]
	UNION ALL
	SELECT ISNUMERIC('+') AS [What]
	UNION ALL
	SELECT ISNUMERIC(CHAR(9)) AS [What] 
	UNION ALL
	SELECT ISNUMERIC(CHAR(10)) AS [What] 
	UNION ALL
	SELECT ISNUMERIC(CHAR(11)) AS [What]
	UNION ALL
	SELECT ISNUMERIC(CHAR(12)) AS [What] 
	UNION ALL
	SELECT ISNUMERIC(CHAR(13)) AS [What]

SELECT ISNUMERIC('$') AS [What]

UNION ALL

SELECT ISNUMERIC('£') AS [What]

UNION ALL

SELECT ISNUMERIC(',') AS [What]

UNION ALL

SELECT ISNUMERIC('.') AS [What]

UNION ALL

SELECT ISNUMERIC('0e+99') AS [What]

UNION ALL

SELECT ISNUMERIC('2e2') AS [What]

UNION ALL

SELECT ISNUMERIC('12D4') AS [What]

UNION ALL

SELECT ISNUMERIC(',1,1,1,1,1,1,1') AS [What]

UNION ALL

SELECT ISNUMERIC('-') AS [What]

UNION ALL

SELECT ISNUMERIC('+') AS [What]

UNION ALL

SELECT ISNUMERIC(CHAR(9)) AS [What]

UNION ALL

SELECT ISNUMERIC(CHAR(10)) AS [What]

UNION ALL

SELECT ISNUMERIC(CHAR(11)) AS [What]

UNION ALL

SELECT ISNUMERIC(CHAR(12)) AS [What]

UNION ALL

SELECT ISNUMERIC(CHAR(13)) AS [What]

Thanks for reading!

Brent says: the funny part to me is that if you just try to union all of them, SQL Server throws its hands up:

SELECT '$' AS [What]
UNION ALL 
SELECT '£' AS [What]
UNION ALL 
SELECT ',' AS [What]
UNION ALL
SELECT '.' AS [What]
UNION ALL
SELECT '0e+99' AS [What]
UNION ALL
SELECT '2e2' AS [What]
UNION ALL
SELECT '12D4' AS [What]
UNION ALL
SELECT ',1,1,1,1,1,1,1' AS [What]
UNION ALL
SELECT '-' AS [What]
UNION ALL
SELECT '+' AS [What]
UNION ALL
SELECT ASCII(9) AS [What] 
UNION ALL
SELECT ASCII(10) AS [What] 
UNION ALL
SELECT ASCII(11) AS [What]
UNION ALL
SELECT ASCII(12) AS [What] 
UNION ALL
SELECT ASCII(13) AS [What];

SELECT '$' AS [What]

UNION ALL

SELECT '£' AS [What]

UNION ALL

SELECT ',' AS [What]

UNION ALL

SELECT '.' AS [What]

UNION ALL

SELECT '0e+99' AS [What]

UNION ALL

SELECT '2e2' AS [What]

UNION ALL

SELECT '12D4' AS [What]

UNION ALL

SELECT ',1,1,1,1,1,1,1' AS [What]

UNION ALL

SELECT '-' AS [What]

UNION ALL

SELECT '+' AS [What]

UNION ALL

SELECT ASCII(9) AS [What]

UNION ALL

SELECT ASCII(10) AS [What]

UNION ALL

SELECT ASCII(11) AS [What]

UNION ALL

SELECT ASCII(12) AS [What]

UNION ALL

SELECT ASCII(13) AS [What];

Result:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '$' to data type int.

1 2	Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '$' to data type int.

COME ON SQL SERVER YOU JUST TOLD ME THEY WERE ALL NUMERIC, dammit, convert to numeric for me if you’re so smart.

Computed Columns and Cardinality Estimates

Last Updated February 12, 2018

Erik Darling

Development, Execution Plans, SQL Server

:thinking_face:

When most people think about computed columns, they don’t think about cardinality estimates.

Heck, I’m not sure most people think about cardinality estimates. At all. Ever.

One of the few people who has ever responded to my emails does, and I didn’t even have to threaten him.

Do you think about computed columns?

If you’re here, you might just be thinking about lunch, avoiding jail time, or how much you hate Access.

But seriously, they’re great for a lot of things. Just don’t put functions in them.

One thing they can help with, without you needing to persist or index them, is cardinality estimates.

Demo Block

If I run this query:

SELECT  COUNT(*) AS records
FROM    dbo.Users AS u
JOIN    dbo.Posts AS p
    ON p.OwnerUserId = u.Id
WHERE   u.UpVotes + u.DownVotes = 0
        AND p.Score >= 5;

SELECT COUNT(*) AS records

FROM dbo.Users AS u

JOIN dbo.Posts AS p

ON p.OwnerUserId = u.Id

WHERE u.UpVotes + u.DownVotes = 0

AND p.Score >= 5;

I get this plan:

The first thing that jumps out to most people is the warning on the Hash Join — indeed, it’s grail overfloweth.

The root cause of this bad guess is from the Users table. Our WHERE clause is just awful. How on earth would SQL Server know how many Upvotes + Downvotes = 0? In fact, it’s guess is just plan goofy.

The guess is off by about 470k rows. If your concern is with cardinality estimates, you may try the Advanced Dead End known as multi-column statistics.

CREATE STATISTICS users_upv_dv ON dbo.Users (UpVotes, DownVotes) WITH FULLSCAN;

1	CREATE STATISTICS users_upv_dv ON dbo.Users (UpVotes, DownVotes) WITH FULLSCAN;

But that doesn’t help!

You might even try Advanced-Advanced Dead End known as filtered multi-column statistics.

CREATE STATISTICS users_upv_dv ON dbo.Users (UpVotes, DownVotes) WHERE UpVotes = 0 AND DownVotes = 0 WITH FULLSCAN;

1	CREATE STATISTICS users_upv_dv ON dbo.Users (UpVotes, DownVotes) WHERE UpVotes = 0 AND DownVotes = 0 WITH FULLSCAN;

And that still won’t help.

Even if you RECOMPILE. Even if you free the proc cache. Even if you rebuild indexes. Even if you restart SQL.

Magically, I can add this computed column:

ALTER TABLE dbo.Users ADD UpvotesPlusDownvotes AS UpVotes + DownVotes;

1	ALTER TABLE dbo.Users ADD UpvotesPlusDownvotes AS UpVotes + DownVotes;

I’m not persisting it, and I’m not even indexing it. But my plan changes! Kind of.

The Hash Join is no longer spilling!

Before you go accusing me of taking advantage of Batch Mode Memory Grant Feedback — I’m not using any cOLU MNstor3 indexes in here. Everything is plain old row mode.

It’s just a result of the cardinality estimate improving.

The much improved cardinality estimate leads the optimizer to ask for a larger memory grant.

Rather than a small, crappy memory grant (for this query).

Is this as good as it gets?

Obviously not. We could make some other changes to improve things, but I think this is pretty cool.

We didn’t have to change our query, even, for the optimizer to make this adjustment.

If you’ve got this kind of stuff in your queries, a computed column might be a good way to improve performance.

Thanks for reading!

Using WITH (NOEXPAND) to Get Parallelism with Scalar UDFs in Indexed Views

Last Updated February 12, 2018

Brent Ozar

T-SQL

Scalar functions are the butt of everybody’s jokes: their costs are wrong, their STATS IO results are wrong, they stop parallelism when they’re in check constraints, their stats are wrong in 2017 CU3, they stop parallelism in index rebuilds and CHECKDB, I could go on and on.

Recently, we ran across yet another scenario where scalar UDFs were killing performance.

Someone happened to add a scalar UDF to an indexed view, and any query that touched that view couldn’t go parallel – even if it didn’t need query the function-based field!

Check it out, using my friend the Stack Overflow database.

First, let’s create a function that just returns the number 1. That’s it. No data access, no worries about how much work the function’s doing – it just returns 1, that’s it:

CREATE OR ALTER FUNCTION dbo.fn_Returns1 ( @Id INT )
RETURNS BIGINT
    WITH RETURNS NULL ON NULL INPUT,
         SCHEMABINDING
AS
    BEGIN
        RETURN 1;
    END;
GO

CREATE OR ALTER FUNCTION dbo.fn_Returns1 ( @Id INT )

RETURNS BIGINT

WITH RETURNS NULL ON NULL INPUT,

SCHEMABINDING

BEGIN

RETURN 1;

END;

Then let’s create an indexed view that includes that function:

CREATE VIEW dbo.vwVotes WITH SCHEMABINDING AS
SELECT Id, dbo.fn_Returns1(1) AS One
  FROM dbo.Users;
GO
CREATE UNIQUE CLUSTERED INDEX IX_Id ON dbo.vwVotes(Id);
GO

CREATE VIEW dbo.vwVotes WITH SCHEMABINDING AS

SELECT Id, dbo.fn_Returns1(1) AS One

FROM dbo.Users;

CREATE UNIQUE CLUSTERED INDEX IX_Id ON dbo.vwVotes(Id);

Then run a query on that view:

SELECT COUNT(*) FROM dbo.vwVotes;

1	SELECT COUNT(*) FROM dbo.vwVotes;

We scan the index, and the cost is above my Cost Threshold for Parallelism, but it still goes single-threaded:

Why? Well, right-click on the plan, click View XML, and check out the highlighted area (emphasis mine):

Doh. Scalar functions strike again.

But check out WITH (NOEXPAND)

Add that query hint, and look what happens: the query goes parallel!

Top query has no hints, bottom query has WITH (NOEXPAND)

Both plans have full optimization. Both hit the same index. I have no idea why you suddenly get parallelism when you use that hint – I just stumbled on it accidentally.

This may be related to a fix Paul White found in SQL Server 2016 last year – look for the “A New Option” section in that post. That one requires a trace flag, but I’m certainly not using that trace flag here. It might be that WITH (NOEXPAND) now triggers the same behavior in computed fields that the trace flag used to.

I have no idea what version/build this was introduced in – my testing was done on 2017 CU3. Feel free to talk amongst yourselves and try other versions/builds if you’re having the scalar-in-indexed-views problem.

Of course, the best answer isn’t to use the WITH (NOEXPAND) hint – as long as you’re in there mucking with the query, if you can, just get rid of the scalar function.

Let’s Give The Optimizer A Name

Last Updated February 17, 2018

Erik Darling

Humor, SQL Server

Clunky

As my favorite monkey likes to say, the hardest things do with computers are:

1. Naming things

4. Asynchronous processing

2. Cache invalidation

5. Off by one errors

Things are tough for Microsoft, too.

Take “the optimizer” for instance. It’s based on something called the Cascades Framework.

No one wants to say “Cascades Framework”, so they just call it “The Optimizer”.

Not to be confused with “The Storage Engine”, or “The Cardinality Estimator”.

Like I said, clunky.

Friendly Name

I’d like to give The Optimizer a friendly name.

I mean, aside from Paul.

Think you have a good name for it? Leave a comment!

We’ll pick our favorites, and maybe you’ll win something.

Unless you’re in Europe.

Brent says: You’ll definitely win something.

How to Throttle Logins to SQL Server

Last Updated February 12, 2018

Brent Ozar

Bad Idea Jeans

So, uh, you can use WAITFOR in a logon trigger:

CREATE OR ALTER TRIGGER SorryNorm ON ALL SERVER FOR LOGON AS
BEGIN
IF ORIGINAL_LOGIN()= 'NormTheNewGuy'
    WAITFOR DELAY '00:00:15';
END;
GO

CREATE OR ALTER TRIGGER SorryNorm ON ALL SERVER FOR LOGON AS

BEGIN

IF ORIGINAL_LOGIN()= 'NormTheNewGuy'

WAITFOR DELAY '00:00:15';

END;

You probably don’t want it to be TOO long, lest their app report it as a connection timeout. You want it just slow enough to make them angry.

I mean, theoretically. I would certainly never do anything like this. And I’m certainly not as diabolical as Dave Dustin:

More fun to add a RAND() clause in there so it doesn't always happen…

— Dave Dustin (@venzann) February 2, 2018

And as long as I’m talking about triggers, Marc Brooks had a request:

Now do one that prevents CREATE TABLE from working in master

— Marc Brooks ??? (@IDisposable) February 3, 2018

Sure! Here you go:

USE master;
GO
CREATE TRIGGER [I Can't Go For That] ON DATABASE
AFTER CREATE_TABLE
AS
    BEGIN
    ROLLBACK
    END
GO

USE master;

CREATE TRIGGER [I Can't Go For That] ON DATABASE

AFTER CREATE_TABLE

BEGIN

ROLLBACK

END

That one’s an especially bad idea because it’ll likely break 3rd party apps and maybe even SQL Server upgrades.

And I know what you’re thinking: does that trick work in TempDB? Kinda – it prevents the creation of “regular” tables, like dbo.Whatever, but it doesn’t prevent the creation of temp tables.

The Many Levels Of Concurrency

Last Updated February 12, 2018

Erik Darling

Locking, Blocking, and Isolation Levels

“Concurrency is hard”

Construction begins on the Tower of Babel, 610 BCE (colorized)

When designing for high concurrency, most people look to the hardware for answers. And while it’s true that it plays an important role, there’s a heck of a lot more to it.

Start Optimistic

Like, every other major database vendor is optimistic by default. Even the free ones. SQL Server is rather lonesome in that, out of the box, you get the Read Committed isolation level.

Everyone else gives you something close to Read Committed Snapshot Isolation. This is why you don’t see too many Oracle bloggers wasting precious internet on the perils of NOLOCK.

Pros

Avoid reader/writer blocking from the start
Makes reporting on OLTP data easier
Can alleviate some deadlocking scenarios

Cons

Can drive tempdb crazy
If your transactions depend on short blocks for queuing, you may need to use locking hints
Coin Currency Is Hard
Those orphaned “begin tran” queries have more repurcussions

Start Normal

Perhaps the un-sexiest bit of databases: normalization! This plays a huge part in concurrency, though.

If you choose to store all of your data in overly wide tables, a lot more queries rely on them simultaneously. If you’ve followed my advice on optimistic locking, you’ve bought yourself a little reprieve, but all those modification queries still have to get along.

Wider tables are also a lot more difficult to index. The number and variety of queries that touch wide tables in different ways often lead to many wide nonclustered indexes to compensate.

When you spread data out to well-normalized tables, the easier it becomes to spread out writes, and index for specific query patterns.

I can’t recommend Louis Davidson’s book on database design enough.

Start Setting

I know it’s been said everywhere on the internet, and anyone heavily invested in high-end design and implementation will have a runbook for SQL Server setups.

But still, this stuff matters! Let’s take a look at a couple things Microsoft has made better, and a couple things they haven’t.

Good

Option to enable Instant File Initialization
Number and size of tempdb files

Having these two things set up correctly from the get-go can have huge impacts down the line. Especially with trace flags 1117 and 1118 being the default tempdb behavior starting with SQL Server 2016. Back when we talked about optimistic isolation levels, we talked a little about tempdb. All the row versions that allow readers and writers to not get all gummed up go to tempdb. You’re gonna want this going blazing fast.

Having instant file initialization turned on is a no brainer (unless you’re covered in tin foil for non-fetish-related reasons). End users typically don’t like having pauses in query processing while waiting for files to grow. Letting SQL and Windows negotiate this with IOUs behind the scenes makes data file growths a whole heck of a lot less painful — especially if you’re the kind of person who lets their files grow by percentages. You know, 10% of 4 GB is a whole lot less than 10% of 400 GB.

Still bad

MAXDOP
Cost Threshold for Parallelism

I know these are a bit personal, especially if you’re on SharePoint (forgive me for speaking such words), but here’s why they impact concurrency.

You only get a certain number of threads to work with, and they’re based on your CPU count.

You’ve got to factor a lot into how many threads you need

Number of Users
Number of parallel queries
Number of serial queries
Number of databases and data synchronization (think Mirroring, AGs, backups for Log Shipping)

If you leave MAXDOP and CTFP at the defaults, you’re letting just about every query use every core.

You’re looking at running out of threads pretty quickly, if you’ve got any kind of user base. Nothing kicks concurrency to the curb like THREADPOOL waits.

This is SQL Server we’re talking about, and all those cores are doggone expensive. If a query wants to use more than one, it better be worth it.

Those defaults have been around since Justin Timberlake was country the first time.

Start Hard

As we speak, I’m kicking 128 GB of RAM. I don’t even have a real job.

If you’re sizing production servers, or Tituss Burgess forbid, VM hosts, and you think 256 GB of RAM is a good number, you have lost your mind to oblivion.

SQL Server 2016 SP1 Standard Edition can use 128 GB of RAM to cache data alone. If your database isn’t that big today, it will be soon. And you know you’re working on a data warehousing project, too.

On top of that, it can use

32 GB for column store
32 GB for Hekaton

And it can use any memory in your server above the 128 GB mark for query processing, DMV and plan caching, backups, and just about anything else.

Thanks to Bob Ward (who through all things are possible, except learning how to use the debugger) for confirming my suspicions about the memory grant part.

Stepping back a bit to design — you better choose those data types carefully if you want to make the most out of your memory.

Running out of memory means waiting on memory. Much like THREADPOOL, this is not what you want your queries waiting on.

Having data cached in memory and having enough memory for queries to run without spilling oodles of noodles to disk is important.

Babel of Bloggers

Concurrency isn’t the domain of any one aspect of the database. You have to put a lot of thought into it at every level of design.

There are other ways to shoot yourself in the foot, whether it’s instance stacking, putting all your clients in one database, or putting hundreds of client databases on one server.

These are the kinds of design decisions people seemed determine to make.

While they’re cheaper and easier in the beginning them, they’re often expensive and difficult to undo later.

Like these butt implants I got.

Thanks for reading!

[Video] Office Hours 2018/2/14 (With Transcriptions)

Last Updated February 17, 2018

Brent Ozar Unlimited Team

Videos

This week, Brent, Tara, and Richie discuss the lack of backward compatibility for SQL Server backups, deploying SQL Server on VMs with shared SAN storage, query tuning, replication, full-text indexes, backup and restore, saying “no” to your superiors as a DBA, clearing your transaction log, and much more.

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.

Enjoy the Podcast?

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

Office Hours – 2-14-18

When the data file fills up, how long does SQL stay up?

Brent Ozar: Let’s see; Ricardo asks, “if the data file has no more space to grow…” This is so good. “How long will SQL Server keep continuing to process transactions as long as the log file has plenty of disk? Will it go down when the log is full, up to the next checkpoint, when exactly will it fail?”

Erik Darling: Is this, like, an exam question? What are those asterisks?

Brent Ozar: That’s so cool. I don’t think any of us would ever test it. My guess is, you would keep running until you needed to add a page in the data file, which you could do inserts, updates and deletes, even. The deletes may be making- enough space on some pages that matter. Maybe inserts don’t require a new page. Updates may not, but if you’re depending on knowing the exact time it’s going to go down, you’re probably screwed to begin with.

Tara Kizer: It’s going to be a short amount of time, I suspect.

Erik Darling: Yeah, I don’t think that’s going to go well.

Brent Ozar: I don’t think it will even be up long enough to get the answer.

Erik Darling: But that would be a fun thing to test, because you could just create a small, like 1MB, data file, cap the size and start banging away on transactions and see how long it takes. Why you got to ask us? I don’t want to go do that demo now.

Brent Ozar: And if you do it, put some time in between the loops. Don’t just go one equals one, because this whole thing’s going to go down like a house of cards. It sounds like the kind of thing we usually do. And you see us do stuff like that during Dell DBA Days, but not always; it’s pretty rare.

Erik Darling: I don’t want to do demos that don’t have a query plan anymore. They’re hard.

Brent Ozar: You’re in love with query plans.

Erik Darling: I just like perf, and I can’t…

Brent Ozar: Amen, that’s me with availability groups. I’m like, oh it’s over there; I know how it works. I’m kind of done with that piece now. Lovely, I like it. Same thing with clustering, like, it’s over there. I’m going to be over here writing query demos.

Why can’t I restore to an older SQL Server version?

Brent Ozar: Michael asks, “Why is there no backwards compatibility for SQL Server backups? Why can’t I backup a database from a SQL 2012 instance and then restore it on a 2008 R2 instance?”

Erik Darling: Things change, man; all sorts of stuff changes. I guess the way I’d think of it is like the amount of fundamental changes that take place from version to version, both internally to resource DB, master, DMVs, the way SQL just does things, where it looks for things. There’s just no sense in having a database turn back time just so that you can restore it to an older version. I totally get why they don’t do that. It doesn’t seem fair to you because you have a weird environment where you need to downgrade databases, but I get why they don’t want to switch databases back to something else. That sounds like a nightmare.

Richie Rump: It’s like, you also have to ask questions, like have you ever written software that just works once? And then you want it to work with previous versions – it gets super hard. I take a look at Windows and my mind is blown that you could actually install software that was written 20 years ago, and essentially, it probably will work. That’s insane. It’s just insane. You can’t even open up QuickBooks in the previous version without having to upgrade to the new version.

Brent Ozar: I am so amazed that you can take a database from SQL Server 2005 and restore it to 2014 or 2016 and it just works. And you don’t have to do any steps, the magic just happens; it’s phenomenal.

Erik Darling: But you know, if you think about phone operating systems, which are even worse – you have Android or you have an iPhone, there are apps that don’t work because you upgraded Android. Like, there are apps that stop functioning because they make such fundamental changes to the operating system that those apps don’t do things the same way. So like Richie was saying, you could install something on Windows from 20 years ago that still works, but you can be crap out of luck on phone OS if it’s like six months ahead of whatever app you installed. Compatibility is hard.

Richie Rump: That’s a philosophy, right, when you’re writing software. It’s like, yeah, we’re going to forget everything that’s old, and it’s just new, new, new, new, new and you just need to upgrade to a new version, and that’s it. And that’s just a philosophy – whereas Windows was the complete opposite. Like, I know we need everything to work in previous versions. It’s just really hard to do it.

Erik Darling: That, and as someone who works on scripts that have to work across a wide variety of things, as soon as we don’t have to support an older version anymore, I want constant forward momentum. I don’t want to have to keep thinking about other stuff. I have it on my calendar for 2019 when 08 and 08 R2 are pulled, because I am not doing one more version check.

Brent Ozar: Amen, I think it’s also going to catch people by surprise as they start playing around with Azure SQL DB Managed Instances. Managed Instances are roughly equivalent to Always On Availability Groups. You can upload a database into there, it just kind of works and they manage it for you. But man, heads up, if you ever want to get your data back out, you’re going to be SSIS or BCB…

Erik Darling: There is no SQL Server version, physically, that is the same as the Azure DB version.

Should I separate my data and log files?

Brent Ozar: Landon says, “I know these questions come up a lot, but deploying SQL Server on VMs with shared SAN storage – is it worth separating the data logs and tempdb files onto their own disks like traditional best practices advise?” What you want to do is – with your SAN vendor – because there are cases with some SANs where they will handle caching differently, for example. And then they require you to have the logs on separate drives for different ways of doing caching. But it is going to be different for every make and model of SAN. Generally speaking, it’s not going to be the performance difference that gets you across the finish line.

Tara Kizer: I have a different take on it. So, I like to still split them up regardless; especially by database even. So that if one database – it blows out the log, it doesn’t cause storage issues for other ones. So by separating them to mount points or drives, I can avoid production issues that some databases might cause.

Brent Ozar: And if it’s not by database, I also like by business unit too. Like if you’ve got a bunch of accounting databases on the server and they all have their own use pattern, then you can group them together.

Why is my query sometimes slow?

Brent Ozar: Let’s see. Chris asks, “Hey, I have a task where I need to update millions of rows in a table. I wrote a script that will batch these updates into smaller 1000 row updates. Sometimes when I run it, it’s smoking fast; other times it’s completely slow. When it’s slow, I stop it, count to three, hit run again and it goes back to being smoking fast. I’m not seeing any blocking and my database is in simple recovery model; any ideas of what to do when it sometimes just runs slow?”

Erik Darling: So is it slow from like the outset, or does it slow down after a few batches? Don’t blame fragmentation.

Brent Ozar: He says from the outset… I’m guessing bad query plan – oh no, because stopping it and counting to three wouldn’t make a difference on that.

Erik Darling: Blocking?

Brent Ozar: He said he’s not seeing any blocking.

Erik Darling: Oh, he did say that. Thanks, guys.

Tara Kizer: Check the waits – what is the query waiting on?

Brent Ozar: And how would you do that?

Tara Kizer: Well, I would do it with sp_whoisactive. Erik might get a little upset though…

Erik Darling: No, I use sp_whoisactive for lots of stuff; sp_whoisactive is superior in its own special ways that I am not trying to compete with. BlitzWho does stuff and it grabs stuff from new DMVs that sp_whoisactive doesn’t touch, and that’s our niche with that. We look at stuff that Adam is too busy running Python to mess with.

Brent Ozar: And one of the things I love about sp_BlitzWho over sp_whoisactive is that it will also show you session waits much easier. So you don’t just see the current wait, you see all kinds of other waits. So he follows up with he thinks it’s usually CXPACKET, but definitely check sp_BlitzWho because it will show you the session waits altogether; which may be a lot other than CXPACKET.

Erik Darling: You know what happens to me sometimes when I run things as a loop? I forget that I have query plans turned on, and that just messes me all off because it makes the query plan for each iteration of the loop nest.

Should I do CHECKDB in a maintenance plan or a script?

Brent Ozar: So, let’s see, amigos. Pablo says, “What do you think about CHECKDB? Should I do it on a maintenance task or should I do it with T-SQL and log the output?”

Tara Kizer: I don’t care how you do it, just do it. I’m not a fan of maintenance plans at all, so I won’t use those no matter what. But I’m okay with maintenance plans, just not for my servers. But other people’s servers, I’m okay with them for backups and CHECKDB. I’m not okay with them for index maintenance. Use Ola Hallengren’s Index Optimize.

Brent Ozar: It’s totally free. Ola.Hallengran.com – Swiss Army Knife; all kinds of cool stuff.

Tara Kizer: The only thing to add is make sure that you are logging the outputs so that if CHECKDB ever does fail, you don’t want to have to run CHECKDB manually in a query window to get the output. So have that output go to a file so that you can take a look at – sometimes the failures are benign, but sometimes it’s going to mean database corruption. And you do not want to have to run CHECKDB when you encounter corruption again because you might be talking about a 10TB database, and that could take hours.

I haven’t mastered replication yet…

Brent Ozar: Speaking of which, Daryll says, “I haven’t mastered replication yet…” [crosstalk] It’s like a support group here. “I’ve scripted out my mess and successfully run it and added articles with the GUI. My vendor says don’t use the GUI; only script it and never touch it.” He says he only runs snapshot when the article isn’t flagged. “Can you help me find the column for an article not flagged for snapshot?”

Tara Kizer: I have no idea what any of that meant, but I’m a fan of the GUI when it comes to replication. In my last job, they were fond of scripting it. So luckily, when I arrived they already had everything scripted and I would just change what I needed to. I just followed their template. But previous jobs, my boss always said you should start scripting this stuff – but the GUI works fine here. I mean, I supported replication for many years, but I didn’t love it so I never cared to figure out the scripting.

Brent Ozar: Yeah, if it works. I like – if you know it implodes all the time – so like I had one job where replication just always imploded, so we script it out and we could just go run it whenever it broke. But god, if you’re in this situation that bad, how about you fix it so that it doesn’t break all the time? We couldn’t be bothered.

Best practices for full-text indexes

Brent Ozar: Ron asks, “Do y’all have any best practices for using full-text indexes?”

Erik Darling: Yeah, it’s called Elasticsearch…

Richie Rump: Yeah, Elasticsearch…

Tara Kizer: Don’t use it.

Brent Ozar: What’s the difference between Elasticsearch and SQL Server?

Richie Rump: So I always felt that Elasticsearch, you could just do so much more. You could do more with the querying, you could do a heck of a lot more with the functionality it actually brings out. Remember, full-text search really hasn’t been updated in years and years and years and years, so it’s pretty basic. I mean, if you just want to do a simple search, that’s fine. But with Elasticsearch, you could create these crazy search strings and really dig into your data and find, you know, insane stuff. The only problem is that you do have to move your data from SQL Server into Elasticsearch and put the index on top of that. So, if you have something that’s, you know, you want to keep secure, you’ll have to think about how that’s going to work. But other than that, it’s designed for searching. That is what it’s designed to do. And so it’s the best tool out there for doing that.

Brent Ozar: How much does it cost?

Erik Darling: Free 99…

Richie Rump: Free to me.

Erik Darling: It’s funny, I was on the phone with a client and they were like, “Oh well I wrote the poor man’s version of Elasticsearch in SQL Server.” And I’m like, Elasticsearch is free. It is the poor man’s version. It doesn’t get any more poor man than free. You can just go download it; it’s right there for you.

Tara Kizer: That must be the client that we share because I remember hearing that.

Richie Rump: You know, running the poor man’s version of Elasticsearch ids, what, Apache Solar, which they branched off the same thing. I mean, I don’t know.

Erik Darling: Back when I used to support relativity, they had the option to create full-text indexes. And leaving aside the fact that setting off a crawl on a several terabyte table kind of sucked, but I really just hated dealing with the search queries that would come out of that. Like with the contains and the contains table, no one was ever quite sure what to do to get what results back. And as part of the application, they also used dtSearch and they sometimes used Elasticsearch for things. So obviously, even they had been pulling away a little bit or giving people alternatives because full text just wasn’t good for all kinds of searches. What I really like the best about Elasticsearch is that it’s outside of SQL Server, so you’re not wasting all your RAM and other resources on these giant full-text catalogs. You’re putting it outside into a product that was meant for researching that kind of thing.

Brent Ozar: We should probably suffix it to say too, if you’re doing home development of an app that’s 5GB to 10GB in size and it’s never going to hit 100GB, then sure, full-text indexing is probably fine, but you don’t need any best practices. It’s just going to work. But beyond that, that’s when we start asking questions.

Do differential backups take longer for striped backups?

Brent Ozar: Brain asks an interesting one. “Have you ever seen differential backups take longer – like ten times longer – when the full original backup was striped as opposed to done to a single file?” It’s a no from me…

Erik Darling: No from me…

Brent Ozar: Alright, I think you got a unicorn there.

Richie Rump: I mean, are there unit tests that you could run against it? I don’t know.

Brent Ozar: Yeah, if you could reproduce that, create a database 10GB in size, fill it with random junk data and do the test back and forth, showing it’s [immediately] different and reproduce it on a couple of different SQL Servers, then submit it as a question to DBA.StackExchange.com. And people will up-vote the hell out of that because if it’s reproducible, that would be an awesome bug to find.

Erik Darling: Honestly, the only time I’ve ever seen diffs take longer is when there was more diff to diff. I don’t think the backup striping ever had anything to do with it.

Richie Rump: Choose these DBAs, choose diff.

Erik Darling: I like it. I found myself in a weird situation last week where…

Brent Ozar: Just once?

Erik Darling: Just once. Well, I mean one day, in one instance – one relevant instance of a weird situation last week where the client was doing big data warehouse stuff and they wanted some replication of the data, and I actually recommended differential shipping to them because they were all in simple recovery. And there was just no reason for them to switch to full and start doing log backups, so I was just like look, you can take a diff – because they were doing nightly fulls with snapshots. So I was just like, you could just take these diffs at four to six-hour increments and be okay. They’re going to be big, but you can do it. It might beat the pants off you having to learn transaction log backups at this late place in your developer career.

Brent Ozar: I also just want to point out for people who are watching the video, you see how my webcam is doing this weird thing where it wraps around? I can point it one side and my finger comes out the other. This has happened all morning with my training webcast. I noticed it when I was like doing jazz-hands and they started coming out the other side. I was like, what the hell is going on here.

Tara Kizer: What have you done to the matrix?

Brent Ozar: I know. What I wanted to do is I wanted to get over far enough that I could scratch my own ear, but…

Erik Darling: I thought it was like, is somebody in there with him? What’s going on here?

Tara Kizer: Erik is trying to [crosstalk].

Brent Ozar: I will crush your head.

Does SQL Server need 2 IP addresses?

Brent Ozar: Sri asks, “Do we need to have two IP addresses for a SQL Server?”

Erik Darling: For what?

Tara Kizer: Need to? No.

Are the 2008 setup files a security risk?

Brent Ozar: Terry says, “We have SQL Server 2012, and it’s” – hold on. They have a SQL Server 2012 but somebody installed the 2008 management objects and the 2008 set up files. “Our security team is asking us to remove them. I’ve Googled and found the people ran into issues. What are your thoughts?” Well, you know what I’d do? I’d tell your security team to remove them. If they’re so confident in how this works.

Tara Kizer: And if they’re requiring you to do this, I’m not sure what kind of security risk there is for having them, I’m sure there’s something that it’s just not aware of, I would require that the company gives me a new server so I can move everything over to this. I would not be doing this on an existing production server.

Brent Ozar: Log ship or database mirror over to it or something. But, yes, it’s a little sketchy.

Do log backups truncate the transaction log?

Brent Ozar: Sri asks, “Do maintenance plans transaction log backups truncate the logs once the log backup finishes?”

Erik Darling: Well, any transaction log backup will truncate the log internally. Not externally.

Brent Ozar: As long as – like, there’s some things that will cause it not to.

Erik Darling: Right, like replication or whatever other weird log reuse wait. But – or like, an open transaction that’s keeping things all big and bloaty. But generally, I think if you’re expecting the size of the file to physically shrink after a transaction log backup, you’re going to be waiting a very long time because that’s just not what happens. The transaction log grows as it needs to, to accommodate active stuff, and then internally, marks space for reuse when transaction log backups happen. Apart from like, what we talked about, if there’s a weird log reuse issue. Like, an open transaction or replication or whatever else shows up in that sys.databases log_reuse_wait_desc.

Brent Ozar: Funny. Things you never think you’d learn as like, you know, as a kid, or you’re getting into the industry, you’re like, “What are all these cryptic DMV names?” And now we’re like, “It’s over in log sys.databases log_reuse_wait_desc.”

Erik Darling: Yes, things I’m trying to forget so that I can learn other things. There’s only so much space in here and I’m just trying to like, flush some of this stuff out so I can get new things in there.

Followup on the security risks

Brent Ozar: Terry says – follows up with, “Ha-ha, that’s a good one. It isn’t how it works. Security is in charge and the DBAs are dirt, so yes, they’re requiring it and we have to do it, and no, we can’t have a new server.” I have a great consulting line for this. I’m just like, “Go ahead and show me how it’s done. I don’t quite understand.” Like, [unintelligible]. “No, I’ve never done that before, I’m not comfortable with that. It’s a risk for me, I’m not comfortable with it.”

Erik Darling: That’s a production box. If you want me to make changes to that, I want something to test those changes on before I push them live into production. I don’t want to just willy-nilly install stuff that might mess up connectivity, whatever, to my server, because then I have to troubleshoot that on top of your stupid security issue that’s probably non-existent.

Tara Kizer: And then also, if you Googled and found that other people have run into issues doing this, this needs to be documented, and that they need to – yes, the security team needs to sign off, and the CTO needs to sign off that this is a potential known issue to cause problems and you’re requiring me to do it in production.

Brent Ozar: And start a support call with Microsoft. It’s 500 bucks, be just like, “Sure, I’ll do it as long as you, you know, start a support call with Microsoft and they walk me through it.” Microsoft will be like…

Erik Darling: No, like, you have to be on the call with me when it happens. Because if you’re asking me to do this thing that I can’t do, like, you can’t just like, physically change that server during business hours. You can’t just like YOLO uninstall crap. Why would you do that to yourself? I don’t know.

Why won’t our admins let us do this?

Brent Ozar: Anon emus says, “We have a developer who’s trying to use OA create processes”, this just keeps getting better. The more that I read it gets even better. “For making bots to send notifications about certain jobs running across multiple servers, using linked servers.” I think that’s probably the single sentence with the most bad ideas I have seen in it in quite some time. “We cannot get SA permissions.” Yes, that’s probably a good idea.

Erik Darling: Good.

Brent Ozar: Whoever made that decision [crosstalk].

Erik Darling: Whoever is saying no to you is pretty smart.

Brent Ozar: You should buy them a beer.

Tara Kizer: I have a blog post today that’s how to suck at database administration and in the comments like I added something today that said, “Guess some of us need to talk about the people skills, the personal skills that senior DBAs are very lacking out there.” And one of the issues on my reviews at past jobs is that people would say, “These are third-party reviews”, people would say, “You know, she needs to have a softer way of saying no.” And for this here, I would say, “No, end of story, done. I don’t care what my review’s going to say. This is just dumb.”

Richie Rump: A softer way of saying no? Hell no? I mean [crosstalk].

Tara Kizer: I know.

Richie Rump: And the horse you rode in on.

Tara Kizer: They would say that, even if the answer’s no, she should have more words go around it, make it sound nicer, and it’ll still be no in the end. It’s like, what?

Richie Rump: You know that sunshine, it should go up the rear, that’s where I want to blow it off. Okay, here’s two words. No-o.

Brent Ozar: You smell great today. No.

Erik Darling: Good news anon. Look, I’m going to give you some good news though. Between you and me, I’m sure you can do that in one line of PowerShell, so just don’t sweat.

Tara Kizer: Even Erik could do it in one line.

Erik Darling: That’s it. One line.

Richie Rump: There’s no unit tests around that PowerShell code, I’m sorry.

Brent Ozar: None of us – I think it’s universal, none of us support doing that. We’re not [unintelligible].

Erik Darling: SP create OA things are like, deprecated. They’re like, in the dust. Like, I remember reading like, old like, SQL some workbench articles on Simple Talk with like, someone would use like SP OA create to do all this crazy stuff, and I’ll be looking at it like, “Are you sure? Why would you make a database do that?”

Brent Ozar: I remember stumbling across stuff like that at some point when I was way young and I’m trying to figure out, “Alright, well, it’s out on the web, it must work. This must – someone had to get approval to post this, right?”

Erik Darling: Yes, it’s like creating Excel files and then destroying Excel files and doing all this other stuff. I’m like, this just seems dumb. This just seems a bit daft. It has to be – I learned that word because I went to Bits by the way. Daft is an English word. After I got done converting my fat ass to kilos, I learned how to say daft in English.

Brent Ozar: Richie’s been telling me that I should check out ear grey tea. That was what he was talking about.

Tara Kizer: Ear grey? Earl grey.

Richie Rump: My favorite. It’s great. I’ll never live that typo down. That’s exactly right.

Brent Ozar: I’m going to serve him some. I’m going to be like, “Here’s your ear grey tea.”

Richie Rump: Is it Van Gogh flavor?

Brent Ozar: Nice. Terry follows up with, “You guys are great, now I’m ticked at myself for not thinking of all those things.” It’s the – how everything works anytime you talk to anybody else. Everyone else seems to have all the good ideas.

How do I clear sleep_bpool_flush blocking?

Brent Ozar: Let’s see. Mahesh says, “My transaction log has grown too large, like, 200GB and as I dig into it, it seems like some internal checkpoint is stuck with sleep_bpool_flush. Any idea how to clear the transaction log and complete a checkpoint? The database is in simple recovery model.

Erik Darling: Look in your error log for messages that say flush cash. Because if you have messages in there that say – like, there’s messages – it’ll be like, flush cash waited x amount of seconds to push x amount of pages to disk. It’s a really nasty, like, kind of I/O issue. You’ll also probably see like, if you search for flush cash and also search for 15 seconds, because you’re probably running into nasty I/O issues where the buffer pool can’t flush stuff from memory to disk fast enough.

Tara Kizer: So while you guys were answering a previous question, I started researching that wait stat, and so – I was keeping myself busy, so Paul Randal said that you may see as a top wait, if a checkpoint is blocked for some reason, and he doesn’t mention slow I/O, but he says an example of this would be trying to start a data backup, which does perform a checkpoint, while the database is being encrypted for a TDE, transparent data encryption.

Erik Darling: That happens to the best of us.

Tara Kizer: Yes, I suspect that maybe, you know, if this isn’t happening during your full backup, that you may have – oh, you said simple recovery model, so you have TDE and a full backup occurring at the same time.

Brent Ozar: Or is someone trying to rotate the keys, which requires it to re-encrypt the whole database and you can’t take a backup while that’s happening?

Can you give me a brief overview of query store?

Brent Ozar: Right, the last question we’ll take, can you – Tom asks, “Can you give a brief overview of the query store feature in 2016?”

Richie Rump: Quick rundown? Oh wait, I know this, right? So, this is – this is when you go and you take a look at queries and you select which one to buy. And you install it on your machine, yes. It’s like an [crosstalk], nobody uses it.

Brent Ozar: You especially want to focus on the one that has a little robot hand, and it’s a claw, and it picks up queries and drops them off to you. Just make sure you pick the right one, not the truncate table one. No, none of us are serious. You know what you do? Okay, here you go. Go to GroupBy.org – not as in group purchase, but GroupBy.org, and query store on there and there’s been a couple of sessions on how query store works. Check those out and you can watch like, a 90-minute free video on there about how it works and see demos of it too as well.

Erik Darling: And then if you want to go explore your query store, I have a stored procedure called sp_BlitzQueryStore, which allows you to do very sp_BlitzCache-y things on your query store queries. End of story, sentence, period.

Richie Rump: I was waiting for more.

Brent Ozar: Then comes the robot claw. And make sure that you’re fully patched too, because there’s a lot of known issues around query store that got better with Service Pack 1 cumulative updates 6 or whatever it was.

Erik Darling: Standard issue couldn’t flush plans out of it and couldn’t clear it out. It grew astronomically.

Richie Rump: I’m looking at a sp_Blitz roll right now for query store clean up.

Erik Darling: Look at that.

Brent Ozar: I like it.

Erik Darling: Thanks, Richie.

Brent Ozar: Alright, well thanks everybody for hanging out with us this week, and we won’t see you next week on Office Hours because we’re – three of the four of us are going to be in Kingdom of United, Richie’s staying home and holding the fort down in Miami, but the rest of us are going to be at SQLBits in London. So we will see y’all in two weeks. Adios everybody.

Then Why Doesn’t SQL Always Seek?

Last Updated February 6, 2018

Erik Darling

Execution Plans, Indexing, SQL Server

Scan The Man

There seems to be a perpetual battle waged against the Index Scan.

At some point it was declared that scans were inferior to seeks, and all energy should be dedicated to eradicating them.

Much like asking why the whole plane isn’t made out of the stuff the black box is made out of, you start to wonder why the Index Scan is even an operator.

Obviously, if Microsoft cared about performance, there would be no Index Scans.

Right?

I bet Postgres would never scan an index.

Being Practical

It should be pretty easy to prove Seek Supremacy once and for all. Then you can literally (LITERALLY!) ignore every other operator in a query plan and focus the entirety of your being on this unknown astral plane toward mocking those without the mental capacity to receive your wisdom.

About Index Scans.

Let’s start with a query.

SELECT  COUNT(*)
FROM    dbo.Users AS u
JOIN    dbo.Posts AS p
    ON p.OwnerUserId = u.Id;

SELECT COUNT(*)

FROM dbo.Users AS u

JOIN dbo.Posts AS p

ON p.OwnerUserId = u.Id;

It has no predicates. It’s just joining two tables together in total.

In the query plan, we have two scans. It’s a two scan plan.

For the Seekists out there; fear not, your moment of ascension is at hand.

SELECT  COUNT(*)
FROM    dbo.Users AS u WITH ( FORCESEEK )
JOIN    dbo.Posts AS p
    ON p.OwnerUserId = u.Id;

SELECT COUNT(*)

FROM dbo.Users AS u WITH ( FORCESEEK )

JOIN dbo.Posts AS p

ON p.OwnerUserId = u.Id;

This plan must surely be superior.

Not All Who Scan Are Lost

In this case, the Two Scan Plan does a bit better.

The source of all those extra reads, and likely the extra CPU time for the seek plan is…

Well, it’s in the seek.

You can see why the optimizer chose the scan in the first place.

Hang on though, maybe we picked the wrong table to force a seek on.

SELECT  COUNT(*)
FROM    dbo.Users AS u
JOIN    dbo.Posts AS p WITH ( FORCESEEK )
    ON p.OwnerUserId = u.Id;

SELECT COUNT(*)

FROM dbo.Users AS u

JOIN dbo.Posts AS p WITH ( FORCESEEK )

ON p.OwnerUserId = u.Id;

This must be the better choi-

Important Information

The reason we get this error here is because we don’t have an index on the Posts table with OwnerUserId as a key column.

This is where most Earth Peoples start to get annoyed — they usually do have an index. In fact, if consulting has taught me anything, you have 17 of the same index with _dta_ somewhere in the name on the data you wish to seek into.

Alright then. Let’s add an index.

CREATE INDEX ix_maybe
    ON dbo.Posts ( OwnerUserId );

1 2	CREATE INDEX ix_maybe ON dbo.Posts ( OwnerUserId );

If we re-run our query without hints, what happens?

We still get two scans!

Comparing all three plans (we can force a seek on Posts now, because we have an index on OwnerUserId), the seeks still aren’t doing so hot.

Now what?

Hopefully you learned that not every seek is great, and not every scan is awful.

If you’re joining two tables together, often a single scan of the data is the wisest choice.

The worst part of a plan may not always be obvious, and it may not always be the method that the optimizer chooses to access data with.

Thanks for reading!

New SQL Server Management Studio 17.5: It’s Classified

Last Updated February 16, 2018

Brent Ozar

Breaking News, SQL Server 2017

SQL Server Management Studio 17.5 is out, and new in this release is a SQL Data Discovery & Classification feature. The idea is that it’ll scan your database, identify columns containing potentially sensitive data, and help you become compliant with regulations like PCI, HIPAA, and GDPR.

Let’s see how it works on the Stack Overflow public data dump.

Start by opening up SSMS, and then right-click the database, Tasks, Classify Data:

I could show you this, but it’s classified

Within moments, your compliance needs are…hang on, that can’t be right:

I could teach you, but I’d have to charge

I’ve tried it on 3 VMs now, even tried with AdventureWorks, and no dice. My guess – and this is just a guess – is that maybe it has to do with the upgrade process not working, even though I even tried a full install as opposed to an upgrade. We’ll probably get another build in the next few days.

Compliance. Sure is hard.

Update: looks like nobody tested it on case-sensitive database servers:

C’mon, guys….

Memory Grants: SQL Server’s Other Public Toilet

Last Updated May 26, 2020

Erik Darling

Execution Plans, Indexing, Memory Grants, Parameter Sniffing

Sharing Is Caring

When everything is going well, and queries are behaving responsibly, one need hardly think about memory grants.

The problem becomes itself when queries start to over and under estimate their practical needs.

Second Hand Emotion

Queries ask for memory to do stuff. Memory is a shared resource.

What kind of stuff? Well, usually to sort or hash data.

How much memory the optimizer asks for is dependent on:

Number of rows
Row goals
Data types and sizes
If the query is going parallel
Max server memory
Any memory limiting query hints
Resource governor settings

If you need to do something with 10 rows of VARCHAR(10) data, the amount of memory you need will generally be estimated to be much lower than if you need to do something with 10 rows of VARCHAR(100) data.

Underestimating

If I have this query plan.

SELECT      u.DisplayName
FROM        dbo.Users AS u
WHERE       u.CreationDate >= '2016-10-11'
            AND u.CreationDate < '2016-10-12'
ORDER BY    u.CreationDate;

SELECT u.DisplayName

FROM dbo.Users AS u

WHERE u.CreationDate >= '2016-10-11'

AND u.CreationDate < '2016-10-12'

ORDER BY u.CreationDate;

The sort spills, with only 1968KB of memory granted.

Is this when you should worry about spills? Definitely not.

But it’s easy to see how we can influence the memory grant with One Weird Trick And Basically You’re Kidding Me.

SELECT      CONVERT(NVARCHAR(215), u.DisplayName) AS DisplayName
FROM        dbo.Users AS u
WHERE       u.CreationDate >= '2016-10-11'
            AND u.CreationDate < '2016-10-12'
ORDER BY    u.CreationDate;

SELECT CONVERT(NVARCHAR(215), u.DisplayName) AS DisplayName

FROM dbo.Users AS u

WHERE u.CreationDate >= '2016-10-11'

AND u.CreationDate < '2016-10-12'

ORDER BY u.CreationDate;

The important thing to pay attention to here is that we’re not sorting BY a column with a large data type. We only have to sort some data by a column that has no index to support the sort order.

It’s like when you highlight an entire Excel file and order it by a single column. The whole sheet winds up sorted by that one column.

SQL Server has to do the same thing, and it wants to do it in memory, if possible. Spilling to disk stinks.

The DisplayName column is defined as NVARCHAR(40), but if we CONVERT it to have a length of 215 (this was the just the lowest number that it stopped spilling at, there’s nothing special about an NVARCHAR(215), generally) we get a larger memory grant.

We get this plan.

Lying about that causes the optimizer to ask for a large enough grant for nothing to spill.

This doesn’t mean you should litter every query spilling to disk with CONVERTS. The more appropriate response might be a better index that supports the sort.

Or just not asking for sorted data.

But still, this query underestimated it’s practical needs. It chose the wrong stall, to coin a phrase.

Overestimating

It’s possible for the optimizer to choose unwisely in the other direction, as well.

If we change our query to this:

SELECT      CONVERT(NVARCHAR(4000), u.DisplayName) AS DisplayName
FROM        dbo.Users AS u
WHERE       u.CreationDate >= '2008-12-25'
            AND u.CreationDate < '2008-12-26'
ORDER BY    u.CreationDate;

SELECT CONVERT(NVARCHAR(4000), u.DisplayName) AS DisplayName

FROM dbo.Users AS u

WHERE u.CreationDate >= '2008-12-25'

AND u.CreationDate < '2008-12-26'

ORDER BY u.CreationDate;

We wind up with another kind of warning! An excessive grant. Again, at this size, I wouldn’t worry about it. But there it is.

If you have a lot of queries asking for much larger grants than they’re using, you can wind up with a whole bunch of queries with big needs waiting on a bunch of queries with much lesser needs.

You’rein trouble, in other words.

You may even think SQL Server has a memory leak.

Rain Dance

SQL Server only has a limited amount of memory to do stuff, which means that when it’s given out all the memory it can, you end up with queries waiting on memory to do stuff.

Not just memory consuming stuff, either. They’ll hang out waiting for memory to start running, or even compile.

This is something you want to control, because you may have queries stealing memory space from the buffer pool, or the plan cache.

Which is worse depends on what sort of shape your server is in.

If you’re already under provisioned for memory, the large grants can hurt more
If you have plenty of memory, but maybe sup par (no 3PAR jokes, here) storage, spills to disk may be worse

Too Many Things

What causes this to happen?

Poor cardinality estimates
Parameter sniffing
Badly defined data types
Not eliminating rows early in the query
Selecting more columns than you need
Selecting all your columns at once

You can’t index for every aspect of every query, and you can’t have infinite RAM.

Yet.

Thanks for reading!

How to Suck at Database Administration

Last Updated February 14, 2018

Tara Kizer

Production Database Administration

157

This isn’t about having a sucky job, this is about you sucking at your job. There’s a big difference. You could be sucking at your job because you hate your job and just don’t care. I get that. In that case though, you know better but choose not to do better.

I’ve encountered some people that have Senior DBA as their job title but don’t really have senior-level experience. I’ve seen some very questionable things in my career.

Signs you aren’t a Senior DBA

Not checking the databases for corruption
Shrinking databases on a schedule or using auto-shrink
Not knowing what wait stats are or how to use them
Relying on the Database Tuning Advisor to determine which indexes are needed
Using Activity Monitor – I might get some flack on that one, but it’s true
Thinking that 1 FULL backup and 1 LOG backup per day is sufficient for an RPO goal of anything less than 24 hours
Thinking you should set MAXDOP to 1 to avoid CXPACKET waits
Updating statistics after the respective index was rebuilt
Not monitoring your systems – you don’t need expensive monitoring tools to monitor your systems!
Manually doing a task over and over again when it can be automated
Ignoring job failures and warnings/errors in the Error Log
Not checking for high-value missing indexes periodically
Using maintenance plans – I’m okay, though I still dislike them, if you use them for backups or DBCC CHECKDB but not for index maintenance
Not patching your servers
Not trying to improve your skills – don’t rely on your company to send you to training

What would you add to this list? What are the questionable things you’ve seen a Senior DBA doing or not doing?

How do you stack up?

I’ve done many of these things, but I was either a Junior DBA or an Intermediate DBA.

How many of these are you doing? The higher your number is, the less of a Senior DBA you are. It’s all fixable! Spend some time figuring out why you shouldn’t be doing these.

#TSQL2sday: What I’m Passionate About – Sas Christian’s Art

Last Updated February 13, 2018

Brent Ozar

Humor

For this month’s T-SQLTuesday, Aaron Bertrand asked what we’re passionate about – but outside of the tech community, and asked for pictures. Seems like a great way to get to know about us.

From my personal blog and my Instagram feed, you probably already know that I’m into cars, travel, and food, so I wanted to pick something different to share with you.

I’m really into Sas Christian’s paintings. (Warning: her stuff is mostly safe for work, but her husband Colin does a lot of NSFW sculptures. Those are awesome too, but just giving you the heads up before your monitor fills up with a high-resolution alien sex organ.)

I walked through our place and took photos of some of my favorite pieces. For years, Erika’s said we have too much art, and as I edit down the number of photos I show you, I realize she’s right. (I bought another one yesterday.)

I adore this 2006 piece. She’s just bad ass. The first time I saw this painting in a book, I thought she was wearing a watermelon t-shirt – I thought the holes were seeds.

I’ve got a signed print and the artist’s proof, and heaven forbid the original painting ever come up for sale. I would trade a kidney for that.

After collecting a few prints, I moved up to her pencil sketches:

I love her pencil sketches, and luckily for me, so did Erika, so we ended up getting several that hang on the living room walls. I love that these are subtle – they force you to get right up on top of ’em and take a close look at what’s going on:

Eyes are a big theme in our house, too – a lot of our art involves eyes. Kinda creepy now that I think about it.

Which leads me to the first original oil paintings of Sas’s that I could afford:

A series of 8″ x 10″ works with different faces:

I used to have the Peepholes on the wall behind me, but Erika pointed out that they looked really creepy to clients when I was doing conference calls.

Sometimes she collaborates with her husband, Colin, on different techniques. This one is 18″x24″ on wood, done with metal flake paint:

“Saturday Night Sunday Morning” by Sas Christian

Glitters spectacularly in the light, and reminds me of a bass boat from my Kentucky childhood.

“Nova” is the little eye sculpture covered in Swarovski crystals, sitting in a little pile of keepsakes. Sas and Colin have done some neat stuff with crystals – I really, really want their recent Twinkle to Death sculpture. I’m just hoping one of you buys it before I find a stack of hundos under a couch cushion somewhere.

Wanna learn more about Sas & Colin Christian? Here’s a few links, but keep in mind that like a lot of art, some of it is very not safe for work.

SasChristian.com – her paintings, and her Instagram feed
ColinChristian.com (NSFW) – his sculpture, and his Instagram feed – seriously, he’s not safe for work. I have a couple of Colin’s pieces, but there’s a reason why they’re not pictured in this post. Don’t come blaming me when you see Miley Cyrus wearing his sculpture of a unicorn’s sex organ.
Juxtapoz Magazine – I found Sas & Colin via the lowbrow art movement, and Juxtapoz is a good place to see other styles of lowbrow.
Beautiful Bizarre – magazine not really dedicated to lowbrow, but has a few other artists I like.
Yuri Shwedoff – another artist I love, but he focuses on digital work. Wolf Pack is one of my favorites, and I love watching the video of how he does it.

Attending our SQLBits Pre-Con? Prepare Your Laptop Now.

Last Updated November 26, 2020

Brent Ozar

SQLBits

If you’re joining Erik & me at our SQLBits precon, Expert Performance Tuning for SQL Server 2016 & 2017, you’ll be able to follow along with the demos on your laptop. We’re going to be showing you some common and not-so-common query anti-patterns, and explaining how they’re easier to solve with SQL 2016 & 2017.

We’re also giving you two workloads to troubleshoot: an easy set, where the queries are named with the anti-patterns they exhibit, and a not-so-easy set – where you have to figure out the problems yourself. You can work through these labs as we talk about the anti-patterns in class, and you can continue working through ’em after the conference.

To prep, here’s what you need – and we’ll also distribute USB drives with this stuff before class, but if you’ve already got it done, then you’ll be able to network with us & the other attendees before class starts:

SQL Server 2016 with Service Pack 1 or SQL Server 2017 with Cumulative Update 3 – these two updates add a lot of performance tuning capabilities, and our demos are going to flat-out require those patches. If you’re not patched, the demos simply won’t work.

SSMS 17.4 – Microsoft’s been adding all kinds of goodies in SSMS, too.

StackOverflow2010 (1GB zip) – this is a scaled-down version of the StackOverflow public data dump. It has exactly the same schema, but it only has data from the first few years of Stack (2008-2010), so it’s only 10GB in size. (You could actually follow along with the full-sized >100GB version too if you had a really beefy laptop, but it will take much longer for your indexes & queries to finish, so we’re using the smaller version for the live classes.). Remember to bump the compatibility level up to whichever version you’re following along on.

SQLQueryStress – open source load test app that lets you run lots of queries at once. If you’d like to learn more about it, here’s how I use it, and here’s the Github repo. It’s maintained by @ErikEJ, a Data Platform MVP from Denmark who’s definitely worth a Twitter follow.

Our First Responder Kit – download & install our scripts. Optionally, if you want to use the Power BI Dashboard for DBAs, then you’ll also need to install the Power BI Desktop, and set up the sp_BlitzFirst Agent jobs to collect data on your laptop during class. That part really is totally optional.

Join the Slack chat room – in large events, we handle Q&A and attendee chat using the SQL Server community chat. If you’re not already in it, get a free instant invite, and then join the #sqlbits-tuning room.

We’re excited! See you in London. If you can’t make it there, we’ve also got an online version of the course, too.

The Annals of Hilariously Bad Code, Part 2

Last Updated February 9, 2018

Erik Darling

Development

You’re Crazy

In Part 1, I showed you code that I think has some anti-patterns in it.

In case you didn’t recognize it, it’s actually code that Microsoft wrote. It’s from sp_delete_backuphistory, and it is plum awful.

I have a lot of personal dislike for this one, because after inheriting a server with a 25GB msdb, I nearly crashed it running this proc on one day of backups.

Back then, a really smart guy told me something: If you make a copy of it that uses temp tables instead, it’s a lot faster.

The issues I have with it can be distilled into three things

Table variables, c’mon
Running eight different deletes inside one BEGIN TRAN
TABLE VARIABLES, C’MON

Making Faces

Anyone who has processed data in volume knows how bad table variables can be. The inserts are forced serial, they spill to tempdb a lot, and unless you recompile (which this proc doesnt), you end up with a one row estimate.

Which makes it doubly awkward to trip yourself up inserting to a table variable with a predicate on another table variable. Have mercy.

I have no idea if sticking a PK on any of these might help. Heck, the DML sort might even slow things down.

But it’s not like the optimizer is gonna know you went through the trouble of selecting distinct IDs, otherwise. Table variables get no column level statistics.

This is the thing I like about the temp tables — you can stick an index on them afterwards. In this scenario, I’m in favor of that causing a statement level recompile.

Begin Without End, Amen

The worst thing, though, really is the use of a transaction around eight deletes.

You can have a god awful amount of data in these tables, even in a single day. The server that I had issues with had around 5000 databases on it, getting ~15 minute log backups.

Plus daily fulls and 6 hour Diffs.

Do that math.

The whole time a delete is running, the log backup jobs running couldn’t write to the tables.

If one of them throws an error towards the end, they all have to roll back. Imagine 7 or 8 deletes all rolling back.

Blocking your log backup jobs from writing to tables.

So you can’t take more log backups.

And these are the people who write the product

If you’re an ISV, or someone getting started with SQL Server, you might poke around the code in the system for tips and tricks.

And you’d see stuff like this.

Which may explain some things…

Microsoft — if you’d like some help with SQL Server, click the consulting link at the top of the page!

Thanks for reading!

Creating Insert Triggers to Silently Ignore Data You Don’t Want

Last Updated February 1, 2018

Brent Ozar

Bad Idea Jeans, T-SQL

Say you’ve got an application that insists on inserting data into the database, and…you don’t want the data.

You want the application to THINK it inserted the data – you don’t want to roll it back or return an error to the end user. You just don’t want the data, and you don’t want the hassle of deleting it later.

Here’s our imaginary table Documents:

CREATE TABLE dbo.Documents
    (DocumentName VARCHAR(50), 
     CreationDate DATETIME);
GO

CREATE TABLE dbo.Documents

(DocumentName VARCHAR(50),

CreationDate DATETIME);

We want to ignore all documents with a CreationDate prior to 2018. Enter our new friend the INSTEAD OF trigger:

CREATE OR ALTER TRIGGER IgnoreOldDocuments ON dbo.Documents
    INSTEAD OF INSERT
 AS
    BEGIN
    SET NOCOUNT ON;
    INSERT dbo.Documents(DocumentName, CreationDate)
        SELECT DocumentName, CreationDate
        FROM inserted
        WHERE CreationDate >= '2018/01/01';
    END
GO

CREATE OR ALTER TRIGGER IgnoreOldDocuments ON dbo.Documents

INSTEAD OF INSERT

BEGIN

SET NOCOUNT ON;

INSERT dbo.Documents(DocumentName, CreationDate)

SELECT DocumentName, CreationDate

FROM inserted

WHERE CreationDate >= '2018/01/01';

END

The SET NOCOUNT ON statement is important here because it hides the “1 row(s) affected” message that would normally come out of the trigger.

Now, when we insert two rows – one before 2018, and one after – they both work:

INSERT INTO dbo.Documents (DocumentName, CreationDate)
    VALUES ('December 2017', '2017/12/01');
INSERT INTO dbo.Documents (DocumentName, CreationDate)
    VALUES ('January 2018', '2018/12/01');
GO

INSERT INTO dbo.Documents (DocumentName, CreationDate)

VALUES ('December 2017', '2017/12/01');

INSERT INTO dbo.Documents (DocumentName, CreationDate)

VALUES ('January 2018', '2018/12/01');

Our application thinks they were both inserted:

But if I select the data out of the table:

This is just a starting point: you still need to communicate this to the dev team, and handle updates (because the app could set the CreationDate to an earlier value,) and deal with bulk inserts (where triggers are ignored by default.) It’s just something that I needed for a project, and I figured you might get a chuckle out of it too.

Using LIKE on Integers Gets You Implicit Conversion

Last Updated February 1, 2018

Brent Ozar

T-SQL

Using the Stack Overflow public export, take these two queries looking for a particular user by Id (the clustering key):

SELECT * FROM dbo.Users WHERE Id = 26837;
SELECT * FROM dbo.Users WHERE Id LIKE 26837;

1 2	SELECT * FROM dbo.Users WHERE Id = 26837; SELECT * FROM dbo.Users WHERE Id LIKE 26837;

The first one (=) gets a clustered index seek, does just 3 logical reads, and correctly estimates that only 1 row will be returned.

The second one (LIKE) does a clustered index scan, reads the entire table, and wildly overestimates that 475,005 rows will be returned – even though only 1 row will.

The actual execution plans complete with the warning on the SELECT statement tell the story: SQL Server thinks you’re implicitly converting the Id to a VARCHAR(12), and then doing a comparison there.

Wanna go faster without changing your code? Sure, we all do – so give SQL Server a pre-converted field that it can use for comparisons:

ALTER TABLE dbo.Users ADD IdAsVarchar AS CAST(Id AS VARCHAR(12));
CREATE INDEX IX_IdAsVarchar ON dbo.Users(IdAsVarchar);

1 2	ALTER TABLE dbo.Users ADD IdAsVarchar AS CAST(Id AS VARCHAR(12)); CREATE INDEX IX_IdAsVarchar ON dbo.Users(IdAsVarchar);

We’re adding a computed field, then indexing that field.

Then run your really, really stupid query again – don’t change it, just run it exactly as-is:

SELECT * FROM dbo.Users WHERE Id LIKE 26837;

1	SELECT * FROM dbo.Users WHERE Id LIKE 26837;

And presto, SQL Server does an index seek on our newly created computed field:

Sure, it’s still warning about implicit conversion, but get this: it now correctly estimates just 1 row will come back, and does just 6 logical reads.

I’d rather fix the code, but if you can’t, this is a pretty spiffy way to help SQL Server do bad things – faster.