March 21, 2014 at 8:26 am
Dear friends, today I attended an interview for a Senior SQL Developer position, the interviewer asked me a question as follow:
Suppose that you are a SQL Developer, you have no right to configure anything on server such as adding indexes, update statistics, change server configurations...now you have a query that have very poor performance, please list the step you follow to improve it and the sort by priority
I have answer as follow:
first, check whether the query is using index? If yes, check it is a index scan or index seek.
second, check how the developer write the sql query:
1. Don't select any unneccessary columns, because it can cause key lookup
2. check columns on join clause that whether they have indexes, if not, suggest admin to create
3. Check where clause, if using <> or like '%...%',... the index will not be used.
4. avoid using sort, we can remove it and sort on application, for example, sort on C# code when displaying the result for client. The interviewer didn't agree with me about the idea
5.don't use function on where clause
6. and many thinks developer should know, they can search on google
and final, check server configuration:
- Check auto_close, auto_shrink (this can raise index fragmentation), auto_updatestats...check I/O setup
The interviewer seems does not agree with me about the second item, he said that I say as theory and they are not useful. He suggest me to reduce my expectation offer, and he just explains the reason if I accept to join their company.
Friends, how about your ideas. Could you give your ideas about the question 'Steps to tune a specific query' above. Do I miss something for a perfect answer to persuade the interviewer.
Many thanks for reading this.
March 21, 2014 at 9:32 am
I think your reply is solid. "In theory they are not..." Sounds like a response from someone who has "read" a lot but is lacking experience in "doing" a lot regarding performance tuning. In theory, every query would be written perfectly and no tuning would ever be needed :hehe:
If you were to join this company sounds like you have a good opportunity to be a star member of the team!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 21, 2014 at 9:36 am
Firstly never get Demotivated due to the kind interviewer's perception of your answers , its possible he/she may even have no idea that something that you answered even exists .
I been asked about if I have seen the Racks and hardware of my Data center , being a DBA I should have but it was restricted to few people based on company policy . So I fumbled in that . Anyways what I will check for query performing poorly , not saying what you answer is incorrect .
I will first ask is any recent changes have been in the code , like any deployment or anything like that .
No query will start behaving super slow with out any big change , lot of people miss that and jump directly chasing the goose .
1. Actual Execution plans , which tells a lot about operator consuming more cost and if required Index is missing or not ( Read Book by Grant , I guess the only Awesome book on Execution plan) .
2. Logical reads , which causes slowness for sure . SET STATISTICS IO ON etc .
3. Isolation level , table hints .
4. Wild cards , Joins and clauses .
The list probably goes further on type of query , what type objects included . I am sure there are more items and its extensive. Hope it help to some extent .
March 21, 2014 at 9:43 am
Great points! Also, if the query suddenly started performing poorly first you start with your execution plan, but you would also want to check to see if your statistics were out of date (can easily happen if your tables recently had a lot of insert/delete operations) or if your indexes haven't been rebuilt in quite some time. So, you'd also want to see what the fragmentation levels were for the indexes referenced in your execution plan.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 21, 2014 at 9:57 am
These are my observations about your answer, they're not wrong but I can partly understand the interviewer.
first, check whether the query is using index? If yes, check it is a index scan or index seek.
How would you do that? You're missing the previous steps to get here.
Is index really necessary?
second, check how the developer write the sql query:
1. Don't select any unneccessary columns, because it can cause key lookup
How do you define which columns are not necessary?
2. check columns on join clause that whether they have indexes, if not, suggest admin to create
That's a good option but you were told you can't change them, so you need to work on what you have.
3. Check where clause, if using <> or like '%...%',... the index will not be used.
You can change this, but only if it's not really needed or you can change it to something SARGable.
4. avoid using sort, we can remove it and sort on application, for example, sort on C# code when displaying the result for client. The interviewer didn't agree with me about the idea
Sort is expensive but you'll end up doing it anyway if needed. It's like delivering the problem to someone else, but the problem still exists.
5.don't use function on where clause
Don't use system functions on where clause and don't use UDF anywhere. UDF degrade performance anywhere they're used (except for iTVF).
6. and many thinks developer should know, they can search on google
I didn't get this. However, google won't help you if you don't know what to look for.
and final, check server configuration:
- Check auto_close, auto_shrink (this can raise index fragmentation), auto_updatestats...check I/O setup
Same thing as before, you should focus on the query.
Again, there's no perfect answer and you're not wrong, but you could have explained more things to demonstrate you know what you're doing and you're not repeating something you learned on a SQL course.
March 21, 2014 at 3:24 pm
Hi,
I would have similar observation as Luis and would concentrate on the requirements and constraints. Your task is to tune a bad performed query written by someone else. Your constraints say that you cannot add/change indexes, clear cache, update statistics etc. So, you need to perform two tasks:
1. to understand why the query performs poor
2. to rewrite the query
For the first task you would need to observe and analyze the execution plan, to check discrepance between estimated and actual rows, if you find the discrepance to check statistics, to check if the problem is caused by using cached plan previosly created for some other parameters or literals...
After you are done with analysis and you have an idea how to rewrite the query you should bear in mind that you need to return the same result to the client. You cannot eliminate some columns because of Key Lookup (as Luis said, you don't know which columns are required - this is a business logic and it is unknown to you) and if the business logic requires all persons having 'uis' somewhere in the last name you can warn them that this could be slow but you still have to provide the result. The same is for Sort operator. Yes, it is memory consumer and you could eventually remove it if it's caused by final ORDER BY. You can ask business guys if they really need ordered results and you could eliminate it. If business guys are available at the time you tune the query. And if they say OK. First is very rare the case. Second you can guess. Other Sorts are usually in execution plan because an index is missing, but you cannot eliminate them due to constrains you have.
The point with functions in WHERE clause is OK, but with explanations done by Luis. You should try to rewrite predicates to make them SARG-able, but not forget that you cannot change the results - if business needs NOT LIKE, you don't have a choice, but to accept it. You can try to rewrite JOINS to subqueries and vice versa, to use CTE, temp tables, table variables, to reach the same logical result with different queries.
Remember that as someone who tunes a query written by someone else you usually don't have contact persons to discuss with them if it's a good idea to return given columns and given number of rows. In this particular task someone expects from you to make the query execution faster in short time without changing the query interface.
HTH.
___________________________
Do Not Optimize for Exceptions!
March 24, 2014 at 7:47 am
Hi friends,
Thank you very much for your suggestions, they are helpful for me.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply