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!
A sampling of things overheard at our 2015 Performance Troubleshooting Class in Denver:
- “I’m not a GUID hater.”
- “Oh, God help me. I’ve clicked on XML.”
- “Reporting Services looks around at the available memory, makes itself at home, maybe takes its pants off.”
- “Where would I go to check if I — hypothetically — accidentally set my fill factor to 10%?”
- “I use the Database Tuning Advisor for humor.”
- “SQL Server gives you this great set of [partitioning] tools and then sprinkles broken glass all over them.”
- <sirens outside> “The partitioning police are here!”
- “If the index fits, I sits.”
- “You don’t need foreign keys in a data warehouse. Because you have a source system.”
- “This is the title slide. It is also the agenda slide.”
- “It sounds like a bad ad, but the more you query the more you save!”
- <Execution plan recognizes an indexed view and swaps it in> “Let it be known that for once, SQL Server did the right thing!”
- “Great questions. You should have prizes. We don’t have any prizes left, so have some smug.”
- “When there’s no uninstall, there’s your clue that this feature may not be a delicious lava cake. It may in fact be a disease.”
- Attendee: “So how do we know Hekaton ran out of memory?”
Kendra Little: “It stops working.”
- “If your query lasts more than four hours, discontinue using Hekaton.”
- “Tune queries with this one simple trick: TRUNCATE TABLE”
- “There’s a lot of empty lines at the end of this file. I keep scrolling down, expecting to find a bonus album track or something.”
- “Even SHAREPOINT knows better!”
- “The SQL Server is hung. We need a back door.”
Over the years, I’ve done all kinds of awful things with T-SQL and made countless mistakes. Some were harmless; others were borderline catastrophic (exciting times!). I was curious what kind of horrible mistakes other people make, so I threw the question out to Twitter.
Every answer I got was unique, which was both telling (so many ways for developers to mess up) and fascinating (no consensus which was worst). Since I didn’t get any winners by popular vote, here are the top three answers I agree with most, with the worst mistake first:
#1: CODING FOR TODAY
We’ve all been there — we just need a script or stored procedure to run and we’re under the gun to get it deployed. The problem here is that as soon as it works and passes QA — you do have QA, don’t you? — we call it a success and don’t look back. We don’t account for any kind of future growth in data size or user base. Instead of moving on, this is the perfect time to check the working copy into source control and then start refactoring to improve performance.
If we don’t revisit and revise our code, we end up with a server full of code that “ran well enough at the time” but now won’t scale. Future-proofing is never at the top of anyone’s list of priorities but it’s just like exercise and healthy eating. We all know we need to do it. It’s a matter of making the commitment to write leaner, healthier code before it clogs the SQL Server’s arteries or gives it a heart attack outright.
There is no better time to refactor than when you get it to meet requirements. If you don’t start refactoring right away, you’ll forget all of the nuance and context involved with the code, you probably won’t remember how/why you arrived at the code that’s there now, and you may not even get permission to spend time on it later. Future You will thank Present You for it.
THE FIX: Buffer your development time to include refactoring, and make sure you have a performance goal in mind. For example: “We need this procedure to return the top five recommendations for the specified account in under 1500 milliseconds.”
Do you care if your query results are wrong? No? Congratulations, NOLOCK might be right for you!
The trouble with NOLOCK is twofold: developers usually don’t fully understand the risks involving dirty reads, and when they do, they often leave it in code because it’s better to risk sketchy results than move back to the suburbs of Slowville.
There are appropriate circumstances for NOLOCK, but developers often add it blindly as a performance enhancer, not a legitimate solution.
THE FIX: If the risks that come with NOLOCK aren’t acceptable, you can usually fix those performance problems either with code or index changes. For example, if a SELECT and UPDATE are fighting over the same index and locking each other out, an index tailored to the SELECT statement will prevent it from waiting for the UPDATE to release its lock.
Cursors do terrible, terrible things to performance. Every developer has this moment of enlightenment at some point. As soon as we learn cursors are performance killers, we hunt them down like a starving owl in a field of mice. We shouldn’t be embarrassed that we wrote them in the first place; we were solving the problem the best way we knew how at the time. Still, there’s a universal sense of shame when one developer sees another developer’s cursor, as if the neighbor passed by our open garage and spotted the recycling bin full of cheap beer cans and Cat Fancy magazines.
Like NOLOCK, there are times it’s appropriate to use a cursor, but those occasions are very rare.
THE FIX: Write code that operates in sets, rather than one row at a time.
- Overuse of dynamic SQL
- Security by obscurity
- No indexes
- Incorrect data types, leading to implicit conversions
- Not following their own best practices
- The same mistakes they made two years ago
- Hundreds of columns in a table
As a developer, one of the things I can no longer live without is a date table. Who wants to type DATENAME and DATEPART over and over again? Not this guy, and once you have a date table, you won’t want to either.
In this 16-minute video, I’ll give you four reasons why you should stop writing so many date functions and concatenations, and start looking them up in a table instead.
In this video, Doug talks about how changing a column’s data type in SQL Server can be dangerous when there are Access clients using it, and a reference table to help avoid type mismatches when planning data type changes.
Click here to see the list of SQL Server data types according to Access.