Advanced CTEs Slides and Samples

Today I presented on Advanced CTEs at SQL Saturday Denver.
sqlsat190_speaking

Here are my slides and sample code for download: Advanced CTEs Denver

Thanks to everyone who attended, I hope you enjoyed the presentation.

Don’t forget to try out the Database Health Reports.  Here is what some people are saying about it:  Raving Fans

Beta 7+ Filter Technical Debt by Debt Type

One of the ideas behind Scrum software development is the idea of “Release early, Release Often and Get Customer Feedback.”

After the release of Beta 7 of the Database Health Reports earlier this week I received an email with a really great suggestion that I just had to implement.  The suggestion was to be able to click on the debt type and get the list of items with that debt.

DebtTypeFiltering

 

If you are running Database Health Reports with a version (as shown in the title bar) less than 1.99.7.503 and you want this new feature, just go to the download page and download the latest version.

Suggest An Idea for Database Health Reports

As I get ready for Beta 7 to launch I have decided that for Beta 8 I will focus on feature suggestions from the users of the Database Health application.  I have created a feedback system for users to suggest their ideas, then they can be voted on with the star rating. Users can comment on the ideas.

submitYourIdeasIf you would like to see any new features added into the Database Health Reports, please use the idea suggestion feature at the Database Health website.

Here is the link to submit a new idea:

http://databasehealth.com/is/new-idea/

SQL Server Technical Debt

As Beta 7 of the Database Health Reports approaches is just three weeks, the big new feature that has been added is the SQL Technical Debt Analysis.

TechDebtGraphic

The SQL Technical Debt Analysis a great check for those areas that are known problems when working on SQL Server.

TechDebtGraphic2

But what if I like the GOTO statement?

All of the Database Health Reports checks for Technical Debt have specific over-ride options. If your coding standards state that using the GOTO statement is appropriate, then just turn that option off.

What if I use NOLOCK everywhere to improve performance?

WITH NOLOCK can be a very dangerous option if it is used incorrectly. It can however be extremely useful if used correctly in the right situation. If you have the belief that NOLOCK should be used on every query as the “GO FASTER” option, you may want to do some research on the negative issues around using NOLOCK that way.  If you have a technical standard that states when NOLOCK should be used and when it should not and you are aware of the issues, then you can just turn off the check for NOLOCK in the Database Health Reports.

Sustainable Code

Is there an section of code in your database that you know you shouldn’t touch, for instance if you ever need to make changes, or fix a bug that specific area is known to be much more expensive to maintain than other areas of your system. Areas like this that are difficult to sustain and work with over time are often times the areas with the highest technical debt.

Transparency

There are some who love transparency in the process, and some who don’t.  Some people don’t like to have their work analyzed or criticized. Technical Debt reporting allows you to establish a baseline for the quality of your T-SQL code and database design, and then determine are you getting further into debt, or is it improving.

Database Health Reports

Here is a preview of the Technical Debt analysis coming in the next beta of the Database Health Reports.

TechDebt1z

See Also:

One Thousandth Download of Database Health Reports

Today marks the 1000th download of the Database Health Reports in the last 11 months since Beta 1 was released. I thought this would be the perfect opportunity to recap on the history of the database health report application.

First I would like to thank everyone who has downloaded Database Health Reports application.

Thank You!

Here is a quick history, before it existed as an installable application, Database Health Reports started out as a set of interlinked SSRS reports (Version 1.0).

Database Health Reports 1.0 – Originally a set of SQL Server Report Server reports.

  • February to October 2011 – Initial planning and development – determining what queries would be useful in the Database Health Reports.
  • November 19, 2011 Database Health Reports (SSRS) initial release.
  • November 2011 to March 2012 – Adding more reports, and improving the overall interaction.
  • April 2012 – The realization that the install process and distribution of the SSRS reports was going to be too difficult to support. At this point I started working on version 2.0 as an application rather than a set of SSRS reports.

Database Health Reports 2.0 – Application – Beta towards version 2.0

  • April 2012 to August 2012 – Preliminary development of the Beta 1 release.
  • • September 1, 2012 – Beta 1 Release – Just getting the initial product out the door. This was a scary release, wondering if anyone would download it, or if anyone would like it.
  • • September 30, 2012 – Beta 2 Release – Added a few more reports and advisors, including the Unclustered tables report.
  • • October 2012 – Major redesign and addition of the historic wait monitoring.
  • • November 18, 2012 – Beta 3 Release. The big new feature was historic wait monitoring.
  • • November 21, 2012 – Beta 3.1 release. There were some problems with Beta 3 that needed to be addressed quickly. This one was a bit embarrassing, but I learned a lot about the release cycle.
  • • January 10, 2013 – Beta 4 Release. Including a rewrite of the setup/install program to make the program easier to get installed. Removed support for SQL Server 2000, it never actually worked, but people didn’t know.
  • • March 3, 2013 – Beta 5 Release Added the Big Clustered Indexes Report, along with various bug fixing. Beta 5 was the first beta where the timeout period was extended from 2 months to 4 months.
  • • June 3, 2013 – Beta 6 Release – I considered this the general cleanup beta, fixing things should have been done in the beginning, better clipboard support to copy for all the grids, sorting on all the grids, additional setting to configure the application behavior, and many bug fixes.
  • • July 2013 – New website. The hosting company where I had the DatabaseHealth.SteveStedman.com website hosted was not performing well. Pages were very slow to respond, and there were a couple of outages. I moved the website to a new hosting company which is must faster, and has better availability. The new website is http://DatabaseHealth.com

All of these Betas have been building up to the Version 2 release, which is still a few more Betas away.

I often get questions about the timeout period in the Database Health Reports. People ask the question of “its free, but it expires, what gives?” Part of the beta process and giving it away for free is that I don’t have a huge budget for a support team to diagnose what might be going wrong on an older version of the beta, or what might have been a bug 8 months ago, that is no longer a bug in the current version. To mitigate this I ask that everyone using the product upgrades as newer versions come out. Now once a new beta version is release it is about every 3 months, and it the old version will usually run for about 2 months once it is expired. This just keeps you, the customer from running old code with bugs. The application has come a long way in the last year, and it will continue to evolve.
Database Health Reports currently supports SQL Server 2005 to SQL Server 2012, Beta 7 will add support for SQL Server 2014 CTP1.
Beta 7 is about 30 days away from release. There are a few bug fixes and minor enhancements in Beta7, but the big new feature is the addition of technical debt analysis.

Technical Debt Analysis – coming soon

Technical Debt is a programming concept that is also known as code debt, it applies to any type of code that is not written to standards. The debt is looked at as the cost to bring the code up to standards. As part of my recent Scrum Master certification course (yes I am a certified scrum master now) the concept of technical debt came up. Technical debt is something that I have been involved with arguing with between developers and DBAs on many occasions, but until the Scrum Master certification course, the concept of technical debt never really stuck for me. In the past when technical debt was argued, it was often times used to write off those areas of code that developers and DBAs just don’t enjoy working on, but there is more to it than that. Analyzing technical debt is like getting a report card on the work you do, or on the systems you have to maintain.
The concept of Technical Debt applies closely with Agile software development, but also applies to database development. There are many tools available to report on technical debt for C#, PHP or other programming languages, but there isn’t much available to manage the T-SQL technical debt or general database design technical debt on SQL Server.
Some of the traditional technical debt causes are:

  • Poor design.
  • Lack of coding standards.
  • Different conflicting coding standards over time.
  • A rush to get things done rather than focusing on stability and sustainability.
  • Non refactoring code as it grows.
  • Evolving code or environments to just make things work, rather than to make things work well.
  • many more…

Reducing technical debt will lead to a more sustainable environment.

Beta7 will add a new report which analyzes your technical debt and provides the areas needing the most improvement. Here is a preview of the technical debt report.

TechDebtPreviewClick image to view the full resolution.

Coming Soon – September 2013.

Any of these technical debt items can be overridden if you have a coding standard that supports something else. For instance if you don’t agree with the coding standard that keywords should be uppercase, thats OK you can just turn off that technical debt check.

What Is Coming In the Next Year

My plan is for another Beta ever 60 to 90 days, with a focus first on stability and bug fixing. The second focus will be on adding functionality into the existing features, for instance more details on the historic monitoring, and more checks on the technical debt feature. Then the focus will be on adding new features.
If you like what you have seen in the last 11 months, stick around the next year should be just as feature rich as the first.

Thank You!

Again, thanks to everyone who has been using the Database Health Reports, thanks to all those who reported problems, and have been patient enough to work with me on fixing bugs. Thanks to everyone who was spread the word of the Database Health Reports to help grow the user base.
Thanks you!

-Steve Stedman
Founder, Developer, Tester and Support Technician – Database Health

Working on the T-SQL Technical Debt feature in the Database Health Reports

As part of my contribution back to the SQL Server community I am adding a new feature into the free Database Health Reports application.

I am currently working on adding a new feature into the Database Health Reports to analyze T-SQL Technical Debt.

If you are not familiar with technical debt, it applies to all aspects of programming or technology. This is going to be a new report added into the Database Health Reports in the next beta release (late summer).

Here is a preview of the technical debt report.

TechDebt1

The report provides an estimate for the monetary value of the amount of work involved in fixing the technical issues found.

The thing with programming standards, and best practices is that it often times turns into a technical battle between various camps. One of the key points behind technical debt is that if you don’t agree with the rule set that is available you can always over-ride it by providing your own local coding standard.

One of the things that the technical debt report for SQL Server databases provides is transparancy. If you are a stellar SQL Server DBA writing great code that is sustainable, maintainable, and will continue to work for years into the future, that will show up. For the DBA writing bad code, that is likely to crash and burn the first time it is run, or very soon there after, that will show up also. This is intended to be a tool to help address those problems, and to fix them by improving coding standards, better education for everyone working on the database, and improved understanding of the overall health of your SQL Server.

In my initial testing after building the basic rule set, I have found some interesting results.