Introducing Open Query Store

Introducing Open Query Store

Many of us may have heard about the amazing new feature in SQL Server 2016 called Query Store. However, there are a lot of SQL Servers out there that are neither 2016, nor will they ever be upgraded to 2016.

What is Open Query Store?

Open Query Store is our attempt at copying the capabilities of the Query Store, but for SQL 2005 – 2014. As the name suggests, this is an open source project with the MIT license, meaning you can use it for fun or profit (or both).

After a few meetings (read: SQL Server events and beers), Enrico van der Laar ( b | t ) and myself got the idea of creating a Query Store for the rest of us!

This quickly became what is now known as Open Query Store.

The first release (Updated release 2017-07-03) was published at the end of June 2017 and provides a background collection of query execution statistics in a database of your choice. The v1.0 release supports SQL Server from 2008 to 2014 and all editions (Express, Standard, Enterprise). There is also a PowerShell installer for those that are so inclined, which will grab the code from GitHub and install OQS into a specified database.

There is also a custom report which can be installed in SSMS (2016 and 2017), which will display information that OQS has collected.


What is the Future of Open Query Store?

The future of OQS is to get as close to feature parity with “real” Query Store as possible. We’re not sure yet exactly how close that will be, but we’ll do our best!

We have an overview of the current features on GitHub and will be adding features as time goes by.

If you have a specific feature that you want to add, then please provide feedback/suggestions via the Issues tab in GitHub.

Thanks for reading and we hope OQS can help you with query tuning in the future.

Presenting: Presentation Mode!

As a presenter at events I am constantly trying to improve the experience of showing information in slides and transitioning back and forth to demos.

ZoomIt: An OK solution for a bad problem?

The most jarring aspect of this is making sure that demo code is visible to the audience. The fantastic ZoomIt allows a presenter to (surprise, surprise) zoom into portions of the screen and highlight/annotate code or information to the audience:


First of all, the act of zooming can be disorienting to the audience. There is a flurry of zoom and scrolling activity to get to where you want to on the screen. After this, the actual presentation of the zoomed content usually works nicely enough. However, the zoom out must occur before moving back into the PowerPoint slide deck to continue with the next portion of the presentation.

This has been the only way to give a consistent and clear overview to an audience, particularly when SSMS was being used for demos. The issue revolves around the fact that although the T-SQL code editor window can resize fonts, the remainder of the SSMS interface is set in a single font type and size.

Many of you may have noticed that Microsoft made a key change in their deployment strategy with regards to SSMS when SQL Server 2016 was released. SSMS was decoupled from the core engine components and follows a much shorter release cycle. Since SQL Server 2016 was released to market in September, there have been at least 6 versions of SSMS released. This is fantastic, we now no longer have to wait for the next release of SQL Server (whether a full version or a service pack) for SSMS to get bug-fixes or feature additions.

This is now extremely important when we look at the issue around font sizes and types. Microsoft has paid attention and with their current Release Candidate (RC) for SSMS 17 they included a very important release note entry…..

Presentation Mode!

If we read the release notes, we see that there are three new tasks available via Quick Launch inside SSMS.

  • PresentEdit – This allows the fonts for presentation mode to be set
  • PresentOn – This turns on presentation mode
  • RestoreDefaultFonts – Reverts SSMS back to the default fonts after activating presentation mode

All three tasks are pretty easy to understand, although the final task highlights that a task to specifically turn off the presentation mode is currently missing (this is an RC after all).

The “Quick Launch” field can be found in the top right corner of SSMS 17.0 RC3 and begins searching as soon as we start to type in it:


By choosing “PresentEdit” an xml file is opened in a new tab in SSMS, showing us the options that we can change to make SSMS look different when presentation mode is activated.


We are presented with the option to choose font family and font size for both the text editor and, more importantly, for the environment in general (menus, object explorer etc.). This is where we can play around an find the fonts that work best in our presentations.

Using the values in my screenshot and launching PresentOn made a huge difference in font readability inside SSMS. The image below shows SSMS on the left in “standard” mode and in presentation mode on the right.


The difference is quite clear, all environment fonts are much larger and easier to read on during presentation mode. This is great for demoing SSMS during a presentation!

However, the biggest improvement is when we are querying data. In previous versions of SSMS the grid results were tiny when projected onto a wall. The only way to see the results were to either return the results as text (which has the downside of running off the right-side of the screen for larger result sets), or using ZoomIt and people getting motion sickness.

Now, with presentation mode on, the results grid is included in the font resizing:


Praise be to the spaghetti monster! No more motion sickness required and attendees can concentrate their contempt at all the bullet points in the slide deck instead.

So if you are a presenter, or want to have more control over the fonts in SSMS, your wait is almost over…… or is over now if you are brave enough to install the RC of SSMS 17 🙂

Happy font-changing

I’ve been awarded my first MVP award

You’ve got mail!

This afternoon during a typical afternoon working on fixing a client’s database design, I received an email that I never expected to receive:

For the non-German readers, this is an email from Microsoft saying that I have been awarded the Microsoft Most Valuable Professional (MVP) Award. The award is a recognition of work in the community which is based on other people in the community nominating people for the award. Read up on the MVP award on the dedicated website.

Standing on the shoulders of giants

I have been part of the SQL Server community for a few years now, starting back in 2013 with my first session at SQL Sat 196 in Exeter. This continued to multiple SQLSaturday events and other tech events around Europe and also at the PASS Summit in 2016. In the mean time, I setup a SQL Server user group in my quiet corner of Germany and also a cool little event called SQLGrillen

However, all of this would never have come to pass without the cajoling and wrangling of a few very important people.

  • André Kamman (b | t) – friend, mentor and now business partner
  • Mladen Prajdić (t) – friend, mentor and creator of the excellent SSMS Toolspack
  • Marco Blasio (t) – friend, mentor and crazy Italian in Ireland 🙂

These three were instrumental in getting me to begin engaging in the community, especially as a speaker at events. Without their pressure support, I would never have taken those first steps. Thanks gents!

  • Jonathan Allen (t) – friend, mentor, former minion colleague
  • Annette Allen (t) – friend, mentor, inspiration for community work

Jonathan and Annette are in fact, as the organizers of SQLSat Exeter, the original enablers of getting me hooked on community stuff. It is fully their fault that I began speaking by giving me a platform to start my speaking career.

  • Karla Landrum (t) – friend, mentor and fantastic all-round gal.

Karla was/is just amazing. She is best known as the face of PASS at SQLSaturdays around the world until she stepped down last year. She helped me so much in the background, tirelessly listening to my questions. She provided me with invaluable insights and inspiration in everything to do with the community.

One common theme here is that I count each of these people as friends first. For me at least, this is why I do what I do. I get to spend time with friends, talking about things I am passionate about and try to get others excited about it too. What’s not to love?!

However, being awarded anything is nice and I don’t deny it is a good feeling to receive “official” recognition for what I have done.

Now the real work begins

I now hope to use the access to extra information and resources to help even more people who want to be successful with SQL Server. With the speed that the tools and tech coming out of Redmond, there will be plenty of things for me to keep up with.

So for now, I’ll say thanks for the award and well wishes and I hope to see you at an event somewhere soon!

You didn’t know that?! But that’s so obvious!

Rant-mode activated

Today I noticed a tweet from Andrew Prewski (@DBAFromTheCold)

Loving all the passive aggressive comments about my article on SQL on linux, keep ’em coming!

Andrew’s tweet piqued my interest and I went to look at his blog-post “Killing databases in SQL Server on Linux“. In the post, Andrew investigates how files are handled inside Linux, in particular how database files are handled. Go and read it, it is really interesting and a possible eye-opener for anyone who has no experience with Linux.

Now, the reason for this rant is the comments that came through from people who do have experience with Linux/Unix (go and read those too). Many of them boil down to basically scoffing at how Andrew didn’t know that Linux handles files differently to Windows and has done for years/decades. The tone of these comments were aimed to belittle Andrew for not knowing about this behaviour.

This pisses me off!

Maybe I’m just having a bad day or something, but it seems wrong to me that someone who is trying to learn should be made to feel in any way stupid for exploring something. It seems even wronger for this sort of behaviour to come from an area of the IT world which generally prides itself with the idea of “hacking”. The whole premise of taking a technology that you don’t understand and prodding, poking, dissembling and rebuilding – that is the “way of the hacker”, or at least how I have always understood it.

I for one hope that Andrew won’t be deterred by this shitty behaviour and also would hope that anyone reading this would think twice about how they respond to a similar situation. Everyone starts out at zero, not even Linus Torvalds was born being able to code.

Please remember: Don’t be condescending. Offer tips, advice or references to help, not ridicule.

My fifth (and simultaneously my first) PASS Summit

The PASS Summit is something special. It is the largest gathering of SQL Server aficionados on the planet. With attendance reported at over 4000 people each year, that is one biiig pile of SQL Server geeks!

As the PASS Summit is held in the USA, it is quite special to be able to attend for me (and many other non-US based people). I was lucky in having employers that were happy to send me on an all expenses trip to Seattle for the past few years (I skipped the Charlotte-based Summit to make sure my buddy and former co-worker @Fatherjack got to go). Attending has allowed me to learn plenty about SQL Server, but to also make some life-altering connections with people that I would probably never have otherwise met.

The first life-altering connections cajoled me into speaking at SQLSaturdays: my good friends André Kamman, Mladen Prajdić and Marco Blasio. They convinced me over many beers and steaks that speaking was the next step on my journey. This culminated in my submitting my very first session to SQLSaturday Exeter 2013 entitled “Replication: What, How and Why“. While nerve-wracking, it was one of the best career moves I have ever made. Since then I have spoken at numerous SQLSaturdays and other events around Europe and met a ton of people. My speaking career hit a couple of high notes recently, I was a contestant in last year’s Speaker Idol at PASS Summit 2015 (losing in the finals to David Maxwell) and also delivering both a standard session as well as a pre-con at SQLBits XV.

However, back to the title of this post. This was both my fifth and also my first PASS Summit. It was my first, because it was the first time I have delivered a full-length session at PASS Summit. I had submitted session in previous years, but failed to be chosen for varying reasons.

This year was different! I received confirmation that one of my three submitted sessions had been chosen…… Replication: What, How and Why! To say I was surprised is an understatement. After multiple years of not being chosen, I had received a positive response. On top of that, the session that started my speaking career had been chosen. This was quite unexpected, although in previous years when PASS had surveyed attendees on what subjects they would like to see more of, Replication was regularly in the top ten.

So I dusted off the session and made some much needed updates to the flow of the session and attempted to make the content clearer. I was surprised to find that I was able to rattle off the outline of the session pretty much from memory when practicing at home. The tweaks I made allowed me to run a full 75 minutes instead of a “normal” 60 like at most events.

I was assigned an afternoon session slot on the Thursday of PASS Summit. This means I would be in the middle of the conference, straight after lunch. Going by past experience at other events, this means that pretty much all attendees are still around (unlike Friday afternoon sessions, where many take an afternoon flight home), but as it is after lunch, some people struggle to stay awake! With that in mind, I tried to keep the audience engaged with questions and interaction. This is a great way of making sure that wandering minds are re-engaged and gives me something to focus my nervous energy on.

When I stepped into the session room I was more nervous than usual – the sessions are recorded and the room looked like it had seating for about 300 people! This is larger than most events and I assumed the room would be mostly empty. Replication is a niche subject/feature and I have had between 20 and 50 attendees in this session at other events. You can imagine my surprise when I saw about 60 people already in the room. I immediately took some photo evidence Smile


Please bear in mind that this was 20 minutes before my session should start, so I made sure that my title slide was visible to the entire room.


This didn’t scare anyone off, on the contrary, the room continued to fill. I was officially shocked/concerned and took more photos to prove to myself after the session, that I wasn’t dreaming. I went out of the main entrance to check that the signage for the room was right (it was) and the room attendant told me she had counted 220 people into the room with 5 minutes before kick-of!


I proceeded to deliver the session to a packed room, only a few seats remained empty with a number of people standing at the back of the room too. The session went great, with some fantastic questions from the audience – directing my explanations deeper where possible and ensuring people took the information they needed. I ended my session on time and fielded questions from about 20-30 people at the edge of the stage and then in the hallway afterwards. There are a lot of people using replication in ways that neither I nor (I’m sure) Microsoft had ever dreamed of. It was also great to hear that the SQLClinic, run by Microsoft, had a chalk-talk about replication and there were mentions of replication potentially receiving some more attention by the dev team in Redmond (about time if you ask me!).

The next few hours after my session were a blur of adrenaline. Even a week later, it is hard to believe that my first session at PASS could go so well (as far as I could assess). Now I need to wait for the session feedback and see what I can do to improve. According to some of my attendees, a deeper dive or extended session on replication is something for me to think about submitting next year.

All in all I can say that my fifth PASS Summit was great fun – I saw some new things, met some new people and enjoyed Seattle (again).

As for my first PASS Summit: fantastic. I never expected this “old” session to be such fun to present, or to be so well attended. I look forward to next year to see if I will be able to present a new session.

PS: If you attended my session, please fill in the feedback (or write a comment here). That is what helps me to improve my sessions and my presenting in general.

Thanks for reading and see you around.

Speaking at SQLSaturday Oslo

For Valhalla!!

I was very pleased to recently be chosen to speak at SQLSaturday Oslo on the 3rd of September. I spoke there last year and had a great time. The organisers of the event are really friendly and took us on a great trip up to the huge ski jumping location just outside town. I’m looking forward to what they have in store for us this year!

I will be presenting my session “You don’t need Enterprise Edition for do that!” a fun session showing that you don’t necessarily need Enterprise Edition to achieve some of its features.

Just the other day I was also asked if I could fill in a last minute cancellation, so will be delivering a second session “SQL Server 2016, is it worth the Upgrade?“, taking a look at the new features and whether they are compelling enough to make you want to upgrade.

If you are in the Oslo area and haven’t signed up for the event, do it! Going on last year’s success, this year will be well worth a visit! Check out the jam-packed agenda

See you there!

Database objects can DIE in SQL 2016 without using the KILL command

When developing database code for SQL Server one issue that has got in the way of smooth deployment has been a simple check for the existence of an object before creation.

The issue being, whenever an object should be created, we need to be sure that there isn’t an object already in the database that has the same name. If there is, we need to do something beforehand otherwise the CREATE command is going to fail like this:

Msg 2714, Level 16, State 6, Line 16
There is already an object named {objectname} in the database.

DIE Database Object, DIE!

Up until SQL Server 2016 the only way of making sure an object wasn’t already present was by adding a snippet of code directly before the CREATE statement. This could be achieved in multiple ways, but essentially all solutions just checked for an object with the name provided.

Something along the lines of this:


While that is a very short piece of code, it is still code that needs to be typed and checked and tested. Of special note is the fact that the object name needs to be correctly typed twice. Let us also not forget, because there are multiple ways of achieving this existence check a new developer may not immediately understand what this code is doing.

You may notice in the paragraph above the code example, I wrote “Up until SQL Server 2016….”. That is because with the release of SQL Server 2016, Microsoft has made our lives a little bit easier in this respect. It is now possible to do the existence check and the drop in one command rather than two.

The long-winded and more error prone example above simply becomes:


Wow! Drop Table If Exists, or DIE for short. Super-short (only one chance of mistyping the object name) and super easy for anyone to understand, right?

But wait! There’s more!

This existence check is not limited to tables. It covers many more objects, as outlined on the SQL Server Database Engine Blog.

But wait! There’s even more!

If you wanted to be really efficient and only want to write the (not really) verbose DROP TABLE IF EXISTS once for all the tables that you want to drop, you can!

This code will work flawlessly:

DROP TABLE IF EXISTS MyTable, MyOtherTable, YetAnotherTable

The elegance of DROP TABLE IF EXISTS is that if any (or all) of the three tables above exist, they will be deleted. If none of them exist, then nothing will happen.

And here is the catch

Unfortunately, the pluralised usage of DROP TABLE IF EXISTS doesn’t seem to work for all object types. I tried to do the same thing with database users:


This would end in a syntax error.

So there we have it. Objects can now DIE inside SQL Server, without the KILL command 🙂


At the time of writing I have not checked all of the supported objects of DIE, but will update this post once I have found the time to do so.