Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Steps to tune a specific query Expand / Collapse
Author
Message
Posted Friday, March 21, 2014 8:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:39 AM
Points: 148, Visits: 314

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.
Post #1553511
Posted Friday, March 21, 2014 9:32 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:38 PM
Points: 3,931, Visits: 7,160
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

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; They'll drag you down to their level and beat you with experience"
Post #1553545
Posted Friday, March 21, 2014 9:36 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 12:54 PM
Points: 611, Visits: 439
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 .
Post #1553549
Posted Friday, March 21, 2014 9:43 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:38 PM
Points: 3,931, Visits: 7,160
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; They'll drag you down to their level and beat you with experience"
Post #1553556
Posted Friday, March 21, 2014 9:57 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:04 PM
Points: 3,572, Visits: 8,008
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.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1553574
Posted Friday, March 21, 2014 3:24 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 18, 2014 6:39 AM
Points: 158, Visits: 629
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!
Post #1553686
Posted Monday, March 24, 2014 7:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:39 AM
Points: 148, Visits: 314
Hi friends,

Thank you very much for your suggestions, they are helpful for me.

Post #1554027
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse