Performance setting for a job

Hello,
I have a script that create some tables with (Select ... Into) for caching
and optimizing. This cache is created twice a w automatically. Problem:
The caching process needs 20 mins and while this 20 mins my website is not
working because SQL Server is busy with 100%.
Is there any chance to tell the SQL-Server that he should not use more than
20% of it's CPU for this job? No problem if it needs longer...
Thanks in advance.
CFIf you have indexes on the tables, drop these before you delete and
re-insert the data, then add the indexes after
It'll be quicker than inserting a huge amount of data into an indexed table.
You might want to consider using triggers or difference/update scripts to
maintain the tables over the course of the day if possible,
but without knowing what the cache tables consist of or how they are
populated it's difficult to say.
"Christian Funke" <ChristianFunke@.discussions.microsoft.com> wrote in
message news:1575B198-0132-46AA-8C32-1590110AA17A@.microsoft.com...
> Hello,
> I have a script that create some tables with (Select ... Into) for caching
> and optimizing. This cache is created twice a w automatically. Problem:
> The caching process needs 20 mins and while this 20 mins my website is not
> working because SQL Server is busy with 100%.
> Is there any chance to tell the SQL-Server that he should not use more
than
> 20% of it's CPU for this job? No problem if it needs longer...
> Thanks in advance.
> CF

Performance of SPROC changed by dbo. prefix

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

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

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

Performance of extended stored procedures in SQL Server 2000

What is the overhead of using extended stored procedures?

I created a table with 500,000 rows.
1) I ran a select on two columns and it runs in about 5 seconds.
2) I ran a select on one column and called an UDF (it returns a
constant string) and it takes 10 seconds.
3) I ran a select on one column and called a UDF that calls an extended
stored procedure that returns a string and it takes 65 seconds.
I also tried running test 3 with 4 concurrent clients and each client
takes about 120 seconds.(smauldin@.ingrian.com) writes:
> What is the overhead of using extended stored procedures?
> I created a table with 500,000 rows.
> 1) I ran a select on two columns and it runs in about 5 seconds.
> 2) I ran a select on one column and called an UDF (it returns a
> constant string) and it takes 10 seconds.
> 3) I ran a select on one column and called a UDF that calls an extended
> stored procedure that returns a string and it takes 65 seconds.
> I also tried running test 3 with 4 concurrent clients and each client
> takes about 120 seconds.

The overhead is apparently significant in this case. And it may well be
typical. By calling an extended stored procedure, you are completely
serializing the processing. If you rewrote the query as a cursor and
called the XP in the extednded stored procedure, you would probably
see a higher value than 65, but not that much higher.

Thus the overhead is not so much in the XP itself, as in the way
it affects the query.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp