Testing stored procedure scalability
SQLQueryStress is a great tool for small scale concurrency testing but it isn't really up to the job of load testing. A surprisingly little known toolset is available for free, from Microsoft, that can cope with most SQL Server stress testing scenarios, the RML Utilities.

A very brief outline of the tools:


ReadTrace converts Profiler traces from .trc files to .rml (Replay Markup Language).
OStress is used to replay .rml and .sql files against a server.
ORCA (OStress Replay Control Agent) co-ordinates replay across distributed instances of OStress.
Reporter provides performance analysis reports.


There is an excellent fast start article from the SQLCat team which includes a sample database and workloads, Precision Performance for Microsoft SQL Server using RML Utilities 9.0

You have the option of either Profiling activity for replay or, possibly more appropriate in your scenario, hand cranking a set of .sql scripts that you then replay via OStress and ORCA.

Great tools for both load testing and investigating performance issues.