SQLTeam.com | Weblogs | Forums

Sql server performance tesring

What's is the best way to check sp / query performance stats before and after archiving the table data used in sp/query.

You could just time it (i.e. a (deliberate) synthetic test)

You could build a timer into the Sproc. We do that, every one of our SProcs logs the "start" with a (concatnated) list of all the parameters used, and then on exit updates that log-row with the exit time, and a zero/error-value "result code". So easy for us to see the elapsed time for that Sproc over time.

Or you could use SQL Profiler and monitor the performance of either "everything", or a specific Sproc / query etc. You could run that after data archiving and compare the two.