Performance and tuning

• CapnHector

  SSCoach

  Points: 16143

  More actions

  April 13, 2012 at 12:53 pm

  #1473429

  also from the specific times there was probably a query in the company some one tuned from 20 min to 2 seconds and the interviewer knows exactly what was done (maby it was the interviewer) and by asking it wants to see if the interviewee (sp?) knows where to look to trim the execution time.

  ---

  For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

  For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

  Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
  Jeff Moden's Cross tab and Pivots Part 1[/url]
  Jeff Moden's Cross tab and Pivots Part 2[/url]

• SQlLogic

  SSCrazy Eights

  Points: 9686

  More actions

  April 13, 2012 at 1:12 pm

  #1473445

  this is most commonly asked question in SQL DBA job interviews. I am also looking for the good answer.

  I tried to convince them to go through exec plan , Index, joins, Filtering conditions.

  thanks for posting here..

• Grant Fritchey

  SSC Guru

  Points: 398882

  More actions

  April 14, 2012 at 5:27 am

  #1473638

  zojan (4/13/2012)

  ---

  Sean Lange (4/13/2012)

  ---

  jzoran (4/13/2012)

  ---

  If the request is reduce time of execution from 20 min to 20 sec the only one possibility is checking indexes (missing indexes or it could be right index but with big fragmentation...). So look at the execution plan for this stuffs.

  If the request is create best possible query then (in my opinion) is first step look at the code:

  look at the joins, and filter conditions in them, is there any function in where cond. is there anything for calculate in where cond.... and so on

  After that you can look at execution plan and start tune indexes ...

  There are SO many other things to consider besides indexes. To blindly say the only one possibility is indexes is way too short sighted. Maybe there is a varbinary(max) column in the query but is not used. No indexing at all but the execution time would cut dramatically. There simply is no magic button for how to make a query faster.

  Oh yeah, I agree with you , it could be a lots of things, but I understand there is an interview question(not a real situation). The answer must be simple and most common option. And I think there is something with index (if request is drastically reduce time of execution).

  Have to disagree with you here. If I'm interviewing, I don't want you to jump to some simple and common option. I want to hear your thought processes and your investigative processes. If I say I have a slow running query, and we know which query it is, what do I do to fix it. If your answer is "Look at indexes" our interview is going to be very short.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning