Database Health Monitor June 2017 Version Released Today

Download PDF

Today I released the June 2017 version of Database Health Monitor, you can get it on the download page at http://DatabaseHealth.com.  Here is what it included.

Version 2.5.5 Release Notes

Version 2.5.5 is the June, 2017 release of Database Health Monitor

New Features in 2.5.5

  • Added a Recovery Model column to the Backup Status Report based on a customer request. This applies to the database specific Backup Status Report and the multiple-instance Backup Status Report.
  • Added new File Utilization report that shows the amount of space being used and the amount of space free in your data and log files.
  • Added a check in the historic monitoring “test connection” feature to check for SQL Server Express edition, and display a warning since the historic monitoring uses the SQL Server agent, and there is no agent on SQL Express edition.
  • Added a menu item (under the Help menu) to bring up the license agreement, in case someone wants to read it later. This was requested by a customer.
  • Adding F5 support for refresh on the multiple instance reports. Backup Status, CheckDB Status and File Utilization.

Bug Fixes in 2.5.5

  • Excluded offline databases from showing warnings in red if the database is offline for the Backup Status Report.
  • Fixed a bug in the historic connection test dialog. When attempting to check the connection from one server to another there was an error encountered. This referred to the error “Incorrect Syntax near ‘)’.” This has been corrected.
  • Fixed a bug with the multiple instance reports. Hitting the ctrl + left or right arrow keys caused a crash.
  • The option to generate a script to rebuild all statistics needing rebuilding was not working correctly in SQL Server 2005. This has been fixed and now works.
Posted in Database Health Tagged with: , , , , , , ,

Database Health Monitor – May 2017 version released today

Download PDF

Today I had the opportunity to release the May 2017 update of Database Health Monitor. This release is version 2.5.4.

There are some new cross-instance reports available to allow you to check on your backup and CheckDB work on all of the instances that you are connected to with Database Health Monitor.

Version 2.5.4 Release Notes – May, 2017.

Version 2.5.4 is the May, 2017 release of Database Health Monitor

New Features in 2.5.4

  • Upgraded to .net version 4.6.
  • When a new version of Database Health Monitor is available, when you click “yes” to upgrade, it now downloads the installer and runs it rather than redirecting you to the download page. This saves time and makes things easier for updates. Based on a customer request.
  • Additional checks in the QuickScan report for the following:
    • Database Mail not configured
    • Service Broker not enabled on TempDB
    • Added checks for obsolete xp_sqlmaint stored procedure being used in agent jobs to perform checkdb or statistics maintenance. There are better ways to do these.
  • Added the DATA_PURITY option into the CheckDB dialog.

Bug Fixes in 2.5.4

  • Fixed a bug with the Disk Space instance level report crashing on certain collations that use a comma for a decimal separator.
  • Fixed a bug where the table size report was sometimes reporting the wrong number of rows in a table.
  • Bug fix with the backup advisor coming up empty or blank. This was introduced in version 2.5.3, and resolved in 2.5.4.

Visit the Database Health Monitor Download page for the latest update.

Enjoy!

Posted in Database Health Tagged with: , , , ,

DBCC FREEPROCCACHE – What is the impact.

Download PDF

Here is a question that I received from a friend today and I thought it would be a good post explaining the details:

Steve,

I have a question on DBCC FREEPROCCACHE.  I used sp_blitzcache from Brent and it shows that one particular query is horrendous ( I already knew that as I ran it and cancelled it after 10 minutes or so).

It gives me a DBCC FREEPROCCACHE (0x03000700FA15020D5EAA560063A7000001000000000000000000000000000000000000000000000000000000);

What will be the impact of running this?

The application was updated an hour or so before I ran this.

 

My response on DBCC FREEPROCCACHE:

So DBCC FREEPROCCACHE takes the parsed plans and dumps them out of memory forcing SQL Server to recompile the plan the next time a query is run that would have used the plan that was just dumped.  The danger is if you run DBCC FREEPROCCACHE with no parameters, it will dump the entire plan cache forcing every plan to be recompiled, similar to if you had just restarted SQL Server (DON’T DO THAT).

Running DBCC FREEPROCCACHE with a parameter tells it to just dump one plan and recompile it. This is very low impact, and is not that different that if you had just changed the query slightly causing it to recompile.

Here is a post that I wrote a while back on FREEPROCCACHE:

http://stevestedman.com/2013/05/dbcc-freeproccache/

If you suspect that the query is being really slow because of parameter sniffing issues. Using FREEPROCCACHE to dump the bad plan can help with the performance if you are lucky enough that the next time the query is run that it has the “right” parameters to create a plan that runs faster. If that fixes things, then you might think you are in good shape, but if that plan gets pushed out of the cache later and is recompiled with the “bad” parameters then you may end up in the bad performance place.

All that being said, even after freeing the plan from the cache, it may still perform horribly. In that case, it may just be an inefficient query, missing index, out of date statistics, or one of the other common issues that causes queries to be slow.

 

Related Links

Posted in Performance Tagged with: , ,

I’m Presenting at the Compañero Conference In October

Download PDF

I am excited to announce that I will be speaking at (and helping organize) the SQL Conference called Companero Conference in Norfolk VA on October 4th and 5th 2017.

SQL Conference

 

SQL Server Conference for the Accidental, Lone or New DBA

The Compañero Conference is a two-day DBA centered conference bringing together an amazing network of professionals to help you get actionable information and have fun all at the same time.  As you know from our podcast, we love to mix our desire to learn more about SQL Server with a few laughs and make new friends at the same time.  We want to bring this idea to the conference and we hope you will join us.

While we can all learn something, this conference is geared towards DBAs that alone in their organizations, accidently came upon taking care of a database or have responsibilities beyond SQL Server.  While there will be plenty of SQL Server, we want to include other topics you will need to help you be as successful as possible.

Over the next week or two we will be announcing other presenters for the conference. It looks like it is going to be a great conference.

I hope to see you there.

-Steve Stedman

Related Links

Posted in Companero Conference Tagged with:

Podcast Episode 91: DBA Tools – Listen and find out what you are missing.

Download PDF

Today Episode 91 released and it is our pleasure to present this episode on DBA Tools.  DBA Tools is an open source project (http://DBATools.io) that provides a number of powershell scripts to better help the DBA perform common SQL Server tasks. This was a fun episode with the panel of Chrissy, Rob, Constantine, and Aaron who were super excited to talk with us and we loved their energy.

DBA Tools

We all want an easy button.  It is human nature.  We heard quite a bit about how easy PowerShell will make everything, but for those of us who aren’t programmers, it can be a bit intimidating to get started.  The PowerSHell tools from dbatools.io are shaping up to be the closest thing to an easy button for DBAs.  On this episode we invited some of the team to chat with us about their tool, how they got started and the types of problems they are looking to solve.

Episode Quotes

“The features that are now inside of DBA tools, honestly, I would describe them as really awesome.” – Constantine
“I promised you this is the best code ever used and that you will ever have.” – Aaron
“It is important to us that people do feel welcomed and that their codes gets merged in.” – Chrissy

Read more ›

Posted in Podcast Tagged with: , , , ,

My CheckDB Script

Download PDF

From time to time I get asked about checkDB, and there are many solutions out there, but I have one that I generally use that is very simple and does the job.

The script below created a stored procedure in the DBHealthHistory database that can be used to check as many databases as you can get through in a specific time interval. Here it is set to 5 minutes, but that usually gets extended for large databases.  If you set the job to daily, and the job doesn’t get through checking all the databases today, it will pick up where it left off and check the rest tomorrow.

 

Depending on the number and size of your databases you may want to run this more than once a day, or for a longer period than the 5 minutes.

Note: the 5 minute limitation is checked before starting the next check, so if you have a database that takes hours to check, that will kick off in the 5 minute interval and run until completion.

 

Read more ›

Posted in Uncategorized Tagged with: , , , , , , ,

Podcast Episode 90: DBCC CheckDB

Download PDF

This weeks episode of the SQL Data Partners Podcast is Episode 90 released today.

 Episode Quote

“I would say that the bigger issue is probably going to be something like snapshot space for your disk”

“When the rubber hits the road I’m still running CheckDB and CheckTable but it’s all about the process behind it”

 Overview

DBCC CheckDB.  It is a simple command, but one that can cause database contention, dba stress, confusing results, dba elation, and everything in between.  At one time we will all have to face the results of having a corruption issue and we’re delighted to have Sean McCown with us again to discuss the ins and outs of the issues of this command.

We talk with Sean about how frequent issues we have with maintenance like we often we should run CheckDB on our databases or what happens when I can only check one database during my window. While we don’t end up discussing the command as much, we definitely review all aspects of making sure it can run in ALL environments.

Listen to Learn

  • Pros and cons of DBCC CheckDB
  • Scenarios to do CheckDB on databases
  • Issues with CheckDB
  • Running CheckTable
  • Minion Tools

Related Links

Posted in Uncategorized Tagged with: , , , , ,

Finding Blocking Queries

Download PDF

One of the common problems with when diagnosing what appears to be a slow scenario where there may be blocking involved is determining what is blocking on SQL Server.

Here is a query that I like to use.

SELECT * 
INTO #runningQueries
 FROM master..sysprocesses WITH (NOLOCK);


;WITH BlockingCTE as
(
 SELECT q1.blocked as spid
 FROM #runningQueries q1
 WHERE q1.blocked != 0 
 AND q1.blocked not in (SELECT spid FROM #runningQueries q2 WHERE q2.blocked != 0)
)
, recursiveBlocking AS
(
 SELECT b.spid, cast(0 as SMALLINT) as blocked, 
 cast(b.spid as varchar(1000)) as treePath, 0 as level,
 sp1.sql_handle, b.spid as topBlock
 FROM BlockingCTE b
 INNER JOIN #runningQueries sp1 on sp1.spid = b.spid 

 UNION ALL

 SELECT sp.spid, rb.spid as blocked, 
 cast(rb.treePath + '->' + cast(sp.spid as varchar(1000)) as VARCHAR(1000)) as treePath, 
 level + 1 as level, sp.sql_handle, topBlock
 FROM #runningQueries sp 
 INNER JOIN recursiveBlocking as rb ON rb.spid = sp.blocked
)
, topBlockCount AS
(
 SELECT *, count(1) over(partition by topBlock) as NumBlocked 
 FROM recursiveBlocking
)
SELECT DISTINCT 
 tb.SPID,
 tb.blocked,
 tb.treePath as blockingChain,
 tb.level,
 tb.topBlock,
 tb.NumBlocked, 
 LTRIM(REPLACE(REPLACE(st.text, char(10), ' '), char(13), ' ')) as theQuery
 FROM topBlockCount tb
 CROSS APPLY sys.dm_exec_sql_text(tb.sql_handle) AS st
 ORDER BY NumBlocked DESC, treePath
 OPTION (RECOMPILE);


DROP TABLE #runningQueries;

If there is no blocking occurring then this query will return nothing.

 

This script works on SQL Server 2008, 2008R2, 2012, 2014, and 2016.

 

Posted in TSQL Tagged with: , , , , ,

February 2017 Release of Database Health Monitor

Download PDF

Today I had the opportunity to complete and launch the February 2017 version of Database Health Monitor.

I hope you like the latest version. Here are the Release Notes:

Version 2.5 Release Notes – February 2017.

Version 2.5 is the February 2017 release of Database Health Monitor, released on February 19, 2017.

What People Are Saying About Database Health Monitor

  • Outstanding app. Already referred it to several friends in the field. Well I do have to say the changes you have made (regarding version 2.5), seems to have made everything in the interface much snappier. Your work here is quite impressive. (Frank from Texas)
  • Outstanding! Besides enjoying the layout of your app, one thing I really like about the Quick Scan Report is each entry has a corresponding link that discusses the finding so I can go back and review recommendations, cautions, etc. (Michael)

New Features in 2.5

  • Upgraded application to use the .Net 4.5 runtime.
  • Fragmented Indexes Advisor – added buttons to rebuild or reorganize all fragmented indexes. Wow this will save some time when you have a bunch of indexes to defragment.
  • Added historic monitoring for disk space. Still need to add reports, but the tracking is there.
  • Added a menu to the main application window. Removed the big buttons for Connect, Settings, and About, and replaced them with menu items.
  • Added a menu item to link to the feedback survey.
  • Rearranged some of the columns on the What is Active report to make it easier to see what is running right away without scrolling.

Read more ›

Posted in Database Health Tagged with: , , , , , , ,

SQL Compañero

Download PDF

If you have listened to the SQL Data Partners Podcast where Carlos Chacon and I are the co-hosts of the show, you have probably heard us use the term SQL compañero. In case you are wondering where the term comes from and why we use it, Carlos has put together a short video explaining the SQL compañero term.

And if you haven’t listened to our latest podcast on indexing, you might want to check it out. The latest is part 2 in a 2 episode series on Indexing on SQL Server – Indexing Podcast (Part 1, Part 2).

I hope to see you on the SQL Trail.

Related Links

Posted in Podcast Tagged with: , , ,