performance monitoring: sproc

Friday, March 9, 2012

Performance of SPROC changed by dbo. prefix

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

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

--
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

|||On 19 Feb, 22:35, Gert-Jan Strik <s...@.toomuchspamalready.nlwrote:

Quote:

Originally Posted by

teddysn...@.hotmail.com wrote:
>

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Any thoughts?

Quote:

Originally Posted by

Edward

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

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.

performance of select count sproc

I have a sproc :
select count(itemId) from Items where itemName = @.itemName and
itemDescription = @.itemDescription and itemParentId is null
The Items table does not have indexes on itemName nvarchar(150) or
itemDescription nvarchar(512) because inserts are frequently made, and such
indexes would kill that performance ...
How to speed up my "select count(itemId) ..." sproc ?Add some indexes?
Seriously - how frequent do you mean by frequent with regard to the inserts?
In order to get the rows back that match you probably need some indexes, but
how many rows are there in the table, what is the structure, and what other
indexes already exist?
Mike John
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:OkNolGEVGHA.1868@.TK2MSFTNGP09.phx.gbl...
>I have a sproc :
> select count(itemId) from Items where itemName = @.itemName and
> itemDescription = @.itemDescription and itemParentId is null
> The Items table does not have indexes on itemName nvarchar(150) or
> itemDescription nvarchar(512) because inserts are frequently made, and
> such indexes would kill that performance ...
> How to speed up my "select count(itemId) ..." sproc ?
>|||John A Grandy (johnagrandy-at-yahoo-dot-com) writes:
> I have a sproc :
> select count(itemId) from Items where itemName = @.itemName and
> itemDescription = @.itemDescription and itemParentId is null
> The Items table does not have indexes on itemName nvarchar(150) or
> itemDescription nvarchar(512) because inserts are frequently made, and
> such indexes would kill that performance ...
> How to speed up my "select count(itemId) ..." sproc ?
You add an index that includs at least of the columns itemName and
itemDescription. If it also includes itemParentID and ItemId,
the index will be covering, which may be even better.
Yes, an index will degrade INSERT performance, but "kill" is too
strong a word. Even if the potential index columns are a tad long
to be in an index.
On the other hand, when you run your SELECT query, you will have a
table lock, and you will block inserts entirely while your query
runs.
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|||I forget to mention this is on SS2K and the itemDescription field does
contain values exceed 900 bytes ... so creation of a compound index is
impossible.
I'm thinking of adding a non-clustered non-uniquedly indexed HashValue
column that is computed from itemName and itemDescription. But how to write
the SQL that computes the hashes for existing rows where itemParentID = null
?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97974A93A02DYazorman@.127.0.0.1...
> John A Grandy (johnagrandy-at-yahoo-dot-com) writes:
> You add an index that includs at least of the columns itemName and
> itemDescription. If it also includes itemParentID and ItemId,
> the index will be covering, which may be even better.
> Yes, an index will degrade INSERT performance, but "kill" is too
> strong a word. Even if the potential index columns are a tad long
> to be in an index.
> On the other hand, when you run your SELECT query, you will have a
> table lock, and you will block inserts entirely while your query
> runs.
>
> --
> 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|||If the problem is the nvarchar being wider than 900 I would ask if it
*really* needs to be Nvarchar. What data is actually stored in there -Ofthe
people use it just in case, but in fact varchar will do the job, and save
half the disc space, and (in your case) allow the index.
Regardless of the above I suspect having an index on name and parentid would
seem to be likely to help. But please give us some facts - how many rows,
what existing indexes and how many inserts per second are you looking at to
make you worry about an index *killing* insert performance.
Mike John
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:%23l6EIoEVGHA.736@.TK2MSFTNGP12.phx.gbl...
>I forget to mention this is on SS2K and the itemDescription field does
>contain values exceed 900 bytes ... so creation of a compound index is
>impossible.
> I'm thinking of adding a non-clustered non-uniquedly indexed HashValue
> column that is computed from itemName and itemDescription. But how to
> write the SQL that computes the hashes for existing rows where
> itemParentID = null ?
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns97974A93A02DYazorman@.127.0.0.1...
>
>|||About half a million rows.
Can't change nvarchar to varchar (not sure why nvarchar but too late to
change)
Existing indexes:
itemId (PK unique non-clustered)
itemParentId (non-unique, non-clustered)
compound index : itemId_itemPartialName (non-unique, non-clustered)
inserts target rate = approx 8 per second
itemId (PK , int , not null)
itemPartialName (nvarchar(24) , not null)
itemParentId (int , null)
itemName (nvarchar(150) , not null)
itemDesc (nvarchar(512) , null)
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:uvienyEVGHA.6048@.TK2MSFTNGP11.phx.gbl...
> If the problem is the nvarchar being wider than 900 I would ask if it
> *really* needs to be Nvarchar. What data is actually stored in
> there -Ofthe people use it just in case, but in fact varchar will do the
> job, and save half the disc space, and (in your case) allow the index.
> Regardless of the above I suspect having an index on name and parentid
> would seem to be likely to help. But please give us some facts - how many
> rows, what existing indexes and how many inserts per second are you
> looking at to make you worry about an index *killing* insert performance.
> Mike John
> "John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
> news:%23l6EIoEVGHA.736@.TK2MSFTNGP12.phx.gbl...
>|||John,
create an index on checksum(itemDescription) and checksum(itemName)|||John A Grandy (johnagrandy-at-yahoo-dot-com) writes:
> I forget to mention this is on SS2K and the itemDescription field does
> contain values exceed 900 bytes ... so creation of a compound index is
> impossible.
Create the index itemName alone then. That will have to do.

> I'm thinking of adding a non-clustered non-uniquedly indexed HashValue
> column that is computed from itemName and itemDescription. But how to
> write the SQL that computes the hashes for existing rows where
> itemParentID = null ?
You could add a checksum column:
chksum AS checksum(itemDescription)
and then index that column. The condition would then have to be:
chksum = checksum(@.itemDescription) and
itemDescrtipion = @.itemDescription
I would try indexing only itemName first, though. Or itemParentID + itemName
if the condition itemParenID is selective.
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|||What effect will this solution have on the performance of inserts ?
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1143769674.159298.83680@.j33g2000cwa.googlegroups.com...
> John,
> create an index on checksum(itemDescription) and checksum(itemName)
>|||What is wrong with creating a bigint hash col and populating it dynamically
in C# whenever a new item is added ? ( Write a console app to update the
existing rows. ) The hashValue is MD5 algorithm for itemName concatenated
itemDescription (throw a pipe char inbetween to prevent accidental dupes
from being created ).
Then write a sproc which
1. creates a temp table
selects hashValue, count(hashValue) from Items where parentItemId = null
group by hashValue
2. write an update query that sets itemCount for each parentless child item
to the hashCount selected from the temp table joined to the original table
on the hashValue
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97976CD79D92BYazorman@.127.0.0.1...
> John A Grandy (johnagrandy-at-yahoo-dot-com) writes:
> Create the index itemName alone then. That will have to do.
>
> You could add a checksum column:
> chksum AS checksum(itemDescription)
> and then index that column. The condition would then have to be:
> chksum = checksum(@.itemDescription) and
> itemDescrtipion = @.itemDescription
> I would try indexing only itemName first, though. Or itemParentID +
> itemName
> if the condition itemParenID is selective.
>
>
> --
> 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

Friday, March 9, 2012

Performance of SPROC changed by dbo. prefix

performance of select count sproc

performance monitoring

Blog Archive

About Me