Anyway, I was working my way through the Self-Paced Training Kit, and stumbled across one thing that I hadn’t known before and am so excited about that I want to share it with all 12 of our readers.  Here we go…

First, because good bloggers give credit where credit is due, major props to the team that wrote the training kit for exam 70-433.  They include:  Tobias Thernstrom, Ann Weber, Mike Hotek, and GrandMasters.  It’s a well-written book, and they obviously snuck some things in there that were more about good design and development and less about answering test questions.  Kudos, gentlemen and lady!  I’m basically re-writing something that you’ve already covered in your book, but I think it’s really, really cool and want everyone to see it regardless of whether they’ve bought the book or not.  Given that, buy the book, dear readers.  Even if you’re not studying for the exam.  I promise you’ll learn something new.

Okay, to the point… Do me a favor, get on your local database and run this query.  Go ahead, I’ll wait for you. 

SELECT name, is_not_trusted
FROM sys.foreign_keys; 

Good to have you back.  I missed you.  I stared pensively into the horizon while I awaited your return.  I even wrote a poem and a folk song.  Sorry, I digress… one too many rom-coms lately.  Did you see any “1” values in the is_not_trusted column?  Did you even know that foreign keys could be trustworthy?  Nope, neither did I.  What does it mean?  It means that your foreign key hasn’t been verified by the system.  How does this happen?  Well, remember that optional clause called WITH CHECK | NOCHECK when you create a foreign key constraint?  Yup.  That did it. 

So why does this matter? Well, it actually has an effect on your query execution plan in some cases.  You know, that Query Optimizer is pretty darn smart.  Let’s look at an example from the trusty old AdventureWorks database.  I’m using AdventureWorks2008R2, but it should work with the older AdventureWorks databases.  The scenario is this:  I want to know if I have any sales orders that have invalid customers.  CustomerID is a NOT NULL column in Sales.SalesOrderHeader.  I know that if I count all the rows in Sales.SalesOrderHeader and the number of rows returned in the query we’re about to run, I should get the same number of rows back each time.  But, sometimes rogue values slip in, especially when the database design has been refined over time.  This could happen for any number of reasons:  constraints that were added after the fact, legacy data, disabled constraints, etc. 

Run this query, but turn on Include Actual Execution Plan (Ctrl+M) before you do. 

SELECT soh.*
FROM Sales.SalesOrderHeader soh
WHERE EXISTS (SELECT * FROM Sales.Customer c WHERE soh.CustomerID = c.CustomerID);

Note that I’m using WHERE EXISTS rather than an IN clause with a subquery.  This is because in this business scenario, I just want a boolean result (true or false), and I want it to run FAST. 

Check out the execution plan.  Note that the Customer table was never accessed.  Why?  Because that foreign key is trusted!  Since it was verified on creation, we know that no rogue CustomerID’s snuck into the Sales.SalesOrderHeader table.  It doesn’t even need to look at it.  
 

Now, let’s muck with the foreign key and make it un-trusted.  We’re disabling the foreign key with this statement.  Books Online has a good article about what this means. 

ALTER TABLE Sales.SalesOrderHeader
 NOCHECK CONSTRAINT FK_SalesOrderHeader_Customer_CustomerID;

Verify that the foreign key is disabled by checking sys.foreign_keys again: 

SELECT name, is_disabled, is_not_trusted
FROM sys.foreign_keys
WHERE name = ‘FK_SalesOrderHeader_Customer_CustomerID’;

If we run our query again, we see a completely different execution plan.  Now, the query optimizer has to go look at the Sales.Customer table to get us an answer: 

SELECT soh.*
FROM Sales.SalesOrderHeader soh
WHERE EXISTS (SELECT * FROM Sales.Customer c WHERE soh.CustomerID = c.CustomerID);

 
The execution plan had to change because SQL Server cannot guarantee that a CustomerID wasn’t entered while the foreign key constraint was disabled. 

Here’s where it gets interesting.  Enable the foreign key by executing the following: 

ALTER TABLE Sales.SalesOrderHeader
 CHECK CONSTRAINT FK_SalesOrderHeader_Customer_CustomerID;

Check out your sys.foreign_keys table again. 

SELECT name, is_disabled, is_not_trusted
FROM sys.foreign_keys
WHERE name = ‘FK_SalesOrderHeader_Customer_CustomerID’;

What?  It’s enabled, but it’s still not trusted!  If we execute our query, we’re still going to get the execution plan that looks at Sales.Customer.  Why?  Well, that CHECK keyword up there just said to enable the foreign key, it didn’t say verify it.  We have to issue this statement (Of course, if an invalid CustomerID snuck in while your FK was disabled, this ALTER is going to fail): 

ALTER TABLE Sales.SalesOrderHeader
 WITH CHECK —> this clause will make your FK trustworthy again
 CHECK CONSTRAINT FK_SalesOrderHeader_Customer_CustomerID;  

If we run our query now, we’ll get the sleeker, more efficient plan, because now our foreign key is enabled and trusted. 

SELECT soh.*
FROM Sales.SalesOrderHeader soh
WHERE EXISTS (SELECT * FROM Sales.Customer c WHERE soh.CustomerID = c.CustomerID);

 
Cool, huh?  The point is, this one teensy-tiny flag in the foreign key metadata makes a huge difference in how the query optimizer handles your query.  It might not be much, but why not make sure that you can get as many trusted foreign keys as possible?  You might just end up looking like a rock star for improving performance without having to modify any actual queries. 

Now if I could just figure out put to get a is_not_trusted flag on people…

About 4 months back, I remember facing the same issue when we had installed CU1 on a SQL Server 2005 (SP3) Named Instance running in Clustered environment. After installing the CU, the nodes were rebooted. When the nodes came back online, all the SQL Server Logins started failing to authenticate. On checking the settings, the authentication mode had changed to Windows Only from the earlier setting of Mixed Authentication. Then the authentication mode was changed back to Mixed Authentication manually.

This is a bug in SQL Server 2005 which is closed as “Won’t fix”. After installing a hotfix on SQL Server 2005 Named Instance on Cluster, ensure that the Authentication Mode has not changed.

Related posts:

1. Change authentication mode from Windows Registry
2. How to Resolve missing MSI or MSP packages during SQL Server Service Packs, Hotfix or Cumulative Updates
3. Start SQL Server in Single User Mode and allow only a particular Application to Connect

What Causes Overlapping Statistics
Boiling this down to basics, here's how it happens: AUTO_CREATE_STATISTICS is set to ON, SQL Server creates some column statistics based on queries that get executed, and then an index is created which creates an overlapping set of statistics on the same column. One real world example of how this can happen is when applying a missing index suggested by SQL Server to help improve performance. Looking at missing indexes is a very common performance tuning practice and I suspect that many people have unknowingly created overlapping statistics but applying missing index suggestions.

Why Overlapping Statistics Can Cause Problems
Paul White (Blog | Twitter) pointed me at Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 and Plan Caching in SQL Server 2008, two Microsoft whitepapers which contain an (easily overlooked?) snippet about how the query optimizer decides which set of statistics to use when there are overlapping statistics on a column. The answer, in a nutshell, is "it's complicated". There are guidelines, e.g. preference is given to statistics computed with FULLSCAN, but neither resource goes into any level of depth about how it really works. I'm not a Microsoft insider and I don't have access to the specific rules so my way of thinking is why leave it to chance? If I know I've got only one set of statistics on a column and the query plan goes wonky because of something statistics related I have more control over dealing with it. Otherwise leaving overlapping statistics in place can lead to the odd behavior I've previously blogged about.

I hope the additional information helps.

So, consider this to be my personal invitation to you to follow my lead and stop procrastinating!!  Go now to the PASS elections site, read about the candidates.  If you want to know something about us, use the forums, and ask.  I know at least 1 candidate that will answer your question.  Once you've done all that come back here, Ill still be waiting.  Now, do what I'm going to do:  I'll be finding the PASS BOD email ballot, then Ill be selecting the link and clicking on «Allen Kinsel» & 2 other candidates then hitting submit!

I've never been known to be the subtle type, so I figure no sense in starting now.  If you need a little more subtlety about why I think you should vote for me, look here.  The #1 thing though, is you, PASS Member, you need to vote.  It matters a great deal to our SQL Server community, even if you must vote for someone other than me [I disagree but, understand]..

Related posts:
Its PASS V Day… you know what that means!
5 things I want to change while on the PASS Board
What I'm doing to evolve the Program Committee next
The long evolution of the PASS Program Committee
More on PASS Summit Community Choice sessions & a general PASS update

Video chat with Brent Ozar and SQL Server MVPs Kevin Kline, Hilary Cotter and Geoff Hiten to talk about how SQL Server 2008 addresses DBA challenges and pain points. In this webcast, we cover features like Transparent Data Encryption, spatial data, and other new things that really differentiate SQL Server 2008 from 2005.

Posted December 24, 2008.

Position Summary:

This is a MS SQL Server DBA role largely responsible for providing operational database services to the organization. Some of the primary responsibilities of this role would include owning, tracking and resolving database related incidents and requests, fulfilling requests and resolving incidents within SLAs, reviewing service related reports (e.g: database backups, maintenance, monitoring) on a daily basis to ensure service related issues are identified and resolved within established SLAs, responding to database related alerts and escalations and working with database engineering to come up with strategic solutions to recurring problems.

This MS SQL Server DBA role requires a service oriented mentality, high sense of ownership of the problems and requests assigned, focus on managing and resolving issues in alignment with the SLAs, establishing and maintaining communication with technology customers to keep them updated with status of their requests, initiating and performing changes on production systems and proactively escalating any issues that cannot be resolved within the established timeframes.

Position Requirements:

We are looking for a person who:

• Has 8+ years of experience in database development and support in MS SQL Server environments.
• Strong experience in Database Administration in SQL Server ( 2005 and 2008 )
• Experience in troubleshooting and resolving database integrity issues, performance issues, blocking and deadlocking issues, replication issues, log shipping issues, connectivity issues, security issues etc.
• Experience in Performance Tuning, Query Optimization, using Performance Monitor, SQL Profiler and other related monitoring and troubleshooting tools.
• Ability to detect and troubleshoot SQL Server related CPU,memory,I/O, disk space and other resource contention.
• Strong knowledge of backups, restores, recovery models, database shrink operations, DBCC commands, Clustering, Database mirroring, Replication.
• Expert experience in implementing operational automation.
• Strong knowledge of how indexes, index management, integrity checks, configuration, patching. How statistics work, how indexes are stored, how they can be created and managed effectively.
• Knowledge of SQL Server tools ( Profiler, DTA, SSMS, SAC, SSCM, PerfMon, DMVs, system sprocs)
• SQL Development – ability to write and troubleshoot SQL Code and design ( stored procs, functions, tables, views, triggers, indexes, constraints )
• Solid acquaintance with windows server, security delegation, SPNs, storage components.
• Documentation skills for processes and procedures ( creating KBs, runbooks, topology etc )
• SQL Database Operational support to tech users

Preferred candidates would also meet the following criteria:

• Solid acquaintance with windows server, security delegation, SPNs, storage components.
• Documentation skills for processes and procedures ( creating KBs, runbooks, topology etc )
• Knowledge of 3rd party DBA tools and applications ( e.g redgate, idera, SCOM, Erwin )
• MCDBA / MCT certification
• Knowledge in a scripting language like Powershell, VBScript, WSH

OK, now let’s break this thing down. My comments are inline to the original:

Position Summary:

This is a MS SQL Server DBA role largely responsible for providing operational database services to the organization. Some of the primary responsibilities of this role would include owning, tracking and resolving database related incidents and requests, fulfilling requests and resolving incidents within SLAs, reviewing service related reports (e.g: database backups, maintenance, monitoring) on a daily basis to ensure service related issues are identified and resolved within established SLAs, responding to database related alerts and escalations and working with database engineering to come up with strategic solutions to recurring problems. Nothing wrong with this paragraph, sounds good so far.

This MS SQL Server DBA role requires a service oriented mentality, high sense of ownership of the problems and requests assigned, focus on managing and resolving issues in alignment with the SLAs, establishing and maintaining communication with technology customers to keep them updated with status of their requests, initiating and performing changes on production systems and proactively escalating any issues that cannot be resolved within the established timeframes. Again, this sounds great, I am ready to hit the apply button myself.

Position Requirements: OK, that means everything here is required.

We are looking for a person who:

• Has 8+ years of experience in database development and support in MS SQL Server environments. OK, first warning shot. They say they want a DBA, but here they mention database development. That’s OK, we’ll keep reading, but it warrants mentioning.
  
• Strong experience in Database Administration in SQL Server ( 2005 and 2008 ) Um, I hope they are not expecting 8+ years of experience with either of those versions, but we’ll press on.
• Experience in troubleshooting and resolving database integrity issues, performance issues, blocking and deadlocking issues, replication issues, log shipping issues, connectivity issues, security issues etc. OK, seems fine, but I am curious as to why they are using both log shipping and replication. Must be a really big shop or they have very specific needs. But this is all standard troubleshooting skills.
• Experience in Performance Tuning, Query Optimization, using Performance Monitor, SQL Profiler and other related monitoring and troubleshooting tools. Fair enough, I wonder what tools they may have purchased from a third party.
• Ability to detect and troubleshoot SQL Server related CPU,memory,I/O, disk space and other resource contention. Ability to detect? What am I, a basset hound? What if I didn’t detect the fact that a developer fills up a disk with a long running transaction? Is that going to reflect poorly on me because I couldn’t detect that the user was a moron, or that they were about to make a mistake?
  
• Strong knowledge of backups, restores, recovery models, database shrink operations, DBCC commands, Clustering, Database mirroring, Replication. What, no mention of log shipping here? But they tossed in clustering, so I guess they are doing all three. Must be an even bigger shop than I thought. Or more paranoid than I can imagine. How many people have strong knowledge of all three, anyway?
• Expert experience in implementing operational automation. This seems rather vague, but nothing to really worry about yet, I suppose. But I am wondering what it means to have ‘expert experience’. How would I show that I have expert experience?
  
• Strong knowledge of how indexes, index management, integrity checks, configuration, patching. How statistics work, how indexes are stored, how they can be created and managed effectively. Why would they mention patching here? It seems rather out of place, almost as if they are just tossing around buzzwords. Now I am wondering if they are really doing clustering, log shipping, and replication or if they just want to weed out people from applying. The rest seems fine, but mentioning integrity checks is redundant since they already said DBCC earlier. So, yeah, I think they are cramming in words here.
  
• Knowledge of SQL Server tools ( Profiler, DTA, SSMS, SAC, SSCM, PerfMon, DMVs, system sprocs) Well, yeah. Seems silly to mention these things here, unless you wanted to cram more buzzwords and acronyms into the description.
• SQL Development – ability to write and troubleshoot SQL Code and design ( stored procs, functions, tables, views, triggers, indexes, constraints ) OK, just out of curiosity, when would I be doing this? At night and on weekends? The first two paragraphs talked about a slightly different role, more administrative, but now it sounds like they also want a developer. That’s two jobs, really. And the bullet points up until now only talked about experience, this one talks about actions, and those actions are not in line with what was mentioned above. I am going to consider this a serious red flag.
• Solid acquaintance with windows server, security delegation, SPNs, storage components. This seems quite vague. Will I be building the servers as well? Handling Active Directory duties too? After the red flag above I will consider this another red flag, because this could be a casual mention of a third or fourth role. That’s a lot of work to be done in any one day or week. And what is a ‘solid acquaintance’ anyway? Is that like my old friend from High School?
  
• Documentation skills for processes and procedures ( creating KBs, runbooks, topology etc ). The hell? When is this supposed to happen? I can’t spend all day writing, racking servers, configuring Active Directory, troubleshooting performance problems, answering the phone, building clusters, fixing replication after it breaks, and writing code. This job description went downhill in a hurry. I’m not sure I would even bother applying to this ad, I really don’t think these people know what they are asking for. Consider this a fifth job, at least, and another red flag.
  
• SQL Database Operational support to tech users Of course. Now, I’m not sure what these users are doing all day, since I am doing everything else apparently. I’ll just assume this means the end users who will complain about performance and then complain about my performance because I can’t do five jobs at once. I mean, not even if they paid me for five jobs could I do all five well enough to please anyone.

Preferred candidates would also meet the following criteria:

• Solid acquaintance with windows server, security delegation, SPNs, storage components. Wasn’t this a requirement?
• Documentation skills for processes and procedures ( creating KBs, runbooks, topology etc ) The hell? Wasn’t this also a requirement? Seems strange to list it here as well, either every applicant needs this skill or not.
• Knowledge of 3rd party DBA tools and applications ( e.g redgate, idera, SCOM, Erwin ) Interesting. It would appear they use a variety of tools, and they consider SCOM to be a DBA tool. I wonder how they are using it as such. Wait a minute, I am now wondering if I will also be the SCOM admin.
• MCDBA / MCT certification Well, MCDBA is for SQL 2000, but they want me to be focused on SQL 2005/8, which would be MCITP. So, which version did they want me to be certified in? And does being an MCT really qualify you to do everything that is listed above?
• Knowledge in a scripting language like Powershell, VBScript, WSH I will assume this is for the automation requirement, but I can’t imagine that they would be using all three, or expect me to know all three well enough.

So, there you go, that’s how I read a job ad before I decide to not apply. There are a possible five jobs listed above and what would appear to be roughly 60 hours or more a week worth of work. Unless the job starts at about $400k a year, it is not even worth my time. So I will pass. I am also curious to know how many of you would pass on this particular job as well. If you want to apply I will give you the link to the actual job. Feel free to make yourself miserable.

So what would be a better job description for a DBA? Let’s assume that you needed everything that was listed in the above description. If your goal is to entice as many SQL Server Experts to apply then how should you present the same information? I am glad you asked. Here is what I would do:

Position Summary:

This is a MS SQL Server DBA role largely responsible for providing operational database services to the organization. We are looking to fill a need to have a highly competent and highly motivated individual in this role. This is a production DBA role, as such it will require a commitment on your part as well as ours. Some of the primary responsibilities of this role would include owning, tracking and resolving database related incidents and requests, fulfilling requests and resolving incidents within SLAs, reviewing service related reports (e.g: database backups, maintenance, monitoring) on a daily basis to ensure service related issues are identified and resolved within established SLAs, responding to database related alerts and escalations and working with database engineering to come up with strategic solutions to recurring problems.

This MS SQL Server DBA role requires a service oriented mentality, high sense of ownership of the problems and requests assigned, focus on managing and resolving issues in alignment with the SLAs, establishing and maintaining communication with technology customers to keep them updated with status of their requests, initiating and performing changes on production systems and proactively escalating any issues that cannot be resolved within the established timeframes.

During your interview you will be asked to discuss a project or particular piece of technology that excites you. We will ask you questions on that project or piece of technology in order to get a better understanding of the depth of your knowledge.

This role requires good communication skills. If you have a blog then please pass along the URL so we can review your work. If you do not actively blog that is fine, but you should be prepared to show us an example of something you have written previously if we should happen to ask.

Position Requirements:

We are looking for a person that has:

• Experience with Database Administration for MSSQL Server.
• Experience in troubleshooting and resolving database problems.
• Experience in Performance Tuning and Optimization (PTO), using native monitoring and troubleshooting tools.
• Experience with backups, restores and recovery models.
• Knowledge of High Availability (HA) and Disaster Recovery (DR) options for MSSQL Server.
• Experience in implementing operational automation using scripts.
• Knowledge of indexes, index management, and statistics.
• Experience working with Windows server, including Active Directory and proper disk configurations.
• Good communication and documentation skills.

Preferred candidates would also meet the following criteria:

• Involvement with the MSSQL Server Community; membership in PASS, active in forums or newsgroups.
• Certification is a plus; MCTS, MCITP, MVP
• Previous experience in either a teaching or volunteer role.

Which One Would You Apply For?

Be honest. I can handle the truth. If you believe the first job description is better, that’s fine.

The reason I like my version (and I hope others would as well) is that it is not overloaded with buzzwords and phrases. It also doesn’t list five different jobs explicitly. That means you are more likely to get one of those elusive SQL Server Experts to actually be interested in applying. Lastly it stresses the importance of communication. Every successful DBA I have met is always a good communicator to some degree.

Not everyone blogs, but most everyone uses email. Certification is a preference, as that shows you can communicate some technical aptitude. And the teaching or volunteering is a way to display that you have an idea as to what to expect in the DBA role. Every DBA I know needs to take time to explain things to others, or to take on some extra work for little to no benefit (just like a volunteer). All of those traits help to define what I would consider a SQL Server expert (or at least someone on their way to becoming an expert).

Now, I understand that the original job description is supposed to be the starting point for a conversation. You will often hear people say “just apply anyway”, because we all know the job description is loaded with buzzwords. So people will build a resume to match the description so that they can have a chance at having the conversation. How, exactly, is this not the same thing that happens on Match.com when people fill out their profiles? You put in information about yourself (which is embellished to a degree) and then you put in information about the person you are looking for. And then you are shocked (SHOCKED!) that you don’t get a match?

The same thing is happening with the standard hiring practice. That’s why we need better job descriptions. If you want a SQL Server Expert to be interested in your open position then you need to change how you are advertising for them.

Oh, one last thing. Look again at that original job description. They are looking for a top notch SQL Server expert. Everyone else is as well. But how much do you think they are willing to pay for that expert? That’s the other problem. Companies want the very best talent for the very lowest cost. And then they are shocked (SHOCKED!) that they can’t find someone good enough.

This ain’t rocket surgery, it’s quite simple. Scale back on the job descriptions. Focus on people with good technical skills (not great, but good), are trainable, have good communication skills, and a willingness to serve others. You’ll be happier with the results.

For me, enjoying my role is something that I consider to be a given and forms very much an essential part of my work life. On the flip side, Steve’s comments have me wondering about other Data Professionals out there and whether or not they too are enjoying their work.

• Do you enjoy your role and are you making the most of it?
• Is you career moving in the direction you want to be?

It’s interesting because often these are not clear cut and dry questions right. We all have our bad days, I sure know I do from time to time, and every role has it’s share of mundane tasks/projects that need to get done.

One thing I do believe strongly is that it’s important you are able to identify for yourself what it is about your work that you enjoy so that you can ensure, create or find the perfect role for you.

What is it that you enjoy most about your role as a Data Professional?

Don’t forget to follow the hashtag (#TSQL2sday) on twitter.

I hope this helps somebody remember that the blog party is less than a week away at this point.

We will be meeting Thursday at the same DBA Place, Same DBA Time.

You can find more details here.  That is my blog post from last week on the topic.  We will be listening to Stacia Misner talk to us on Power Pivot.

The meeting will be broadcast over livemeeting.  Hope you will be able to attend either virtually or in person.