Database Health Monitor Version 2 Released

After 4 years of beta and 5500 installs of Database Health Monitor beta releases in the last 2 years, Database Health Monitor version 2 is finally complete. It is no longer in beta. If you are curious about the history of Database Health Monitor, take a look at my post from earlier in the week.

Here is a preview of one of the latest reports added in the Version 2.0 release of Database Health Monitor.

CpuHeatMap

 

Also new in the version 2 release is the I/O By Database report as shown here. This report allows you to quickly find the databases that have the most I/O on your SQL Server.

IObyDatabase2

Here is a list of the new features and bug fixes that have been implemented since the last Beta release.

New Features

  • Removed Beta 3 month expiration.
  • Added the Database I/O overview panel.
  • Added the I/O by database instance level report.
  • Database overview reports on databases set to autoshrink.
  • Added a check for those using Veeam backups. There is a configuration where the Veeam backup breaks the log restore chain. This is now shown in the errors and warnings page.
  • Filtered snapshot backups from the backup restore chain.
  • Backup report now differentiates copy only backups from normal backups.
  • Added ctrl+a for select all on the historic query advisor.
  • Fixed a bug in the log restore chain script in the backup report.
  • Added the database CPU heat map to show the average CPU load hour by hour for the last 7 days.
  • Added the instance level linked servers report to display linked servers.
  • Added documentation at http://DatabaseHealth.com for many of the newer reports that were missing help.

Bug Fixes

  • Renamed the “DBCC Last Known Good” report to the “DBCC CheckDB Last Known Good” report, this is a more accurate description.
  • Fixed bug with the linking of the Server Connection report to the connections for a database. The link redirected to the database overview page rather than the connections page.
  • Removed filtered indexes from the duplicate indexes report. This was causing filtered indexes to show up as duplicates.
  • Fixed bug with the historic waits advisor was displaying 2 days in one slot on the chart.
  • Bug fixed with the progress bar not clearing on the connection report when there are no connections.
  • Fixed linking from the needs parameters report on the server overview page.
  • Fixed bug with click through on database I/O on the server overview panel.

 

Its free, really free, so take a look at the latest release by downloading from DatabaseHealth.com

Enjoy,

-Steve Stedman

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

Interview with Brent Ozar

Almost a month ago Brent Ozar did a blog interview with me regarding the Database Corruption Challenge so I thought I would return the gesture and do the same for him. I ended up going to the Bellingham SQL Server Users Group (PASS Chapter) to build the list of questions. The members of the group helped build the questions, and here is what we came up with, including responses from Brent.

Brent is a Microsoft MVP, Microsoft Certified Master of SQL Server, and published author. He lives in Chicago and is the founder of Brent Ozar Unlimited.

Question: For the self-taught or accidental DBA where do you suggest they start to improve their SQL Server Skills?BrentOzar

Brent: DBAs get in the most trouble when they assume the backups are working. They’re probably not. Go set up a development server or VM, and restore last night’s backups onto it. Figure out what it would take to go live on that newly restored server. Rehearse it a few times, get confident in it, and then build a little checklist with the length of time it will take. Show it to your manager as proof that you’re working to keep the company’s data safe.

After that, it’s so tempting to get caught up just poking around in metrics. Go to your manager or your top power user and ask, “What do you want to do, but the database is holding you back?” These are the people who will give you a raise, and these are the reasons they’ll give it to you. Help them help you.

Read more ›

Posted in Interviews Tagged with: , , , ,

Database Health Monitor Version 2 Almost Done

Over the last 4 years I have been working on the Database Health Monitor. I am about a week away from releasing Version 2, which will finally be out of the beta process. In preparation for the release I am sharing some of the history of this program.

In 2011 version 1 was released as a set of my favorite monitoring queries that had been formatted and made available through SSRS. As people started to try it out in late 2011 and early 2012, I quickly discovered that the process for distributing shared queries via SSRS reports wasn’t very easy for the people trying to use them. Many people quickly discovered the difficulty of installing these reports. The set of reports looked something like this.

OnetimeUseQueries

 

There were 10 reports that were linked from the top panel in the SSRS user interface. Although SSRS has some great features, it was challenging to switch database connections and monitor multiple servers.

In April of 2012 I gave up on the concept of using SSRS reports, and converted all the queries into a windows application and started enhancing the program. In September of 2012, I finally had the Beta 1 release of Database Health Reports out. It was a bit rough in the beginning, but from 2012 to 2015 through a series of almost 20 beta releases labeled names like Beta 1, Beta 8.2, Beta 10.1, and so forth I kept enhancing the program. Here is an early screenshot of somewhere around Beta 1 or Beta 2.

Read more ›

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

I will be presenting at PASS Summit 2015 in October

The speaker lineup for PASS SUMMIT 2015 was announced today and my Advanced Common Table Expressions session was accepted.

I'm Speaking Graphic_Large

 

Here is the abstract:

You might have been introduced to Common Table Expressions (CTEs) and understand the WITH syntax, but want to know more. Learn how recursive queries work with CTEs and how to display hierarchical data. Did you know that you can INSERT, UPDATE, and DELETE data from CTEs? This session covers some common use cases for CTEs, including finding holes in patterns, finding and removing duplicate data, string parsing, and more. Get an in-depth understanding of the performance behind a CTE and learn when a CTE is the right (or wrong) solution. Finally, take a look at some classic recursive algorithms and how they can be implemented with CTEs.

 

Earlier this year I created my Epic Life Goals list (level 1) which included speaking at PASS Summit. It looks like I will be able to check that one off in October.

Here is a link to the speaker line-up check it out, there are some great speakers listed there. http://www.sqlpass.org/summit/2015/Sessions/Speakers.aspx

If you are going to PASS Summit then I will see you in October.

Posted in PASS Summit Tagged with: ,

Database Corruption Challenge #8 has Ended

With many participants and 16 who correctly solved Corruption Challenge week 8, the challenge has come to an end. The overall winner was Rob Farley, and there were several new participants this week. The scores page has been updated.

Some participants called this the toughest challenge yet which makes my job even harder to come up with something more challenging for next time.  There were a couple interesting twists in this one. One was the way the corruption caused issues with DBCC Page. On SQL Server 2014 DBCC Page on the corrupt page with the output parameter of 3 didn’t work, and the only output option that worked was option 2. However on SQL Server 2008R2 and 2012, none of the DBCC Page output options worked on the corrupt page, which forced some to open the database file with a hex editor to pull the data in from the corrupt page.

The one thing that I didn’t judge anyone badly with was the capitalization of the person in the corrupt record.  I accepted EMMA, E WILLIAMS, and Emma, E Williams, and Emma, E WILLIAMS all as correct answers, as to figure out any of those it took the same amount of work.

 

The solution by Rob Farley:

 

In Record1035, Emma E Williams’ record had the problem. Her correct record is:

-9223372036854775435, -9223372036854775688, 10730, EMMA, E, WILLIAMS, Chief, (null), 20141030

 

I got the values out by just looking in page 249. The structure was easy.

 

There are some problems with the NCIX too, on both Record1035 (Jakob B Miller) and Record1000 (WA980), but they could just be rebuilt.

 

(eg, page 244 was corrupt here, where Jakob was messed up as “SMIIob”)

000000001153A1F4:   6b6f6241 534d4954 48361902 00000000 00800400  kobASMITH6……….

000000001153A208:   f0030019 001a0020 534d4949 6f62424d 494c4c45  ð…… SMIIobBMILLE

000000001153A21C:   52365b01 00000000 00800400 f0030019 001a001f  R6[………ð…….

 

And page 191 was corrupt here:

000000001153AF78:   19001b00 23005741 38383157 41323031 31303732  ….#.WA881WA2011072

000000001153AF8C:   38361703 00000000 00800400 f0030019 001b0023  86……….ð……#

000000001153AFA0:   00574138 38365741 32303132 30343231 361c0300  .WA886WA201204216…

000000001153AFB4:   00000000 800400f0 03001900 1b002300 57413935  …….ð……#.WA95

000000001153AFC8:   37574132 30313330 36323536 22030000 00000080  7WA201306256″…….

000000001153AFDC:   0400f003 3132311b 00230057 41393830 57413230  ..ð.121..#.WA980WA20

000000001153AFF0:   31323131 30363623 03000000 00008004 00f00300  1211066#………ð..

000000001153B004:   19001b00 23005741 38393457 41323031 30303232  ….#.WA894WA2010022

 

Anyway, I think that’s about it. I recreated the NCIX on Record1000, and truncated Record1035 once I had its rows in a different spot.

 

The checksums submitted were.

1000       526         0              72766    1051256                0              0              0              53

1000       0              0              877708  -2030100812       2              -1756609606       0              0              5920

And that is how Rob solved it.

 

Nice work to everyone involved. I will post some other solutions over the next week.

Related Links

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

Deleting from a CTE with an EXISTS statement

During my 24 Hours of Pass presentation on Advanced CTE’s today I was asked the question about deleting from a CTE when it uses an EXISTS statement that queries another table. I figured I would create quick blog post to show the example.

First some background when using the delete statement with a CTE you can’t delete if the query inside of a CTE references multiple tables. For instance:

;WITH CustomerCTE AS
( 
	SELECT c.*
		FROM Customer AS c
		INNER JOIN SalesInvoice AS si ON si.CustomerID = c.CustomerID
		WHERE c.LastName like 'Williams'
) 
DELETE FROM CustomerCTE;

Will return an error like this:

CteDelet1

 

An the question was asked around referencing a second table using EXISTS, for instance:

;WITH CustomerCTE AS
( 
	SELECT c.*
		FROM Customer AS c
		WHERE EXISTS (SELECT 1 FROM SalesInvoice AS si 
					   WHERE si.CustomerID = c.CustomerID
						 AND c.LastName like 'Williams')
) 
DELETE FROM CustomerCTE;

Since the CTE using the EXISTS example only references one table in the CTE query (technically the query in the exists is a correlated subquery or derived table), you are able to do it just fine.

CteDelet3w

So if you can restructure a query inside of a CTE into an EXIST clause being used for DELETE, you can make it work as a way to get around the single table in a CTE delete statement error.

Thanks for asking.

Posted in CTE Tagged with: , , ,

24 Hours of PASS – Advanced CTE Presenation

Today I am presenting on Advanced Common Table Expressions as part of the 24 Hours of PASS conference. It is being webcast so check it out if you can.

The outline for the presentation will be the following:24HOP_GOC_SpeakerButton

  • Recursive Queries
  • Hierarchical Recursive Data
  • Manipulating Data
  • Common Use Cases
  • CTE Performance Considerations
  • Classic Recursive Algorithms

The sample code and slides are available to download here:

AdvancedCTEs.zip

You can download the samples and follow along if you would like.

Enjoy!

Related Links:

Posted in CTE Tagged with: , , , , , , ,

Extra clue added for Database Corruption Challenge Week 8

Several participants have stated that week 8 is the most difficult week yet. However it is possible to recover all of the corrupt data.

For anyone having difficult with Database Corruption Challenge week 8, I have added an extra clue.

Click through on Database Corruption Challenge #8 and page down to the extra clue section.

You can still try to solve without using the extra clue, but it is there if you need it.

Week8_clue_details1

 

Good luck to everyone participating.

Posted in Corruption Tagged with:

Advanced Common Table Expressions – 24 Hours of PASS

On Wednesday June 24th at 6:00pm pacific time or( 25 Jun 2015 01:00 GMT )

will be speaking at the 24HOP (24 Hours of Pass) conference which is broadcast online.

I will be presenting on Advanced Common Table Expressions, and it is going to be fun.

24HOP_GOC_SpeakerButton

 

Here is the session abstract.

You have been introduced to Common Table Expression, you understand the WITH syntax, but you want to know more. Learn how to recursive queries work with CTEs and how to display hierarchical data. Did you know that you can INSERT, UPDATE and DELETE data from CTEs. Some of the common use cases for CTEs will be covered including finding holes in patterns, finding and removing duplicate data, string parsing, and more. See how CTEs compare to SQL Server 2012 offset and fetch paging techniques. Get an in depth understanding of the performance behind a common table expression. Understand when the CTE is the right solution, and the wrong solution to use. Finally we will take a look at some classic recursive algorithms and how they can be implemented with CTEs. Take away several CTE samples that you can use to extend your TSQL query abilities.

 

It should be a good time.

 

Posted in CTE, CTE Book Tagged with: , , , , , ,

Corruption Challenge #8

Welcome to the eighth week of 10 in the Database Corruption Challenge (DBCC), this is an about weekly competition. Here is how it works; I have created a corrupt database, hopefully more corrupt or more interesting than the previous week. I then solved the corruption myself in order to prove that it is possible to fix, without data loss. There is a total of 4 points available to be earned this week.

  • 1 point extra for being the first correct answer. Already taken by Rob Farley.
  • 1 point to all who get the correct answer.
  • 1 point extra if you get the correct answer before any hints are given.
  • 1 point extra if your solution is correct the first time you submit it to me.  (check your work)

Week8

Corruption Challenge General Info

The challenge will be to download the corrupt or somehow damaged database and attempt to recover it. If you can recover it, please send me the steps you used to recover the database, along with some proof that the database has been recovered. The goal each week will be the following:

Read more ›

Posted in Corruption Tagged with:

Newsletter Signup

Stay informed of Database Corruption Challenge events, and other things happening at SteveStedman.com with my newsletter.
Newsletter signup form.

Stedman Solutions

StedmanSolutionsRemoteSkilledDBA