SQL SERVER – SP Performance Improvement without changing T-SQL

« SQL SERVER – 2005 Reserved Keywords

SQL SERVER – 2005 – Silent Installation – Unattended Installation »

SQL SERVER – SP Performance Improvement without changing T-SQL

April 10, 2007 by pinaldave

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

Posted in Pinal Dave, SQL, SQL Authority, SQL Optimization, SQL Performance, SQL Query, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology | 13 Comments

13 Responses

on April 29, 2007 at 11:26 pm | Reply Martin

If we mention the DB name like db.Sp_name means what will happen? if it looks first master or mentioned DB.
on April 29, 2007 at 11:28 pm | Reply pinaldave

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://www.SQLAuthority.com)
on April 30, 2007 at 12:59 am | Reply Martin

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
on April 30, 2007 at 6:26 am | Reply pinaldave

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://www.SQLAuthority.com)
on May 2, 2007 at 9:38 pm | Reply Martin

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
on May 2, 2007 at 9:47 pm | Reply pinaldave

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://www.SQLAuthority.com)
on May 2, 2007 at 9:56 pm | Reply Martin

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

Regards
Martin M A
on May 22, 2007 at 3:21 am | Reply Martin

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
on July 13, 2007 at 8:33 am | Reply Deepak Sinha

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
on July 14, 2007 at 12:02 am | Reply Nilesh

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.
on July 14, 2007 at 12:35 am | Reply Nilesh

which give ther better performance in dyanmic T-SQL Or TSQL
on March 26, 2008 at 10:40 pm | Reply Mohit

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
on July 21, 2009 at 6:12 pm | Reply Yogesh

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