Monthly Archives: July 2020

SQL Saturday Albany 2020

Posted on by
1

So, another SQL Saturday Albany is in the books. First, I want to thank Ed Pollack and his crew for doing a great job with a changing and challenging landscape.  While I handle the day to day and monthly operations of the Capital Area SQL Server User Group, Ed handles the planning and operations of the SQL Saturday event. While the event itself is only 1 day of the year, I suspect he has the harder job!

This year of course planning was complicated by the fact that the event had to become a virtual event. However, it’s a bit ironic we went virtual because in many ways, the Capital District of NY is probably one of the safer places in the country to have an in-person event. That said, virtual was still by far the right decision.

Lessons Learned

Since more and more SQL Saturdays will be virtual for the foreseeable future, I wanted to take the opportunity to pass on some lessons I learned and some thoughts I have about making them even more successful. Just like the #SQLFamily in general passing on knowledge about SQL Server, I wanted to pass on knowledge learned here.

For Presenters

The topic I presented on was So you want to Present: Tips and Tricks of the Trade. I think it’s important to nurture the next generation of speakers. Over the years I was given a great deal of encouragement and advice from the speakers who came before me and I feel it’s important to pass that on. Normally I give this presentation in person. One of the pieces of advice I really stress in it is to practice beforehand. I take that to heart. I knew going into this SQL Saturday that presenting this remotely would create new challenges. For example, on one slide I talk about moving around on the stage. That doesn’t really apply to virtual presentations. On the other hand, when presenting them in person, I generally don’t have to worry about a “green-screen”. (Turns out for this one I didn’t either, more on that in a moment.)

So I decided to make sure I did a remote run through of this presentation with a friend of mine. I can’t tell you how valuable that was. I found that slides I thought were fine when I practiced by myself didn’t work well when presented remotely. I found that the lack of feedback inhibited me at points (I actually do mention this in the original slide deck). With her feedback, I altered about a 1/2 dozen slides and ended up adding 3-4 more. I think this made for a much better and more cohesive presentation.

Tip #1: Practice your virtual presentation at least once with a remote audience

They don’t have to know the topic or honestly, even have an interest in it. In fact I’d argue it might help if they don’t, this means they can focus more on the delivery and any technical issues than the content itself. Even if you’ve given the talk 100 times in front of a live audience, doing it remotely is different enough that you need feedback.

Tip #2: Know your presentation tool

This one actually came back to bite me and I’m going to have another tip on this later. I did my practice run via Zoom, because that’s what I normally use. I’m used to the built-in Chroma Key (aka green-screen) feature and know how to turn it on and off and to play with it. It turns out that GotoWebinar handles it differently and I didn’t even think about it until I got to that part of my presentation and realized I had never turned it on, and had no idea how to! This meant that this part of my talk didn’t go as well as planned.

Tip #3: Have a friend watch the actual presentation

I actually lucked out here, both my kids got up early (well for them, considering it was a weekend) and watched me present. I’m actually glad I didn’t realize this until the very end or else I might have been more self-conscious. That said, even though I had followed Tip #1 above, they were able to give me more feedback. For example, (and this relates to Tip #2), the demo I did using Prezi was choppy and not great. In addition, my Magnify Screen example that apparently worked in Zoom, did not work in GotoWebinar! This feedback was useful. But even more so, if someone you know and trust is watching in real-time, they can give real-time feedback such as issues with bandwidth, volume levels, etc.

Tip #4: Revise your presentation

Unless your presentation was developed exclusively to be done remotely, I can guarantee that it probably need some changes to make it work better remotely. For example, since most folks will be watching from their computer or phone, you actually may NOT need to magnify the screen such as you would in a live presentation with folks sitting in the back of the room. During another speaker’s presentation, I realized they could have dialed back the magnification they had enabled in SSMS and it would have still been very readable and also presented more information.

You also can’t effectively use a laser pointer to highlight items on the slide-deck.

You might need to add a few slides to better explain a point, or even remove some since they’re no longer relevant. But in general, you can’t just shift and lift a live presentation to become a remote one and have it be as good.

Tip #5: Know your physical setup

This is actually a problem I see at times with in-person presentations, but it’s even more true with virtual ones and it ties to Tip #2 above. If you have multiple screens, understand which one will be shown by the presentation tool. Most, if not all, let you select which screen or even which window is being shared. This can be very important. If you choose to share a particular program window (say PowerPoint) and then try to switch to another window (say SSMS) your audience may not see the new window. Or, and this is very common, if you run PowerPoint in presenter mode where you have the presented slides on one screen, and your thumbnails and notes on another, make sure you know which screen is being shared. I did get this right with GotoWebinar (in part because I knew to look for it) but it wasn’t obvious at first how to do this.

In addition, decide where to put your webcam! If you’re sharing your face (and I’m a fan of it, I think it makes it easier for others to connect to you as a presenter) understand which screen you’ll be looking at the most, otherwise your audience may get an awkward looking view of you always looking off to another screen. And, if you can, try to make “eye contact” through the camera from time to time. In addition, be aware, and this is an issue I’m still trying to address, that you may have glare coming off of your glasses. For example, I need to wear reading glasses at my computer, and even after adjusting the lighting in the room, it became apparent, that the brightness of my screens alone was causing a glare problem. I’ll be working on this!

Also be aware of what may be in the background of your camera. You don’t want to have any embarrassing items showing up on your webcam!

For Organizers

Tip #6: Provide access to the presentation tool a week beforehand

Now, this is partly on me. I didn’t think to ask Ed if I could log into one of the GotoWebinar channels beforehand, I should have. But I’ll go a step further. A lesson I think we learned is that as an organizer, make sure presenters can log in before the big day and that they can practice with the tool. This allows them to learn all the controls before they go live. For example, I didn’t realize until 10 minutes was left in my presentation how to see who the attendees were. At first I could only see folks who had been designated as a panelist or moderator, so I was annoyed I couldn’t see who was simply attending. Finally I realized what I thought was simply a label was in fact a tab I could click on. Had I played with the actual tool earlier in the week I’d have known this far sooner.  So organizers, if you can, arrange time for presenters to log in days before the event.

Tip #7: Have plenty of “Operators”

Every tool may call them by different names but ensure that you have enough folks in each “room” or “channel” who can do things like mute/unmute people, who can ensure the presenter can be heard, etc. When I started my presentation, there was some hitch and there was no one around initially to unmute me. While I considered doing my presentation via interpretive dance or via mime, I decided to not to. Ed was able to jump in and solve the problem. I ended up losing about 10 minutes of time due to this glitch.

Tip #8: Train your “Operators”

This goes back to the two previous tips, make sure your operators have training before the big day. Setup an hour a week before and have them all log in and practice how to unmute or mute presenters, how to pass control to the next operator, etc. Also, you may want to give them a script to read at the start and end of each session. “Good morning. Thank you for signing in. The presenter for this session will be John Doe and he will be talking about parameter sniffing in SQL Server. If you have a question, please enter it in the Q&A window and I will make sure the presenter is aware of it. This session is/isn’t being recorded.” At the end a closing item like, “Thank you for attending. Please remember to join us in Room #1 at 4:45 for the raffle and also when this session ends, there will be a quick feedback survey. Please take the time to fill it out.”

Tip #9: If you can, have a feedback mechanism

While people often don’t fill out the written feedback forms at a SQL Saturday, when they do, they can often be valuable. Try to recreate this for virtual ones.

Tip #10: Have a speaker’s channel

I hadn’t given this much thought until I was talking to a fellow speaker, Rie Irish later, and remarked how I missed the interaction with my fellow speakers. She was the one who suggested a speaker’s “channel” or “room” would be a good idea and I have to agree. A private room where speakers can log in, chat with each other, reach out to operators or organizers strikes me as a great idea. I’d highly suggest it.

Tip #11: Have a general open channel

Call this the “hallway” channel if you want, but try to recreate the hallway experience where folks can simply chat with each other. SQL Saturday is very much a social event, so try to leverage that! Let everyone chat together just like they would at an in-person SQL Saturday event.

For Attendees

Tip #12: Use social media

As a speaker or organizer, I love to see folks talking about my talk or event on Twitter and Facebook. Please, share the enthusiasm. Let others know what you’re doing and share your thoughts! This is actually a tip for everyone, but there’s far more attendees than organizers/speakers, so you can do the most!

Tip #13: Ask questions, provide feedback

Every platform used for remote presentations offers some sort of Q&A or feedback. Please, use this. As a virtual speaker, it’s impossible to know if my points are coming across. I want/welcome questions and feedback, both during and after. As great as my talks are, or at least I think they are, it’s impossible to tell without feedback if they’re making an impact. That said, let me apologize right now, if during my talk you tried to ask a question or give feedback, because of my lack of familiarity with the tool and not having the planned operator in the room, I may have missed it.

Tip #14: Attend!

Yes, this sounds obvious, but hey, without you, we’re just talking into a microphone! Just because we can’t be together in person doesn’t mean we should stop learning! Take advantage of this time to attend as many virtual events as you can! With so many being virtual, you can pick ones out of your timezone for example to better fit your schedule, or in different parts of the world! Being physically close is no longer a requirement!

In Closing

Again, I want to reiterate that Ed and his team did a bang-up job with our SQL Saturday and I had a blast and everyone I spoke to had a great time. But of course, doing events virtually is still a new thing and we’re learning. So this is an opportunity to take the lessons from a great event and make yours even better!

I had a really positive experience presenting virtually and look forward to my PASS Summit presentation and an encouraged to put in for more virtual SQL Saturdays after this.

In addition, I’d love to hear what tips you might add.

A Summit To Remember

Posted on by
Reply

There’s been a lot of talk about the 2020 PASS Summit and how the impact of making it virtual this year. I’ve even previously written about it. I’ll be clear, I would prefer an in-person summit. But that said, I think having it virtual does provide for some fascinating and interesting possibilities and I look forward to seeing how they’re handled.  It will certainly be different being able to watch a session at a later time as a default option. And my understanding is that session schedules will no longer be constrained by the timezone the Summit is being held in.

That said, I also have to admit a certain bias here. I’ve wanted to speak at Summit for a couple of years now and have been turned down twice in the past two years. This year I was hoping again to speak, but alas, I procrastinated a bit too long and literally missed the original window to submit by a few hours.

Note I said original window. Because the Summit was moved to a virtual Summit the decision was made to re-open the call for speakers. This time I took advantage of that 2nd chance and submitted a bid.

And I’m so glad I did. Because if you didn’t have a reason to attend summit before, you do now! You get to hear me talk about PowerShell! So, I’ll admit to getting an unexpected benefit out of the move to a Virtual Summit.

I still recall one of my first attempts to use PowerShell at a client site, about 8 years ago. It did not go well. The security policy wouldn’t let me do what I wanted and the available knowledge on the Internet was sparse. Basically I wanted to loop through a list of servers and see if they had SQL Server installed. I eventually gave up on that project.

Since then though, I’ve been drawn to PowerShell and have come to love it. Now, when you hear a DBA talk about PowerShell, they will almost always mention dbatools. I want to go on record right now, I think it’s a GREAT addition, but I rarely use it. Not because there’s anything wrong with it, but mostly because my current usage is a bit different than what it provides. I do talk about it a bit here though.

For the talk I’ll be presenting, my plan is to start with a real simple PowerShell Script and slowly build on it until it’s a useful script for deploying SQL Scripts to multiple servers. For anyone who has read my articles at Red-Gate, much of this will be familiar territory, but I hope to cover in 75 minutes what I cover in 3-4 articles.

Checking this morning, I noticed that I’m among good company, and it’s humbling to see it, when it comes to speaking about PowerShell.

So, I hope you “come” and see me present on PowerShell at SQL Summit 2020. I’ll be in New York, where will you be?

“We want information…information… information!!!”

Posted on by
Reply

For anyone who has ever watched the classic British mini-series “The Prisoner” this is a very recognizable line. But it applies to many parts of our lives.

This is a tale of hiking, a non-cave rescue, and yes, eventually Extended Events.

“I went to the woods…”

This past weekend I spent some time in the woods hiking and getting away from it all. This is the first time in literally decades I had done an overnight hike on the Appalachian Trail. My goal was to get in an overnight and work on closing a gap of it that I had not yet hiked.

The last time I hiked the trail, cell phones were a very rare item, carried by business people only and often weighing several pounds, they certainly weren’t something the average hiker could afford, and even if they could, they would be too heavy to carry.

I mention this because I had fully intended to carry mine with me, so that I could take pictures, and perhaps even, I’ll admit it, if I had connectivity when I camped that night, catch up on some Wikipedia reading, or send a picture or two to friends and family. But alas, about 2 miles into the hike, at a gorgeous viewpoint (see older photo above), I stopped, tried to pull out my phone and realized that unsettled feeling I had at my car before locking it wasn’t “Am I sure I have my keys” but really should have been “am I sure I have my phone!”

It turns out, other than my inability to document my trip with some photos, and not being able to call my wife to let her know I’d be at the pick-up point much earlier than we had planned, not having access to information of the outside world was a refreshing change of pace. I’m almost glad I didn’t have my phone.

A Missed Call

As some of my readers know, besides being a DBA, I also teach and at times perform cave rescues. As I tell folks once they get past the “That’s so cool” phase, it’s not really all that glamorous. If I get called out to one actual rescue a year here in the Northeast, it’s a busy year. But, on warm weekends in the summer, the odds are higher than say the middle of the week in the winter (though that has happened too).

So a concern I had in regards to not having my phone was that I would miss a call for a potential rescue.

It turns out I was partially correct in my concern.  On the way home, I saw my phone buzz. I didn’t answer it, but a few minutes later did glance down to see “Missed Call”. It was from my team co-captain. (To be transparent here, the terms team and co-captain are used loosely, it’s not a very formal setup). She rarely calls, especially if it’s a weekend, except in an emergency. I waited until I got home to call her back. And it wasn’t an actual call-out, yet. It was at this point a “potential missing caver.” What this meant in this case was a vehicle had been spotted outside a popular cave, and it had been there for at least 18 hours. That is unusual for this cave, most trips are 2-3 hours in length. So, this was concerning. But, we didn’t have enough information. Was someone in the cave? If so, where? Were they in need of assistance? We needed information, and by hook or by crook we were going to get it. Or at least some of it.

In general, one of the biggest issues we have when starting a cave rescue is the lack of information. In this case case it was even, “are they in the cave?” Had we determined they most likely were, the next question would have been, “where?”. That shapes our search. “How long?” That might shape what equipment we bring on our initial search. “What injuries?” That would also shape our response. In any cave rescue we eventually get the information, but it can be frustrating to have to wait. Caves don’t have cell service inside. (We often do literally put our own phone system into caves during a rescue however!) When we train folks, they often find it hard to believe at first that a patient could be 300 feet into a cave, and it would take a skilled, fresh caver 45 minutes to simply get to them, and another 45 minutes to get back. So as simple a request as “can you get me information about the patient” could easily take 90 minutes or more. And yes, that’s a real life incident.

In this case, eventually the authorities ran the plates and it appears the plates had expired before 1990, the VIN that could be found on the insurance card sitting on the dashboard was made up (or belonged to a vehicle decades older) and the address on the card was fake. We stood down. There wasn’t going to be a search that day. It was entirely a police matter.

#TeamExEvents

I said I’d get to Extended Events and here we are.  I’ve written about them before and I’m a huge fan of them. Simply put, if you’re not using them, you’re probably missing information that you can very useful. I started in the days of SQL Server 4.21a, but really started to cut my teeth on SQL Server with 6.5 came out. Back then our problem sets were probably easier and smaller, but we still dealt with similar issues, the biggest has often been performance related. In the early days there were some decent tricks and ways of diagnosing where your performance bottlenecks were, but to be honest, sometimes it was hit or mess. Over the years, Microsoft has added a lot of functionality to SQL Server including DMVs and Extended Events. I now routinely use Extended Events to track down performance issues or other problems. Last night at our local User Group Meeting, Grant Fritchey did a lightning round where he highlighted one of the features of Extended Events that honestly, I know about, but don’t use enough: Causality Tracking

Causality Tracking Checked

Causality Tracking extends the power of Extended Events to a new Level!

Let’s just say this is a feature that makes picking out the specific events you want to follow much easier. The example Grant gave showed a ton of detail, more than you’d normally need, but extremely useful if you did in fact need it. In other words a simple checkbox can now give us a great deal of useful information.

With the right information, you can often identify bottlenecks and make huge performance gains.

At times I feel like I’m Number Six, trying to get information about a database problem or a potential cave rescue…

Number Six: Where am I?

Number Two: In the village.

Six: What do you want?

Two: Information.

Six: Whose side are you on?

Two: That would be telling. We want information…information… information!!!

Six: You won’t get it!

Two: By hook or by crook, we will.

In conclusion, there are times when disconnecting from the information around us can make a weekend in the woods more enjoyable, but a dearth of it is standard at the start of a cave rescue, while having ready access to it can make solving a problem far easier.

Where do you stand on the information spectrum today? Do you have a lack of it, the right amount, or too much?

 

Trust but Verify

Posted on by
2

This is one of those posts where you’ll just have to trust me. Honestly.

I want to talk about indexes.

About a week ago, a friend on a chat system I use mentioned how one of their colleagues had mentioned, “oh, we don’t have to optimize the database, the server is fast enough” or words to those effect. All of us in the discussion blanched a bit. Yes, when I started in the business a 10GB database was considered large and because of the memory limit with 32-bit SQL, we were limited to 2GB (or 3GB if you took the right steps) of memory so it was literally impossible to keep a large database in memory. Of course now we routinely deal with databases 100s of GB in size with machines that can easily have .5TB of memory or more. This means except for writes, an entire database can easily be kept in memory.

But that said, optimization still matters. Last week I was debugging an ETL process that I’ve helped a client with. I’d love to show screen shots, but my NDA won’t allow me (hence my asking you to trust me). Ok, that’s partly a lie. I couldn’t provide too many details if I wanted to, but the bigger issue is, I’ve since closed the windows I that showed the scripts in questions and the results of my changes.

One of the last things each step in the ETL does is write back to the source table an updated Sales Force id. It’s actually a bit more complicated because what it really does is write to either a Success table or an Error table and depending on a factor or two, a trigger will then update the source table. I had previously debugged and improved the performance of the trigger. But something was still bothering me about the performance. I looked a bit deeper and one of the things that trigger does if there’s a success is make sure to remove the row from the Error table. This was taking longer than I suspected it should, so I dug into it and I noticed that the Error table had no index.  

I can’t show the original queries I used, but I can show an example of the impact of adding a simple clustered index. (See, you can’t even trust me to say I won’t show any examples! You’d better read the entire post to verify what I’m really writing!)

Here’s an example query (with some changes to hide client specific data)

select * from ErrorTable where SF__External_Id__c='005A000022IouWqIAX'

It’s a very simple query (and simpler than the actual one I was dealing with) but is enough to show the value of a proper index.

Now, in my original query, the Query Tuning Advisor actually suggested an index on SF__External_ID__c. In the example above it didn’t. There’s a canard among many DBAs that the QTA is generally useless and often it is, though I think it’s gotten better. As a consultant, I can often come into a new client and can tell when someone has gone crazy with the QTA and adopted EVERY SINGLE suggestion. In other words, they trusted it, but they never verified it. Why is this a problem? Well at times the QTA can be overly aggressive in my experience, suggesting indices that really provide little benefit, or if you add an index in response to a select query that is run say once a day, but where there are 1000s of updates a day, you might actually slow down your updates (since now the update also has to update the index). And as mentioned above, sometimes it might fail to suggest an index. (I think in this case, it didn’t suggest one on my example because the size of the underlying table was far smaller than before).

So, I like to verify that the index I’ll add will make a difference. In cases like this, I often go old school and simply bracket my test queries

set statistics IO ON
set statistics Time ON
select * from ErrorTable where SF__External_Id__c='005A000022IouWqIAX'
set statistics IO OFF
set statistics Time OFF

And then I enable Actual Execution Plan.

The results I received without any sort of index are below. Some key numbers are highlighted in red.

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 47 ms, elapsed time = 63 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(2 rows affected)
Table 'ErrorTable'. Scan count 1, logical reads 3570, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 15 ms.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

You’ll notice the physical reads are 0. This is nice. This means everything is in memory.

In this case, because I’m familiar with how the ErrorTable is accessed I decided a clustered index on SF__External_Id__c would be ideal. (all my updates, inserts, deletes, and selects use that to access this table).

I added the index and my reran the query:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(2 rows affected)
Table 'ErrorTable'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Note the number of logical reads dropped by about a factor of 1000. My elapsed time dropped from 15 ms to 0 ms (or rather less than .05 ms so SQL Server rounded down).

If we look at the graphical query plan results we something similar:

First, without the index:

Trust_but_Verify_Query Table Scan

Table scan to find 2 rows

Trust_but_Verify_Query Table Seek

Table Seek to find 2 rows

That’s nice, I now know I’m doing a seek rather than a scan, but is that enough? I mean if the ErrorTable only has 2 rows, a seek is exactly the same as a scan!

So let’s dig deeper:

Trust_but_Verify_Query Table Scan Details

Query plan showing details for a scan

Trust_but_Verify_Query Table Seek Details

Query plan showing details for a seek

Here you can definitely see the dramatic improvement. Instead of reading in over 100,00 rows (at a bit over 2.5 KB per row, or over 270MB) we only need to read in 2 rows, for a total of just over 5 KB of data.

Now wonder it’s faster. In fact, in the ETL process where it was originally taking about 1 minute to process 1000 rows, my query with the index was now executing 3000 rows in under 10 seconds.

The above is a bit of a contrived example, but it’s based on actual performance tuning I did last week. And this isn’t meant to be a lesson in actual performance tuning, but more to show that if you make a chance (in this case adding an index) you can’t just trust it will work, but you should VERIFY that it has made a difference, and more importantly, that it makes a difference for your workload. I’ve seen GTA often make valid, but useless index suggestions because someone ran an uncommonly used query against it and assumed the recommendation was good. Or, they’ve made assumptions about the size of the table.

So never just trust an index will help, but actually VERIFY it will help.