Major Performace problem

My server slows way down after a couple of days. I'm running an application
(directly on the server) which calls a stored procedure several thousand
times. This procedure in turn calls another one 6 times. Using the Profiler
I see a 4 second delay between calls of the outer (or first) SP. If I reboot
the machine and run the same process again there is about .2 second delay
between calls. In either case checking the start and complete time of each
call in the Profiler shows the exact same time so I know my SP's are very
quick. Another thing worth mentioning is just restarting the server itself
doesn't correct the problem. I actually need to reboot the machine.
The 4 second delay is more than double what it was yesterday.
This is a fairly new machine and database. I'm running 2000 developers
addition with SP6a. The Physical usage of the data file is only ~125 megs.
Something else I've noticed is that my connection seems to get dropped from
a remote client. In a .NET application I get a General Network error and in
the SQL Server Management Studio (from SQL 2005) I get an Named pipe error.
Any suggestions?
Thanks,
Joe
On Fri, 13 May 2005 00:00:01 -0400, "Joe"
<J_no_spam@._no_spam_Fishinbrain.com> wrote:
>Any suggestions?
Use perfmon to help locate the problem as memory or IO or CPU, and do
a virus scan.
J.
|||When running the process the CPU usage is 48-51%. Memory is way below the
physical memory and there are no viruses.
If I'm not running the process the CPU usages is < 2%.
"JRStern" <jxstern@.bogus.com> wrote in message
news:o0q981pveqdtfea1fv31isv053grmnnudt@.4ax.com...
> On Fri, 13 May 2005 00:00:01 -0400, "Joe"
> <J_no_spam@._no_spam_Fishinbrain.com> wrote:
> Use perfmon to help locate the problem as memory or IO or CPU, and do
> a virus scan.
> J.
>
|||Running another app on the same server is never good for performance since
they have to share resources with Sql Server. In this case I think you
might have several issues. One is memory pressure. I would guess the app
and sql server are vying for the same memory and SQL Server is constantly
swapping out with the OS. But your symptoms of the outer sp taking a while
to run is most likely caused by one of two things. One is simple compile
time. The first time it runs it must compile not only that sp but all the
sp's it is calling. If they are not coded properly they may get recompiled
each time they run. You should check for compiles and recompiles. Also
make sure you are owner qualifying the sp calls (actually all objects should
be owner qualified) such as EXEC dbo.my_sp. Make sure you are calling the
sp's properly so they can reuse the initial query plans. You can look at
master..syscacheobjects to see if the usecounts column is just one or many
for those sp's to be sure. You also may want to limit the amount of memory
sql servers memory pool can use by adjusting the Max Memory to an amount
that will always leave plenty of memory for the app that is running.
Remember to leave between 256 and 384MB of memory for the MemToLeave area of
Sql Server on top of the Max Memory setting as well.
Andrew J. Kelly SQL MVP
"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:Oml59A3VFHA.2172@.tk2msftngp13.phx.gbl...
> My server slows way down after a couple of days. I'm running an
> application
> (directly on the server) which calls a stored procedure several thousand
> times. This procedure in turn calls another one 6 times. Using the
> Profiler
> I see a 4 second delay between calls of the outer (or first) SP. If I
> reboot
> the machine and run the same process again there is about .2 second delay
> between calls. In either case checking the start and complete time of each
> call in the Profiler shows the exact same time so I know my SP's are very
> quick. Another thing worth mentioning is just restarting the server itself
> doesn't correct the problem. I actually need to reboot the machine.
> The 4 second delay is more than double what it was yesterday.
> This is a fairly new machine and database. I'm running 2000 developers
> addition with SP6a. The Physical usage of the data file is only ~125 megs.
> Something else I've noticed is that my connection seems to get dropped
> from
> a remote client. In a .NET application I get a General Network error and
> in
> the SQL Server Management Studio (from SQL 2005) I get an Named pipe
> error.
> Any suggestions?
> Thanks,
> Joe
>
|||Hi Andrew,
the usecounts for my SP is > 25000. I take it this isn't good.
-Joe
This application only runs once a day and requires very little resources
itself.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#beqX1ZWFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Running another app on the same server is never good for performance since
> they have to share resources with Sql Server. In this case I think you
> might have several issues. One is memory pressure. I would guess the app
> and sql server are vying for the same memory and SQL Server is constantly
> swapping out with the OS. But your symptoms of the outer sp taking a
while
> to run is most likely caused by one of two things. One is simple compile
> time. The first time it runs it must compile not only that sp but all the
> sp's it is calling. If they are not coded properly they may get
recompiled
> each time they run. You should check for compiles and recompiles. Also
> make sure you are owner qualifying the sp calls (actually all objects
should
> be owner qualified) such as EXEC dbo.my_sp. Make sure you are calling the
> sp's properly so they can reuse the initial query plans. You can look at
> master..syscacheobjects to see if the usecounts column is just one or many
> for those sp's to be sure. You also may want to limit the amount of
memory
> sql servers memory pool can use by adjusting the Max Memory to an amount
> that will always leave plenty of memory for the app that is running.
> Remember to leave between 256 and 384MB of memory for the MemToLeave area
of[vbcol=seagreen]
> Sql Server on top of the Max Memory setting as well.
> --
> Andrew J. Kelly SQL MVP
>
> "Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
> news:Oml59A3VFHA.2172@.tk2msftngp13.phx.gbl...
delay[vbcol=seagreen]
each[vbcol=seagreen]
very[vbcol=seagreen]
itself[vbcol=seagreen]
megs.
>
|||No that is good as long as it is the only entry for that sp's execution
plan. The lower the number the less the plan is reused and you want it to
be reused. Are you owner qualifying the calls to the ps executions?
Andrew J. Kelly SQL MVP
"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:eOrNH7nWFHA.712@.TK2MSFTNGP14.phx.gbl...
> Hi Andrew,
> the usecounts for my SP is > 25000. I take it this isn't good.
> -Joe
> This application only runs once a day and requires very little resources
> itself.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:#beqX1ZWFHA.2520@.TK2MSFTNGP09.phx.gbl...
> while
> recompiled
> should
> memory
> of
> delay
> each
> very
> itself
> megs.
>
|||nope. I'm just calling them by name execute sp_****.
I was wondering if adding a with recompile would help the outer SP since the
time is greatly decreased when I reboot the server. It goes from ~4 seconds
between the SP Complete and SP start to ~ 2.5 calls per second.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#fVo2SoWFHA.2256@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> No that is good as long as it is the only entry for that sp's execution
> plan. The lower the number the less the plan is reused and you want it to
> be reused. Are you owner qualifying the calls to the ps executions?
> --
> Andrew J. Kelly SQL MVP
>
> "Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
> news:eOrNH7nWFHA.712@.TK2MSFTNGP14.phx.gbl...
app[vbcol=seagreen]
constantly[vbcol=seagreen]
compile[vbcol=seagreen]
Also[vbcol=seagreen]
at[vbcol=seagreen]
amount[vbcol=seagreen]
area[vbcol=seagreen]
developers[vbcol=seagreen]
dropped
>
|||You absolutely want to owner qualify the sp call. If you don't you can get
serialization of the sp calls. When you call it lots of times this can be a
big hit. Adding recompile will force it to recompile each time and you
normally don't want that.
Andrew J. Kelly SQL MVP
"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:OLsLzYoWFHA.3636@.TK2MSFTNGP14.phx.gbl...
> nope. I'm just calling them by name execute sp_****.
> I was wondering if adding a with recompile would help the outer SP since
> the
> time is greatly decreased when I reboot the server. It goes from ~4
> seconds
> between the SP Complete and SP start to ~ 2.5 calls per second.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:#fVo2SoWFHA.2256@.TK2MSFTNGP14.phx.gbl...
> app
> constantly
> compile
> Also
> at
> amount
> area
> developers
> dropped
>
|||I fully qualified the name but it didn't make any difference. Could the
index need to be rebuilt or is there anything else that could be effected by
calling the SP's so many times and adding, updating so many records?
I'm at a total loss and don't know where to go from here.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OBy5$GtWFHA.3828@.TK2MSFTNGP10.phx.gbl...
> You absolutely want to owner qualify the sp call. If you don't you can
get
> serialization of the sp calls. When you call it lots of times this can be
a[vbcol=seagreen]
> big hit. Adding recompile will force it to recompile each time and you
> normally don't want that.
> --
> Andrew J. Kelly SQL MVP
>
> "Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
> news:OLsLzYoWFHA.3636@.TK2MSFTNGP14.phx.gbl...
the[vbcol=seagreen]
a[vbcol=seagreen]
all[vbcol=seagreen]
objects[vbcol=seagreen]
calling[vbcol=seagreen]
look[vbcol=seagreen]
or[vbcol=seagreen]
If[vbcol=seagreen]
second[vbcol=seagreen]
are[vbcol=seagreen]
server[vbcol=seagreen]
machine.[vbcol=seagreen]
~125[vbcol=seagreen]
error[vbcol=seagreen]
pipe
>
|||After looking at the syscacheobjects table again it show the objtype for the
SP's is Compiled Plan and not execution plan. Does this make a difference?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#fVo2SoWFHA.2256@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> No that is good as long as it is the only entry for that sp's execution
> plan. The lower the number the less the plan is reused and you want it to
> be reused. Are you owner qualifying the calls to the ps executions?
> --
> Andrew J. Kelly SQL MVP
>
> "Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
> news:eOrNH7nWFHA.712@.TK2MSFTNGP14.phx.gbl...
app[vbcol=seagreen]
constantly[vbcol=seagreen]
compile[vbcol=seagreen]
Also[vbcol=seagreen]
at[vbcol=seagreen]
amount[vbcol=seagreen]
area[vbcol=seagreen]
developers[vbcol=seagreen]
dropped
>