Give More Feedback

I write on my blog and get a couple comments at best. I talk at conferences and a large part of the audience fills out evals at the time.  Then I often wonder if I’m making a difference while rarely, if ever, knowing if anyone actually used what they learned at work.  There are some confidence issues at hand here, which I was convinced were just limited to me.  However, talking to a couple others in the SQL community, it’s not just me.

In fact, I told one of the leading respondents to SQL Server Central forums questions that he really made a difference in my career with the quantity and quality of the knowledge he shares.  His response was a lot like I’d imagine mine to be.  A humbleness and sincere thank you with a follow up that it’s very difficult to know if the work you put into the community really makes a difference.  That’s saying something considering that he’s in a more personal position than me with his online presence because he’s often answering user questions while I offer unfocused unsolicited advice.

The blog posts, articles, and forum help you see online is all done on a volunteer basis.  Sure, some people get paid $25 for an article, which is a lower hourly rate than an entry-level DBA if you think about how much work they put into them.  Some people write blog posts to promote their business or help their careers while knowing without a doubt that well over 99% of people who read what they have to say will never hire them.  Yes, there are ways you can say it’s not on a volunteer basis, but if any of us were really in it for the money then almost all of us would opt for something more lucrative such as setting up a lemonade stand on the corner with our kids.

That wasn’t even getting into in-person events like SQL Saturdays where volunteers put everything together then ask for volunteers to come speak, many of whom pay their own travel expenses.  Full disclosure, it’s not completely unpaid, we get invited to a free dinner the night before and typically get a free shirt.  Both of these are amazing benefits because I’m eating dinner with people I look up to then I have a shirt that I wear to work every time I need a good confidence boost, so I can’t say it’s really free.  By-the-way, every SQL Saturday is numbered, and I they all have an email address of  Help keep the motivation going and force me to update that masked email address to have four digits!

So, you may notice that I write at length about not getting much feedback on my only post that does not allow comments.  I even went out of my way and deleted the name of the guy whose answers I liked so much on SQL Server Central.  Why?!?!  It’s not as counter-productive as it seems.  I know this post didn’t make your career better, I know it’s not likely to change your life, and this isn’t a plea for you to comment on THIS post or for people who inspired ME.

This post is a challenge to you.  Think back to blog posts that helped make you awesome at work.  Think about conferences that were put together so well that all you noticed were the great learning opportunities.  Then go out and comment as publically as possible how it helped, adding as many details as you can.  After that, keep this in mind when you’re learning in the future, be it free or paid events, reading blog posts, or using #sqlhelp on twitter.  Say it all, constructive criticism, compliments, offers to buy someone a beer, don’t hold back!  If you want more of something, speak up.  We have an amazing community, and I want more of it!

By the way, the guy from SQL Server Central likes plain old Budweiser.

Technical Interviews – How to Thrive

There’s a trick to technical interviews. Every question is looking for integrity first, and intelligence and energy second. This is paraphrasing Warren Buffet, who became the most successful investor out there by becoming the best at interviewing the management of the companies he was investing in.

The Warren Buffet Way

“In evaluating people you look for three qualities: integrity, intelligence, and energy. And if you don’t have the first, the other two will kill you.” -Warren Buffet pp. 172-173

To understand what answers are right you need to understand the motives of the interviewer. Why are they asking these questions, what are they looking for? Yes, you can cram for an interview and hope you get more questions right, but that isn’t what’s going to make or break you. It may help you represent yourself as more of a subject-matter expert than you are, which can be both good and very, very bad. What they’re looking for is someone who can be a good employee a lot more than someone who can currently pass a written exam.

In the end, if you don’t know how to answer a question I know you can go look it up. However, what integrity do you have when you don’t know the answer? This is where all those years you spent in school are going to fail you, miserably. You’ve been taught from a young age that you need to have an answer for every question on the test, even if it’s because you’ll get 25% of them right on multiple choice or 10% partial credit on something else. Interviewers, like your teachers, know the answers they’re looking for before the question was asked. However, interviewers are different in that they give negative credit instead of partial credit.

So, what is this negative credit and why are they giving it to you? Lets look at it from the context of being on the job. I’m a Database Administrator, and I have developers ask me questions from time to time. Many times I know the answers and can help them out off the top of my head, we’re not going to worry about those times. The problems come from when I don’t know or I’m unsure of the answer. Do I have the integrity to tell that developer I don’t know? If I don’t, will they build the world upon inaccurate information and end up with a world that needs to be torn down and rebuilt? One of these answers cost a little bit of time to look it up. I’m not going to try to put an estimate on the cost of the other answer.

Interviewers don’t like it when you know all the answers because of this, and a good interviewer is going to have a question or two they can bring up that no one would know the answer to off the top of their head for this very reason. Yes, they do like it when you know a lot of answers, but more importantly they want to know what type of integrity and mindset you have when you don’t know. The right answer if you’re uncertain is to say you would need to verify it, but here’s my answer and here’s how I’d check it. The right answer if you have no clue is “I don’t know”, hopefully followed up by where you would start to look for answers.

In both cases, you write down the question, look it up after the interview, and be ready to answer it next time. If you want bonus points, and who can’t use bonus points in this situation, you’ll email your interviewer the next day and say that you looked into the question and here’s the right answer. Now you’re acting like the ideal employee. You maintained integrity by saying you either didn’t know or weren’t certain of the answer, which couldn’t have done a better job showing you had the integrity Warren Buffet was referring to. Then you followed up with the energy to gain the intelligence to answer it better.

On the answers you know, be it ones you knew all along or ones you crammed for, you fulfilled one of the lessor criteria that Warren Buffet would look for. On the answers you don’t know you instantly fulfilled the most important criteria if you admitted you didn’t know, then you had the opportunity to throw in the other two criteria on a follow up.

Here’s the problem. Every good employee is a teacher, and you’re better off without a teacher if their lessons are wrong. A good interviewer is looking for a good teacher more than a know-it-all.

Speaking of being a teacher, how do you answer the questions you do know very well? Chances are you know how to answer them in a way that someone else who knows the answer would understand. However, they want to know if you could teach someone else the answer, even if that person doesn’t have your specialty. Make sure you can teach them the right answer in an interview when you understand it well enough, especially if it’s a common question they’ll ask at every interview.

Back to my Database Administrator background, every interview I’m in someone will ask what the primary types of indexes are in SQL. The “right” answer is that a clustered index is the table itself in a sorted order, and a nonclustered index is a subset of that data which is also sorted. This is good, they understood my answer and know that indexes aren’t something new to me. However, if you’re talking to someone on the job who doesn’t specialize in SQL Server, how well are they going to understand and remember this answer?

The answer that will blow them away is that a telephone book is a clustered index which is sorted by the key fields “Last Name” then “First Name”, and has all the relevant information is clustered together in that single index. A nonclustered index is the index at the back of a book where it’s sorted by the key field “key word”, and it includes the page number, which is the clustered index key, so you can look up the rest of the information if the index didn’t tell you everything you needed to know. It’s not uncommon to get the response “Do you teach?” from your interviewer after an answer like that, to which you respond “every chance I get.”

There are other questions you can expect in interviews, too. “Tell me about yourself”, “Where do you expect to be in 5 years”, and so on. I’m not going to pretend to be a good list of all the common questions, but I can tell you how to prep for them. Again, think about it from the interviewer’s perspective.

“Tell me about yourself” is obviously a very vague question. I hate it this question, but it still gets asked. I view it from the standpoint that they want to see how you handle vague questions. Do you just ramble on about your kids and dog or do you clarify what they are asking? Do you have the ability to break this down into something specific and get to the right answer? On my job people will often come to me and say “the server is slow”, which I have to break down into pieces until I can start asking the right questions and answers. Now I’m taking a “why would you ask this” question and demonstrating what they want to see in an employee.

“Where do you expect to be in 5 years” is also every common, and they aren’t expecting you to be a fortune teller here. Also, I wouldn’t expect someone to try to commit to being with my company for that length of time before they’re even offered the job. What else could an interviewer be hoping for? They just want a direction. Are you excited about what you’re doing and have the drive to keep learning? Do you want to make the jump from technical employee to manager, which would start another conversation with the stated goal still being to look for the energy to improve yourself and be a continually better asset to the company. If you don’t know what career you want to have in 5 years then you can’t be expected to have the energy to improve your abilities at your current career.

Here’s some more you should know before an interview, but I’ll be brief since it goes beyond the scope of this post.  When people ask you questions in an interview, do you exude the following?

  • Humble – If someone asks you to rate yourself on anything from 1 to 10 then 10 is not an option.  Even 9 is arrogant unless you’re known around the world for your skill.  Too high and you’re too confident, won’t take criticism, and, my personal favorite, you’re done learning because you think you know it all.
  • Hunger or Drive – You want to keep bettering yourself, both in work and out.  You want something a little above your skill level because you’ll be there soon.
  • Energy – If the interviewer is good, they’ll ask you questions you don’t know no matter how good you were.  Did you come back with a solution after the interview?
  • Communication – How were you able to answer the questions you got right?  Were they just technically right, or did you put off the impression that you could help others get to your level?
  • Leadership – Chances are you aren’t interviewing for a management position, but management isn’t leadership.  Are you showing that you can take an idea and run with it, asking others for help as you go, and making sure it’s right without someone looking over your shoulder the whole time?  Managers want to do more than stand around holding a bullwhip, they want to watch people fly on their own with ground control giving a little direction.

These questions are being asked now in the context of an interview, but you should be asking yourself these things in your continual career planning phase as well. “Tell me about yourself”, can you break down large issues and find the root of the problem, and do you care enough about what you’re doing to get down into the details and root causes? “Where do you expect to be in 5 years”, do you care enough about this career path to make yourself a better you? If you don’t have good answers to these questions for yourself then where do you want to be? Figure that part out and you’ll have a better chance at finding the career you want to do. And don’t forget to consider the integrity of your answers when coworkers look towards you for guidance.  Do you give each answer your best shot off the top of your head, or do you tell them you would have to look it up and follow up with the right answer after you do?  Following this advice you’ll be a lot closer to the ideal employee the interviewers are looking for, then all you have to do is walk into the interview and be yourself.

TSQL2SDAYThis post was already in the works before I found out it was the topic for this month’s T-SQL Tuesday led up by Boris Hristov (b | t) on Interviews and Hiring. This is an amazing coincidence for both of us. For me, it gives me more visibility on this post, which I think a lot of people could benefit from. For you, it gives you a couple links in this paragraph to exercise your hunger to better yourself. Just remember that a lot of what you see is going to be how to get a job, but you can skew it to how to be better at your job and evaluate where you want to be.

Free SQL Server Training (not a sales pitch)

My company cut the training budget this year, and it’s not that uncommon for that to happen anymore. However, I’m still getting my week of training in, and more, without paying much.

Blogs of people who talk too much – FREE

There’s so much for free online, but you’re reading my blog so I don’t have to dive too deep here. Follow a couple blogs in an RSS reader, and make sure you sign up for freebies they may offer like Paul Randal giving away training!

Personally, I try to read a majority of everything written by:

You can include me on this list if you’re feeling generous. However, they’re all smarter than me, and I’ll understand if you don’t.

Local User Groups – FREE

I know most DBAs don’t go to the local user groups. Some never go, some go when the topic is right, but you can benefit from these every time.

Even the presentations that are outside of your normal job duties are valuable. If someone’s teaching SSIS and you don’t use it, knowing what you can learn from an hour-long presentation will put you in a situation to have more intelligent conversations when these topics come up. Read over job descriptions, they expect DBAs to know everything, and here’s your chance to know a little about it.

Also, you can learn from every presentation. I don’t care if you could give that presentation, people in a field known for introverts could use practice and examples of how to present ideas to others. You’ll see good, you’ll see bad, and you’ll be better because of it. Also, one or two small details you might pick up on a topic you know extremely well will make a big difference.

Speaking of our field being known as introverts, this is your opportunity to step out of your comfort zone and present. That’s what I did. Some present because they were born to do it, and I started presenting because I was born to blend into a cube farm! I’ll be presenting at my local user group in April, have already done a SQL Saturday this year, and would like to do at least two more SQL Saturdays, which drags me (kicking and screaming) into improving my soft skills where a typical DBA, like me, is behind the curve.

Our last presentation by Bill Wolf was an excellent presentation on avoiding common coding mistakes. It wouldn’t have been too much of a stretch for me to make a similar presentation, but I still learned a couple things and met some good people. The next meeting on March 25th will be about the interdependency between SQL Server and .NET, which would be amazing for any DBA or .NET developer to go to, even if Dell wasn’t giving away free laptops as door prizes!

Finally, the presentations aren’t the real reason you’re going there, are they? You’re getting to know the other DBAs in the area, and you’re sitting there talking person-to-person to the ones that want to share everything they know with you. You get to know them, they get to know you, and when one of you wants a new job or new coworker then you have a better chance at finding a good fit that you can be comfortable with from the start.

If you’re not sure where your local user group is or if you even have one, start by looking at this link.  Most groups are affiliated with PASS, and that means they’ll be listed there.

SQL Saturday – $10 Lunch

SQL Saturday is a cross between the local user group and the PASS Summit. In fact, the last one I was at has been referred to as a mini PASS Summit because of the number of high-quality speakers there, including multiple names from the list of bloggers I mentioned above.

The cities that host these events typically do so as an annual event, and there’s probably one not too far from you each year. I live in Pittsburgh, PA, which hosts one just a couple minutes from my house. I’m also trying to make it to ones reasonably close to me such as Cleveland, OH a couple weeks ago that’s about 2 hours away, Philadelphia, PA coming up in June that’s a 5 hour drive where I have family I can stay with. I think I’ll skip Columbus, OH because it’s the week after Philly and I have three small kids. Although I won’t make it this year, I may go to Richmond, VA, which is a 6 hour drive where I also have family to stay with.

It’s been a cold winter here in Pittsburgh, and now I’m starting to think about how much I miss my relatives in Texas, California, Florida and South Carolina. It knocks this idea out of the free or low cost training blog post, but that doesn’t mean it’s a bad idea.

While my company did cut the budget, they still paid mileage and gave me Friday off as a travel day. Even without a budget, still ask to see what you can get.

Virtual PASS Chapters – FREE

Pass also does virtual online events. I know I’m risking making this sound like a PASS commercial doing the local PASS chapter, the PASS SQL Saturdays, now the Virtual PASS chapters, but it’s hard to talk about free or low cost SQL Server training without talking about these people. So, why would I avoid this sounding like a commercial for them? They deserve to be known, and with all this free junk they can’t have much of an advertising budget!

Anyways, the virtual chapters each have their own focus, and I’m a member of the PowerShell, Performance, Database Administration, and I think a couple others. They do presentations about once a month, and I try to watch them when I have time. Worst case, they usually make the recordings available later.

There are also a couple “24 hours of PASS” events throughout the year where there are 24 back-to-back sessions. The next one on the schedule is the Russian version, which I’ll pass on. – FREE

In addition to their blogs, the Brent Ozar Unlimited team (Brent’s just a member, not their leader!!!!) does weekly webinars that are usually very useful to me. I’d say I watch 3 of them a month, missing one either due to my schedule or because it’s a rare one that I don’t feel bad about missing. They put up the recordings later, although I have to admit I usually don’t watch them if they aren’t live. I know I don’t make any sense, but you’ll get used to it.

Twitter – FREE

Seriously, if you can fit your question and #sqlhelp in 160 characters or less, you’ll have someone pointing you in the right direction within minutes. They’ll point you to the posts that you didn’t know what key words to Google to find, but keep in mind that they’re doing this for free. Always be grateful, and understand when they say that digging into the issue would mean a trip to the consultant.

The End – Sad, but still free

So, yeah, my company cut my training budget. That means that so far this year (March 3rd here) I had to stick to learning from Steve Jones, Grant Fritchey, Erin Stellato, Stacia Misner, Tom LaRock, and others in-person. Then I watched free videos by Jes Borland, Jeremiah Peschka, Brent Ozar, Kendra Little, and Doug Lane. I have a free month of the SQL Skills Pluralsight classes with a code that Paul Randal personally emailed me, with which I’m in the middle of a class by Jonathan Kehayias right now. I’m planning on taking full advantage of this trial with classes by Joe Sack, Erin Stellato, Paul Randal, Bob Beauchemin, and Kimberly Tripp, although I’ll end up running out of time and buying a subscription for $30 / month.

So, is a lack of a training budget stopping you? You can say it is, but it’s not.

I gave you a lot and I missed a lot. This is not all-inclusive, and a quick look around the internet will prove that point. There are still Free e-Books and many other resources out there. I have to start skipping stuff or this post will never make it out to you, and what good would that be?

Hey, look!!! There’s a section right below here marked “Comments” that’s perfect for your addition to the list. Maybe it can be all-inclusive!!!

The Approachable DBA

DBAs are the gatekeepers, but if we make it an unpleasant process then people will find a way around the gate. It’s common to think of DBAs and developers being polar opposites that don’t speak to each other. This is wrong! It’s not one of the database-themed answers of “it depends”, this is just flat wrong.

DBA with a bat

I may look calm, but in my head I’ve killed you 3 times.

The issue with this is that when you take a polarizing position you get a polarized result. Take Grant Fritchey’s use of his Hickory Stick of Education for example. He goes on a rant about databases needing to be backed up, which, as protector of the data, is the proper view to have. However, the harsh approach many DBAs take to this role, with this being an exaggerated extreme, alienate their coworkers.

His coworkers on the other end of this conversation, we’ll call them developers to go with the theme here, will take one of two initial views. “I better do what he says, or he’ll beat me”, or “I don’t need this, I don’t need him, I have my reasons, and I’ll do it my way without discussing the details with him.” Unfortunately, both of these answers are wrong, and the ensuing conversation with HR may also be unpleasant.

Although they’re opposite responses, they’re both wrong for the same reason. Neither developer is going to discuss the details. The first developer is going to start backing up the database at any cost, but is a full backup and restore the best recovery option? The second developer isn’t going to back anything up, but are pieces of this database unique and need to be recovered in a disaster? After all, you’re responsibility as a DBA isn’t making sure you’re backing everything up, it’s making sure you can put it back to where it was in accordance to the written agreement you have with the business owners. In this case it could be data that is pulled from 30 different locations and the best option for recovery is to pull that data again, or you could have 90% static and the best option is to have a static backup of one filegroup and nightly backups of another, or…talk to the developer.

The number of details they tell you is directly proportional to how approachable you are. If you use a polarizing approach that sets your approachability to 0 then you’ll get 0 details, even to the extreme that people will try to spin up production servers without your knowledge. I’ve seen it happen. I’ve also seen those same developers come back later and ask us to restore something that was never backed up because we never knew it was production. You can’t allow the recovery conversation to be the first time the developers talk to you because that will end as well as trying to do a restore without a backup. It’s your responsibility to make sure there is communication going on.

So, would I hire a DBA with a reputation for not working well with developers? Maybe, after a long conversation about how to work well with others, not as my only DBA, and it would take a lot more in other areas for to convince me I was making the right choice. I need to have a DBA that will talk to the developers, go out to lunch with them, and have the developers accept someone protecting our data as one of their own. When they get to that point the developers start asking questions that help them use the database as smoothly as possible and the DBAs start asking questions to find out why the bad practices are so tempting in the first place.

Stop and think about this. Would I hire you? I would hire Grant because I’ve met him outside of an interview and feel his is much more approachable than his nicknames and this blog post. Just look at his posts directly before this post where he’s acknowledges the issue saying “I’m pretty sure most people don’t talk to their Dev teams if they can help it, and it does seem like most Dev teams seem to be on less than a perfectly chatty basis with their DBAs”, and after this post where he mentions the struggle to resolve this issue stating “I spent years at my previous job trying to do a better job interacting with the development teams.” The thing is, Grant’s an odd one; he’s a SQL Server MVP which means I can find more than one piece of information about him. Does your future manager know other details about you or is it possible that one rant is the only piece of your reputation they’ve heard? Chances are, if I ever heard pieces of this about you then it’d be an uphill struggle to get to an interview, let alone a job offer. Even worse, IT is a small world, and if you live in the same city as me then I can find someone who has worked with you before that’s not on your list of references.

Now think about it from the side of your current job. Yeah, you just kinda won the fight with making sure this database is backed up, but the developers are starting another project as well. This time they’re making a new database from scratch, didn’t involve you, and it’s an ORM reading and writing directly to tables. Everything’s a clustered index scan or table scan, but it works great with 1,000 rows of sample data. There are no cases where they’re reading from a view, no writes are being done through a proc, and they’ve built a lot of stuff on this foundation before you knew any details of the project. You know if you were involved from the start that it would have been done better from the start, but, still licking their wounds from their last conversation with you, the developers avoided you as long as possible. There’s a big mess, it’s your own fault, and a lot of people are going to be paying for it for a long time.

I’m not asking for you to be a pushover. Instead the point is to discuss the situations, find all of the details, give them proof of your logic when you differ in opinion, educate them as you accept their invitations to educate you, and go out of your way to be viewed as approachable. The result you’re going for is the same, which is recoverability of data, and I’m not advocating letting off until you achieve that goal. However, that’s not the only interaction with the developers you’ll ever have, and this approach will help you “win” this interaction quicker at the expense of not being able to work together through every other interaction. The only thing I’m going for is that DBAs as a whole need to develop the proper soft-skills to work with developers. This is going to help a lot of people in the long run, including you, your family, the developers, and me.

You: Your job will be easier. Things that would be issues will be handled before they’re big issues, and a lot more things will be set up right from the first place. Your reputation will be where you want it, allowing you to enjoy the job you have and get the job you want.

Your Family: You’ll be coming home from work less stressed, actually enjoying your interactions with developers. Beyond that, you’ll be more and more desirable, getting better and better jobs, bringing home a better income to help provide for your family.

The Developers: Problems are avoided, they have a mentor on the database side, and going to work is more enjoyable.

Me: I get really tired of this whole “Us vs. Them” mentality, hearing DBAs talk and only half jokingly say “and maybe even, ‘gasp’, talk to your developers”, and developers, rightfully so, assuming DBAs their enemy from the start. If for no other reason, all of the DBAs in the world should be more approachable to make my life easier!!! The only thing you bullheaded DBAs do is give developers ways to slow down! Why do I always have be here to fix your problems? The whole profession is nothing more than a bunch of socially awkward nerds on a power trip going out and telling developers “NO” with a smile on their face every chance they get. Who needs you?!?!

You know what? The developers that don’t talk to DBAs are usually right in their logic. Why ask someone a question if you know the answer is no, you just don’t know how you’ll get belittled on your way to that answer? In many cases developers aren’t talking to their current DBAs because of experiences at past jobs, but it’s still your problem to fix.

I’ll end this post the way I started it. DBAs are the gatekeepers, but if we make it an unpleasant process then people will find a way around the gate.

Related articles

New Job Checklist

I started a new job a month ago, but didn’t want to just figure things out as they came to the surface. Instead, I walked in with a list of questions I wanted to know about their environment. For both my side and theirs, this went over very well.

For those used to my blog, you’d expect all the performance monitoring to be a part of it, but that’s not all there was to it. In fact, the most important questions had to do with backups, recoverability, and nonprod environments. Lets go through it all in the order that I feel it’s important. This is already a long enough list with just saying what to watch for without getting into the details, and I’ll do deep dives on each section either over time or upon request.

Staying Organized

Do we know what SQL Servers are out there and which ones are prod? Also, who do we contact if it either went down on its own or if we’re going to intentionally take it down for maintenance? For when you do take it down intentionally, what’s the typical maintenance window for each server? Also, what builds of SQL Server and Windows are you running, including SQL Server build number and architecture for both Window and SQL Server? There are a lot of things we should know about the servers we’re working on, right up to basic hardware stats with current and max RAM and the number of cores for licensing. Know what you’re working with and make sure you have all the access you need on all the servers you’re supporting.

Backups and Recoverability

When was the last time each Tier-1 system was recovered from offsite backups? I’m looking for two things here. First, that they are being backed up offsite. Second, that we know those backups are working by testing them regularly. I don’t care if you use tape or disk, and I don’t care if the “test” was an emergency restore to get data back that someone accidentally deleted a week ago. Do they exist and do they work? In my case, I found a bit of a surprise here and ended up getting in the middle of a business continuity plan as seen on last week’s post,Disaster Recovery and Business Continuity

Continuing on with Business Continuity, I wanted to get the typical misconceptions out of the way. So I continued with the questions of how long it would take to recovery from different types of disasters and what does the business think those expectations are. Also, how much potential data loss does the business think is possible compared to the current recoverability? I know from my last blog post that some people feel that I’m overstepping the boundaries of a DBA with some of this, and I’m ok with that. My immediate boss is the VP of Infrastructure, and I think that people would do much better trying to view things from their boss’s point of view instead of their current position’s point of view.


Now that I have basic recoverability covered, I moved on to security. First, most companies have too many people with access to prod along with sensitive data in all environments. This is easy to overlook. First thing’s first, protect prod at all costs. Who has what permissions, specifically, who has sysadmin? It used to be BUILTIN/Administrators, and your old boxes will still have that default, often unchanged. Don’t trust them to tell you who, query it yourself.

The next concern is the sensitive information that needs to be in prod and gets carried down to nonprod. Information such as SSNs don’t need to be accurate in nonprod, and there doesn’t need to be a way to recreate them from the nonprod data. However, you have to be careful when making this data useless since many apps join on this field, making you have a consistent algorithm to obfuscate the data. Hopefully you have another field such as EmployeeID or ClientID that you can substitute consistently and the developers would never know the difference.

Server Builds

Is there any consistency to the servers? Is the Min/Max server memory set up properly, are they using Instant File Initialization where possible, how big and how many files are there for TempDB, what is MaxDOP set as, what accounts does SQL Server run off of and is it a different account for each server? You need to have consistency if you’re going to consistently manage these servers. Yes, the values will vary, but it’s good to know that they’re set up right before you start getting into the issues. Jonathan Kehayias and Brent Ozar both have good server build docs out there, and I think most DBAs could benefit from reviewing what they have.

Depending on how they do it, the DBA will have some role in setting up failure notifications for SQL Agent as well. I’ve seen this as Database Mail where the DBA does almost everything and as an external program that reads the error logs where the DBA just makes sure the jobs are set to write to the app log on failure. Either way, you have to query the system tables on all the servers to make sure the setting are what you expect them to be.

You also can’t forget about the maintenance jobs. For which I believe everyone can benefit from Ola Hellengren’s Maintenance Solution. You may not use all of it, but the parts you do use you won’t write this thoroughly. Ola did something amazing here, put it out there for free, and you’d be crazy not to at least look at his generous offerings.

Staying Up-To-Date

It’s best to find out up-front what the plans are to move forward with getting everything to SQL 2012 or 2014. Even below that level, it’s good to know the process of getting all servers to the latest service pack and what the plan is with hotfixes and cumulative updates. The quicker you know this information the quicker you’ll know what’s on your plate for the next couple months.

How are we doing?

How do other departments view the DBA group? How do our customers (end users of any type) view our overall performance? How will you ever know if you don’t ask? The answers to questions like these are going to make a big impact on where your focus is. I would love to have questionnaires go out to Sr. Management, Mid-Level Management, and to End Users to find out their thoughts on a lot of different things, with a mix of open-ended questions such as their 3 favorite and 3 least favorite things on different areas and simple ratings questions with a rating from 1 to 4 (there is no average, there’s horrible, bad, good, and great). I want to know what they hope the app can do differently a year from now, how they view performance, and what their biggest headaches are.

In my case I made a mistake here when starting my new job because I came in knowing they had severe performance issues. When I looked at the different servers I saw a 2 TB OLTP with PLE dipping below 1,000 at times and a 100 GB OLTP with PLE staying above 10,000. I KNEW which one they were complaining about performance on and starting making some huge improvements. I was wrong, and that 100 GB database needed a lot more help then it was letting on. That’s not to say that the improvements I made weren’t needed or appreciated, it’s that I was fixing stuff that internal users were complaining about while waiting for another manager to ask me why I wasn’t focusing on making life better for the people who were paying our bills. It flew under my initial radar because it was smaller waits and coding issues while it was a constant struggle for the users that could pick one of our competitors over us.

I made a couple large changes that the SAN admin should notice on the 2 TB database and I got a couple “good job” comments out of it. Then I made a smaller change on the 100 GB database and got genuine gratitude and appreciation from multiple people. It wasn’t that I was working harder, it’s that my effort was focused in the right place after knowing where we stood and where our pain points actually were.

Finally, Monitoring

Once you know what you have, if it’s being backed up properly, it has security, is built consistently, there are plans to stay up-to-date, and you know where the databases stand from others’ point-of-view, you are finally ready to start looking into monitoring your servers. Look at my Monitoring and Baselines Presentation to give you an idea of what I’m looking to watch on each server. To be fair, we have Idera at my new job and I think that works out rather well, so there are things that I don’t have custom code watching. What I do is make sure that there is something accessible that watches everything I need and most of the things I want without duplicating too much work.

I do have complaints about Idera, and perhaps I’ll get into the pros and cons about the product in a later post. As far as this type of product, it is by far the one I have the most pros to talk about, so if any of the folks at Idera are reading this then you shouldn’t be sweating too much.

Wrap it up already!

This is a quick overview of the things that I’d prefer to talk about in my first two weeks on a job. Nothing was hit in-depth, and I could definitely write a whole post on each subject. However, don’t count on that happening. If you want a dive into a subject, ask.

I’d love to hear your comments on this, with the warning that I will stick to my guns completely if asked about wandering outside of the DBA role. I used to stick closer to my job description and requested duties, but didn’t feel like I fully bloomed until I tried to see everything from a higher view, most often from my boss’s view. Everyone organization can benefit from more leadership, and leaders have nothing to do with position in the company. I truly hope that this post encourages at least one or two people into taking the lead and getting the full picture under control, no matter whose responsibility it was.

Disaster Recovery and Business Continuity

Fires, SAN failures, tornados, and car accidents all came up today, yet it was a very good day at work.  I just started my job two weeks ago and made a list of questions to go through with them. After asking the one about when they last pulled an offsite backup and restored each Tier-1 database, I ended up in the middle of a project to restructure the disaster recovery and business continuity plan.

As a DBA I thought my job was to ensure that a database was recoverable from any disaster and that if you focused completely on the worst disasters possible then you would be safe from anything.  In the short-sighted version of things, perhaps I’m still right.  However, in the realistic version I had much to learn, and still do.

What is my job?

First, my job isn’t to make sure the databases are recoverable, it’s to make sure that database recoverability is part of a full business continuity plan.  That goes well beyond restoring a simple database to some server that I have no idea where it’s at or how it got there connecting to an unknown network without an app server.  There is so much more to a DBA’s responsibility in this aspect than I once believed.

If the app isn’t restored with the database then there’s no purpose for the database.  If there’s nowhere for the employees to sit and use the app then there’s no purpose for the app.  Finally, if the you restore too far from your current location then no one except the most senior and dedicated employees will travel to work.  All of this has to fit together for the mere responsibilities of a DBA to matter at all.

Second, the worst of disasters isn’t the most common, and the multiple day turnaround time is unreasonable for something as simple as a SAN failing or a building burning down.  Save the week long turnaround times for the disasters that remove your ability to work in the city you’re in.  Small disasters shouldn’t cost more than a couple days of data and productivity loss.

You need to look at disasters in different levels, and you’ll realize that as you go up the levels that they are increasingly less common.  Therefore you can’t have a simple business continuity plan and expect it to cover it all.  Also, if you don’t have anything then you can’t wait until you can figure it all out before you implement the first levels.

Missing Data

At the most common, you have a processing mistake.  The server and infrastructure are perfectly fine, someone just did something they shouldn’t have done.  Forget about the details, it doesn’t matter to you at this point if it was a developer mixing up < and > (haha, brings back memories of lost weekends), a user deleting the wrong records, or a DBA making a simple mistake.  No matter what, your challenge at this point is the same, you’re missing data and the business wants life back to normal as quick as possible.  For this the business needs to define what level of recoverability you need to have and help ensure you have the budget to do it.  If they never want to lose more than 2 minutes of data, you need log backups.  If an hour is ok then you can get by with Volume Shadow Snapshots (VSS) on VMs or possibly differentials on physical boxes.  If a whole day of data loss is acceptable then you’re home free with daily fulls or differentials.  No matter what you’re talking about here, this should be easily accessible by the DBA without outside help anytime day or night.

Small Hardware Failures

Now lets move on to local hardware failures.  A single drive shouldn’t be a concern because there’s no excuse not to have a minimum of RAID-5 on absolutely every file on a server.  Not saying you don’t have to double-check that the server “Prod-012” isn’t a laptop sitting under a stack of papers on an engineer’s desk, I’m just saying there’s no excuse.  Anyways, motherboards don’t last forever, RAM fails, and if your RAID-5 array is too large then it’s no safer than a stand-alone drive.  If you lose something, how long will it take to have something else take over?  If you lose a couple drives do you have enough spare space to get you by until you can replace them?  As for servers, Active/Passive clusters or VMs are great, one loss and you’re up and running in 5 minutes without too many complaints.  Active/Active clusters are also great if they’re set up right, as long as you’re not double-dipping on the RAM with two instances each set up to use 50 GB of RAM on a box with 64 GB of RAM where the OS will lose the fight quickly.  Standalones, however, are out of luck and need to have a plan in place.  How long does is the business able to deal without that server?  Did the business give you that answer in writing, and is the person in the business responsible for that server currently aware of that potential downtime as of your last annual review of disaster recovery and business continuity?  Wait, do you do an annual review with the business of your disaster recovery and business continuity???  Many companies don’t, and it’s your responsibility to start that conversation.

Shared Hardware Failures

Shared hardware and services also fail, no matter how fault-tolerant you think it may be. Power goes out, do you have a generator?  Networks go down, do you have two connections that can handle the traffic at an acceptable speed coming in through separate hardware?  SANs spontaneously combust, were your data files and backup files on that same rack?  Walk through your server room one day with a system admin and point to each device, discuss what happens when it stops working.  Telephone polls and transformers take time to replace, were your fault-tolerant connections coming in on the same pole?  Now we’re at the point that I’m not expecting you to have a spare on hand, but you still need a plan.  SANs are expensive, so you at least need to know where the money is going to come from if you need a new one overnight.  Again, know your risks, know your recovery time, and make sure the business has agreed to that in writing.

Building Loss

Now for what people tend to have in mind, fires, tornados, floods, blah, blah, blah, they all happen.  I’m in Pittsburgh, so hurricanes are covered in floods and there is no specific earthquake plan.  Is your server room in an area that would get flooded more than once a century?  If so, can it be moved?  If the building your server room in is no longer there, what do you have to recover with?  Offsite backups of databases aren’t enough.  You aren’t restoring databases at this point.  If that’s what you were thinking, you’re in a heap of trouble here.

First, you’re panicked; everyone’s panicked. Where is your plan, it wasn’t in that building, was it?  Who knows where to find the plan since you’re not in a disaster where you can count on everyone being available anymore.  Can everything be restored to a functioning state with acceptable data loss from data stored offsite, and was it tested to prove that it could be?  Remember, if you’ve only restored the database then you’ve restored a lot of valuable, yet useless, information.  Are the app servers in that same plan and up-to-date so the users can use the data?  Where are those users going to sit?  I know, this is on a DBA’s blog, so mostly DBA’s are reading this, we don’t tend to think about a user’s cube, or if their temporary cube is further than an hourly employee would even consider traveling to work.  However, right now our job is business continuity, not strictly databases.

So, say you do have an offsite, easily accessible plan that is written in a way that panicked and stressed employees mixed in with temps to make up for the employees that had to tend to their families instead of showing up for work could understand and implement flawlessly, but what does the business expect?  I keep coming back to this because the answer is usually that they think we have a million-dollar plan while giving us a small budget.  Realistically it may take us a week to have it all together, and we’d never know the full picture without testing it.  However, the business may be there saying that if it’s down for more than four days then we’re out of business, which would really suck after putting all that work in to this awesome, yet lower budget plan.  Make sure everyone knows the expectation and have that offsite plan not only signed off by the business, but the proof that they signed off on it stored offsite as well.  The IT department isn’t the only department stressed out when the data center burns to the ground, and stressed out people like to blame others for everything no matter who is at fault.

Oh no, I never thought that would happen

Finally, the largest of disasters.  What if the entire city where your data center in is inaccessible.  I know, how often does that really happen?  Hurricanes took out New Orleans for months and parts of New York City for weeks, earthquakes threaten to do the same to parts of the country, terrorist attacks are possible, plagues can happen (although I don’t foresee the zombie apocalypse), and I now know where the nearest nuclear power plant is.  Do you have recent backups stored outside of the region that can recover from these types of disasters, and has the business signed off on this?  For some companies it is acceptable for the answer to be that they don’t exist and it’s acceptable to go out of business for these disasters, and that’s ok.  Again, get it in writing, and store that piece of information in your personal offsite storage (yay, Google drive).  If this is the case, you’re protecting your rehirability when a company goes out of business saying their IT department failed them in a disaster.

I provide advice, not business continuity plans

Now it’s time for my disclaimer.  This is by no means all inclusive, and nothing you get from ANYONE’s blog should be taken as such.  Some companies have someone who has the job title VP of Business Continuity, and some don’t.  No matter what the case, it’s your responsibility as a DBA to make sure the DBA team has a part in this process and that the process is implementable for all levels of disaster with restorability that the business is aware of.  Unless you’re the only person in the IT department you shouldn’t have to do this alone, but it is your responsibility to start it if no one else has taken that responsibility.

Monitoring and Baselines Presentation

Here’s my full presentation for SQL Saturday #250 in Pittsburgh this past Saturday along with some notes on what I got out of it.

My Lessons

I learned a lot doing this, and I hope people learned a lot from it. For the people on the other side of the podium from me, the main lessons were in what I put in the abstract; they learned mostly about SQL Server. On my side of the podium though, the lessons were far from the same. Public speaking, creating presentation, prepping for unknown audiences, and seeing the gratitude of the SQL Server community in person. Anything that was on the abstract I learned slightly better, but that didn’t compare to what wasn’t on it.

Public speaking was always a fear of mine, and I tend to be someone who avoids my fears. However, throwing myself out there at the public as a whole was never my thing either, and I’ve been doing half decent, at least in my own mind. That being said, I decided to go all out and push myself a little further. After all, what are the chances of them picking me to present with all those professional teachers, consultants, and MVPs out there throwing up their abstracts. Best case I could say I tried, worst case I was going to throw myself out of my comfort zone and hope for the best.

They picked me, which I didn’t let myself expect. Everyone that knew I put in my abstract was also told by me that I only had a 50/50 chance at best, which was more optimistic than I really was, but I don’t like people knowing when I’m being dismissive of myself. It turns I was wrong a lot, and in every case I was wrong I was glad I was wrong.

Being Forced to Get Better

I’m not one to learn things for the sake of learning them, which is why I sucked in school. To really learn things I need a real-life use case, somewhere I’m going to apply it. I didn’t practice speaking ever before because I didn’t speak in front of large groups. I didn’t know how to put together a presentation properly because that’s not my thing…well, wasn’t is probably a better word now. Just like a couple months ago I wasn’t quite sure how to blog, but I’ve made it past that point.

Like everything in my life, blogging went from unknown to addiction quick. I’m not sure that I can say the same thing about speaking, but I can see the possibility. SQL Saturday only comes to Pittsburgh once a year and the local user group, which I’ll be joining soon, only meets once a month and has a single speaker. However, I can’t say that I didn’t look up when the SQL Saturdays in Cleveland and Washington DC were. Also, I took my own thoughts on my presentation and the feedback I received in and immediately starting thinking about how I could make that presentation better.

What’s below is the original, not touched up at all in the last 4 days. In part to show where I went wrong and how I’m going to fix it, and in part because Monday was my first day at a new job. By the way, starting a new job when between jobs is the best and worst thing you could ever do to yourself. You don’t have time to be too nervous about anything because you’re too overworked, but you’re also too overworked. Eh, you can’t win them all.

Well, on to the presentation. What you have here is my script that I talked through and taught myself before the presentation. However, if you were there, you’ll notice this isn’t exactly what I said. I didn’t read it, I presented it. There were no demos (the biggest complaint from the crowd) to avoid me from stumping myself, and the PowerPoint was just a whimsical picture for the start of each paragraph to keep me on track because I knew I’d be nervous and lose my way without a paragraph-by-paragraph guide helping me along.

I must have done at least descent because my reviews came back with two 3’s and the rest 4’s and 5’s out of 5. You’re just asking me to get off topic and start talking about why I hate ratings that are odd numbers, but no, I’m not getting off topic, I’m getting on to my presentation!!!

The Presentation

Download slide deck here.

I’ve been working on databases for over a decade, and most of that without having good standards or monitoring in place. My job was rough and I was much less effective. Now I know what I’m doing and I want to share that knowledge. In this presentation I am going to tell you what I watch and why I watch it. A presentation is a poor format to go through how to do this; having it in writing is much more beneficial to you. That is why I’m pretty much skipping that portion here and diverting you to my blog at Not so much to promote a blog that I lose money on, but instead to give you the resources in the best format.

As a DBA you will hit problems with the performance of your databases. It doesn’t matter if it’s one process or across the board, or if it’s just today or it has always been that way. No matter what it is, the more you know about your servers the easier it’s going to be to fix them.

It’s not easy, there’s no single spot to watch, no single solution to all of your problems, and there’s no “normal” values for these counters you can apply across every server you manage (Note: Thank you, Mike John, you stressed this point a lot). I watch several things on all the important servers, and most of these on every server. The important part is that you watch them continuously, even when you aren’t expecting to use the data. Some parts are cumulative and you can’t tell what was during an incident or what was from overnight maintenance. Other parts are snapshots and there’s no looking back.

(Note: Now I’m on to the stuff Brian Castle taught me. He’s the best you could hope for in a manager, and last I checked he was still hiring at EDMC in Robinson Township near Pittsburgh, PA)

To me, monitoring and baselining is the same thing. I know others will do a specific baselining process on a specific day and keep that. I feel you lose too much doing that and I watch enough to say that I have a continuous baseline going back for 13 months in most instances. I’m not all that worked up about lucky numbers or anything, it’s just nice to be able to say “that annual process we ran last week ran like this last year”. If you’re superstitious or have OCD, 400 days makes me just as happy.

Traces – Snapshot
Wait Stats – Cumulative – Resets on restart
Blocking – Snapshot
Query Stats – Cumulative – Resets on recompile
Index Stats – Cumulative – Resets on restart
OS Perf Counters – Varies
Database Sizes – Snapshot
Table Sizes – Snapshot

Traces – What long running processes have completed on the server?

You have to be careful, this one can kill your server. I have, on more than one occasion (sorry, Brian), caused a large server to reboot in the middle of the day because I filled the drives writing 40 GB of traces in 5 minutes. That being said, there is a safer way to trace, just don’t do an unfiltered trace catching everything.

I personally feel that absolutely every server should have a trace capturing RPC:Completed and SQL Batch:Completed that ran over X seconds duration. What is X? Well, that depends on the server. I’ve seen the best number for X be as low as 100 ms and as high as 10 seconds. Start high, and work your way down. You can add or do a separate trace for the Statement Completed for each of these if you want more detail.

You can get all of this except for SQL Batch:Completed with the text data from Extended Events, and that is a very good alternative. I haven’t made that jump for several reasons, although I would recommend it. First, this level of tracing hasn’t hurt me. Second, I still support several important servers that are still on SQL 2005 and I want to be as consistent as possible to provide as consistent as possible support.

What you’re trying to solve here is answering what ran long, have some hints as to why it ran long, and be able to say how often it has been running that way in recent history. A long duration doing very little work was waiting on something else, and other monitoring will help solve that problem. A lot of CPU, reads, or writes shows that you may need to look into tuning or statistics. Just keep in mind that reads can be reads of work tables, and writes can be writes to tempdb.

If you do it right you should have 4 or more days worth of history, and by doing it right I mean having 5 to 10 rollover files sized a reasonable size you can send off if needed (100 MB at most, they zip well) and capturing over the best duration threshold for your environment.

Wait Stats – What’s slowing you down?

The traces tell you what work was done, and the waist statistics tell you what went on when the query couldn’t actively do its work. This is cumulative over the server, so you can’t get details down to an individual process, however, you can see how much time is wasted and where it’s wasted at.

There are almost 500 distinct wait types in 2008 R2, but you don’t need to worry about that. The important thing is to know what are your biggest waits are, and you can look them up if you don’t recognize them. In the last slide I have links to a free eBook written by Jonathan Kehayias and Tom Kruger for the Accidental DBA that does an amazing job documenting what you can ignore and the meaning of the big ones you’re most likely to see.

If you’re looking for overall server health then you would want to look at waits over a long period of time, specifically the times of day you’d like to see better performance. For incidents, you want to look at what your wait stats are now and compare them to your running baseline. For me, the typical baseline I use is the same timeframe yesterday and 7 days ago. These are actual relevant days that have all but the changes you put in over the last couple of days, and it’s a true baseline for this specific server. If this server never waits on PageIOLatch, but it’s in your top 3 today then you instantly have a direction your heading in troubleshooting.

Blocking – Who’s in your way?

Locking occurs when one query is using data, and blocking occurs when another query needs to do something that is incompatible with that lock. If you don’t keep an eye on it then you’re looking back at a trace and seeing that a query took 1 second of CPU, did 1,000 reads, and no writes, yet it figured out a way to take 5 minutes in duration. The users complain the app is slow or unusable, and you’re giving them the answer that you’ll try to figure it out next time it happens and THEN try to find out the root cause.

That’s a bad idea. There are three types of users – those who don’t know you because things are running smooth, those that like you because you can say “I see exactly what happened and I can start working on avoiding reoccurrences”, and those users that you keep telling that you’re not sure what happened and you’ll try to get a better idea the next time it happens. I wish I could tell you that I could have more users not know your name, but watching blocking is a very easy way to get the users that like you to outnumber the users that hate you.

So, what can you do to tell them you know what just happened? My answer is to capture everything that’s blocking on the server every minute. Sure, there’s going to be a lot you capture that is actually only blocking for 10ms, and there are going to be things that were blocking for 59 seconds that were timed perfectly so you never saw it happen. The trick is to know that there is no perfect solution, and capture what you can. The DMVs are there to provide all the information, and being able to capture that on the fly along with in a proactive monitoring setup will make you look amazing to the users. If something blocked for 5 minutes, you have no excuse not to say “This query blocked this query, and this was the head blocker”. Sure you may have an excuse to say “It’s a vendor database and I passed it on to their support”, but at least you can tell the users something, which is always better than nothing.

Query Stats

The DMV dm_exec_query_stats is used by many DBAs, even if all we realized was that we were running one of the “Top 10 Most Expensive Query” scripts off the internet. The problem is that this is using it wrong. This DMV holds everything from when the query first went into cache and loses that information when the query goes out of the cache, even just for being recompiled. So the big process you ran overnight could still be in cache, leading you down a path that’s actually low priority for you. However, that relatively large process that runs every 5 minutes that just recompiled a couple minutes ago due to stats auto updating, it’s not even in there. This is too volatile to say that you’re getting good numbers out of it.

The answer isn’t to find somewhere else to get this information, it’s to capture this information more often and more intelligently. Capture it once and you have a snapshot of what it looked like, capture it again an hour later and you know what work has been done in that hour. Repeat the process over and over again, constantly adding another hour’s worth of data and saving off the latest snapshot in the process and you have some real information to go off of. This isn’t so much of what happened to be in cache at the time, it’s a pretty good summary of what ran and when it ran. Now you can query in detail, specifically saying “I want to know what queries are doing the most physical reads between 9:00 AM and 5:00 PM on weekdays so I can have the biggest impact when I tune this database.”

Index Stats

The DMV dm_exec_index_usage_stats isn’t too different from dm_exec_query_stats except that it’s cumulative since the time SQL services were started. Looking at a snapshot still leaves you half blind, unable to see what happened before the last time you rebooted the server and unable to tell when that index was used. Personally, seeing when it was used is less important to me than how much it was used long-term. Tracking this can do amazing things for you if you’re really fine-tuning a database.

This DMV is really under-used in my experience. As time goes on with a database the data changes and you add more indexes to make it run faster, but you don’t know if or when the index it used to use was being used by anything else so it stays there. Over time that means you have more and more unused indexes, and there’s no solid proof that they aren’t being used without you doing the work to collect that proof. This, in addition to duplicated indexes, adds to the workload for inserts and updates, adds to the workload of index maintenance, adds to the database size, and put additional strain on your cache and thus the PLE.

OS Performance Counters

This is one of the most misunderstood DMVs while also being one of the most useful. It’s giving you critical counters that give you an idea of how SQL Server is interacting with the hardware, but it’s doing it with several different types of counters that have to be measured differently. There are a couple great posts out on the internet to understanding how each one needs to be measured. As for right now, we’re focusing on why you want to watch it and what it will do for you. And wouldn’t you know that I saved the hardest one to describe for last, because each value is different and there will be controversy on which ones you should be watching and which ones are just a waste of your time monitoring.

PLE is one that this is no controversy about if you should watch it. This is the average age of the pages in cache, and a good measurement of when you’re doing too many physical reads. Peeking in on this from time to time is good, you can make sure it’s high enough. However, watching this will let you know when it’s dropping and help you dive into why it’s dropping. If you’re also running a trace you can see what did a lot of reads in that timeframe, and if you’re capturing Query Stats then you can find out which one of your large read queries is doing all the physical reads that destroy your PLE.

Server Target and Total memory are also great to watch even though they rarely change after the server is back up to speed following a restart of SQL services. The target memory is how much SQL Server would like to have, and Total memory is how much it actually does have. If target memory drops then there’s OS pressure you need to worry about. Also, as Total memory is increasing then it means the server is still filling the cache and PLE can pretty much be ignored. Of course it’s low, everything in there is new.

Page Lookups, Page Reads, Page Writes and Lazy Writes give you a better idea of how SQL Server is interacting with your cache and disks. If you want a real picture of what SQL Server is doing behind the scenes, this is much more useful than glancing at cache hit ratio.

Deadlocks and Memory Grants Pending are two things you’d like to always see at zero. That may be out of the question with deadlocks, but if you’re getting above zero on memory grants than you need to find out when that happened and everything that was running at that time.

SQL Compilations and Recompilations are hidden CPU hogs. They’re never going to be at zero because very few servers have stuff stay in cache forever, ad-hoc code runs, and several other reasons. However, when one of these counters jump up, you may have a hidden cause. For instance, if a piece of code that runs every minute or more was written in a way that it can’t be stored in cache, these numbers will be noticeably higher. When this happens, your CPU will be noticeably more stressed with no other indicators as to why, and this query won’t even show up in your Query Stats as it relies on showing the stats of everything that’s currently in cache.

Database Sizes

This seems simple, and it is. Most people know how to find the sizes of their databases, but do you track it? If not, you should. Some questions you should be able to answer readily are how fast your databases are growing, when will you run out of space, and is the steady decline in PLE justified by the growth of the database. Getting into more details, you can touch on when does the database grow such as an accounting app jumping in size every April, or answering if the data is growing steadily or exponentially. These are important things to know when it comes time to budget for new servers and disk space.

On the other side of things, you also have information to push back on application teams on how much space they’re using and if that’s really necessary. A lot of times you’ll find that they’re surprised by their growth to the point that you have to show them the numbers for them to believe it. It’s not uncommon for the response from this to be a cleanup project that helps keep the databases smaller and running faster, which is a big goal being accomplished.

Table Sizes

This is just building off of watching your database sizes in more detail. When a database starts filling up, and it’s filling up quick, it’s good to tell an application team what table is doing it. In my current environment it makes sense to watch every table that is over .5% of the database size AND over 100 MB. This means that some databases we don’t watch anything and other databases we watch about the 20 biggest tables. The biggest point is that we’re not trying to watch everything while watching anything big enough to make a difference to us.

If a database is filling a lot faster than normal then a lot of the time there’s a process that isn’t running like it should. The biggest table in the database may be a rather static value, but the third biggest table in the database wasn’t even on your radar two weeks ago. The app teams love it when you can tell them that not only is the database growing out of control, but we also see the growth in table X which has been growing at 200 MB per day starting on the first Saturday of last month. By the way, wasn’t last Saturday the day you changed a couple procs around or implemented an upgrade?

Now you just graduated from “Hey, there’s a problem” to also include “and here’s a huge lead to finding the root cause.” It’s rare to use this information, but it’s lightweight to capture for something that gives you a heavyweight appearance when you reference this knowledge to other teams.

My Critiques

I’d love to hear what you think about this presentation. Before we get to that, here are the complaints from the most harsh person in the room when I was presenting…

I didn’t do enough to say what each thing was. Although I marked the presentation as intermediate, you don’t know who’s going to be there. In this case, I knew a couple people in the crowd, and they ranged from data analyst to database manager. It should have been presented in more of “If you’re not ready for intermediate then you have everything you need, but it will take some effort to keep up”.

My slides were lacking. I stick with the idea that there should be few words on the screen, but I took it too far. I admitted above that the slides were there more to keep me on track then it was to help the audience, which I probably needed for my first public speaking venue. However, next time I’ll be less nervous and more prepared to make it geared for the audience better. Instead of random pictures, I should have more of graphs and data that I can talk through to give the audience visuals.

Nothing was given to the audience, and there should have been something. To be fair, none of the presentations that I know of handed anything to the audience. However, I set my own bar, and I feel that people would like a sheet of paper with the outline on it with links to online sources. My blog, of course, because it shows how to grab everything. Also, an outside link for each item. This gives a physical reminder to turn this knowledge into action.

There was no demo, and that was the audience’s biggest complaint. While I’m still not sure that I would do a live demo on my second go around, I’m going to hold myself on the remark above about my slides having graphs and data. That would also drag me away from the podium to talk through the slides, making it a more dynamic and interesting presentation; that would really help the audience take things in.

Your Turn

Now I need you to do two things. Put this information to work in your environment is what will help you the most. What will help me the most is you commenting on this post both before and after you implement any of this so you can help me become a better presenter.

Thank you for taking the time to read this!