Introducing the Joes2Pros Academy

The new Joes2Pros Academy has just launched. The Academy provides an interactive classroom experience that can be accessed on your own time as you need it. This site has high quality content, quizzes and exams. Click play to watch the video below for more information on the Joes2Pros Academy.

Students can interact with other students and instructors to get the best experience out of the Joes2Pros Academy.

Courses offered in the following areas:

  • SQL Server Administration
  • SQL Server Developer
  • SQL Server BI
  • SSRS
  • AlwaysOn
  • C#
  • and more

The Joes2Pros Academy helps you take the journey from average Joe to technology Pro.

As an instructor in the Joes2Pros Academy I have been able to learn a great deal from the other instructors.  I recommend taking a look and at least trying out the free 3 day trial.

What is a Common Table Expression – Video

 

Related Common Table Expression Links

You can also find more CTE examples on the TSQL Wiki.

If you enjoyed these Common Table Expression posting, and want to learn more about Common Table Expressions, please take a look at my book on CTE’s at Amazon.com. The book is titled Common Table Expressions - Joes 2 Pros® - A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs.

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

What twitter hash tags do you follow for SQL Server?

#

The key to twitter is to follow the right hash tags.  What hash tags do you follow related to Microsoft SQL Server? Here are a few of the twitter hash tags that I follow:

#SQLServer

The generic SQL Server hash tag. Most anything goes on this hash tag.

#SQLHelp

The #SQLHelp hash tag is great when you need to get a question answered. May experts follow this hash tag and answer a wide variety of SQL Server questions. There are some general rules about not spamming this hash tag, and keeping it a pure Q and A mechanism.

#SQLPass

General discussion about PASS and PASS events. When a major PASS event is happening there is a lot of activity on this hash tag.

#SQLSaturday

General discussion about SQL Saturday events.

#SQLSatXXX where XXX is a number

Discussion about a specific SQL Saturday event.

#SQLFamily

Discussion between those who are part of the worldwide SQL Family… Those who thrive on SQL Server on twitter, and in the world at large.

#SSRS

Questions, answers and news about SQL Server Reporting Services.

#SSMS

Questions, answers, news, and gripes about SQL Server Management Studio. It’s common to read things like “lost all my work when SSMS locked up on me”.

What SQL Server hash tags do you follow?  Please post a comment with your favorite SQL Server related hash tag on twitter.  

Thanks, and keep on tweeting.

SQL Server Memory Hog Query

The article could be named, “How to use up all of your SQL Server available memory with a single CTE query.”  Another name for the article could just be “SQL Server Bug Report” depending on how you look at it.

When presenting unleashing Common Table Expressions at SQL Saturday a while back, I was asked a couple of great questions that I didn’t know the answer to. So I did the research and tracked it down:

1. How many levels of recursion can you have in a CTE?

2. How many levels of nesting can you have in a CTE?

So I started doing the research and doing some testing to figure it out.

How many levels of recursion can you have in a CTE?

This is the easier one to answer between the two questions. The answer is, you can have more levels of recursion that you would ever need for standard recursion. I have tested CTEs with up to 1 million levels of recursion, and the have performed pretty well.  If you are writing queries that need more than 1 million levels of recursion, perhaps you should take a look at a different approach.

How many levels of nesting can you have in a CTE?

This is where it gets really interesting. SQL Server does a really good job with overall performance on CTEs, but where it completely breaks down is on deep nested CTE queries. By deep nested I mean more than a thousand or two thousand. To answer the question of how many levels can you have, I would answer this as you can nest more than you would ever really need. If you really need more than a thousand levels of nesting in a CTE, you might want to rethink your approach.

On SQL Server 2005 the limit is 255. But in SQL Server 2008 and newer this limit was extended, and appears to not have a fixed limit, rather the limit is based on the amount of memory available for the query to use.

So to build the crazy CTE, I used excel to build out several thousands of rows of nested CTEs. One calling another, calling the next, and so on. What I found was that this was a very easy way to use up almost all the memory on your database. DO NOT TRY THIS ON A PRODUCTION DATABASE. The reason that DBAs and developers have test or development databases is to play around with things that may be dangerous on a production server. This is one that you could try on a test server.

The other interesting thing that the query does when it uses up all the memory, somehow it dumps some of the connections that are currently active on the SQL Server at that point.

Watch the video to find out how a nested CTE query could use up all of the memory on your SQL Server.

If anyone has a test server with 512GB, or 1TB of available, memory, I would love to see how this type of query performs. Give it a try and let me know.

You can download the script NestedCTE.zip here. The zip file contains 2 files, one called CTE.sql, and one called CTE2500.sql.  The 2500 version is the one that I used in the demo, and the CTE.sql has 32767 nestings.  I would love to see the bigger on run on a server with a TB or more of memory to see how it performs.

Here are a few other links to CTE related posts on my site.

It is finally here. Beta 1 of the Database Health Reports for SQL Server.

In October last year I started work on the Database Health reports project.  It started out as a series of SSRS reports that I released and updated in December 2011 to March 2012.  After that it became clear that the SSRS reports weren’t going to do what I was looking for so I started on the Database Health reports version 2.0 which migrated all the original reports into an application and added much more functionality than I had before.

Today (September 1, 2012) I have released the Beta 1 of the Database Health reports project.

Please give it a try and let me know how it works for you.  See the Database Health website for more details on this project.

Here is a screenshot of the database overview page.

Enjoy!