Performance testing SQL Server

What would be the recommended way of testing performance on stored procedures? There is one SP that takes about 20 booleans and I would like to test some of the combinations. There is another SP with the same parameters but a different implementation and would like a 开发者_StackOverflow社区way to compare. I can do this manually in .NET if necessary, but was wondering if there was a better way. Thanks!

---

You can measure performance in Server Management Studio by executing the two stored procedures with an actual execution plan. It will give you a break down of performance/cost per each statement in the sp.

You can also you SQL Server Profiler to gather performance metrics.

I personally tend to use a combination of the above two.

---

When you have an overwhelming amount of parameters, always consider Pairwise Testing. I prefer the PICT tool.

---

Well, there are only 2^20 combinations...

You could make a table of the settings you want to test (bool1, bool2, bool3, ...), then add columns for alt1 results and alt2 results (start and end times, actual numerical results in case one has a bug, etc).

Then open a cursor on the settings table (or loop on SELECT TOP 1 WHERE result IS NULL) and EXEC each proc with the settings, recording the start and end times and results and anything else.

So at this point I would keep it all in the database, no need for .NET yet. If the sets of data are large and you need to check end-to-end performance, I might move out of the DB, but right now, less moving parts, the better.