Database Health Monitor – Beta 8 – Soft launch

Today I launched Database Health Reports Beta 8 as a soft launch. What I mean by the soft launch is that other than updating the DatabaseHealth website and this blog posting, I haven’t done much to promote it.  Why you might ask?  Due to it being a holiday week between Christmas and New Years, there are many people out of the office. I figured I would wait until after January 1st to make a big splash.

Beta 8 is out. The biggest change is the charting. About 75% of the charts in the system have been completely rewritten. I was using a charting module that didn’t allow for the flexiblity that was needed to make the charts look the way I wanted. In September right after the Beta 7 release I started rewriting the charts from scratch.  Between now and the next Beta I will work to get more of the charts converted over to the new look.

Here is an example of some of the newer charts. The red and green bars on the plan cache are used to indicate change.  Red indicates that the value was worse, and green indicates that it improved or stayed the same. The CPU by database chart was rewritten to make better use of the available space.

New Charts

 

Also shown in the above screen shot is the new Server Configuration panel with details on the specific version of SQL Server, when it was installed, the number of processors and more.

Beta 8 Release Notes

Beta 8 Released 12/29/2013.

The following changes have been made since Beta 7. The big feature in the Beta 8 is the rewrite of many of the charts.

New Features

  • Renamed to Database Health Monitor, attempting to avoid confusion with SSRS Reports.
  • Additional checks for obsolete or unusual settings (SHRINK_DATABASE, TORN_PAGE_DETECTION).
  • Blocking reporting with hierarchical drilldown on the blocking queries.
  • Server details panel showing logical and physical cpu counts, SQL Server Start time, SQL Server install data, Server Name, SQL Server version info, and information on real or virtual server.

Bug Fixes

  • Multithreading the re-connect of databases, vastly improving the startup time if one or more databases is not available.
  • A large amount of the project has been refactored to help mature the product and allow for additional feature growth, and reduce bugs.
  • Improved background threading.
  • Updates and bug fixes on SQL Technical Debt.

SQL Server Performance for Developers

For the .NET programmer, Visual Basic programmer or PHP programmer, if you are accessing a SQL Server database there are some things you should know to performance tune your queries. Learn how to improve query performance with Indexes, how to properly use parameterized queries, using the query analyzer, and avoiding common SQL Server performance pitfalls.

PerformanceTuning

This presentation is a lot of fun. This is one of the few presentations where there is audience participation. Four luck participants will be selected to help simulate the work that SQL Server does when accessing tables structured with different types of indexes.

Download the presentation here:

PerformanceTuning.zip

If you are interested in performance, please take a look at the Database Heath Monitor.

SQL Server FILESTREAM and FileTables

Monday: Using FILESTREAM and FILETABLES in SQL Server

Configuring and Using FILESTREAM and FILETABLES in SQL Server.  Developers love to use SQL Server to store files, but this causes headaches for the DBA, finally a reasonable solution for file storage in SQL Server FILETABLES and FILESTREAM. SQL Server 2008 and 2012 add the new features of FILESTREAM and FILETABLES. Learn how to configure and manipulate files in your SQL Server with FILESTREAM, then learn how to do everything that FILESTREAM sounds like it should do with FILETABLES. With FILETABLES inserting is as easy as drag and drop.

Here is the outline for the presentation:

  • FILESTREAM – SQL Server 2008 and newer
    • Introduction and Configuration
    • Creating a Table Using FILESTREAM
    • TSQL FILESTREAM Access
  • FileTables – SQL Server 2012 and newer
    • Configuring and Creating FileTables
    • Insert, Update and Delete with a FileTable
    • Drag and drop with the file system

 

Here are the slides from the presentation and the supporting sample files.

Filestream and FileTables.zip

 

Speaking at Vancouver DevTeach this week.

This week I will be attending and speaking at Vancouver DevTeach. This event taking place on December 2nd to 4th 2013 at the Vancouver Sheraton Wall Center Hotel. Monday and Tuesday I have morning presentations which leaves the rest of the day to attend a few other sessions.

DevTeach

Here are the sessions that I will be presenting.

Monday: Using FILESTREAM and FILETABLES in SQL Server

Configuring and Using FILESTREAM and FILETABLES in SQL Server.  Developers love to use SQL Server to store files, but this causes headaches for the DBA, finally a reasonable solution for file storage in SQL Server FILETABLES and FILESTREAM. SQL Server 2008 and 2012 add the new features of FILESTREAM and FILETABLES. Learn how to configure and manipulate files in your SQL Server with FILESTREAM, then learn how to do everything that FILESTREAM sounds like it should do with FILETABLES. With FILETABLES inserting is as easy as drag and drop.

Tuesday: SQL Server Performance for Developers

For the .NET programmer, Visual Basic programmer or PHP programmer, if you are accessing a SQL Server database there are some things you should know to performance tune your queries. Learn how to improve query performance with Indexes, how to properly use parameterized queries, using the query analyzer, and avoiding common SQL Server performance pitfalls.

Looking at the lineup of other speakers this looks like it will be a great few days of education. It is nice to attend a conference so close to home, no flights involved for me this time.

Introduction to CTEs Slides and Sample Queries

Today I am at SQL Saturday Portland Oregon, and at 9:00am I am presenting the Introduction To Common Table Expressions session:

Introduction

Here is the abstract:

Have you ever wanted to create a recursive query, but didn’t see how to do it. With the Common Table Expressions session you will learn everything needed to start using CTE’s for recursive queries, as temporary views, and to use the result set multiple times in the same query. Learn how simplify query syntax using CTE’s. One of the most overlooked features of SQL Server is the CTE which not only simplifies the query, but gives you the ability to do things that would otherwise be impossible (or at least very challenging) with SQL Server. The class is designed for people who haven’t used CTE’s before, or for those who want to learn the basics of CTEs including data paging. This session pairs well with the Advanced Common Table Expressions session.

This session will include the following topics:

  • Introduction to Memory Tables and CTEs
  • Simple CTE
  • CTE Instead of a Derived Table
  • Multiple CTE in a Query
  • Data Paging
  • CTEs in Stored Procedures, Functions and Views
  • Introduction To Recursive CTEs

At 10:45 I will be giving the Advanced Common Table Expressions Session.

Download the presentation here:  Introduction to CTEs.zip

Related Posts:

SQL Saturday #265 in Portland Oregon

I will be attending SQL Saturday #265 in Portland Oregon on November 16th 2013.

This will be my second time attending SQL Saturday in Portland, and it is looking to be a great event. There are some great speakers on the line up that I am looking forward to learning from.

I will be presenting 2 sessions on Common Table Expressions.

Common Table Expressions – Introduction

Have you ever wanted to create a recursive query, but didn’t see how to do it. With the Common Table Expressions session you will learn everything needed to start using CTE’s for recursive queries, as temporary views, and to use the result set multiple times in the same query. Learn how simplify query syntax using CTE’s. One of the most overlooked features of SQL Server is the CTE which not only simplifies the query, but gives you the ability to do things that would otherwise be impossible (or at least very challenging) with SQL Server. The class is designed for people who haven’t used CTE’s before, or for those who want to learn the basics of CTEs including data paging. This session pairs well with the Advanced Common Table Expressions

Common Table Expressions – Advanced

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. This session pairs well with the Introduction to Common Table Expressions session.