There are two ways, which can be used to improve the performance of Stored Procedure (SP) without making T-SQL changes in SP.
- 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. - 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
15 Comments. Leave new
If we mention the DB name like db.Sp_name means what will happen? if it looks first master or mentioned DB.
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
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
Thank you i will try this; if i feel hard i will ask you.
Regards
Martin M A
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
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
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.
which give ther better performance in dyanmic T-SQL Or TSQL
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
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
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
Hi Dave, Does this compile locking on one part names still occur on 2012 and newer?
Thanks for the help!
JD