If the stored procedure starts with sp_, sql server first searches that sp in master database and comes to current database. The stored procedures in master database is global,It is available to all DB’s. It becomes an performance issue.
Query Optimizer: It is a component which analysis the query and determines the the most efficient way to request the data and thus optimizing the sql statement. The process of choosing an execution plan out of several plans is called optimizing and this is done by query optimizer.
SQL Server Query Optimizer is a cost based optimizer. Each possible execution plan has an associated cost in terms of computing resources. The query optimizer chooses that plan which has lowest estimated cost.
Query Execution plan deals with the sequence in which the source tables are accessed, and the methods used to extract the data from the table.
Optimizer Hints are used to override the Query Optimizer’s choice of execution. The five categories of hints are Table hints, Join hints, Query hints, View hints, Lock hints.
Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )