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 (https://blog.sqlauthority.com) , BOL

Previous Post
SQL SERVER – 2005 Reserved Keywords
Next Post
SQL SERVER – 2005 – Silent Installation – Unattended Installation

Related Posts

15 Comments. Leave new

  • 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
    http://blog.SQLAuthority.com)

    Reply
  • Hello Martin,

    #temp is local temptable which does not exist after transaction or session.

    ##temp is global temptable and exist after transaction and session.

    I will soon write article about that and add link here.

    Regards,
    Pinal Dave
    http://blog.SQLAuthority.com)

    Reply
  • If we mention the DB name like db.Sp_name means what will happen? if it looks first master or mentioned DB.

    Reply
  • 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.

    Regards
    Martin M A

    Reply
  • 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,

    Regards,
    Pinal Dave
    http://blog.SQLAuthority.com)

    Reply
  • 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.

    Regards,
    Martin M A

    Reply
  • Thank you i will try this; if i feel hard i will ask you.

    Regards
    Martin M A

    Reply
  • 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.

    Regards,
    Martin M A

    Reply
  • Deepak Sinha
    July 13, 2007 8:33 am

    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

    Reply
  • 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.

    Reply
  • which give ther better performance in dyanmic T-SQL Or TSQL

    Reply
  • 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.

    Thanks,
    Mohit

    Reply
  • 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.

    Thanks

    Yogesh

    Reply
  • Sanjay Kakkar
    April 6, 2012 2:01 pm

    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

    Reply
  • Hi Dave, Does this compile locking on one part names still occur on 2012 and newer?

    Thanks for the help!

    JD

    Reply

Leave a Reply Cancel reply

Menu
Exit mobile version