SQL SERVER – SP Performance Improvement without changing T-SQL

There are two ways, which can be used to improve the performance of Stored Procedure (SP) without making T-SQL changes in SP.

  1. Do not prefix your Stored Procedure with sp_.
    In SQL Server, all system SPs are prefixed with sp_. When any SP is called which begins sp_ it is looked into masters database first before it is looked into the database it is called in.
  2. Call your Stored Procedure prefixed with dbo.SPName – fully qualified name.
    When SP are called prefixed with dbo. or database.dbo. it will prevent SQL Server from placing a COMPILE lock on the procedure. While SP executes it determines if all objects referenced in the code have the same owners as the objects in the current cached procedure plan.

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL

14 thoughts on “SQL SERVER – SP Performance Improvement without changing T-SQL

  1. Hello Martin,

    It will first look in to master database and then after it will look into mentioned DB. This is degrades performance a bit.

    In future version of SQL Server, it may come with stored procedure which starts with sp_ , if that ever happens all the user created sp_ will be replaced by the system stored procedure.

    Kind Regards,
    Pinal Dave


  2. Thank you Mr.Dave

    I have some doubt in DTS Package. If in my SP using #TEMP table i am not getting Source Column and Destination Column while creaing package.
    If i use ##TEMP instead of #TEMP it gives Column Names. Whats the difference between ## and #.

    Please advice me.

    Martin M A


  3. Hello Dave,

    Thanks for your reply.

    Ok if i use TEMP table in my SP’s. How to do create package and Job Scheduler. While creating package its asking source and destination column. I am taking source column from TEMP table.

    Please advice me how to create package.

    Martin M A


  4. Hi Martin,

    I personally have never used Global Temp Table with SSIS package. I use “Real” Temp Table or “Staging” Temp Table.
    Staging table is temp table created just to populate the data, this is real table created just like any other table and dropped when the use is over. You can use same SELECT statement to INSERT data which you use for temptable.

    #Temp Table does not exist out side scope and I have never experimented with ##Temp table. Staging table is my opinion here.

    Hope this helps,

    Pinal Dave


  5. Hello Dave,
    I have some query, please advice me,

    1. How to generate Column Heading in BCP command.
    2. How to generate differnet file name while executing a job scheduler(it executes a Package)-> Regarding Daily Scheduler.
    3. How to get deleted record from LOG file.

    Martin M A


  6. Hello Pinal,

    I have a requirement in my application where I need to create a function and then call it without using the dbo.function()

    I just want to call it like this

    Select Trim()

    Is this possible…

    I am using Sql Server 2005 in my application. It was possible to use it that way in sql server 2000 by adding some twaeks but i have not found anything for sql server 2005

    your help will be appreaciated


  7. When i Get the Actual Plan Execution of stored procedure , But i didn’t get Non-Clustered index used in Actual Plan Execution, I had write a Dynamic query.


  8. HI Dave,

    I have tried to made changes in SP according to your suggestion but i am not getting any difference of them
    like with your changes i am getting records from 5 min. but in previously sp i am getting same record from 3.5 min .

    I have again executed the sp with your changes i has executed in 2.21 min. can you tell me why.



  9. Hi Dave,

    I want to find out the dependant object of a table in other database. sp_depends gives list of objects only of current database.




  10. Hi Dave,

    I want to improve the query performance. I’ve a table which is 80 Lac records approx. when i run the query for select a record it’s take a lot of time.

    Query : – Select Top 1 case when validitydate<=getdate() then 5 else unsubflag end as unsubflag,RegId,RegistrationDate,planid From Temp Where unsubflag0 and SubId = 1111 and Mobileno =1234567890 Order By RegId desc

    Kindly Suggest.

    Many Thanks,
    Sanjay Kakkar


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