Hiring a SqlServer OLTP Specialist, what experience or requirements should you look for?
I am facing Db performance issues with an OTLP project that I am working on. Another developer and I got to our accumulated performance knowledge and asked a person to join the team to help us speed up our application.
For some background, we made schema changes to denormalize chunks of data, optimized each query, ran several database tuning consultants to get our indexes in order, configured MSSql server parameters.
We don't need someone to come and say that unification can be slow and what is lethargy, we need someone who knows what to do after exhausting all the steps listed above.
Does anyone have any advice or experience of hiring an OLTP DBA for exchange? What questions can we ask the database administrator during the interview?
In our strange situation that we are in, we know that we need someone 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. It makes sense?
source to share
Ok, this is telling me something:
For some background, we made schema changes to denormalize chunks of data, optimized each query, ran several database tuning consultants to get our indexes in order, configured MSSql server parameters.
You've already matched or exceeded what 90% of people who call themselves database administrators can do.
The problem is that many DBAs are not really programmers, they are more on the system administration side. You need a DBA programmer who is not only excellent at TSQL but also knows your other programming languages.
I spend a significant chunk of my time on database setup issues like this, and the solution often involves a significant reorganization all the way from the front end to the database schema. You cannot solve these problems in isolation, and without full control (and full understanding) of the entire architecture, you will never get the performance you need.
You may be the best person for the job, and it might be wiser for you to hire someone to get the job done from your plate to focus on OLTP performance issues.
source to share
This is where you have to be careful, you might end up hiring DBA Guru, get it to significantly improve database performance and still have problems with your application that are rooted in its architecture.
Some ideas:
-
Take the most complex query that you have optimized, give it to the DBA with QA candidate and ask him / her to optimize it again. Ask them to describe what they did and how they did it.
-
Make sure this person understands the hardware when you will be using multiple filegroups, RAID arrays, data partitioning, 64 and 32 bit performance, etc.
-
Look for someone who also has a software architecture background.
-
Ask them some tricky SQL server questions for example. What is an OVER expression? Are GUIDs good primary keys and why is ID preferred?
source to share
Re-tune your DB a bit before your own optimization and give it to them. See if they can tweak it to be good or better than the changes you made.
Ask why they chose this technique.
Take the links and find out about the mediums they came from and which are most likely to match your own.
source to share
A good DBA will be able to tell you in a high-level interview what steps they will take next. You should pay more attention to the thought processes here, rather than solving the problem. When the DBA has provided some solutions, go back and try them and ask them why the problem needs to be addressed along these lines.
This method very quickly distinguishes men from boys.
source to share
Of course, without seeing the database, it's hard to tell what might be the best optimization method. Given what you have done, you probably need to hire a database designer - one with experience designing and configuring databases in the size range that you have. When addressing the question of how they approach the problem, see if the interviewer will look at the poorly performing queries first and run the profiler to see what is happening and identify them. The person should be able to answer specific questions about sniffing parameters and how to avoid them, what are the methods that can be used to prevent cursors, why statistics need to be updated, making the query acceptable. There are some common things that I would consider when tuning performance.Is the network maximum (sometimes it is not a database), is the overall design poorly thought out, are you using SQl code that does not perform well at all? If all of your searches allow wildcards as the first character, for example, they can't even be forced quickly. If your connections are on multi-column natural keys, they are slower than they should be. Are you storing more information in the field, causing a lot of manipulation to get the data back? Are you using cursors? Are you using functions? You are reusing code,when you shouldn't be? Do you always return the minimum required information? Are you closing connections? Are you getting dead ends? Are your table rows too wide? Do you have too many records in specific tables (cleaning up old records or placing them in an archived database can make a huge difference)? How string-oriented is your code and not? These are examples of how the people with whom the experienced database will look, and therefore the things they should talk about in interviews.
Some examples of bad code (you know you've already optimized) might give you a good idea of their approach to how they are going to be tuned. You want someone to be methodical and have a depth of SQL knowledge.
There are some good books on performance tuning - I would suggest getting them and reading them before the interview.
source to share