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
Showing posts with label half. Show all posts
Showing posts with label half. Show all posts
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:
>
> 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.
Subscribe to:
Posts (Atom)