lunes, 05 de marzo de 2012 17:20
what are the main 3 things we see in stored procedure perfoformance
Todas las respuestas
lunes, 05 de marzo de 2012 17:29ModeradorI guess as little CPU and memory in shortest time as possible.
Arthur My Blog
lunes, 05 de marzo de 2012 17:32
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/
lunes, 05 de marzo de 2012 19:39also check disk I/O for read activity, pages read, etc
miércoles, 07 de marzo de 2012 7:24ModeradorHi vijay60,
Please also refer to the following article about monitorring Stored Procedure Performance:
viernes, 09 de marzo de 2012 7:04
how to understand query excution plan
lunes, 12 de marzo de 2012 18:06
Stored procedure optimization mostly revolves around "reads" reduction through indexing:
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:
Kalman Toth SQL SERVER & BI TRAINING
- Editado Kalman TothMicrosoft Community Contributor lunes, 12 de marzo de 2012 18:08
martes, 13 de marzo de 2012 1:59Moderador
For understanding execution plan, please refer to the following link:
- Marcado como respuesta Eileen ZhaoMicrosoft Contingent Staff, Moderator martes, 27 de marzo de 2012 12:50