bizarre performance issue

Ive got a box that is having bizarre performance behavior. There is plenty o
f
CPU and RAM available. There is no blocking. I cannot find any table scans,
(other than - - 120 row temp table scans) nor does the Index Tuning Wizrd
recommend that I add anything. No software other than SQL Server and some
CommVault backup software are on the box. But still, performance is all over
the place. Sometimes everything is humming right along between 3 and 5
seconds, but later on it will be between 40 and 50 seconds. Of course I see
all of these Procs in Profiler, but everything appears to be OK with them.
Ive been on this all day and am really at a loss as to what to do from here.
Any ideas?
TIA,
ChrisRThat is not much to go on. What is it you are doing? 3 to 5 seconds isn't
humming along in my book but it is hard to say without seeing what you are
trying to do. No that it should make that much of a difference but maybe
you are getting a lot of recompiles due to the temp table populations. Have
you tried replacing them with table vars? They sound like the optimal size
for table vars over temp tables.
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:594022ED-21B3-44F6-84A3-803F877D243D@.microsoft.com...
> Ive got a box that is having bizarre performance behavior. There is plenty
> of
> CPU and RAM available. There is no blocking. I cannot find any table
> scans,
> (other than - - 120 row temp table scans) nor does the Index Tuning Wizrd
> recommend that I add anything. No software other than SQL Server and some
> CommVault backup software are on the box. But still, performance is all
> over
> the place. Sometimes everything is humming right along between 3 and 5
> seconds, but later on it will be between 40 and 50 seconds. Of course I
> see
> all of these Procs in Profiler, but everything appears to be OK with them.
> Ive been on this all day and am really at a loss as to what to do from
> here.
> Any ideas?
> --
> TIA,
> ChrisR|||ChrisR wrote:
> Ive got a box that is having bizarre performance behavior. There is
> plenty of CPU and RAM available. There is no blocking. I cannot find
> any table scans, (other than - - 120 row temp table scans) nor does
> the Index Tuning Wizrd recommend that I add anything. No software
> other than SQL Server and some CommVault backup software are on the
> box. But still, performance is all over the place. Sometimes
> everything is humming right along between 3 and 5 seconds, but later
> on it will be between 40 and 50 seconds. Of course I see all of these
> Procs in Profiler, but everything appears to be OK with them. Ive
> been on this all day and am really at a loss as to what to do from
> here. Any ideas?
When you say the procs, as they appear in Profiler, appear ok, what do
you mean? If they are in fact appearing within your expected performance
parameters, you should see CPU, Reads, and Duration figures that are
normal. Duration should be in the 3,000 - 5,000ms range as you mentioned
and in all likelyhood, performance at the client should be good as well.
If OTOH you are seeing a Duration of 30,000ms or elevated CPU or Read
activity, then I'm guessing that Profiler is showing you there is a
problem.
Can you be more specific about any differences in the Profiler output
between good and bad performance.
David Gugick
Quest Software
www.imceda.com
www.quest.com