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.

A great day at PASS Summit today.

I started out the day with the keynote presentation with some great info on PASS and the PASS volunteers, followed by Microsoft presenting  on in memory tables, and native compilation for stored procedures, described and demonstrated as “Wicked Fast”.

Next I attended a fabulous presentation from Erin Stellato on Extended Events, an improved replacement for the SQL profiler. I learned a few things that I will use regularly to troubleshoot performance issues.

After that Glenn Berry presented on using DMVs to diagnose performance problems. This was one of my favorite sessions for the day. Glenn did a great job presenting about 60 queries that can be used to track different performance and configuration problems on SQL Server.

During break I then had a chance to talk to the Microsoft SQL Server team to get a better understanding of the execution plans associated with recursive CTEs.  I will have to blog about this one later, but after talking with 4 different people from Microsoft, I have a better understanding of exactly what the execution plan shows for the recursive CTE.

The next session was on using table and row compression to speed up SQL Server. Using compression you can reduce the number of logical reads because the data is compressed and less pages are hit when running a query. This does however increase CPU load, but it looks to me like the small increase in CPU load would be worth it.

At the end of the day during the Exhibitor Reception I had my first book signing on the CTE book at the Joes2Pros booth. We had 30 books to give away, and when the book signing started, there were already 30 people in line to get a book. It was great to talk with people about what they are using CTEs for.

CTE_UK

I also had a chance to meet Pinal Dave and talk with him for a bit.

Another great day at PASS Summit 13.

Day 1 at PASS Summit 13

After the red eye flight last night I finally arrived at PASS Summit 13, and picked up my registration packet.

On the cab ride from the airport I had a great lecture (not much of a discussion) from the cab driver on politics and the Affordable Care Act. When I finally asked him what it was going to change for him he said “Nothing, my wife is a nurse and we have great insurance”. It was an interesting ride.

Here is the main entrance to the convention center.

image

Today was the first day of pre-conference sessions, which I didn’t register for due to my travel plans. Tomorrow I start the day with my first pre-conference session titled “Making SQL Server Apps Go Faster” by Brent Ozar, Jes Borland and Kendra Little.  I am looking forward to that session to see what I can learn to help the applications that I work on run faster.

wpid-20131014_124713.jpgEven thought the conference hasn’t started yet, only pre-cons going on today, the area was buzzing with activity.

Tonight I am going to attend the Networking Dinner at the Buffalo Wild Wings hosted by Steve Jones and Andy Warren. It is a networking event to get to know other people in the world wide SQL family.  I am looking forward to it.

The weather in Charlotte NC looks a lot like the weather back home in Bellingham WA, a bit gray and slightly rainy.