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,
JoeOn 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:
> >Any suggestions?
> 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
> 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
> >
> >
>|||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...
>> 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
>> >
>> >
>>
>|||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...
> 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...
> >> 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
> >> >
> >> >
> >>
> >>
> >
> >
>|||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...
>> 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...
>> >> 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
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||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
> 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...
> >> 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...
> >> >> 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
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||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...
> 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...
> >> 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
> >> >
> >> >
> >>
> >>
> >
> >
>|||This is my last Post - I hope...
I decided to look at the inner sp in the syscacheobjects table and the
usecounts = 1 and the pagesused = 11.
What would I need to look for to have this be reused?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#fVo2SoWFHA.2256@.TK2MSFTNGP14.phx.gbl...
> 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...
> >> 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
> >> >
> >> >
> >>
> >>
> >
> >
>|||If the sp plan is 11 pages then it must be one heck of a sp. Chances are
you are recompiling often. Try breaking the sp down into smaller sp's so
that each one is less likely to be recompiled.
--
Andrew J. Kelly SQL MVP
"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:ePrP9HzWFHA.2288@.TK2MSFTNGP14.phx.gbl...
> This is my last Post - I hope...
> I decided to look at the inner sp in the syscacheobjects table and the
> usecounts = 1 and the pagesused = 11.
> What would I need to look for to have this be reused?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:#fVo2SoWFHA.2256@.TK2MSFTNGP14.phx.gbl...
>> 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...
>> >> 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
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>