This heat has got right out of hand

So many things happen in mid-Summer. Dreams. Death. Pit stains.

And FRK updates!

Special thanks to @digitalohm for once again taking the axe to sp_DatabaseRestore. You are the only person on our email list who isn't getting a Box O' Tripe®

sp_Blitz Improvements

• #872 @MosesOfJ pointed out that, while our code is flawless, our spelling was not. Thanks, Mo.
• #877 Dear sir Moses also pointed out that our code is not flawless, but our spelling was! Hey, we're... 50/50. Kinda. What's math got to do with code, anyway? Now SkipChecks work NVARCHAR(4000)% better.

sp_BlitzCache Improvements

• #889 Added pre-2017 future-proofing to pull information about statistics and adaptive joins out of XML
• #890 Things were formatted. No one got drooled on. Quite the day.

sp_BlitzFirst Improvements

• #879 After a weird situation (blame @TaraKizer ), we decided to stop ignoring CLR waits. Boy oh boy.

sp_BlitzIndex Improvements

• Nothing this go around. We try not to mess with perfection.

sp_BlitzWho Improvements

• Has attained a zen-like state and did not receive any chakra filth.

sp_DatabaseRestore Improvements

• #871 Here we stand athwart log restores yelling STOPAT! So, you know, if you want to stop at a certain time within a log restore, you can do that now. This was all @digitalohm, whose mailbox will not smell like the worst hangover ever.

sp_BlitzBackups

• Initial release! There were many PRs and fixes in the dev branch leading up to this that aren't really pertinent historically, because it has no history. Literally none. This is a newborn. Give it a spankin.

sp_BlitzQueryStore

• MAZEL TOV! It's another new stored procedure. Requires 2016, and an enabled query store. Details will be forthcoming in a blog post this week, but you can get it now and feel the hot rush of blood that accompanies hitting F5 and finding out how horrible things truly are.

It was all a dream, I used to read Wizard magazine

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.

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.
• #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.

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 !

Leap Month

I got 128 GB of RAM just to develop sp_BlitzCache with! Also, thanks to @RichBenner @BrentOzar and @douglane for code, and @LitKnd and @HenrikStaunPoulsen for great ideas!

sp_Blitz Improvements

• #677 Finally! A bug that isn't ours! Microsoft forget to let SE clean up query store. We warn you about that now.
• #650 2016 SP1 gave us exciting new (old) Enterprise features. We give you some boring new wording about them. (suggested by @HenrikStaunPoulsen )
• #602 More checks to see if you did something ridiculous to your CPUs

sp_BlitzCache Improvements

• #688 Curious about RLS in your query plan? So is @LitKnd! Thanks, Kendra!
• #678 In 2016 and up, we'll tell you something if estimated rows and estimated rows read are way different. Seriously, upgrade to 2016. What's wrong with you?
• #665 So uh, you can now sort by 'all' and 'all avg'. This may perform terribly on some servers. Please report any bugs.
• #663 Sometimes stored procedure costs would show up as double. That's no good.
• #661 Function join detection was weird in some circumstances.
• #660 Sometimes we don't find any warnings. Now we tell you when we don't find any warnings. Relief.
• #659 Operator checks sometimes take a long time. Breaking them into smaller parts helps.
• #653 Ever see something in BlitzCache that wasn't yours? Funny thing about SPIDs, huh? We filter on those now, too.
• #652 Filters! Referencing scalar valued functions! What a bad idea. We'll let you know about that.
• #633 Are you modifying a lot of indexes? I don't know. But BlitzCache does. This looks a little inflated because it takes into account IF branches, but you get the idea.
• #459 If a stored procedure gets executed once, does anyone care? @douglane doesn't! Thanks, Doug!

sp_BlitzFirst Improvements

• #668 Context is everything. Especially for databases. When you're checking for stored procs.
• #658 Long running queries being blocked. Great post, Brent! (coded by @BrentOzar )
• #647 Is SQL running? Yes? So is something other than SQL, and it's taking >= 25% of your processor time. (coded by @RichBenner )
• #646 Because you should know if some goofball is restoring a database (coded by @RichBenner )
• #645 Night of the sleeping SPIDs (coded by @RichBenner )

sp_BlitzIndex Improvements

• #680 Statistics can have more than one column in them. Go figure.
• #671 Not all ReportServer databases are just called ReportServer (coded by @BrentOzar )

sp_BlitzWho Improvements

• #649 Oh, that pesky database_id error.
• #645 Night of the sleeping SPIDs (coded by @RichBenner )

2016 Dec 10 - The December to Remember Script Event

Happy holidays! Shout out to our contributors this month: @BlitzErik, @RichBenner, @runamuk0, @SQLMonger, and @BrentOzar.

sp_Blitz Improvements

• #591 - add check for 2016 Standard installed without Service Pack 1 (coded by @RichBenner)
• #615 - add check for trace flag 834 used in combination with columnstore indexes (coded by @RichBenner)
• #603 - add check for min server memory = max server memory (suggested by @BlitzErik, coded by @RichBenner)
• #598 - added checks for non-default database configuration scoped settings in SQL 2016 (suggested by @HenrikStaunPoulsen, coded by @BrentOzar)
• #593 - added check for Instant File Initialization using new sys.dm_server_services field in SQL 2016 SP1 (suggested by @BlitzErik, coded by @BrentOzar)
• #592 - added check for Lock Pages in Memory using new sys.dm_os_sys_info field in SQL 2016 SP1 (suggested by @BlitzErik, coded by @BrentOzar)
• #582 - added check for TempDB file creation error (suggested by @BlitzErik, coded by @BrentOzar)
• #616 - improved Target Recovery Time warning because some SQL tools set it to 0 and some set it to 60 (suggested by @aclenscorp, coded by @BrentOzar)
• #619 - fixed case sensitivity bug introduced in another patch (coded by @mikejwhitty, only visible to folks like us who work in the dev branch)

sp_BlitzCache Improvements

• #617 - add alert for computed columns with scalar operators (coded by @BlitzErik)
• #626 - add alerts for more kinds of expensive remote queries (coded by @BlitzErik)
• #228 - skip offline databases (coded by @BlitzErik)

sp_BlitzFirst Improvements

• #599 - made it more PowerShell-friendly by making all column names unique in the FileStats column output (coded by @runamuk0)

sp_BlitzIndex Improvements

• #615 - added warning if trace flag 834 is used in combination with columnstore indexes (coded by @RichBenner)
• #622 - stop counting hypothetical indexes in the total number of indexes on a table (coded by @BlitzErik)
• #607 - now works in partially contained databases (reported and fixed by @SQLMonger)

sp_BlitzWho Improvements

• #544 - added database name in the output to show the query's context (coded by @BlitzErik)

2016 Nov 15 - Giving Thanks to @RichBenner and @BlitzErik

These improvements go great with cranberries. The food, not the b - actually, they go pretty well with the band, too.

sp_Blitz Improvements

• #568 - @RichBenner added a check for the default parallelism settings

sp_BlitzCache Improvements by @BlitzErik

• #495 - add warning for indexed views with missing stats
• #557 - bug fix - don't alert on unused memory grants if query is below the server's minimum memory grant
• #583 - add warning for backwards scans, forced index usage, and forced seeks or scans

sp_BlitzIndex Improvements, Mostly by @BlitzErik

• #566 - new @SkipStatistics flag enabled by default. This means you only get the stats checks if you ask for it. (We were having some performance problems with it in last month's version.)
• #567 - bug fix - now adds persisted to a field definition if necessary
• #571 - bug fix - better checks for computed columns using functions in other schemas
• #574 - bug fix - long filter definitions over 128 characters broke quotename
• #578 - bug fix - @RichBenner made sure SQL 2005 users understand that they're unsupported

2016 Oct 23 - sp_BlitzCache, sp_BlitzIndex new checks

Just when you thought we were done for October, we're turning into ROCKTOBER.

sp_BlitzCache Improvements, All @BlitzErik's Handiwork

• #543 - look up execution plans for a stored procedure by name, the new @StoredProcedureName parameter
• #540 - add warnings for recently compiled plans (last 4 hours)
• #497 - add warning for CLR functions
• #482 - add check for compute scalar operators that call functions
• #422 - add check for table variable use
• #556 - add and improve cursor checks
• #532 - fix double warnings on expensive remote queries
• #559 - fix varbinary conversion error

sp_BlitzIndex Improvements, All By The Incredible Non-Edible @BlitzErik

• #529 - add divide-by-zero handling to stats queries
• #530 - add more clear wording to stats warnings
• #479 - add a check for filtered stats
• #531 - don't get filtered stats on SQL 2005
• #528 - add schema information to collection and results
• #527 - faster stats collection with >50k objects
• #490 - sp_BlitzIndex should be marked to recompile

sp_Blitz, Both @BrentOzar Fixing His Bugs

• #526 - fixed arithmetic overflow in wait stats checks
• #546 - refine poison wait for serializable locking

2016 Oct 22 - sp_BlitzCache, sp_BlitzIndex new checks

(Deprecated due to emergency fix with sp_BlitzCache)

2016 Oct 14 - sp_Blitz markdown output, sp_BlitzIndex statistics checks

In addition to lots of bug fixes and tweaks, here's the new stuff:

sp_Blitz v53.3:

• @OutputType = 'markdown' (Brent Ozar) - I'm always looking at StackOverflow.com questions and saying to myself, "Man, if I just had the sp_Blitz output for that server, I bet I could answer this." Makes it way easier for people to get help for free.
• New check for failover cluster health (Matt Tucker) - warning if you don't have failover cluster nodes available in sys.dm_os_cluster_nodes.
• New check for endpoints owned by user accounts (HBollah)
• New check for wait stats cleared since startup (Brent Ozar) - useful in combination with @CheckServerInfo = 1, which includes any wait stats that are bottlenecks.
• Improved Amazon RDS and SQL Server 2005 compatibility (Brent Ozar)

sp_BlitzFirst v25.2:

• Split what's running now code into new sp_BlitzWho (Ryan Howard) - several folks said they found this section useful on its own. We'll add more into sp_BlitzWho down the road.

sp_BlitzIndex v4.2:

• Added statistics checks (Erik Darling) - warning about outdated stats and stats with low sample rates.
• Added database name to multi-db result sets (Brent Ozar) - notable because it's a breaking change if you'd built anything on top of @GetAllDatabases = 1.

2016 Sept 3 - Bug Fixes, Memory Grants, and Improvements to @OutputServerName, @CheckServerInfo

In addition to lots of bug fixes, here's the new stuff:

sp_Blitz v53.2:

• @OutputServerName writes output to remote server (Haris Khan) - for years, these stored procs have had an @OutputServerName parameter just waiting to be hooked up, and Haris is doin' the hookin'. Now you can push your sp_Blitz results to a central server for easier monitoring! He's doing the same with the rest of the stored procs, too, and we just have to put more work into testing those.
• New warning for unevenly sized TempDB data files (Brianc-DBA) - we'd always checked for different autogrowth sizes, but shockingly, we weren't looking for unevenly sized files! Great catch, Brian.
• @CheckServerInfo = 1 now includes Windows restart time (Julie OKC) - it had always included the SQL Server instance restart time, but now you get Windows too.
• @CheckServerInfo = 1 now checks for Instant File Initialization (Tara Kizer) - starting with SQL Server 2014 SP2 and 2016, Microsoft's logging it in the error log on startup, so we're checkin' for it. We only say yes if it's enabled - we can't say for sure that it's not enabled, since you may have cycled the errorlog since startup.

sp_BlitzCache v3.2:

• New warning on unused memory grants (Erik Darling) - thanks to new DMV improvements in 2012/2014/2016 that show how much memory queries are granted versus how little they actually use.
• Add @sortorder options for 'memory grant' and 'avg memory grant' (Erik Darling) - makes it way easier to troubleshoot queries that get a huge memory grant.

sp_BlitzFirst v25.1:

• Add waits from sys.dm_os_waiting_tasks to wait stats (Erik Darling) - now, if we've got a long-running query blocking others, the lock waits will go up even though the query hasn't finished. Before, we were only adding up activity from sys.dm_os_wait_stats, which doesn't increment until the wait clears.

sp_BlitzIndex v4.1:

• Better version number/date formatting (Jorge Solorzano) - making it easier to update new versions of sp_BlitzIndex.
• New @SkipPartitions parameter (Erik Darling) - for faster processing on databases with large numbers of partitions.
• Better results when @GetAllDatabases = 1 (Erik Darling) - when you've got lots of problems, you want 'em sorted by priority.

2016 July 15 - Performance Tuning Help

SQL Server 2012-2016 users are going to find a lot of stuff to love in here:

sp_BlitzCache v3.1:

• Show cost for stored procedures. #339
• Warn about trace flags added at the query level, and global trace flags. #361
• Add warnings about Remote Queries. #315
• Do not show Forced Plans warning if the real cause is forced parameterization. #343
• Fix divide-by-zero error if Cost Threshold for Parallelism is 0. #358
• Fix warning for unparameterized query. #334

sp_BlitzFirst v25 (The Artist Formerly Known as sp_AskBrent):

• Add new memory grants columns to 2012-2016 live queries output. #362
• Add SQL login to live queries output. #354
• Filter Perfmon counter display to skip counters with zeroes. Still logged to table though. #356

sp_Blitz v53.1:

• Warn about 2016 Query Store cleanup bug in Standard, Evaluation, Express. #352
• Updating list of supported SQL Server versions. #344
• Fixing bug in wait stats percentages. #324

sp_BlitzIndex v4.1:

• Compression information in @mode = 2. #18
• Use recently-modified check to improve indexes-not-in-use recommendations. #220
• Alphabetical sort for @GetAllDatabases = 1, @mode = 2 output. #351
• Remove per-day cost filter for missing indexes in @mode = 4. #338
• Missing index benefit is now labeled per-day to make it more obvious. #330