What does SET NOCOUNT ON do?

MSDN tells us that NOCOUNT:

Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.

It also tells us when its useful to use it:

SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

So basically it can aid performance when you have a sProc that executes multiple statements, but that don't return a great deal of data.

A while back, some ODBC libraries got entirely confused by DONE_IN_PROC messages, so it was actually needed in order to make client code function correctly. I don't believe that is the case any more with current generation libraries.

SSRS (Report Services) likes you to use them in your Stored Procedures that support a report. Apparently too many messages coming back from the stored procedure can confuse the report processing.