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.

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...

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:

---

> 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.sql

Interesting SQL problem : How to track movement history

Hello everyone,

There's an interesting SQL problem I've come across that I'm currently
banging my head against. Given the following table that contains item
location information populated every minute :

location_id date_created
=========== ============
5 2000-01-01 01:00 <-- Don't need
5 2000-01-01 01:01 <-- Don't need
5 2000-01-01 01:02 <-- Need
7 2000-01-01 01:03 <-- Don't need
7 2000-01-01 01:04 <-- Need
5 2000-01-01 01:05 <-- Need
2 2000-01-01 01:06 <-- Don't Need
2 2000-01-01 01:07 <-- Need
7 2000-01-01 01:08 <-- Need

how would you generate a result-set that returns the item's location
history *without* duplicating the same location if the item has been
sitting in the same room for a while. For example, the result set
should look like the following :

location_id date_created
=========== ============
5 2000-01-01 01:02
7 2000-01-01 01:04
5 2000-01-01 01:05
2 2000-01-01 01:07
7 2000-01-01 01:08

This is turning out to be a finger twister and I'm not sure if it
could be done in SQL; I may have to resort to writing a stored-proc.

Regards,

AnthonyIf you are using SQL Server 2005, you can do this

with cte(location_id,date_created,grp)
as (
select location_id,
date_created,
rank() over(partition by location_id order by date_created)
- rank() over(order by date_created)
from mytable)
select location_id,
max(date_created) as date_created
from cte
group by location_id,grp
order by max(date_created)|||On 20.04.2007 15:49, markc600@.hotmail.com wrote:

Quote:

Originally Posted by

If you are using SQL Server 2005, you can do this
>
with cte(location_id,date_created,grp)
as (
select location_id,
date_created,
rank() over(partition by location_id order by date_created)
- rank() over(order by date_created)
from mytable)
select location_id,
max(date_created) as date_created
from cte
group by location_id,grp
order by max(date_created)

I'd do

select location_id, max(date_created) date_created
from your_table
group by location_id
order by max(date_created)

Am I missing something?

robert|||Hello Robert,

That wouldn't work since it groups by location_id and would return the
following :

location_id date_created
=========== ============
5 2000-01-01 01:05
2 2000-01-01 01:07
7 2000-01-01 01:08

I am looking into Mark's solution as we speak...

Regards,

Anthony

On Apr 20, 9:59 am, Robert Klemme <shortcut...@.googlemail.comwrote:

Quote:

Originally Posted by

On 20.04.2007 15:49, markc...@.hotmail.com wrote:
>

Quote:

Originally Posted by

If you are using SQL Server 2005, you can do this

>

Quote:

Originally Posted by

with cte(location_id,date_created,grp)
as (
select location_id,
date_created,
rank() over(partition by location_id order by date_created)
- rank() over(order by date_created)
from mytable)
select location_id,
max(date_created) as date_created
from cte
group by location_id,grp
order by max(date_created)

>
I'd do
>
select location_id, max(date_created) date_created
from your_table
group by location_id
order by max(date_created)
>
Am I missing something?
>
robert

|||Yep. What if the item returns to a location that it once occupied? Your
solution would not pick up the previous history.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Robert Klemme" <shortcutter@.googlemail.comwrote in message
news:58rv9iF2ikhoqU1@.mid.individual.net...
On 20.04.2007 15:49, markc600@.hotmail.com wrote:

Quote:

Originally Posted by

If you are using SQL Server 2005, you can do this
>
with cte(location_id,date_created,grp)
as (
select location_id,
date_created,
rank() over(partition by location_id order by date_created)
- rank() over(order by date_created)
from mytable)
select location_id,
max(date_created) as date_created
from cte
group by location_id,grp
order by max(date_created)

I'd do

select location_id, max(date_created) date_created
from your_table
group by location_id
order by max(date_created)

Am I missing something?

robert|||On 20.04.2007 16:08, Tom Moreau wrote:

Quote:

Originally Posted by

Yep. What if the item returns to a location that it once occupied? Your
solution would not pick up the previous history.

Right, thanks Tom and Anthony for correcting me. Stupid me. I should
have spent few more CPU cycles on this. :-)

robert|||Hello Mark,

So fast on the trigger! What do you do, have a notification bell ring
whenever something is posted on this newsgroup? :P

Your solution worked perfectly... I'm used to regular ol' SQL and what
you've just shown me is amazing; I didn't know SQL had these
capabilities. rank, over and partition are new to me. Do you have any
book recommendations?

Regards,

Anthony

On Apr 20, 9:49 am, markc...@.hotmail.com wrote:

Quote:

Originally Posted by

If you are using SQL Server 2005, you can do this
>
with cte(location_id,date_created,grp)
as (
select location_id,
date_created,
rank() over(partition by location_id order by date_created)
- rank() over(order by date_created)
from mytable)
select location_id,
max(date_created) as date_created
from cte
group by location_id,grp
order by max(date_created)

|||www.insidetsql.com
--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Anthony Paul" <anthonypaulo@.gmail.comwrote in message
news:1177078960.725463.69600@.y80g2000hsf.googlegro ups.com...
Hello Mark,

So fast on the trigger! What do you do, have a notification bell ring
whenever something is posted on this newsgroup? :P

Your solution worked perfectly... I'm used to regular ol' SQL and what
you've just shown me is amazing; I didn't know SQL had these
capabilities. rank, over and partition are new to me. Do you have any
book recommendations?

Regards,

Anthony

On Apr 20, 9:49 am, markc...@.hotmail.com wrote:

Quote:

Originally Posted by

If you are using SQL Server 2005, you can do this
>
with cte(location_id,date_created,grp)
as (
select location_id,
date_created,
rank() over(partition by location_id order by date_created)
- rank() over(order by date_created)
from mytable)
select location_id,
max(date_created) as date_created
from cte
group by location_id,grp
order by max(date_created)

|||
"Anthony Paul" <anthonypaulo@.gmail.comwrote in message
news:1177078960.725463.69600@.y80g2000hsf.googlegro ups.com...

Quote:

Originally Posted by

Hello Mark,
>
So fast on the trigger! What do you do, have a notification bell ring
whenever something is posted on this newsgroup? :P
>

I can't speak for Mark, but some of us have way too much time on our hands.
;-)

Quote:

Originally Posted by

Your solution worked perfectly... I'm used to regular ol' SQL and what
you've just shown me is amazing; I didn't know SQL had these
capabilities. rank, over and partition are new to me. Do you have any
book recommendations?

Look for books by Itzak Ben-Gan. (Inside Microsoft SQL Server 2005; T-SQL
Querying covers this. it's a island/gap problem.)

I remember the first time I saw him demo those functions (they're new in SQL
2005).

Some pretty incredible stuff.

Quote:

Originally Posted by

>
Regards,
>
Anthony
>
On Apr 20, 9:49 am, markc...@.hotmail.com wrote:

Quote:

Originally Posted by

>If you are using SQL Server 2005, you can do this
>>
>with cte(location_id,date_created,grp)
>as (
>select location_id,
> date_created,
> rank() over(partition by location_id order by date_created)
> - rank() over(order by date_created)
>from mytable)
>select location_id,
> max(date_created) as date_created
>from cte
>group by location_id,grp
>order by max(date_created)

>
>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||The usual design error is to have only one time in a row to capture
when an event started, then do horrible self-joins to get the duration
of the status change. Let me use a history table for price changes.
The fact to store is that a price had a duration:

CREATE TABLE PriceHistory
(upc CHAR(13) NOT NULL
REFERENCES Inventory(upc),
start_date DATE NOT NULL,
end_date DATE, -- null means current
CHECK(start_date < end_date),
PRIMARY KEY (upc, start_date),
item_price DECIMAL (12,4) NOT NULL
CHECK (item_price 0.0000),
etc.);

You actually needs more checks to assure that the start date is at
00:00 and the end dates is at 23:59:59.999 Hrs. You then use a
BETWEEN predicate to get the appropriate price.

SELECT ..
FROM PriceHistory AS H, Orders AS O
WHERE O.sales_date BETWEEN H.start_date
AND COALESCE (end_date, CURRENT_TIMESTAMP);

It is also a good idea to have a VIEW with the current data:

CREATE VIEW CurrentPrices (..)
AS
SELECT ..
FROM PriceHistory
WHERE end_date IS NULL;

Look up the Rick Snodgrass book on Temporal Queries in SQL at the
University of Arizona website; it is a free download.