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...
quote:
> I am having a severe performance issue with a procedure, but it only
happens
quote:
> 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
quote:
> table 2
> 6. query temp table 1 again (different summary) and insert results
into
quote:
> table 2
> 7. query temp table 1 again (different summary) and insert results
into
quote:
> 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
quote:
> 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
quote:
> 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
quote:|||Hi Rick,
> 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.
>
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.sql
No comments:
Post a Comment