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

I would like to know how to measure/know that one's stored procedure is optimum in performance. That stored procedure can be inse开发者_Python百科rting/deleting/updating or manipulating data in that procedure. Please describe the way/approach how to know performance in SQL Server.

Thanks in advance!

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

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.