Inconsistent Performance of Insert

I am experiencing inconsistent performance when running
insert statements.
Our application automatically generates and sequentially
executes SQL statements that build a results table. After
loading data and running this process, all statements
(about 125 in total) will execute in about 10 minutes. If
it is necessary to rerun the statements I truncate the
results table and rerun the job. In some cases this
second job runs in 10 minutes, and in other cases it runs
6 hours. In particular, one statement takes an inordinate
amount of time.
I've used PROFILER (although I'm not too experienced in
this) and can see that when statements are running
normally the log file updates in the 1000+ per second
range, but with the problematic statement updates are in
the 10 per second range. These updates seem to appear
after the commit statement is executed.
In various combinations I've tried restarting SQLServer,
updating statistics, defragging the hard drive, using
query optimizer, all with no luck.
Any ideas?
Here's one of the statements that seems to get hung up:
Table AV has 4 columns including a Record ID (RID), a
record name (FID) and a record value (Value). This table
will ultimately have approximately 4 million rows at the
completion of the job.
Table A contains a Record ID (RID) and a Process ID
(PID). This table has approx. 100,000 records.
INSERT INTO AV(RID, FID, Value) SELECT A.RID, 325, ISNULL
(ZZTEMPA.Value, 0)+ISNULL(ZZTEMPB.Value, 0)+ISNULL
(ZZTEMPC.Value, 0)+ISNULL(ZZTEMPD.Value, 0)+ISNULL
(ZZTEMPE.Value, 0) FROM A LEFT JOIN AV AS ZZTEMPA ON A.RID
= ZZTEMPA.RID AND ZZTEMPA.FID = 496 LEFT JOIN AV AS
ZZTEMPB ON A.RID = ZZTEMPB.RID AND ZZTEMPB.FID = 497 LEFT
JOIN AV AS ZZTEMPC ON A.RID = ZZTEMPC.RID AND ZZTEMPC.FID
= 499 LEFT JOIN AV AS ZZTEMPD ON A.RID = ZZTEMPD.RID AND
ZZTEMPD.FID = 502 LEFT JOIN AV AS ZZTEMPE ON A.RID =
ZZTEMPE.RID AND ZZTEMPE.FID = 504 WHERE (A.PID IN (275,
276, 277, 278, 279, 280, 281))
I recognize that the same table AV is being joined
multiple times, but it's my understanding that this should
not be a problem.
Thanks for any guidance on this.
Not much to go on but have you looked to see if you are being blocked when
this is happening? How about disk and cpu queues?
Andrew J. Kelly SQL MVP
"Chuck Hardy" <chardy@.coreprofit.com> wrote in message
news:16c501c4a62e$f11bbd90$a601280a@.phx.gbl...
> I am experiencing inconsistent performance when running
> insert statements.
> Our application automatically generates and sequentially
> executes SQL statements that build a results table. After
> loading data and running this process, all statements
> (about 125 in total) will execute in about 10 minutes. If
> it is necessary to rerun the statements I truncate the
> results table and rerun the job. In some cases this
> second job runs in 10 minutes, and in other cases it runs
> 6 hours. In particular, one statement takes an inordinate
> amount of time.
> I've used PROFILER (although I'm not too experienced in
> this) and can see that when statements are running
> normally the log file updates in the 1000+ per second
> range, but with the problematic statement updates are in
> the 10 per second range. These updates seem to appear
> after the commit statement is executed.
> In various combinations I've tried restarting SQLServer,
> updating statistics, defragging the hard drive, using
> query optimizer, all with no luck.
> Any ideas?
> Here's one of the statements that seems to get hung up:
> Table AV has 4 columns including a Record ID (RID), a
> record name (FID) and a record value (Value). This table
> will ultimately have approximately 4 million rows at the
> completion of the job.
> Table A contains a Record ID (RID) and a Process ID
> (PID). This table has approx. 100,000 records.
> INSERT INTO AV(RID, FID, Value) SELECT A.RID, 325, ISNULL
> (ZZTEMPA.Value, 0)+ISNULL(ZZTEMPB.Value, 0)+ISNULL
> (ZZTEMPC.Value, 0)+ISNULL(ZZTEMPD.Value, 0)+ISNULL
> (ZZTEMPE.Value, 0) FROM A LEFT JOIN AV AS ZZTEMPA ON A.RID
> = ZZTEMPA.RID AND ZZTEMPA.FID = 496 LEFT JOIN AV AS
> ZZTEMPB ON A.RID = ZZTEMPB.RID AND ZZTEMPB.FID = 497 LEFT
> JOIN AV AS ZZTEMPC ON A.RID = ZZTEMPC.RID AND ZZTEMPC.FID
> = 499 LEFT JOIN AV AS ZZTEMPD ON A.RID = ZZTEMPD.RID AND
> ZZTEMPD.FID = 502 LEFT JOIN AV AS ZZTEMPE ON A.RID =
> ZZTEMPE.RID AND ZZTEMPE.FID = 504 WHERE (A.PID IN (275,
> 276, 277, 278, 279, 280, 281))
> I recognize that the same table AV is being joined
> multiple times, but it's my understanding that this should
> not be a problem.
> Thanks for any guidance on this.
>
|||Thanks for replying Andrew.
I did forget to mention: when this occurs CPU usage is 100% with minor
disk access every few seconds. There is never any blocking. These jobs
are being run on a dedicated server with a single user.
The odd thing is that the same data and system conditions produce wildly
different results. In between runs with the same data I truncate the
results table. Is there a possibility that the truncate doesn't manage
indexes the same as a delete would?
Chuck
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Not quite sure why sometimes it takes much longer but it may have something
to do with the query plans.. If you are starting with a small or empty
table the query plan will probably be a scan. As you continue to insert the
rows the scans will get longer and longer. Do you have proper indexes on
the table to ensure an efficient plan? If so then you may want to try and
use an Index hint(s) to force seeks rather than scans if necessary.
Normally I don't advocate using hints but since this is a controlled process
it should not be a problem if you know they are the correct thing to do.
Andrew J. Kelly SQL MVP
"Chuck Hardy" <chardy@.coreprofit.com> wrote in message
news:ueOQ7otpEHA.3244@.tk2msftngp13.phx.gbl...
> Thanks for replying Andrew.
> I did forget to mention: when this occurs CPU usage is 100% with minor
> disk access every few seconds. There is never any blocking. These jobs
> are being run on a dedicated server with a single user.
> The odd thing is that the same data and system conditions produce wildly
> different results. In between runs with the same data I truncate the
> results table. Is there a possibility that the truncate doesn't manage
> indexes the same as a delete would?
> Chuck
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||I've used query optimizer to create indexes as required.
You're right though, the results table starts empty and fills up at
about 50-100K records per each of the 125+ statements that get executed.
Unfortunately, I don't have a lot of control over the way the queries
are built as a 3rd party tool to us does that. I can, however, control
the execution of batches to some degree. Would it make sense to run 1
or 2 smaller batches before the batch that causes the problem?
Also, is it possible that the number of inserts is causing index
fragmentation? (I haven't run DBCC SHOWCONTIG on the results table.)
Would index frag cause 100% CPU spin? BTW, I don't see an appreciable
change in memory usage or paging while this condition exists.
Andrew, thanks for all of your help.
Chuck
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||What does the actual DDL for the insert table look like, especially the
Clustered index?
Andrew J. Kelly SQL MVP
"Chuck Hardy" <chardy@.coreprofit.com> wrote in message
news:%23teT4expEHA.3712@.TK2MSFTNGP15.phx.gbl...
> I've used query optimizer to create indexes as required.
> You're right though, the results table starts empty and fills up at
> about 50-100K records per each of the 125+ statements that get executed.
> Unfortunately, I don't have a lot of control over the way the queries
> are built as a 3rd party tool to us does that. I can, however, control
> the execution of batches to some degree. Would it make sense to run 1
> or 2 smaller batches before the batch that causes the problem?
> Also, is it possible that the number of inserts is causing index
> fragmentation? (I haven't run DBCC SHOWCONTIG on the results table.)
> Would index frag cause 100% CPU spin? BTW, I don't see an appreciable
> change in memory usage or paging while this condition exists.
> Andrew, thanks for all of your help.
> Chuck
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi Andrew,
I hope this is what you're looking for. I'd ask your guidance on the
fill factors, along with any other insights. Because each of the 125
SQL statements are inserting, (and I assume but am not sure) that not
all inserts are being placed at the end of the table perhaps the fill
factors should be decreased?
Thanks,
Chuck
/****** Object: Table [dbo].[AccountValues] Script Date: 10/1/2004
9:56:09 AM ******/
CREATE TABLE [dbo].[AccountValues] (
[AccountID] [int] NOT NULL ,
[IFormulaID] [int] NOT NULL ,
[FormulaValue] [decimal](30, 10) NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [AccountValues1] ON
[dbo].[AccountValues]([AccountID]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
CREATE INDEX [AccountValues3] ON [dbo].[AccountValues]([IFormulaID],
[AccountID]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi Andrew,
Two more things:
. The problem I'm experienceing is described almost exactly in MS
KB835864, except that I'm running a single CPU.
. I have noticed that the performance statistic Available Mbytes starts
high after a system reboot, but as I execute procedures or ad-hoc
queries this slowly decreases to 5-10Mb. Is there a way to make SQL
Server free up memory without stopping and restarting the server?
Thanks,
Chuck
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Sounds like you don't have a whole lot of memory to begin with. How much do
you have and are there other processes running on the server other than SQL
Server? You can use the MAX Memory setting to ensure the OS and other apps
always have some memory available.
Andrew J. Kelly SQL MVP
"Chuck Hardy" <chardy@.coreprofit.com> wrote in message
news:e0FxYv%23pEHA.3396@.tk2msftngp13.phx.gbl...
> Hi Andrew,
> Two more things:
> The problem I'm experienceing is described almost exactly in MS
> KB835864, except that I'm running a single CPU.
> I have noticed that the performance statistic Available Mbytes starts
> high after a system reboot, but as I execute procedures or ad-hoc
> queries this slowly decreases to 5-10Mb. Is there a way to make SQL
> Server free up memory without stopping and restarting the server?
> Thanks,
> Chuck
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Chuck,
Not sure if I understand exactly what you are trying to do with that query.
It seems as if you can get the desired result like this:
SELECT A.RID, 325, SUM(A.Value) AS Value
FROM A
WHERE A.PID IN (275, 276, 277, 278, 279, 280, 281)
AND a.FID IN (496,497,499,502,504
GROUP BY a.RID
In any case you need to determine what the query plan is that this is using
during the slow periods. You can run a trace that will show recompiles and
query plans to see if they change during the operation. You can also try
specifying hints to force the proper plans.
Andrew J. Kelly SQL MVP
"Chuck Hardy" <chardy@.coreprofit.com> wrote in message
news:eSP09s9pEHA.2696@.TK2MSFTNGP15.phx.gbl...
> Hi Andrew,
> I hope this is what you're looking for. I'd ask your guidance on the
> fill factors, along with any other insights. Because each of the 125
> SQL statements are inserting, (and I assume but am not sure) that not
> all inserts are being placed at the end of the table perhaps the fill
> factors should be decreased?
> Thanks,
> Chuck
> /****** Object: Table [dbo].[AccountValues] Script Date: 10/1/2004
> 9:56:09 AM ******/
> CREATE TABLE [dbo].[AccountValues] (
> [AccountID] [int] NOT NULL ,
> [IFormulaID] [int] NOT NULL ,
> [FormulaValue] [decimal](30, 10) NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [AccountValues1] ON
> [dbo].[AccountValues]([AccountID]) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
> CREATE INDEX [AccountValues3] ON [dbo].[AccountValues]([IFormulaID],
> [AccountID]) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!