Brent says: remember a couple months back when we announced that we were hiring a SQL Server Triage Specialist? We’re really proud to welcome Angie Walker to our funky little medical team. When you bring us in for a SQL Critical Care®, Angie might be the first person you work with to make sure the server’s in a safe place. Let’s hear how she got here….
Angie says: WHERE TO START…?
As with many IT professionals I’ve met, I didn’t intend to find a career in IT, but took when an opportunity came along a few years back, I jumped at the chance. After a couple of years, I moved to a Junior DBA position and I’ve been learning SQL Server ever since. When I first started, I couldn’t tell you what a database was. I remember the first time I forgot the WHERE clause on an UPDATE statement, blindly deploying code into production, and asking why we take backups.
The first session of the first SQL Saturday I attended was so packed, I sat on the floor, and watched Kendra present “How to Find Bad Queries, Fast”. I didn’t know what she was talking about half of the time (What’s Perfmon? Activity Monitor is the graphs, right? She has pink hair!), but I hoped that if I took enough notes, I might be able to absorb some of what I heard. And I keep going to SQL Saturday and SQL user groups for that exact reason. I might not understand today, but one day it might click when I see the issue or feature firsthand.
The world of SQL Server is vast, murky, and sometimes scares me, but knowing where to look for help has been vital. I’ve been following Brent Ozar Unlimited for almost three years; signing up for the Accidental DBA 6 Month Training Plan, taking advantage of Kendra’s “DBA Job Interview Question and Answer Kit” for my first DBA interview, trying (and failing) to score killer Black Friday deals last year, and of course, following the blog every day.
So when I saw the open Triage Specialist position, I knew I HAD to apply. The morning I woke up, checked my email, and saw Kendra Little was emailing me, I was bouncing around as if I drank three cups of coffee. I mean, I read the blog and use the tools, and even though I know they might be real people, the possibility to work alongside some of the most well-known folks in the business seemed too good to be true! When I met with Kendra she was so nice and I thought it went really well, but when she said it would take about two weeks to hear back I had to stop myself from thinking that she was trying to let me down easy. I didn’t know just how badly I wanted the job until I got the invite for a second interview – It didn’t matter that I didn’t know everything about SQL Server, I have to learn it, and who better to learn from than Brent Ozar Unlimited?! So I prepared myself for the interview with Kendra, Jeremiah and Brent… And I must have done something right, because here I am!
I’m super excited for this new adventure as the newest member of the Brent Ozar Unlimited team!
SQL Server 2012 SP3 has just been freshly released! Not only does this release contain bug fixes, it also contains a slew of performance tuning related features.
Many of these features are about memory grants. This is NOT the memory used in the buffer pool/ data cache by the query — it’s the memory also known as Query Workspace Memory. Start reading more here.
Check these out:
KB 3107398 – Improvements to sys.dm_exec_query_stats
This is kind of a big deal. The sys.dm_exec_query_stats DMV will now report on total, last, min and max statistics for granted workspace memory, ideal workspace memory, degree of parallelism, and reserved threads. These columns are in SQL Server 2016 CTP3, but it’s really awesome that they’ve been backported town to 2012!
KB 3107397 – “Actual Rows Read” Added to Query Execution Plans
This is really exciting! It’s hard for people to read execution plans. You may have a very large index seek or scan that reads a ton of rows, and it has a hidden filter in the seek or scan. But it may be hard to diagnose because you only see the number of rows to come OUT of the seek or scan operator. This lets you know not only the rows that exit the operator, but how many it had to consume, too. It’s sorta like having a little bit of STATISTICS IO in your execution plan!
KB 3107401 – New Query Hints for min_grant_percent and max_grant_percent
Previously, if you identified that a query was asking for a problematic memory grant, your options were pretty limited – code changes, index changes, resource governor, or server wide settings. These options let you
shoot yourself in the foot tune this just for a single query. Check out the KB– there is a safety in the feature. If the minimum required grant is higher than max_grant_percent, it will still get the minimum.
KB 3107172 – hash_spill_details Added to Extended Events to Diagnose tempdb Spills, Improvements to sort_warning and hash_warning
Ever seen sort or hash spill warnings in execution plans and wondered how much of a difference they were making to the runtime of the plan? That may be less of a mystery with these improvements.
KB 3107173 – query_memory_grant_usage added to Extended Events
This XEvent allows tracing for the ideal memory grant for queries, what they actually got, and the degree of parallelism they used.
What About SQL Server 2014?
I don’t believe any of these are currently available for SQL Server 2014, but looking at past release patterns it’s likely they’ll be available soon.
PSA: Don’t Forget to Test Your Service Packs
Even the best laid plans can sometimes go awry.
We sometimes help clients interview DBAs and Developers for their team. Requirements vary depending by position, but sometimes it’s important to test a candidate’s TSQL skills.
While I can test candidates thoroughly on strategies for TSQL (when they’d use certain constructs, the pros and cons of various approaches), if what you care about is skills writing complex TSQL there’s one surefire test: a lab.
The first step is to pick your dataset and environment, then dream up the TSQL query you’d like your candidate to write. Next, test it with one of your own staff members and make sure the problem is suitable for the job you’re trying to fill.
But you may not want to give candidates access to your production, or even development data — after all, they don’t work for you yet.
Good news, there’s an easy way to do this online! Check out the Stack Exchange Data Explorer. You can assign the candidate a query to write using this tool online.
How to make it work:
- This only works for in-person interviews. Because human nature.
- You’re going to need a moderator and at least 30 minutes, because the candidate will need to figure out the schema.
- A history of queries is kept in the tool. You’ll need to let them know they can’t search that history, and it’s up to you if you’re going to let them use Books Online (hey, we all use it in the real world).
If you don’t want to deal with the hassle of the query history online, you can always download a copy of the Stack Overflow database and set up your problem on a locally installed test instance of SQL Server, too.
The secret test: if your candidate likes writing TSQL, they’re going to love the challenge and it will energize them.
Query Store is so cool
Billed as a flight data recorder for SQL Server, the Query Store is a repository of execution plan information, like the plan cache, except a bit more actionable. And it has a GUI.
You can read all about what it does and what you can do with it around the internet. You can be suitably impressed and enchanted by the promise of data that’s persisted between restarts, being able to quickly and easily address plan regression issues, and so forth.
I’m here to bum you out when you’re done with all that.
You can’t use it in tempdb
On tempdb? Whatever.
What? Really? With all the stuff that goes on there? Aside from the many selfish demo reasons I have for wanting it, I know lots of people who make tempdb their default database for user logins so they don’t go messing things up in the real world, or more fragile system databases. I’m also aware of several vendor apps that dump users into tempdb and then use fully qualified names to point them to application data.
I found this all out by accident. I had just nabbed CTP 3 of SQL Server 2016, and was settling in for an evening of staring contests with execution plans and new features.
tragedy struck I got an error message:
ALTER DATABASE tempdb SET QUERY_STORE = ON; Query Data Store cannot currently be enabled on tempdb Msg 12438, Level 16, State 1, Line 1 Cannot perform action because Query Store cannot be enabled on system database tempdb. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.
And yeah, it’s an official no-go
I’m all disillusioned and stuff, man.
This is going to be frustrating out in the wild for a lot of people. Not just fancy-
pants camo shorts consultants who like to make demos where they treat tempdb like it owes them money. Real people. Who pay real money, for real servers.
Anyway, I started a Connect item to get this changed. Vote for it. Or don’t. Just don’t vote more than once, or if you’re dead.
One of the great things about the SQLPASS Summit is the annual Women in Technology lunch. Not only does the lunch feature an interesting presentation, but you get to talk to men and women at your table about diversity, how to build better teams, and how we can help others.
Many of the best moments at conferences happen when you connect with your peers. The two things I found most fascinating at the WIT lunch this year came from my table and from audience questions.
We can give back. Kathi Kellenberger’s doing it!
I was lucky to sit at a table next to Kathi Kellenberger. She shared with me that she volunteers as a mentor for LaunchCode. Kathi volunteers as part of this free, non-profit program to inspire and teach talented people and help them land jobs in IT. She trains her group regularly in person and provides awesome, free online resources for CoderGirls.
I’m really inspired by the work Kathi is doing. Rock on, Kathi!
Harvard offers free tools to test for unconscious bias
Cindy is on the Azure CAT team, and has recently begun presenting to people and encouraging us all to take free online tests to help us identify our unconscious bias as a first step to building better, happier teams.
Thanks PASS, for hosting such a terrific lunch. I’m already looking forward to attending next year.
Earlier this week I had a great time teaching a day on index tuning at the 2015 SQL PASS Summit. Along the way I got great questions! Here’s the links that came up to answer them:
The Data Loading Performance Guide by Thomas Kejser, Peter Carlin and Stuart Ozer.
Deleting LOB Data and Shrinking the Database by Denny Cherry.
Slow in the Application, Fast in SSMS by Erland Sommarskog.
Conor vs. “Does Join Order Matter?” by Conor Cunningham.
Connect Item: Rebuilding an index clears stats from sys.dm_db_index_usage_stats by Joseph Sack.
Limitations in Indexed Views from Books Online.
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’ll learn what you need to script and document from your current instance, which settings you should change after your migration, required tests for new installations, and better alternatives to in-place upgrades.
Whether your SQL Server 2005 instance is hitting end of life or you’re planning an upgrade to the latest and greatest, our migration checklist will save you time and frustration.
Watch this free video online now at the Veeam site here (registration required).
Download the SQL Server Migration Checklist as an eBook from our free First Responder Kit.
How hard is it for a systems administrator who’s used to running SQL Server on Windows Clusters to tackle Availability Groups? Our example system administrator knows a bit of TSQL and their way around Management Studio, but is pretty new to performance tuning.
Well, it might be harder than you think. First, let’s look at the skills needed to succeed. Then let’s tackle a sample training plan to get those skills.
Do You Have the Skills to Manage AGs? Take this Test!
sp_Blitz® Sanity Test
- Have you eliminated all warnings regarding backups, CHECKDB, alerts, and other administration basics that our free sp_Blitz® script warns about? (Not thought about eliminating them, actually eliminated them.)
- Have you established clear RPO and RTO for the databases you’re considering for AGs and made sure your backups and restore plans are meeting them? (Not an estimate, actual sign-offs by business owners.)
Incident Management Skills Test
- Do you regularly use techniques to learn what’s running and how long it’s been waiting at any given point in time that are NOT SQL Server’s Activity Monitor or sp_who/sp_who2? (Sorry for the bias, but Activity Monitor is full of lies, and those SQL Server 2000 tools don’t really help anymore.)
- Do you have a documented incident response process to collect data when performance or availability suffers that at least 2 people can run?
- Do you regularly execute a process to perform root cause analysis when performance is bad or the SQL Server is offline? (Only answer “yes” if it has successfully led to a real root cause 3 times in the last year.)
Performance Tuning Skills Test
- Do you regularly analyze your SQL Server wait statistics and baseline them? (A “yes” means that you know what your top 10 bottlenecks mean, what “poison” waits are and what they mean if they show up, and you could quickly identify any changes in your top waits and react.)
- Do you know the top 10 queries that run against your SQL Server by CPU, and have you tuned their indexes?
SQL Server Scripting/Configuration Skills Test
- Do you have the scripting skills to be able to handle managing jobs across all nodes? Jobs must all run everywhere, and be smart enough to know who is the primary replica.
- Do you have the scripting skills and security knowledge to be able to manage linked servers and logins across all replicas? (Don’t say yes if you haven’t done it before– it’s trickier than you think!)
Windows Clustering Skills Test
- Have you successfully executed basic Windows clustering management tasks with the version of Windows you plan to go live on? These tasks are: configuring quorum, getting clean cluster validation reports, and using the Cluster Failover Manager.
- Have you practiced advanced cluster rescue scenarios at least twice within the past year? (These tasks are: forcing quorum, generating and reading cluster logs)
Testing and Patching Skills Test
- Are you subscribed to and do you actively review all hotfixes that come out for your version of Windows Failover Clustering and SQL Server?
- Do you test and apply hotfixes and selective updates to a test environment on a regular basis as part of a patching deployment for production?
Here’s the deal: to do well with Availability Groups you need to honestly say yes to every single question on this list.
Closing the Gap: A Training Plan
The right training plan for Availability Groups includes learning from others as well as hands-on experience. To make this work, you need to be devoting at minimum 12 full days per quarter to learning. That’s at least one full day a week, and even then, your time may be very, very tight.
First Quarter: Learn the Pitfalls of AGs and Build your Incident Response Process
If you didn’t score two “yes’s” on the sp_Blitz® sanity test, that’s priority #1. Make sure you have completely covered that section before moving on.
Next, make sure you understand why you need to learn so much, and also make sure you really need AGs! Dig in and learn the basics about Availability Groups and why they’re so tricky, and how to choose the right HA/DR solution in SQL Server. Options:
- Free option: Get reading on our blog, we’ve got tons of articles.
- Video training option: Take our $299 online course – The DBA’s Guide to High Availability and Disaster Recovery
Next, get working in production and build your incident response kit. It will take work and time to get incident response and root cause analysis really working, but this is an absolute pre-requisite for working with AGs.
Second Quarter: Baseline and Analyze Wait Stats and top Queries
Focus on establishing lightweight monitoring and baselining your top waits and queries in SQL Server.
- Free tools: sp_AskBrent® helps you with wait information and sp_BlitzCache® helps you with queries
- Books and blog posts: We have a list to get you started here
- Video training option: Our Developers Guide to SQL Server Performance class walks you through it for $299.
Third Quarter: Build a Prototype and Get Advanced Training
At this point, you’ve learned a lot about how to understand what SQL Server is telling you. It’s time to start understanding Availability Groups specifically.
- Review your notes from what you learned about looking out for in AGs– and make sure you plan the right version of Windows and SQL Server that’s really right to use.
- Set up a prototype environment. You need the same hardware and storage that you’re going to use in production. This isn’t a temporary environment, either: to live well with AGs you need a pre-production environment!
- Restore full sized production databases to your AG environment and generate activity against it.
- Make the environment fail! You need to cause failures in multiple places and validate that your incident and performance management tools really work in this brave new world.
- Oh, and work on all those scripting skills, too.
At this point in the process, it’s time to learn from others and really make sure you’re on the right track. You can do this in multiple ways:
- Attend a SQL Server conference that lets you attend “lessons learned” style panels– such as the SQL PASS Summit
- Attend an advanced SQL Server DBA training class like our Senior DBA course
Fourth Quarter: Reassess Your Progress and Fill the Gaps
You’ve come a long way. But you still had a job to do with other tasks in it.
Step back and re-take the quiz. How are you executing on all of those tasks, and how many people can execute on them? Return to the areas where you’ve scored the weakest and build those skills up.
Yep, it Takes a Year
Availability Groups are a very cool, cutting edge tool in SQL Server. They’re expensive, but people are willing to pay the price because availability and performance really matter for them.
But if you’re not careful, the tool that you’re using to try to improve performance can slow you down– or take you offline entirely. And if you don’t have really strong incident management and performance tuning skills, you’ll have no idea if the problem is the AG or something completely unrelated.
The good news is that if you have a foundations in system administration, you know some TSQL, and you really focus your efforts, you can learn these skills in a year!
Not Sure Where to Find the Time?
Show your manager this post and talk about your answers to the questions above, and the fact that you need to fill the gap. Be clear about areas where you and your team are weak and strong, and what risks you’re concerned about if you take on more complex technology that you can’t handle. It may not always be obvious, but usually your manager wants you to succeed as much as you do!
Decisions are emotional, right? Brent loves Sanka and I love Postum. We fight about it all the time. But when we wanted to settle the debate once and for all, we engineered a test to figure out who was right. You can do the same thing to take the personal investment out of technical decisions at work.
Check Yourself at the Door
The first thing you need to remember is that your opinions are just as valid everyone else involved. You need to move those aside and be ready to be wrong.
If the other people involved don’t want to play along, just tell them “Heck, I’d be happy to be wrong because I’ll learn something.” It’s not a bet or a contest, you’re just offering up your willingness to be wrong. Being wrong is great, especially when it’s your turn to be right later on.
Test, Test, Test
The next step to making that decision is to figure out a test. This test has to depend on your different opinions. The purpose of this test is to get your opinions out of the conversation.
Doing this correctly is really hard. You need to figure out:
- What are both sides saying?
- Which metrics will prove both points?
- What won’t prove anyone’s points?
- What’s the fastest way to test both options?
- What’s a realistic scale to for testing?
- What’s the worst that could happen if either side is right?
- What’s the worst that could happen if either side is wrong?
- If you can only run one test, which test should you run?
Hey, You Said This Was Easy!
All of this sounds like a lot of work. It turns out being factually right is just as much work as being factually wrong. If you really want to make sure that you’re choosing the right solution to a problem you need to figure out which option is the most right way to solve the problem – both solutions could be good, but one just might be better. The only way to get to proof is to test everything.
The next time there’s an argument on your team, or between two teams, figure out the best way to test each side’s ideas instead of spending your time arguing about which solution is the best.
Of course, you could always just fight in the parking lot like school kids. I hear that works well, too.