SQL Saturday

Hello followers,

Last weekend (May 5th, 2018), I attended SQL Saturday #710 and it was a fun and educational time.  I quite enjoyed it AND I presented at it.  First time presenting at a large event and had a blast.

My presentation was on Service Broker, which for those that don’t know is a system built into SQL Server that allows for asynchronous processing.  The presentation went mostly smooth with only a few little things that came up that I was uncertain why they appeared.  I’m still looking into it and will blog about it when I figure out why null messages were being sent after restoring from live to test.  This should not have happened.

For those who have not attended a SQL Saturday, I strongly encourage it.  It is an excellent networking opportunity as well as a great way to get some free training.  Did I mention it is free?  Completely free.  The one in Edmonton offers a large lunch for $10 which I recommend.

If you are looking for an opportunity to speak on a topic, I recommend talking to your local PASS chapter leader and looking at the SQL Saturday website to find SQL Saturdays close to you that you want to present at.

This was my second time attending a SQL Saturday and I spent most of the time networking and preparing for my presentation and trying not to be nervous.  In the end, I had nothing to be nervous about.  One person had been using service broker for a while and had some interesting questions that I had not come across with my service broker usage related to encrypted databases.  This is something I am going to be experimenting with and will hopefully have a good blog on.  There were also some people who were very new to SQL let alone service broker and I think I taught them some fun stuff with it.

As SQL Server professionals, I feel we should be constantly learning new things and even while preparing for this presentation I learned something new.  I learned that when you restore your test system to live, you don’t need to drop and recreate all of your database certificates and database master key!  Everything I could find online indicated that that was the method you needed to do.  Turns out you just need to regenerate your master keys!  While I was thinking about that, I realized you likely don’t even need to regenerate the master key – you likely can just restore the master key from a backup!  But I have not tested this and I don’t like to recommend or present on something I have not personally tested to ensure that my findings are correct and repeatable.  Regenerating the master key is a simple 2 liner – first open the database master key then alter the database master key, but any messages that are currently queued up will be destroyed as it regenerates the certificates and thus cannot decrypt the certificates.

So while I prepare my presentations (I am breaking the “troubleshooting service broker” presentation up into 2 different presentations), I plan on doing more testing and figure out new and interesting ways of handling service broker restores!  Next year I will have 2 presentations that I will be doing  at the Saskatoon SQL Server User Group on service broker.  First being a “what is service broker and how can it help me?” and the second is “my service broker is broken!!!”.

But until next time, keep on SQL-ing!


Tips for presenting

After my first presentation that went mostly good, but had a few hiccups, I started thinking about what makes a good presentation.  I have a few thoughts on this that fall under a few different categories: presentation demos, presentation preparation, and personal preparedness.  That’s a lot of P’s.

Under presentation demos, having your information accurate is a good start.  Although, if you find things wrong with your presentation, there is nothing wrong with posting corrections after the presentation.  It is important to try to have your information correct and to have your demos (if any) match what you are stating, but be prepared for things to not go the way you expect.  For example, if you have a demo that shows off 2 different methods for performing a task and you know method 1 is faster based on all of your other testing then in the presentation method 2 is faster, it is good to have some thoughts lined up as to why this could be.  One reason it could happen, for example, would be different locking/blocking, poor execution plan choice.  And worst case, don’t feel bad telling them “this worked better when I tested it at home.  What we should have seen was X.  The reason that X should have happened is Y.  I am not sure why this happened, but check out my blog in the next few days and I’ll try to explain what happened.”.
The other thing with your demos is to make sure to run them on the same machine you are going to be running the final presentation on.  That way you can tune them to get better results.  For example, if the demo runs in 10 seconds on your work computer but 10 minutes on your personal laptop, filling in the 10 minute time gap when you are not prepared for it will drive you nuts and possible bore or confuse your audience.

For presentation preparedness, make sure your presentation device (usually your laptop) has minimal apps running.  You don’t want to see skype messages pop up in the middle of your presentation.  Also make sure your battery is charged AND that you have your charger.  It is good to do both as you never know when your charger will decide to quit on you.
Also make sure you have all of the appropriate tools installed and configured before your presentation.  If you need SSMS, make sure it is installed AND started before the presentation.  If you need zoomit, start it before the presentation.  If you need powerpoint, make sure it is installed and running before the presentation.
Last point here, make sure to pick an appropriate desktop background before the presentation.  Having something SQL related or some scenery or even a plain black background is much better than a video game related background.  Some video game related backgrounds are OK, but if it is something your mother would raise an eyebrow at, you probably should think of changing it.  And make sure your laptop is set to maximum power mode, not battery saving mode.  This will give it a little extra power, at the cost of less battery life, but may help make your demos run slightly faster.

Lastly, personal preparedness.  This is the tips almost every presenter will hand out.  Make sure you get enough sleep the night before.  Make sure to bring water to drink.  Refrain from drinking coffee or tea beforehand (I recommend not drinking much before the presentation).  Use the bathroom before the presentation.  Relax.  That last one can be difficult since you are standing in front of a room full of people who may know the topic better than you and may ask you some difficult questions.  If you don’t know, there may be somebody else in the room who can help you.  Maintain eye contact with your audience.  And don’t be afraid to be wrong.

Presenting, while terrifying the first few times, I hear gets easier the more you do it.  I’ve done one presentation and I am preparing for my second and third which will be given in quick succession; roughly 1 week apart.  This terrifies and excites me.

If you have any tips on presenting, please leave them in the comments.  If you think any of my points are wrong, please let me know in the comments.  I will try to answer each comment as they come in.  And if you have any questions or comments about SQL or my blog, please leave them in the comments.


First live presentation

My first live presentation was today.  Was I nervous? yes.  Was I excited? yes.  Did it go well.  Mostly.

I’ve seen enough presentations that I should be able to manage this, but I’ve also never done a real presentation like this before with demos.  I am going to do a post-presentation blog as well for tips and tricks for next time.


This presentation is on indexes.  It is a very rough overview of what they are and how they work along with a few demos.  I have the slide deck and demos attached to this blog if anyone wants to go over it.  Feel free to comment on it and critique my presentation.  That is the best way for me to learn.

Powerpoint presentation:

SQL Server Performance and Maintenance



Setup.sql.txt – Notepad

demo1.sql.txt – Notepad

demo2.sql.txt – Notepad

demo3.sql.txt – Notepad

demo4.sql.txt – Notepad

WordPress is weird about the file formats you can upload, so I renamed the .sql to .txt and converted the txt to PDF then uploaded the PDF’s here.  Not the cleanest solution I know, but it works.  You can use Adobe Acrobat Reader DC (or your PDF viewer of choice) to open it and copy-paste the text into SQL, just be sure to remove the “setup.txt” and “page #” if you do a select all and copy paste it.

If there is interest in text-only versions of the scripts, please leave a comment in the section below and I will create a different blog post with the scripts in it.  It is a bit long, but it should be pretty easy to follow.  I can also remove the whitespace.  That was just for presenting it so people didn’t read ahead.

The documents have all been updated to the versions I used in the presentation.  This is the transcript I wrote up for what I was hoping to say and notes on when to change slides:


If you were at the presentation, I did venture off of the transcript which worked well as otherwise the presentation would have been even longer.  The presentation took just over an hour, so I needed to skip the very last part of the very last demo.  But had my laptop cooperated better, I probably would have had a better presentation too.  But that is something for my next blog post.


EDIT – I have some misinformation in my notes on the scripts.  Corrections can be found HERE.

First Live Presentation – corrections

So, as I noted on the First Live Presentation blog post, I have some misinformation in there and some typos.  I was looking at the scripts and there are a few things I screwed up.

In the “setup.sql.txt – notepad” script, I have a comment of:
–Create 10,000 indexes on table sprinkles_ci_lotsindex

that is not possible.  SQL does not allow 10,000 indexes on a table.  1000 is the max with 999 being non clustered and 1 being clustered, but this is not recommended to do.  when I was initially writing it, I meant to type “1,000” not “10,000” but when I was testing it on my machine, it caused too large of a performance impact so I changed it to 50 indexes, but forgot to update the comment.  Otherwise the setup script should be correct.

In the “demo1.sql.txt – notepad” script, I have a typos in there as well.  I have the following comment:
— non covering index. The above is an example of a non covering index. The
column we filtered on, the color, was not part of the clustered index or any
existing index.

This is incorrect.  A clustered index IS a covering index.  A clustered index on a table turns the table from a heap INTO a clustered table and by definition, it must contain every column in the table.

In the “demo2.sql.txt – notepad” script, I have some bad information.  First, I state:
— 2 things to note. First, the clustered index insert was the fastest. This is
contradictory to what I said earlier.
— Although it is only kind of contradictory. Due to several things going on in
the back end, when you are doing the inserts, with the heap,
— SQL starts at the first spot on disk for the table and just scans ahead until it
finds an opening. If you had done some random deletes to the table, you’d have
open spaces.
— Like the bookshelf example, if you take out book 3 from the first shelf, 3rd row
down, when you are looking to put any book back, if it fits in that slot you are
good to go.
— Otherwise you need to keep going until the end of the bookshelf to add it.
— With the clustered index, you know that the new entry should go at the end of
the table based on the ID, so you just jump to the end and are good to do that

That is incorrect.  The clustered index insert should not be faster than the heap insert.  The heap insert just goes to the end of the list and puts the data in there.  It should have been faster than the clustered index insert.  There are various reasons why it may have been slower or nearly the same speed on my machine during the demo such as memory getting full and paging to disk, idle CPU ramp up time, other processes (antivirus, disk defragmenter, etc).  Further repeated tests that I did with larger data sets than in the example show that a heap insert is going to be faster in every case.  The timing is similar, but the heap insert is faster.  I ran my test 4 times (which isn’t a very exhaustive test), but in each of the 4 runs, the heap insert was faster.
Update and Delete performance should be faster with the clustered index in almost every case.
The table with many indexes will always be slower.

“Demo3.sql.txt – notepad” has some information that should have been included that I missed.  Filtered indexes are not always good.  Update and Delete performance can be impacted by it even if you are updating or deleting stuff that should not be using that index.  A good example is provided by Paul White here:

The take away from Demo3 is more that you CAN use filtered indexes to help performance and disk space, but you need to be careful with them as they can cause negative side effects if used improperly.

“Demo4.sql.txt – notepad” starts out with a typo.  It states:
— Filtered indexes demo

but this demo is on Statistics, not filtered indexes.  Otherwise this demo does what it is intended to do.  It shows that SQL estimates incorrectly the number of rows in the table due to bad statistics.  This can lead to incorrect query plans being created or incorrect indexes (or possibly no indexes at all) being used.

Sorry for any misinformation.

PASS 2017

PASS Summit 2017.  What a blast.  Just as entertaining and educational as the previous ones I have been at.  For those DBA’s who have never attended a PASS Summit, I strongly encourage you to attend one.

It was nice seeing old friends and meeting new ones.  It is always good to meet new people and since the Summit is a group of like-minded people it is very easy to talk to someone new.  The whole conference is set up to make it easy to meet new people.

The presentations were awesome as well.  If you have never seen Melody Zacharias, Brent Ozar, Kendra Little, Bob Ward, Pinal Dave, or Hamish Watson present, I strongly recommend checking them out.  I did not get to Brent Ozar or Pinal Dave’s presentation this year, but I’ve seen them present before and it is always entertaining.  All of the others I was able to see present at PASS Summit 2017 along with a lot of other good presenters.  I am not trying to single out any one presenter by any means, these were just the first few that came to mind.  Bob Ward’s presentations almost always dive deep into SQL and bring about a “my brain is melting” experience.

It is also nice to meet up with and talk to the vendor sponsors who are there.  Idera (thanks for the duck!), RedGate, DH2i (cross platform availability groups!  How awesome is that?), Quest, Microsoft and more.

The first year I went I learned that I knew very little of what SQL could do.  I picked up on a lot of cool tricks that SQL can do and improved performance of various stored procedures at work.  The second year I learned more about specific features such as service broker which was part of a project I was working on at work.  This year, I went to more cloud-based (Azure) presentations and security based ones.  I learned about little security holes that can cause big problems without seeming like they would.  Every year I learned something new and useful for my workplace.

Also, when I went for the first year, I met some amazing people who encouraged me to start a local PASS chapter where I live.  At first I thought I’d never find time to work on that and that it would be horribly difficult.  Not saying it is easy; there is a lot of things you need to do, but the benefit I get out of it knowing that I am helping my local SQL Community is great.  If you have never been to your local PASS chapter meetings, I encourage you to check them out.

Lastly, for those DBA’s who cannot afford to attend the full PASS Summit, I encourage you to check out SQL Saturday’s – those are mini-summits.  They are entirely free and you get to hear some amazing speakers.