SQL diagnostic tool - Database Health Monitor

[gglidden]

Hi.

 

This is more of a give back post, in that I've come across a useful tool that I've used in working on SIMS/PARS performance issues that may be of use to the Edugeek community. Not sure if it has been mentioned before, not a regular on the website.

 

The software is called Database Health Monitor, available at - Database Health Monitor. I regularly use it to look at the state of SIMS indexes and statistics as part of the performance monitoring tasks. Just as a tool to look at your server and what it's doing is useful in itself, but as the attached screenshots show, useful in looking at stats and reindexing.

 

One very useful tool is the Quick Scan Report, which pointed me to setting up multiple tempdb database files to help general SIMS performance and balancing out the work queues - see Quick Scan Report - TempDB only has a single data file - Database Health Monitor.

 

I'm using this tool to come up with a routine SIMS database maintenance task list for technical staff here to use - along the lines of backups OK and tested, LDB file size, statistics re-run, top 10 indexes redone, etc.

 

Note the tool is free and I've not been paid anything to endorse this tool or have any personal dealings with the company.

 

As every, be careful using any SQL Server database tool and I don't accept any liabilities for mis-use, etc. etc.

 

Glenn.

 

[Tall_Paul]

Something to possibly be aware of is that Avast blocked this from installing for me.

 

I've submitted the file to Avast for their labs to check it out.

[Geoff]

Also worth noting is the MS SQL Server 2012 Best Practice Analyser.

 

https://www.microsoft.com/en-gb/download/details.aspx?id=29302

 

It will tell you if you set anything up wrong.

[jinnantonnixx]

This looks like a very useful tool.

 

Definitely useful to keep on eye on when the stats were updated.

 

If I may inject a caveat; it's to watch for red herrings. The index highlighted with high fragmentation is very small. Only 498 pages, and each page is only 8k. This is likely to be held in memory and the fragmentation practically irrelevant. Once you're into many thousands of pages, it's a different story. Even if you defragmented it, it would soon split (at least 50% fragmentation) when a new entry is inserted as it's a clustered index.

Edited January 29, 2016 by jinnantonnixx

[Geoff]

Also if your Tier 1 storage is flash based. Fragmentation doesn't matter.

[jinnantonnixx]

Flowchart from https://www.brentozar.com/archive/2016/01/should-i-worry-about-index-fragmentation/