Inserted Rows

Does anyone have any SP or script that can help me easily
determine the activity within my tables? Specifically, I
am looking for any kind of SPs, scripts, or tools that
can help me easily determine how heavily hit my various
tables are. For example is table A with 1,000,000 rows
in it not the heavily used while table B with 5,000 rows
in it is constantly being inserted to, deleted from, and
updated. I am trying to track down my heavy hitter
tables to do some P&T on them or move them to their own
files, etc.Z,
You can use SQL Profiler to track activity against a database. One data
column it can report is the ObjectName being referenced. Examine the
discussion in the BOL on SQL Profiler and SQL Trace.
Ideally, you would run the trace and spool its results to a file. Afterward
you can load the file into a table and do some queries to aggregate the
activity you are experiencing.
Running a trace will take some CPU from your server, but if you are
judicious in the events and data columns it should not be oppressive to the
server unless you are running at very high CPU levels already.
Russell Fields
http://www.sqlpass.org/
2004 PASS Community Summit - Orlando
- The largest user-event dedicated to SQL Server!
"Z" <anonymous@.discussions.microsoft.com> wrote in message
news:07a601c3af8f$d7d328f0$a001280a@.phx.gbl...
> Does anyone have any SP or script that can help me easily
> determine the activity within my tables? Specifically, I
> am looking for any kind of SPs, scripts, or tools that
> can help me easily determine how heavily hit my various
> tables are. For example is table A with 1,000,000 rows
> in it not the heavily used while table B with 5,000 rows
> in it is constantly being inserted to, deleted from, and
> updated. I am trying to track down my heavy hitter
> tables to do some P&T on them or move them to their own
> files, etc.
>

insert statement script/stored proc.

I have a table that has 3 columns that I need to split up the data in
each column and insert into another table as rows.
As well for each record inserted the Status is set to "O".
I am thinking I stored procedure would be the way to go? But trying to
determine the best way to go about this.
Below is an example of the table structures.
I have 2 options, delete all the data from table2 and do a bunch of
inserts, or perform updates?
I am assuming the first would be better but having trouble with the sql
statement.
Any ideas?
table1
ID Name1 Name2 CITY
500 John Jeff TO
501 Sheila Rose TO
502 Barb Jen TO
503 Tom Jerry TO
504 Alan Scott TO
505 Steve John TO
506 Pat Cathy TO
table2
ID Name Status
500 John O
500 Jeff O
501 Sheila O
501 Rose O
502 Barb O
502 Jen O
503 Tom O
503 Jerry O
504 Alan O
504 Scott O
505 Steve O
505 John O
506 Pat O
506 Cathy O
One way is to use an UNION like:
SELECT id, name1 AS "Name" FROM tbl
UNION
SELECT id, name2 AS "Name" FROM tbl
Another option is to use a CASE expression like:
SELECT id, CASE seq WHEN 1 THEN name1 ELSE name 2 END
FROM tbl, ( SELECT 1 UNION SELECT 2 ) D ( seq )
Make sure you have a composite key on ( id, name ) to prevent potential
duplication of names.
Anith
|||<pisquem@.hotmail.com> wrote in message
news:1160507284.442954.181820@.i3g2000cwc.googlegro ups.com...
>I have a table that has 3 columns that I need to split up the data in
> each column and insert into another table as rows.
> As well for each record inserted the Status is set to "O".
> I am thinking I stored procedure would be the way to go? But trying to
> determine the best way to go about this.
> Below is an example of the table structures.
> I have 2 options, delete all the data from table2 and do a bunch of
> inserts, or perform updates?
> I am assuming the first would be better but having trouble with the sql
> statement.
> Any ideas?
>
> table1
> ID Name1 Name2 CITY
> 500 John Jeff TO
> 501 Sheila Rose TO
> 502 Barb Jen TO
> 503 Tom Jerry TO
> 504 Alan Scott TO
> 505 Steve John TO
> 506 Pat Cathy TO
>
> table2
> ID Name Status
> 500 John O
> 500 Jeff O
> 501 Sheila O
> 501 Rose O
> 502 Barb O
> 502 Jen O
> 503 Tom O
> 503 Jerry O
> 504 Alan O
> 504 Scott O
> 505 Steve O
> 505 John O
> 506 Pat O
> 506 Cathy O
>
I might not be following, but this should do what you wish.
INSERT table2 (ID, Name, Status)
SELECT ID, Name1, 'O'
FROM Table1
UNION ALL
SELECT ID, Name2, 'O'
FROM Table1
Rick Sawtell
|||Thanks for the posts.
Which is the most effective and efficient way?
Rick Sawtell wrote:
> <pisquem@.hotmail.com> wrote in message
> news:1160507284.442954.181820@.i3g2000cwc.googlegro ups.com...
> I might not be following, but this should do what you wish.
> INSERT table2 (ID, Name, Status)
> SELECT ID, Name1, 'O'
> FROM Table1
> UNION ALL
> SELECT ID, Name2, 'O'
> FROM Table1
>
> Rick Sawtell
|||Darn, I forgot to include something...
In the second table I need to have another column inserted with the
value of either 001 or 002 depending on if its Name1 being inserted or
Name2. So the table should be outputed as follows.
ID Name Code Status
500 John 001 O
500 Jeff 002 O
501 Sheila 001 O
501 Rose 002 O
Any ideas?
pisq...@.hotmail.com wrote:[vbcol=seagreen]
> Thanks for the posts.
> Which is the most effective and efficient way?
>
> Rick Sawtell wrote:

insert statement script/stored proc.

I have a table that has 3 columns that I need to split up the data in
each column and insert into another table as rows.
As well for each record inserted the Status is set to "O".
I am thinking I stored procedure would be the way to go? But trying to
determine the best way to go about this.
Below is an example of the table structures.
I have 2 options, delete all the data from table2 and do a bunch of
inserts, or perform updates?
I am assuming the first would be better but having trouble with the sql
statement.
Any ideas?
table1
ID Name1 Name2 CITY
500 John Jeff TO
501 Sheila Rose TO
502 Barb Jen TO
503 Tom Jerry TO
504 Alan Scott TO
505 Steve John TO
506 Pat Cathy TO
table2
ID Name Status
500 John O
500 Jeff O
501 Sheila O
501 Rose O
502 Barb O
502 Jen O
503 Tom O
503 Jerry O
504 Alan O
504 Scott O
505 Steve O
505 John O
506 Pat O
506 Cathy OOne way is to use an UNION like:
SELECT id, name1 AS "Name" FROM tbl
UNION
SELECT id, name2 AS "Name" FROM tbl
Another option is to use a CASE expression like:
SELECT id, CASE seq WHEN 1 THEN name1 ELSE name 2 END
FROM tbl, ( SELECT 1 UNION SELECT 2 ) D ( seq )
Make sure you have a composite key on ( id, name ) to prevent potential
duplication of names.
--
Anith|||<pisquem@.hotmail.com> wrote in message
news:1160507284.442954.181820@.i3g2000cwc.googlegroups.com...
>I have a table that has 3 columns that I need to split up the data in
> each column and insert into another table as rows.
> As well for each record inserted the Status is set to "O".
> I am thinking I stored procedure would be the way to go? But trying to
> determine the best way to go about this.
> Below is an example of the table structures.
> I have 2 options, delete all the data from table2 and do a bunch of
> inserts, or perform updates?
> I am assuming the first would be better but having trouble with the sql
> statement.
> Any ideas?
>
> table1
> ID Name1 Name2 CITY
> 500 John Jeff TO
> 501 Sheila Rose TO
> 502 Barb Jen TO
> 503 Tom Jerry TO
> 504 Alan Scott TO
> 505 Steve John TO
> 506 Pat Cathy TO
>
> table2
> ID Name Status
> 500 John O
> 500 Jeff O
> 501 Sheila O
> 501 Rose O
> 502 Barb O
> 502 Jen O
> 503 Tom O
> 503 Jerry O
> 504 Alan O
> 504 Scott O
> 505 Steve O
> 505 John O
> 506 Pat O
> 506 Cathy O
>
I might not be following, but this should do what you wish.
INSERT table2 (ID, Name, Status)
SELECT ID, Name1, 'O'
FROM Table1
UNION ALL
SELECT ID, Name2, 'O'
FROM Table1
Rick Sawtell|||Thanks for the posts.
Which is the most effective and efficient way?
Rick Sawtell wrote:
> <pisquem@.hotmail.com> wrote in message
> news:1160507284.442954.181820@.i3g2000cwc.googlegroups.com...
> >I have a table that has 3 columns that I need to split up the data in
> > each column and insert into another table as rows.
> > As well for each record inserted the Status is set to "O".
> > I am thinking I stored procedure would be the way to go? But trying to
> > determine the best way to go about this.
> > Below is an example of the table structures.
> > I have 2 options, delete all the data from table2 and do a bunch of
> > inserts, or perform updates?
> > I am assuming the first would be better but having trouble with the sql
> > statement.
> > Any ideas?
> >
> >
> > table1
> >
> > ID Name1 Name2 CITY
> > 500 John Jeff TO
> > 501 Sheila Rose TO
> > 502 Barb Jen TO
> > 503 Tom Jerry TO
> > 504 Alan Scott TO
> > 505 Steve John TO
> > 506 Pat Cathy TO
> >
> >
> >
> > table2
> >
> > ID Name Status
> > 500 John O
> > 500 Jeff O
> > 501 Sheila O
> > 501 Rose O
> > 502 Barb O
> > 502 Jen O
> > 503 Tom O
> > 503 Jerry O
> > 504 Alan O
> > 504 Scott O
> > 505 Steve O
> > 505 John O
> > 506 Pat O
> > 506 Cathy O
> >
> I might not be following, but this should do what you wish.
> INSERT table2 (ID, Name, Status)
> SELECT ID, Name1, 'O'
> FROM Table1
> UNION ALL
> SELECT ID, Name2, 'O'
> FROM Table1
>
> Rick Sawtell|||Darn, I forgot to include something...
In the second table I need to have another column inserted with the
value of either 001 or 002 depending on if its Name1 being inserted or
Name2. So the table should be outputed as follows.
ID Name Code Status
500 John 001 O
500 Jeff 002 O
501 Sheila 001 O
501 Rose 002 O
Any ideas?
pisq...@.hotmail.com wrote:
> Thanks for the posts.
> Which is the most effective and efficient way?
>
> Rick Sawtell wrote:
> > <pisquem@.hotmail.com> wrote in message
> > news:1160507284.442954.181820@.i3g2000cwc.googlegroups.com...
> > >I have a table that has 3 columns that I need to split up the data in
> > > each column and insert into another table as rows.
> > > As well for each record inserted the Status is set to "O".
> > > I am thinking I stored procedure would be the way to go? But trying to
> > > determine the best way to go about this.
> > > Below is an example of the table structures.
> > > I have 2 options, delete all the data from table2 and do a bunch of
> > > inserts, or perform updates?
> > > I am assuming the first would be better but having trouble with the sql
> > > statement.
> > > Any ideas?
> > >
> > >
> > > table1
> > >
> > > ID Name1 Name2 CITY
> > > 500 John Jeff TO
> > > 501 Sheila Rose TO
> > > 502 Barb Jen TO
> > > 503 Tom Jerry TO
> > > 504 Alan Scott TO
> > > 505 Steve John TO
> > > 506 Pat Cathy TO
> > >
> > >
> > >
> > > table2
> > >
> > > ID Name Status
> > > 500 John O
> > > 500 Jeff O
> > > 501 Sheila O
> > > 501 Rose O
> > > 502 Barb O
> > > 502 Jen O
> > > 503 Tom O
> > > 503 Jerry O
> > > 504 Alan O
> > > 504 Scott O
> > > 505 Steve O
> > > 505 John O
> > > 506 Pat O
> > > 506 Cathy O
> > >
> >
> > I might not be following, but this should do what you wish.
> >
> > INSERT table2 (ID, Name, Status)
> > SELECT ID, Name1, 'O'
> > FROM Table1
> > UNION ALL
> > SELECT ID, Name2, 'O'
> > FROM Table1
> >
> >
> > Rick Sawtell

insert statement script/stored proc.

I have a table that has 3 columns that I need to split up the data in
each column and insert into another table as rows.
As well for each record inserted the Status is set to "O".
I am thinking I stored procedure would be the way to go? But trying to
determine the best way to go about this.
Below is an example of the table structures.
I have 2 options, delete all the data from table2 and do a bunch of
inserts, or perform updates?
I am assuming the first would be better but having trouble with the sql
statement.
Any ideas?
table1
ID Name1 Name2 CITY
500 John Jeff TO
501 Sheila Rose TO
502 Barb Jen TO
503 Tom Jerry TO
504 Alan Scott TO
505 Steve John TO
506 Pat Cathy TO
table2
ID Name Status
500 John O
500 Jeff O
501 Sheila O
501 Rose O
502 Barb O
502 Jen O
503 Tom O
503 Jerry O
504 Alan O
504 Scott O
505 Steve O
505 John O
506 Pat O
506 Cathy OOne way is to use an UNION like:
SELECT id, name1 AS "Name" FROM tbl
UNION
SELECT id, name2 AS "Name" FROM tbl
Another option is to use a CASE expression like:
SELECT id, CASE seq WHEN 1 THEN name1 ELSE name 2 END
FROM tbl, ( SELECT 1 UNION SELECT 2 ) D ( seq )
Make sure you have a composite key on ( id, name ) to prevent potential
duplication of names.
Anith|||<pisquem@.hotmail.com> wrote in message
news:1160507284.442954.181820@.i3g2000cwc.googlegroups.com...
>I have a table that has 3 columns that I need to split up the data in
> each column and insert into another table as rows.
> As well for each record inserted the Status is set to "O".
> I am thinking I stored procedure would be the way to go? But trying to
> determine the best way to go about this.
> Below is an example of the table structures.
> I have 2 options, delete all the data from table2 and do a bunch of
> inserts, or perform updates?
> I am assuming the first would be better but having trouble with the sql
> statement.
> Any ideas?
>
> table1
> ID Name1 Name2 CITY
> 500 John Jeff TO
> 501 Sheila Rose TO
> 502 Barb Jen TO
> 503 Tom Jerry TO
> 504 Alan Scott TO
> 505 Steve John TO
> 506 Pat Cathy TO
>
> table2
> ID Name Status
> 500 John O
> 500 Jeff O
> 501 Sheila O
> 501 Rose O
> 502 Barb O
> 502 Jen O
> 503 Tom O
> 503 Jerry O
> 504 Alan O
> 504 Scott O
> 505 Steve O
> 505 John O
> 506 Pat O
> 506 Cathy O
>
I might not be following, but this should do what you wish.
INSERT table2 (ID, Name, Status)
SELECT ID, Name1, 'O'
FROM Table1
UNION ALL
SELECT ID, Name2, 'O'
FROM Table1
Rick Sawtell|||Thanks for the posts.
Which is the most effective and efficient way?
Rick Sawtell wrote:
> <pisquem@.hotmail.com> wrote in message
> news:1160507284.442954.181820@.i3g2000cwc.googlegroups.com...
> I might not be following, but this should do what you wish.
> INSERT table2 (ID, Name, Status)
> SELECT ID, Name1, 'O'
> FROM Table1
> UNION ALL
> SELECT ID, Name2, 'O'
> FROM Table1
>
> Rick Sawtell|||Darn, I forgot to include something...
In the second table I need to have another column inserted with the
value of either 001 or 002 depending on if its Name1 being inserted or
Name2. So the table should be outputed as follows.
ID Name Code Status
500 John 001 O
500 Jeff 002 O
501 Sheila 001 O
501 Rose 002 O
Any ideas?
pisq...@.hotmail.com wrote:[vbcol=seagreen]
> Thanks for the posts.
> Which is the most effective and efficient way?
>
> Rick Sawtell wrote:

Insert scripts

I'd like to generate a script which contains insert commands (with full name
of columns and their values) out of the data in one of my tables something
like this:
Insert TBTest(Col1,Col2) Values ('1',2)
I have seen couple of tools which create insert into select .... sort of
command which is not the one that I want.
Thankshttp://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"J-T" <J-T@.nospam.com> wrote in message news:eRp9yq8jFHA.3164@.TK2MSFTNGP15.phx.gbl...[color
=darkred]
> I'd like to generate a script which contains insert commands (with full na
me
> of columns and their values) out of the data in one of my tables something
> like this:
> Insert TBTest(Col1,Col2) Values ('1',2)
> I have seen couple of tools which create insert into select .... sort of
> command which is not the one that I want.
> Thanks
>[/color]|||Try my stored procedure:
http://vyaskn.tripod.com/code.htm#inserts
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"J-T" <J-T@.nospam.com> wrote in message
news:eRp9yq8jFHA.3164@.TK2MSFTNGP15.phx.gbl...
> I'd like to generate a script which contains insert commands (with full
> name
> of columns and their values) out of the data in one of my tables something
> like this:
> Insert TBTest(Col1,Col2) Values ('1',2)
> I have seen couple of tools which create insert into select .... sort of
> command which is not the one that I want.
> Thanks
>|||Try www.sqlscripter.com
Its free
"J-T" wrote:

> I'd like to generate a script which contains insert commands (with full na
me
> of columns and their values) out of the data in one of my tables something
> like this:
> Insert TBTest(Col1,Col2) Values ('1',2)
> I have seen couple of tools which create insert into select .... sort of
> command which is not the one that I want.
> Thanks
>
>

Insert Script from one table to another

I have a table in the SQL Server 2005 database that I want to copy 5 columns from one table into another table. I wanted to create a script to do this operation so I can use this on another database that would have different values for these columns.

If I were to use something like this:

DECLARE @.pdtno smallint,

@.publyear smallint,

@.issord int,

@.pdtrnno smallint,

@.layout varchar(60)

code to select the values from the original table and put the column values into variables

code to loop through each record

INSERT INTO layout_temp(pdtno, publyear, issord, pdtrnno, layout)

VALUES (@.pdtno, @.publyear, @.issord, @.pdtrnno, @.layout)

Does this make sense? How would I code the script to go through each record and put the column values from the original table into the variables then insert these into the copy of the original table?

Thanks in advance

? Why not just do: INSERT INTO layout_temp(pdtno, publyear, issord, pdtrnno, layout) SELECT pdtno, publyear, issord, pdtrnno, layout FROM layout-- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <nailers67@.discussions..microsoft.com> wrote in message news:06652218-e226-4a6e-98de-b9046345ef27@.discussions.microsoft.com... I have a table in the SQL Server 2005 database that I want to copy 5 columns from one table into another table. I wanted to create a script to do this operation so I can use this on another database that would have different values for these columns. If I were to use something like this: DECLARE @.pdtno smallint, @.publyear smallint, @.issord int, @.pdtrnno smallint, @.layout varchar(60) code to select the values from the original table and put the column values into variables code to loop through each record INSERT INTO layout_temp(pdtno, publyear, issord, pdtrnno, layout) VALUES (@.pdtno, @.publyear, @.issord, @.pdtrnno, @.layout) Does this make sense? How would I code the script to go through each record and put the column values from the original table into the variables then insert these into the copy of the original table? Thanks in advance|||

I believe that was a stupid question on my part. I think I tried to make it too difficult. That worked well. Thank you for your help. I appreciate it.

Insert Script

Hi,
I have a table with some data in it, I would like to
generate one Insert script command for each record.
Is there a SQL Server tools for doing it?
I mean, this is my table:
NamesTbl
Name Surname
--
Filippo Bettinaglio
John Gill
Nick ....
the script genereted should be:
INSERT INTO NamesTbl (Name, Surname) VALUES
('Filippo', 'Bettinaglio')
INSERT INTO NamesTbl (Name, Surname) VALUES
('John', 'Gill')
...
thanks,
FilippoThere's a script here to do that:
http://vyaskn.tripod.com/code.htm
"Filippo Bettinaglio" <anonymous@.discussions.microsoft.com> wrote in message
news:0c6501c4b510$8fcda620$a501280a@.phx.gbl...
> Hi,
> I have a table with some data in it, I would like to
> generate one Insert script command for each record.
> Is there a SQL Server tools for doing it?
> I mean, this is my table:
> NamesTbl
> Name Surname
> --
> Filippo Bettinaglio
> John Gill
> Nick ....
>
> the script genereted should be:
> INSERT INTO NamesTbl (Name, Surname) VALUES
> ('Filippo', 'Bettinaglio')
> INSERT INTO NamesTbl (Name, Surname) VALUES
> ('John', 'Gill')
> ...
> thanks,
> Filippo
>

Insert Script

Hi,
I have a table with some data in it, I would like to
generate one Insert script command for each record.
Is there a SQL Server tools for doing it?
I mean, this is my table:
NamesTbl
Name Surname
Filippo Bettinaglio
John Gill
Nick ....
the script genereted should be:
INSERT INTO NamesTbl (Name, Surname) VALUES
('Filippo', 'Bettinaglio')
INSERT INTO NamesTbl (Name, Surname) VALUES
('John', 'Gill')
....
thanks,
Filippo
There's a script here to do that:
http://vyaskn.tripod.com/code.htm
"Filippo Bettinaglio" <anonymous@.discussions.microsoft.com> wrote in message
news:0c6501c4b510$8fcda620$a501280a@.phx.gbl...
> Hi,
> I have a table with some data in it, I would like to
> generate one Insert script command for each record.
> Is there a SQL Server tools for doing it?
> I mean, this is my table:
> NamesTbl
> Name Surname
> --
> Filippo Bettinaglio
> John Gill
> Nick ....
>
> the script genereted should be:
> INSERT INTO NamesTbl (Name, Surname) VALUES
> ('Filippo', 'Bettinaglio')
> INSERT INTO NamesTbl (Name, Surname) VALUES
> ('John', 'Gill')
> ...
> thanks,
> Filippo
>

insert performance varies

hi. i'm no dba and wouldn't mind a few pointers for investigation of an issue
i have with sql server 2000 sp3. i am running a script within query analyzer.
the batch includes hundreds of deletes followed by hundreds of inserts, all
within a single transaction.
i have noticed that running the batch multiple times results in widely
varying run times (between 9 and 100 seconds). however, the time taken to do
the deletes never varies (always around 2.5 seconds). how might i begin
investigating this? assuming no other queries are being run against the
database, is there anything sql server might be doing in the background which
might cause this.
note one of my colleagues found this link, which sounds similar to our
problem. unfortunately applying sp4 is out of the question at the moment.
http://support.microsoft.com/kb/835864
many thanks
khA wild guess is that autogrow for the log file (or even database file) kicks in when it is slow.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"kh" <kh@.nospam.nospam> wrote in message news:50CF0E7F-3653-4600-998E-90323DF73F5C@.microsoft.com...
> hi. i'm no dba and wouldn't mind a few pointers for investigation of an issue
> i have with sql server 2000 sp3. i am running a script within query analyzer.
> the batch includes hundreds of deletes followed by hundreds of inserts, all
> within a single transaction.
> i have noticed that running the batch multiple times results in widely
> varying run times (between 9 and 100 seconds). however, the time taken to do
> the deletes never varies (always around 2.5 seconds). how might i begin
> investigating this? assuming no other queries are being run against the
> database, is there anything sql server might be doing in the background which
> might cause this.
> note one of my colleagues found this link, which sounds similar to our
> problem. unfortunately applying sp4 is out of the question at the moment.
> http://support.microsoft.com/kb/835864
> many thanks
> kh
>|||Hi,
I understand the the the delay only occurs for insert operations. As you
said, 835864 shall be a possible cause if you are use multi-processes or
hyperthread CPUs. If it is the cause, I'm afraid that install latest SP4
when possible shall be the only fix.
You may want to use the following statements and check the difference of
each execution.
SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SET STATISTICS IO ON
dbcc freeproccache
dbcc dropcleanbuffers
Go
--Exec <place your stored procedure or query here>
go
SET STATISTICS PROFILE OFF
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
go
HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/?id=243589
It's a little werid that only insert time varies. You may want to check if
there is any trigger on the table for insert. Autogrow of the database/log
as Tibor mentioned might be a problem.
Also, I'd like to know if you have clustred index on the table? Are the
rows you insert/delete are the exactly same? Since statistics information
might be different if the rows are not same and this may affect execution
plan.
If you have any update, please feel free to let's know. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
I'm still interested in the issue. Did you try the suggestions to see the
difference of execution? If you have any update, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

insert performance varies

hi. i'm no dba and wouldn't mind a few pointers for investigation of an issue
i have with sql server 2000 sp3. i am running a script within query analyzer.
the batch includes hundreds of deletes followed by hundreds of inserts, all
within a single transaction.
i have noticed that running the batch multiple times results in widely
varying run times (between 9 and 100 seconds). however, the time taken to do
the deletes never varies (always around 2.5 seconds). how might i begin
investigating this? assuming no other queries are being run against the
database, is there anything sql server might be doing in the background which
might cause this.
note one of my colleagues found this link, which sounds similar to our
problem. unfortunately applying sp4 is out of the question at the moment.
http://support.microsoft.com/kb/835864
many thanks
kh
A wild guess is that autogrow for the log file (or even database file) kicks in when it is slow.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"kh" <kh@.nospam.nospam> wrote in message news:50CF0E7F-3653-4600-998E-90323DF73F5C@.microsoft.com...
> hi. i'm no dba and wouldn't mind a few pointers for investigation of an issue
> i have with sql server 2000 sp3. i am running a script within query analyzer.
> the batch includes hundreds of deletes followed by hundreds of inserts, all
> within a single transaction.
> i have noticed that running the batch multiple times results in widely
> varying run times (between 9 and 100 seconds). however, the time taken to do
> the deletes never varies (always around 2.5 seconds). how might i begin
> investigating this? assuming no other queries are being run against the
> database, is there anything sql server might be doing in the background which
> might cause this.
> note one of my colleagues found this link, which sounds similar to our
> problem. unfortunately applying sp4 is out of the question at the moment.
> http://support.microsoft.com/kb/835864
> many thanks
> kh
>
|||Hi,
I understand the the the delay only occurs for insert operations. As you
said, 835864 shall be a possible cause if you are use multi-processes or
hyperthread CPUs. If it is the cause, I'm afraid that install latest SP4
when possible shall be the only fix.
You may want to use the following statements and check the difference of
each execution.
SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SET STATISTICS IO ON
dbcc freeproccache
dbcc dropcleanbuffers
Go
--Exec <place your stored procedure or query here>
go
SET STATISTICS PROFILE OFF
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
go
HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/?id=243589
It's a little werid that only insert time varies. You may want to check if
there is any trigger on the table for insert. Autogrow of the database/log
as Tibor mentioned might be a problem.
Also, I'd like to know if you have clustred index on the table? Are the
rows you insert/delete are the exactly same? Since statistics information
might be different if the rows are not same and this may affect execution
plan.
If you have any update, please feel free to let's know. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications
<http://msdn.microsoft.com/subscripti...s/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscripti...t/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi,
I'm still interested in the issue. Did you try the suggestions to see the
difference of execution? If you have any update, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

insert performance varies

hi. i'm no dba and wouldn't mind a few pointers for investigation of an issu
e
i have with sql server 2000 sp3. i am running a script within query analyzer
.
the batch includes hundreds of deletes followed by hundreds of inserts, all
within a single transaction.
i have noticed that running the batch multiple times results in widely
varying run times (between 9 and 100 seconds). however, the time taken to do
the deletes never varies (always around 2.5 seconds). how might i begin
investigating this? assuming no other queries are being run against the
database, is there anything sql server might be doing in the background whic
h
might cause this.
note one of my colleagues found this link, which sounds similar to our
problem. unfortunately applying sp4 is out of the question at the moment.
http://support.microsoft.com/kb/835864
many thanks
khA wild guess is that autogrow for the log file (or even database file) kicks
in when it is slow.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"kh" <kh@.nospam.nospam> wrote in message news:50CF0E7F-3653-4600-998E-90323DF73F5C@.microsoft
.com...
> hi. i'm no dba and wouldn't mind a few pointers for investigation of an is
sue
> i have with sql server 2000 sp3. i am running a script within query analyz
er.
> the batch includes hundreds of deletes followed by hundreds of inserts, al
l
> within a single transaction.
> i have noticed that running the batch multiple times results in widely
> varying run times (between 9 and 100 seconds). however, the time taken to
do
> the deletes never varies (always around 2.5 seconds). how might i begin
> investigating this? assuming no other queries are being run against the
> database, is there anything sql server might be doing in the background wh
ich
> might cause this.
> note one of my colleagues found this link, which sounds similar to our
> problem. unfortunately applying sp4 is out of the question at the moment.
> http://support.microsoft.com/kb/835864
> many thanks
> kh
>|||Hi,
I understand the the the delay only occurs for insert operations. As you
said, 835864 shall be a possible cause if you are use multi-processes or
hyperthread CPUs. If it is the cause, I'm afraid that install latest SP4
when possible shall be the only fix.
You may want to use the following statements and check the difference of
each execution.
SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SET STATISTICS IO ON
dbcc freeproccache
dbcc dropcleanbuffers
Go
--Exec <place your stored procedure or query here>
go
SET STATISTICS PROFILE OFF
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
go
HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/?id=243589
It's a little werid that only insert time varies. You may want to check if
there is any trigger on the table for insert. Autogrow of the database/log
as Tibor mentioned might be a problem.
Also, I'd like to know if you have clustred index on the table? Are the
rows you insert/delete are the exactly same? Since statistics information
might be different if the rows are not same and this may affect execution
plan.
If you have any update, please feel free to let's know. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
I'm still interested in the issue. Did you try the suggestions to see the
difference of execution? If you have any update, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============