Hi.
SQL 2005 Std. Linked server to iSeries. I have run INSERT INTO <sql
table> SELECT * FROM [I].[SERIES].[TA].[BLE]
There are about 23mln records to copy. I's been running for the last
half an hour and I would like to check what the progress is. Is there
any way of doing this?
--
PLHow about below?
SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Piotr Lipski" <pl@.mibi.pl> wrote in message news:fimij9$56h$1@.news.onet.pl...
> Hi.
> SQL 2005 Std. Linked server to iSeries. I have run INSERT INTO <sql table> SELECT * FROM
> [I].[SERIES].[TA].[BLE]
> There are about 23mln records to copy. I's been running for the last half an hour and I would like
> to check what the progress is. Is there any way of doing this?
> --
> PL|||Tibor Karaszi wrote:
> How about below?
> SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
The result is zero. I guess that the whole import is being done through
some kind of temporary storage and enclosed in a transaction so it can
be tricky (impossible?) to see the progress.
Any other ideas?
--
PL|||Piotr,
I suppose that if you know the size of your data and any other update load
being placed on the database, you could track the size of your transaction
log to see how much it is growing as the data is imported.
DBCC SQLPERF(LogSpace)
So, assume:
23,000,000 rows at 50 bytes average size.
Indexes add about 20% in size. (Depends on your index definitions)
Add another 20% overhead for the log entries.
1,656,000,000 bytes of log space
These are definitely just made up numbers, but do your own calculation
(taking into account all the factors that I do not know) and maybe you can
guess. (Truth in Advertising: I have never tried to use this approach for
figuring out the 'progress bar'.)
RLF
"Piotr Lipski" <pl@.mibi.pl> wrote in message
news:fimklr$bj5$1@.news.onet.pl...
> Tibor Karaszi wrote:
>> How about below?
>> SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
> The result is zero. I guess that the whole import is being done through
> some kind of temporary storage and enclosed in a transaction so it can be
> tricky (impossible?) to see the progress.
> Any other ideas?
> --
> PL
Showing posts with label insert. Show all posts
Showing posts with label insert. Show all posts
Friday, March 30, 2012
long time process - what's the progress?
Hi.
SQL 2005 Std. Linked server to iSeries. I have run INSERT INTO <sql
table> SELECT * FROM [I].[SERIES].[TA].[BLE]
There are about 23mln records to copy. I's been running for the last
half an hour and I would like to check what the progress is. Is there
any way of doing this?
PLHow about below?
SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Piotr Lipski" <pl@.mibi.pl> wrote in message news:fimij9$56h$1@.news.onet.pl...n">
> Hi.
> SQL 2005 Std. Linked server to iSeries. I have run INSERT INTO <sql table>
SELECT * FROM
> [I].[SERIES].[TA].[BLE]
> There are about 23mln records to copy. I's been running for the last half
an hour and I would like
> to check what the progress is. Is there any way of doing this?
> --
> PL|||Tibor Karaszi wrote:
> How about below?
> SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
The result is zero. I guess that the whole import is being done through
some kind of temporary storage and enclosed in a transaction so it can
be tricky (impossible?) to see the progress.
Any other ideas?
PL|||Piotr,
I suppose that if you know the size of your data and any other update load
being placed on the database, you could track the size of your transaction
log to see how much it is growing as the data is imported.
DBCC SQLPERF(LogSpace)
So, assume:
23,000,000 rows at 50 bytes average size.
Indexes add about 20% in size. (Depends on your index definitions)
Add another 20% overhead for the log entries.
1,656,000,000 bytes of log space
These are definitely just made up numbers, but do your own calculation
(taking into account all the factors that I do not know) and maybe you can
guess. (Truth in Advertising: I have never tried to use this approach for
figuring out the 'progress bar'.)
RLF
"Piotr Lipski" <pl@.mibi.pl> wrote in message
news:fimklr$bj5$1@.news.onet.pl...
> Tibor Karaszi wrote:
> The result is zero. I guess that the whole import is being done through
> some kind of temporary storage and enclosed in a transaction so it can be
> tricky (impossible?) to see the progress.
> Any other ideas?
> --
> PL
SQL 2005 Std. Linked server to iSeries. I have run INSERT INTO <sql
table> SELECT * FROM [I].[SERIES].[TA].[BLE]
There are about 23mln records to copy. I's been running for the last
half an hour and I would like to check what the progress is. Is there
any way of doing this?
PLHow about below?
SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Piotr Lipski" <pl@.mibi.pl> wrote in message news:fimij9$56h$1@.news.onet.pl...n">
> Hi.
> SQL 2005 Std. Linked server to iSeries. I have run INSERT INTO <sql table>
SELECT * FROM
> [I].[SERIES].[TA].[BLE]
> There are about 23mln records to copy. I's been running for the last half
an hour and I would like
> to check what the progress is. Is there any way of doing this?
> --
> PL|||Tibor Karaszi wrote:
> How about below?
> SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
The result is zero. I guess that the whole import is being done through
some kind of temporary storage and enclosed in a transaction so it can
be tricky (impossible?) to see the progress.
Any other ideas?
PL|||Piotr,
I suppose that if you know the size of your data and any other update load
being placed on the database, you could track the size of your transaction
log to see how much it is growing as the data is imported.
DBCC SQLPERF(LogSpace)
So, assume:
23,000,000 rows at 50 bytes average size.
Indexes add about 20% in size. (Depends on your index definitions)
Add another 20% overhead for the log entries.
1,656,000,000 bytes of log space
These are definitely just made up numbers, but do your own calculation
(taking into account all the factors that I do not know) and maybe you can
guess. (Truth in Advertising: I have never tried to use this approach for
figuring out the 'progress bar'.)
RLF
"Piotr Lipski" <pl@.mibi.pl> wrote in message
news:fimklr$bj5$1@.news.onet.pl...
> Tibor Karaszi wrote:
> The result is zero. I guess that the whole import is being done through
> some kind of temporary storage and enclosed in a transaction so it can be
> tricky (impossible?) to see the progress.
> Any other ideas?
> --
> PL
long time process - what's the progress?
Hi.
SQL 2005 Std. Linked server to iSeries. I have run INSERT INTO <sql
table> SELECT * FROM [I].[SERIES].[TA].[BLE]
There are about 23mln records to copy. I's been running for the last
half an hour and I would like to check what the progress is. Is there
any way of doing this?
PL
Tibor Karaszi wrote:
> How about below?
> SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
The result is zero. I guess that the whole import is being done through
some kind of temporary storage and enclosed in a transaction so it can
be tricky (impossible?) to see the progress.
Any other ideas?
PL
|||Piotr,
I suppose that if you know the size of your data and any other update load
being placed on the database, you could track the size of your transaction
log to see how much it is growing as the data is imported.
DBCC SQLPERF(LogSpace)
So, assume:
23,000,000 rows at 50 bytes average size.
Indexes add about 20% in size. (Depends on your index definitions)
Add another 20% overhead for the log entries.
1,656,000,000 bytes of log space
These are definitely just made up numbers, but do your own calculation
(taking into account all the factors that I do not know) and maybe you can
guess. (Truth in Advertising: I have never tried to use this approach for
figuring out the 'progress bar'.)
RLF
"Piotr Lipski" <pl@.mibi.pl> wrote in message
news:fimklr$bj5$1@.news.onet.pl...
> Tibor Karaszi wrote:
> The result is zero. I guess that the whole import is being done through
> some kind of temporary storage and enclosed in a transaction so it can be
> tricky (impossible?) to see the progress.
> Any other ideas?
> --
> PL
SQL 2005 Std. Linked server to iSeries. I have run INSERT INTO <sql
table> SELECT * FROM [I].[SERIES].[TA].[BLE]
There are about 23mln records to copy. I's been running for the last
half an hour and I would like to check what the progress is. Is there
any way of doing this?
PL
Tibor Karaszi wrote:
> How about below?
> SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
The result is zero. I guess that the whole import is being done through
some kind of temporary storage and enclosed in a transaction so it can
be tricky (impossible?) to see the progress.
Any other ideas?
PL
|||Piotr,
I suppose that if you know the size of your data and any other update load
being placed on the database, you could track the size of your transaction
log to see how much it is growing as the data is imported.
DBCC SQLPERF(LogSpace)
So, assume:
23,000,000 rows at 50 bytes average size.
Indexes add about 20% in size. (Depends on your index definitions)
Add another 20% overhead for the log entries.
1,656,000,000 bytes of log space
These are definitely just made up numbers, but do your own calculation
(taking into account all the factors that I do not know) and maybe you can
guess. (Truth in Advertising: I have never tried to use this approach for
figuring out the 'progress bar'.)
RLF
"Piotr Lipski" <pl@.mibi.pl> wrote in message
news:fimklr$bj5$1@.news.onet.pl...
> Tibor Karaszi wrote:
> The result is zero. I guess that the whole import is being done through
> some kind of temporary storage and enclosed in a transaction so it can be
> tricky (impossible?) to see the progress.
> Any other ideas?
> --
> PL
Long text column not accepting
Hi DBAs,
I am very new in SQL server. I created a table where one column is varchar(8000). But when I am trying to insert value from enterprise manager this column cann't accept a long text value. I counted that its' capacity is 1012 charecters. I have tried a lot but don't know how to solve this. I really need help from you. Pls help.
Thanks in advance
Rajat RaychaudhuriI am under pressure , pls advice me soon|||Use Query Analyzer.|||Have you tried using INSERT statement from QA? What's the total length of your record?|||Thanx friends. From Query Analyzer it worked.
Rajat|||If you are new to this, one quick note. SQL Server's Row Byte size is limited somewhere right around 8k bytes for physical data stored in the database. So, if the table has other columns in it, you might be in for trouble. Switching a very large column to Text may save you some future hassle. Of course, it could cause you a little hassle right now. :)|||I respect people that appreciate the value of TEXT/IMAGE datatypes ;)|||A little hassle now? That's not near as much hassle as it could cause him down the road.
RANT OFF
Go ahead...your turn now.|||TEXT/NTEXT/IMAGE columns definitely have their place. They can do things that are otherwise impossible in SQL Server. I don't see them as substitutes for VARCHAR or NVARCHAR any more than I see DATETIME as a substitute... Under certain rigorous conditions any of them might work, but as a generic substitute they are poor choices.
-PatP|||Switching a very large column to Text
-----------
http://www.3-ibm.com|||Is this a question? And how large is "very large"?|||I think he means it's veeeeeeeeeeeeeeeery large.
I am very new in SQL server. I created a table where one column is varchar(8000). But when I am trying to insert value from enterprise manager this column cann't accept a long text value. I counted that its' capacity is 1012 charecters. I have tried a lot but don't know how to solve this. I really need help from you. Pls help.
Thanks in advance
Rajat RaychaudhuriI am under pressure , pls advice me soon|||Use Query Analyzer.|||Have you tried using INSERT statement from QA? What's the total length of your record?|||Thanx friends. From Query Analyzer it worked.
Rajat|||If you are new to this, one quick note. SQL Server's Row Byte size is limited somewhere right around 8k bytes for physical data stored in the database. So, if the table has other columns in it, you might be in for trouble. Switching a very large column to Text may save you some future hassle. Of course, it could cause you a little hassle right now. :)|||I respect people that appreciate the value of TEXT/IMAGE datatypes ;)|||A little hassle now? That's not near as much hassle as it could cause him down the road.
RANT OFF
Go ahead...your turn now.|||TEXT/NTEXT/IMAGE columns definitely have their place. They can do things that are otherwise impossible in SQL Server. I don't see them as substitutes for VARCHAR or NVARCHAR any more than I see DATETIME as a substitute... Under certain rigorous conditions any of them might work, but as a generic substitute they are poor choices.
-PatP|||Switching a very large column to Text
-----------
http://www.3-ibm.com|||Is this a question? And how large is "very large"?|||I think he means it's veeeeeeeeeeeeeeeery large.
Wednesday, March 28, 2012
Long query block insert?
Hi,
Have a question, I have a select query that I used for bcp out one of my MS
SQL 2000 DB table ( about 30 million of records) and it take more than 3 hrs
to finish(about 13 million record). It happen that when this bcp process is
running no record can be inserted to this table.
Isn't that the select will release lock after it read pass the data?
Thanks!
KH TanHi,
Generally for BCP OUT the table will not be locked and user can insert data.
Can you try using BCP OUT with -b (Batch size) option and see what happens.
Thanks
Hari
SQL Server MVP
"KH TAN" <KH TAN@.discussions.microsoft.com> wrote in message
news:A4F01220-E1A3-4329-B059-6807D824A7B4@.microsoft.com...
> Hi,
> Have a question, I have a select query that I used for bcp out one of my
> MS
> SQL 2000 DB table ( about 30 million of records) and it take more than 3
> hrs
> to finish(about 13 million record). It happen that when this bcp process
> is
> running no record can be inserted to this table.
> Isn't that the select will release lock after it read pass the data?
> Thanks!
> KH Tan|||Thanks!
I notice that not only the BCP Out having the problem, some other long
running query (select statement) also do the same. I do a dbcc dbreindex on
the table and rerun the query, everything seem comming back to normal..weird
!
Regards,
KH Tan.
"Hari Prasad" wrote:
> Hi,
> Generally for BCP OUT the table will not be locked and user can insert dat
a.
> Can you try using BCP OUT with -b (Batch size) option and see what happens
.
> Thanks
> Hari
> SQL Server MVP
> "KH TAN" <KH TAN@.discussions.microsoft.com> wrote in message
> news:A4F01220-E1A3-4329-B059-6807D824A7B4@.microsoft.com...
>
>|||Have you used sp_lock or select from sysprocesses to see where the blocking
might be?
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"KH TAN" <KHTAN@.discussions.microsoft.com> wrote in message
news:78B09111-07E0-4380-A8A8-45FA56FD93CC@.microsoft.com...
> Thanks!
> I notice that not only the BCP Out having the problem, some other long
> running query (select statement) also do the same. I do a dbcc dbreindex
on
> the table and rerun the query, everything seem comming back to
normal..weird![vbcol=seagreen]
> Regards,
> KH Tan.
>
> "Hari Prasad" wrote:
>
data.[vbcol=seagreen]
happens.[vbcol=seagreen]
my[vbcol=seagreen]
3[vbcol=seagreen]
process[vbcol=seagreen]
Have a question, I have a select query that I used for bcp out one of my MS
SQL 2000 DB table ( about 30 million of records) and it take more than 3 hrs
to finish(about 13 million record). It happen that when this bcp process is
running no record can be inserted to this table.
Isn't that the select will release lock after it read pass the data?
Thanks!
KH TanHi,
Generally for BCP OUT the table will not be locked and user can insert data.
Can you try using BCP OUT with -b (Batch size) option and see what happens.
Thanks
Hari
SQL Server MVP
"KH TAN" <KH TAN@.discussions.microsoft.com> wrote in message
news:A4F01220-E1A3-4329-B059-6807D824A7B4@.microsoft.com...
> Hi,
> Have a question, I have a select query that I used for bcp out one of my
> MS
> SQL 2000 DB table ( about 30 million of records) and it take more than 3
> hrs
> to finish(about 13 million record). It happen that when this bcp process
> is
> running no record can be inserted to this table.
> Isn't that the select will release lock after it read pass the data?
> Thanks!
> KH Tan|||Thanks!
I notice that not only the BCP Out having the problem, some other long
running query (select statement) also do the same. I do a dbcc dbreindex on
the table and rerun the query, everything seem comming back to normal..weird
!
Regards,
KH Tan.
"Hari Prasad" wrote:
> Hi,
> Generally for BCP OUT the table will not be locked and user can insert dat
a.
> Can you try using BCP OUT with -b (Batch size) option and see what happens
.
> Thanks
> Hari
> SQL Server MVP
> "KH TAN" <KH TAN@.discussions.microsoft.com> wrote in message
> news:A4F01220-E1A3-4329-B059-6807D824A7B4@.microsoft.com...
>
>|||Have you used sp_lock or select from sysprocesses to see where the blocking
might be?
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"KH TAN" <KHTAN@.discussions.microsoft.com> wrote in message
news:78B09111-07E0-4380-A8A8-45FA56FD93CC@.microsoft.com...
> Thanks!
> I notice that not only the BCP Out having the problem, some other long
> running query (select statement) also do the same. I do a dbcc dbreindex
on
> the table and rerun the query, everything seem comming back to
normal..weird![vbcol=seagreen]
> Regards,
> KH Tan.
>
> "Hari Prasad" wrote:
>
data.[vbcol=seagreen]
happens.[vbcol=seagreen]
my[vbcol=seagreen]
3[vbcol=seagreen]
process[vbcol=seagreen]
Long query block insert?
Hi,
Have a question, I have a select query that I used for bcp out one of my MS
SQL 2000 DB table ( about 30 million of records) and it take more than 3 hrs
to finish(about 13 million record). It happen that when this bcp process is
running no record can be inserted to this table.
Isn't that the select will release lock after it read pass the data?
Thanks!
KH TanHi,
Generally for BCP OUT the table will not be locked and user can insert data.
Can you try using BCP OUT with -b (Batch size) option and see what happens.
Thanks
Hari
SQL Server MVP
"KH TAN" <KH TAN@.discussions.microsoft.com> wrote in message
news:A4F01220-E1A3-4329-B059-6807D824A7B4@.microsoft.com...
> Hi,
> Have a question, I have a select query that I used for bcp out one of my
> MS
> SQL 2000 DB table ( about 30 million of records) and it take more than 3
> hrs
> to finish(about 13 million record). It happen that when this bcp process
> is
> running no record can be inserted to this table.
> Isn't that the select will release lock after it read pass the data?
> Thanks!
> KH Tan|||Thanks!
I notice that not only the BCP Out having the problem, some other long
running query (select statement) also do the same. I do a dbcc dbreindex on
the table and rerun the query, everything seem comming back to normal..weird!
Regards,
KH Tan.
"Hari Prasad" wrote:
> Hi,
> Generally for BCP OUT the table will not be locked and user can insert data.
> Can you try using BCP OUT with -b (Batch size) option and see what happens.
> Thanks
> Hari
> SQL Server MVP
> "KH TAN" <KH TAN@.discussions.microsoft.com> wrote in message
> news:A4F01220-E1A3-4329-B059-6807D824A7B4@.microsoft.com...
> > Hi,
> > Have a question, I have a select query that I used for bcp out one of my
> > MS
> > SQL 2000 DB table ( about 30 million of records) and it take more than 3
> > hrs
> > to finish(about 13 million record). It happen that when this bcp process
> > is
> > running no record can be inserted to this table.
> >
> > Isn't that the select will release lock after it read pass the data?
> >
> > Thanks!
> >
> > KH Tan
>
>|||Have you used sp_lock or select from sysprocesses to see where the blocking
might be?
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"KH TAN" <KHTAN@.discussions.microsoft.com> wrote in message
news:78B09111-07E0-4380-A8A8-45FA56FD93CC@.microsoft.com...
> Thanks!
> I notice that not only the BCP Out having the problem, some other long
> running query (select statement) also do the same. I do a dbcc dbreindex
on
> the table and rerun the query, everything seem comming back to
normal..weird!
> Regards,
> KH Tan.
>
> "Hari Prasad" wrote:
> > Hi,
> >
> > Generally for BCP OUT the table will not be locked and user can insert
data.
> > Can you try using BCP OUT with -b (Batch size) option and see what
happens.
> >
> > Thanks
> > Hari
> > SQL Server MVP
> >
> > "KH TAN" <KH TAN@.discussions.microsoft.com> wrote in message
> > news:A4F01220-E1A3-4329-B059-6807D824A7B4@.microsoft.com...
> > > Hi,
> > > Have a question, I have a select query that I used for bcp out one of
my
> > > MS
> > > SQL 2000 DB table ( about 30 million of records) and it take more than
3
> > > hrs
> > > to finish(about 13 million record). It happen that when this bcp
process
> > > is
> > > running no record can be inserted to this table.
> > >
> > > Isn't that the select will release lock after it read pass the data?
> > >
> > > Thanks!
> > >
> > > KH Tan
> >
> >
> >
Have a question, I have a select query that I used for bcp out one of my MS
SQL 2000 DB table ( about 30 million of records) and it take more than 3 hrs
to finish(about 13 million record). It happen that when this bcp process is
running no record can be inserted to this table.
Isn't that the select will release lock after it read pass the data?
Thanks!
KH TanHi,
Generally for BCP OUT the table will not be locked and user can insert data.
Can you try using BCP OUT with -b (Batch size) option and see what happens.
Thanks
Hari
SQL Server MVP
"KH TAN" <KH TAN@.discussions.microsoft.com> wrote in message
news:A4F01220-E1A3-4329-B059-6807D824A7B4@.microsoft.com...
> Hi,
> Have a question, I have a select query that I used for bcp out one of my
> MS
> SQL 2000 DB table ( about 30 million of records) and it take more than 3
> hrs
> to finish(about 13 million record). It happen that when this bcp process
> is
> running no record can be inserted to this table.
> Isn't that the select will release lock after it read pass the data?
> Thanks!
> KH Tan|||Thanks!
I notice that not only the BCP Out having the problem, some other long
running query (select statement) also do the same. I do a dbcc dbreindex on
the table and rerun the query, everything seem comming back to normal..weird!
Regards,
KH Tan.
"Hari Prasad" wrote:
> Hi,
> Generally for BCP OUT the table will not be locked and user can insert data.
> Can you try using BCP OUT with -b (Batch size) option and see what happens.
> Thanks
> Hari
> SQL Server MVP
> "KH TAN" <KH TAN@.discussions.microsoft.com> wrote in message
> news:A4F01220-E1A3-4329-B059-6807D824A7B4@.microsoft.com...
> > Hi,
> > Have a question, I have a select query that I used for bcp out one of my
> > MS
> > SQL 2000 DB table ( about 30 million of records) and it take more than 3
> > hrs
> > to finish(about 13 million record). It happen that when this bcp process
> > is
> > running no record can be inserted to this table.
> >
> > Isn't that the select will release lock after it read pass the data?
> >
> > Thanks!
> >
> > KH Tan
>
>|||Have you used sp_lock or select from sysprocesses to see where the blocking
might be?
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"KH TAN" <KHTAN@.discussions.microsoft.com> wrote in message
news:78B09111-07E0-4380-A8A8-45FA56FD93CC@.microsoft.com...
> Thanks!
> I notice that not only the BCP Out having the problem, some other long
> running query (select statement) also do the same. I do a dbcc dbreindex
on
> the table and rerun the query, everything seem comming back to
normal..weird!
> Regards,
> KH Tan.
>
> "Hari Prasad" wrote:
> > Hi,
> >
> > Generally for BCP OUT the table will not be locked and user can insert
data.
> > Can you try using BCP OUT with -b (Batch size) option and see what
happens.
> >
> > Thanks
> > Hari
> > SQL Server MVP
> >
> > "KH TAN" <KH TAN@.discussions.microsoft.com> wrote in message
> > news:A4F01220-E1A3-4329-B059-6807D824A7B4@.microsoft.com...
> > > Hi,
> > > Have a question, I have a select query that I used for bcp out one of
my
> > > MS
> > > SQL 2000 DB table ( about 30 million of records) and it take more than
3
> > > hrs
> > > to finish(about 13 million record). It happen that when this bcp
process
> > > is
> > > running no record can be inserted to this table.
> > >
> > > Isn't that the select will release lock after it read pass the data?
> > >
> > > Thanks!
> > >
> > > KH Tan
> >
> >
> >
Long query block insert?
Hi,
Have a question, I have a select query that I used for bcp out one of my MS
SQL 2000 DB table ( about 30 million of records) and it take more than 3 hrs
to finish(about 13 million record). It happen that when this bcp process is
running no record can be inserted to this table.
Isn't that the select will release lock after it read pass the data?
Thanks!
KH Tan
Hi,
Generally for BCP OUT the table will not be locked and user can insert data.
Can you try using BCP OUT with -b (Batch size) option and see what happens.
Thanks
Hari
SQL Server MVP
"KH TAN" <KH TAN@.discussions.microsoft.com> wrote in message
news:A4F01220-E1A3-4329-B059-6807D824A7B4@.microsoft.com...
> Hi,
> Have a question, I have a select query that I used for bcp out one of my
> MS
> SQL 2000 DB table ( about 30 million of records) and it take more than 3
> hrs
> to finish(about 13 million record). It happen that when this bcp process
> is
> running no record can be inserted to this table.
> Isn't that the select will release lock after it read pass the data?
> Thanks!
> KH Tan
|||Thanks!
I notice that not only the BCP Out having the problem, some other long
running query (select statement) also do the same. I do a dbcc dbreindex on
the table and rerun the query, everything seem comming back to normal..weird!
Regards,
KH Tan.
"Hari Prasad" wrote:
> Hi,
> Generally for BCP OUT the table will not be locked and user can insert data.
> Can you try using BCP OUT with -b (Batch size) option and see what happens.
> Thanks
> Hari
> SQL Server MVP
> "KH TAN" <KH TAN@.discussions.microsoft.com> wrote in message
> news:A4F01220-E1A3-4329-B059-6807D824A7B4@.microsoft.com...
>
>
|||Have you used sp_lock or select from sysprocesses to see where the blocking
might be?
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"KH TAN" <KHTAN@.discussions.microsoft.com> wrote in message
news:78B09111-07E0-4380-A8A8-45FA56FD93CC@.microsoft.com...
> Thanks!
> I notice that not only the BCP Out having the problem, some other long
> running query (select statement) also do the same. I do a dbcc dbreindex
on
> the table and rerun the query, everything seem comming back to
normal..weird![vbcol=seagreen]
> Regards,
> KH Tan.
>
> "Hari Prasad" wrote:
data.[vbcol=seagreen]
happens.[vbcol=seagreen]
my[vbcol=seagreen]
3[vbcol=seagreen]
process[vbcol=seagreen]
sql
Have a question, I have a select query that I used for bcp out one of my MS
SQL 2000 DB table ( about 30 million of records) and it take more than 3 hrs
to finish(about 13 million record). It happen that when this bcp process is
running no record can be inserted to this table.
Isn't that the select will release lock after it read pass the data?
Thanks!
KH Tan
Hi,
Generally for BCP OUT the table will not be locked and user can insert data.
Can you try using BCP OUT with -b (Batch size) option and see what happens.
Thanks
Hari
SQL Server MVP
"KH TAN" <KH TAN@.discussions.microsoft.com> wrote in message
news:A4F01220-E1A3-4329-B059-6807D824A7B4@.microsoft.com...
> Hi,
> Have a question, I have a select query that I used for bcp out one of my
> MS
> SQL 2000 DB table ( about 30 million of records) and it take more than 3
> hrs
> to finish(about 13 million record). It happen that when this bcp process
> is
> running no record can be inserted to this table.
> Isn't that the select will release lock after it read pass the data?
> Thanks!
> KH Tan
|||Thanks!
I notice that not only the BCP Out having the problem, some other long
running query (select statement) also do the same. I do a dbcc dbreindex on
the table and rerun the query, everything seem comming back to normal..weird!
Regards,
KH Tan.
"Hari Prasad" wrote:
> Hi,
> Generally for BCP OUT the table will not be locked and user can insert data.
> Can you try using BCP OUT with -b (Batch size) option and see what happens.
> Thanks
> Hari
> SQL Server MVP
> "KH TAN" <KH TAN@.discussions.microsoft.com> wrote in message
> news:A4F01220-E1A3-4329-B059-6807D824A7B4@.microsoft.com...
>
>
|||Have you used sp_lock or select from sysprocesses to see where the blocking
might be?
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"KH TAN" <KHTAN@.discussions.microsoft.com> wrote in message
news:78B09111-07E0-4380-A8A8-45FA56FD93CC@.microsoft.com...
> Thanks!
> I notice that not only the BCP Out having the problem, some other long
> running query (select statement) also do the same. I do a dbcc dbreindex
on
> the table and rerun the query, everything seem comming back to
normal..weird![vbcol=seagreen]
> Regards,
> KH Tan.
>
> "Hari Prasad" wrote:
data.[vbcol=seagreen]
happens.[vbcol=seagreen]
my[vbcol=seagreen]
3[vbcol=seagreen]
process[vbcol=seagreen]
sql
Monday, March 26, 2012
long insert time
I have a table with about half a million records.
Select queries run fine.
This test query I ran in QA took over 1 minute to run.
insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
What are some possible causes?Two possible causes are blocking and insert trigger(s) on the table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:OpY%23$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>|||Tibor Karaszi wrote:
> Two possible causes are blocking and insert trigger(s) on the table.
>
I don't have any triggers. Funny thing, I re-ran the query about 5
times. The first 4 times it took over a minute to perform. The 5th
time, it took 2 seconds. I performed it again and it only took 2 seconds.
Any idea for the change in performance?|||Blocking is still a possible reason.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi wrote:
> > Two possible causes are blocking and insert trigger(s) on the table.
> >
> I don't have any triggers. Funny thing, I re-ran the query about 5
> times. The first 4 times it took over a minute to perform. The 5th
> time, it took 2 seconds. I performed it again and it only took 2 seconds.
> Any idea for the change in performance?
>|||Another reason could be an autogrow kicking in...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:u5gzVvYQEHA.3596@.tk2msftngp13.phx.gbl...
> Blocking is still a possible reason.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl...
> > Tibor Karaszi wrote:
> >
> > > Two possible causes are blocking and insert trigger(s) on the table.
> > >
> >
> > I don't have any triggers. Funny thing, I re-ran the query about 5
> > times. The first 4 times it took over a minute to perform. The 5th
> > time, it took 2 seconds. I performed it again and it only took 2 seconds.
> >
> > Any idea for the change in performance?
> >
>|||Hi,
As Tiber pointed out this execution time diffence while inserting a record
will be due to Blocking. So to see the blocking:-
While you execute the insert statement, if it is delaying open a new query
analyzer window and execute SP_WHO command.
In the result look for the column BLK, In the normal case all the values
will be "0". If there is a block then a SPID will displayed in BLK field.
If blocked understand the statement that user( SPID - blocked ) is doing by
using the below command:-
DBCC INPUTBUFFER(SPID) -- Replace the SPID with the SPID blocked.
Thanks
Hari
MCDBA
"Won Lee" <noemail@.nospam.com> wrote in message
news:OpY#$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>|||Tibor Karaszi wrote:
> Another reason could be an autogrow kicking in...
>
OK. Looking up Blocking in Books Online.
Next time I have this issue, I will be write down the DB size to see if
the autogrow is part of the problem. Thanks.sql
Select queries run fine.
This test query I ran in QA took over 1 minute to run.
insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
What are some possible causes?Two possible causes are blocking and insert trigger(s) on the table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:OpY%23$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>|||Tibor Karaszi wrote:
> Two possible causes are blocking and insert trigger(s) on the table.
>
I don't have any triggers. Funny thing, I re-ran the query about 5
times. The first 4 times it took over a minute to perform. The 5th
time, it took 2 seconds. I performed it again and it only took 2 seconds.
Any idea for the change in performance?|||Blocking is still a possible reason.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi wrote:
> > Two possible causes are blocking and insert trigger(s) on the table.
> >
> I don't have any triggers. Funny thing, I re-ran the query about 5
> times. The first 4 times it took over a minute to perform. The 5th
> time, it took 2 seconds. I performed it again and it only took 2 seconds.
> Any idea for the change in performance?
>|||Another reason could be an autogrow kicking in...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:u5gzVvYQEHA.3596@.tk2msftngp13.phx.gbl...
> Blocking is still a possible reason.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl...
> > Tibor Karaszi wrote:
> >
> > > Two possible causes are blocking and insert trigger(s) on the table.
> > >
> >
> > I don't have any triggers. Funny thing, I re-ran the query about 5
> > times. The first 4 times it took over a minute to perform. The 5th
> > time, it took 2 seconds. I performed it again and it only took 2 seconds.
> >
> > Any idea for the change in performance?
> >
>|||Hi,
As Tiber pointed out this execution time diffence while inserting a record
will be due to Blocking. So to see the blocking:-
While you execute the insert statement, if it is delaying open a new query
analyzer window and execute SP_WHO command.
In the result look for the column BLK, In the normal case all the values
will be "0". If there is a block then a SPID will displayed in BLK field.
If blocked understand the statement that user( SPID - blocked ) is doing by
using the below command:-
DBCC INPUTBUFFER(SPID) -- Replace the SPID with the SPID blocked.
Thanks
Hari
MCDBA
"Won Lee" <noemail@.nospam.com> wrote in message
news:OpY#$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>|||Tibor Karaszi wrote:
> Another reason could be an autogrow kicking in...
>
OK. Looking up Blocking in Books Online.
Next time I have this issue, I will be write down the DB size to see if
the autogrow is part of the problem. Thanks.sql
long insert time
I have a table with about half a million records.
Select queries run fine.
This test query I ran in QA took over 1 minute to run.
insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
What are some possible causes?Two possible causes are blocking and insert trigger(s) on the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:OpY%23$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl
..
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>|||Tibor Karaszi wrote:
> Two possible causes are blocking and insert trigger(s) on the table.
>
I don't have any triggers. Funny thing, I re-ran the query about 5
times. The first 4 times it took over a minute to perform. The 5th
time, it took 2 seconds. I performed it again and it only took 2 seconds.
Any idea for the change in performance?|||Blocking is still a possible reason.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl
..
> Tibor Karaszi wrote:
>
> I don't have any triggers. Funny thing, I re-ran the query about 5
> times. The first 4 times it took over a minute to perform. The 5th
> time, it took 2 seconds. I performed it again and it only took 2 seconds.
> Any idea for the change in performance?
>|||Another reason could be an autogrow kicking in...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:u5gzVvYQEHA.3596@.tk2msftngp13.phx.gbl...
> Blocking is still a possible reason.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK
2MSFTNGP12.phx.gbl...
>|||Hi,
As Tiber pointed out this execution time diffence while inserting a record
will be due to Blocking. So to see the blocking:-
While you execute the insert statement, if it is delaying open a new query
analyzer window and execute SP_WHO command.
In the result look for the column BLK, In the normal case all the values
will be "0". If there is a block then a SPID will displayed in BLK field.
If blocked understand the statement that user( SPID - blocked ) is doing by
using the below command:-
DBCC INPUTBUFFER(SPID) -- Replace the SPID with the SPID blocked.
Thanks
Hari
MCDBA
"Won Lee" <noemail@.nospam.com> wrote in message
news:OpY#$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>|||Tibor Karaszi wrote:
> Another reason could be an autogrow kicking in...
>
OK. Looking up Blocking in Books Online.
Next time I have this issue, I will be write down the DB size to see if
the autogrow is part of the problem. Thanks.
Select queries run fine.
This test query I ran in QA took over 1 minute to run.
insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
What are some possible causes?Two possible causes are blocking and insert trigger(s) on the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:OpY%23$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl
..
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>|||Tibor Karaszi wrote:
> Two possible causes are blocking and insert trigger(s) on the table.
>
I don't have any triggers. Funny thing, I re-ran the query about 5
times. The first 4 times it took over a minute to perform. The 5th
time, it took 2 seconds. I performed it again and it only took 2 seconds.
Any idea for the change in performance?|||Blocking is still a possible reason.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl
..
> Tibor Karaszi wrote:
>
> I don't have any triggers. Funny thing, I re-ran the query about 5
> times. The first 4 times it took over a minute to perform. The 5th
> time, it took 2 seconds. I performed it again and it only took 2 seconds.
> Any idea for the change in performance?
>|||Another reason could be an autogrow kicking in...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:u5gzVvYQEHA.3596@.tk2msftngp13.phx.gbl...
> Blocking is still a possible reason.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK
2MSFTNGP12.phx.gbl...
>|||Hi,
As Tiber pointed out this execution time diffence while inserting a record
will be due to Blocking. So to see the blocking:-
While you execute the insert statement, if it is delaying open a new query
analyzer window and execute SP_WHO command.
In the result look for the column BLK, In the normal case all the values
will be "0". If there is a block then a SPID will displayed in BLK field.
If blocked understand the statement that user( SPID - blocked ) is doing by
using the below command:-
DBCC INPUTBUFFER(SPID) -- Replace the SPID with the SPID blocked.
Thanks
Hari
MCDBA
"Won Lee" <noemail@.nospam.com> wrote in message
news:OpY#$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>|||Tibor Karaszi wrote:
> Another reason could be an autogrow kicking in...
>
OK. Looking up Blocking in Books Online.
Next time I have this issue, I will be write down the DB size to see if
the autogrow is part of the problem. Thanks.
long insert time
I have a table with about half a million records.
Select queries run fine.
This test query I ran in QA took over 1 minute to run.
insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
What are some possible causes?
Two possible causes are blocking and insert trigger(s) on the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:OpY%23$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>
|||Tibor Karaszi wrote:
> Two possible causes are blocking and insert trigger(s) on the table.
>
I don't have any triggers. Funny thing, I re-ran the query about 5
times. The first 4 times it took over a minute to perform. The 5th
time, it took 2 seconds. I performed it again and it only took 2 seconds.
Any idea for the change in performance?
|||Blocking is still a possible reason.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi wrote:
>
> I don't have any triggers. Funny thing, I re-ran the query about 5
> times. The first 4 times it took over a minute to perform. The 5th
> time, it took 2 seconds. I performed it again and it only took 2 seconds.
> Any idea for the change in performance?
>
|||Another reason could be an autogrow kicking in...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:u5gzVvYQEHA.3596@.tk2msftngp13.phx.gbl...
> Blocking is still a possible reason.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl...
>
|||Hi,
As Tiber pointed out this execution time diffence while inserting a record
will be due to Blocking. So to see the blocking:-
While you execute the insert statement, if it is delaying open a new query
analyzer window and execute SP_WHO command.
In the result look for the column BLK, In the normal case all the values
will be "0". If there is a block then a SPID will displayed in BLK field.
If blocked understand the statement that user( SPID - blocked ) is doing by
using the below command:-
DBCC INPUTBUFFER(SPID) -- Replace the SPID with the SPID blocked.
Thanks
Hari
MCDBA
"Won Lee" <noemail@.nospam.com> wrote in message
news:OpY#$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>
|||Tibor Karaszi wrote:
> Another reason could be an autogrow kicking in...
>
OK. Looking up Blocking in Books Online.
Next time I have this issue, I will be write down the DB size to see if
the autogrow is part of the problem. Thanks.
Select queries run fine.
This test query I ran in QA took over 1 minute to run.
insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
What are some possible causes?
Two possible causes are blocking and insert trigger(s) on the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:OpY%23$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>
|||Tibor Karaszi wrote:
> Two possible causes are blocking and insert trigger(s) on the table.
>
I don't have any triggers. Funny thing, I re-ran the query about 5
times. The first 4 times it took over a minute to perform. The 5th
time, it took 2 seconds. I performed it again and it only took 2 seconds.
Any idea for the change in performance?
|||Blocking is still a possible reason.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi wrote:
>
> I don't have any triggers. Funny thing, I re-ran the query about 5
> times. The first 4 times it took over a minute to perform. The 5th
> time, it took 2 seconds. I performed it again and it only took 2 seconds.
> Any idea for the change in performance?
>
|||Another reason could be an autogrow kicking in...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:u5gzVvYQEHA.3596@.tk2msftngp13.phx.gbl...
> Blocking is still a possible reason.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl...
>
|||Hi,
As Tiber pointed out this execution time diffence while inserting a record
will be due to Blocking. So to see the blocking:-
While you execute the insert statement, if it is delaying open a new query
analyzer window and execute SP_WHO command.
In the result look for the column BLK, In the normal case all the values
will be "0". If there is a block then a SPID will displayed in BLK field.
If blocked understand the statement that user( SPID - blocked ) is doing by
using the below command:-
DBCC INPUTBUFFER(SPID) -- Replace the SPID with the SPID blocked.
Thanks
Hari
MCDBA
"Won Lee" <noemail@.nospam.com> wrote in message
news:OpY#$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>
|||Tibor Karaszi wrote:
> Another reason could be an autogrow kicking in...
>
OK. Looking up Blocking in Books Online.
Next time I have this issue, I will be write down the DB size to see if
the autogrow is part of the problem. Thanks.
Monday, February 20, 2012
Login not allowed to do bulkinsert?
Hello,
This statement fails in my app:
BULK INSERT [CASOPSA_TB042_INSTELLING_WERKGEVER] FROM
'D:\Temp\Xtb042_instelling_werkgever.TXT'
WITH (CODEPAGE = 'RAW',
DATAFILETYPE = 'char',
TABLOCK,
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\0',
MAXERRORS = 0)
with
Invalid object name 'CASOPSA_TB042_INSTELLING_WERKGEVER'
The app is logged in to the database as user DTA6User. DTA6User has access
to the database with roles 'public' and 'db_owner', he also has the server
role 'Bulk insert admin'.
The table was created from the same program logged in to the database as
DTA6User.
The code fails for all tables in the DB that were created this way.
In EM this user is listed as the Owner of all the tables.
The code also fails when run from QA, and there it makes no difference if I
am logged in as 'sa' or 'DTA6User'.
If I log in to QA as 'sa' and execute
EXEC sp_changeobjectowner
'dta6user. casopsa_tb042_instelling_werkgever','dbo
'
I *can* do the bulk insert in QA when logged in as 'sa', but not when logged
in as DTA6User.
The database originally came from a backup from another database, with other
logins. The DTA6User was created and assigned to the database after the
database restore. I notice that older tables that have not been created from
my program *can* be bulk inserted into from my app. These are listed in EM
as having owner 'dbo'.
(I essentially cloned both the program and the database to develop the next
version).
This has me all pretty
...
What can I do to solve this?
I recently upgraded SQL Server 2000 from no SP's to SP4.
Thanks in advance for any tips
JanJan Doggen,
Did you try qualifying the object with the owner?
BULK INSERT [DTA6User].[CASOPSA_TB042_INSTELLING_WERKGEVER] FROM
...
AMB
"Jan Doggen" wrote:
> Hello,
> This statement fails in my app:
> BULK INSERT [CASOPSA_TB042_INSTELLING_WERKGEVER] FROM
> 'D:\Temp\Xtb042_instelling_werkgever.TXT'
> WITH (CODEPAGE = 'RAW',
> DATAFILETYPE = 'char',
> TABLOCK,
> FIELDTERMINATOR = '\t',
> ROWTERMINATOR = '\0',
> MAXERRORS = 0)
> with
> Invalid object name 'CASOPSA_TB042_INSTELLING_WERKGEVER'
> The app is logged in to the database as user DTA6User. DTA6User has access
> to the database with roles 'public' and 'db_owner', he also has the server
> role 'Bulk insert admin'.
> The table was created from the same program logged in to the database as
> DTA6User.
> The code fails for all tables in the DB that were created this way.
> In EM this user is listed as the Owner of all the tables.
> The code also fails when run from QA, and there it makes no difference if
I
> am logged in as 'sa' or 'DTA6User'.
> If I log in to QA as 'sa' and execute
> EXEC sp_changeobjectowner
> 'dta6user. casopsa_tb042_instelling_werkgever','dbo
'
> I *can* do the bulk insert in QA when logged in as 'sa', but not when logg
ed
> in as DTA6User.
> The database originally came from a backup from another database, with oth
er
> logins. The DTA6User was created and assigned to the database after the
> database restore. I notice that older tables that have not been created fr
om
> my program *can* be bulk inserted into from my app. These are listed in EM
> as having owner 'dbo'.
> (I essentially cloned both the program and the database to develop the nex
t
> version).
> This has me all pretty...
> What can I do to solve this?
> I recently upgraded SQL Server 2000 from no SP's to SP4.
> Thanks in advance for any tips
> Jan
>
>|||Not *that* syntax ;-)
(I tried several)
In the meantime I have read up on when the owner of a table becomes the
login vs. 'dbo'.
I have decided to give DTA6User a sy
min rule too. Now everything works
fine.
Maybe not the best solution, but I have a delivery date coming...
Thanks for your help in refreshing my meory
jan
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:42601DF8-FBF3-41F4-A8D4-D54B5CCE0DB2@.microsoft.com...
> Jan Doggen,
> Did you try qualifying the object with the owner?
> BULK INSERT [DTA6User].[CASOPSA_TB042_INSTELLING_WERKGEVER] FROM
> ...
>
> AMB
> "Jan Doggen" wrote:
>
access
server
if I
logged
other
from
EM
next
This statement fails in my app:
BULK INSERT [CASOPSA_TB042_INSTELLING_WERKGEVER] FROM
'D:\Temp\Xtb042_instelling_werkgever.TXT'
WITH (CODEPAGE = 'RAW',
DATAFILETYPE = 'char',
TABLOCK,
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\0',
MAXERRORS = 0)
with
Invalid object name 'CASOPSA_TB042_INSTELLING_WERKGEVER'
The app is logged in to the database as user DTA6User. DTA6User has access
to the database with roles 'public' and 'db_owner', he also has the server
role 'Bulk insert admin'.
The table was created from the same program logged in to the database as
DTA6User.
The code fails for all tables in the DB that were created this way.
In EM this user is listed as the Owner of all the tables.
The code also fails when run from QA, and there it makes no difference if I
am logged in as 'sa' or 'DTA6User'.
If I log in to QA as 'sa' and execute
EXEC sp_changeobjectowner
'dta6user. casopsa_tb042_instelling_werkgever','dbo
'
I *can* do the bulk insert in QA when logged in as 'sa', but not when logged
in as DTA6User.
The database originally came from a backup from another database, with other
logins. The DTA6User was created and assigned to the database after the
database restore. I notice that older tables that have not been created from
my program *can* be bulk inserted into from my app. These are listed in EM
as having owner 'dbo'.
(I essentially cloned both the program and the database to develop the next
version).
This has me all pretty
...What can I do to solve this?
I recently upgraded SQL Server 2000 from no SP's to SP4.
Thanks in advance for any tips
JanJan Doggen,
Did you try qualifying the object with the owner?
BULK INSERT [DTA6User].[CASOPSA_TB042_INSTELLING_WERKGEVER] FROM
...
AMB
"Jan Doggen" wrote:
> Hello,
> This statement fails in my app:
> BULK INSERT [CASOPSA_TB042_INSTELLING_WERKGEVER] FROM
> 'D:\Temp\Xtb042_instelling_werkgever.TXT'
> WITH (CODEPAGE = 'RAW',
> DATAFILETYPE = 'char',
> TABLOCK,
> FIELDTERMINATOR = '\t',
> ROWTERMINATOR = '\0',
> MAXERRORS = 0)
> with
> Invalid object name 'CASOPSA_TB042_INSTELLING_WERKGEVER'
> The app is logged in to the database as user DTA6User. DTA6User has access
> to the database with roles 'public' and 'db_owner', he also has the server
> role 'Bulk insert admin'.
> The table was created from the same program logged in to the database as
> DTA6User.
> The code fails for all tables in the DB that were created this way.
> In EM this user is listed as the Owner of all the tables.
> The code also fails when run from QA, and there it makes no difference if
I
> am logged in as 'sa' or 'DTA6User'.
> If I log in to QA as 'sa' and execute
> EXEC sp_changeobjectowner
> 'dta6user. casopsa_tb042_instelling_werkgever','dbo
'
> I *can* do the bulk insert in QA when logged in as 'sa', but not when logg
ed
> in as DTA6User.
> The database originally came from a backup from another database, with oth
er
> logins. The DTA6User was created and assigned to the database after the
> database restore. I notice that older tables that have not been created fr
om
> my program *can* be bulk inserted into from my app. These are listed in EM
> as having owner 'dbo'.
> (I essentially cloned both the program and the database to develop the nex
t
> version).
> This has me all pretty
...> What can I do to solve this?
> I recently upgraded SQL Server 2000 from no SP's to SP4.
> Thanks in advance for any tips
> Jan
>
>|||Not *that* syntax ;-)
(I tried several)
In the meantime I have read up on when the owner of a table becomes the
login vs. 'dbo'.
I have decided to give DTA6User a sy
min rule too. Now everything worksfine.
Maybe not the best solution, but I have a delivery date coming...
Thanks for your help in refreshing my meory
jan
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:42601DF8-FBF3-41F4-A8D4-D54B5CCE0DB2@.microsoft.com...
> Jan Doggen,
> Did you try qualifying the object with the owner?
> BULK INSERT [DTA6User].[CASOPSA_TB042_INSTELLING_WERKGEVER] FROM
> ...
>
> AMB
> "Jan Doggen" wrote:
>
access
server
if I
logged
other
from
EM
next
Subscribe to:
Posts (Atom)