Our free server troubleshooting tool, sp_Blitz™, just learned some new tricks. If you call it like this:
@OutputDatabaseName = ‘DBAtools’,
@OutputSchemaName = ‘dbo’,
@OutputTableName = ‘BlitzResults’
It will create the table DBAtools.dbo.BlitzResults (if it doesn’t already exist) and insert the results. The table includes columns for the server name and the check date, so you can query this table to see what’s changed since the last time you ran sp_Blitz™. Plus, there’s more:
Added new checks for disabled CPU schedulers (due to licensing or affinity masking), extended stored procedures, disabled remote access to the DAC, databases in unusual states like suspect or emergency, logins with CONTROL SERVER permission.
Easier readability – whenever anyone’s asked how to add checks, I’ve always just said, “Read the code.” Last week, I read the code with fresh eyes and realized it could use some cleanup and comments. I used Red Gate SQL Prompt to clean up the formatting, which is better but could still use some work. The code is nearing 4,000 lines, so I moved the old changes out of the proc and into a new online changelog. I put in some time to explain what the proc is doing, and that should help people reading it from the first time. I’ve also started an sp_Blitz™ Documentation page where I’ll be explaining some of the more advanced uses.
New downloadable sp_Blitz™ app for Windows - over and over, we heard from people that they wanted a quick way to generate a print-friendly copy of the sp_Blitz™ results. Jeremiah worked with a developer and built our first app:
The app creates sp_Blitz™ in TempDB on the server of your choice, executes it, shows you the results, and can output the results to PDF – complete with links to the explanations for each result.
We’ve got lots of ideas for the app, but we wanted to get it out there first and let you help drive it. If there’s something you’d like to see added or changed, leave a comment here or contact us. The whole point of this thing is to make your life easier. I wish this tool would have been available back when I was a production DBA struggling to figure out what was going on in my environment, so every little improvement helps. Hopefully this can save you from the stress I had back then. Get it from the download page and enjoy!
There’s a new version in town. v18 adds new checks looking for:
- Backups stored on the same drive letters as user databases (Thanks @AlinSelicean!)
- Agent jobs that don’t have failure notifications set up (Thanks @Thirster42!)
- Shows free drive space per drive letter if you pass @CheckServerInfo = 1 in. We don’t consider this a problem – we just show additional info if you use this parameter.
- Added the elevated database permissions check back in (whoops! dropped it accidentally)
Plus bug fixes and improvements including:
- Lots and lots of typos
- Ignore max file sizes for filestream data files
- Switched a lot of @@SERVERNAME usage out with SERVERPROPERTY(‘ServerName’) because in some SQL Server cloud implementations, those don’t match, and it’s okay.
- Changed database name variables to be NVARCHAR(128) to handle those monster SharePoint databases
- Improved the non-aligned-indexes check to return results even if the index hasn’t been used
And more. Version 17 also added a neat new exclusions table parameter – if you’re the kind of DBA who wants to automate sp_Blitz data collection throughout the environment, but you want to skip certain checks or certain databases, you can do that now. I haven’t written up full instructions on that, but the basic idea is pretty obvious within reading the first 100-200 lines of the code.
As always, you can get the latest version and instructions from http://www.BrentOzar.com/blitz/. If you’d like to contribute code, you can email us at Help@BrentOzar.com. I’m still working through a backlog of about 20 more new check contributions, and we’ve got some fun stuff coming – including a thank-you page listing the contributors and their work.
I don’t blog every release of sp_Blitz (we pushed v15 out silently with a few bug fixes) but we added a lot of improvements and fixes in this version – and by we I mean you. After I blogged about v14′s release earlier this week, that encouraged a lot of people to come out of the woodwork and contribute code. I’m still going through all the submissions and adding ‘em in, but I’m pushing this one out the door now because it’s got some cool stuff:
- Chris Fradenburg @ChrisFradenburg http://www.fradensql.com added check 81 for non-active sp_configure options not yet taking effect and improved check 35 to not alert if Optimize for Ad Hoc is already enabled.
- Rob Sullivan @DataChomp http://datachomp.com suggested to add output variable @Version to manage multiple-server installations. This way you can query all your servers and get back what version they currently have installed.
- Vadim Mordkovich added check 85 for database users with elevated database roles like db_owner, db_securityadmin, etc.
- Vladimir Vissoultchev rewrote the DBCC CHECKDB check to work around a bug in SQL Server 2008 & R2 that reports dbi_dbccLastKnownGood twice.
We added checks for database snapshots, stored procs with WITH RECOMPILE in the source code, Agent jobs with SHRINKDATABASE or SHRINKFILE in the steps, and a check for databases with a max file size set.
- We added @CheckServerInfo perameter default 0. Adds additional server inventory data in checks 83-85 for things like CPU, memory, service logins. None of these are problems, but if you’re using sp_Blitz to assess a server you’ve never seen, you may want to know more about what you’re working with. (Kendra’s idea!)
- Tweaked check 75 for large log files so that it only alerts on files > 1GB.
- Fixed a few case-sensitivity bugs.
- Added WITH NO_INFOMSGS to the DBCC calls to ease life for automation folks. I was surprised by the number of requests we got for this – turns out a lot of people are doing widespread patrols of their servers with sp_Blitz!
- Works with offline and restoring databases. (Just happened to test it in this version and it already worked – must have fixed this earlier.)
If you’d like to contribute code, contact us. Pro tip: if your code is written in a way that I can just copy/paste into sp_Blitz, it’ll get published a lot faster. I get a lot of contributions that are various DMV queries, but if I have to rework it to handle multiple databases simultaneously, work differently for 2005/2008/2012, and handle case-sensitive collations, then it takes me much longer to implement (sometimes months).
You can download sp_Blitz now and stop getting surprised by your SQL Server’s hidden past. Enjoy!
Today, we’ve got a big one, and it’s all thanks to you.
For the last couple of versions, I haven’t added any big features because I’d been focused on the plan cache improvements. Today, though, we’ve got a big one with all kinds of health-checking improvements – and they’re all thanks to your contributions.
- Lori Edwards @LoriEdwards http://sqlservertimes2.com - Did all the coding in this version! She did a killer job of integrating improvements and suggestions from all kinds of people.
- Chris Fradenburg @ChrisFradenburg http://www.fradensql.com – added a check to identify globally enabled traceflags
- Jeremy Lowell @DataRealized http://datarealized.com added a check for non-aligned indexes on partitioned tables
- Paul Anderton @Panders69 added a check for high VLF count
- Ron van Moorsel tweaked and added a couple of checks including whether tempdb was set to autogrow, and checking for linked servers configured with the SA login
- Shaun Stuart @shaunjstu http://shaunjstuart.com added several changes – the much-desired check for the last successful DBCC CHECKDB, checking ot make sure that @@SERVERNAME is not null, and updated check 1 to make sure the backup was done on the current server
- Sabu Varghese fixed a typo in check 51
- We added a check to determine if a failsafe operator has been configured
- Added a check for transaction log files larger than data files suggested by Chris Adkins
- Fixed a bunch of bugs for oddball database names (like apostrophes).
Here’s how to use it:
Head on over to BrentOzar.com/blitz and let us know what you think. Enjoy!
When you send your beautifully hand-crafted organic T-SQL statement to SQL Server, the database engine takes a moment to appreciate the poetry of your work. Before it turns your request into results, it has to build an execution plan. SQL Server considers:
- Which tables it needs to join together
- What subqueries it needs to execute
- Whether it can reverse-engineer your intent to achieve the same results faster
- What indexes exist for the tables/views you’re trying to join
- If it can do partition elimination to make things go faster
- And much, much more
Much like you, SQL Server doesn’t like doing much work. SQL Server put a lot of work into building your execution plan (which may also be a work of art itself), and wants to avoid reinventing that wheel again, so it caches the execution plan in memory. If the same query comes in again, SQL Server can just check the cache, find your beautiful snowflake, and reuse the same plan.
This is the plan cache, and it stores more than just plans. We can get metrics about how many times the query was called and how much resources it used (min/max/avg/total).
It’s not perfect – there’s a lot of things that can cause the plan cache to flush completely or in part:
- Service restarts
- Database restores
- Statistics changing on an object
- Server comes under memory pressure
- People running DBCC FREEPROCCACHE
I still love the plan cache anyway. It’s not perfect (just like many of the things I love) but it’s a fast, easy-to-access way to discover some of the queries that have been using a lot of resources lately.
Making the Plan Cache Easier to Analyze
I’ve built up a set of queries to slice and dice my way through the plan cache, but I wanted to make it easier for people tackling their first performance tuning project.
At the PASS Summit this month, I unveiled the latest version of sp_Blitz™, which makes all this easier. I’ve added a few new parameters:
@CheckProcedureCache – if 1, we grab the top 20-50 resource-intensive plans from the cache and analyze them for common design issues. We’re looking for missing indexes, implicit conversions, user-defined functions, and more. This fast scan isn’t incredibly detailed – we’re just looking for queries that might surprise you and require some performance tuning.
@OutputProcedureCache – if 1, we output a second result set that includes the queries, plans, and metrics we analyzed. You can do your own analysis on these queries too looking for more problems.
@CheckProcedureCacheFilter – can be CPU, Reads, Duration, ExecCount, or null. If you specify one, we’ll focus the analysis on those types of resource-intensive queries (like the top 20 by CPU use.) If you don’t, we analyze the top 20 for all four (CPU, logical reads, total runtime, and execution count). Typically we find that it’s not 80 different queries – it’s usually 25-40 queries that dominate all of the metrics.
To learn more about how the plan cache works and how I analyze it, here’s a 30-minute video:
Or you can take a shortcut and just grab sp_Blitz™ now. Enjoy!
It’s crucial for database administrators to recognize signs of pending doom. Make sure you’re able to quickly spot problems in SQL Server Management Studio by studying these screenshots.
What’s that? You can’t see the differences in the screenshots? That’s because SQL Server Management Studio doesn’t show you serious problems.
The more I learn about SQL Server and the more customer servers I examine, the more frustrated I get with SQL Server Management Studio. It’s the year 2012, and the product’s been out for well over a decade – yet dangerous settings like Priority Boost and Lightweight Pooling don’t even have warnings in the GUI. Just enable ‘em, click OK, and your server’s performance and reliability can go right down the toilet without so much as a warning icon in SSMS.
When you’re facing performance or reliability problems, your first step should be to run our free sp_Blitz stored proc to show you this stuff. It just breaks my heart when we do our SQL Server health checks, find these problems within the first few minutes, and everyone’s surprised that someone enabled these obscure options long ago without telling anyone.
sp_Blitz: because no one likes zombie cockroaches in the database server.