how to see query performance in sp

Question

what are the main 3 things we see in stored procedure perfoformance

Answer 1

I guess as little CPU and memory in shortest time as possible.

---

Arthur My Blog

Answer 2

Hi,

If you want to check the execution times of some SP you can use Profiler: http://msdn.microsoft.com/en-us/library/ms187929.aspx.

If you want to analyze the performance of your SP code you can check the query plan: http://blog.sqlauthority.com/2009/08/21/sql-server-get-query-plan-along-with-query-text-and-execution-count/

David.

Answer 3

also check disk I/O for read activity, pages read, etc

---

John

http://knowledgy.org

Answer 4

Hi vijay60,

Please also refer to the following article about monitorring Stored Procedure Performance:
http://www.sql-server-performance.com/2008/monitor-stored-procedure-performance/ 

Thanks,
Eileen

Answer 5

how to understand query excution plan

Answer 6

Stored procedure optimization mostly revolves around "reads" reduction through indexing:

http://www.sqlusa.com/articles/query-optimization/

However, prior to addressing "reads" reduction through indexing, the stored procedure may require reengineering to eliminate inefficiencies and reduce complexity. Overly complex query may confuse the database engine when generating execution plan.  A single statement complex query may be replaced with multiple simple queries connecting via #temptables or @tablevariables during sp reengineering. A query using views may actually look simple, however, the SS engine replaces the views with their (complex) definition, and the result may turn out to be a very complex query.

When you reduce "reads", you reduce memory & CPU load simultaneously.

Frequent optimization goal: eliminating non-SARGable predicates in WHERE clause:

http://www.sqlusa.com/bestpractices/sargable/

---

Kalman Toth SQL SERVER & BI TRAINING

Answer 7

Hi vijay60,

For understanding execution plan, please refer to the following link:

http://www.simple-talk.com/sql/performance/execution-plan-basics/

http://www.codeproject.com/Articles/9990/SQL-Tuning-Tutorial-Understanding-a-Database-Execu 

Thanks,
Eileen