Skip to content

SQL Server

Database Health Monitor – Version 2.3 Released

With over 7500 downloads worldwide to date, I have just released the next installment of the FREE Database Health Monitor application.

Here what one person had to say about it “Excellent – It’s the first tool I open every morning to see what is going on in my production environment! Thanks for the great work!”

Download today:

 

Database Health Version 2.3 Release Notes

Released March 20th, 2016.

It has been about 3 months since the last release, and I have added several new features, and many bug fixes.

New Features

  • Reducing the number of different database connections used by Database Health Monitor. Reusing existing connections where applicable.
  • Backup Report
    • Database restore script for the backups report now includes the “RESTORE HEADERONLY” option.
    • Updated the restore script to include a FILELISTONLY option to check on file locations before restoring.
    • Recent backups are now shown in green so it’s easy to see which databases have recent backups and which do not.
    • Added column to show the size of the backup.
  • CheckDB dialog: Added the instance name to the CheckDB dialog so that you can know which instance the CheckDB is being run against.

Read More »Database Health Monitor – Version 2.3 Released

SchemaDrift – Database Schema Differences – My Weekend Project

This weekend I had some spare time, so I started coding on a new project on Friday night. This project is something that I intend to include in the next release of Database Health Monitor. THis project allows users to compare the database schema between two SQL Server databases.

As you read this, consider two questions that I will ask again at the end of the post, I would really love some feedback:

  1. Would you use this?
  2. Would you be interested in helping me test it out as an early release?

This was inspired by working with clients who need to compare the schema between different SQL Servers, either between multiple production servers with the same schema, or between test and production servers.

I know there are other tools out there that do this, but its not easy to convince a client to purchase 3rd party tools, so I am left not having a good tool to do this.

This is just a start, I still have a great deal of work to do, but its the foundation for me to build upon.

How it works:

  • Connect to a SQL Server and choose a database to compare (known as the source).
  • Connect to another SQL Server and choose another database to compare against (known as the destination).
  • Click the compare button.
  • The schema is then compared between the two servers (Tables, Sprocs, Functions, Users, Indexes, etc…)
  • Results are sorting into 4 categories.
    • Matched between the two servers.
    • Exist on both servers, but something is different.
    • Only on the Source Server.
    • Only on the Destination Server.
  • You can the browse the code behind those objects, and see the differences.

Database Schema Drift The Demo:

To start with we just run the program.

SchemaDrift Database Schema Comparison Diff

Next we click the connect button under the Source Server section. For the first server I am going to connect to a SQL Server 2008 R2 instance with SQL credentials.

Read More »SchemaDrift – Database Schema Differences – My Weekend Project

Optimize for Ad Hoc Workloads

The Optimize for Ad Hoc Workloads server configuration can improve performance, and is extremely unlikely to negatively impact performance.
This was a new feature that was introduced in SQL Server 2008, and as with many new features in SQL Server, it is not turned on by default.

What Optimize for Ad Hoc Workloads Changes.

Without the Optimize for Ad Hoc Workloads feature enabled, when a plan is compiled, it is kept around in memory (cached) until something else pushes it out of the cache.

With the Optimize for Ad Hoc Workloads feature enabled, the first time a plan is compiled, it is not kept around in cache, rather a stub is kept around to indicate that they query has previously been complied. The second time the plan is compiled it is kept around in memory. Over time you end up with fewer one time use queries in memory, reducing the memory impact of the plan cache. All other multi-use ad hoc plans will need to be compiled twice on the first and second time that they are used.

What it doesn’t to

The Optimize for Ad Hoc Workloads server configuration doesn’t change the size of the plan cache in SQL Server, it does however prevent the single use ad hoc plans from using up plan cache space, making more space for other cached plans.

Read More »Optimize for Ad Hoc Workloads