SQL SERVER 2008 – Why one should not prefix user stored procedures with sp_

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 )

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s