Stack Overflow Demo Database Column Store Edition: Making It Bigger

Posted on by Erik Darling

Stack Over Sharing

Rather than an F5 script to do this, I decided to make a stored procedure out of it. It’s in good shape, but if you find any problems or have any requests, head over to my GitHub repo to file an issue.

One thing I’d love feedback on is advancing dates. Right now, the script doesn’t do that at all. I thought of different ways to handle it, but didn’t like any of them, mostly because of how it might mess with the partitioning function I’m using. I felt like I was overthinking it quite a bit, and decided to leave dates as-is, and only increment User and Post Ids.

A quick note: This script assumes that a database called StackOverflow will be the source of the loads. If you need to use a different version, that’s a manual change. I didn’t want to go down the dynamic SQL route here until I gauged popularity.

Options

The stored procedure has relatively few options.

@loops INT = 1
@truncate_tables BIT = 1
@rebuild_when_done BIT = 1
@count_when_done BIT = 1

How many loops you want to run, if you want to start fresh by truncating tables first, if you want to rebuild indexes after you’re done loading, and if you want to get a count from each table at the end.

There are good enough reasons to include these for you to decide on. For instance, you might want to:

  • Start fresh, and see what the DMVs say about column store compression without rebuilding
  • Load on top and then see what they say without rebuilding
  • Do the opposite
  • Make additional bullet points

Iter and Iterate Walked Into A Store

Without being too explain-y, the way the script works is to:

  • Figure out if we need to increment Ids
  • Go through each of the main tables (Badges, Comments, Posts, Users, Votes) and insert the contents into the CCI version
  • Do this for as many loops as you specify

At the end of the script, do some cleanup of things that shouldn’t exist. Then rebuild and get counts if you asked for it.

Examples

This doesn’t require too much, but here you go:

EXEC dbo.make_big_stack @loops = 25, @truncate_tables = 1, @count_when_done = 1, @rebuild_when_done = 1;

EXEC dbo.make_big_stack @loops = 25, @truncate_tables = 0, @count_when_done = 1, @rebuild_when_done = 1;

In tomorrow’s post, I’ll give you download links and some more details about the database.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Related Posts