Hiring a SqlServer OLTP Specialist, What experience or requirements should I look for?

Question

I'm running into Db performance issues with an OTLP project I'm working on. Another developer and I have reached the end of our accumulated performance knowledge and seek out an individual to join the team to help us speed up our application.

For some background we've done schema changes to denormalize pieces of the data, optimized every query, ran multiple database tuning advisers to get our indexes just right, tuned MSSql's server options.

We don't need somebody to come in and tell us joins can be slow and what deadlocking is, we need somebody who knows what to do after exhausting all the steps listed above.

Anybody have any tips or experiences hiring OLTP DBA's to share? What kind of questions can we ask the DBA during the interview process?

Its an odd situation to be in, we know we need somebody who knows more than the current team, but we don't know what questions to ask because we don't know what the next steps are. Does that make sense?

Answer 1

You have to be vary careful here, you can end up hiring a Guru DBA, have him improve the database performance significantly and still have issues with your application that are rooted in its architecture.

A few ideas:

1. Take the most complex query you optimized give it to the candidate DBA with QA and get him/her to optimise it again. Have them describe what they did and how they did it.

2. Make sure this person understands hardware, when you would use multiple filegroups, raid arrays, data partitioning, 64 vs 32 bit performance etc...

3. Look for someone who also has some software architecture background.

4. Ask them a few harder SQL server questions eg. What is the OVER statement? Are GUIDs good primary keys and why, is int identity preferable?

Answer 2

De-tune your DB back a little to before your own optimization and give it to them. See if they can tune it to perform as good or better compared to the changes you made.

Ask why they chose that technique.

Take up references and find out about the environments they've come from which are most likely to match your own.

Answer 3

A good DBA will be able to tell you in the interview at a high level what steps they would take next. You should pay more attention to the thought processes here, rather than the solution to the problem. When the DBA has given some solutions, go back and quiz them and ask them why the problem should be solved in those ways.

This method will very quickly distinguish the men from the boys, as it were.

Answer 4

Ok, this tells me something:

For some background we've done schema changes to denormalize pieces of the data, optimized every query, ran multiple database tuning advisers to get our indexes just right, tuned MSSql's server options.

You've already matched or exceeded what 90% of people who call themselves DBAs will be able to do.

The problem is that a lot of DBAs aren't really programmers, they are more on the system administration side of things. You need a DBA programmer who is not only really good at TSQL, but who knows your other programming language(s) as well.

I spend a significant portion of my time on database tuning issues like this, and the solution often involves significant redesigns all the way from the front end back to the database schema. You can't solve these problems in isolation, and without complete control (and total understanding) of the entire architecture, you'll never get the performance you need.

You might be the best person for the job, and it might be smarter for you to hire somebody to take busy work off your plate so you can concentrate on the OLTP performance problems.

Answer 5

How close are you to the max performance of the db? It is very easy to create an OLTP problem that is unsolvable with this technology. As Eric said, total architecture redesign might be in order. More ram, just add more ram :)

Answer 6

Certainly without seeing the database it is hard to say what could be the best way to optimize. Given what you have done, likely you need to hire a database designer - one with experience designing and tuning databases in the size range that you have. In asking how they would approach the problem see if the interviewer would look at first the poorly performing queries and run profiler to see what is happening and to identify them. The person should be able to answer specific questions on parameter sniffing and how to avoid it, what are the methods that can be used to avoid cursors, why do statistics need to be updated, what makes a query saregable. There are some common things that I would look at in performance tuning. Is the network maxed out (Sometimes it isn't the database), is the overall design poorly thought out, are you using SQl code that in general performs badly? If all your searches allow a wildcard as the first character for instance, it isn't even possible to get them to be fast. If your joins are on multi-column natural keys, they are slower than they should be. Are you storing more than one piece of information in a field causing lots of manipulation to get data back out? Are you using cursors? Are you using functions? Are you reusing code when you shouldn't be? Are you always returning the minimum information needed? Are you closing connections? Are you getting deadlocks? Are your table rows too wide? Do you have too many records in particular tables (purging old records or putting them to an archive database can make a huge difference)? How much of your code is row oriented and not set oriented? These are examples of the kinds of things an experienced database person would look at and thus the kinds of things they should talk about in the interview.

Some bad code examples (you know what you have already optimized) can give you a good idea as to their approach to how they would tune. YOu want someone who is methodical and has depth of SQL knowledge.

There are some good books on performance tuning - I would suggest getting them and getting familar with them before interviewing.