SQL Server: how can I know my stored procedure is optimum in performance

Question

Hi all,

I would like to know how to measure/know that one's stored procedure is optimum in performance. That stored procedure can be inserting/deleting/updating or manipulating data in that procedure. Please describe the way/approach how to know performance in SQL Server.

Thanks in advance!

Answer 1

There is a very simple test to establish this... ;)

If it's done by the time you come back with a cup of coffe it is a perfect sproc.

If it's done before you're done drinking your coffe, it is a solid sproc.

If you're done drinking your coffe and the sproc is still running it needs improvement.

Answer 2

1) Find out the runtime of the stored procedure under typical load

2) Find out expected response time for majority of the users

3) if #1>#2, it needs fixing

4) Find out the IOs used by SP

5) Multiply #4 by expected average usage (e.g. will the SP be used once a day by one user, or every 1 minute by 100s of users?)

6) If #5 is greater than your DBAs will tolerate as too much load on DB, it needs fixing.

7) Repeat #5/#6 with expected peak usage.

8) if none of #3. 6 or 7 triggered "fix it" it's optimal. Go fix something else.

If you want more precise/better suggestions, go fix your question to provide actual details about the stored procedure, the environment and the usage.

Also, you need to read performance tuning guides, these 2 can start you off

http://www.sql-server-performance.com/tips/performance_main.aspx

Especially query plan analysis:

http://www.sql-server-performance.com/tips/query_execution_plan_analysis_p1.aspx