SQL Server Performance

Estimated Execution plan

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by rekincaid, Feb 14, 2011.

  1. rekincaid New Member

    I'm working on a system where the stored procs are all saved to a "central" database library. The tables from which the procs read are in different (many) databases.
    When I attempt to look at the estimated execution plan for any sp, the result just shows the "use database" statement and the actual execute line from the proc.
    Is this due to the designer's decision to put all stored procs in a central database that does not contain the tables from which the actual proc reads? That's my suspicion.
    If that's the case, then doesn't it follow that this is not a recommended practice for performance? I've already had the discussion about why it's not the most intuitive course of action, but I'm the "consultant."
    Thanks!
  2. satya Moderator

    Welcome to the forums.
    Are you trying to obtain the estimated execution plans on the stored procedures as a part of your consultation?
    I would suggest to capture the execution plan events using PROFILER (Server side trace) saved to a table where you can refer them for further analysis. FYI the estimated is an estimation which may not give clearer picture on the usage of indexes while the stored procedure is executed.
    http://www.sql-server-performance.com/tips/query_execution_plan_analysis_p1.aspx
    http://msdn.microsoft.com/en-us/library/ms178071.aspx
    http://www.simple-talk.com/sql/performance/execution-plan-basics/
    http://www.mssqltips.com/tip.asp?tip=1856

Share This Page