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<teddysnips@.hotmail.comwrote in message
news:1171898428.745133.216550@.v33g2000cwv.googlegr oups.com...
Quote:
Originally Posted by
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.
Quote:
Originally Posted by
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|||teddysnips@.hotmail.com wrote:
Quote:
Originally Posted by
>
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|||(teddysnips@.hotmail.com) writes:
Quote:
Originally Posted by
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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nlwrote in message
news:45DA261B.75D850BB@.toomuchspamalready.nl...
Quote:
Originally Posted by
teddysnips@.hotmail.com wrote:
Quote:
Originally Posted by
>>
>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
Quote:
Originally Posted by
Gert-Jan
Quote:
Originally Posted by
teddysn...@.hotmail.com wrote:
>
Quote:
Originally Posted by
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.
>
Quote:
Originally Posted by
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.
>
Quote:
Originally Posted by
Any thoughts?
>
Quote:
Originally Posted by
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|||(teddysnips@.hotmail.com) writes:
Quote:
Originally Posted by
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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
No comments:
Post a Comment