Blog

SQL Server 2017: query_store_wait_stats

Last Updated May 12, 2017

Query Store (QDS), SQL Server 2017, Wait Stats

The missing link

Back when the Query Store first got announced, I was amazed.

The amount of information it gathered and persisted between reboots was such a big improvement over the plan cache, plus being able to force plans was really cool.

I was always surprised that wait stats weren’t a part of that, especially with 2016 adding session level wait stats, and run time info to Extended Events and Query Plans.

After all, wait stats really are the bread and butter of troubleshooting SQL Server bottlenecks.

SQL Server vNext

Or SQL Server 2017. Whatever. It adds a new Query Store DMV called query_store_wait_stats. So what’s in there?

Well, you know when you used to open up Activity Monitor, back before you learned how lousy Activity Monitor is?

Right now, that’s kind of what this DMV shows you.

Here’s my uninteresting Query Store query.

SELECT qsws.plan_id, 
     qsws.wait_stats_id, 
     qsrs.runtime_stats_interval_id,
     count_executions,
     qsws.wait_category_desc, 
     qsws.total_query_wait_time_ms, 
     qsws.avg_query_wait_time_ms, 
     qsws.last_query_wait_time_ms, 
     qsws.min_query_wait_time_ms, 
     qsws.max_query_wait_time_ms,
     qsqt.query_sql_text,
     x.actual_xml
FROM sys.query_store_wait_stats qsws
JOIN sys.query_store_plan AS qsp
ON qsws.plan_id = qsp.plan_id 
JOIN sys.query_store_runtime_stats AS qsrs
ON  qsrs.plan_id = qsp.plan_id
JOIN sys.query_store_query AS qsq
ON  qsq.query_id = qsp.query_id
JOIN sys.query_store_query_text AS qsqt
ON qsq.query_text_id = qsqt.query_text_id
CROSS APPLY ( SELECT TRY_CONVERT( XML, qsp.query_plan) AS actual_xml ) AS x
WHERE qsws.plan_id = 62
AND qsrs.execution_type = 0

SELECT qsws.plan_id,

qsws.wait_stats_id,

qsrs.runtime_stats_interval_id,

count_executions,

qsws.wait_category_desc,

qsws.total_query_wait_time_ms,

qsws.avg_query_wait_time_ms,

qsws.last_query_wait_time_ms,

qsws.min_query_wait_time_ms,

qsws.max_query_wait_time_ms,

qsqt.query_sql_text,

x.actual_xml

FROM sys.query_store_wait_stats qsws

JOIN sys.query_store_plan AS qsp

ON qsws.plan_id = qsp.plan_id

JOIN sys.query_store_runtime_stats AS qsrs

ON qsrs.plan_id = qsp.plan_id

JOIN sys.query_store_query AS qsq

ON qsq.query_id = qsp.query_id

JOIN sys.query_store_query_text AS qsqt

ON qsq.query_text_id = qsqt.query_text_id

CROSS APPLY ( SELECT TRY_CONVERT( XML, qsp.query_plan) AS actual_xml ) AS x

WHERE qsws.plan_id = 62

AND qsrs.execution_type = 0

And here are the results.

There’s a wait_stats_id column, but I don’t see that it maps to anything right now. I was hoping that it mapped to Extended Events wait stats map keys, but it doesn’t appear to.

  SELECT dxmv.map_key, dxmv.map_value
  FROM sys.dm_xe_map_values AS dxmv
  WHERE dxmv.name = 'wait_types'
  ORDER BY map_key

SELECT dxmv.map_key, dxmv.map_value

FROM sys.dm_xe_map_values AS dxmv

WHERE dxmv.name = 'wait_types'

ORDER BY map_key

So for now, you just have to take the description of the wait stats for what they are until there’s something to link the actual wait types to.

But still, this is really cool, and a great step towards making Query Store a total performance troubleshooting and profiling tool.

Thanks for reading!

Update (04/25/2017): After a conversation the Patron Saint Of Databases, it doesn’t look like the wait_stats_id column will map to more specific wait stat types, and the wait stats are going to continue to be bucketed by troubleshooting scenario. Logging specific wait types adds significant overhead to collection, and table significant bloat.

Update (05/01/2017): Documentation for this DMV fell out of the sky and landed here.

Brent says: to guess what it might map to, I checked sys.all_columns looking for names like ‘%wait%’ and like ‘%id%’, and I didn’t get a good answer – but I came up with another question. There’s something called sys.plan_persist_wait_stats, and it doesn’t appear to be documented anywhere yet. Hmm.

The Internet ends here at plan_persist_wait_stats

SQL Server 2017: Unpublished Gems

Last Updated May 12, 2017

Erik Darling

SQL Server 2017

Sneaky, sneaky, sneaky

Release notes never seem to tell the full story, do they?

At some point, you have to take matters into your own hands and go hunting for, as Paul Harvey would say, “The Rest Of The Story”.

So what did I find?

New stuff! Brand. New. Stuff. For you. If you ever upgrade, lazybones.

This the full list of new stuff.

I’m not going to talk about all of them, just a few that I find interesting. Hint: you’re not going to learn anything about Linux.

sys.dm_db_missing_index_group_stats

sys.dm_db_missing_index_group_stats
What have we here?

SO MANY COLUMNS!

Of particular interest are last_sql_handle, query_hash, and query_plan_hash. It appears that we’ll finally be able to easily tie missing index requests to their queries, without doing a lot of painful XML processing. I had planned on adding something like this, but couldn’t find a good fit between 1) adding XML processing to sp_BlitzIndex, or adding more DMV queries and rather unpleasant XML processing to sp_BlitzCache. This will make implementing it far easier, assuming it works the way it looks like it will work.

fn_full_dblog

Up next is a new function called fn_full_dblog. This apparently makes querying fn_dump_dblog easier, and hopefully more bug-free.

I wouldn’t want to remember all that, either.

dm_exec_query_stats

Has a bunch of new columns that tell you about ColumnStore index access. You know, if you’re into that sort of thing.

Bright and shiny

All this new stuff, and we’re only on CTP 2. I can’t wait to see what gets added between now and release day.

Thanks for reading!

SQL Server 2017: Quick Rundown

Last Updated May 12, 2017

Erik Darling

SQL Server 2017

ICYMI

Microsoft released CTP 2 of SQL Server vNext (well, yesterday today, but who knows when this thing will hit?).

There’s some great new stuff in the Engine in this release that I’m just crazy about getting to know better.

Going through the Release Notes made me feel things that I haven’t felt since the first time I saw Nicole Eggert.

Highlight Reel

I’ll be digging into each of these in more detail. For now, you’ll just have to listen to me gush.

Batch Mode Adaptive Joins
Do you have any idea how many words have been written about parameter sniffing, on this blog alone? Like, a billion. I started to count but I ran out of bubble gum. Not to mention the number of times we’ve pointed people to Erland’s (other) masterpiece. Well, as this feature progresses, that may all become a thing of the past. No more RECOMPILE hints or eye-squinting at cardinality estimates.

Interleaved Execution for Multi-Statement Table Valued Functions
I might not get to make as much fun of MSTVFs in the future. For all the problems they tend to solve, stuffing complex business logic all into one place, they introduce oodles, possibly even kaboodles, of performance problems as well. One row estimates can be awful even if you’re only working with one row. While I’m excited that improvements are getting made here, I’m still not a terribly big fan of people other than My Favorite Canadian using table variables.

Automatic Tuning
What started as a joke in Azure has come to vNext. Well, okay, maybe it didn’t start as a joke. When I heard about this feature, I thought Microsoft had just weaponized DTA. I really hate DTA, unless I’m trying to demo awful index recommendations. Why yes, I’d love 40 included columns, thanks. What could go wrong? Same index 15 times? Sign me up. What if one goes bad? But this could be really great for ISVs who sell software to shops that often don’t have DBAs or other people capable of performance tuning on staff. SQL will start trying to fill that role.

But, can I tell you what I’m really excited about?

Query Store now also tracks wait stats summary information.

GET THE EVERLASTING @#!& OUTTA HERE!

That is SO awesome. And there was like, zero fanfare about it in the release notes. This is the first thing that I’m messing with in vNext, and it’s super interesting.

Stay Tuned

Now, I know, vNext isn’t even out yet, and people are hardly even using 2016.

Getting people to upgrade is harder than getting them to give up brunch.

The upside is that all these awesome new features won’t be putting performance tuners out of business anytime soon.

The downside is that features that really could help people are going to remain out of reach for various reasons.

Thanks for reading!

Brent says: Wanna see more details? Watch dreamy Joe Sack talk about adaptive query processing. (I can comfortably say in public that he’s dreamy because both Erik and I have dreams about him, so I know I’m not the only one.)

Is it ever worth adding indexes to table variables?

Last Updated April 16, 2017

Erik Darling

Execution Plans, Indexing

Disclaimer

I found this totally by accident, and it even surprised me.

You can probably guess the TL;DR on this is yes, huh?

Dude, where’s my table variable?

I was trying to come up with a demo for something totally different. Don’t ask. Seriously. It’s top secret.

Okay, so it’s just embarrassing.

Anyway. I had these two queries. Which are actually the same query twice. The only difference is the table variable definition.

DECLARE @UserId TABLE (UserId INT NOT NULL)
INSERT @UserId ( UserId )
    VALUES ( 22656 )

SELECT p.OwnerUserId, COUNT(*) AS Records
FROM dbo.Posts AS p
LEFT JOIN @UserId AS ui
ON ui.UserId = p.OwnerUserId
GROUP BY p.OwnerUserId
GO 


DECLARE @UserId TABLE (UserId INT NOT NULL PRIMARY KEY CLUSTERED)
INSERT @UserId ( UserId )
    VALUES ( 22656 )

SELECT p.OwnerUserId, COUNT(*) AS Records
FROM dbo.Posts AS p
LEFT JOIN @UserId AS ui
ON ui.UserId = p.OwnerUserId
GROUP BY p.OwnerUserId
GO

DECLARE @UserId TABLE (UserId INT NOT NULL)

INSERT @UserId ( UserId )

VALUES ( 22656 )

SELECT p.OwnerUserId, COUNT(*) AS Records

FROM dbo.Posts AS p

LEFT JOIN @UserId AS ui

ON ui.UserId = p.OwnerUserId

GROUP BY p.OwnerUserId

DECLARE @UserId TABLE (UserId INT NOT NULL PRIMARY KEY CLUSTERED)

INSERT @UserId ( UserId )

VALUES ( 22656 )

SELECT p.OwnerUserId, COUNT(*) AS Records

FROM dbo.Posts AS p

LEFT JOIN @UserId AS ui

ON ui.UserId = p.OwnerUserId

GROUP BY p.OwnerUserId

What I saw in the query plans was amazing.

The second JOIN query had no table variable operator in it. It had been optimized away.

Well, that’s pretty cool. But that doesn’t seem to be anywhere in the XML. Here’s a pasted plan: https://www.brentozar.com/pastetheplan/?id=H1HbqOxae

Crying Game

Since I’ve never seen this before with a table variable, I thought I might have stumbled on some crazy enhancement for them. But apparently it’s just regular ol’ join simplification.

Thanks for reading!

Brent says – don’t feel bad if you have to read this twice to understand what was going on. It took me three times before I caught that it was a left outer join.

Wanna speak at the PASS Summit? Here’s how.

Last Updated April 16, 2017

Brent Ozar

#SQLPass, Writing and Presenting

The PASS Summit is the biggest SQL Server community event, and a lot of us speakers have “present at PASS” on their bucket list.

Getting in is legendarily difficult because competition is fierce. Many sessions are submitted for each one that’s accepted, and you have to work hard to stand out from the pack. This year’s Call for Speakers comes out on May 10th, and PASS has said this year will be shorter than normal – and you won’t get a second chance to get your abstract right.

Here’s what I’ve learned over the years about getting sessions accepted.

First, define their pain and your relief. Forget the title and the abstract; first, you need to figure out why people would attend your session. They’re having some kind of problem at work, and that problem is bringing them to a conference to learn how to fix it. What’s that problem, and how are you proposing relief? To learn more, read The Best Presentations are Based on Pain.

Second, define the target attendee feeling that pain. You might think that your session is for everyone, but due to its size, the Summit has a wide variety of attendees. You get everybody from non-technical managers to deeply technical app developers to DBAs who can’t touch code. Understanding exactly who you want in the seats helps you craft the abstract to appeal to them. To learn more, read Define Your Presentation’s Attendee.

Write the technical part of your abstract. Don’t use any catchy wording at all: just communicate the exact pain, relief, and attendee in as few words as possible. Get this down to as few words as you can – if a word isn’t helping, cross it out. This becomes your abstract’s skeleton.

Add your personality. Once you’ve got a small, strong skeleton, you can dress it in a variety of different outfits to bring your personality to it. You can make a car theme for it, medicine, therapy, sportsball, fashion, Survivor, whatever really speaks to you. I’ll often write 3-4 different versions of the same skeleton abstract, trying different ideas. To learn more, read ProBlogger’s 52 Types of Blog Posts.

Finally, send it to other presenters for review. If you have a social network of people who’ve been accepted to Summit before, send it to them – not just one or two folks, but several different people who have different points of view on what makes a successful submission.

If not, submit it to GroupBy.org, where presenters (including me!) and attendees will post comments and questions. They’ll help you see things in a new way, and who knows? Maybe you’ll get accepted! Starting this year, you have to have 3 speaking engagements under your belt to speak at Summit, and GroupBy is a great way to get the experience you need without traveling.

How to Join the SQL Server Community Chat in Slack

Last Updated December 11, 2020

Brent Ozar

GroupBy Conference

Slack is a team chat system where you can:

Go to SQLslack.com to get a free instant invite, and then join
Go to https://SQLcommunity.Slack.com, or download the Slack app
Meet cool people and join their chat rooms

Chat rooms at SQLCommunity include:

- #SQLhelp and #PowerShellHelp – very active
- #FirstResponderKit – questions and development discussions about sp_Blitz and friends
- #Consultants – share ideas, strategies, lamentations about the business

#Blogging and #PresentingOrSpeaking – community activism

And there’s dozens more – in the app or the web, click on the Channels list and find your fetish.

It’s #GroupBy time! Wanna watch free SQL Server training?

Last Updated April 17, 2017

Brent Ozar

GroupBy Conference

You voted in a great lineup for this Friday’s free GroupBy.org conference:

8AM Eastern – How to Use Parameters Like a Pro and Boost Performance by Guy Glantser
10AM – Introducing the SQL Server 2016 Query Store by Enrico van de Laar
Noon – Worst Practices & Less Known Limitations for Columnstore Indexes by Niko Neugebauer
2PM – Gems to Help You Troubleshoot Query Performance by Pedro Lopes
4PM – Crash Course on Better SQL Development by Vladimir Oselsky

Register now, then vote to pick the next sessions.

GroupBy is 100% community-driven – so it’s up to you to pick the list. After you’ve registered for Friday’s event, you can head over to the voting page to decide the next lineup.

Here’s some of the sessions I’m excited about:

Measuring the Overhead of the Query Store by Jason Hall – I think Query Store is the single most important feature of 2016, and a tool that will make your performance tuning dramatically easier. (We even throw a high-priority warning in sp_Blitz if you’re on 2016 and you haven’t turned Query Store on for a database yet.)
Alleviating database consolidation pains by Alexander Arvidsson – this is one of those projects that looks really easy until you get halfway into it. During our weekly Office Hours, we get a lot of questions about “is it safe to put these two databases on the same server?”
Apache Zeppelin and SQL Server: two best friends by Davide Mauri – Zeppelin is a neato technology that lets you visualize data quickly, and you can run it on your desktop in a Docker container. It’s like free, open source PowerBI. To see it in action, watch Davide demo a quick graph. I’m not going to become a Zeppelin guru in an hour, but this session will help me see if it’s a good fit for my clients.
T-SQL for Beginners by Andrea Allred – no, not because my T-SQL sucks (which it does), but because I think the community needs a good 60-90 minute online tutorial video that we can point users to. Since GroupBy records & transcribes all of the session videos, this would be a helpful community asset.
Green is good, Red is bad – Turning your Checklists into Pester Tests by Rob Sewell – Pester is a unit testing framework for PowerShell, and hold on, wait, come back here! Like the Zeppelin session, this isn’t something I’m probably gonna implement in an hour, but I’d like to see an expert give a tour of it. Rob is @SQLDBAwithBeard, writes at SQLDBAwithaBeard.com, and he’s spoken all over the place, so I bet this will be good.
Level Up Your Biml: Best Practices and Coding Techniques by Cathrine Wilhelmsen – I hear from all my SSIS friends that Biml is the future, and it’s hard for them to find good online training material. Cathrine’s a solid speaker.

So go register, then go vote. I’m excited to see what you pick.

[Video] Office Hours 2017/04/12 (With Transcriptions)

Last Updated April 15, 2017

Brent Ozar Unlimited Team

SQL Server, Videos

DISCLAIMER: this week, we learn that a naked Erik Darling looks like a bear covered in squid ink. Thankfully, we don’t learn that via the webcams.

Aside from that, Brent, Richie, and Erik also discuss updating stats and rebuilding indexes, feature request for Paste the Plan, included columns’ effect on DML, CLR store procedures, SQL command and SQL command query mode, ALTER TABLE statements, using a GUID as a primary key instead of an IDENTITY column, keeping SQL server settings across multiple servers in sync, and more.

Here’s the video on YouTube:

Office Hours Webcast - 2017/04/12

Watch this video on YouTube

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

Enjoy the Podcast?

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

Office Hours Webcast – 2017-04-12

What do I do if stats updates are causing blocking?

Brent Ozar: Let’s see what we got here, John says “after we upgraded from SQL Server 2014 to 2016 last month we’re seeing stats update get blocked while my indexes are being reorganized, then my inserts are being blocked too.” So he’s running all kinds of problems with updating stats and rebuilding indexes. I’m going to zoom out to a bigger question and say if you’re seeing blocking due to index maintenance and statistics, what would you do differently?

Erik Darling: Oh you’re asking me?

Richie Rump: He wasn’t asking me, that am for sure.

Erik Darling, I don’t know, he could have been asking the person who asked the question too – rephrase that long bible… I would want to know first if he’s running them at the same time or if there’s some query running which is like pooping its pants and saying ooh I’ve got to update stats now, and that’s what the organized stats update conflict it. That would be my first question because if you’re running them both at the same time, well then that’s not a good idea.

Brent Ozar: Yes, index of stats at the same time, that’s a bad idea to begin with. Don’t change a table across multiple sessions – bad idea.

Do rollbacks affect AGs differently?

Brent Ozar: Gordon says, “if I have a database in an AG and within a transaction I’m moving data from it to an archive database that’s not a member of the AG will it cause a problem on an AG secondary if I rollback the transaction?” You may want to sketch this out and ask it on Stack Exchange because there’s a few different things at play here, like I’m wondering why you’re even asking about the secondary if you’re talking about inserts that are over on the primary node…

Erik Darling: Well I’m guessing if it’s an archive there’s an insert and then a delete and then there might be a rollback of the delete?

Brent Ozar: I would do the insert in a separate transaction, make sure everything worked perfectly, and then the delete should be just one statement, just delete from the primary wherever it’s already in the archive, but more details on that one are probably needed and to see it’s best on dba.stackexchange.com.

What’s causing all these forwarded fetches?

Brent Ozar: James asks “I ran sp_BlitzFirst and I noticed high forwarded fetches per second. I’m seeing 36,000 forwarded records per second, how do I find out what table is causing this issue?”

Erik Darling: Run sp_BlitzIndex on your databases in mode four and you’ll see all the heaps you have, and sp_BlitzIndex will tell you if you have a heap with many forwarded records and you can link that up to whatever’s going on there. The other thing you could do is, I believe if you run BlitzFirst with the plan cache switch turned on, you might get the query plan of what’s running at the time. Also, BlitzWho which runs at the beginning and end of BlitzFirst in expert mode will give you what’s currently running and you might be able to see the query plan there. So in order, BlitzWho, BlitzFirst with the plan cache on and then BlitzIndex, that’s where I’d go.

Brent Ozar: Wow, that’s good.

Erik Darling: This is what happens when you spend a lot of time in GitHub, you learn what stuff does.

Brent Ozar: That’s true…

Erik Darling: [Crosstalk]

Richie Rump: And what stuff does it do?

Brent Ozar: Bugs, side effects…

Erik Darling: Work…

Brent Ozar: It’s been a couple of weeks fixing bugs, ladies and gentlemen, and introducing bugs; we introduced a few as well.

Erik Darling: It was nice to meet them.

Richie Rump: Never nice to meet them, no.

What do I do with Paste the Plan feature requests?

Brent Ozar: Well speaking of which, Wes has a feature request for you, Jorriss, for Paste the Plan, he says he want to be able to jump to the pain points, either high percentage of the query plan or high query bucks.

Erik Darling: Wow, what would that be worth to you?

Rickie Rump: That’s interesting.

Brent Ozar: Yes, could we have like swipe your credit card through and then it takes you to the right – I like that…

Erik Darling: How many Bitcoins?

Brent Ozar: We actually don’t have a place to submit feature requests but if you hit contact at the top of the page, you can email us there and we still won’t code it, but at least we’ll send you something nice in response, you know, we might actually code it – we could. Wes says it might actually be worth using paste the plan.

Richie Rump: [Crosstalk] See I was going to before, but now I’m going now, Wes, that’s it, it’s on. …

Should I make a 2×3 VM or a 3×2?

Brent Ozar: Now I don’t know why I’m asking Wes’ question because he’s got another one that’s a real question and I should skip it, Wes says, “I have two socket 14 core VM hosts, I’ve freed up a couple of SQL Server licenses that are already paid for, I want to throw those at our current VMs, should I provision…” Okay you’ve asked like five things that are unrelated so let’s get to the meat of it, “Should I provision the VM with two CPUs with three cores each, or three CPUs with two cores each?”

Erik Darling: Neither…

Brent Ozar: What in the hell? Yes, so what would you do Erik?

Erik Darling: I would either go with one core with a bunch of it or I would go with two cores with things split evenly…

Brent Ozar: Sockets…

Erik Darling: Right, sockets, sorry, sockets with cores split evenly. The odd number thing with CPUs and cores never seems to just work out all that well.

Brent Ozar: And I would just go two by two…

Erik Darling: That’s something that sp_Blitz looks for now too, if you have an odd number of cores, which oddly enough has fired off on 2016 when they do the auto soft-NUMA thing [crosstalk] because the DMV show things as being weird and like – there’s been one client with Tara and a couple with me now where I run it and it’s just like, you have three cores with how many… then auto soft-NUMA…

Brent Ozar: Wow, yes, neat-o. and it would be easy to set that up to repro it in a lab, I need to do that.

Brent Ozar: Wes says, “is six cores no-bueno?” No, you could do one socket and six cores and you’d be fine, that works totally.

Erik Darling: I would like that totally fine.

Are elastic databases different than regular databases?

Brent Ozar: Don says, “good morning, I have a question about elastic databases in Azure, are there any DBA tips that may be different than usual?” Really, I’ll just step back, Azure SQL DB, there’s things that you do differently in Azure SQL DB period, forget even elastic databases, but like you’re not managing backups, you’re not doing CHECKDB. If you want to get your data out things are a little bit more difficult. You can’t use exactly the same DMV scripts, so like not everything works in Azure SQL DB as other databases. Performance monitoring is a tough one for me because if you have one chatty SQL Server or SQL database and the rest are dead, it’s hard to manage that holistically across elastic pools but you’re just relying on Microsoft Azure’s portal there to do the management for you.

Can index includes slow down DML operations?

Brent Ozar: Guillermo asks – holy-moly he’s got three questions lined up. Guillermo, I’m going to ask the first one then I’m going to [Crosstalk] to someone else. Yes he’s got lots of … copy and pasting them in from all his coworkers probably, they’re ashamed to ask.

Erik Darling: Guys come here…

Brent Ozar: Check this out, people are actually answering, they don’t know what they’re talking about but they’re answering. Guillermo says, “can included columns on an index slow down DML operations? Is there any reason that I shouldn’t just, other than using more storage, should I just include all the fields?”

Erik Darling: He asked a similar question last week where I think he was trying to talk someone out of creating indexes that were like a few key columns then including all of the rest of the columns in the table, and I think I answered that then, so to, I guess, expound on that point, yes included columns can impact DML because you have to now keep the leaf level. With Rich, the leaf level is up top, with me the leaf level is down the bottom. You have to now keep the leaf level of that index updated along with everything else.

Richie Rump: A tree has leafs at the top.

Erik Darling: Yes, but an index doesn’t grow up, it’s on a disk…

Richie Rump: Don’t look at my slides because it does have the leafs at the bottom.

Brent Ozar: Oh no, no…

Erik Darling: I like to draw mine sideways.

Brent Ozar: Oh like [Crosstalk] org-chart…

Erik Darling: This is your B-Tree, everyone’s like what, why is it round? Like because your disk is round. [Crosstalk] SSD, then it’s a…

Brent Ozar: There’s another idea for a blog post we should write, Salvador Dali draws your database diagram.

Erik Darling: There you go. [Crosstalk] Homer Simpson crying on it…

Brent Ozar: Or Grampa Simpson walking it …

Richie Rump: He did actually say any other downsides besides using more storage, wouldn’t using more memory be one of those as well?

Erik Darling: Yes, more space on disk, more space on memory.

Richie Rump: That’s right, got to pull it out and read it somehow…

Brent Ozar: CHECKDB takes longer, index rebuilds, stats updates, you name it.

Why do CLR stored procedures work on only one AG replica?

Brent Ozar: Deborah asks a question that I know all of us are going to fight over for the opportunity to answer. She says, we have stored procedures that are CLR…” Should I continue reading, gentlemen? “they work on the primary replica but not the secondary, we get a .NET framework error. Supposedly the two nodes are identical, do you have any hints about the things we should check?

Erik Darling, Yeah… That was mirroring, I don’t know if it’s true for AGs as well, where the first time you failover you have to enable TRUSTWORTHY.

Brent Ozar: That’s true, I don’t think that’s – I love where you’re going, so explain that…

Erik Darling: Well so with mirroring there was a bug where if you had CLR, the TRUSTWORTHY, that should be enabled on a database, the first time you fail it over, that wouldn’t carry over with it, you would have to go enable it on the secondary; so you would have to enable CLR over there. I guess the other question would be if they’re both the same version on both nodes.

Brent Ozar: I would got for the .NET version too, there’s no reason that the.NET version is the same across all those and you may be targeting a specific .NET version that is not on the secondary. I’m not good enough to know what the command is to check, I know there’s a SQL Server command you can use to check the .NET and Framework Installed version, and I don’t know what that is off the top of my head.

Erik Darling: There might be a DLL loader in the error log too that would tell you – that’s wild speculation though.

I got a big raise after going to Brent’s class…

Brent Ozar: Graham says, and of course I’m going to read this one real slow because it’s excellent, “I went to Brent’s performance tuning class, which was awesome by the way…” I should probably repeat that, he says, just so everyone hears, “I went to Brent’s performance tuning class, which was awesome by the way, and I got a job offer shortly after completing the class. Then I got a great counter offer to stay at my current jobs, so take Brent’s classes and you should make more money – results may vary.” Or, I’ll raise you one better than that, Richie took one of our classes before he came to work here and we ended up hiring him, so…

Richie Rump: It was the very first one too, It was your first in-person training classes.

Brent Ozar: Hotlanta… So have you ever put any of that stuff that you learned to use yet or no?

Richie Rump: Yes of course.

Brent Ozar: I was joking…

Richie Rump: Of course… Not here at Brent Ozar, no, but like elsewhere yes, absolutely.

Brent Ozar: The other jobs.

Do you use SQLCMD query mode?

Brent Ozar: Let’s have another one of Guillermo’s excellent question. Guillermo asks, “how much do you guys use SQLCMD as well as the SQLCMD query mode and what do you use it for?

Erik Darling: Wow, well I always used that when I was messing around with AGs in the lab because it made it real easy to hop around and do stuff in there, that was always my primary use for it.

Brent Ozar: That’s the only time I’ve ever used it, it’s so useful for that, to jump, run queries back and forth between different replicas – excellent for that. Kendra used to use it for demos sometimes, she would use it just to have a query coming from somewhere else, you know, just outside of management studio.

What are database snapshots good for?

Brent Ozar: Because Guillermo asks so many good questions there’s others in the queue but I’ll ask Guillermo’s next one because it’s so doggone good, “how often have you guys used native database snapshots and for what purposes?”

Erik Darling: Rollbacks.

Brent Ozar: Rollbacks for transactions?

Erik Darling: No – so at a previous job, NDA-NDA-NDA, where there were like weekly software releases, we would take a database snapshot before a code release so that if we had to run anything back in a hurry we could roll it back in a hurry or restore the database snapshot back over stuff.

Brent Ozar: What about you Richie, have you ever used database snapshots for anything?

Richie Rump: Nope.

Brent Ozar: Alright, there you go. I’ve also used them to get point-in-time reading for, you know, someone says hey I need a consistent point-in-time as of this morning, and snapshots have worked for that. Okay so there’s an extra check I guess, we’ve all used it for CHECKDB because that’s how snapshots work behind the scenes, but that’s kind of a cheating…

Erik Darling: Technicality.

Brent Ozar: Yes.

Erik Darling: The snapshot of your mom…

Brent Ozar: It broke the mirror.

Will query memory grants cause page life expectancy to drop?

Brent Ozar: Brian says, “what can cause page life expectancy to plummet on a server with plenty of memory and seemingly static activity, could it be a bad query that gets a large memory grant to clear the buffer pool?” [Crosstalk] If you knew about that happening or suspected that of happening, where would you go look?

Erik Darling: Well I guess I’d have to go look in BlitzCache and sort by memory grant if I was on a newer version of SQL Server. If not I would just turn on Resource Governor and cap everyone’s memory to zero… Just kidding.

Brent Ozar: I like it, one kilobyte…

Erik Darling: You get nothing.

Brent Ozar: that sp_BlitzCache sort order memory grant requires 2012, 2014 or 2016, but you have to be on a current service pack or cumulative update. If you search BrentOzar.com for sp_BlitzCache and memory grant you’ll see the version where Erik introduced this and it also has the versions of the exact service packs and cumulative updates that you need to be on.

Erik Darling: Fun fact, you also have to restart after installing one of those, otherwise your version number will update but you won’t have that information in the DMV. I found that out the really weird way on the phone with someone one day.

Brent Ozar: Oh wow…

Erik Darling: But then, like server-up time was like way too long and I was looking at it, I was like did you reboot after you installed that patch?

Brent Ozar: Wow [Crosstalk] how you would even detect that…

Erik Darling: Well just check the DMV for the columns I guess.

Brent Ozar: Yeah, that blows. [Crosstalk]

Erik Darling: BiltzCache, I don’t think so…

Brent Ozar: At this point those things are so big that anything you touch is terrifying, you know, we go in there and we want to make changes and – Erik and I were on a performance tuning terror last week, two weeks ago, and we both just made rampant changes through sp_BlitzIndex, we did all kinds of changes all throughout and dealing with the merge afterwards was horrible. My first thing was alright, I just made this 3x to 20x faster, but I think it’s going to be so full of bugs it’s not even funny, and it was, it was.

Richie Rump: Oh no, it was funny to me, it was very funny.

Erik Darling: Finally, someone else’s bugs.

Richie Rump: Exactly.

Brent Ozar: Yes, Doug was like how did you do that, how did you make it so much faster? And I’m like well I put all kinds of bugs in there, that works every time.

Erik Darling: You skipped a bunch of stuff.

Does ALTER TABLE work through a table in portions?

Brent Ozar: J.H asks, “does ALTER TABLE statements lock the entire table all at once or in portions?” oh I love how you’re going with that, as if you like added a column would it lock portions of the table as it rolled through the table?

Erik Darling: Probably not, I would guess not because after a certain number of locks that’s just going to try and go for – that’s going to go for an object lock and I’m pretty sure ALTER TABLE is one of those like, no, no, no I’ll wait, I’m going to wait and hang on and do that. Unless, unless you add it as a nullable column with no default value. Then, you know, if you add it as a nullable column you could do some stuff afterward to update…

Richie Rump: Yes, and that’s what you should do anyway.

Brent Ozar: Lacing fast – there’s like a name for that in development and I never remember what it is, like only make constructive changes or additive changes, you know, gradually, never drop things out of your tables over time. I can’t remember what the name is but one of these days I’ll remember it.

Why should I use a GUID as a primary key?

Brent Ozar: Let’s see, Mark says, “can you give me one good reason to use a GUID as a primary key instead of an identity column?”

Erik Darling: You’ll never run out of them.

Brent Ozar: Ooh okay, I like that. Also really good for when you’re doing writes in two locations and you need to make sure that they never have any conflicts. With two locations you could think about doing an identity field that one starts at one and goes up and the other starts at negative one and goes down, and that’s okay but then it starts to fall apart as you start to add more replicas in or like more multi-user bidirectional type stuff. You need to generate unique IDs on the client’s side for batches and then push stuff up into the database. If you want to piss off [Crosstalk]

Richie Rump: That’s how I’ve used it actually, as the client – each client generates their own IDs and then it comes into the database.

Erik Darling: I guess my only piece of advice there was if you’re going to use it as the primary key, I would use it as a non-clustered primary key rather than sick my clustered index around it and have to deal with all the GUID inserts all over the place.

What is the dedicated admin connection for?

Brent Ozar: Guillermo says – here’s another awesome question… Well Guillermo, here’s the deal, I see a theme with your questions; you’re going through books online and picking features and then saying have you ever used this feature. I’m not telling you to stop, I’m just saying I see what you’re doing there. Guillermo says, “has a dedicated amen connection…” And I think he means not amen as in the religious thing or Idi Amin, but a “dedicated admin connection ever come in handy for you and how?” I feel so sorry for our transcriptionist who has to keep up with this stuff.

Erik Darling: it’s great for THREADPOOL demos.

Brent Ozar: What is THREADPOOL?

Erik Darling: THREADPOOL is a nasty weight that you run into when you run out of CPU worker threads to assign the queries and they all just kind of stop and wait, you know, hanging on a minute here – 15 minute union worker thread break, everyone go and get your lunch bucket.

Brent Ozar: Yes it’s good for that. That’s the only time I’ve ever used it, I can’t think of any other time where I’ve had to use it.

Erik Darling: I know the book that Wes’ VMware guy read.

Should I build a 1×6 VM or a 6×1 VM?

Brent Ozar: [Crosstalk] there’s a follow up here to Wes’ CPU configuration thing, and there’s a couple of problems with this follow up here. It says, “I just told my VMware admin to set it to one socket with six cores, he linked something and said that screws with vNUMA and that VMware says to go wide and flat with six sockets with one core each. Do you have a rebuttal?” that’s totally okay, although I would say if you’re on standard edition, standard edition’s going to max out at four sockets, so that trick is only going to work if you run enterprise edition. I would also say, whatever he just linked you to, make sure you read it carefully, read the whole thing and use a highlighter to start calling out things that you know aren’t right, for example, if the author says something about page life expectancy being measured in seconds, maybe that’s something you want to just think about before you take the gospel truth out of that one.

Erik Darling: [inaudible] for parallelism…

Brent Ozar: That’s not what I said…

Erik Darling: Page life expectancy, which is in seconds, but it has nothing to do with CPU cores, probably. That would make parallelism so weird…

Brent Ozar: Yes right.

Brent Ozay: Adam says, “interesting, our VTAM for VMware said the opposite.” Yes it’s funny, you can get a lot of interesting conflicting advice out there from folks.

Erik Darling: I would try it both ways and see how it goes better, because if it’s SharePoint who cares?

Brent Ozar: Yes that’s true, just give it one core and be done.

Erik Darling: Yes right, that’s all it needs anyway.

Brent Ozar: I would also say, you know, we’re joking a lot about it and neither of those is going to screw you, as long as you don’t mess up the licensing thing with standard edition. Whether you go six sockets one core or one socket with six cores, if that’s your biggest problem you’re in pretty doggone good shape.

Half our NUMA nodes are offline. How should we set MAXDOP?

Brent Ozar: Daisy asks, “in production…” Oh I’m going to have to take a deep breath. Daisy you’re lucky, you have the only question left, otherwise I wouldn’t go through all of these, so any of you who have questions in, feel free to get yours in…

Erik Darling: Otherwise I’m going to start taking clothes off.

Brent Ozar: Oh god [crosstalk]. Daisy says, “in production there are two SQL NUMA nodes online out of four…” To go through a long story, she has some gotchas with NUMA nodes being online and offline. There’s a bigger problem here; if you have NUMA nodes that are offline I also worry about you having memory offline. Go read – run sp_Blitz on your SQL Server… Go read sp_Blitz – don’t, whatever you do, don’t read sp_Blitz. Run sp_Blitz on your SQL Server and read the output, one of them is going to warn you about CPU and memory being offline. The only reason I’m having you do that is go copy the URL out of there and go to that blog post, it talks about what happens when you have CPU offline or memory offline. MAXDOP is the least of your problems, you were asking a question about MAXDOP, you have much bigger problems than that.

Brent Ozar: Alright, well good news, Erik doesn’t have to take any clothes off because more questions are coming in.

Erik Darling: I look like a bear got attacked by a squid, not pretty.

Brent Ozar: It took me a minute… Okay so there’s the next visual we’ll have our cartoonist go draw, Erik’s going to be wrestling with a squid [crosstalk]…

Should I store files in the database?

Brent Ozar: Don says, “do you guys recommend storing binaries in the database, like Excel files, PDFs, or should I store my files somewhere else?

Erik Darling: No es bueno. Store them somewhere else…

Brent Ozar: Por que?

Erik Darling: There are no es BLOBs in el database-o. Jeremiah has a great post from the blog grave about keeping BLOBs out of the database and how it’s much better to just store a pointer to where they live in the database and then let something else worry about storing them and opening them…

Richie Rump: Yes, I’ve made that mistake in my career where we put BLOBS on SQL Server and it’s like, okay, so how am I supposed to read this outside, how is someone supposed to edit it and update it? Well now we’ve got to, you know, put it as a BLOB again and pull it out and it’s just – it’s hassle, it’s a mess.

Erik Darling: And have you ever seen a stored procedure that was made to edit a PDF?

Brent Ozar: Oh god…

Erik Darling: It’s worse than those or ones you’re like I need to generate a barcode in T-SQL, how do I do that? You don’t, come on.

Brent Ozar: In fairness, I do also have to point out that Greg Low recently published a blog post on how you write line numbers in T-SQL…

Erik Darling: Oh good, now I’m going to see that on all my client’s servers.

Richie Rump: Oh god, please no. [Crosstalk]

Do I need a separate network for my AG replication?

Brent Ozar: Matt asks, he says he links to an article on SQL Server Central and he says, “do I need to configure a dedicated network for my availability group, do I need a separate network for traffic throughput?” generally speaking no, if you think about most modern servers have 10GB Ethernet and it’s really hard for an availability group to saturate 10GB Ethernet, even when it’s sharing traffic with regular network type stuff. I have not seen something that I really needed to do that on, on any SQL Server. It’s like Bigfoot, I’ve heard of people seeing it, I’ve just never actually seen it myself.

Erik Darling: Well that’s when I turned on jumbo frames, then everything worked.

Brent Ozar: Yeah oh god, increase packet size to 88 miles an hour…

Erik Darling: If we use large pages then everything was fine.

How should I keep settings in sync across multiple servers?

Brent Ozar: Guillermo asks, “what are the best ways to keep SQL Server settings across multiple SQL Servers in sync. Like if you’re using availability groups, database mirroring, how do you keep like sp_configure stuff in sync? Like if you’re using availability groups, database mirroring, how do you keep like sp_Configue stuff in sync?

Erik Darling: Agent jobs that look for changes to Agent jobs.

Brent Ozar: Turtles all the way down.

Erik Darling: It’s awful…

Richie Rump: Like Inception.

Erik Darling: You know like with all the stuff that Microsoft adds to these things you would thing that just a simple mechanism to keep stuff straight and lined up would be there.

Brent Ozar: And I have this philosophy that you shouldn’t because a lot of times there are differences in the servers, like that I’m going to have different settings on a primary that’s used for readable, you know, for readable reports than I would – think max memory for example, not all your severs are going to have the same max memory, so I would go hesitant on that… [Crosstalk]

Erik Darling: Some stuff that you want all on there, available in case something starts happening, like users.

Brent Ozar: Max worker threads.

Erik Darling: That as well. Turn that up to 50,000.

Brent Ozar: 50,000. I got a talking too recently, a client too by the way, whose like – and you know who it was, they pulled me aside and they were like we set max worker threads, Erik said it’s wrong but you should tell him that it’s fine for us. Okay, alright cool.

Erik Darling: Well I agree with them.

Brent Ozar: Yes, you like them, they’re nice people.

Erik Darling: they are.

Brent Ozar: You like all our clients.

Erik Darling: I do, they’re so nice to me.

Brent Ozar: They’re adorable, even though you look like a bear covered in squid ink.

Erik Darling: Even though I’m nude for all my calls that are weird.

Brent Ozar: That’s why we got him a high definition webcam, so everybody can pick out the exact tattoos there, because sometimes it’s confusing which parts is the squid and which parts is the bear, but…

Erik Darling: Squid bear.

What’s included in the new Enterprise Subscription Plan?

Brent Ozar: Kevin Bradley, I try not to ask last names but this is good, it’s a good question so he shouldn’t be ashamed, because sometimes I don’t want people’s employers being read out or whatever, but Kevin says, “training question, would you describe what’s included in the subscriptions, especially the enterprise bundle?” [Crosstalk] hand puppets…

Erik Darling: Happy.

Brent Ozar: If you go to the site and click training at the top of the page the easiest way to see it is on that training page there are sections for what’s in the DBA class, what’s in the performance class? Just go to the top and click training, you can actually go click on each class and see all of the modules, how long they are before you subscribe. You can’t watch previews of every module or anything like that, but it gets you detailed step-by-step descriptions of what’s inside of every other module.

Are you archiving these Office Hours videos?

Brent Ozar: then the last one we’ll take, J.H says, “Are you archiving these office hours’ videos so that we can watch them again afterwards? I don’t know if you’ve answered my question as I keep losing connection here and I have to reconnect back in.” Good news, if you go to the top of our site and click blog we actually – not only can you watch them, but wait, there’s more, you can read the transcripts, so you don’t have to look at the bear covered in squid ink, you can just read the transcription afterwards and see exactly what we said about your question, and we did answer your question J.H because you’ve been in a few times and I think you always ask good questions.

Erik Darling: you really thoughtfully answered that question too, now I’m sad and depressed, I’m going to have to go to the gym and work on stuff.

Brent Ozar: On the bear and the squid, is today a bear day or a squid day?

Erik Darling: It’s all bear days, all bear days. Squid days sound like cardio, I don’t want to do cardio, I’m lazy.

Brent Ozar: You and me both. Alright well thanks everybody for hanging out with us at this week’s Office Hours and we will see you guys next week, adios.

Replacing ISNULL In A WHERE Clause

Last Updated April 7, 2017

Erik Darling

Execution Plans, T-SQL

I like blogging

Really, I do. But sometimes I like a change of pace.

Instead of blogging about this question, I posted it over on dba.stackexchange.com. Also, I thought it was an interesting question, and apparently many other people agreed. Not just because I wanted some more points, but because blog comments are notoriously sucky for people to post responses in, when they involve code.

For example, there were a couple great answers on my question from Users DMason and Joe Obbish, neither of which would have been possible in blog comments.

I think I’ll do that more often

It was fun putting all the scripts and examples together and not wrestling with WordPress to get things how I wanted them. I encourage our readers to sign up and start posting and answering questions there as well.

If that type of community isn’t your speed, then there are some great forums over at SQL Server Central and SQL Team.

Thanks for reading!

The Surprising Behavior of Trailing Spaces

Last Updated April 11, 2017

Brent Ozar

T-SQL

In every training class we do, I learn stuff from students. Here’s last week’s surprise.

Create a temp table, and put in a value with trailing spaces. Then query for it using the = and <> operators, using different numbers of trailing spaces:

CREATE TABLE #Wines (Winery VARCHAR(50));
INSERT INTO #Wines VALUES ('Cliff Lede        ');
SELECT * FROM #Wines WHERE Winery = 'Cliff Lede';
SELECT * FROM #Wines WHERE Winery <> 'Cliff Lede';
SELECT * FROM #Wines WHERE Winery = 'Cliff Lede        ';
SELECT * FROM #Wines WHERE Winery <> 'Cliff Lede        ';

CREATE TABLE #Wines (Winery VARCHAR(50));

INSERT INTO #Wines VALUES ('Cliff Lede ');

SELECT * FROM #Wines WHERE Winery = 'Cliff Lede';

SELECT * FROM #Wines WHERE Winery <> 'Cliff Lede';

SELECT * FROM #Wines WHERE Winery = 'Cliff Lede ';

SELECT * FROM #Wines WHERE Winery <> 'Cliff Lede ';

SQL Server simply ignores the trailing spaces:

When it comes to trailing spaces, SQL Server is the honey badger

The mind-blowing part to me is the <> operator – that seemed utterly crazy to me.

And if you add another table, and join to it, guess what happens:

CREATE TABLE #PreferredDrinks (Drinker VARCHAR(50), FavoriteWine VARCHAR(50));
INSERT INTO #PreferredDrinks VALUES ('Brent Ozar', 'Cliff Lede');
INSERT INTO #PreferredDrinks VALUES ('Richie Rump', 'Cliff Lede   ');       /* Has 3 spaces */
INSERT INTO #PreferredDrinks VALUES ('Tara Kizer', 'Cliff Lede        ');   /* Has the exact number of spaces */
INSERT INTO #PreferredDrinks VALUES ('Erik Darling', 'Manischewitz');

SELECT *
  FROM #Wines w
  INNER JOIN #PreferredDrinks p ON w.Winery = p.FavoriteWine;

CREATE TABLE #PreferredDrinks (Drinker VARCHAR(50), FavoriteWine VARCHAR(50));

INSERT INTO #PreferredDrinks VALUES ('Brent Ozar', 'Cliff Lede');

INSERT INTO #PreferredDrinks VALUES ('Richie Rump', 'Cliff Lede '); /* Has 3 spaces */

INSERT INTO #PreferredDrinks VALUES ('Tara Kizer', 'Cliff Lede '); /* Has the exact number of spaces */

INSERT INTO #PreferredDrinks VALUES ('Erik Darling', 'Manischewitz');

SELECT *

FROM #Wines w

INNER JOIN #PreferredDrinks p ON w.Winery = p.FavoriteWine;

All of the joins work regardless of the trailing space count:

This behavior is documented in KB 316626. Turns out the K in KB really does stand for knowledge.

Does a TempDB spill mean statistics are out of date?

Last Updated April 11, 2017

Brent Ozar

Execution Plans

When you see a yellow bang in an execution plan, and you hover over it to discover that SQL Server spilled out to TempDB, your first reaction is to wail in pain and rend your clothes.

After that, though, it seems like people always ask the same question:

“Why doesn’t updating stats fix this?”

In the example here, I’m using Cade Roux’s rank-and-percentile query against the Stack Overflow data dump. It’s not that statistics are out of date – look at the estimated number of rows versus the actual number of rows.

They’re identical. The stats are fine.

And no matter how many times I update statistics, I’ll still get a ~400MB spill to disk.

I’m not using a small server, either: my virtual machine has 32GB RAM, and I’ve allocated 28GB of that to SQL Server. There are no other queries running at the same time – it’s just one lonely query, spilling to disk for the lack of 400MB RAM.

When I right-click on the SELECT operator and click Properties, I get the fancy properties window in SSMS, which shows me more details about the query’s memory grants:

Note the rigorous devotion to alphabetical order in the properties window. It’s up to you, dear reader, to figure out what you’re supposed to read, in which order. Here, I’ll give you a peek at my decoder ring:

DesiredMemory – look here first. It’s how much memory SQL Server wanted.
RequestedMemory – sometimes, this is less than desired. (I’d really like a Porsche 911R, but I know better than to ask my wife for the budget to buy one.)
GrantedMemory – how much I actually got.

SQL Server wasn’t off by too much: this operator only needed 7,643 more 8KB pages, around 60MB. Close – but no cigar.

Fixing it with the claw hammer: MIN_GRANT_PERCENT

Starting with SQL Server 2012 SP3, 2014 SP2, and 2016, I have a nifty new query option:

OPTION (min_grant_percent = 50)

1	OPTION (min_grant_percent = 50)

That brains SQL Server into giving me all the memory I need, and then some. The spill is gone from the execution plan, and my memory grants are way higher:

But this has a terrible drawback: I’m claiming memory that no one else can use while my query runs. And how do I know if I even got that grant percent right, anyway? It’ll change over time as my data changes.

Fixing it with the scalpel: CREATE INDEX

Whenever you’re tuning queries, look at the big picture: if the data was already sorted in the order you needed, you wouldn’t be having this problem with the sort operator. And SQL Server’s trying to give you a hint in the form of a missing index recommendation:

That impact number might seem low at just 14, but remember – that’s a suggestion SQL Server came up with before it even executed the query. Before the query ran, it didn’t even know about the TempDB spill, so the impact’s going to be even larger.

Can you fix every spill? Of course not – sometimes, you’re dealing with data that’s larger than the physical box’s memory. But even when you can fix it, just make sure you’re fixing it with the right tool.

Free Webcast Next Tuesday: How to Triage SQL Server Emergencies

Last Updated April 11, 2017

Brent Ozar

Processes and Practices

I’m speaking at the PASS DBA Fundamentals Virtual Chapter next Tuesday! Here’s the topic:

When the phone rings, you need a repeatable process to discern the symptoms, root cause, what will happen if you don’t take action, and based on that, what actions you should take. You’ll see Brent step through several emergency scenarios live, learn what tools he uses, and how he uses them. Brent Ozar loves to make SQL Server faster and more reliable. He created sp_Blitz® and sp_BlitzFirst®, and he loves sharing knowledge at BrentOzar.com.

Fun With Logging Dynamic SQL

Last Updated April 7, 2017

Erik Darling

T-SQL

While working on a demo

I thought this might make a fun aside to share on the blog, because working with dynamic SQL can be challenging. Especially if you don’t have a monitoring tool or application profiling to figure out who ran what and when, most of the time you’ll have no idea what went wrong.

For example, the dynamic SQL that my query produces (sometimes) looks like this:

SELECT 		
  u.DisplayName, 
  SUM(CONVERT(BIGINT, u.UpVotes)) AS [UpVotes], 
  SUM(CONVERT(BIGINT, u.DownVotes)) AS [DownVotes], 
  SUM(CONVERT(BIGINT, u.Reputation)) AS [Reputation] ,				
  SUM(CONVERT(BIGINT, p.CommentCount)) AS [TotalComments], 
  SUM(CONVERT(BIGINT, p.Score)) AS TotalScore ,		
  SUM(CONVERT(BIGINT, c.Score)) AS [CommentScore] 
FROM Users AS u  
JOIN Posts AS p ON p.OwnerUserId = u.Id 
JOIN Comments AS c ON c.UserId = u.Id  
                   AND c.PostId = p.Id 
WHERE 1 = 1 
AND u.Id = @i_UserId 
AND p.PostTypeId = @i_PostTypeId 
GROUP BY u.DisplayName  
ORDER BY SUM(CONVERT(BIGINT, u.Reputation)) DESC

SELECT

u.DisplayName,

SUM(CONVERT(BIGINT, u.UpVotes)) AS [UpVotes],

SUM(CONVERT(BIGINT, u.DownVotes)) AS [DownVotes],

SUM(CONVERT(BIGINT, u.Reputation)) AS [Reputation] ,

SUM(CONVERT(BIGINT, p.CommentCount)) AS [TotalComments],

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

SUM(CONVERT(BIGINT, c.Score)) AS [CommentScore]

FROM Users AS u

JOIN Posts AS p ON p.OwnerUserId = u.Id

JOIN Comments AS c ON c.UserId = u.Id

AND c.PostId = p.Id

WHERE 1 = 1

AND u.Id = @i_UserId

AND p.PostTypeId = @i_PostTypeId

GROUP BY u.DisplayName

ORDER BY SUM(CONVERT(BIGINT, u.Reputation)) DESC

Notice those two variables in the WHERE clause? Part of how using parameterized dynamic SQL protects you is by not just sticking whatever stinky nonsense users submit directly in to your dynamic SQL block. That’s also what makes it challenging to troubleshoot. Sure, you can head to the plan cache, but you may not find anything there.

Logging Tables

If your company is too cheap to get you monitoring tools, well, that’s another discussion. If you want to figure out why dynamic queries are slow anyway, here’s one option. Without showing you all of what my dynamic SQL does, I’ll show you how the logging works. It starts with a table!

CREATE TABLE dbo.Logger
(
  Id BIGINT IDENTITY(1, 1),
  RunHash UNIQUEIDENTIFIER,
  UserName NVARCHAR(128),
  StartTime DATETIME2,
  EndTime DATETIME2,
  RunTimeSeconds AS DATEDIFF(SECOND, StartTime, EndTime),
  DynamicSQL NVARCHAR(MAX)
);

CREATE TABLE dbo.Logger

(

Id BIGINT IDENTITY(1, 1),

RunHash UNIQUEIDENTIFIER,

UserName NVARCHAR(128),

StartTime DATETIME2,

EndTime DATETIME2,

RunTimeSeconds AS DATEDIFF(SECOND, StartTime, EndTime),

DynamicSQL NVARCHAR(MAX)

);

You can add in more stuff if you want, this is just the bare minimum I’d collect.

Now, in the stored procedure, you can do something like this.

DECLARE @RunHash UNIQUEIDENTIFIER = NEWID()

...Whole Mess O' Stuff...

INSERT dbo.Logger ( RunHash, UserName, StartTime, EndTime, DynamicSQL )
        VALUES(@RunHash, SUSER_SNAME(), SYSDATETIME(), NULL, @Command)

EXEC sys.sp_executesql 'Your lousy query'

UPDATE dbo.Logger 
SET EndTime = SYSDATETIME()
WHERE RunHash = @RunHash

DECLARE @RunHash UNIQUEIDENTIFIER = NEWID()

...Whole Mess O' Stuff...

INSERT dbo.Logger ( RunHash, UserName, StartTime, EndTime, DynamicSQL )

VALUES(@RunHash, SUSER_SNAME(), SYSDATETIME(), NULL, @Command)

EXEC sys.sp_executesql 'Your lousy query'

UPDATE dbo.Logger

SET EndTime = SYSDATETIME()

WHERE RunHash = @RunHash

But all that will log is your dynamic SQL with the variables. What I prefer to do is spin up a placeholder that I can replace values in without messing with the dynamic SQL to be executed. Since this command will never be executed, I can use REPLACE to figure out what my values are.

DECLARE @PrettyPrint NVARCHAR(MAX) = N''
DECLARE @RunHash UNIQUEIDENTIFIER = NEWID()

SELECT @PrettyPrint = REPLACE(
            REPLACE(
              REPLACE(@Command, 
                '@i_PostTypeId', ISNULL(@PostTypeId, N'')
                  ), '@i_UserId', ISNULL(@UserId, N'')
                    ), '@i_Top', ISNULL(@Top, N'')
              )

INSERT dbo.Logger ( RunHash, UserName, StartTime, EndTime, DynamicSQL )
        VALUES(@RunHash, SUSER_SNAME(), SYSDATETIME(), NULL, @PrettyPrint)

UPDATE dbo.Logger 
SET EndTime = SYSDATETIME()
WHERE RunHash = @RunHash

DECLARE @PrettyPrint NVARCHAR(MAX) = N''

DECLARE @RunHash UNIQUEIDENTIFIER = NEWID()

SELECT @PrettyPrint = REPLACE(

REPLACE(

REPLACE(@Command,

'@i_PostTypeId', ISNULL(@PostTypeId, N'')

), '@i_UserId', ISNULL(@UserId, N'')

), '@i_Top', ISNULL(@Top, N'')

)

INSERT dbo.Logger ( RunHash, UserName, StartTime, EndTime, DynamicSQL )

VALUES(@RunHash, SUSER_SNAME(), SYSDATETIME(), NULL, @PrettyPrint)

UPDATE dbo.Logger

SET EndTime = SYSDATETIME()

WHERE RunHash = @RunHash

Logjammin’

I double logged my stored proc calls so you can get a better idea of what happens. Here’s what my logging table looks like:

You can already see one spot where an optional @Top was substituted with the actual number “the user” asked for. Don’t give me any application security guff here, I log in as sa because I am the app and the app is me.

Zooming in on where the other variables live in the WHERE clause, now we can see which ones were called.

This can pose some security risk. If some malicious person were to realize that you’re logging these calls, they could change your code to execute the printable version instead. If security is a concern, you may want to offload the logging code to other stored procedures.

If you want to really ball out on this one, you could even frame the replaced values in special delimiters like ‘||’ and then parse those out as well. But I’ll leave that as an exercise for the reader.

That’s you.

Thanks for reading!

SQL Server 2017 Release Date: May 31?

Last Updated May 12, 2017

Brent Ozar

SQL Server 2017

At the SQLbits conference in the UK, Victoria Holt sat in on a session about the State of the SQL Nation. She writes:

“SQL Server vNext will be launched this year with the UK launch 31 May 2017.”

Keep in mind that “launch” is a marketing term, very different from “release.” (Even “release” is tricky because the code can be released to manufacturing without being available to download publicly.)

Victoria’s post also lays out what’s new in vNext. I don’t think it’s a list that’ll get most conventional DBAs excited, but Linux & Docker support had to have been challenging enough for Microsoft already. (Example: last month’s CTP 1.4 release fixed GETDATE() and introduced VDI support for backup software.)

A May marketing launch would tie in nicely with PASS’s delayed call for Summit speakers, too. It gives the community the best chance to build sessions that will be highly relevant when October rolls around. (Granted, this also means speakers will be building session content for things they haven’t actually done in real-life customer implementations, but that’s how it always is with brand new technology – folks have to kick it around in the lab and then guess how it’ll work in real life. It is what it is.)

Update April 19 – Microsoft’s marketing material is now officially referring to it as SQL Server 2017:

[Video] Office Hours 2017/4/5 (With Transcriptions)

Last Updated April 10, 2017

Brent Ozar Unlimited Team

SQL Server, Videos

This week, Erik, Tara, and Richie discuss altered tables and update statements, parameter sniffing issues, their issues with Azure, a resource for column store indexes, AlwaysON availability groups, killing sleeping connections, and fill factor value for indexes.

Here’s the video on YouTube:

Office Hours Webcast - 2017/04/05

Watch this video on YouTube

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

Enjoy the Podcast?

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

Office Hours Webcast – 2017-04-05

What are the pros and cons of Platform-as-a-Service?

Erik Darling: Richie, I think this is one that you’ll like. Platform as a service, pros and cons, because you like Cloud stuff more than I do. You like things that happen.

Richie Rump: I do?

Erik Darling: In the cloud, a lot more than I do. Like, things that happen in the Cloud. I’m annoyed with the Cloud constantly.

Richie Rump: I do, and if you’re coming from on-prem to Cloud, you probably will be annoyed as well. There’s a lot of things – I haven’t really played a ton with Azure SQL but you know, just a little bit that I have played with, there’s a lot of stuff like why isn’t that there and why isn’t that there and why isn’t that there.

Erik Darling: There’s a lot of our stored procs that just won’t work up there because there are you know, features in certain systems – we can’t use sp_BlitzCache up there because you can’t use a global temp table. I don’t know what you expect me to tell you but…

Richie Rump: But the same thing is true when you code for the Cloud as well. The way that you would program is significantly different if I were to just write a typical server application, so there’s plus and minuses for both sides. I don’t have everything the top of my head other than you’re going to need to try it for yourself. What is going – I thought it was actually Brent Ozar coming on. This is better. But this is better. I was getting scared there for a second. But PaaS – it is – you’re going to have to try it out along with your requirements and see if it’ll work for your environment and your application that you’re building.

Erik Darling: PaaS changes a lot. Like, Microsoft just pulls things in and takes things out. CLR got yanked out very unceremoniously recently because…

Richie Rump: And we’re going to put it back, and nothing.

Erik Darling: Yes, they discovered that there were security issues with letting people use CLR up in Azure then they were just like, oh wait, you can’t do that, all this stuff you want is gone. All that CLR just got pulled out and there wasn’t any like, case by case, like oh you can use it in … you’re special, like it was just like no CLR, figure it out. So pros and cons – con is you control nothing. You just have to…

Richie Rump: Yes, I could definitely see them say, hey you know what, the benefits for this feature really doesn’t outweigh the security concerns that we have and really, who’s complaining about I don’t have CLR in SQL Azure, so we’re probably just not going to put any resources on fixing this thing. It’s just – we’ll just rip it and we’ll just tell people it’ll be back at some point and see who complains the loudest and frankly I don’t see hardly anybody really complaining that I don’t have the .NET CLR inside of SQL Azure so now that we have something like Lambda functions, I can you know – once they start getting better integration with Azure SQL and Lambda functions, now I could write Python or I could write f# or I could write c# or JavaScript or whatever and have it interact with my SQL. So hey, functional languages man, it’s…

Erik Darling: Richie said f#. I want to hit the bell on that every time Richie says f#.

Richie Rump: Hey, functional languages. It’s all the new hotness man, all the cool kids are doing all the functional languages

Erik Darling: Never heard you say f# before like, without it being derogatory.

Richie Rump: I’m not going to talk bad about f#. The folks who know how to wield that broad sword are amazing. You can do a lot of cool stuff there.

Erik Darling: Interesting.

Richie Rump: Yes.

What resources get used when you ALTER TABLE?

Erik Darling: Alright, let’s see here. Ron, I’m sorry, I don’t get that question. Tara, do you want to – there’s a question there from a fellow named Ron. Make more sense to you, it didn’t make sense – thing, kind of went by me. J. H. does ask though, “What resources get used mostly when issuing ALTER TABLE and update statements, CPU desk memory?” Jeez.

What resources get used most? Well, certain ALTER TABLE things don’t use all that much resource. Like, if you alter a table and you add a column that’s nullable and you don’t have a default in there, it’s pretty quick. It doesn’t really use much resource. For updates, depends on the query. If you have a totally unindexed query and you let that run, it’s going to use a whole lot of everything. So then it has to, you know, first run that query, read a bunch of pages, figure out a bunch of stuff out, then write all that stuff and then you know, internally SQL figures out if it can just do an in-place update or if it has to do an insert and delete, and all this other stuff. So I mean, you’re looking at a bunch of resources depending on how well optimized the query and indexes are to begin with. If you’re updating a column that is in 50 indexes, you’re going to do a lot more work for like, you know, writing and what not than if you update a column that’s in five indexes. So that question’s a little weird, but alright. I see why you didn’t want to put that on Stack Exchange, you would have gotten down-voted into oblivion.

Richie Rump: Oh no, but why would anybody do that? An honest question on Stack Exchange?

Erik Darling: Well, honest is different from good. Honest and good are two totally different things.

Richie Rump: I see how you backed away very quickly from the In-Memory OLTP question. That was pretty amazing there.

Erik Darling: Well, because the person left. I’m not going to ask the question If someone left.

Richie Rump: They leave but they ask a question saying, hey you know, I’m going to actually watch this later, maybe.

Erik Darling: Maybe, but they didn’t say, hey I got to go, sorry. I just got a someone’s left.

Richie Rump: And then again, In-Memory OLTP, just – it makes them freak out, which is pretty amazing.

Erik Darling: Yes, so like you know, I was honest with my wife about the fact that I spent $400 on a bottle of Yamazaki 18, that was not good. So there’s like honest and good. Two different things. I didn’t mean to insult your question, I just wanted to draw like my boundaries between honest and good.

Richie Rump: I’m so sorry, that you spent that much money on the bottle. I’m sorry for that.

Erik Darling: Oh no, it’s one of those, I put it in my little bar cart and I just say that I have it. I don’t let anyone touch it. My precious, my precious – just one of those little White whale holy grail things that you get. Of course I say that about a lot of different bottles of scotch and they end up gone pretty quick. What can you do?

Why is parameter sniffing an issue in only some of my procs?

Erik Darling: Let’s see, we have a parameter sniffing issue in some stored procedures but not an issue in other stored procedures. Any ideas? Write the stored procedures where you have the parameter sniffing problems, just like the ones where you don’t have them and then they’ll go away. That sound about right, Tara?

Tara Kizer: No.

Erik Darling: No? No, you shouldn’t? What would you do if some of your stored procedures had parameter sniffing?

Tara Kizer: So, I would see what the problem is here, compare the execution plans is it an optimization and is it optimizing for a value that isn’t representative of the other values. I just worked out a client this week that is having sort of a parameter sniffing issue between two servers and it just – one server’s locked in a clustered index scan, the other one is locked in a non-clustered index seek and you know, using index hints has worked around the problem but doesn’t necessarily solve – it doesn’t tell us what the root cause is, so anyway, to answer the guy’s question, I’d be comparing execution plans and seeing what I could do to get it to use the better execution plan for the rest of the grammar values.

Erik Darling: Sounds like a good idea. At what point would you resort to something crazy like plan guides?

Tara Kizer: When a Microsoft engineer’s on sight and looking at the system request. That’s when I started using plan guides. It really was onsite for an update for ESCO, because it was such a major upgrade we decided to pay someone to watch me. Plan guide or index hints or any of those, option for – it doesn’t matter what you do. Plan guide is just an easy – I shouldn’t say it’s easy. It’s a hard opter to put in place because it has so many requirements that doing the optimize four trick or index hint trick in the actual query can be easier. Plan guide is just another object that has those options in it.

Erik Darling: Sounds good.

Richie Rump: Yes, sometimes I just, you know, depending on the query, just have a recompile every time and just let it do it and automatically it gets better, and if I need to put more effort into that and if that’s not the solution then so be it, but I think sometimes especially when I’m doing the time performance tuning, we need to have that exit goal – okay, what point is good enough, because I could spend another week on this problem, but sometimes the fastest, easiest way is maybe not the best way, but it’s the way that meets the requirement to get out of the performance tuning.

Erik Darling: Yes, you know what, big thing for me with the parameter sniffing is always making sure that my indexes are lined up so that SQL doesn’t have an option between too many different plans. Tara ran into a weird one where it was the same everything and the query on one server chose a totally different index and everything from a query on another server. But for me, if it’s just happening on one server then I’m most concerned that like you know, it’s like choosing a weird key lookup plan when it should be choosing like a different plan. You got to get rid of that key lookup to get the same shape plan in place.

What’s your experience with Azure?

Erik Darling: There’s a long question there from Ian. I think that’s a Tara question, so if you want to read that and answer that in the background, we’ll get to something else. J has a follow up question about Azure. “Do you have similar experience of the features that…” – no, I don’t touch Azure. I don’t want to touch Azure. I don’t want to be around Azure. You know, I’ll take Amazon RDS and have like a complete list of stuff that doesn’t work and isn’t compatible that’s pretty stable over time.

Richie Rump: Yes, I think that’s what gets people especially in the Microsoft community kind of confused about us, is that you know, we’re pretty agnostic about the Cloud. I do a lot of work in AWS, we obviously wrote the white papers for Google Cloud, SQL server in there, but Azure – I dabble in Azure but by far, I use AWS more than the other two. So we could give you a big general idea but when we get to some of the specifics, we haven’t really dived into some of those, probably outside of Google Cloud and AWS, which I think surprises some of our more Microsoft-y type followers.

Erik Darling: Yes, Azure has kind of been weird from the get go and we try to get our labs set up in Azure the way we have it set up in AWS a long time back and after three days – and I think Jeremiah, who’s smart doing stuff in Azure, he said I don’t know, no, I throw my hands up. So there’s that.

Richie Rump: It feels like Azure has this kind of enterprise-y security layer that they’ve kind of put in their Cloud where AWS doesn’t really have that.

Erik Darling: There’s a portal and a beta portal and a preview portal and then PowerShell. It’s like, I don’t know what to do.

Richie Rump: Yes, when I’m messing with Azure, I’m always you know, messaging our Microsoft community guys like how do I do this, how do I do that? With AWS, you do this, but how do I do it over there and typically, AWS is a little simpler to get into and out of as opposed to Azure, there’s layers upon layers that you kind of have to go through. Yes, it’s peculiar. I would definitely say it’s definitely peculiar, but not to say that it’s bad. It’s just additional steps to kind of have to go through in general, to get stuff done.

Erik Darling: I would agree.

Any good references on columnstore indexes?

Erik Darling: Gregory asks if we have any good references or thoughts on column store indexes. I sent a link out into chat through a website from a fellow name Niko Neugebauer, I believe that’s how you pronounce it, I couldn’t be a 100% on that, but it’s Nikoport.com and he’s been writing for years and years about column store and he has more good thoughts on it than I do. As far as order of columns, the number of columns, it doesn’t matter in column store, the way it does with row store indexes. For non-clustered you know, there are obviously some limits on column length at this point. I’m not sure if you can have max column types in there just yet. I think I read something – no, that was hekaton. Yes, because when I go to column store to find my stack overflow database, I always have to ditch the body column from the post table, so it does have some limitations but as far as the number of and the order of – and you can’t column store index a computed column either; another funny thing. So there are limitations, I would let the limitations guide me more than some arbitrary number though.

How should I train someone on Always On Availability Groups?

Erik Darling: Tara did you want to answer that long question about the always on stuff?

Tara Kizer: From Ian?

Erik Darling: Yes.

Tara Kizer: Sure. So Ian is saying he needs a – only DBA – the company is large enough to need another DBA and ASCII training, an emergency backup for our main instance, they’ve got AlwaysOn, Analysis Services Integration Services, Reporting Services – they’ve got everything apparently. Trainee is knowledgeable on SQL server but it’s another office, it’ll be remote and it’s a second language, so he’s asking how to train this person. Ian is asking the question you know, that he has been doing it for eight years and hasn’t documented it. It sounds exactly like what I’ve had to do in the past. You know, I’m not a documenter, that’s for sure, everything I store is in my head and people need to learn that they’re going to have to ask me because I don’t write stuff down. So I’ve had to train people in the past. One time I was going on maternity leave for three months and we hired a consultant to replace me while I was gone so I had to get that person up to speed on – it was like five years worth of knowledge of troubleshooting performance issues in production and that’s hard to do because it’s so specific to applications at the time. So anyways, what worked well for us is shadowing. I realize that this other person is remote but you can do a WebEx session or go to webinar, Skype, whatever you’re – Google hangouts or whatever and share your screen, talk through everything that you’re doing for one, two hours per day. Don’t do a whole day because that’s just going to burn everybody out, but you know, share your screen and talk with the person. You know, what are you doing day to day, because that’s really what they need to know is day to day, and also talk about the things that are – come up as emergencies. What are the types of things and have that person be responsible for documenting going forward and that’ll be part of their training, you know, writing it down.

Erik Darling: Sounds good to me.

Can I add an AG listener online?

Erik Darling: Suzie asks if she – well, I’m going to assume Suzie is a she. Suzie is quite a gal’s name. “Can I create a listener for an existing AlwaysOn availability group on the day or do I need to wait for after hours? Can it cause any issues?”

Tara Kizer: I mean, you certainly can. Will it cause any issues, probably this one time it will cause an issue, so you know, I would run the command later in the day. But you should be able to add a listener without an issue, they’re just – you know, weird things happen when you’re working in production, you know, you’re rolling the dice.

Richie Rump: Yes, I mean, I would always opt to do stuff like that, you know, later or during a maintenance window but creating a listener just seems like one of those sort of like nebulous things where it’s like, okay, I’ve got to create this and then nothing’s actually going to use it until I tell it to…

Tara Kizer: In theory that’s how things should work but I don’t know about everyone else, but I have bad luck in production.

Erik Darling: So Suzie, the verdict is it’ll probably be okay but you probably also want to wait.

Do you have a server health checklist?

Erik Darling: James asks, “Do you have a health server checklist template?” Now James, you’ve been in Office Hours a lot. You’ve been to our training, you’ve been to our online training. You should have our first responder kit with the set up checklist in it, James. I know you have the first responder kit. I would definitely want to download, if you don’t have it handy, download that and look for the set up checklist. It’s a pdf, it’s not really a template, but you can go through and, you know, check things off as you go, make sure that it’s all done. Other than that you know, our healthy server template is running sp_Blitz. Making sure that nothing crazy comes back in there, so those are our versions of healthy server checklist templates.

Do sleeping connections cause negative impacts?

Erik Darling: J. H. asks, well, says, “I have a bunch of sleeping connections not doing anything for days. Are these connections taking up resources or are there any negative impacts? Is it good to kill these connections on a daily basis? What reasons to kill them or not to kill them?” Well, off the top of my head I could give you a good reason not to. Now, I’m going to pop a long command in there for sp_WhoIsActive that’s going to give you a bunch of information about what a query has been up to. If you run that, and you see that one of those sleeping transactions has a bunch of tran’ log writes, then it’s also blocking – you might also see a blocking – if you see it has a bunch of transaction log writes, just know that if you kill it, it may have to roll back whatever transaction log stuff it wrote out. So a good reason not to just kill them is you may have an unforeseen rollback. A good reason to kill them, they might be blocking stuff.

Tara Kizer: If these sleeping connections don’t have an open transaction and they aren’t blocking anything then I don’t bother killing them. They aren’t using many resources, or using a teeny, teeny, teeny bit of resources. It’s like 8K of memory or something like that, you likely have sleeping connections due to connection pulling so applications will go out and connect to a server and say the app admin has configured it to go ahead and do 200 connections, but maybe you have only 50 connections are needed. Those 200 connections are still going to be there and if you go ahead and kill them, the app’s just going to recreate those 150 connections again. So take a look at your system. What are those sleeping connections doing and do you have connection pooling in place? Talk to your web ops people and see what number they’ve configured for the pool.

Richie Rump: There’s a task I need to set up. Pooling was on my list, I completely forgot about it.

Should I use fill factor 100% or something else?

Erik Darling: Let’s see here. Oh, there was a fun question. Where did that question go? There was a fun question that I wanted to ask.

Tara Kizer: I’ll let you find it. How about Renee’s question that just came in? “Would you recommend to use a fill factor of 100% for indexes or would you go with a different value?” I like that question.

Richie Rump: I would absolutely go with zero. I think zero is the way you need to go.

Tara Kizer: Zero and 100 are equivalent when we’re talking about fill factor, so he is still right. Default to 100%. Don’t change it at the instance level. You need to take it by case by case basis. If you’re talking about GUIDs and you’re trying to avoid page splits then maybe reduce your fill factor. The recommendation is to keep your fill factor 100% except when page splits are a problem, like is the case with GUIDs. But I had a client a few weeks ago that said that they had lowered the fill factor from 100% down to 75, 80. There was something like that said 78, or some weird number and it was because what they had encountered page splits years ago, and that was causing problems and I was like, is it causing problems now because your reads probably are slower with these lower fill factors. It’s just not something they realized, that read performance goes down when you lower your fill factor down. Yes, it helps out with your inserts to avoid page splits but usually, select performance is way more critical than the insert updates and deletes because the selects are what users are using generally.

Richie Rump: I think that’s kind of a tough thing in general to try and figure out is at what point do I stop optimizing prematurely, right? What is going to be a good median to say hey, this is a good performance tuning whatever I do to start off with versus this is a problem now and I need to go and fix it.

Erik Darling: The stupid thing about fill factor for me anyway, is – I mean, it’s like – one of the first things is like introducing like enforced fragmentation into your indexes, you’re leaving a certain amount of empty space in there that SQL is going to have to read and store on disk and in memory. The second thing about fill factor for me is that it’s not maintained on insert update and delete. It’s only enforced when you rebuild your index. So like, in between rebuilds, your index will probably do better on page splits but unless you rebuild your index fairly frequently, all those new pages, all those split pages, they’re just going to fill up to 100% and split again, so it’s like one of those like well, I chose a 70% fill factor for my GUIDs, well I still have lots of page splits because all these new pages keep getting split. So like, you would have to be out of your mind to constantly rebuild an index to try and put that back in place. So I usually just leave fill factor somewhere between 90 and 100 and just never bother with it again. I prefer 100 because then I don’t have to change anything.

Richie Rump: Yes, five is usually a bad idea.

Erik Darling: Five is terrible.

Richie Rump: So if you’re considering that, don’t.

Erik Darling: Don’t use a fill factor of five…

Unless you’re doing a fragmentation demo, then it might be okay.

Richie Rump: Unless you want to call us later and then we’ll explain to you why that’s a bad idea.

Erik Darling: Yes. I can do that, in like two words.

How do I talk people out of including every field in an index?

Erik Darling: Alright, I found a question that I thought was interesting. It was from Ben. “What’s the best way to convince someone that a new index with every field as an include is not necessarily a good thing?”

Tara Kizer: I know that you and Brent will say that when you have it – when we see a client that has a – maybe the key is fine but then they have like, 48 includes, to go ahead and chop off the includes and check out the performance of the query, let it do a key lookup to the clustered index or also test it with the covering index with all those includes. Now, of course the key lookup version is going to be slower but it is the perfect difference between the two…

Erik Darling: Well, it comes down to the query. If it’s like on a single primary lookup or something, then I don’t care if it does one key lookup for 40 columns, go ahead. It’s when there’s like thousands – hundreds of thousands of them, I’m like, no, not the key lookups.

Tara Kizer: But when you’re looking at the missing indexes and there’s a missing index with say, 48 includes, do you go ahead and recommend that – when it has a really high benefit per day, do you go ahead and recommend that index or do you chop off the includes for them?

Erik Darling: I vary a little bit on that. If I can track down the query that’s asking for it, and I look at this query and I say do you need this query as it lives and breathes and they say yes, we cannot change this query, we need this query as it lives and breathes then I say okay, then you need this index to make this query better. You can try it with just a key column first and see how that does or you can go for the whole kit and caboodle and see – you know, take the hit on having 48 includes in there. But if I can’t, then – if I can’t find that query and ask them those questions, then I’ll just give them the choice of you know, key columns plus includes and then just monitor how it gets used in BlitzIndex.

Richie Rump: When I start off with an index, it’s usually the key columns that I need and then the actual clustered key inside the includes and then that’s it. I put the clustering key in there just in case someone changes that later. It’s not going to do anything, right? And then as we start going on and hey, there’s a performance problem here or there, that’s when I’ll start throwing includes on top of that and not just let’s throw everything in there so I’ve gotten this really big index now for no reason. I’ve got no queries running up against it. It’s just taking up space, it’s on disk, it’s just taking up space in memory. Why is all this stuff there? So I don’t put included in there until I need it. That’s it.

Erik Darling: Alright, well we are – we have hit the 12:46 mark. I don’t see any other questions pouring in that I can answer so I think we should call it on Office Hours. What do you guys say?

Richie Rump: Yes, I’m good.

Erik Darling: Alright, cool. Take care everyone, thanks for showing up. We will see you next week. Goodbye.

First Responder Kit Release: Now With 100% More Working Code Than The Irish Space Program

Last Updated April 7, 2017

Erik Darling

First Responder Kit, SQL Server

Every April I think about this girl I grew up with named April who smelled like Cheez-Wiz.

She died tragically in a home for the blind on Taco Tuesday.

Special thanks to @jeffrosenberg , @mrthomsmith , @rwhoward for contributing this month. Your complimentary oxygen is now available. The rest of you can go download the First Responder Kit.

sp_Blitz Improvements

#762 This big dummy would crash on certain versions of 2008R2 when checking for memory dumps, ironically causing a memory dump. So hey, there’s that. Now we don’t do that for those versions. You’re welcome.
#824 Some files are bigger than others, and some sizes do matter. For instance, we only care if your tempdb files are more than 1 GB apart in size. Now you know, and knowing is half the burden,

sp_BlitzCache Improvements

#770 We now warn you if you use a Spatial index, since that probably means you accidentally created some Spatial data in SQL Server. I mean, who does that?
#796 sp_BlitzCache will now warn you if your code creates or drops tables and indexes while running. Yeah, who knew?
#801 We do our best to bubble up information from statements in stored procedures to their calling code. Because that’s the right thing to do. Why should those lazy statements get all the attention? They’re not special. Now we do that with memory grants, where available.
#808 Code is weird. Code that calls other code is weirder. Code that gets blocked is double weird, like a trailer full of twin sized mattresses. We now warn you if low cost queries have high CPU, or if long running queries have low CPU. Surely those are signs of trouble.
#816 Ah, zero. Is there anything you can’t do? Oh, be divided by. Yeah. Let’s not do that.

sp_BlitzFirst Improvements

#819 Biggie Smalls for mayor, the rap slayer, Hekaton say your prayers. Also get notified if any activity is happening by BlitzFirst.
#823 Funny story: no one ever found this error, because no one ever ran sp_BlitzFirst when they had a query being blocked for more than 30 seconds, which are EXACTLY THE KIND OF PEOPLE WHO SHOULD BE RUNNING sp_BlitzFirst, especially since this bug is fixed.

sp_BlitzIndex Improvements

#772 Mode 2 is now capable of being output to a remote server. Not available for other modes, or Depeche Mode. Thanks, @rwhoward!
#777 Cleanliness is next to Godliness, so I cleaned up this code to find your blasphemous computed columns based on scalar functions so you may be doomed for all eternity.
#778 Unused indexes are, like, a bummer, man. But they’re a bigger bummer when they have a lot of writes. Thanks to @amtwo for the totally badical suggestion. Air guitar and whatnot.
#779 I keep hearing about this SQL Server 2016 thing. I hope someday someone uses it, so they can use temporal tables, and marvel at the information that so_BlitzIndex provides them.
#711 If you had two copies of a database (like if you backed it up, then restored it under a different name), you might have been seeing double. Now, it’s not sp_BlitzIndex’s fault – it’s just due to your trifocals.
#827 Brent did a big round of performance tuning, and it’s now 3x-20x faster. That probably means he introduced a ton of bugs. Hey, It Just Runs Faster!®

sp_BlitzWho Improvements

#822 Sleeping transactions can violate the NAP by blocking other queries with their snoring so we can call our gold level sp_BlitzWho to arrest them. or just find them. Why weren’t we going this before? That was silly.

sp_DatabaseRestore Improvements

#765 We have the most elegant, luxurious storage for our LSNs. Really. Ask anyone. They’re NUMERIC(25,0).
#766 Similarly, we have the most elegant and luxurious storage for our file paths: NVARCHAR(MAX).
#775 Being compatible is important. Being backwards compatible means you have more bugs to fix. But hey, now you can find bugs if you’re on 2008 or later.
These were all done by @jeffrosenberg! A threefer!
#787 It’s not a stored procedure unless it starts with sp_ — thanks @mrthomsmith !

You can download the updated FirstResponderKit.zip here.

Announcing Our Summer/Fall Training Schedule and Instant Video Access

Last Updated April 5, 2017

Brent Ozar

Company News

I had a great time in San Diego over the last couple of weeks teaching classes. I’d love to be able to have every training class where we can do breakfast & lunch outdoors!

Performance Tuning Class, San Diego, March 2017

Buuut, we gotta move classes around, so here’s our upcoming lineup:

Performance Tuning (4 days, $3,995) – You’re a developer or DBA who needs to speed up a database server that you don’t fully understand – but that’s about to change in four days of learning and fun with Brent.

April 17-20 online (still 2 seats left!)
August 22-25 online
October 24-27 in Chicago
Learn more and register now

The Senior DBA Class (4 days, $3,995) – You’re a SQL Server DBA who is ready to advance to the next level in your career, but you aren’t sure how to fully master your environment and drive the right architectural changes.

April 11-14 online (still 3 seats left!)
August 28-31 online
October 16-19 in Chicago
Learn more and register now

And a new perk: instant video access. As soon as you enroll, you can start watching a recording of your class. Our Instant Replay access has been a huge hit with students – they’ve really loved being able to catch up with things they missed in class. I figured, “Why not let you prepare ahead of time, too?” So now, when you register, you can start streaming the videos right away. No need to wait for class for the learning to start, and you get access to the videos for 1 year (so you can prep before class, and revisit your learning after class.) There’s nothing else like it in the industry.

You can also catch me at upcoming conferences including SQLDay 2017 in Wroclaw, Poland and SQL Intersection in Orlando, Florida. See you around!

Announcing Our New DBA Job Interview Q&A Course.

Last Updated April 5, 2017

Brent Ozar

Interviewing

Whether you’re applying for a DBA job or getting ready to hire a DBA, you probably don’t know what questions to ask to tell if somebody really knows what they’re doing or not – or what answers to look for.

There’s the classic tired question, “Tell me the difference between a clustered and nonclustered index,” but after that, what?

In our new DBA Job Interview Q&A course, you’ll hear:

Heck – let’s just go ahead and get started with the Development DBA Questions module right now for free. (If you can’t see the video below, you’re reading this on an RSS reader or off-site – head over to the blog post.)

Wanna see the rest? It’s part of our DBA Subscription – normally $39/month, but use coupon code Relaunch2017 and get off at $19.50/month.

Announcing Monthly Training Subscriptions and Some Cool Perks.

Last Updated April 11, 2017

Brent Ozar

Company News

We’ve been selling training videos for a few years now, and we’ve figured out that we have two kinds of buyers:

Price-sensitive short-term customers – who are often paying out of their own pocket because their company won’t pay, and they want to learn as much as they can in a short period of time
Long-term enterprise-y customers – who submit their training via an expense report, and they just want all of our training, renewable every year

So to make things easier for everybody, we now offer 3 subscriptions:

DBA $39/mo – with DBA Job Interview Q&A, HA/DR, hardware, storage, virtualization.
Performance Subscription $49/mo – wait stats, indexing, query tuning, table statistics
Enterprise (Annual) – all of the above at a 43% savings.

But because you’ve been around here a while, you know that we launch stuff with a sale – and this one’s a doozy.

Knock 50% off your subscription for life with coupon code Relaunch2017 in April. Not just your first subscription – but 50% off that subscription for the whole life of it! It’s the gift that keeps on giving. Pick your subscription now.

Existing owners are getting a pleasant surprise in their emails today: if your subscription included any DBA class, then you’ve been upgraded to the entire DBA subscription through the life of your membership. Same thing with performance classes. Time to bust open that browser and get to learnin’!

If you’d like to extend your subscription, you can buy an extension this month (or upgrade to the Everything Bundle) and the time will be added on to your existing subscription. Enjoy!

[Video] Office Hours 2017/03/26 (With Transcriptions)

Last Updated April 1, 2017

Brent Ozar Unlimited Team

SQL Server, Videos

This week, Erik and Richie discuss truncating log files, reindexing, using sp_rename, query tuning, columnstore indexes, debugging parameter sniffing issues, query processing internals, and troubleshooting query performance issues.

Here’s the video on YouTube:

Office Hours Webcast - 2017/03/29

Watch this video on YouTube

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

Enjoy the Podcast?

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

Office Hours Webcast – 2017-03-26

If I configure a VM with local SSDs, and the host fails…

Erik Darling: Alright, James F – you have a question about encryption, I’m not the best on that stuff admittedly, I’m not much on security and certificates and what not. I would post that up on dba.stackexchange.com. James F asks another question, though, a follow-up – well, not a follow-up, a completely different question -“There is an article on your site that talks about tempdb on local host SSD with a SAN, how does that work with host failover?” Well, geez, that’s not really for VMs. That’s not a good VM idea. That is a good failover cluster idea. So if you’re on a failover cluster you can use local SSDs and when you know, tempdb fails over and is restarted, it’ll just find the new thing and be happy. With VM hosts is completely weird and different and I wouldn’t do it.

Would you automate log file truncation?

Erik Darling: Let’s see here, “Would you automate log file truncation and update growth for next extent in initial…?” – no. No, leave it alone. If you have to ask, leave it alone. I don’t like truncating log files.

Richie Rump: Why is that?

Erik Darling: Well, because they have stuff to grow again. When they grow, they don’t get the instant file initial – it’s an instant file initialize, you can tell I’m on cold medicine – Initialilazize…. So they have to grow and it takes time because they have to zero out all that space, so when you back up a transaction log, the space inside it is truncated. It’s the worst word that they could have possibly used for it because everyone pictures their transaction log shrinking to a magical size when they say your log file is truncated when you back it up, but that’s not it. So internally, space can be reused after you back up a log file. I don’t like truncating log files because they look – when they grow it can be painful. Leave them alone, and as they’re growing, database activity pauses while they grow because nothing else can get logged in there. It’s like oh hang on, we’ve got to grow the file 10GB, so you’re rebuilding a dumb index. Have fun. Alright.

Which index should I rebuild next?

Erik Darling: J. H asks, “Any good methods of anticipating which index to tend to ahead of time before a weekly re-index job and accommodating their needed spacings?” Jeez, well, what indexes usually get rebuilt? I mean, if you’re using Ola Hallengren that all gets logged to the command log table. If you’re using Minion, assume they have some sort of logging apparatus on there, so I would just look at which ones get rebuilt in their size. I’m not sure where you want me to go with that, if you meant something else please let me know.

Have you had issues with sp_rename?

Erik Darling: Joshua asks, “Have you used sp_rename for tables much and have you had issues using it? Just looking for experience points here.” Well, yes actually. I have a fairly old blog post, it was called the sp_rename follies and I got beat up in the comments about not using the GUI or something. Let me see if I can find it.

Richie Rump: The what? What is that? How do you spell that?

Erik Darling: It’s a French word and that means what’s on your screen, dummy.

Richie Rump: Oh wait, Goonies? That’s what my finger movie back in the day man, you should have said that in the first place, that’s amazing.

Erik Darling: It’s a good movie. So let me dump this into a – good enough for the chat window here for Joshua. So anyway, I had this thing with sp_rename where I would mess up and I would either put too many – I would put a DBO in where I didn’t need to and it would rename the table dbo.dbo, and it was dumb and I couldn’t rename it because something else had a lock on the table all of a sudden. I think it was probably like Intellisense looking at this table like did this dummy really do that – so anyway, that was my funny sp_rename folly, when I used it, I put in an extra DBO and I had a dbo.dbo.dbo.

Richie Rump: Yes, so I think the extent for my sp_rename stuff for tables was back in the day, I mean like, mid-2000s, maybe even earlier…

Erik Darling: Mid-2000s?

Richie Rump: Yes, we were doing like poor man’s replication, you know, just hopping data over because we didn’t want to deal with a lot of replication overhead, so as a reporting system we would just copy it over as a table name and then we would do a rename – sp_rename on the table that was in the database and then we would rename the new table actually to whatever the original table name was and do it that way. So that’s old school way of doing things.

Erik Darling: Yes, that’s almost like the weird ETL Schema switch thing.

Richie Rump: Yes.

Erik Darling: Let’s flop a new table in.

Richie Rump: Yes.

Erik Darling: I’ve always like tricks like that. I think they’re neat, but then I always like expect there to be some horrible drawback. Like I remember like – I think I did like Alter Schema switch or I did sp_rename and I remember like waking up in the middle of the night once thinking, but what if like statistics about the old table are caches? I was just like, but wait, what if this horrible thing happens and like I woke up the next morning after like finally drinking a bottle of whiskey and falling back asleep and I was just like, waiting for that email like whatever you did ruined the server, you are fired.

Richie Rump: So is that like an honest thing with DBAs? Waking up in cold sweat at three o clock in the morning like oh my gosh.

Erik Darling: Yes, like every time I would roll out a new job or a new thing to do, like at my last job where it was a relatively shop and I would do this thing where I would script out BlitzIndex to look for missing indexes because with relativity, you can add missing index requests and they don’t mind. They’re like, cool, you’re using our application in a different way, we respect that. So they let you as a DBA, add into indexes kind of to your heart’s content. You can do all sorts of stuff in there. So I would set up these things that would run sp_BlitzIndex and gather missing index requests from across all of the databases. At the time, BlitzIndex didn’t have to get all databases and it had to be run individually and dumped to a table or something. That was annoying, but I did it, it worked, then I would review the missing index requests and I would set up a script that would go through and add missing indexes. I remember when I was doing that, I was petrified and I was just like, every time that ran I would stay up and watch it and just like shake. Like, please don’t mess, no red text, no red text.

Richie Rump: You were Gloria Gaynor, so first you were afraid, you were petrified.

Erik Darling: I was, and I still am. I still wait to get a phone call about one of those scripts, like, hey man, remember that…

Richie Rump: No, no I don’t, I don’t at all, click…

Erik Darling: Nope, never heard of it, neither has my NDA, never heard of it, goodbye.

Ramblings about unit tests and feedback

Richie Rump: See, that’s the one thing I’m pretty happy what happened in the development space over the past ten years or so, is that now we have become so test focused and the test focus has absolutely been on repeating those tests and being able to write those tests and then run them at any given point, so now all the code we write at least here, we have unit test for and…

Erik Darling: Let’s tamper that statement. All the code that Richie writes has unit tests in stuff. All the code that we write has an AWS lab that may or may not meet our needs and you, the end user of our free scripts, are often a guinea pig for us.

Richie Rump: Oddly enough, that’s true, but we get your feedback and we integrate it into the product, so thank you very much.

Erik Darling: We will fix things based on that.

Richie Rump: Yes.

Erik Darling: I do like getting bug reports and stuff because then I have something to do when I’m bored, other than blog and annoy Richie with blog posts and the word blog.

Richie Rump: Yes, I don’t get bored around here. I don’t know what you’re talking about.

Erik Darling: No, I get bored.

Richie Rump: Yes, I know you get bored, but I don’t get bored. There’s so much stuff to do. I’m like why don’t you just come and help me Erik, and he’s like no, another blog post…

Erik Darling: Well, my problem is I only know Java, so I can’t help you.

Richie Rump: You can’t help anybody. You only know Java.

Erik Darling: I have my doctor Java app and I can write little Java things in there and that’s it. End of it.

Richie Rump: I spent the last 25 years of my career avoiding Java, so there you go.

Erik Darling: It’s not that bad, and it’s gotten better. I mean, 25 years ago, Java today is better than it was 25 years ago. Don’t be silly, Richie.

Richie Rump: Nope.

Erik Darling: Everything gets better – if SQL Server can get better over 25 years…

Richie Rump: Eat your Java beans please.

Erik Darling: I don’t know what that means. It’s probably funny to someone.

If a SQL Server gets more queries, does it get slower?

Erik Darling: Alright, let’s see here. “A user recently complained that some of their queries are taking longer than normal and timing out, looking at our monitoring tool, it looks like there’s more concurrent query activity but not much more can be done if there’s more traffic, correct?” Oh boy. Sounds like you have a…

Richie Rump: Is that correct?

Erik Darling: I mean, on the face of things, no, because you can always tune things more. There’s always a query or an index to be tuned. If it’s a third party app then no, probably not. The only thing you can do is throw some hardware at it, right? More RAM, more CPU, there’s something in there that can be done. I mean, you say not much, I assume that like you know, you’ve kind of thrown your hands up. If it’s a third party app, there’s hardware, if it’s your own internal app then you know, there’s query and index tuning and more hardware, so I wouldn’t say not much. I’d say there’s not much you can do today, but there is stuff that you can start testing and doing over time.

Richie Rump: I would say most things that I have run across with queries, the problem is with the query themselves. So there’s something in the structure of the query that can be adjusted a little bit, whether maybe it’s temp tables or CTEs or there’s something there, maybe a join or two that can be tweaked so it can make the query run faster, and it probably was done by a developer who just doesn’t have much experience with SQL and they just kind of went to joint city and just joined everything together.

Erik Darling: It’s true. That’s a big one, making sure that all your predicates can be searched on. You know, there’s a lot of stuff that I would keep looking at for that, but you know, that’s more than I could fit into a webcast I think.

Should I use columnstore indexes if I have constant inserts?

Erik Darling: Let’s see here. “I have a database, we’ll call it ProductionDB, it has small constant inserts but it’s a pseudo-reporting database. Can I expect, assuming I implement them smartly, large gains in performance by implementing column store indexes?” Jeez, if you’re inserting – I mean, are they a lot of single row inserts or are they lots of large inserts that you can tinker with? Because there is like a weird row number that you have to hit for inserts for column store to take them and make the delta stores and all that other compression thing, fancy language work correctly. If there’s lots of small inserts I would be hesitant to implement column store on it because as far as I know, column store still doesn’t handle writes terribly well, but I haven’t messed with it all that much.

Richie Rump: Sounds like a perfect candidate to test it in a…

Erik Darling: It does sound pretty good. I mean you know, from what I have messed with, column store is pretty rocking technology. The compression is awesome, make your data teeny tiny, lots of aggregation queries are pretty sweet, and you know, if your queries are fairly selective you can get lots of good row and segment elimination – segment elimination I believe it’s called, fancy word for it. So there is lots of great stuff about column store. Whether it’s going to work in this specific use case with the inserts I am not sure.

Richie Rump: Try it, let us know. We would love to find out.

How much memory is SQL Server using?

Erik Darling: Let’s see here. John – someone named John, John Smith, get in the squad car, ask another question. “I’ve assigned 28GB of memory to a SQL server. Task manager of course said it’s using 28GB. Task manager is not reliable for that, how do I tell how SQL server is using those 28GB? Like x percent are going to this thing and y are going to that thing.” There are all sorts of cool system DMVs where you can see what’s in memory. I think there’s like sys.dm buffer memory descriptors or something like that, you can figure out what tables and what indexes are in memory, you can query the plank cache and figure out how many GBs or MBs of plant you have in memory. So there is all sorts of interesting stuff you can look at. I would just check out the DMVs for that. I would just read up on the memory DMVs and figure out which ones are in there. There might be a query somewhere on a blog, I just don’t know – Brent would know if that was on there offhand now. Not that good.

How do you debug parameter sniffing issues?

Erik Darling: In general, how do you start debugging parameter sniffing issues? Well, the first thing I do is I make a temporary stored procedure, I don’t test with local variables and then I figure out what runtime parameters are and what compile parameters are and then I figure out what a plan would look like with each of them. If that’s the same then I start looking for outliers, so I might go into my data and I might start looking for certain values that have a big skew to them. By that I mean if there’s one type of value that has millions and millions or just way more rows than other values in general then I start comparing the plan that that gets by itself – the plans that smaller values get by itself. The classic example is if you have a sales report between Rhode Island and Texas, or if you have the sales reports or procedure you call Rhode Island, it’s going to get one plan, if you call Texas, it’s going to get a different plan. Or, it should get a different plan. It may need a different plan because Rhode Island’s plan would not be sufficient for it. If you call Texas first, you’ll get the big Texas plan and then Rhode Island will reuse that and that might be good or bad, because you might be spending up a lot of additional resources to help out Rhode Island. So there are a lot of ways to go with that, but in general I start looking at common variables to get passed in and if you know, isolated, they get different plans and what those plans are – it could be an indexing thing where like, you know, SQL is too like a weird key lookup plan for a smaller variable because that makes sense, and then when a large variable comes in and starts doing billions and billions of key lookups and that doesn’t make sense for the larger variable, so that could be an index thing where if you can tinker with an index, you’ll take away the key lookup plan as a choice for the large and small variables.

Richie Rump: It sounds like you need to do a little bit of data profiling.

Erik Darling: Little bit.

Richie Rump: When you’re trying to find these parameter sniffing, so if you’re unfamiliar with data profiling, there’s some pretty decent tools out there for it. I wrote a small script that I haven’t touched in a couple years that kind of gets you 75% of the way there, 70% there, called SP Data Profile, but you want to take a look at your data, take a look at your skew and then do all the stuff that Erik just said.

Erik Darling: No, no one’s paying attention there anyway. I feel bad – our transcriptionist is going to fall asleep. Late-stage Groucho Marx is falling asleep.

What causes a query plan to change?

Erik Darling: Nathan asks, “What causes a query plan to change? If all of the variables going in are the same, just the values are different, could auto stats trigger new plans that seem to not be as good?” Well that sounds a lot like parameter sniffing too – could be. So if you’re talking about a stored procedure that takes the same three variables and you pass in the same three variables but then execution plans are different. Well yes, it sounds like auto stats could have updated and invalidated an execution plan and then a weird set of variables may have gotten passed in and you may have gotten one plan and that plan doesn’t make sense for anything else. That could be one thing. If that plan uses temp tables, there’s a recompile threshold with temp tables and store procedures. Paul White wrote the banging-est blog post about it like a decade ago – it’s called ‘Temp Tables and Stored Procedures” if you want to check that out. Just stored procedure uses temp tables, after a temp table changes enough times that something recompiles and you get a different plan and statistics have updated so all sorts of stuff happens, so there’s a lot to look at with that.

Richie Rump: What about an index rebuild, would that trigger it as well?

Erik Darling: Yes, because an index rebuild does update statistics. A lot of other stuff – if there are a lot of table definition changes, you know, stuff like that. If the plan gets thrown out of cache for some reason, like memory pressure, next time you go and run the stored procedure it will have to come up with a new plan and that new plan may have been based on some weird set of variables. I would look into – I guess you can’t really look into that; either it happened or it didn’t.

What’s the best way to find unused indexes?

Erik Darling: Richard asks “what’s the best way to identify unused indexes? What length of time should they be unused before you consider them for delete?” Well, Richard, you know where you are, right? You know that you’re on the Brent Ozar Unlimited Office Hours and that we publish a set of scripts called the First Responder Kit. One of those scripts is called sp_BlitzIndex, and sp_BlitzIndex tells you when you have unused indexes, and that’s a really great place to start. For free.

Richie Rump: For free, and to be honest, those are the scripts that I used first, before I came here.

Erik Darling: I was like dismally familiar with them. Let’s see here, “what length of time should they be unused before considering them for delete?” Well, that’s why you don’t delete them, you just disable them. So then if it comes up that you needed one of those indexes, it’s a lot easier to go back into the GUI and right click and hit rebuild than it is to figure out which index definition you’ve lost entirely. What length of time kind of depends on your application, you know, you could have that index that gets used once a month for an end of month processing report or quarterly for some vice-president report that you might absolutely need but might look unused the rest of the time. So when I start looking at that stuff I like to run BlitzIndex weekly and then look at things and what I like to look for, over longer periods of time than a week, is writes versus reads. So for unused indexes you’re always going to see – things that get flagged as unused indexes in sp_BlitzIndex are always zero reads, but I like to look for ones that also have high writes, because if they have zero reads and zero writes I just don’t care, they can sit there and take up metadata space and not do anything, it doesn’t really bother me much, especially because I don’t rebuild indexes so it doesn’t matter, but if they have really high writes and no reads then I start getting concerned because that’s slowing me down. That’s hurting I/O, that’s hurting modifications, inserts, updates and deletes but it’s not really helping any re-query. So more than any length of time, I like to look at how many writes have accumulated on our index; that’s a good way to go.

What names should Erik and Richie use next week?

Richie Rump: So here’s another question, “next week Erik should be named Sloth and Richie should be named Chunk, or the other way, whatever you guys want, fight over it…” I call Data, but thank you, that’s a good suggestion. Sloth over here is probably…

Erik Darling: I want to take Mouth.

Richie Rump: Mouth…

Erik Darling: He was my favorite.

Richie Rump: That actually is pretty accurate.

Erik Darling: That scene where he’s talking to the maid in Spanish about where the drugs are. That still makes me laugh.

When will I see a GroupBy.org presentation from you?

Richie Rump: Here’s another question, “when will I see a GroupBy.org presentation from one of you fine gentlemen?”

Erik Darling: Well not the next one around because those presentations already got picked. I was sort of intentionally not throwing my hat in the ring for the first couple because I really wanted other people, I didn’t want it to seem like the Brent Ozar Unlimited show for GroupBy, but Brent is actually actively encouraging us to submit for future sessions, so probably the next one.

Richie Rump: Yes, I can’t wait until all of you guys hear about the intricacies of Javascript promises compared to callbacks, because I’m sure all of you wanted to hear about that.

Erik Darling: Yes, tell me more about sorting arrays efficiently.

Richie Rump: Who does that anymore man?

Erik Darling: I don’t know…

Richie Rump: Let the CS101 guys figure it out.

Erik Darling: I’m basically a CS101 guy, that’s my gig.

Should I shrink my database if…

Erik Darling: Let’s see here, “is there a reason to shrink if you have a non-normal operation that made it grow in the first place?” Well, I’m going to – yes, go ahead and shrink it down. Just truncate the empty space. I assume you’re talking about your data file or your log file, I assume you’re not talking about anything personal. So what I would do is just get rid of the empty space, there’s a truncate only thing you can do where you just get rid of the empty space; that’s usually the safest thing to do. If you – see whenever people talk about the bad stuff that shrinking a file causes, it’s like when you shrink a file like way beyond just what’s empty, when you start mashing stuff things get really bad. Lately when I’ve tested it and I’ve looked at things and I’ve just been getting – like when I do demo stuff and I blow up stuff by doing things then I have all this stuff in my overflow data file that just isn’t used by anything, I’ll truncate the empty space in 1GB or 5GB increments, and then like if I check the next fragmentation after that, like oh, this would make a good blog post, but I haven’t seen anything from fragmentation like that in a while so…

What headsets and microphones do you guys use?

Richie Rump: Meanwhile I’m in derailing mode, so Erik, which make/model headset and microphone are you using? Pros and cons…

Erik Darling: I don’t know, Brent sent it to me… An Audio Technica BPHS1, and it is hooked into, what’s it called, a Scarlett Focusrite?

Richie rump: Focusrite Scarlett 2i2 – I think that’s pretty standard what we’ve got here.

Erik Darling: It has the one jack, not the two jacks.

Richie Rump: Oh, so you’ve got the Focusrite Scarlett Solo?

Erik Darling: Yes, well it has the big jack and then the little microphone jack and the knob and I can hear myself talk too much, and it has a guitar thing on it, so I guess if I wanted to play the guitar I could do that too.

Richie rump: Yes, that’s the Solo. The audio interface that I’m using is the Focusrite Scarlett 2i2, has two audio inputs; never use them, only need one because there’s only one of me, and I’m also using the Audio Technica ATH-50X headphones with Memory Foam inserts as well as a special skin, you can see it right there, because I’m vain like that, and I’m also using an Audio Technica 2005 microphone which is a USB and XLR. One of the kind of flexibilities of USB or XLR, I think I’ve kind of outgrown that and I only need an XLR because I’m using the Scarlett so much, but if I’m ever on the road and I need a USB, it’s kind of there. So that’s kind of my gear and how we’ve got the setup.

Erik Darling: And that was my chance to tune out. Thanks, Richie.

Richie Rump: Everybody except that one guy is like “what?”

Erik Darling: Good time, good time.

What’s your favorite learning resource for internals?

Erik Darling: Alright, Germal asks a fun learning question. I like this one because I don’t have to get too specific and it’s okay if I NyQuil this one a little bit, “what’s your favorite resource to learn about query processing internals and what do you recommend for getting better at troubleshooting query performance issues?” Well for me, Paul White’s blog posts – when you start reading them they will be a million years beyond your reach, but as you mature they will start to make more sense. Ditto Adam Machanic’s blog posts, they both write some rocket science-y stuff. As far as other stuff goes, the SQL server internals books by Kalen Delaney at all are good. there are optimizer chapters written by Connor Cunningham who used to own the query optimizer at Microsoft and the 2012 and 2008 are two versions of that, pretty much the same so I would just stick to the 2012 one; those are my favorites, those are my crazy people.

Richie Rump: I think I started my journey just kind of paying attention to what people were writing over Twitter and posting on Twitter. A lot of, oddly enough, Brent Ozar stuff – I Don’t know why you’d listen to that guy… Then I started attending a lot of SQL Saturdays and started presenting to a bunch of SQL Saturdays about project management. That’s kind of how I got started, I was talking about project management at SQL Saturdays so I could go in and I could meet the speakers the night before and then meet and hear all the database stuff kind of afterwards, and Summit is a great place I think to learn a whole bunch of different stuff and meet a bunch of different people. So that’s kind of how I came into the whole database stuff, and now I don’t even touch it at all. Thanks, guys, back to my developer roots…

Erik Darling: The gateway to query processing knowledge really is learning about execution plans. So the more you can learn about execution plans the more you can pry into…

Richie Rump: Oh and Grant has a good book on that too, I believe.

Erik Darling: He does.

Richie Rump: Yes I’ve read that one, that was a good one.

Erik Darling: Yes, Grant has a good book on – Grant Fritchey has a book on execution plans, and a long time ago a guy named Fabiano Amorim published a series for Simple Talk where he talked through common execution plan operators, and I used to love reading that over and over again.

Richie Rump: I remember that, that was pretty good.

Erik Darling: yes, it was like 14 or 15 different things about all different query plan operators and what really impressed me so much about those was that this guy could write queries that would produce certain operators, and that’s when it started to make sense to me, like oh no, there’s a logic to this. Like when I do certain things, certain operators are going to pop up and this is why. That was one of the first like light bulb SQL moments I ever had, reading through this like “you can make it do that, that means it does that reliably based on things!” And that was lovely for me.

Richie Rump: So my first ah-ha in SQL server was I was at SQL Saturday Orlando and I sat through an Adam Machanic hour-long session and I didn’t get lost. Right, so this was like a 400 level session, and I’m sitting there and I’m hanging in there and hanging in there, I’m like I got it, I got it, and then at the end it was like “I understood that!” Light bulbs started going off, fireworks behind my head, I’m like wow…

Erik Darling: Do you remember which talk it was?

Richie rump: No, I have no idea. I’m sure I could Google back and figure it out.

Erik Darling: One of my favorite talks ever by him was – it’s on Youtube, it was a TechEd conference talk and it’s called like the Top Five execution Plan Problems That You Can Fix or something like that, and it was like watching – like I’m with you on that…

Richie Rump: Was that the crazy ones where he goes, you know, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10?

Erik Darling: Yes…

Richie Rump: It may have been the same one.

Erik Darling: Yes, and then like all of a sudden it’s parallel and there’s a distinct and the averages and you’re like “I get it, that made sense to me, I can go home and reproduce that!” But yes, Adam Machanic and Paul White are two of the main guys.

What are you learning next?

Richie Rump: I know we’re a little bit over time but let’s get one more, “what are you each learning now for your next blog posts?”

Erik Darling: Learning? Oh boy – I’ve been writing and rewriting a lot of training material for some upcoming videos that are going to be part of our cool subscription re-launch. So I’ve been going over training materials on sargability, dynamic SQL, filtered indexes, computed columns, common table expressions, tons of stuff like that. I don’t know how much of it is going to make it to blog posts because I do still have to record it all, but we’ll see what happens.

Richie Rump: Yes, I’ve been doing a ton of server-less and Javascript and that type of stuff, so that probably is not going to make it to our blog at least, BrentOzar.com. I don’t really write so much because I’m too busy doing stuff, but I did start a blog post series on Entity Framework, kind of for the DBA and kind of like the top five or six things that you can do to kind of optimize and find problems in any framework queries. So I’m hoping to finish that soon, but I started that last year. So we’ll see how much I can get done, because as opposed to Erik, I’m not bored here.

Erik Darling: I’m not actually bored, sometimes it’s just weird to be able to choose what you want to do sometimes. Like you have a mission, you have a dedicated purpose…

Richie Rump: I’m on a mission. This is a mission, not a smalltime thing…

Erik Darling: You know, like when I have free time, in other words when I’m not on client time and I get to pick what to do, I’m like First responder Kit, blog, new training materials. There’s like all these directions that I get pulled in.

Richie Rump: Someday I’m going to write a video training and it’s just going to be how to live with Erik, and it’s going to be like an hour or two hours of just that.

Erik Darling: I can’t wait for my wife to watch that.

Richie Rump: I’m mainly doing it for your kid, do…

Erik Darling: Oh she doesn’t know, she barely knows I’m here.

Richie Rump: She will, she will figure out that you’re in a closet, okay.

Erik Darling: She will. Well, it’s not that closet-y, this is like a separate zip code in New York.

Richie rump: It’s closet-y.

Erik Darling: You’re closet-y. We should go – goodbye, thank you for showing up. Wish me well because I’m sick, goodbye.

Wanna join us at the next Office Hours to ask a question? Register here.

Blog

The missing link

SQL Server vNext

Sneaky, sneaky, sneaky

So what did I find?

sys.dm_db_missing_index_group_stats

fn_full_dblog

dm_exec_query_stats

Bright and shiny

ICYMI

Highlight Reel

Stay Tuned

Disclaimer

Dude, where’s my table variable?

Crying Game

What do I do if stats updates are causing blocking?

Do rollbacks affect AGs differently?

What’s causing all these forwarded fetches?

What do I do with Paste the Plan feature requests?

Should I make a 2×3 VM or a 3×2?

Are elastic databases different than regular databases?

Can index includes slow down DML operations?

Why do CLR stored procedures work on only one AG replica?

I got a big raise after going to Brent’s class…

Do you use SQLCMD query mode?

What are database snapshots good for?

Will query memory grants cause page life expectancy to drop?

Does ALTER TABLE work through a table in portions?

Why should I use a GUID as a primary key?

What is the dedicated admin connection for?

Should I build a 1×6 VM or a 6×1 VM?

Half our NUMA nodes are offline. How should we set MAXDOP?

Should I store files in the database?

Do I need a separate network for my AG replication?

How should I keep settings in sync across multiple servers?

What’s included in the new Enterprise Subscription Plan?

Are you archiving these Office Hours videos?

I like blogging

I think I’ll do that more often

Fixing it with the claw hammer: MIN_GRANT_PERCENT

Fixing it with the scalpel: CREATE INDEX

While working on a demo

Logging Tables

Logjammin’

What are the pros and cons of Platform-as-a-Service?

What resources get used when you ALTER TABLE?

Why is parameter sniffing an issue in only some of my procs?

What’s your experience with Azure?

Any good references on columnstore indexes?

How should I train someone on Always On Availability Groups?

Can I add an AG listener online?

Do you have a server health checklist?

Do sleeping connections cause negative impacts?

Should I use fill factor 100% or something else?

How do I talk people out of including every field in an index?

sp_Blitz Improvements

sp_BlitzCache Improvements

sp_BlitzFirst Improvements

sp_BlitzIndex Improvements

sp_BlitzWho Improvements

sp_DatabaseRestore Improvements

If I configure a VM with local SSDs, and the host fails…

Would you automate log file truncation?

Which index should I rebuild next?

Have you had issues with sp_rename?

Ramblings about unit tests and feedback

If a SQL Server gets more queries, does it get slower?

Should I use columnstore indexes if I have constant inserts?

How much memory is SQL Server using?

How do you debug parameter sniffing issues?

What causes a query plan to change?

What’s the best way to find unused indexes?

What names should Erik and Richie use next week?

When will I see a GroupBy.org presentation from you?

Should I shrink my database if…

What headsets and microphones do you guys use?

What’s your favorite learning resource for internals?

What are you learning next?

Hi! I’m Brent Ozar.

Get Free SQL Stuff