Here’s a handy flowchart for whenever you find yourself worrying about fragmentation:
*Talk to your doctor about whether fragmentation is a problem for you. Seek help for index maintenance lasting longer than four hours. Serious side effects may occur if all index maintenance is ignored completely.
Brent says: pop quiz: what are you doing more often, rebuilding indexes or running DBCC CHECKDB?
June 2, 2011. I woke up around 5:00 AM, got dressed, left my cruise ship cabin and went downstairs to find a place to write about the dream I’d just had: A database murdered. Suspects isolated together on a ship. Technical sleuthing.
I knew immediately this was a presentation I needed to give. It would be a huge departure from anything I’d done before. And I wanted to do it on the biggest stage I knew of: the PASS Summit.
Four and a half years later, I did exactly that; I presented SQL Server Mystery Hour: Dead Reports Don’t Talk at the PASS Summit. It’s been a couple of months since the Summit; the euphoria has worn off, and I want to share some of the risks I took — both in getting there and delivering the session — and why that matters to you as a presenter (or potential presenter).
Risks i’m glad i took
Field Testing: I had to run this session several times before feeling reasonably confident I could do it at the Summit. That meant delivering it at SQL Server User Group meetings and SQLSaturdays. They didn’t always go well — sometimes I was done in 45 minutes (too quickly), sometimes I read my lines very obviously just so I didn’t miss something important. I had to learn what worked and what didn’t, and the only way to find out was to do it live.
Call for Speakers: I started submitting my SSRS mystery session to events in 2012, but I also submitted conventional sessions at the same time. I was competing with myself as well as all the other abstracts in that track. This year, if selected, would be my fifth year speaking at the PASS Summit. I hoped my established speaker history might make the Program Committee trust me with a more off-beat abstract (as long as it was well-written). I decided to go all-in this year and submit nothing but murder mystery abstracts. Attempting to up my chances, I did some A/B testing by not explicitly titling them all mysteries (compare the SSRS abstract with this one: Living and Dying by Dynamic SQL). I was incredibly fortunate to get two of them selected for the 2015 Summit.
(Honestly, I’m still shocked they trusted me twice.)
Multimedia: I got a lot of positive comments on the A/V aspect of it. Both sessions begin with a faux Skype call from the CEO (the one and only Buck Woody!) explaining the situation. The SSRS mystery ends with a dramatic re-enactment video featuring the perpetrator. There’s background music while attendees discuss the case. I think that went well and hopefully made the session (and its content) more memorable.
Casting: For a mystery like this to go smoothly, I can’t do all the talking or recap suspect interviews. I have to do them live. I got experienced speakers to fill the cast: Mark Vaillancourt, Jes Borland, Mickey Stuewe, Jason Strate, Gina Meronek, Bill Fellows, Hope Foley, Jason Horner, and Allen White. There’s no way I could have pulled it off without their help. (Thank you, cast!)
risks i wish I had mitigated
Rehearsal: Even though I chose experienced speakers to be the suspects, the sessions felt unpolished to attendees. For SQL Saturdays and user group meetings (where I have audience volunteers read the parts), unpolished is okay. At a major conference, I needed to do more to make sure we didn’t stumble through lines. It’s on me to make sure that, as busy as other speakers are with their own sessions, we get together — if only once briefly — to run through our lines together.
Having said that, spontaneity created the most memorable moments — Mark Vaillancourt’s stream of puns, Jes Borland’s unicorn-flipping exit, and a joke that had Mark crying just a few minutes into the session.
Not planning the gaps well enough: There are breaks in between each chapter of the mystery where attendees turn to each other and discuss the clues and interviews they just saw. This can result in dead time if the groups either veer off-topic or just don’t talk much. I need to do a better job of keeping things on track, perhaps by shortening those discussion intervals.
Pushing the Networking Aspect: The abstract defines the murder mystery as part technical presentation, part networking event. I had roughly 80 and 50 attendees for the two sessions. This was great because people who showed up were willing to talk to others. However, I could have toned down the networking angle and gotten more attendees without their expectations.
risks i wish i had taken
Slide Decks: Before presenting at the PASS Summit, I had given the SSRS session several times at user group meetings and SQL Saturdays. Each time, I’d done them without any supporting slides. No bullet points, no summary slides. The only reasons I needed a projector at all were for the demo and re-enactment video at the end.
At the Summit, I panicked a little and decided not to go with an empty deck. I saw this as a make-or-break year for my mystery sessions and I wasn’t willing to risk screwing it up by forgetting material. I also didn’t want to risk getting skewered by attendees for not having bullet points to follow. I’m not afraid of that anymore (and I shouldn’t have been in the first place, honestly).
Marketing: I went out of my way to say as little about myself and my real-life company as I could. There was already some chatter about the free magnetic poetry we were giving away and I didn’t want to make any more waves. I removed the About Me slide from my decks and didn’t mention my BlitzRS script, even though it would’ve been a natural fit (and of some benefit) to those in my SSRS session. I did have SQL Sleuth badge ribbons that I offered to people for having come to the session, but those don’t advertise anything except the session itself. In hindsight, I could’ve left the About Me slides in without any fuss.
we are ready for risk-takers and storytellers
Whether it’s a user group presentation or a major worldwide conference, our SQL Server community has settled into a comfortable spot regarding session format. The expectation is we sit in a crowd and give a speaker our attention for 60-75 minutes. We take notes, maybe mention something about the session on twitter. That’s perfectly all right.
But what would happen if we took more risks with that model, or broke from it entirely? I found at least one way it can be done. There’s another group who’s been doing something similar (for much longer than I have), presenting a collection of short stories — with demos even! — and the audience absolutely loves it.
We need more storytellers. Audiences love storytellers.
Be a storyteller.
Weaving technical details into a story makes your content memorable for months after it’s delivered. If you have the technical topic in mind but need help with the storytelling or how to convey the material more memorably, read these books:
I’m only beginning to transition from presenter to storyteller. I’m still looking for ways to make the content I share stick in your mind long after the session is over. I’ve found these books to be invaluable and will be working more of their concepts into my 2016 talks.
risks are risky — what if I bomb?
You’ll always be taking risks, but preparation and practice will mitigate the largest ones. You can prepare alone, but if you’re going to try something our community has never seen before, you need to practice with a real-live audience. Find a user group to present to; that’s about as low-stakes as you can go while still having your target audience. If sixty minutes of daring is too much, try thirty. Try ten. Just give it a shot. From there, you’ll get feedback on what works and what doesn’t. Even if you bomb, it won’t hurt much, and you will be closer to realizing your vision.
Stories want to be told
There wasn’t a single day out of the 1,610 days between conception and realization that I didn’t think about this mystery session. I couldn’t get it off my mind. Instead of me having the idea, it’s like the idea chose me — I was just along for the ride.
Is there an idea, a story that has you captivated? Something nagging that won’t let you rest? Stop trying to rest.
Accept that you will have to take risks. Know that the risks are worth it. Tell us a story the way only you can. We’re ready for you to take risks.
Today, I’m going to answer the #1 question I get when it comes to making technical videos: “What gear do I need to make my own videos?” I’ve put together three lists based on different levels of experience and production quality. These lists cover gear only, not essentials of producing a high-quality video, so keep in mind the more high-end list won’t create better results unless you have the skills to go along. This list also assumes you’ll be shooting in an indoor space (as opposed to shooting outdoors or on location).
Before you go shopping
You need to ask one very important question before you open your wallet:
Are you planning to shoot studio VIDEOS, WEBCAST, or both?
Making studio videos means:
- More attention on you (or your props) as the presenter
- Increased importance on visual elements like lighting, scenery, and depth of field
- More eye contact expected as you present
Making webcast-style videos means:
- More attention on the content shared on screen and less on you as the presenter
- Decreased importance on lighting and scenery
- Less frequent eye contact is okay
Your choice(s) here will drive what equipment you need to buy in order to produce a high-quality video on a reasonable budget. You don’t want to overspend on lighting and camera equipment if you’re never going to be shown bigger than a 240×180 pocket in the corner. Likewise, you may not need a USB microphone if you’re going to shoot mostly studio video.
Entry Level (< $250 budget)
This list is for you if you’re just trying out technical videos, not sure you want to make a significant investment, and are willing to buy better gear later on.
Your smartphone or tablet. No, really! Virtually any smartphone made in the last three years will have a camera capable of at least 720p video. This is perfectly good as a starting point, and it costs you nothing except a grip to hold it (like this one) and a tripod to mount it on.
Unless you want to rely on natural ambient light (subject to weather and time of day), you definitely need additional light sources you can move around. Fluorescent light kits are inexpensive, easy to work with, and don’t get burning hot even after hours of use. While softboxes are great for dispersing light evenly, avoid them at this price point unless you don’t intend to take them down. Low-end softboxes are very fragile, and repeated setup and takedown will shorten their lifespan considerably. I recommend you get a light kit with umbrellas instead.
Depending on your camera, you may be able to capture audio directly using an input jack and external microphone. This is very likely the case with your smartphone or tablet. If you decide to go with the webcam instead, whatever you use to record the webcam video should accept a USB microphone.
Anything, and I mean ANYTHING is better than the on-board mic of whatever you’re filming with. This is true of even the higher-end cameras. You want people to hear you, not your surroundings. On-board mics are omnidirectional, meaning they don’t focus in on any single direction.
You can go a couple of different ways depending on how much moving around you intend to do. If you’re going to be relatively stationary, a lavalier (lapel) mic will work well, even at a longer distance from the camera. The Polsen MO-PL1 lavalier microphone can be plugged into your computer or smartphone and has a generous 12-foot cord. If you are going to be moving your arms a lot and don’t want the mic to get bumped by your shirt, a shotgun mic is the way to go — something like the Rode VideoMic Go.
Enthusiast (< $1,000 budget)
This list is for you if you are:
- Reasonably confident in your commitment to making videos
- Willing to make a significant but not semi-professional level investment in equipment, and not interested in upgrading gear for a while
As soon as you leave the entry-level space, equipment costs escalate quickly. Production quality will be easier to improve because you’ll have more control over the audio and video capture process. Your gear will likely hold up a little better too.
You can go a few different ways here, buying either a camcorder like the Canon Vixia HF 600, a compact digital camera like the Canon PowerShot ELPH 300, or a DSLR camera. The camcorder will have one big advantage over the compact digital — a rotating LCD screen. Having a LCD screen you can see while you’re in front of the camera is a big time-saver because you get instant feedback on the framing of your shot. Without one, you won’t know if the top of your head was cut off in the shot until you record and play back. Make sure you get a camera that can shoot 1080p; most cameras are capable of that these days.
If you time your purchase right, you may be able to get an even better camera like a Canon T5i DSLR for just a little bit more than a compact digital camera. If you get that chance, pounce on it. The T5i and T6i offer high quality and user-friendly operation while giving you more creative control over your video.
For this level of investment, it’s worth getting better quality lights that will last longer during operation and survive repeated setup and breakdown. You probably also want to move from umbrellas to softboxes, which distribute light more evenly and produce less edgy light than umbrellas. The more of you that needs illumination, the bigger your softbox should be. Unless you’re doing close-ups, get at least a 24″ softbox, like these softboxes & lights from Interfit.
Like the entry-level setup, your choice of microphone depends on your own movements in your videos. If you aren’t going to move around much, you can go with a lavalier like the Sony. If you plan on moving your arms a lot or can’t be wired, the Sennheiser MKE 400 is a shotgun mic you can mount directly to the top of your camera.
Semi-Professional (< $2,000 budget)
This list is for you if you want to make videos with outstanding production quality, and are willing to invest in equipment for the long haul.
You don’t need to overspend to get an outstanding camera capable of sharp, colorful video at 1080p. The Canon Rebel T6i is the latest in a long line of T_i cameras that are incredibly popular among YouTube personalities for their ease of use and beautiful images. Seriously, there are YouTube celebrities with 100k+ subscribers that swear by them and are still using models as old as the T3i. They’re that good, and they’re very affordable.
It’s worth getting a light kit that allows you more control over how much light is produced, beyond just moving them closer or farther away from you. Get fixtures that have a dimmer switch, or in the case of fluorescent lights (that can’t be dimmed), multiple switches to turn some lights in the bank off. Having more control over light output will be helpful if you’re going to be doing chromakey (blue or green screen) work. The Flolight 110HM3 kit is a good option.
At this point, you’re better off not capturing audio directly to the camera, but instead to a separate device and then synching the audio in your editing application. The tradeoffs of having to sync audio are totally worth it. A dedicated audio recorder will have more options than your camera for adjusting the sound as it comes in. Higher-end microphones use something called phantom power, which simply means the recording device will supply the power to the mic (just like a USB device pulls power from the computer it’s plugged into). They also use XLR cables: thicker cables that are resistant to electrical interference. DSLR cameras don’t accept XLR inputs (roughly the size of a penny — too big) and even if they did, the input jack wouldn’t have enough juice to supply phantom power. Plus, you can have your audio recorder much closer to you than the camera, so your lavalier cord doesn’t have to reach all the way back to your camera.
With XLR inputs, you can go with a lavalier microphone, or with a shotgun mic mounted on a boom pole. With a lavalier, you don’t have to worry about the mic being in the shot because it’s perfectly normal to have it visible. With a boom-mounted shotgun mic, you can get it just outside the frame and still pick up great sound while having a little more freedom of movement than you get with a lavalier. Much more so than with the entry-level and enthusiast gear, the audio you capture will be crisp and clear. For a lavalier mic, a good option is the Sony ECM-44B. I used a shotgun mic, the Sennheiser MKE 600 (connected to the Tascam DR-60D) for my T-SQL Level Up videos and I love it.
Webcaster (< $500 budget)
If you prefer to make videos where you’re only a small part of the picture, you can get by with a smaller budget, especially for video. Proper lighting and clean audio are still important.
Go with the Logitech C930 webcam, especially if you’re on a Mac. The C920 has a nice picture but the software is only officially offered for PCs, not Macs. Both the C920 and C930 are 1080p capable.
Like the entry-level setup, a simple pair of lights and umbrellas will do the trick. Softboxes can be a little intense if you’re sitting at a desk and have them aimed right at you.
Whatever software you’re using to capture video and screen sharing will also capture audio. No need to buy a standalone device unless you choose to get a mic with an XLR connection.
The Blue Yeti is has excellent sound, provided you don’t aim it across a room. It’s wonderful for desktop recording from either mounted on the included base or hanging from a boom arm and shock mount. It connects using a USB cable, so no special connection is necessary.
What about software?
If you’re planning on doing any screen sharing, whether it’s flipping through PowerPoint slides or running a demo in a command prompt, you need something to record what’s happening on your desktop. The easiest option here is to get an application like Camtasia or Screenflow. Both have free trials, so test them out and see which one works for you. For what it’s worth, QuickTime has the ability to record desktop activity but from there, you still have to pull it into an editing app. Camtasia and Screenflow allow you to both capture and edit in the same app.
If you’re not doing any screen capture, you can go with the built-in applications for your operating system — either Windows Movie Maker or iMovie. Both are capable (though their features are limited) and easy to get the hang of. If you’re ready for a slight step up without going full-on pro with editing software, try Adobe Premiere Elements. It’s very affordable ($100 or less) and has a lot of the features you need without the complexity of Premiere Pro.
For more advanced users, Adobe Premiere Pro and Final Cut Pro (Mac OS only) offer a lot more power and flexibility, but they come at the cost of a much steeper learning curve. I wouldn’t recommend starting with them unless you’re seriously committed to spending the next several years learning the application as you go.
What about 4K?
It’s easy to get excited about making ultra-sharp 4K videos, but I’m here to tell you there’s a lot that needs to happen before you think about investing in a 4K camera. Before getting a camera, you need to:
- Decide that making videos is something you want to commit to.
- Learn basic lighting and sound skills
- Learn basic camera work
- Refine your personal style and screen presence
- Refine your lighting and sound skills
- Refine your camera skills
- Upgrade lighting and sound equipment if you have entry-level gear
Plan on these skills taking at least a year, maybe two. The good news is by then, more people will have 4K-ready monitors. And you will have learned enough about video production to make a well-informed decision about which 4K camera is right for you.
No matter where you go, there you are
It’s easy to get carried away with buying video gear. Remember why you’re doing this — to share a message with your viewers. If you focus on great gear but don’t work on your content or delivery, you’ll be wasting your time and money. Don’t massively over-buy; get the gear you need for what you can do today and tomorrow. When your video-making skills have sharpened and it’s truly the gear that’s holding you back, you’ll know precisely what to shop for, and you’ll have some idea if your videos will support a higher budget.
Dynamic SQL can be an incredibly powerful tool in the pocket of a SQL Server developer. Developers frequently express amazement at the level of flexibility dynamic SQL offers, an astonishment at how quickly things get out of hand, and finally the humbling realization that such machinery nearly tore their limbs off. This process can be broken into five stages: The Five Stages of Dynamic SQL Grief. Here’s how to recognize where you, or someone you know, is at in their relationship with dynamic SQL.
Stage One: Denial
“THIS IS INCREDIBLE AND I’m going to use it everywhere!”
Stage one is where the developer will stare slack-jawed in amazement as the statement they just glued together like a words-cut-from-magazines collage not only compiles but returns results as expected. Suddenly, an entire universe of possibilities presents itself. What if we have one code path for admins and a separate one for power users? What if we just roll our INSERTs, UPDATEs, DELETEs, and SELECTs into one stored proc? That way we only have to make one proc per table.
But why stop there? What if we allow the user to pass in the name of the table they want to use? Then it’s one proc for the whole database! Oh this is going to rock people’s worlds when we roll it out.
SYMPTOMS OF DENIAL:
- Disregard for/obliviousness to code performance, security, and the general lunacy of contemplating a universal stored procedure
- Not yet tired of typing single quotes (‘)
Stage Two: Anger
“HOW MANY SINGLE QUOTES do i need?”
Stage two sees the developer having struggled to get their code to production due to a multitude of issues, including but not limited to:
- Coding each of the many branches created by nested IF statements
- Testing and debugging each branch of the code
- Trial and error to figure out how many single quotes it takes to get the statement to compile
During this stage, the developer is irritable, but still resistant to the idea of simplifying anything. Their most important task is to get the code to run, and then ship.
SYMPTOMS OF ANGER:
- Refusal to believe complexity is part of the problem
- Profanity-laced tirades about single quotes
- Unwillingness to discuss the code with others
- Disinterest in security flaws
- Unfamiliarity with Erland Sommarskog
- Occasional lower back pain
Stage Three: Bargaining
“WHAT DO I HAVE TO CHANGE TO GET THIS TO run?”
Stage three begins when the developer can no longer deny being overwhelmed, and they begin to simplify their code. They start looking for areas of obvious frivolity to remove in order for it to test successfully. It is a turning point; they have reached high tide of complexity and must send their wildest ideas back out to sea in order to make their code viable again.
SYMPTOMS OF BARGAINING:
- Willingness to discuss code with others
- Receptivity to feature-cutting suggestions
- Continued disinterest in security flaws
- Admission that they have been overly ambitious in the application of dynamic SQL
Stage Four: Depression
“WHAT WAS I THINKING?”
Stage four is the low point for the developer writing dynamic SQL. At this stage, refactored code now works as designed. However, the security issues which the developer refused to address are now at the forefront, and cause a second round of rewrites. Forcing the developer to undergo a second set of rewrites is the proverbial kicking them while they’re down, and their spirits are at an all-time low.
SYMPTOMS OF DEPRESSION:
- A sudden interest in Erland Sommarskog
- Realization that many of their other dynamic SQL patterns were ill-conceived
- Extended work hours to fix their many, many bad ideas and security holes
- Increased interest in large quantities of ice cream and Netflix
Stage Five: Acceptance
“I was a fool, but now I know better.”
Stage five is the rebound stage for a developer’s initial encounter dynamic SQL. The fog of depression begins to lift as they work through the second rewrite process. This may seem counter-intuitive. It is not the rewrite that lifts the spirits of the developer, but rather the transformation from uneducated caterpillar to educated butterfly that improves their mood. They now have clearer insight into the consequences of their code decisions. They realize dynamic SQL is not a universal translator for turning business logic into a single script, and they will not make that mistake again.
SIGNS OF ACCEPTANCE:
- Willingness, if not eagerness, to discuss the topic of dynamic SQL
- Close familiarity with Erland Sommarskog
- Interest in other dynamic SQL references
- Sense of humility about their code abilities
- Return to nominal interest in ice cream and Netflix
If you encounter a SQL Server developer writing dynamic SQL, use this guide to identify which stage they are progressing through and treat them with empathy. They are going through a difficult time.
If you would like to learn more about dynamic SQL and will be at the 2015 PASS Summit, be sure to come to my murder mystery session, “Living and Dying by Dynamic SQL”.
You spend at least an hour or two each day writing T-SQL code. You have a pretty good hunch that it’s not as fast or crisp as it should be, and you wonder if you’re missing a few techniques. You’ve heard about date tables and windowing functions, but you’re just not comfortable with ’em yet.
I’m Doug Lane of Brent Ozar Unlimited, and my new online training class is for you. It’s loaded with T-SQL goodies like using computed columns, how to replace cursors, and more. Here’s a sneak preview:
It’s unlike any T-SQL training you’ve ever seen (seriously!), and it’s just $29. I’d love to hear what you think.
Our customers, who got free access to it as a thank-you, have already started leaving reviews:
“Great job on delivering information with a wink and a nod. It really held my interest.” – Debby
“Doug is an exceptional speaker, and uses humour in effective ways to convey difficult concepts. I learnt a thing or two from this course.” – Randolph
“If only l had seen this course when l started out my SQL journey! I love the teaching style it works well for me. Then there are those simple time saving tips slipped in just at the right time. The analogy about banking the coins, genius wish l had thought of it! Teaching pace it timed perfectly. Overall looking forward to seeing more ” – Robert
“This is perhaps the nerdiest thing I’ve seen all year.” – Wulfgar
“Great starter for thinking set-based!” – Gustavo
“I loved the Set Based sections with the examples of the cursors and how you could change the code to make them set based operations. But I think my favorite part was the windowing functions. Now I have good examples of how they work and when they are a good fit.” – Colin
“This course is funny and filled with a lot of good information. A great recap for any DBA. And a gold mine for anybody less experimented with coding T-SQL. The Windowing functions part is especially helpful. I suggest this course to anybody writing T-SQL.” – Benoit
There are two kinds of recruiters. The first kind is the Relationship Recruiter. These are the great ones. These recruiters take time to listen, not just to you but to their clients. They try their best to match you and your skills with a client and their needs. If the two sides don’t match well, they don’t try to force it to work. People-based recruiters stay up-to-date on your career. They ask what kind of work you want to be doing — not what you have been doing — and try to place you somewhere that will help you get there.
Then there’s the second kind of recruiter. The Shotgun Recruiter. These are the people whose email you reflexively trash because you already know how bad it’s going to be. The ones who will send you an opening for a Visual Studio Forms Designer when you’ve been an operations DBA for twelve years and only listed Visual Studio on your LinkedIn profile because you had to emergency repair an SSIS package late one night. I feel for these people; recruiting is hard work. But it’s still work (or at least it should be):
We want to hear your Shotgun Recruiter stories. We know you have them. We know they’re amazing. Send us your worst/funniest/strangest recruiter stories and we’ll share the cream of the crop.
But that’s not all.
We don’t want to destroy your faith in humanity without building it back up again. Therefore, we also want your stories of surprisingly spectacular recruiters — those who went out of their way to make people (or at least the two of you) happy. Like we said, there are two kinds of recruiters. We want to hear about them both.
If you’ve got a great recruiter story and want to share it on Dear $firstname, please send it to firstname.lastname@example.org. Thanks!
Brent says: I get so many of these emails that I had to come up with an email template for recruiter replies.
I’m delighted and honored to be presenting two sessions at the 2015 PASS Summit in Seattle: “Living and Dying by Dynamic SQL” and “SQL Server Mystery: Dead Reports Don’t Talk”. Since I’ve never done either of these sessions at the Summit before, here’s a little more information about the sessions to help you decide whether to attend:
- Although the titles differ, these are both murder mystery sessions, modeled after a murder mystery dinner party. Except we won’t be serving food.
- Like a murder mystery party, you’ll be conversing with the people around you. You’ll discuss clues and work as a group to solve the mystery.
- The suspects will be sitting in the crowd too; you may end up next to one of them.
- The mystery loops through a pattern 3-4 times: topic – interview – clue. This means every 2-3 minutes, we’re moving on to the next step and keeping things lively.
- The two main goals of the session are for you to learn more about 1) the technical topic and 2) the people you’re sitting with. The PASS Summit is as much (if not more so) a networking event as it is an educational event. I want to emphasize both points in the same session.
- In the coming months, I’ll be retweeting people at Cromulent Technologies — people you’ve never heard of. If you’re thinking of coming to my session(s), pay special attention to these retweets. Cromulent Technologies is the workplace of the principal characters in our murder mysteries.
- If you’re familiar with other speakers in the SQL Server community, you’ll probably recognize a few of the suspects.
I promise you, you’ve never seen SQL Server presentations quite like these. I hope to see you there! Got questions? Ask away in the comments!
Brent says: I love stuff like this that breaks up the monotony of conference sessions. Make me think in new ways and keep the conference lively – that keeps me engaged.
I recently came across a curious case where a SQL Server was suffering a number of long-running queries coming from an application written in Entity Framework. When I measured the average query execution times, I got some unexpected results:
- CPU time: 12 milliseconds
- Reads: 273
- Elapsed time: 6800 milliseconds
Looking at the wait stats for these queries, I saw there was a lot of ASYNC_NETWORK_IO — often 1000+ milliseconds. That didn’t make any sense either! How can a query with so little CPU time and so few reads take so long to complete? It’s not like the application was asking for millions of rows and couldn’t consume the results fast enough.
I ran the query myself in Management Studio:
- CPU time: 17 milliseconds
- Reads: 273
- Elapsed time: 155 milliseconds
These numbers made a lot more sense, and confirmed that the application was to blame. But what exactly was the application doing for 6645 milliseconds?
Entity Framework will try to be clever about connections
After some research, I found that Entity Framework can be sneaky about how it manages database connections. An article about EF connection management on MSDN proved both enlightening and slightly terrifying:
Entity Framework will handle database connections automatically by default. Note two things here: EF will open the connection if you specify any LINQ or ObjectQuery method, and that connection won’t be closed until the ObjectResult has been completely consumed or disposed.
In this case, the EF code invoked the Where method, then went on to do a number of foreach loops, assuming the connection had been closed when it hadn’t. SQL Server was left holding the phone until EF said good-bye and hung up. The solution here was to open the connection, do as little as necessary, and then close the connection.
UPDATE: EF MVP Julie Lerman (@julielerman on Twitter) mentions in the comments below that the MSDN documentation is outright wrong about some methods opening a connection, and has passed this on to Microsoft so they can correct the error.
This would be the end of the connection management story, except…
Entity Framework would like to announce it will no longer be clever about connections (when you open them yourself)
Another MSDN article about EF connection management points out changes to Entity Framework 6 and later:
Again, the seemingly innocuous and trivial “Note” is anything but. It’s a reasonable assumption on Entity Framework’s part; if you’re going to open it yourself, you’ll be the one who closes it. Still, it means we have to be careful with Entity Framework code when it comes to database connections. And now, depending on the Entity Framework version, we’ll see one of two different connection management behaviors.
How to spot Entity Framework keeping connections open
The tell-tale signs, as we discovered in this case are:
- When running the query from the application
- Relatively low CPU time but high elapsed time when running the query from the application.
- ASYNC_NETWORK_IO waits for the query
- When running the query from SQL Server Management Studio
- Relatively similar CPU time and elapsed time when running the query from Management Studio.
- Significant amounts of application code that execute in between the connection open event and close event. To prove the connection is left waiting during the open and close events, step through the code in a debugger and pause before the connection is closed. You should see the query racking up ASYNC_NETWORK_IO waits. (Remember, the events that open and close the connection may not be explicitly doing so.)
IT’S NOT A SQL SERVER PROBLEM. IT’S An entity framework problem.
Entity Framework is great for developers who don’t have the spare time or motivation to learn SQL Server querying, but that convenience comes with costs. One of those costs is keeping a suspicious eye on how it manages database connections. It’s tempting to look at a long-running query and blame SQL Server for being slow. However, if this happens to you and your Entity Framework-based application, it’s worth investigating further to see who’s leaving whom waiting.
Brent says: don’t be ashamed of using EF, either! I’m all about getting to market as quickly as possible. After all, if you don’t get your app out there, you don’t get paid, and DBAs need to get paid too.
I’ve been doing SQL Critical Care® work with clients for over a year now. It’s absolutely true that every client’s situation is different and needs special attention. However, I’ve found that there’s a short list of issues that nearly all clients have in common. Drawing from that list, here are the top three high-impact, low-effort areas you can work on today to tune up your SQL Server.
Check power savings everywhere
I recently had a client whose CPU would peg at 100% for short stretches even though the server was under a light load. After some exploration, we found they had power savings turned on. In the BIOS. Because we couldn’t get to the BIOS remotely (and we didn’t want to reboot the server right then and there), we used a free tool called CPU-Z to watch core speeds. Can you spot the difference in speeds?
That’s a whole lot of CPU speed you paid for and aren’t getting! When checking power savings, make sure you check all of the following:
- Windows Power Options (under Control Panel)
- If a physical server, also check the BIOS at startup
- If a virtual server, check:
- hypervisor power settings/performance plan
- BIOS at startup of the hypervisor host server
One other thing: make sure you don’t have outdated BIOS firmware. That can have some nasty CPU consequences too.
Changes to the BIOS require a server restart, so plan accordingly.
How to tell if it worked: Using CPU-Z, watch the Core Speed box at lower left. If it deviates from the core speed in the Specification box by more than approximately 1%, there’s probably power savings turned on somewhere.
address hot missing index requests
Every time a query runs and wants an index that doesn’t exist, SQL Server files that missing index request away. You need to know which missing indexes are being requested and how helpful that index will be. There are DMVs you can query for this information, but my favorite method is sp_BlitzIndex®. It will tell you the missing index definition, as well as three numbers:
- Impact – the difference this index is expected to make in query cost.
- Avg. Query Cost – the price in magical Microsoft Query Bucks as determined by SQL Server.
- Uses – the number of times this index would have been used.
The product of these three numbers (Impact x Cost x Uses) equals the Estimated Benefit.
Because these numbers get reset with every service restart, we need to factor in uptime as well. If you’ve been up for seven days or more with your average user load during that time:
- Est. Benefit > 1,000,000: Keep an eye on this index.
- Est. Benefit > 10,000,000: Try it out in dev environment and see how it does.
- Est. Benefit > 100,000,000: Try it out in a dev environment — TODAY.
I can’t recommend outright that you deploy these missing indexes to production, just that you test them. One reason: it may be more efficient to alter a very similar existing index rather than add the new one. You’ll have to decide what’s best, but don’t let the 100M+ indexes go unnoticed.
And in case you’re wondering, the record high I’ve seen for the Estimated Benefit number is just over 14,000,000,000 (billion). Let’s hope you can’t beat that. (You don’t want to.)
How to tell if it worked: This missing index recommendation should go away in sp_BlitzIndex® results and the query running it should decrease in CPU time and cost.
Raise cost threshold for parallelism
SQL Server’s default Cost Threshold for Parallelism (CTP) is 5. Microsoft has left this setting untouched since the 20th century.
(Don’t laugh — that was at least sixteen years ago.)
Face it — pretty much everything that old needs an update. Your hairstyle. Your clothes. Your car. Your server’s CTP is no different. CPUs are way more powerful today than they were in the late 90’s. With CTP set to 5, chances are a lot of queries are going parallel when they don’t have to. Raise the bar to 25 or even 50 (as always, test this in dev first). Unlike licensing additional cores, changing CTP settings is absolutely free.
How to tell if it worked: You should see a drop in CXPACKET waits, along with some query plans no longer showing parallelism.
There you have it: three simple and quick checks to speed up your SQL Server. For more ideas, try Five SQL Server Settings to Change.
Brent says: Listen, people, this is free advice. Don’t pay us to tell you this. Just do it today. Then pay us anyway. Actually, ignore this post.
When it comes to hiring a SQL Server developer, we can pepper candidates with HR-type questions (“Tell me about a time when you had a conflict with a co-worker and how you resolved it.”) but that doesn’t give us a good sense of a developer’s T-SQL skill level. Here are five questions that will give you a good idea how experienced and skilled a developer is.
1. “WHEN IS IT OKAY TO USE A CURSOR?”
Cursors are the most widely misused T-SQL tool out there because beginning developers writing them haven’t yet learned to think in sets. Once we learn how cursors really behave, it’s tempting to swear them off for good. Senior developers ought to be able to provide an example of when it’s really okay, such as a script that does index maintenance or DDL commands.
- Junior Developer answer: “Anytime.”
- Developer answer: “Never.”
- Senior Developer answer: “When there’s no way to accomplish the task in a set but you have to go through multiple iterations.”
2. “WHAT ARE THREE CODE CHANGES YOU’D MAKE TO SPEED THIS QUERY UP?”
The trick here is having code that might be passable but still leaves plenty of room for improvement. This isn’t a question a manager can ask; an experienced developer will have to come up with the code and decide if the suggested improvements will work. (If you’re the one writing the sample code, here are a few ideas:
user-defined functions, table variables, unnecessary sub-queries, and non-SARGables in the WHERE clause.)
- Junior Developer answer: “I dunno, looks good.” (reaches for something insignificant to change)
- Developer answer: “Oof! Who wrote this?” (goes on to list examples)
- Senior Developer answer: “Here are a few things I see…”
(goes on to list examples and sprinkles in a story about how they learned that by experience)
Although it’s nice to get contextual questions back (like if any indexes exist that can be leveraged) but really it’s about spotting purely code-based inefficiency.
3. “WRITE CODE TO CHECK IF ANY RECORDS EXIST.”
A very common code scenario involves checking for the existence of any records that match certain criteria (or just for a table as a whole). The trick here is that all we care about is a yes/no answer. We want that check to be quick and cheap.
- Junior Developer answer: “SELECT COUNT(*)…”
- Developer answer: “SELECT COUNT([field name])…” (Bonus points if they say the field is indexed.)
- Senior Developer answer: “SELECT TOP 1 1” or “EXISTS (SELECT [fieldname]…)”
4. “WHAT IS PARAMETER SNIFFING?”
When it goes bad, parameter sniffing is a tricky problem to identify for developers. It can make a query perform like Dr. Jekyll one minute and Mr. Hyde the next. There are some things you can do to mitigate risk with parameter sniffing, but just understanding its nature is a big step. Bonus points to anyone who explains how they dealt with parameter sniffing in the past.
- Junior Developer answer: “I’ve never heard of it.”
- Developer answer: “It’s something to do with the query guessing wrong.”
- Senior Developer answer: “It’s when a query uses a plan based on a different parameter value than the one being passed in and the resulting execution is potentially awful.”
5. “DESCRIBE HOW YOU RESEARCH A PROBLEM YOU’VE NEVER ENCOUNTERED BEFORE.”
I’ve yet to meet a good developer who wasn’t also a good researcher. Our success depends on our ability to fill in our knowledge gaps quickly. Books Online is great for syntax but not insight. Technical books are only good if they address your exact problem and you can’t copy/paste directly from them. (Speaking of copy/paste, “search online for the exact error text” is a great answer too.) StackOverflow and Twitter are high-traffic places where people are eager to help you.
- Junior Developer answer: “I go to Books Online or look in a technical book.”
- Developer answer: “I search online, especially on StackOverflow or MSDN forums.”
- Senior Developer answer: “I check sites like StackOverflow or throw the question out on twitter with the #sqlhelp hashtag.”
These are the big questions that came to my mind. What questions have you found helpful in hiring developers? Let us know in the comments!