Blog - Brent Ozar

Microsoft Azure

You’ve got an Azure SQL DB, and your queries are going slow. You’re wondering, “Am I hitting the performance limits? Is Microsoft throttling my queries?”

There’s an easy way to check: run sp_BlitzFirst. sp_BlitzFirst is our free performance health check stored procedure that analyzes a lot of common performance issues and then gives you a prioritized list of reasons why your server might be slow right now.

Here’s what it looks like when you’ve hit your DTU limits – note that there are multiple rows, one for each 15-second period where you approached or hit your DTU limits in the last 15 minutes:

Azure SQL DB is maxing out

If you click on the “ClickToSeeDetails” column, you get:

<?ClickToSeeDetails -- 
At 2018-10-21 17:02:09.153, your database approached (or hit) your DTU limits:
Average CPU percent: 99.88
Average data IO percent: 0.00
Average log write percent: 0.32
Max worker percent: 2.22
Max session percent: 0.88
 -- ?>

<?ClickToSeeDetails --

At 2018-10-21 17:02:09.153, your database approached (or hit) your DTU limits:

Average CPU percent: 99.88

Average data IO percent: 0.00

Average log write percent: 0.32

Max worker percent: 2.22

Max session percent: 0.88

-- ?>

We’re checking the last 15 minutes of the sys.dm_db_resource_stats management view to see if you hit 90% or higher for avg_cpu_percent, avg_data_io_percent, avg_log_write_percent, avg_memory_usage_percent, max_worker_percent, or max_session_percent.

Uh oh – I’m maxed out. Now what?

You could tune queries, tune indexes, lower your app’s workload, or upgrade to a higher DTU limit.

To find which queries to tune, run sp_BlitzCache @SortOrder = ‘___’, where ___ is based on which limit you’re hitting:

CPU limits – run sp_BlitzCache @SortOrder = ‘cpu’
Data IO – run sp_BlitzCache @SortOrder ‘reads’
Log write – run sp_BlitzCache @SortOrder = ‘writes’

If you’d rather tune indexes, run sp_BlitzIndex and focus on the missing index warnings. They’re chock full of dangerous pitfalls, but they’re still one of the easiest ways to get started. We teach you how to interpret the recommendations in our Fundamentals of Index Tuning videos.

Lowering your app’s workload is a totally valid option, too: try caching things in your application rather than hitting the database every time, or staggering your workload so that queries are run at different times.

And of course, you could throw hardware at it by raising your DTU level. If you started on a really low level, like S1/S2/S3, and you’re constantly hitting these throttling limits, then it might be time to upgrade. To keep things in perspective, an S4 with 200 DTUs is only $300/month – if you’re any lower than that, you’re probably going to hit limits sooner rather than later.

All these scripts are part of our free First Responder Kit.

Indexed View Matching With GROUP BY And DISTINCT

December 5, 2018

Execution Plans, Indexing

Bit Of A Kick

I’ve been playing with indexed views a little bit lately for some demos in my Bits Precon.

There are a whole bunch of limitations in creating indexed views. One of them is that you can’t base the query on DISTINCT.

Fair enough, but you can do GROUP BY.

And what’s pretty cool is that the optimizer can match a query written to find distinct values to an indexed view with a group by.

Best Example Ever

Here’s my indexed view:

CREATE OR ALTER VIEW dbo.distincto
WITH SCHEMABINDING 
AS
SELECT b.UserId, 
       p.LastEditorUserId, 
       COUNT_BIG(*) AS why_is_this_still_a_thing
FROM   dbo.Badges AS b
JOIN   dbo.Posts AS p
    ON b.UserId = p.OwnerUserId
WHERE  p.LastEditorUserId > 0
GROUP BY b.UserId, 
         p.LastEditorUserId;
GO 

CREATE UNIQUE CLUSTERED INDEX cx_distincto 
    ON dbo.distincto (UserId, LastEditorUserId);

CREATE OR ALTER VIEW dbo.distincto

WITH SCHEMABINDING

SELECT b.UserId,

p.LastEditorUserId,

COUNT_BIG(*) AS why_is_this_still_a_thing

FROM dbo.Badges AS b

JOIN dbo.Posts AS p

ON b.UserId = p.OwnerUserId

WHERE p.LastEditorUserId > 0

GROUP BY b.UserId,

p.LastEditorUserId;

CREATE UNIQUE CLUSTERED INDEX cx_distincto

ON dbo.distincto (UserId, LastEditorUserId);

Here’s my query:

SELECT DISTINCT
       b.UserId, 
       p.LastEditorUserId
FROM   dbo.Badges AS b
JOIN   dbo.Posts AS p
    ON b.UserId = p.OwnerUserId
WHERE  p.LastEditorUserId > 0;

SELECT DISTINCT

b.UserId,

p.LastEditorUserId

FROM dbo.Badges AS b

JOIN dbo.Posts AS p

ON b.UserId = p.OwnerUserId

WHERE p.LastEditorUserId > 0;

And here’s the query plan:

I think that’s pretty nifty.

Thanks for reading!

Brent says: I’m always amazed at how smart SQL Server is when I’m least expecting it. Microsoft has put so much work into that query optimizer, and it just keeps getting better. It ain’t perfect – but it’s pretty doggone good.

Indexed View Creation And Underlying Indexes

December 4, 2018

Indexing

Accidental Haha

While working on some demos, I came across sort of funny behavior during indexed view creation and how the indexes you have on the base tables can impact how long it takes to create the index on the view.

Starting off with no indexes, this query runs in about six seconds.

DECLARE @blobeater SQL_VARIANT;

SELECT DISTINCT
       @blobeater = b.UserId, 
       @blobeater = p.LastEditorUserId
FROM   dbo.Badges AS b
JOIN   dbo.Posts AS p
    ON b.UserId = p.OwnerUserId
WHERE  p.LastEditorUserId > 0;

DECLARE @blobeater SQL_VARIANT;

SELECT DISTINCT

@blobeater = b.UserId,

@blobeater = p.LastEditorUserId

FROM dbo.Badges AS b

JOIN dbo.Posts AS p

ON b.UserId = p.OwnerUserId

WHERE p.LastEditorUserId > 0;

Here’s the plan and the query stats:

Whole Lotta Hashing

Table 'Badges'. Scan count 5, logical reads 50379, 
Table 'Posts'. Scan count 5, logical reads 4206390, 
Table 'Workfile'. Scan count 0, logical reads 0, 
Table 'Worktable'. Scan count 0, logical reads 0, 

 SQL Server Execution Times:
   CPU time = 19079 ms,  elapsed time = 5489 ms.

Table 'Badges'. Scan count 5, logical reads 50379,

Table 'Posts'. Scan count 5, logical reads 4206390,

Table 'Workfile'. Scan count 0, logical reads 0,

Table 'Worktable'. Scan count 0, logical reads 0,

SQL Server Execution Times:

CPU time = 19079 ms, elapsed time = 5489 ms.

Not bad, at 5.5 seconds elapsed. The optimizer thinks an index on Posts would help.

The join column is an include, which is kinda weird, but hey…

CREATE INDEX ix_whatever
    ON dbo.Posts ( LastEditorUserId )
    INCLUDE ( OwnerUserId );

CREATE INDEX ix_whatever

ON dbo.Posts ( LastEditorUserId )

INCLUDE ( OwnerUserId );

Out of curiosity, I added it, and re-ran the query.

Here’s the new plan and stats:

Still Hashing Things Out

Table 'Badges'. Scan count 5, logical reads 50379, 
Table 'Posts'. Scan count 5, logical reads 13160, 
Table 'Workfile'. Scan count 0, logical reads 0, 
Table 'Worktable'. Scan count 0, logical reads 0, 

 SQL Server Execution Times:
   CPU time = 11969 ms,  elapsed time = 3396 ms.

Table 'Badges'. Scan count 5, logical reads 50379,

Table 'Posts'. Scan count 5, logical reads 13160,

Table 'Workfile'. Scan count 0, logical reads 0,

Table 'Worktable'. Scan count 0, logical reads 0,

SQL Server Execution Times:

CPU time = 11969 ms, elapsed time = 3396 ms.

Saved a couple seconds, but now… The optimizer is asking for a different index.

It wants on on OwnerUserId, LastEditorUserId. Okay then.

CREATE INDEX ix_apathy 
    ON dbo.Posts ( OwnerUserId, LastEditorUserId );

1 2	CREATE INDEX ix_apathy ON dbo.Posts ( OwnerUserId, LastEditorUserId );

… And it doesn’t get used. It’s the same plan and stats as the last time.

I think that’s silly, and I also think it’s silly that The Optimizer doesn’t want an index on the Badges table at all.

Wonder why. Let’s add one.

CREATE INDEX ix_ennui 
    ON dbo.Badges ( UserId );

1 2	CREATE INDEX ix_ennui ON dbo.Badges ( UserId );

Sweet death, now the query takes about a minute and a half.

Here’s the new plan and stats:

No Use For An Index

Table 'Posts'. Scan count 5, logical reads 13188, 
Table 'Badges'. Scan count 5, logical reads 14076, 
Table 'Workfile'. Scan count 0, logical reads 0, 
Table 'Worktable'. Scan count 0, logical reads 0, 

 SQL Server Execution Times:
   CPU time = 214016 ms,  elapsed time = 75999 ms.

Table 'Posts'. Scan count 5, logical reads 13188,

Table 'Badges'. Scan count 5, logical reads 14076,

Table 'Workfile'. Scan count 0, logical reads 0,

Table 'Worktable'. Scan count 0, logical reads 0,

SQL Server Execution Times:

CPU time = 214016 ms, elapsed time = 75999 ms.

I can’t have a query run for this long. Clearly I need a way to make it faster.

This is where things got funny, I promise.

Creating The View

Here I am, a fairly experienced SQL Server user. I at least remembered that you can’t use DISTINCT in an indexed view.

You can GROUP BY, as long as you remember to COUNT BIG.

Let’s do that.

CREATE OR ALTER VIEW dbo.distincto
WITH SCHEMABINDING 
AS
SELECT b.UserId, 
       p.LastEditorUserId, 
       COUNT_BIG(*) AS why_is_this_still_a_thing
FROM   dbo.Badges AS b
JOIN   dbo.Posts AS p
    ON b.UserId = p.OwnerUserId
WHERE  p.LastEditorUserId > 0
GROUP BY b.UserId, 
         p.LastEditorUserId;
GO

CREATE OR ALTER VIEW dbo.distincto

WITH SCHEMABINDING

SELECT b.UserId,

p.LastEditorUserId,

COUNT_BIG(*) AS why_is_this_still_a_thing

FROM dbo.Badges AS b

JOIN dbo.Posts AS p

ON b.UserId = p.OwnerUserId

WHERE p.LastEditorUserId > 0

GROUP BY b.UserId,

p.LastEditorUserId;

Then I went to add the unique clustered index.

CREATE UNIQUE CLUSTERED INDEX cx_distincto 
    ON dbo.distincto (UserId, LastEditorUserId);

1 2	CREATE UNIQUE CLUSTERED INDEX cx_distincto ON dbo.distincto (UserId, LastEditorUserId);

Then I waited.

And waited.

And after about 30 seconds (seriously, that’s how impatient I am), I decided to see what the create index was up to.

It had the same plan as when I ran the query, and took about the same amount of time.

Feeling a little bit crazy, I backtracked up to the point where I created the index on Badges, and re-created the indexed view.

The new plan for creating indexed view clustered index morphed back into the “fast” plan for the query.

It also only took about 6 seconds.

Is There A Moral?

There are a few!

Missing index requests are kinda bananas
Sometimes the lack of a missing index request isn’t bananas
If you want to create an indexed view for a slow query, you can expect creating the index to take at least as long as running the query
If you want to create indexed views faster, tuning the underlying indexes might not be a bad idea

Thanks for reading!

How to Log Active Queries with sp_BlitzWho

December 3, 2018

Brent Ozar Unlimited Team

Monitoring

Queries are running when you’re not around. They’re wreaking havoc – maybe they’re filling up your TempDB, or causing blocking, or flushing your buffer pool out.

When I wanna see what queries are running, I run sp_BlitzWho:

That shows me who’s running the query, what it’s been waiting on over time, memory grants, blocking, and even the live query plan (if I’m on a supported build.) But of course, that’s only right now.

So to log queries when you’re not looking…

Set up an Agent job to run this every, say, 5-15 minutes:

sp_BlitzWho 
  @OutputDatabaseName = 'DBAtools', 
  @OutputSchemaName = 'dbo',
  @OutputTableName = 'BlitzWho_Results';

sp_BlitzWho

@OutputDatabaseName = 'DBAtools',

@OutputSchemaName = 'dbo',

@OutputTableName = 'BlitzWho_Results';

That logs currently running queries to a table. Then, when someone asks you what was going on yesterday at 8PM, back when you were drowning your database memories in cheap beer, you can query that table’s output over time:

BlitzWho results over time

By default, only 3 days of history are kept in the table. You can control that with the @OutputTableRetentionDays parameter.

I don’t recommend leaving this Agent job running 24/7 on every server you have – if you need activity tracking, you’re better off with a monitoring tool or the Power BI Dashboard for DBAs. Instead, I like setting up the Agent job to run every minute, but disable the job. Then, when a problem is happening, I’ll train my team to enable that Agent job even if I’m not around. That way, I can go back later to see what was happening on the server at the time.

Catching blocking, TempDB usage, or long-running queries

To make these scenarios easier to troubleshoot, sp_BlitzWho has a few filtering parameters:

@MinElapsedSeconds
@MinCPUTime
@MinLogicalReads
@MinPhysicalReads
@MinWrites
@MinTempdbMB
@MinRequestedMemoryKB
@MinBlockingSeconds

So that way, if you’re specifically trying to narrow down a query that’s blowing up your TempDB overnight, you can filter it down just to queries using TempDB.

Download it in the latest version of our First Responder Kit. It works in all supported SQL Server versions, from 2008 to Azure SQL DB. Enjoy!

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

December 2, 2018

Videos

This week, Brent, Tara, and Richie discuss storing phone numbers in a database, forced parameterization, how AI will affect DBA jobs in the future, what languages a DBA needs to learn, backup issues, measuring the overhead of transparent data encryption in terms of CPU on SQL Server, starting SQL server without tempdb, 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.

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-11-28

Brent Ozar: Jason asks, “Do you have any suggestions on storing phone numbers? I was thinking bigint versus varchar, say, 15. Do I need to deal with international supports; smallint, ex, field for optional extension s, any thoughts?

Tara Kizer: This is a Richie question, for sure.

Richie Rump: Yeah, use character fields, because at some point you’re going to have to do international and there’s going to be a whole bunch of other stuff…

Tara Kizer: Really?

Richie Rump: Yeah, character fields. And then, what you want to do is format that, not in the database but on the way in through the app, in the app, whatever format so it’s consistent at the very least. Otherwise, you don’t want to have all these numbers and just not have any format because then you’ll have all these different formats all over the place, then your app doing all this work to reformat it. But scrub it on the way in. Have it a consistent format. Store it in a varchar because you won’t be doing any math on it, so why an int?

Brent Ozar: Leading zeros are a problem. And then the other thing you end up having to worry about is commas, because in some auto-dialer software, you need commas to wait for a specific period of time. Terrible things I’ve learned, terrible.

Tara Kizer: Not something I’ve even thought about.

Brent Ozar: No, I was going to make phone number jokes when he first asked him. Like, what is this? You’re putting your black book inside a database; what’s going on there?

Richie Rump: I mean, you don’t, Brent?

Brent Ozar: No. I’m married. I don’t even know my own phone number. I’m just like, honey…

Brent Ozar Shaun asks, “Is there a way to send unique metadata with queries that doesn’t bloat the plan cache? I’d like to send a value of where in my code a query is being called from. I have forced parameterization turned on, but most queries are already parameterized. It would be nice if there was a flag to turn on or off comment stripping before creating plans.”

Tara Kizer: I don’t even understand the question; unique metadata with queries – oh, the documentation in it? I mean, it comes across. It’s in there.

Brent Ozar: I’m going to whip out SQL Server Management Studio, because I don’t think it works exactly the way you think it does, forced parameterization. And now, when you run plan cache queries like sp_BlitzCache, that’s going to only show the parameterized string in there. So we’re going to pop into Stack Overflow 2013. I’m going to set forced parameterization at the database level. So parameterization, let’s go into forced and say okay. Let’s set a new query. And I’m going to put in a comment. I’m going to say, hi mom, and then select star from dbo.users where display name equals Richie Rump or display name equals Tara Kizer – neither of which are their real names on Stack Overflow…

Tara Kizer: No, I’m Tara Kizer on Stack Overflow.

Richie Rump: I’m not…

Brent Ozar: Are you really? You guys might be thinking DBA.StackExchange…

Tara Kizer: Oh, well no, I should be…

Brent Ozar: Alright, so here we’ll do Richie’s…

Tara Kizer: I had to ask, I think, a PowerShell question a few years ago, so I’m on the other side.

Brent Ozar: So now here’s that. If I go query include actual execution plan and I go run it, in the execution plan, it doesn’t look like it’s there. And even if you go into the add a query text, which is freaking broken in SSMS – this drives me crazy. But if I go into sp_BlitzCache and I go look at the plan cache, hopefully it’s going to be up in that list. So here we go. So here it looks like your comment isn’t included. But if I go click on the query plan, it’s going to be inside here if I edit the query text. As you can see here, the comment is completely gone. What the hell? It should have preserved the comment inside there. I would have been sure it would have preserved the comment inside there.

Tara Kizer: I certainly see comments when I open up people’s plans.

Brent Ozar: yeah, even with forced parameterization.

Tara Kizer: Maybe it’s only stored procedures?

Brent Ozar: Oh, well stored procedures will definitely do it. I’m going to gamble and move it. I’m going to try moving it in over here.

Tara Kizer: And I just looked up my name over there. It’s just Tara. I’m surprised. I’ll have to change it.

Brent Ozar: let’s free the plan cache and then let’s try it again, just to see, execute, and then run sp_BlitzCache and see. If it doesn’t show up here either, I’m going be pissed. It doesn’t show up here either. Oh, there is – we’ve already blogged about this even. How does this even happen? Shaun, if we’ve blogged about it, why are you making us look stupid on the live webcast?

Richie Rump: Because it’s so easy, Brent.

Brent Ozar: Copy, paste – I didn’t know … was a movie. Oh, Kendra blogged about it. Oh, okay, so alright. No, in that case, I would read whatever Kendra wrote and then pay attention there. Sadly, we don’t…

Tara Kizer: Consider it gold if Kendra wrote it.

Brent Ozar: It’s true. She’s way smarter than we are.

Brent Ozar: Let’s see, next up August says, “Hi Brent, I notice that you have included Ola’s scripts in your DBA first aid bundle. I was wondering how I’d manage version control between checking your updates and Ola’s? By the way, your great post, Brent, is amazing.” Someone’s trolling us. So we don’t include Ola’s scripts. We don’t include Ola’s script at all. [crosstalk]

Tara Kizer: I was just about to go check, like really?

Brent Ozar: No, no, no, no. We don’t want to manage that kind of version control either. Tara wrote sp_DatabaseRestore, which relies on Ola’s scripts, but we don’t ship them with our First Responder Kit.

Brent Ozar: Jason, throwing back to his question about how to store phone numbers says he was, “Thinking about using bigint because it would be smaller in terms of size.” Yeah, but your problems will be larger.

Richie Rump: Yep.

Brent Ozar: Let’s see, Mark asks, “How do you think artificial intelligence will affect DBA jobs in the future? Also, will DBAs need to learn more languages than just SQL? I know you dislike PowerShell.” We’ll go each through this. So how artificial intelligence will affect DBA jobs in the future – Tara, you first.

Tara Kizer: I don’t think it’s going to affect us. I mean, maybe if there’s someone starting out new, if they’re in their 20s and becoming a DBA, you know, we’re going to retire in the next 20 years or so. I really don’t think it’s going to affect us completely, but certainly, a lot of people will have moved to the cloud and you do have some changes happening for you. But it still takes a human being to be able to look at things. I mean, even BlitzCache, which has a lot of logic built in to help you find things, you still have to open it up and dig into it. And I don’t know that AI, at least for the stuff that we work on, would ever be smart enough to really do what a really good DBA can do.

Brent Ozar: For a while there, there were people who were trying to build something that would automated test your T-SQL to make sure it was the same after changes. Their goal was to have tools like artificial intelligence tune a query the way a DBA would and make sure that it returns the same results afterwards, and those projects keep stumbling all over the place too. Richie, how about you?

Richie Rump: I think we’ve been asking this question for, what, the last 40 years or so. And every time it comes up, people are going to say, we’re going to lose our jobs. But technology keeps improving and yet we still have our jobs. Now, our jobs have changed a bit. We’re not managing so much at the detail level; we’re a little bit higher up. But it’s going to be the same thing. We’re going to manage maybe something at the higher up or maybe we’re going to transition to something different, but we’re not going to be losing our jobs. It’s just going to be something else, something more important that’s going to come on and we’re just going to jump on it. I mean, even when we are working with Aurora, which is pretty much hands off – even to spin it up and to get it going, it’s just click, click, click, boom and all of a sudden we have a cluster in the cloud – there’s still stuff in it that we have to know and we have to get into because we kept crashing it. There you go.

Brent Ozar: I think it’s going to keep fixing smaller problems, you know, that we’re always fixing harder problems these days. Man, I would just love for database servers to back themselves up. It feels like is that so much to ask?

Richie Rump: You look good, won’t you back that thing up?

Brent Ozar: Why can’t, when the SQL Server gets installed, why can’t there be a wizard that says, where would you like your backups to go and how long would you like to keep them? That seems like genuine intelligence to me. It’s not really that hard, but it’s a question of resources.

Richie Rump: Actually, the cloud does that.

Brent Ozar: The cloud does it?

Tara Kizer: Yeah.

Brent Ozar: Yeah, but it’s not artificial intelligence, it’s just a freaking wizard.

Richie Rump: That’s right.

Brent Ozar: Before it steps through, it’s really not that hard. But people are sticking with a whole lot of on-premises databases and this stuff just isn’t getting any easier. Even when you go to the cloud, there’s a lot of this, okay now I need to do a restore, I need developers to start provisioning out restores, I need to manage the security of data, I need to manage what parts of data get masks, what parts do not. There’s things I get excited about for AI to cover, but it’s just not covering everything. It’s not even close.

Richie Rump: Right now, I don’t think that any company’s going to say, let’s go ahead and put all this effort into all these little AI projects and it’s not going to increase our bottom line whatsoever.

Brent Ozar: And really, if you’re building all of that, it’s not cutting your costs today. AI is still way too expensive. Try and go find someone off the street who’s willing to work for nothing as an AI researcher. It’s not going to happen. You had another question in there too like, what languages will a DBA have to learn? If I was a production DBA today – if you made me go back and get a production DBA fulltime job where I was managing multiple servers, I’d learn PowerShell in a heartbeat; not because I want to but because I think I would have to. In our weirdo jobs as consultants, we’re really brought in to kind of resuscitate one server at a time, so we don’t end up using it. But I do believe that it’s a really cool interesting language for those of you who have to manage armies of servers. I just don’t want that work myself.

Richie Rump: That’s for the DBA side. But if you’re on the database development side, it should be Python. Python is the language you should know, not PowerShell, because I don’t even think PowerShell is a language. It’s Python. Learn Python if you are a developer.

Tara Kizer: I’ve actually written a few PowerShell modules over the years and I just learn it – I relearn it every single time I have to write a new one, and I just Google the hell out of it until it starts working.

Brent Ozar: That’s what we also call artificial intelligence.

Tara Kizer: Although I have taken a PowerShell training class a few years ago.

Brent Ozar: Yeah, it’s hard.

Richie Rump: We have PowerShell training classes coming up, don’t we, Brent?

Brent Ozar: We do, and here it is, it’s on the slide. Drew Furgiuele with Hands-on Labs. And it’s specifically for DBAs too. I’ve gone through it too as well. And it teaches you how to accomplish specific tasks that DBAs need to accomplish, which is what I think is much more important in learning. I don’t want to learn internals that I’m never going to map to my day to day job. I want to learn something that’s going to help me do better tomorrow. That’s what Drew’s stuff is focused on.

Tara Kizer: I just saw the slide. It says it uses Hands-on Labs. Is that the virtual machines from AWS also?

Brent Ozar: Yeah, exact same thing.

Tara Kizer: Awesome. I didn’t realize we had any guest speakers that were doing the Hands-on Labs also.

Brent Ozar: I try to encourage everybody to do it. Like, any class that has labs, the students seem to retain better when it’s a practical topic. It’s less about when it’s a theoretical topic.

Brent Ozar: Gordon asks, “A problem with my SSMS initiated backup is it’s timing out after less than a minute. It had to do with the execution timeout being set to 45 seconds. Why did the backup timeout since the backup had already started?”

Tara Kizer: Why is anybody changing the Management Studio timeout setting? I mean, that’s one of the benefits of being able to run queries in Management Studio, that it by default does not timeout. The answer to his question is basically it’s a kill command. You’ve cancelled the query when the timeout happens.

Brent Ozar: That would be bad.

Tara Kizer: It just seems odd to have changed that setting.

Brent Ozar: I’m wondering if maybe somebody didn’t do it to say, oh if I have a query that runs away, I want it to automatically reel back in. But man, that’s dangerous. That gives me the heebie-jeebies. I’m also a big fan of not doing backups via SSMS. I would much rather kick them off via agent jobs, even if it’s a one-time thing, just so that I can make sure that agent will keep running the thing and it will succeed to completion. That’s probably not a real phrase.

Augusto asks, “How can we measure the overhead of transparent data encryption in terms of CPU on the SQL Server?”

Tara Kizer: What kind of load testing do you have? This needs to be done in an environment before you hit production; apply a production load to a test server and see what happens. Hopefully it’s production-like hardware. Check what your system needs.

Brent Ozar: And when you don’t know how to do a load test, because a lot of us don’t, just start with index rebuilds, backups, and CHECKDB; things that you would usually do in a maintenance plan. And you can look at the differences between those while they run.

Tara Kizer: Yeah, good luck on those backups. You get no compression with TDE.

Brent Ozar: So there was a switch. So I can’t remember what Service Pack they came out with, and I want to say it was 2016, where it all of a sudden gave you compression on backups. And after that, there were like six Cumulative Updates in a row that fixed corrupt backup issues. It was so bad. Whatever you do, don’t enable this without CU whatever…

Richie Rump: Oh my gosh…

Brent Ozar: Yeah, it was bad. Plus the worst of it was that your backups succeeded but you just couldn’t restore from them.

Tara Kizer: Oh wow, and how many people are doing those test restores? I mean, I always did, but I don’t think very many people in the world are testing their backups.

Brent Ozar: Not nearly enough. Not enough people in the world take their backups.

Tara Kizer: True. We know that.

Brent Ozar: Teschal asks, “Is it possible to start SQL Server without tempdb or provide a different drive on the fly?”

Tara Kizer: What world of hurt is Teschal in right now? Certainly, it can from the command line – SQL Server .exe and there’s a switch that you can use to have minimal stuff startup. But I think tempdb can be excluded. Okay …

Brent Ozar: Of course he does.

Richie Rump: Of course he does.

Brent Ozar: There you go – /F, minimal configuration, that’s what it is. And then you can alter the tempdb location from there. So what Teschal probably did was alter and set the wrong location for a path that doesn’t exist. Teschal, I’ve been there too.

Richie Rump: I love in the cloud; I haven’t been there.

Brent Ozar: J.H. says, “I’m trying to track logins to a particular database. I’m trying to figure out if that database is still being used. Is a server-side SQL trace pretty much the T-SQL example of the profiler GUI that just runs on the SQL Server?”

Tara Kizer: I’m not quite sure what J.H. is asking because with the server-side SQL trace, you have to select which events you want to do. It’s very tedious work, so I usually use profiler first. It gives me the template after [suck all my vents], and then I script it out and then I’m good to go with the server-side trace. But you have to add your events. Server-side trace doesn’t give you anything; you have to add things to it.

Brent Ozar: Yeah, say that you want to replay exactly what happened. And I’m going to do a terrible trace here just to show as an example. And then yeah, sure, whatever, untitled one is fine. And run, and then immediately stop. If I go file templates – no it’s not templates. Save as trace template, is that what it is? Yeah, boy, it’s been a long time since I’ve done this…

Tara Kizer: No, no, no, it’s under file. It’s definitely under file. Save as – there it goes, yep.

Brent Ozar: Is that it?

Tara Kizer: Yep.

Brent Ozar: Okay, now let’s see if we can open it. Now, who the hell knows where this went?

Tara Kizer: Yeah, I was going to say… There it is.

Brent Ozar: there we go. So yeah, then you can save that as a server-side trace. But is it the same overhead, if that’s what you’re asking? Its overhead is not as bad as you’re running it on your local machine in Tucson, Arizona when your production server’s in Buffalo, New York, you know, trying to go across data centers with huge latency. It’s a lower latency. If you’ve got to do a trace, this is a better way to do it. The other thing I would do is, in a login trigger, I don’t know if you can – well, you know what, it wouldn’t matter. And you know, J.H. I don’t think yours is going to matter either because the problem is, are you really going to be sure that, say, the Stack Overflow 2010 database isn’t used. If someone could go into, say, tempdb and they can run a query like select star from Stack Overflow 2010 dbo.users, even though I’m not logged into that database, I can still run that query. So I don’t know that a trace is really going to get you there.

Tara Kizer: I would probably just disable logins, rather than taking the database offline, just disabling it and then I would imagine that the applications have some kind of alerting in place to notify you’ve got some kind of problem.

Brent Ozar: I love it. That’s even better than – I was going to say – set it to read-only, but that’s bad. I like your idea better.

Brent Ozar: Sheila says, “We have a mass run…” And as soon as she says that, all I can think of is the video Chicken Run, where these chickens are running from the…

Richie Rump: I was thinking like a marathon and we have all these little jobs just in corrals and everybody’s just trying to get through that one start just to get going.

Brent Ozar: That’s because Richie works with our serverless applications, where we have, all of a sudden, thousands of jobs that are trying to – or Amazon Server, Postgres…

Richie Rump: Run at the same time, yeah.

Brent Ozar: “We have a mass run with high executions every night at the same time. We have no consistency with the number of claims per second that get processed during that time. CPU and memory are all consistent from one night to the next and I’m gathering wait stats every 10 minutes now via the first aid kit. What should I look for?” So I would – god, there’s so many interesting things here. I would probably start with when you say our first aid kit, it’s one thing to gather the stats. The other thing you want to do is use the PowerBI dashboard to go check and see the wait stats over time. Richie’s giving a face. Are you giving that face because you can’t believe that I said the words PowerBI, or what are you saying?

Richie Rump: I still can’t believe you actually use PowerBI.

Brent Ozar: I know, right, and enough that I use it in my browser now. I have an Azure account to use it.

Richie Rump: It’s insane – so easy, Brent Ozar can use it.

Brent Ozar: And enjoys it, yeah. It still has more bugs than a mass run of whatever. It’s buggy, and it’s missing tons of features, but I still like it a lot. So this is what I would start with; graphing things out with the First Responder Kit’s PowerBI dashboard, and then you can see what wait stats look different during that time and tackle whatever your top wait type is. Maybe it’s blocking, for example, and that’s why you can’t get more throughput. That’s a common one. THREADPOOL waits, there could be all kinds of things that would get you. Alright, well that’s all the questions that we have this week at Office Hours. Thanks, everybody for hanging out with us and we will see y’all next week at Office Hours.

What Is SQL Injection?

December 1, 2018

T-SQL

Say we have a stored procedure that queries the Stack Overflow database. We have two separate parameters, @DisplayName and @Location, so folks can search for people by name, location, or both.

For performance reasons, we decide to build dynamic SQL:

CREATE OR ALTER PROC dbo.UserSearch 
	@DisplayName NVARCHAR(40) = NULL,
	@Location NVARCHAR(100) = NULL AS
BEGIN
DECLARE @StringToExecute NVARCHAR(4000);
SET @StringToExecute = N'SELECT * FROM dbo.Users WHERE 1 = 1 ';

IF @DisplayName IS NOT NULL
	SET @StringToExecute = @StringToExecute + N' AND DisplayName = ''' + @DisplayName + N''' ';

IF @Location IS NOT NULL
	SET @StringToExecute = @StringToExecute + N' AND Location = ''' + @Location + N''' ';

PRINT @StringToExecute;
EXEC(@StringToExecute);
END
GO

CREATE OR ALTER PROC dbo.UserSearch

@DisplayName NVARCHAR(40) = NULL,

@Location NVARCHAR(100) = NULL AS

BEGIN

DECLARE @StringToExecute NVARCHAR(4000);

SET @StringToExecute = N'SELECT * FROM dbo.Users WHERE 1 = 1 ';

IF @DisplayName IS NOT NULL

SET @StringToExecute = @StringToExecute + N' AND DisplayName = ''' + @DisplayName + N''' ';

IF @Location IS NOT NULL

SET @StringToExecute = @StringToExecute + N' AND Location = ''' + @Location + N''' ';

PRINT @StringToExecute;

EXEC(@StringToExecute);

END

When we run it, it works, and we can see the query on the Messages tab in SSMS:

Finding Bobby Tables

But look what happens when the bad guy runs it:

Well, that’s not good

The bad guy can:

Close the DisplayName search string by adding a couple of apostrophes
Add a semicolon to end the first query
Add his own evil query, like drop database or create login or whatever
Finish the batch and ignore any subsequent syntax errors by throwing in a couple of minus signs

That’s a really simplistic example, but it can get way worse, as Bert Wagner explains in this GroupBy session on SQL injection.

The first step in avoiding this problem is to avoid using user inputs as-is. Pass the parameters in to sp_executesql instead, like this:

CREATE OR ALTER PROC dbo.UserSearch 
	@DisplayName NVARCHAR(40) = NULL,
	@Location NVARCHAR(100) = NULL AS
BEGIN
DECLARE @StringToExecute NVARCHAR(4000);
SET @StringToExecute = N'SELECT * FROM dbo.Users WHERE 1 = 1 ';

IF @DisplayName IS NOT NULL
	SET @StringToExecute = @StringToExecute + N' AND DisplayName = @DisplayName ';

IF @Location IS NOT NULL
	SET @StringToExecute = @StringToExecute + N' AND Location = @Location ';

PRINT @StringToExecute;
EXEC sp_executesql @StringToExecute, 
	N'@DisplayName NVARCHAR(40), @Location NVARCHAR(100)', 
	@DisplayName, @Location;
END
GO

CREATE OR ALTER PROC dbo.UserSearch

@DisplayName NVARCHAR(40) = NULL,

@Location NVARCHAR(100) = NULL AS

BEGIN

DECLARE @StringToExecute NVARCHAR(4000);

SET @StringToExecute = N'SELECT * FROM dbo.Users WHERE 1 = 1 ';

IF @DisplayName IS NOT NULL

SET @StringToExecute = @StringToExecute + N' AND DisplayName = @DisplayName ';

IF @Location IS NOT NULL

SET @StringToExecute = @StringToExecute + N' AND Location = @Location ';

PRINT @StringToExecute;

EXEC sp_executesql @StringToExecute,

N'@DisplayName NVARCHAR(40), @Location NVARCHAR(100)',

@DisplayName, @Location;

END

sp_executesql lets you pass in a list of parameter definitions, and then pass the parameters in safely. Now, when the bad guy calls it, here’s what the query looks like:

Whew – safer.

The bad guy doesn’t get a list of your databases.

Avoid EXEC, and use sp_executesql instead. Then, for more learning, check out:

The Curse and Blessings of Dynamic SQL by Erland Sommarskog
Exploits of a Mom by XKCD – the origin of Bobby Tables
Books Online’s coverage of SQL Injection

Updated First Responder Kit for November 2018: Get Into Sports Dummy

November 30, 2018

First Responder Kit Updates

COACH!

I dunno, I just like this picture.

You can download the updated FirstResponderKit.zip here.

sp_Blitz Improvements

#1786 – @dallyhorton made a good point: if we’re gonna warn you about Change Tracking, we should tell you which database it’s enabled for.
#1791 – Who do DMV queries think they are, blocking each other? Get over yourselves.
#1793 – CheckIds shouldn’t be twins, I suppose.
#1794 – We now skip growth checks on read only databases. A sensible change.
#1797 – Better support for Managed Instances.
#1799 – Filter out Managed Instance waits
#1802 – Skip alerting that your alerts aren’t alerted in RDS
#1814 – 2012SP3 is now officially unsupported. YOU KNOW WHAT WHAT MEANS.
#1839 – Added Managed Instance specific info
#1840 – There are a lot of false positives about Linux. Now there’s one less.
#1856 – More complete list of DBCC command sources to ignore

sp_BlitzCache Improvements

#1806 – You know what really helps when you’re reading long numbers? Commas.
#1812 – Hopefully avoid some invalid length errors on substrings.
#1819 – Improves checks for cursor problems in XML
#1847 – Joe Obbish discovered that you can have a bigint of statistic modifications. Someone get this guy a stats update, would ya?
#1855 – SQL Server 2014 SP3 added row goal information to query plan XML.
#1858 – Expanded the @StoredProcInfo parameter to also search for triggers and functions.

sp_BlitzIndex Improvements

#1848 – It was pretty weird realizing we didn’t collect check constraint information in here. Now we do, and we check to see if you’ve got a scalar udf in the definition. That’s all for now.

sp_BlitzFirst Improvements

#1795 – Something about delta views for multiple servers. Everything named Delta just goes over my head.
#1799 – Like Blitz, not filters out Managed Instance wait stats
#1807 – If you’re on a version of SQL Server that can use show you live query statistics, we’ll throw in warnings if we detect running queries with cardinality estimation or skewed parallelism issues.
#1815 – In Azure SQL DB, we now check sys.dm_db_resource_stats to see if your instance is being throttled.
#1836 – Improved startup time calculations for Azure SQLDB. Until they change something in a week.
#1857 – Re-ignoring some CLR waits that we used to ignore and stopped ignoring. Long story.

sp_BlitzWho Improvements

#1829 – You can now write sp_BlitzWho to a table. In another database. With filtering. It a whole thing, and on Monday, Brent will have a blog post explaining it. We just don’t support it < 2012 yet. You should really just upgrade anyway.

sp_BlitzQueryStore Improvements

#1819 – Same cursor stuff as BlitzCache
#1847 – Same stats mod counter stuff as BlitzCache
#1860 – Skip queries that hit sys.master_files in Azure

Power BI Dashboard for DBAs

#1810 – @hfleitas got mobile reports working. Now you can keep your SQL Server in your pocket, where hopefully it won’t get all warm and smushy.

sp_BlitzLock

#1841 – Removed duplicate columns from output. How they got in there is a mystery.
#1860 – When you run BlitzLock, the default search path is the system health session XE file. That throws a kinda obtuse error in Azure, where file paths like that have to be URLs that point to Azure Blob Storage. I didn’t fix anything, I just give you a more helpful error message.

sp_ineachdb Improvements

Aaron Bertrand was kind enough to open source this script and allow us to distribute it with the FRK. Everyone say thank you to Canada for making Aaron.

There were no changes to sp_AllNightLog, sp_AllNightLog_Setup, sp_DatabaseRestore, sp_BlitzInMemoryOLTP, sp_BlitzBackups, or sp_foreachdb 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.

Tales From Overindexing: Too Many One Column Indexes

November 29, 2018

Execution Plans, Indexing, SQL Server

Master Plan

Sometimes you see a query, and it’s hitting one table, and then the query plan looks like a vase full of spaghetti.

Usually, there’s a view involved.

Usually.

Sometimes, there’s just really weird indexing.

Here’s A Thing, Look At It

Promises

This is a query against one table. No views, no functions, no triggers.

How To Think Like An Index

When most people think about the way a query can use indexes, they usually think it’s limited to one, or sometimes two, if you do a key lookup. And most of the time, that’s about what happens.

A key lookup is when SQL Server gives your nonclustered index inadequacy issues. It’s basically saying “I’m busy Saturday, let’s hang out on Tuesday.”

If we have this index, and this query, we’ll get a Key Lookup.

CREATE INDEX ix_whatever ON dbo.Posts(ClosedDate, AnswerCount, AcceptedAnswerId);

SELECT *
FROM   dbo.Posts AS p
WHERE  p.AcceptedAnswerId > 0
AND    p.AnswerCount > 0
AND    p.ClosedDate >= '20100101';

CREATE INDEX ix_whatever ON dbo.Posts(ClosedDate, AnswerCount, AcceptedAnswerId);

SELECT *

FROM dbo.Posts AS p

WHERE p.AcceptedAnswerId > 0

AND p.AnswerCount > 0

AND p.ClosedDate >= '20100101';

Just Coffee

A Key Lookup is basically a join between the clustered index and nonclustered index. This is one reason why clustered index key columns are in all your nonclustered indexes, and you should be really careful how you choose your clustered index key columns.

That Seek Predicate down the bottom is the join relationship between the clustered and nonclustered indexes, which is the clustered index key column.

For every row that comes out of the nonclustered index, we grab the associated key column value, and join it to the clustered index to produce the columns that aren’t in the nonclustered index.

Look at me. I’m you.

You can’t control what kind of join gets used here (though that would be neat, maybe).

This is the most common, but not the only way that SQL Server can use multiple indexes.

What Happens With A Bunch Of Narrow Indexes?

Like, say, one on almost every column in Posts, but with only one column in each index.

CREATE INDEX ix_posts_AcceptedAnswerId ON dbo.Posts(AcceptedAnswerId);
CREATE INDEX ix_posts_AnswerCount ON dbo.Posts(AnswerCount);
CREATE INDEX ix_posts_ClosedDate ON dbo.Posts(ClosedDate);
CREATE INDEX ix_posts_CommentCount ON dbo.Posts(CommentCount);
CREATE INDEX ix_posts_CommunityOwnedDate ON dbo.Posts(CommunityOwnedDate);
CREATE INDEX ix_posts_CreationDate ON dbo.Posts(CreationDate);
CREATE INDEX ix_posts_FavoriteCount ON dbo.Posts(FavoriteCount);
CREATE INDEX ix_posts_LastActivityDate ON dbo.Posts(LastActivityDate);
CREATE INDEX ix_posts_LastEditDate ON dbo.Posts(LastEditDate);
CREATE INDEX ix_posts_LastEditorDisplayName ON dbo.Posts(LastEditorDisplayName);
CREATE INDEX ix_posts_LastEditorUserId ON dbo.Posts(LastEditorUserId);
CREATE INDEX ix_posts_OwnerUserId ON dbo.Posts(OwnerUserId);
CREATE INDEX ix_posts_ParentId ON dbo.Posts(ParentId);
CREATE INDEX ix_posts_PostTypeId ON dbo.Posts(PostTypeId);
CREATE INDEX ix_posts_Score ON dbo.Posts(Score);
CREATE INDEX ix_posts_Tags ON dbo.Posts(Tags);
CREATE INDEX ix_posts_ViewCount ON dbo.Posts(ViewCount);

CREATE INDEX ix_posts_AcceptedAnswerId ON dbo.Posts(AcceptedAnswerId);

CREATE INDEX ix_posts_AnswerCount ON dbo.Posts(AnswerCount);

CREATE INDEX ix_posts_ClosedDate ON dbo.Posts(ClosedDate);

CREATE INDEX ix_posts_CommentCount ON dbo.Posts(CommentCount);

CREATE INDEX ix_posts_CommunityOwnedDate ON dbo.Posts(CommunityOwnedDate);

CREATE INDEX ix_posts_CreationDate ON dbo.Posts(CreationDate);

CREATE INDEX ix_posts_FavoriteCount ON dbo.Posts(FavoriteCount);

CREATE INDEX ix_posts_LastActivityDate ON dbo.Posts(LastActivityDate);

CREATE INDEX ix_posts_LastEditDate ON dbo.Posts(LastEditDate);

CREATE INDEX ix_posts_LastEditorDisplayName ON dbo.Posts(LastEditorDisplayName);

CREATE INDEX ix_posts_LastEditorUserId ON dbo.Posts(LastEditorUserId);

CREATE INDEX ix_posts_OwnerUserId ON dbo.Posts(OwnerUserId);

CREATE INDEX ix_posts_ParentId ON dbo.Posts(ParentId);

CREATE INDEX ix_posts_PostTypeId ON dbo.Posts(PostTypeId);

CREATE INDEX ix_posts_Score ON dbo.Posts(Score);

CREATE INDEX ix_posts_Tags ON dbo.Posts(Tags);

CREATE INDEX ix_posts_ViewCount ON dbo.Posts(ViewCount);

And then a query that kinda sorta does some self-like joins with some specific predicates.

SELECT *
FROM dbo.Posts AS p
WHERE p.AcceptedAnswerId > 0
AND p.AnswerCount > 0
AND p.ClosedDate >= '20100101'
AND p.OwnerUserId IN
(
    SELECT p2.OwnerUserId
    FROM dbo.Posts AS p2
    WHERE p2.CommentCount > 0
    AND p2.CommunityOwnedDate IS NULL
    AND p.FavoriteCount = p2.FavoriteCount
    AND p.ViewCount = p2.ViewCount
)
AND p.CreationDate IN
(
    SELECT p3.CreationDate
    FROM dbo.Posts AS p3
    WHERE p3.LastActivityDate >= '20100101'
    AND p3.LastEditDate >= '20100101'
    AND p3.LastEditorDisplayName > ''
    AND p.LastEditorUserId = p3.LastEditorUserId
)
AND p.ParentId IN 
(
    SELECT p.ParentId
    FROM dbo.Posts AS p4
    WHERE p.PostTypeId = p4.PostTypeId
    AND p.Score = p4.Score
    AND p.Tags LIKE p4.Tags + '%'
);

SELECT *

FROM dbo.Posts AS p

WHERE p.AcceptedAnswerId > 0

AND p.AnswerCount > 0

AND p.ClosedDate >= '20100101'

AND p.OwnerUserId IN

(

SELECT p2.OwnerUserId

FROM dbo.Posts AS p2

WHERE p2.CommentCount > 0

AND p2.CommunityOwnedDate IS NULL

AND p.FavoriteCount = p2.FavoriteCount

AND p.ViewCount = p2.ViewCount

)

AND p.CreationDate IN

(

SELECT p3.CreationDate

FROM dbo.Posts AS p3

WHERE p3.LastActivityDate >= '20100101'

AND p3.LastEditDate >= '20100101'

AND p3.LastEditorDisplayName > ''

AND p.LastEditorUserId = p3.LastEditorUserId

)

AND p.ParentId IN

(

SELECT p.ParentId

FROM dbo.Posts AS p4

WHERE p.PostTypeId = p4.PostTypeId

AND p.Score = p4.Score

AND p.Tags LIKE p4.Tags + '%'

);

Unnatural Selection

You end up with the query plan I showed you earlier.

Promises

What’s happening here is called index intersection.

It’s pretty rare to see it for a variety of reasons. Most people don’t create a single column index on every eligible column of the table. There’s a special place in hell for people who do that.

By hell, I mean a soup restaurant in a Faraday Cage, with no liquor license, and live comedy.

It’s sort of an expensive process, joining a bunch of nonclustered indexes together, so the optimizer has to really think it’s a worthwhile strategy. You’re talking about reading N number of separate objects, hoping they’re not locked, joining all those objects together, etc.

Remember that you’re probably not joining any of these indexes together on their leading key column. Many of the join types are hash joins, or merge joins that require sorts. Both of those things will drive up the query memory grant.

In all, this query uses 10 nonclustered indexes, and does three separate key lookups back to the clustered index. You could really cut down on the amount of joining, sorting, and hashing, by adding some composite indexes that let our query get all its data from a single source.

Thanks for reading!

Brent says: this blog post stems from a query we saw in the wild leveraging something like 8 different indexes on the same table – even though the table was only specified twice in the FROM clause! I was so impressed by the sheer number of indexes that I said we should blog about it, except that it would probably take days of experimenting to make this happen with the Stack Overflow database. And of course Erik did it that day.

How to Troubleshoot Blocking and Deadlocking with Scripts and Tools

November 28, 2018

Deadlocks, Monitoring, sp_BlitzLock

When you need to find which queries are blocking other queries, your decision comes down to when the blocking happened. Is it happening now, recently, or coming up soon?

During a live emergency, start with sp_WhoIsActive. Adam Machanic’s excellent free sp_WhoIsActive replaces Activity Monitor, sp_who, and sp_who2, and it’s way more powerful. The documentation is extensive – check out how to find blocking leaders:

EXEC sp_WhoIsActive 
    @find_block_leaders = 1, 
    @sort_order = '[blocked_session_count] DESC'

EXEC sp_WhoIsActive

@find_block_leaders = 1,

@sort_order = '[blocked_session_count] DESC'

This is probably my favorite way to see blocking live, but it only works right now. You can log sp_WhoIsActive’s results to a table, but don’t be a hoarder – don’t set this up to just continuously log to disk if you’re not actively doing something with the data.

If it’s 1-2 hours after the live emergency, use sp_BlitzLock. Sometimes people call you up and want you to troubleshoot deadlocking that finished recently, but you didn’t have anything set up on the server ahead of time. If you’re on SQL Server 2012 or newer, download the First Responder Kit, install sp_BlitzLock, and run it:

It checks the default system health session and shows you recent deadlocks, the queries that caused them, and the indexes involved. Download sp_BlitzLock in our free First Responder Kit, and read more documentation here.

If you can prepare ahead of time, set up Extended Events. Jeremiah Peschka wrote an easy Extended Events session and a query for it. You don’t want to leave that running all the time, but if you know you’re heading into a time when blocking is traditionally a problem, fire it up and start keeping an eye on it. For blocking specifically, this is even better than logging sp_WhoIsActive to a table since it grabs blocking more frequently, but at a lower overhead. It’s missing a lot of the juicy details sp_WhoIsActive has, but it’s still effective at capturing lead blockers.

If you’ve got a monitoring tool, learn to use it well ahead of time. It’s not enough to just have it installed: just like a database, you need to understand how to leverage its power.

After you’ve had a monitoring tool installed for a few months, learn how to use it to troubleshoot blocking. Cause a blocking situation yourself by creating a table, then doing a BEGIN TRAN in a couple of different session windows and trying to update all of the rows. Let the blocking go on for a while, and see what kinds of emails and alerts you get from the tool. Then close the sessions, wait a while, and see if you can go back through the history to spot the lead blocker.

If you try that, and you still can’t spot the lead blocker with your monitoring tool, it’s time to hit the manual or call the vendor’s support line for help. They definitely want you to be able to use their tools – the more effective you are at using ’em, the more likely you’ll be to renew your support. Take advantage of the help before blocking strikes.

Getting Sneaky With Forced Parameterization

November 27, 2018

Execution Plans

Silly Rules

I’ve blogged about some of the silly rules about where Forced Parameterization doesn’t work.

One rule that really irked me is this one:

The TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT…INTO, or FOR XML clauses of a query.

TOP and FOR XML, get used, like, everywhere.

TOP is pretty obvious in its usage. FOR XML less so, but since it took Microsoft a lifetime to give us STRING_AGG, lot of people have needed to lean on it to generate a variety of concatenated results.

Heck, I use it all over the place to put things together for in the First Responder Kit.

Examples

In a database with Forced Parameterization enabled, these queries cannot be parameterized.

/*FOR XML*/
SELECT p.OwnerUserId,
       ids = (
                SELECT RTRIM(p2.Id) + ', '
                FROM dbo.Posts AS p2
                WHERE p2.OwnerUserId = p.OwnerUserId
                AND p2.CreationDate >= '20100101'
                FOR XML PATH (''), Type
                )
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 8672
GROUP BY p.OwnerUserId;

/*TOP*/
SELECT p.OwnerUserId,
       ids = (
                SELECT TOP 1 p2.Id
                FROM dbo.Posts AS p2
                WHERE p2.OwnerUserId = p.OwnerUserId
                AND p2.Score > 0
                ORDER BY p2.Score
                )
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 8672
GROUP BY p.OwnerUserId;

/*FOR XML*/

SELECT p.OwnerUserId,

ids = (

SELECT RTRIM(p2.Id) + ', '

FROM dbo.Posts AS p2

WHERE p2.OwnerUserId = p.OwnerUserId

AND p2.CreationDate >= '20100101'

FOR XML PATH (''), Type

)

FROM dbo.Posts AS p

WHERE p.OwnerUserId = 8672

GROUP BY p.OwnerUserId;

/*TOP*/

SELECT p.OwnerUserId,

ids = (

SELECT TOP 1 p2.Id

FROM dbo.Posts AS p2

WHERE p2.OwnerUserId = p.OwnerUserId

AND p2.Score > 0

ORDER BY p2.Score

)

FROM dbo.Posts AS p

WHERE p.OwnerUserId = 8672

GROUP BY p.OwnerUserId;

If we look at the query plans for them, we can see partial parameterization:

Halfsies

The literals passed into our query outside of the illegal constructs are parameterized, but the ones inside them aren’t.

Which means, of course, that we could still end up with the very plan cache pollution that we’re trying to avoid.

For shame.

The Adventures Of Irked Erik

I figured I’d try out some different ways to get around those rules, and it turns out that APPLY is just the trick we need.

For some reason, that’s not mentioned on that page.

I can’t find a newer version.

Maybe my internet is broken?

But anyway, if we change our queries to use APPLY instead, we get full parameterization:

SELECT TOP 1 
       p.OwnerUserId,
       ca.ids
FROM dbo.Posts AS p
CROSS APPLY (
                SELECT RTRIM(p2.Id) + ', '
                FROM dbo.Posts AS p2
                WHERE p2.OwnerUserId = p.OwnerUserId
                AND p2.CreationDate >= '20100101'
                FOR XML PATH (''), Type
) AS ca (ids)
WHERE p.OwnerUserId = 8672;

SELECT p.OwnerUserId,
       ids 
FROM dbo.Posts AS p
CROSS APPLY (
                SELECT TOP 1 p2.Id
                FROM dbo.Posts AS p2
                WHERE p2.OwnerUserId = p.OwnerUserId
                AND p2.Score > 0
                ORDER BY p2.Score
) AS ca (ids)
WHERE p.OwnerUserId = 8672
GROUP BY p.OwnerUserId, ca.ids;

SELECT TOP 1

p.OwnerUserId,

ca.ids

FROM dbo.Posts AS p

CROSS APPLY (

SELECT RTRIM(p2.Id) + ', '

FROM dbo.Posts AS p2

WHERE p2.OwnerUserId = p.OwnerUserId

AND p2.CreationDate >= '20100101'

FOR XML PATH (''), Type

) AS ca (ids)

WHERE p.OwnerUserId = 8672;

SELECT p.OwnerUserId,

ids

FROM dbo.Posts AS p

CROSS APPLY (

SELECT TOP 1 p2.Id

FROM dbo.Posts AS p2

WHERE p2.OwnerUserId = p.OwnerUserId

AND p2.Score > 0

ORDER BY p2.Score

) AS ca (ids)

WHERE p.OwnerUserId = 8672

GROUP BY p.OwnerUserId, ca.ids;

If we look at the new query plans, we can see that:

I see variables.

Bonkers

Partial disclaimer: I only tested this on SQL Server 2017 so far, and that’s probably where I’ll stop. Forced Parameterization is a fairly niche setting, and even if you have it turned on, you may not be able to change the queries.

It’s just something I thought was cute.

Thanks for reading!

Not So Forced Parameterization

November 26, 2018

Execution Plans

Brent Ozar Unlimited Team

Asking The Wrong Question

Sometimes, when you wanna turn on a feature, you spend so much time wondering if you should, you don’t bother asking if it’ll even work when you do.

There are a long list of things that are incompatible with Forced Parameterization, on a page that’s pretty hard to find.

Now, there’s nothing in that list that says what I’m about to show you won’t work, but it’s kind of inferred here.

Statements that reference variables, such as WHERE T.col2 >= @bb.

When you’ve finished rolling your eyes around the known universe, keep reading.

This Also Goes For Assigning Variables

So, if you’re the kind of nutso-wacko that wants their variables to have values, hold onto your Librium!

Let’s check it out.

USE StackOverflow2010;

ALTER DATABASE StackOverflow2010 SET PARAMETERIZATION FORCED;

USE StackOverflow2010;

ALTER DATABASE StackOverflow2010 SET PARAMETERIZATION FORCED;

This sets FP on.

Now I’m gonna run two queries. One of them selects a count, and the other assigns that count to a variable.

SELECT COUNT_BIG(*) AS records
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
JOIN   dbo.Badges AS b
    ON u.Id = b.UserId
WHERE  1 = 1
AND    u.Reputation > 1
AND    p.Score > 0
AND    c.Score > 0
AND    u.Id = 8672;

DECLARE @count BIGINT;
SELECT @count = COUNT_BIG(*)
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
JOIN   dbo.Badges AS b
    ON u.Id = b.UserId
WHERE  1 = 1
AND    u.Reputation > 1
AND    p.Score > 0
AND    c.Score > 0
AND    u.Id = 8672;

SELECT COUNT_BIG(*) AS records

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

JOIN dbo.Badges AS b

ON u.Id = b.UserId

WHERE 1 = 1

AND u.Reputation > 1

AND p.Score > 0

AND c.Score > 0

AND u.Id = 8672;

DECLARE @count BIGINT;

SELECT @count = COUNT_BIG(*)

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

JOIN dbo.Badges AS b

ON u.Id = b.UserId

WHERE 1 = 1

AND u.Reputation > 1

AND p.Score > 0

AND c.Score > 0

AND u.Id = 8672;

The first query is parameterized just fine.

See those little parameters? They used to be literals.

How nice for you.

The second query doesn’t fare so well.

No. Not at all. One may even call it unfair.

Ribbit.

Workarounds?

If you have queries that do this, and you want them to benefit from parameterization, one workaround is to insert the value you would assign your variable to into a temp table, like this.

This query will get parameterized.

SELECT COUNT_BIG(*) AS records
INTO #fp_me_plz
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
JOIN   dbo.Badges AS b
    ON u.Id = b.UserId
WHERE  1 = 1
AND    u.Reputation > 1
AND    p.Score > 0
AND    c.Score > 0
AND    u.Id = 8672;

SELECT COUNT_BIG(*) AS records

INTO #fp_me_plz

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

JOIN dbo.Badges AS b

ON u.Id = b.UserId

WHERE 1 = 1

AND u.Reputation > 1

AND p.Score > 0

AND c.Score > 0

AND u.Id = 8672;

Nifty.

Then you can just hit that temp table for the value.

Not bad.

Not great if you do it a lot, but hey.

Thanks for reading!

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

November 25, 2018

Videos

This week, Brent, Tara, and Erik discuss update statistics, how soon it is safe to use a new version of SQL Server, VMs vs physical servers, backups, disabling TCP Chimney Offset in VMware, SQL Server on Linux, performance troubleshooting, most valuable tools to have in your SQL Server DBA toolkit, nolock and read uncommitted, and 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.

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-11-21

Does UPDATE STATISTICS have a cost?

Erik Darling: [Chitan] asks, “Is update statistics a free tool, or does it have performance impact? How bad is it to run update stats every hour in an OLTP environment?

Tara Kizer: If you think you need to run it hourly, you probably have a parameter sniffing issue. At my last job, I inherited a server that had update stats running every 30 minutes. And I was only there about 18 months before I came here and I never wanted to rock the boat. I mean, it was working fine. I knew that it wasn’t necessary. I normally set up my servers – the ones that I am in control of – I set them up to do, once a day, update stats. And then maybe twice a day if I have a different type of load happening. But usually, I’ll run update stats after the nightly stuff runs, but it was working on that server, so I didn’t want to touch it and the people that set it up were still there and I just didn’t want to rock the boat. But it’s probably completely unnecessary to do it every 30 minutes, every hour. I mean, maybe at the most twice a day. I don’t know, it just sounds like you’re probably dealing with a parameter sniffing issue and you should be troubleshooting that instead of update statistics.

Brent Ozar: The overhead is going to be, of course, it will scan objects, you know, do lots and lots of reads. It’s not doing that many writes, but it’s doing a lot of reads and it will slow queries down while it’s happening.

Erik Darling: The one thing I can think of, if you’re on older versions of SQL Server like pre-2014, sometimes you would hit this nasty thing with ascending keys where if you had fairly large tables and you had, like, let’s just call them identity columns – it could be any column, as long as the value keeps increasing – and you had data coming in at the end and you didn’t have stats updated enough, SQL Server makes some really bad – I want to say like a one row guess for any off-histogram value. And you could have a bunch of stuff in there and SQL Server is like, nope, just the one row, please. You’re like, no, that’s wrong, please don’t guess that.

Brent Ozar: If you want to learn more about that, search for Thomas Kejser; the ascending key problem. And it’s really interesting. Most of us don’t have this problem, but it’s really interesting to read about. It’s like car crashes, you know…

Erik Darling: Erin Stellato has a pretty good post about ascending keys and a couple of the trace flags you can use along with them over on SQL Performance.

Brent Ozar: Okay, cool.

Erik Darling: So good stuff over there too.

When is a new SQL Server version ready?

Erik Darling: So, Steve, I wish there were some way that I could physically touch you, Steve. Steve asks a two-part question. I’m going to read both parts and then I’m going to let them sink in, “With SQL Server coming out fast and furious, how soon or late after a new version comes out do you bring it into your new environment? Also, physical servers or VMs?”

Tara Kizer: Completely different questions.

Brent Ozar: Wow.

Erik Darling: By the by… So let’s start with that first one. How soon after a new version on SQL Server comes out do you think it’s safe to start using it?

Brent Ozar: I’d go like two or three cumulative updates. I’d probably feel comfortable with doing it, but I would only go if it fixes a problem that you have. If there’s a problem you identify and go, 2019’s inlining of functions, got to have that. It’s my biggest performance bottleneck, then I want to jump. But if you don’t see something in the release notes where you go, this solves one of my biggest pain problems, I don’t know that I would jump that fast. I would probably – man, what scares me is you look at – scares is the wrong word. 2017’s cumulative updates, you look at the ones that even come out now in 2018, 2017’s been out for a while, even a dozen cumulative updates in there’s some terrifying stuff inside of there. And it’s not that older versions don’t have problems too, but I want to only go when I know I’m fixing stuff.

Erik Darling: And to be fair, even service packs today for older versions, there’s some pretty terrifying stuff in them too. It’s not like old versions are problem free. Like, these are settled. I had a boss once who was petrified of getting off 2008 R2 RTM and I was like, why? What’s going to be the problem…

Tara Kizer: What? Oh my god…

Erik Darling: He’s like, you know why? Because I know what I’m getting with it. No, you don’t.

Tara Kizer: It’s more risk to stay on it…

Erik Darling: Getting me to work weekends when crap goes wrong, that’s what you know you’re getting.

Brent Ozar: There was a good Twitter thread with Pedro Lopes of Microsoft. Someone was asking – they were like, I don’t really understand why I would upgrade this often and Pedro banged out like four things that are, like, performance, supportability, compliance, and I forget what the fourth one was…

Erik Darling: So does he… So how about that second part, physical versus VMs; what do you opt for? What’s your first choice?

Brent Ozar: Man, I believe that every SQL Server should start as a VM; like a four-core 6GB of RAM VM. And then only add more when you believe you have a performance problem that you can’t fix by changing the queries or indexes. But we’re in kind of a weird business. We’re in this performance tuning business where people come in when they have serious performance problems. Nobody comes in when they’re happy and they’re like, things are great here, we just like hanging out with y’all. So there are a lot of times when we’ll see people on giant metal, 40, 60, 80-core servers. We’re dealing with a couple of 80-core VMs in Amazon the other day.

Erik Darling: The X1E, yeah, 128 cores, 2TB of RAM…

Brent Ozar: And you hit that size and you’re like, why are we not on bare metal, because there’s a couple of advantages you get with bare metal. You get different NUMA handling if the guest and host map up beautifully. You also get the ability to run local solid state for tempdb, which you can do in the cloud with stuff like an X1E. But when you’re running on-premises and VMware and Hyper-V, sysadmins are usually really hesitant to let you do local solid state for tempdb. So people are like, we can put a million dollars in the shared storage and make it perform really fast. Well yeah, I can strap a jet engine to a Honda Civic, but that doesn’t mean it’s a smart investment.

Erik Darling: And you know, for me, it all depends on where that server is coming from. If I have an existing server that’s already a big beast and I need to put it on something else, I don’t want a virtualized big server because you run into a thing where you have this one guest that dominates the host. You can’t have other servers on there with it. You can’t move it to other servers because they have a bunch of other stuff on there. Part of the beauty of VMware is the manageability stuff where you can put different VMs on different hosts, balance things out. As soon as you have that one big boy hanging around, you kind of lose a lot of that. You get that one to one guest host mapping. No one says, that’s my virtualization dream. Plus I hate VMs.

Brent Ozar: It only gets worse in the cloud.

Erik Darling: Alright, so Greg says – and clearly, they have reached a rocket science-y point of tuning this VM – he’s been recommended to disable TCP chimney offset in VMware to improve SQL Server performance.

Tara Kizer: Goodness…

Erik Darling: By god, you’ve done everything else haven’t you?

Brent Ozar: Somebody read a book from 2006. [crosstalk 0:08:33.5] 2006 rings a bell. We did that back in 2006-2006. There was a whole deal with TCP chimney offsets. I would guess that somebody just has a really old best practices. I would be like, if you want to do that, that’s great. Let’s just measure before and after, make sure it makes a difference. And when it doesn’t make any difference, you’ll identify they don’t have pants on.

Erik Darling: Yeah, I’d also press him and say, what metric do you expect to see improve when you make this setting? What’s going to get better? What’s going to get faster? It’s like that disk offset thing in 2003. It’s like, you’re off by 1MB, you bastard, you’ll never work in this industry again…

Who should do the SQL Server backups?

Erik Darling: So Thomas asks, “Have you ever heard of anyone not doing backups in an ERP system and letting another program like ArcServer take the backups?” He says, it sounds like they’re taking a backup of the computer and they’re not able to run CHECKDB on it. So he’s concerned about the recoverability of those backups, the integrity of his data, and wants to know if that’s a good way of doing things.

Tara Kizer: I’m just so confused.

Brent Ozar: I’ve seen people do flat file backups, like Symantec had a backup exec or something a while back that would purport to backup open files; involved VSS snapshots and all that. But the easy way for me is to just ask for a [inaudible]; I want to restore. I want to restore as of this time. You know, whatever, yesterday as of 5 pm, and you just fake it. You go, I dropped a table yesterday at 5pm, can you go ahead and restore the database for me to another server? I’m going to pull the contents across…

Erik Darling: [crosstalk 0:07:51.1] Just say you changed the stored procedure. Don’t say you lost it.

Tara Kizer: That would get escalated quick.

Erik Darling: This guy, Thomas…

Have you used SQL Server on Linux?

Erik Darling: Alright, so Mike asks, “Have you used SQL Server on Linux much? We’re going to start using it soon because our vendor uses Linux for some other apps.” It seems pretty decent to him, but he wants to know if we have any thoughts on it.

Tara Kizer: Have you guys installed it? I don’t think any of us have.

Erik Darling: Ask me if I can install Linux…

Tara Kizer: I know…

Brent Ozar: The release notes were enough that it scared the pants off – I’ve said pants like two questions in a row – scared the pants off of me is what’s not supported…

Tara Kizer: Everything…

Brent Ozar: Holy cow, there’s a lot of stuff that’s not supported. Yeah, I’m going to have to go back down. Replication wasn’t supported…

Erik Darling: Well thank god. That’s a feature, buddy.

Tara Kizer: [crosstalk 0:09:58.0] do an insert, update, delete, and select, you’re good to go. If you need any feature, you’re not getting it.

Brent Ozar: I’m going to have a tough time finding it now on demand, but I would just go through and make sure it actually supports the features that you want. I tried it on Docker containers and there were problems around parallelism, for example. Queries could only use one core. I was like, okay, I’m about enough here. That sounds cool, but I’ll let somebody else dip in.

Erik Darling: That’s a good time to bail out.

Brent Ozar: Yeah, it doesn’t solve a problem I need to fix.-

In which Richie trolls Erik

Erik Darling: Let’s see here. Another Erik – I can’t figure out if this is someone’s last name…

Tara Kizer: I think they just love you…

Erik Darling: Alright, Erik Lover, we’ll see what happens.

Tara Kizer: It’s got the K, I think he loves you.

Erik Darling: Yeah, because a sign of true love is that someone spelled my name right. I know that because my mom doesn’t spell my name right. But Erik Lover has an Availability Group they set up using 2014 and Windows Server 2012. There are three nodes and it’s running as a managed service account on each node. “Can I have Erik’s old desk…” You got me good there. Is that Richie?

Will clearing the plan cache fix my problem?

“We’re experiencing performance issues during a mass run at midnight?” Well, that’s the problem; no one’s there watching the server. “CPU peaks around 98%. The plans in the cache around 40,000. Single plans in the cache is around 18,000. Will clearing the plan cache solve my performance problem?”

Brent Ozar: If queries are already running and they’re using 100% CPU, you’re kind of screwed there. If there were new queries coming in and you had a bad plan, like the queries kept coming in, then you could conceivably clear the plan cache and get new plans coming in. But I wouldn’t do it by clearing the plan cache. I would go look at – if you search for parameter sniffing. Actually, just go to brentozar.com/go/sniff. This would be the first thing that I would suspect it would be. And on that link, we have a video that I did at a SQL Day in Poland explaining how to identify parameter sniffing and why freeing the plan cache works temporarily, but just not long-term.

Erik Darling: You know what – I’d say if you have that many plans coming into the cache, you might want to look into a setting called forced parameterization, because it will make parameter sniffing worse, but it will reduce the number of plans in the cache, so you’ll have fewer of those to look at, which is nice too. There’s all sorts of upsides to that. Because it’s a job security thing, because now you’ll have a parameter sniffing problem to fix, and you’ll have solved the problem of all the plans in the cache so someone will think you’re really, really smart. And you’re smart enough to fix parameter sniffing, so it’s a two-part approach to getting a good raise, I think.

Brent Ozar: So we also have a page on there, so if you search for why multiple plans for one query are bad, it talks a little bit more about how you get into forced parameterization.

Erik Darling: She also adds that page life expectancy is up and down, which yes, it tends to do that. It tends to do a little up-jump-boogie when memory gets different levels of usage.

Brent Ozar: Even when the server is fine it does that. it’s not necessarily an indicator of problems.

A Microsoft presenter recommended sp_BlitzIndex

Erik Darling: Someone sent a bunch of Os, I don’t know what that means. Here’s one from Chris Good, or Chris Bad, maybe, I don’t know, Chris Somewhere In The Middle, “I took a performance tuning course from Microsoft last week…” That’s not good, “The presenter suggested we look into sp_BlitzIndex…” Holy cow…

Tara Kizer: Was it Connor? I think it was Connor.

Erik Darling: It had to have been Connor.

Brent Ozar: Wow, that’s kind of cool.

Erik Darling: Yeah, I want to hang out with that instructor. They know their goods.

Tara Kizer: Yeah, send them some cookies.

Erik Darling: Yeah, right.

Brent Ozar: I really would love to know who that presenter is. That’s amazing. I remember the first time – we used to capture emails every time someone downloaded the First Responder Kit and when I started seeing Microsoft PSS people, like customer support people downloading, I was like, yes, we’ve made it; we’ve made it.

Tara Kizer: That’s awesome.

When does RCSI not get used?

Erik Darling: Alright, let’s see here – sort of a crazy question, “What are the cases when RCSI does not get mapped using SQL Server 2017 Enterprise Edition? Is there any doc or whitepaper?” I’m not sure what you mean by mapped.

Tara Kizer: Well because, you know, my client last week, I think it was – all the days are running together now, but I think it was last week’s client – they had RCSI and we still saw blocking and the select query that was causing the blocking was read committed. It hit none of the limitations. It had none of the isolation level changes, table hints. I mean, none of us could figure out what was going on there. So I don’t know if there’s ever a time where the mapping – you know, map it from read committed over to RCSI – we could find nothing that was escalating above read committed to cause what we were seeing.

Brent Ozar: And there are lists of things that will stop at select. If you put index hints or lock hints in your code, life if you say with serializable, you say with NOLOCK.

Tara Kizer: And I think that client was on SQL 2017 Enterprise Edition. I’m pretty sure that was the client. I know I had a recent 2017 client. So I wonder if there’s a change in 2017, you know?

Brent Ozar: Yeah, I would open a support case just because there was the 2016 service pack – I want to say it was SP1 CU4 – that broke NOLOCK. It suddenly started taking out locks, so it is possible. It’s happened before.

What are the downsides of indirect checkpoints?

Erik Darling: Alright, so let’s see, Rakesh asks, “What are the downsides of indirect checkpoint? We migrated to SQL Server 2016 and the new databases are using it but the migrated databases are using automatic checkpoints.”

Tara Kizer: What the heck are indirect checkpoints? When you’re actually issuing a checkpoint command?

Brent Ozar No, that was – I remember hearing about this from like Conor or something.

Erik Darling: Yeah, I don’t remember anything about it because it was one of those things, like yeah, okay, when will that come in handy?

Brent Ozar: I would honestly just Google. We don’t touch it either. We haven’t heard anything good, we haven’t heard anything bad. It’s just one of those. We all make the same gas-face as we’re reading that…

Tara Kizer: Like why would you ever change it?

We’re thinking about enabling Query Store…

Erik Darling: Well in the meantime, Zoe-Ann asks, “We have over 20 databases…” Holy cow, someone went on a spree, “We’re on SQL Server 2017 and we want to turn on Query Store. We understand that it should generate about 5% overhead. Are there any suggestions you have related to Query Store settings or any overhead that you’ve seen?”

Tara Kizer: I’ll tell you a little story about a recent client [crosstalk 0:16:59.2]. It might even be the same one, I don’t know. It was 2017 and they enabled it and the retention that they set up in the Query Store was one year and they had a failover occur through Availability Groups due to patching or whatever it was. And they were down for several hours. And it had to do with Query Store, because they opened up a case with Microsoft and they needed to put a trace flag in place. In the end, the whole issue was because of the many plans for one query. They had ad hoc queries; thousands of plans per query. And so they have, I don’t know, 100,000 plans hourly and they’re distinct plans, so all of that’s getting dumped into Query Store. So Query Store, I mean, tons of overhead because of their environment, so make sure you don’t have this many plans for one query issue, and primarily ad hoc plans.

Erik Darling: So for me, there’s a setting in Query Store, which I actually just wrote a blog post about that hasn’t gotten pushed out yet. But the setting is in the plan collection portion. And it lets you choose – there’s a setting that says collection something, and you can choose all or you can choose another setting called auto.

Tara Kizer: Auto, that’s right.

Erik Darling: And when you choose the auto setting – auto, not otto – when you choose the auto setting, what happens is SQL Server doesn’t go and collect every single plan that comes in. It only collects ones that meet some certain threshold, like capture mode. So if you change that to auto, then you’ll only get plans that use a certain amount of compile time, a certain amount of CPU, have a certain amount of executions. Before that, it just doesn’t’ stick any old thing in there. Whenever you turn on Query Store – I was experimenting at trying to write sp_BlitzQueryStore – I’d always find these ridiculous Microsoft queries in there. I’m like, why are you showing me this? This is not anything that I would ever want to look at in Query Store. Show me my bad [crosstalk 0:18:55.8]. Pretty crazy.

Brent Ozar: Yeah, so Erin Stellato also has a post – she just happened to publish her most recent one – about the overhead of Query Store. The other thing that I would say too is make sure you’re on the most recent service pack and cumulative update because man, this is one of those features that has had a lot of patches pushed to it.

What are the most valuable SQL Server tools?

Erik Darling: Alright, we have a question from Brandon who is a brand new DBA. He’s eight months into his gig and he wants to know what tools you think are the most valuable to have in your SQL toolkit for working with SQL Server and doing…

Tara Kizer: Has he heard of us before?

Erik Darling: Interesting question.

Tara Kizer: We have tools.

Brent Ozar: Ah yes so we needed those tools desperately. Every not and then, we talk about [inaudible 0:19:50.5] suck if we didn’t have them. If you click on scripts up at the top of our site, we have a whole barrage of those things. You can download them all in one chunk. If you made us each pick one, I think my favorite one would probably be sp_BlitzCache, just to rapidly point out what the problems are. That would probably be my favorite if you only let me live with one for the rest of my life. What about you, Tara? Which one would be your favorite?

Tara Kizer: Well, does it have to be one of our scripts?

Brent Ozar: No…

Tara Kizer: I think sp_whoisactive, because I can do a lot with that.

Brent Ozar: And so that’s over at whoisactive.com. And this is one of those things where there’s a download, you go install it and you run it and you think you know everything. Oh no, no, no. Go into the documentation and it’s insane. It does so much good stuff.

Tara Kizer: If it has to be one of ours it’d be, I think, BlitzFirst because normally, as a production DBA, I am troubleshooting an active performance issue and so BlitzFirst, you know, can really lend a hand to figure out what’s going on.

Brent Ozar: Erik, how about you?

Erik Darling: For me, you know, I’m just going to give a shout-out to a different group of people; dbatools. I don’t work with PowerShell. I thank god every day that I don’t have to work with PowerShell because they’ve written things that do things in PowerShell. You can do – we’ve talked about stuff that helps you troubleshoot performance and query plans and indexes and server liability. This is like bread and butter DBA tasks; moving databases, moving users, setting stuff up, stuff that I don’t ever never in my entire rest of my life want to do again. So thank goodness these people, A, work with a language that I just don’t get along with – I am incongruous to this language – and they have written tools that I don’t ever want to have to remember syntax for, look up, think about. So I think they’re a pretty cool bunch even though me and PowerShell have our disagreements.

Brent Ozar: Somebody said the other week that they were like, hey it’s been a while since Erik’s done a live blog of trying to use PowerShell. We kind of miss that. We should do that again.

Erik Darling: No one said that, did they?

Brent Ozar: They did, they did. It’s funny.

What’s better: NOLOCK or READ UNCOMMITTED?

Erik Darling: Alright, [Chetan 0:21:57.5] asks another question, “It might be a bit of a beginner question, but which one is better for performance; NOLOCK or set transaction isolation level read uncommitted?” Take it away, Tara.

Tara Kizer: I was going to reply just neither, you know, end of story. It is not the turbo button. They’re identical, you know. One of them is just put it at the top of your script and the whole thing is now read uncommitted, the other one’s at each table level. They’re identical. NOLOCK is read uncommitted just at the table level. Don’t use it. I mean, if you think that this is a performance improvement, you know, you need to figure out what the root cause of the blocking or whatever contention issue you’re having and fix that. Read uncommitted and NOLOCK is not safe for production systems where data accuracy matters. I had a client a few weeks ago that – the day three call where we deliver the findings – there were some people in management in the room – this is actually during day one – and everything said NOLOCK. And so I asked the question, does data accuracy matter on this system? And the management people are like, hell yeah it matters. And I was like, well you can’t do this then. This can give you completely erroneous data. It is not the turbo boost. Use RCSI instead. It’s safer. If you have to go the isolation level route…

Erik Darling: The thing about NOLOCK or read uncommitted, no matter which one you choose, is everyone thinks that it means that your query isn’t going to take locks out. What it really means is that your query isn’t going to respect locks taken by other queries that are trying to modify data. And you can read all sorts of just awful nasty stuff that Brent’s going to show you with this lovely typing demo.

Tara Kizer: Is he going to be able to do it fast enough?

Brent Ozar: I think so. So over on the left-hand side, I’m going to run an update. Over on the right-hand side, I’m going to say select count star from DBO.users with NOLOCK. And so I’m going to, on the right-hand side, run a count at the same time on the left-hand side I am updating the table. Notice that I am not, over on the left-hand side, changing the numbers of rows in the table. It’s the same number of rows in the table; I’m just changing the contents of one of the columns. So every time I run a select count star, you would think I would get exactly the same rows. So here’s a quick rundown. I’m going to make sure I don’t have any indexes too because I’ve been playing in this database all day. So count star from DBO.users – so there’s about two and a half million rows in here. If I run it 10 times…

Erik Darling: Not 24 million…

Brent Ozar: Not 24, just two and a half; small number. Now over on the left-hand side, I’m going to do the update. At the same time on the right-hand side, I’m going to go run the select star and my numbers are all over the place. That’s kind of messed up because I’m not even, in this case, going to commit. I’m going to stop while the update’s running, and yet still the count star just sees different numbers of rows. So that’s pretty hairy.

Erik Darling: Crazy, yeah, don’t like that one bit.

Tara Kizer: Fix your queries, add indexes, you know, fix your table design, maybe use RCSI if your system can support it. In my opinion, NOLOCK read uncommitted should not be used except as a DBA or developer querying production data in Management Studio. Then I use NOLOCK because I don’t really care about the results.

Erik Darling: Only use NOLOCK in incognito mode, or else. People will see and you’ll get really embarrassed.

Brent Ozar: When you really don’t care about the quality of data – like if you’re running Twitter or Facebook and you’re just showing people cat pictures, then sure, but how often do those people put that in SQL Server? You don’t put stuff like that in SQL Server. Alright, well thanks, everybody for hanging out with us this week at Office Hours and we will see y’all next week; adios.

Erik Darling: Goodbye.

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

Congratulations to the Scholarship Class of 2019.

November 22, 2018

Company News

To celebrate the holiday season each year, we give back to those who spend the entire year giving back in their own communities. We go looking for data professionals who make a difference, and we try to make a difference in their lives too.

This year, we’re proud to announce a new round of winners. We can’t mention ’em all, but here are just a handful of groups that we’re proud to help:

The scholarship program makes the heart part happy.

The American Institute of Physics is committed to the preservation of physics for future generations, the success of physics students both in the classroom and professionally, and the promotion of a more scientifically literate society.

South Community, Inc. provides Ohioans with mental health care, substance counseling, psychiatric support, and much more.

UNOPS helps people build better lives and countries achieve peace and sustainable development. Yes, they’re using data, like how they’re considering artificial intelligence.

The Africa Health Research Institute works with over 60 academic and clinical institutions for the elimination of HIB and TB.

The Smith Family is an Australian charity helping disadvantaged children get the most out of their education so they can create better futures for themselves.

Easter Seals-Goodwill Northern Rocky Mountain serves children and adults with autism and other disabilities, plus disadvantaged families in Idaho, Montana, Utah, and Wyoming.

This season, take a few moments to think about how lucky you are, and how you can reach out to those around you to help them move forward, too.

Happy holidays.

A Lot of Your SQL Servers Are Unsupported.

November 21, 2018

Production Database Administration, SQL ConstantCare

I’m almost afraid to look into the SQL ConstantCare® client base to see the answer to this, but let’s put on a pair of rubber gloves and go find out.

SQL Server 2016: <1% out of support. I LOVE YOU PEOPLE. However, you’ve had the luxury of time here: SP1 came out in November 2016, so 99% of you have had the time and motivation to install it. SP2 is already out, but both SP1 and SP2 are supported right now. SP1 drops out of support in July 2019. SP2 came out in April of this year, and 64% of you are already on it!

SQL Server 2014: 10% out of support. Both SP2 and SP3 are supported right now, and SP2 isn’t out of support until a luxuriously long-from-now January 2020. I’m not gonna lie, though: a couple dozen of you didn’t even bother to patch the original install of 2014. Get on the ball.

SQL Server 2012: 38% out of support. Service Pack 3 just dropped out of support last month, so I’m not too surprised that a lot of folks have been caught off guard. SP4 came out just over a year ago.

SQL Server 2008 R2: 27% out of support. Service Pack 3 came out four years ago, and it’s the only remaining supported SP. Thankfully, most of you are on it. However, 14 of you are running a completely unpatched SQL Server 2008 RTM. We’re talking about an eight-year-old set of bits. They’re rotted. Throw them out.

SQL Server 2008: 65% out of support. The only remaining supported Service Pack for SQL Server 2008 is Service Pack 4, and 2/3 of y’all aren’t on it. C’mon, now, SP4 came out over four years ago – get ‘er patched.

When I talk to folks about why they don’t patch more often, I usually hear:

We can’t get a maintenance window
The vendor won’t certify their app on the new patch
It works well enough, no reason to risk it
I don’t open support calls anyway, but if we had to, we’d patch it then to get support

What about you? Why aren’t you patching your SQL Servers?

Going to SQLBits in 2019? Come to my pre-con!

November 20, 2018

SQLBits

Head over here to register now.

My Favorite Conference

I love SQLBits, and not just because it gets me out of the country for a week. It’s about the best conference a training buck can buy. There’s tons of great technical material without a lot of the marketing schtick that tends to sneak in.

I’m Putting Together A Team

There are a lot of great pre-cons to choose from, but here’s why you should come to mine:

For years, SQL Server has been stealing from you. Your nights, weekends, vacations you took a laptop on, and who knows how much hardware and licensing money went into it instead of into a raise or bonus for you.

I’m gonna teach you how to turn the tables on SQL Server, and start taking it all back.

You’re going to learn how to find your server’s bottlenecks, the queries that cause them, and how to fix them. We’ll be making sense of wait stats and query plans, and getting deep into not just how a single query interacts with SQL Server, but how queries interact with each other.

Concurrency isn’t just about locking and blocking; it’s about how queries get scheduled on CPUs, share memory, and move data to and from disk.

Think it’s the hardware?

I get it. You’ve been staring at monitoring tools, widgets, and charts and graphs for years, and making heads or tails out of them is just plain aggravating. There seems to be conflicting information everywhere, and no good way to get to the root of which queries were slow, and why.

You’ll head back to work with all the knowledge and tools you need to ~~get a much better job~~ find and fix your server’s toughest problems, and finally start getting back what’s yours.

Away Days

I’ll be writing more about what to expect, and what special goodies we’ll be giving to attendees leading up to the event.

Hope to see you there!

Thanks for reading!

Psst – it’s holiday card time. Update your address.

November 19, 2018

SQL Server

Last year’s Christmas card

We’re getting ready to send out our annual holiday cards to folks who shopped with us this year. If you’d like to get one, make sure you set up your billing address in your account to reflect your current address.

Happy holidays!

How Much Memory is “Normal” for SQL Servers?

November 19, 2018

Brent Ozar Unlimited Team

SQL ConstantCare

When I look at a SQL Server, one of the first things I check is, “How much memory does this thing have relative to the amount of data we’re hosting on here?” I’ve long used some seat-of-the-pants numbers, but armed with data from SQL ConstantCare® users who opted into public data sharing, let’s do a little deeper analysis.

I took last Thursday’s data for about 1,400 servers, then excluded stacked instances (multiple instances installed on the same OS), Azure SQL DB, Managed Instances, and servers with less than 10GB of data.

How much memory do SQL Servers have?

The median SQL Server has 19% of the data size as RAM. Meaning, if it’s hosting 100GB of data, it has 19GB RAM in the server. (Max memory size is a discussion for another blog post.)

To take a few examples from that median:

84GB data hosted on a SQL Server with 16GB RAM
166GB data hosted with 32GB RAM
289GB data hosted with 55GB RAM

Let’s slice it a different way, though: let’s take the median data volume out of this sample, 219GB. (If I sort servers by how much data they host, the middle value is 219GB.) In servers in that range, a few include:

219GB data hosted on a SQL Server with 15GB RAM (OS has 7% of the data size)
222GB data hosted with 128GB RAM (58%)
222GB data hosted with 24GB RAM (11%)

These numbers are thought-provoking, but before you ask questions, think about this one: the data size vs hardware size doesn’t by itself mean that the users are happy. There are a lot of other factors at play, like query volume, tuning done, wait stats, etc.

A few examples from the extreme ends of memory-vs-data-size provisioning:

4.2TB of data hosted on 16GB RAM (HAHAHA)
20TB hosted on 64GB
10GB (not TB) hosted on 64GB RAM
61GB hosted on 256GB RAM

As data size grows, memory doesn’t.

I split the servers into quartiles based on the size of data they’re hosting:

Servers hosting 10-59GB data: median RAM size is 74% of the data! (example: 27GB data, 20GB RAM)
60-224GB data: 23% RAM size (example: 210GB data, 48GB RAM)
225-600GB data: 13% RAM size (example: 488GB data, 64GB RAM)
>600GB data: 6% RAM size (example: 2.1TB data, 128GB RAM)

The low end percentages are a little skewed since in the 10-59GB tier, the OS memory means a lot. In our SQL Server Setup Guide, we tell folks to leave at least 4GB to the OS, and I think most sysadmins would consider 2GB to be the bare minimum. But still, the dropping percentages as data grows – that’s pretty steep.

Do Enterprise Edition servers have more memory?

Overall, Enterprise Edition servers handle larger volumes of data, and they are configured with more memory to handle it:

Standard Edition median data size is 168GB, median RAM size is 32GB
Enterprise Edition: median data size is 358GB, median RAM size is 54GB
Developer Edition: data 111GB, RAM 16GB (poor developers)

So in terms of pure server size, yes, Enterprise servers are larger, but as a percentage of data, something kinda interesting happens:

Standard Edition median: RAM is 23% the size of the data
Enterprise Edition: RAM is 17% of the size of the data
Developer Edition: 11% (c’mon, man, let them get some memory!)

Do older SQL Servers have less memory?

Y’all are starving the dinosaurs:

SQL Server 2008 and 2008R2 median RAM is 15% of the database size
SQL Server 2012: 18%
SQL Server 2014: 19%
SQL Server 2016: 22%
SQL Server 2017: 24%

Which might be due to a few factors, like not caring about the performance on older servers, or dealing with old servers built with much lower memory sizes.

Where to go from here

My next thoughts are:

Which servers are more likely to experience PAGEIOLATCH waits?
Which servers are more likely to experience RESOURCE_SEMAPHORE poison waits when they run out of query workspace memory?
Can I build a sizing tool that predicts user happiness based on data size? (Meaning, if you put 3TB of data on a 64GB RAM VM, how bad will the wait stats be?)
Then, can we give that same advice to customers? For example, showing them a chart of where they rank for data size vs memory vs happiness?

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

November 17, 2018

Videos

Here’s the video on YouTube:

This week, Tara, Erik, and Richie discuss using Docker in dev environment, in-place upgrades, disaster recovery, the minimum amount of CPU and RAM for new SQL Server VMs, troubleshooting long-running SQL agent jobs, dbcc checkdb running longer than normal, and more.

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-11-14

Have you used Docker for SQL Server yet?

Erik Darling: “Have you used Docker in SQL Server at all? What do you think about using it for dev instances? Short answer is absolutely no, I have not touched it.

Tara Kizer: Can barely spell it. I know they sell it at like Coles and all those kinds of places.

Erik Darling: Yep, you can get pleated ones, boot-cut ones, all sorts of things.

Richie Rump: Brent’s probably wearing some right now…

Tara Kizer: He might be, with a polo shirt… (Edit from Brent: worse: a buttondown.)

Erik Darling: With a nice weaved belt, something like that. [crosstalk]

Richie Rump: Oddly enough, pretty frequently, we don’t really do a ton with Docker or anything like that. The closest we get is Lambda, which is automatic. It does all that stuff for us. My opinion is, I don’t know what it buys you in production unless you’re really turning out a lot of servers all the time for different clients or whatnot. Maybe that might make sense in your environment. But for dev, maybe, but remember, just talking about the install, now you’ve got the data on top of that, which will change probably a lot more frequently than how frequently you create a dev server. It’s just easy. It’s more overhead, a little bit, so try it out, see if it works for you guys.

Erik Darling: It would almost make sense for us where we use a pretty static copy of Stack Overflow over and over again, but then I’m creating a new SQL Server and have to remember the settings and, like, the desired state with SQL Server is a crap-show. Nothing about it seems appealing to me.

When will the Summit recordings be available?

Erik Darling: Anyway, next question. There’s one from Colin, “Do you happen to know when the PASS Summit recorded sessions will be posted?” I’m not wearing a PASS shirt. I do not know. Please ask someone from PASS. They don’t tell us, mere mortals, these things.

Will in-place upgrades become more common?

Erik Darling: Joshua asks, “Do you foresee in-place upgrades becoming more common and acceptable for production systems given the shortened release cadence?” Tara, take it away…

Tara Kizer: I mean, absolutely not, for me. In-place upgrades are for a test environment. For production, I mean, I’ve supported some very critical systems and I will not take that risk of doing an in-place upgrade. I mean, it is not happening for critical production systems.

Erik Darling: Not for me either. You know, especially because with each new version now, they are making bigger and bigger changes to more and more critical parts of the system. Like, if you look at the stuff that came down the pipe for 2016 and then for 2017 with adapted joins and the more batch mode stuff. And now 2019, where you’ve got batch mode for row store and you’re getting all the stuff with UDF inline. If anything goes terribly horribly wrong, there is no back button that says, no I want it the way it was before. You are stuck if you just do an in-place upgrade. And if you are stuck with unhappy users and customers, you are most likely stuck on Monster.com for a while trying to figure out what happened.

Tara Kizer: Just think about your maintenance window that you would need for a rollback if something goes wrong there. I mean, I don’t have that kind of downtime in maintenance windows. I’m usually, you know, very small maintenance windows. In-place upgrade, not happening because of rollback reasons.

Erik Darling: No, everyone says, oh it’ll be so easy. We have a VM. We’ll just flip it back. Okay, fine, if anyone touched any data right leading up to that, good luck to you. No thanks. Not the kind of risk I’m willing to take with production SQL Servers.

Richie Rump: Yeah, and I think, you know, when you take a look at risk – I’m putting my project manager hat back on – that you need to actually write them out and say, hey this could happen, this could happen. And what’s the probability that can happen? Is it high? Is it medium? Is it low? And then how much you’re willing to swallow when you go to your management and say, hey if this, which is a medium risk happens, and we are down for five days, are you okay with that? [crosstalk] the risk assessment and then you go off and do what you need to do. So I find that, once they see it on paper, they’ll be like, oh really that’s a high risk, I’m not comfortable with that. Like, yeah, you know what, neither am I…

Erik Darling: You don’t mind if we don’t make money for a week, right? That’s cool, okay, we can just do this then.

Richie Rump: Yeah, no worries.

Does VMware SRM eliminate the need for AGs?

Erik Darling: Alright, there’s one from Marci now. She’s using SRM for VMware, that’s site recovery manager. She wants to know if that makes using Availability Groups for disaster recover redundant. How is that a different strategy form using, I’m going to guess, async AGs for DR? If anyone wants that, they can have it.

Tara Kizer: At my last company, we used Availability Groups, synchronous and asynchronous for HA and DR, and we were also virtualized. But on the systems where we didn’t need the really low RPO, we did use SRM. But that was like for an SSIS server and we were okay with – there wouldn’t be data loss in between. I see Availability Groups, database mirroring, failover cluster instances as lowering your RPO at the Windows SQL Server level. Because what happens when SQL Server crashes or Windows crashes, downtime is longer for those and, I don’t know, SRM, I certainly used, but it was not for my critical systems that had low RPO and low RTO.

Erik Darling: Yep, it was always for – so what I dealt with a lot working with relativity, which has – it’s not just like you have these SQL Servers. You have this sort of army of VMs that all do different units of work. Like some will index different things. I don’t mean like create SQL indexes – like create index files, some will hold files. Workers do all sorts of crazy different things within the relativity environment. So you end up with 200 to 500 VMs – tiny VMs, not big ones – that just do little things. And SRM was great for those because there was no, like, in-flight data on them. They were just doing a task when they got called upon. SRM was awesome for that. for SQL Servers, you know, if you have short RPO and RTO windows for SQL Servers, then I don’t think SRM is the solution for you. If it’s for dev stuff or it’s for things that are not critical where you have, like, 24-hour RPO or eight-hour RPO, then that might make more sense, because I just don’t think you’re going to be doing the snapshots. And I don’t think you’re going to be replicating data at a pace that’s fast enough to meet a short RPO window with it.

Should I set fill factor based on row versioning?

Erik Darling: Alright, next question. I don’t understand this one but I’m going to try to make sense of it. Joe has a job that’s set to change fill factor for row versioning where if most fill factors are between 75 and 90 on the same table, “What are your thoughts?” No. No is my thought.

Tara Kizer: I mean, how are they picking what number they want to use? Per index? That’s a lot of testing you would have had to have done.

Erik Darling: Yeah, it’s a lot of careful thought consideration and testing and not at all just picking numbers at random, I would say. If you could clarify that a little bit and maybe, you know, sneak in why they’re different for certain indexes, that might be helpful too.

What’s the smallest SQL Server I should build?

Erik Darling: Jeremy asks, “What do you recommend the minimum amount of CPU and RAM when standing up a new VM running SQL?”

Richie Rump: All of it.

Erik Darling: Is it on your laptop? What’s it for? It kind of depends. If I’m paying for the licenses then I want to use the minimum licenses that I paid for, CPU-wise. As far as memory goes, as much as I can possibly take.

Tara Kizer: As much as the Windows team will give me. As a production DBA who does not set up the hardware or Windows, I want as much CPU and as much RAM as you’ll give me, and I want more than that, actually. It depends on your system, what your system requires. Maybe eight – I don’t think I’d go any lower than, say, 64GB of RAM. But what happens if you have a 1GB database? It doesn’t need that much.

Erik Darling: No, so there are a lot of, I call them, local variables that might change the answer there. But you know, for me, I just want to make sure that I have enough hardware to meet the needs of the application. So it’s an application by application thing. If it’s a thing no one cares about, then I don’t know, two cores, 8GB of RAM. If it’s a thing that everyone cares about, well I’m going to have to have a bigger talk than that. someone’s going to have to get the credit card out.

Richie Rump: And those needs are going to change, and they most likely will change.

Erik Darling: Data grows. User accounts grow. Everything tends to get bigger and no one out there is archiving. It’s always just bigger and bigger.

I have this cursor…

Erik Darling: Let’s go here. Adam has a question about SQL Server 2012 Enterprise. He has a cursor based application and sometimes he runs into a problem when he’s doing stats maintenance when the cursor could not complete because the schema changed after the cursor was declared.

No, I haven’t run into that, but I don’t use a terribly large amount of cursors. Sorry about that.

Tara Kizer: I suspect that the app using cursors is completely irrelevant to what Ola’s stuff is doing. I think maybe you’ve got a concurrent job running that has changed things and – I don’t know. I don’t think that the application has anything to do with the error that they’re encountering. I think check for other jobs. See what else is running at that time.

Erik Darling: Sounds good to me.

How do I stop people from using regular tables as temp tables?

Erik Darling: Samuel asks, “How do I stop these DBAs…” he you-peopled us, “From using regular tables as temp tables, scripting out drops and adds. This feels wrong. I believe they are afraid of tempdb bottlenecks.”

Tara Kizer: Are you sure that they’re DBAs. I think they’re using that term loosely.

Erik Darling: I’m curious about that. So I guess, for me, it depends a bit on what they’re using those tables for. It’s kind of a wide open one. Okay, well it’s a staging table and they need to import some data, and maybe they want to keep that data around for longer than just the session they’re in. if you could do something like you could make them – if they’re going to do that – either use their own schema so that you know that they’re in a separate place, or prefix the table names with something to let you know that it’s not, like, a real table. And you could just have a job that cycles through, like once a month or once a week, and looks for tables that haven’t been touched in some period of time and just drop some out.

Richie Rump: I guess the regular tables and temp tables, it depends on what you’re doing, right? So if I’m loading like a large amount of data, I’m probably not going to do that in temp tables. And I’m processing that, I’m going to create my own regular table and do that. it depends on what we’re doing.

Erik Darling: It all gets weird. Tempdb is a strange place. Usually, DBAs aren’t afraid of it, though.

I need to troubleshoot long-running Agent jobs…

Erik Darling: Let’s see, Ronnie asks, “I’m suddenly encountering long-running SQL agent jobs that are executing for several hours overnight. Can you recommend a starting point for me to isolate the issue, other than SQL agent logs? Not all jobs are affected.” Do you have a monitoring tool, Ronald? I feel bad calling an adult Ronnie, so I’ll call you Ronald.

Tara Kizer: I would probably just log current activity to a table using sp_whoisactive and then see what was going on. Maybe log it every 15 to 30 seconds, just for this time – although I normally log 30 to 60 seconds in production always. But just get some monitoring in place. And if you don’t have monitoring, sp_whoisactive is a good place to start, and we also have BlitzWho that can do it too. I just like the additional detail whoisactive has.

Erik Darling: That’s a good place to start. Agent jobs are tough because, you know, if it’s a maintenance task like a DBCC CHECKDB or index and stats stuff, then there’s not really a whole heck of a lot you can do about it, so start there. And it might be something that you’re not expecting too. It might be like locking, blocking. It might be something that is not necessarily that the job is performing poorly, it’s just that the job is held up waiting on something else.

Tara Kizer: Yeah, and check what the wait stats are during that time window. Are you now waiting on memory, disk, see what’s going on.

Erik Darling: All that jazz.

Should I tweak fill factor for RCSI?

Erik Darling: We have one last question here. Oh, it’s a follow up. Joe says, “They have this job to adjust fill factor to account for additional bytes for RCSI. They swear…”

Tara Kizer: What? Who are these rocket scientists? I have been using RCSI for years. I mean, I’m talking not quite 10 years, but nine years, and I have never ever done that.

Richie Rump: I’ve never even heard of that. That’s amazing.

Tara Kizer: There is additional overhead for RCSI. I forget what it is. It’s really small though, like, I don’t know, four bytes or something, but I’m not changing fill factor. This is crazy. Oh, man…

Erik Darling: Well you know what – I would set fill factor back to 100% for a week and be like, if you can find me a change, if you can show me on the application where things got worse then we can go back to your way. But until then, we’re going to stick with this. That’s a whole lot of, like, we’re going to fix everything but what the actual problem is.

Tara Kizer: Oh my god, we have page splits…

Erik Darling: Exactly…

Tara Kizer: What year are we in?

Erik Darling: Everything but the problem.

How should I tune CHECKDB?

Erik Darling: Alright, one last one. Marci has another one, “If you have time for this, I’m wondering what to do checks for when DBCC CHECKDB runs for longer than normal.” Corruption, that’s what I would check for. Let’s see – disk I/O, and the error log. I would look for 15-second I/O warnings or messages about saturation or flush cache and just – because usually, when that starts taking a long time, it’s usually a sign that CHECKDB, reading all that stuff from disk, is not having a good time of it.

Tara Kizer: And check with your SAN admins if this is a SAN, because maybe they could see another server running something big and it was just the timing.

Erik Darling: Yeah, because most DBAs, when they set everything up, even though they’re hitting a SAN, all the backup jobs are at the same time, all the CHECKDB jobs are the same. Everything is very regimented. [crosstalk]

Tara Kizer: Of install scripts and you have to create the CHECKDB. We would just run them and then we wouldn’t go back and change the job scales. It was just a template that we would use basically. And we had 700 servers at that company, so you can imagine how many CHECKDB…

Erik Darling: Uh-oh, Tara was consumed by the flames. You know, at least she froze smiling. That’s nice. I’m going to take a picture of that for her…

Tara Kizer: Jobs we had all launching at the same time…

Erik Darling: She’s back.

Tara Kizer: Am I back? I need to call our internet company. It lasts like 30 seconds, so it’s not just Webex, because I could hear you guys still.

Erik Darling: Wild, ain’t that something?

Richie Rump: Look at that.

Erik Darling: Alright, well that’s all the questions we have this week, folks. We should get out of here before Tara’s internet apocalypse continues; before it infects all of us. Alright, thanks for joining us. We will see you next week for another wonderful edition of Office Hours. Goodbye.

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

Adventures In Foreign Keys 5: How Join Elimination Makes Queries Faster

November 16, 2018