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.
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.
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.
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.
But…. I have found it very challenging to distribute the SSRS reports in a way that they are easy to use for anyone anywhere with any SQL Server database environment.
So… The solution is to take the work done in the reports and port it into an application that can replace this collection of SSRS reports. I started on this project today, and think that over the next couple of months, I should be able to replace the existing SQL Server Health reports in SSRS with an application that hosts all the queries and makes it easier than it is now.
I am just getting started, but over the next couple of months I will be releasing the SQL Server Health reports as an application.
Today I started the day early with a great presentation for the SQL Lunch UK group on Common Table Expressions.
For those who attended the presentation here is the Zip file with the Powerpoint and the SQL code from the demo. Feel free to download it and learn from it.
