Intermittent Performance Issue

I am having a severe performance issue with a procedure, but it only happens
intermittently so it's difficult to track down. The procedure is too
complex to include in this post because it uses nested views with lots of
tables etc. Basically it does this:
1. create temp table 1 (fast)
2. insert query result into table 1 (100 lines or so)
3. create temp table 2
4. query temp table 1 and insert results into table 2
5. query temp table 1 again (different summary) and insert results into
table 2
6. query temp table 1 again (different summary) and insert results into
table 2
7. query temp table 1 again (different summary) and insert results into
table 2
8. update temp table 2
9. select * from temp table 2 as output from procedure
Normally it runs very fast (a couple of seconds). When it's slow, it takes
30 - 150 seconds even though it's only dealing with a hundred lines or so.
Most of the time is consumed by lines 4 and 8 (about half and half). The
real mystery is line 4. It's only dealing with 100 lines and it sometimes
takes a minute! It is always very fast or very slow.
Yesterday when I investigated it I found that the C drive, which contained
SQL Server, my main database and the temp database, was nearly full. So I
moved the databases to the D and E drives with lots of space and freed up a
bunch of space on C. But the problem continues today.
When testing, I can run the procedure over and over again, but it always
finishes in a couple of seconds. When I try to display an estimated
execution plan, it gives me "Invalid object name" errors on two of my temp
tables. How can I debug it?
Is this type of performance problem (and inability to debug) caused by the
use of temp tables? If so, I could rewrite it not to use them, but it will
require several UNIONS of similar queries, which seemed to me would take
longer.
I tried setting the transaction isolation level to "READ UNCOMMITTED" in
case the problem was somehow caused by blocking, but that didn't make any
difference.
Rick.It sounds like you may have disk or cpu bottlenecks but that's not a lot to
go on. Try running perfmon to see if you can spot what the disk and
processor queues are like when it's happening. You might also try using a
table variable instead of a temp table and see if it makes a difference.
--
Andrew J. Kelly
SQL Server MVP
"Rick Harrrison" <rick@.knowware.com> wrote in message
news:eJOY1Ze4DHA.1636@.TK2MSFTNGP12.phx.gbl...
> I am having a severe performance issue with a procedure, but it only
happens
> intermittently so it's difficult to track down. The procedure is too
> complex to include in this post because it uses nested views with lots of
> tables etc. Basically it does this:
> 1. create temp table 1 (fast)
> 2. insert query result into table 1 (100 lines or so)
> 3. create temp table 2
> 4. query temp table 1 and insert results into table 2
> 5. query temp table 1 again (different summary) and insert results
into
> table 2
> 6. query temp table 1 again (different summary) and insert results
into
> table 2
> 7. query temp table 1 again (different summary) and insert results
into
> table 2
> 8. update temp table 2
> 9. select * from temp table 2 as output from procedure
> Normally it runs very fast (a couple of seconds). When it's slow, it
takes
> 30 - 150 seconds even though it's only dealing with a hundred lines or so.
> Most of the time is consumed by lines 4 and 8 (about half and half). The
> real mystery is line 4. It's only dealing with 100 lines and it sometimes
> takes a minute! It is always very fast or very slow.
> Yesterday when I investigated it I found that the C drive, which contained
> SQL Server, my main database and the temp database, was nearly full. So I
> moved the databases to the D and E drives with lots of space and freed up
a
> bunch of space on C. But the problem continues today.
> When testing, I can run the procedure over and over again, but it always
> finishes in a couple of seconds. When I try to display an estimated
> execution plan, it gives me "Invalid object name" errors on two of my temp
> tables. How can I debug it?
> Is this type of performance problem (and inability to debug) caused by the
> use of temp tables? If so, I could rewrite it not to use them, but it
will
> require several UNIONS of similar queries, which seemed to me would take
> longer.
> I tried setting the transaction isolation level to "READ UNCOMMITTED" in
> case the problem was somehow caused by blocking, but that didn't make any
> difference.
> Rick.
>|||to see the estimated execution plan
first create the temp tables, then use display est. ..
(from same session)
or use show execution plan, which runs the query and shows
the execution plan
if you don't like the above, do as andrew suggested and
switch to table variables.
also, on the test system, run profiler, capturing
SP:Recompile, see how times your sp recompiles during
execution,
recompile set points in the advent of inserts to temp
tables are 6 rows, 500 rows, and every 20%.
table variables do not cause recompiles
you can also use the hint OPTION (KEEP PLAN) to inhibit 6
row recompile and (KEEP FIXED PLAN) to inhibit 500+ row
recompiles
>--Original Message--
>I am having a severe performance issue with a procedure,
but it only happens
>intermittently so it's difficult to track down. The
procedure is too
>complex to include in this post because it uses nested
views with lots of
>tables etc. Basically it does this:
> 1. create temp table 1 (fast)
> 2. insert query result into table 1 (100 lines or so)
> 3. create temp table 2
> 4. query temp table 1 and insert results into table 2
> 5. query temp table 1 again (different summary) and
insert results into
>table 2
> 6. query temp table 1 again (different summary) and
insert results into
>table 2
> 7. query temp table 1 again (different summary) and
insert results into
>table 2
> 8. update temp table 2
> 9. select * from temp table 2 as output from procedure
>Normally it runs very fast (a couple of seconds). When
it's slow, it takes
>30 - 150 seconds even though it's only dealing with a
hundred lines or so.
>Most of the time is consumed by lines 4 and 8 (about half
and half). The
>real mystery is line 4. It's only dealing with 100 lines
and it sometimes
>takes a minute! It is always very fast or very slow.
>Yesterday when I investigated it I found that the C
drive, which contained
>SQL Server, my main database and the temp database, was
nearly full. So I
>moved the databases to the D and E drives with lots of
space and freed up a
>bunch of space on C. But the problem continues today.
>When testing, I can run the procedure over and over
again, but it always
>finishes in a couple of seconds. When I try to display
an estimated
>execution plan, it gives me "Invalid object name" errors
on two of my temp
>tables. How can I debug it?
>Is this type of performance problem (and inability to
debug) caused by the
>use of temp tables? If so, I could rewrite it not to use
them, but it will
>require several UNIONS of similar queries, which seemed
to me would take
>longer.
>I tried setting the transaction isolation level to "READ
UNCOMMITTED" in
>case the problem was somehow caused by blocking, but that
didn't make any
>difference.
> Rick.
>
>.
>|||Hi Rick,
Thank you for using the Newsgroup and I am reviewing your post and want to
know if the community member's suggestions are helpful or if you still have
questions about it. Here I just want to provide you some more information
of troubleshooting the problem:
1) HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/?id=243589
2) HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/?id=298475
3)INF: Troubleshooting Stored Procedure Recompilation
http://support.microsoft.com/?id=243586
Hope this helps! If you still have any questios about it, please feel free
to post new message here and I am glad to help! Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.