Jeff Moden (9/12/2010)
Be very careful about the functionality of the procedure as your procedure should return the same results after tuning as before. It is not enough that SSMS is returning the "Command(s) completed successfully." message. We should check all the tables used in the procedures. Whether they have the same number of rows and whether the rows are contain the same data. These are the kinds of checks we should perform after tuning.
I'm really happy to see someone say this, especially a "beginner". One of the best things you can do is to set up and execute a series of tests using known data BEFORE you start test and save the results. After tuning, run the exact same tests on the exact same data and compare the results to ensure that NOTHING has changed in the results.
+1; returning different results is not the goal of initial tuning.
My favorite method to check and see if results are identical, particularly on nontrivial result sets, is to send each output (preferably over a good set of test cases) to a text file, and use the Windows commands "comp" or "fc" at the command line to do a byte by byte compare. Alternately, do hash checking with the Java Jacksum[/url] (I currently favor options "-a md5+sha1+sha512 -A -m") or full ECC checking with the faster, more useful, and more limited multithreaded par2 with Intel Threaded Building Blocks.
Note that this method also easily exposes problems like TOP 1 or other "we're only using the first row" without an ORDER by returning essentially arbitrary rows.