Performance Tuning Quiz #2 - Part 1

You've just been handled a routine problem - make a single stored procedure run faster and/or fewer reads using ONLY index changes, plus make recommendations on changes to the procedure that might further increase performance. Up for the challenge? Download the attached database and stored proc (requires SQL 2008 R2), or create your own in any version by making a copy of the person.contact table called Contacts. Part 2 will cover one solution later this week.

Andy Warren is a software trainer focusing on SQL Server, a member of the PASS Board of Directors, and a principal in this site - SQLShare.com.

nav on 5/18/2010

Wait until thursday? was there any point to this video then?

Danny Costello on 5/18/2010

Great idea... nice little challange...

Jamshid Nouri on 5/18/2010

excellent demo!!!!

Matt on 5/18/2010

I love the challenges, keep em coming.

Mark Holahan on 5/18/2010

Good, real-world problem. Clear presentation. Cliff hanging ending ensures I'll be back for the solution.

Kevin on 5/18/2010

Good challenge. And thanks for giving us newer people a chance to work on it ourselves before providing an answer.

Slavius on 5/18/2010

In stage 1 I got it down to 5 reads, in stage 2 I got it down to 3 reads by completely rewriting the SP.

iceman on 5/19/2010

This is great. Great challenge, keep them coming. Not a dba, but a developer who wears multiple hats.

32DE12EB89 on 5/20/2010

I suspect most viewers don't have SQL 2008 R2 so we didn't take the time (not having much to spare don't you know :-) to build this in 2008 or 2005. It wasn't clear why the sample was in R2 but it would have been more 'friendly' to have it in a more common version. It's still good to have the video and watching the solution and I thank you for that.

Ed Svastits on 5/20/2010

Good challenge. BTW, I had forgotten that the "EXECUTE" command used for running a stored procedure in T-SQL is being deprecated. Thanks for reminding me.

Steve Harris on 5/20/2010

There was no solution in this clip. I feel that each clip should be self-containing. Also, how was the performance monitor setup so capture the results of the statement.

Ling on 5/26/2010

good talk

vinodkumar myakala on 6/15/2010

Excellent video for beginer into Index part

Dean Gross on 12/14/2010

great idea, like this concept of providing a problem and then several different solutions.

Steve Duck on 4/27/2011

Not sure I understood all that was going on.