Performance of SPROC changed by dbo. prefix

[teddysnips]

In a system I'm maintaining there is a Stored Procedure called
dbo.MyStoredProcedure. I didn't create this - it was created by a
developer who has now left. I don't know how the object came by its
"dbo." prefix, but I think he created it in QA.

Anyway, there were some performance issues (it was taking between 4
and 10 seconds to complete) so I copied the SQL into a QA window and
it consistently ran in under 1 second. So I created a new SPROC with
SQL exactly identical to the old one, but without the "dbo." prefix,
and that too runs in <1 second.

Any thoughts?

Edward

[Greg D. Moore \(Strider\)]

<te********@hotmail.comwrote in message
news:11**********************@v33g2000cwv.googlegr oups.com...

In a system I'm maintaining there is a Stored Procedure called
dbo.MyStoredProcedure. I didn't create this - it was created by a
developer who has now left. I don't know how the object came by its
"dbo." prefix, but I think he created it in QA.

dbo = database owner.

This is actually fairly common.

So not really sure what you're finding unusual here.

However, that said, you should call all stored procs with the owner
qualifier included.

Example:

stored proc FOO

Created by the sa so it's qualifed as:

dbo.FOO

Now user BAR comes along and calls:

exec FOO

First SQL Server will check to see if there is a stored proc BAR.FOO and try
to execute that. If not, THEN it'll look up dbo.FOO and try to execute
that.

Sounds like what's happening here. (Or something similar.) (note it's even
worse if it's named sp_xxxx).

Not sure why it would take 4-10 seconds, but I suspect that's part of the
issue.

Anyway, there were some performance issues (it was taking between 4
and 10 seconds to complete) so I copied the SQL into a QA window and
it consistently ran in under 1 second. So I created a new SPROC with
SQL exactly identical to the old one, but without the "dbo." prefix,
and that too runs in <1 second.

Any thoughts?

Edward

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

[Gert-Jan Strik]

te********@hotmail.com wrote:

>
In a system I'm maintaining there is a Stored Procedure called
dbo.MyStoredProcedure. I didn't create this - it was created by a
developer who has now left. I don't know how the object came by its
"dbo." prefix, but I think he created it in QA.

Anyway, there were some performance issues (it was taking between 4
and 10 seconds to complete) so I copied the SQL into a QA window and
it consistently ran in under 1 second. So I created a new SPROC with
SQL exactly identical to the old one, but without the "dbo." prefix,
and that too runs in <1 second.

Any thoughts?

Edward

Maybe all the stored procedure needed was a recompilation. Maybe it had
nothing to do with the dbo prefix.

Please post back if the behavior is consistent (IOW, if performance
degrades if you change it back to the dbo prefix).

Gert-Jan

[Erland Sommarskog]

(te********@hotmail.com) writes:

In a system I'm maintaining there is a Stored Procedure called
dbo.MyStoredProcedure. I didn't create this - it was created by a
developer who has now left. I don't know how the object came by its
"dbo." prefix, but I think he created it in QA.

Anyway, there were some performance issues (it was taking between 4
and 10 seconds to complete) so I copied the SQL into a QA window and
it consistently ran in under 1 second. So I created a new SPROC with
SQL exactly identical to the old one, but without the "dbo." prefix,
and that too runs in <1 second.

Any thoughts?

All stored procedures in a database (and all tables, all views etc)
belongs to a schema, and the full name within the database is
schema.procedure. If you leave out the schema when you create your
procedure, the procedure is created in your default schema. If you are
the database owner the default is "dbo". On SQL 2000, your default
schema is always the same as your user name. But in SQL 2005, owners
and schema are separeate, and all users can have dbo as their default
schema.

In many databases, all objects are in the dbo schema.

It follows from this, that whatever the performance problems with
your procedure due to, it was not the dbo prefix. (Unless you recreated
the procedure in your default schema which have tables that are
namesakes with those in the dbo schema - but are much smaller.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Greg D. Moore \(Strider\)]

"Gert-Jan Strik" <so***@toomuchspamalready.nlwrote in message
news:45***************@toomuchspamalready.nl...

te********@hotmail.com wrote:

>>
In a system I'm maintaining there is a Stored Procedure called
dbo.MyStoredProcedure. I didn't create this - it was created by a
developer who has now left. I don't know how the object came by its
"dbo." prefix, but I think he created it in QA.

Anyway, there were some performance issues (it was taking between 4
and 10 seconds to complete) so I copied the SQL into a QA window and
it consistently ran in under 1 second. So I created a new SPROC with
SQL exactly identical to the old one, but without the "dbo." prefix,
and that too runs in <1 second.

Any thoughts?

Edward

Maybe all the stored procedure needed was a recompilation. Maybe it had
nothing to do with the dbo prefix.

Please post back if the behavior is consistent (IOW, if performance
degrades if you change it back to the dbo prefix).

Duh, didn't even think of recompilation.
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Gert-Jan

[teddysnips]

On 19 Feb, 22:35, Gert-Jan Strik <[e-mail]:

[e-mail] wrote:

In a system I'm maintaining there is a Stored Procedure called
dbo.MyStoredProcedure. I didn't create this - it was created by a
developer who has now left. I don't know how the object came by its
"dbo." prefix, but I think he created it in QA.

Anyway, there were some performance issues (it was taking between 4
and 10 seconds to complete) so I copied the SQL into a QA window and
it consistently ran in under 1 second. So I created a new SPROC with
SQL exactly identical to the old one, but without the "dbo." prefix,
and that too runs in <1 second.

Any thoughts?

Edward

Maybe all the stored procedure needed was a recompilation. Maybe it had
nothing to do with the dbo prefix.

Please post back if the behavior is consistent (IOW, if performance
degrades if you change it back to the dbo prefix).

10/10! I dropped the SPROC, then recreated it identically and it ran
like a greyhound. So all it needed was recompilation. I guess
there's an art to knowing how often/in what circumstances to recompile
SPROCs but I'm a developer, not a DBA so I don't know!

Thanks

Edward

[Erland Sommarskog]

(te********@hotmail.com) writes:

10/10! I dropped the SPROC, then recreated it identically and it ran
like a greyhound. So all it needed was recompilation. I guess
there's an art to knowing how often/in what circumstances to recompile
SPROCs but I'm a developer, not a DBA so I don't know!

You don't even have to drop the procedure, it sufficient to say:

sp_recompile procname

to flush all plans of it out the cache.

Normally, this is not needed, but SQL Server has this feature known as
parameter sniffing. When the optimizer builds the plan on the first
invocation, it looks at the actual parameter values and takes this as
guidance. But if that first invocation is for an untypical value, that
may buy you a plan which is bad for regular input.

This is not the only reason for a this sort of behaviour. It can also
be that the statistics are such that the optimizer's estimates for
two plans are very close, although one of the plans are not good at all.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx