performance monitoring: inserting

Showing posts with label inserting. Show all posts

Tuesday, March 20, 2012

Performance problem after sql2k sp4 installed

We'w got major performance problems with inserting data into large tables
(more than 25 000 000 rows) after installing the latest sp. The performance
decreased with more than 90%.
Our program takes one row at the time and creates inserts and updates for
aproximatly 7 tables out of this. Everyr read row is encapsuled in an
transaction.
occassionaly we can capture an error during insert. The error is 1222.
Have anybody encounted the same problem an even got any solution.
ThanksHi
1222 = "Lock request time out period exceeded."
Run profiler and see what is happening on your server during the loads.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Stefan W" wrote:
> We'w got major performance problems with inserting data into large tables
> (more than 25 000 000 rows) after installing the latest sp. The performance
> decreased with more than 90%.
> Our program takes one row at the time and creates inserts and updates for
> aproximatly 7 tables out of this. Everyr read row is encapsuled in an
> transaction.
>
> occassionaly we can capture an error during insert. The error is 1222.
>
> Have anybody encounted the same problem an even got any solution.
> Thanks|||Hi!
I've tried to do that, but i cant get information from the particular job.
The job is desiged as a stored procedure that contains a loop that calls
other stored procedures. Iv'e tried using the different templates provided in
the profiler but a don't se any of the underlying procedurecalls made in this
job. Do you have any suggestions on how to set up the profiler to get hand on
these jobs!
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> 1222 = "Lock request time out period exceeded."
> Run profiler and see what is happening on your server during the loads.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Stefan W" wrote:
> > We'w got major performance problems with inserting data into large tables
> > (more than 25 000 000 rows) after installing the latest sp. The performance
> > decreased with more than 90%.
> >
> > Our program takes one row at the time and creates inserts and updates for
> > aproximatly 7 tables out of this. Everyr read row is encapsuled in an
> > transaction.
> >
> >
> > occassionaly we can capture an error during insert. The error is 1222.
> >
> >
> > Have anybody encounted the same problem an even got any solution.
> >
> > Thanks|||I will suggest to roll back to SP3a.
We have have many problems with SP4 as many other people; check other posts
related to SP4 and you will follow my advice.
Cheers, Massimo
--
Software Engineer
TCF - Amsterdam (NL)
"Stefan W" wrote:
> Hi!
> I've tried to do that, but i cant get information from the particular job.
> The job is desiged as a stored procedure that contains a loop that calls
> other stored procedures. Iv'e tried using the different templates provided in
> the profiler but a don't se any of the underlying procedurecalls made in this
> job. Do you have any suggestions on how to set up the profiler to get hand on
> these jobs!
>
>
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > 1222 = "Lock request time out period exceeded."
> >
> > Run profiler and see what is happening on your server during the loads.
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> >
> >
> > "Stefan W" wrote:
> >
> > > We'w got major performance problems with inserting data into large tables
> > > (more than 25 000 000 rows) after installing the latest sp. The performance
> > > decreased with more than 90%.
> > >
> > > Our program takes one row at the time and creates inserts and updates for
> > > aproximatly 7 tables out of this. Everyr read row is encapsuled in an
> > > transaction.
> > >
> > >
> > > occassionaly we can capture an error during insert. The error is 1222.
> > >
> > >
> > > Have anybody encounted the same problem an even got any solution.
> > >
> > > Thanks|||Hi
Massimo, have you raised a case with Microsoft PSS? If not, you should.
There have been a few posts on performance issues, and they had mostly to do
with a change in the way data type precedence is handled in SP4. I have 78
servers that are on SP4 with 2040 hotfix and none exhibit performance
problems.
Looks to be very application specific.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Massimo" <Massimo@.discussions.microsoft.com> wrote in message
news:640255E5-1885-4210-B9A1-32641D9C17A6@.microsoft.com...
>I will suggest to roll back to SP3a.
> We have have many problems with SP4 as many other people; check other
> posts
> related to SP4 and you will follow my advice.
> Cheers, Massimo
> --
> Software Engineer
> TCF - Amsterdam (NL)
>
> "Stefan W" wrote:
>> Hi!
>> I've tried to do that, but i cant get information from the particular
>> job.
>> The job is desiged as a stored procedure that contains a loop that calls
>> other stored procedures. Iv'e tried using the different templates
>> provided in
>> the profiler but a don't se any of the underlying procedurecalls made in
>> this
>> job. Do you have any suggestions on how to set up the profiler to get
>> hand on
>> these jobs!
>>
>>
>> "Mike Epprecht (SQL MVP)" wrote:
>> > Hi
>> >
>> > 1222 = "Lock request time out period exceeded."
>> >
>> > Run profiler and see what is happening on your server during the loads.
>> >
>> > Regards
>> > --
>> > Mike Epprecht, Microsoft SQL Server MVP
>> > Zurich, Switzerland
>> >
>> > MVP Program: http://www.microsoft.com/mvp
>> >
>> > Blog: http://www.msmvps.com/epprecht/
>> >
>> >
>> >
>> > "Stefan W" wrote:
>> >
>> > > We'w got major performance problems with inserting data into large
>> > > tables
>> > > (more than 25 000 000 rows) after installing the latest sp. The
>> > > performance
>> > > decreased with more than 90%.
>> > >
>> > > Our program takes one row at the time and creates inserts and updates
>> > > for
>> > > aproximatly 7 tables out of this. Everyr read row is encapsuled in an
>> > > transaction.
>> > >
>> > >
>> > > occassionaly we can capture an error during insert. The error is
>> > > 1222.
>> > >
>> > >
>> > > Have anybody encounted the same problem an even got any solution.
>> > >
>> > > Thanks

Wednesday, March 7, 2012

Performance of a query

Hi All,

I am joining three tables each table has got about 1.5 million rows,selecting data from these three tables and inserting into a table,to avoid transaction log issues I am running the query in a batch of size 50,000 rows,it is taking about 5hrs to insert all the 1.5 millions rows.

All the columns in the where clause have proper indexes,I ran show plan for the query and it is using indexes properly and not doing any table scan.I updated the statistics for all the indexes also.

query looks some thing like this.

insert into table d (col1,col2,col3,........... )
values (a.col1,b.col2,c.col3 ................)
from a,b,c
where a.id = b.id
and a.id = c.id
and a.id in between @.minid and @.currid

The @.minid starts from 1 and @.currid starts from 50000 ,I am running this in a loop, in next iteration @.minid will become 50001 and currid 100,000 and so on.

Any idea why it is taking that much time?.

Thanks in advance,
bsr.where...
...and a.id in between @.minid and @.currid"a.id in between"? Try your copy/paste again...

Any idea why it is taking that much time?

Yes:...I am running this in a loop...

Use set-based operations instead of loops if you want efficiency.|||and a.id between @.minid and @.currid...

I posted sample query, actually it is nothing do with the syntax,some how query is running very slow..any ideas?

Thanks in advance
bsr|||It is because you are using loops rather than set-based processing. How many iterations are executed?|||How heavily indexed is the table receiving the inserts? Is it on a RAID-5 disk array? Are you carring blobs to the new table? How many other processes are running? Is blocking occuring on any of the three tables used to generate the source for the data?

maybe try putting the db into simple recovery mode, do the inserts, then put into full-mode and take a full backup. maybe drop all indexes on the receiving table, do the inserts, and add back the indexes. Maybe migrate to RAID 1+0. Can you use NOLOCK hints to extract data?

Without more information, or the results of a profile trace, you get general suggestions.|||Thanks a lot tomh53,

How heavily indexed is the table receiving the inserts?
A: Receiving table doesn't have any indexes,I am building a index after inserting the data.

Are you carring blobs to the new table?
A: No,it doesn't have any blobs (text or images columns).it is a small table with 20 columns,some of the columns are float and some are less than char(50).

How many other processes are running?
A: No other process was running at that time except this insert statement.A java program runs against this database but no process was running at that time since that was the first time I was setting up this new database.

Is blocking occuring on any of the three tables used to generate the source for the data?

A: No, since my insert statment was the only process running on those three tables there was no blocking or lock contention.

As I wrote earlier I ran show plan for this insert query it is using indexes properly, not doing any table scan.

Please advise.

Thanks in advance
bsr|||Hows your CPU/IO statistics during the 5 hours. I bet if you run "select * into d from a", it will use the same order of magnitude of time to complete.|||1. All id columns in your three tables must be added index
2. Do not need any loop.
3. check the excuting time for the query
Select a.col1,b.col2,c.col3 ................
from a,b,c
where a.id = b.id
and a.id = c.id
and a.id in between @.minid and @.currid

If excuting time is ok, change your script as the following

INSERT INTO(col1, col2, col3....)
Select a.col1,b.col2,c.col3 ................
from a,b,c
where a.id = b.id
and a.id = c.id
4. if still has the problem, try DTS data dump

Tuesday, March 20, 2012

Performance problem after sql2k sp4 installed

Performance problem after sql2k sp4 installed

Performance problem after sql2k sp4 installed

Wednesday, March 7, 2012

Performance of a query

performance monitoring

Blog Archive

About Me