Archiving very slow, help needed please

Hey guys,
The vendor gave us an archiving tool for our huge tables. We gave them a
production copy of our tables. They tested their tool against their own
server and told us that it takes 2 seconds to insert each record. I know
this is already bad. But today, I tested it against our own test server.
They also gave us instruction on what to do first before running the
archiving tool. Well anyways, after running the archiving tool, it took 20
seconds to insert 1 record. That's totally bad!
I would like to know if you will be able to help me guys identify the
problem by just looking at this links.
http://restricted.dyndns.org/executionplan.txt
http://restricted.dyndns.org/execplan1.gif
http://restricted.dyndns.org/execplan2.gif
http://restricted.dyndns.org/execplan3.gif
The INSERT statement that you will see there consumed 10 seconds of CPU and
30 seconds of Duration.
Are there any other statements that I can execute against my captured
profiler table that can us troubleshoot?
Any help will be greatly appreciated.
Thanks.
V1rt>> told us that it takes 2 seconds to insert each record
well....
Go back to the vendor (I assume you haven't paid them yet) and tell them
this isn't acceptable.
How many records do you have to deal with - even at 2 secs per record?
"Neil" wrote:
> Hey guys,
> The vendor gave us an archiving tool for our huge tables. We gave them a
> production copy of our tables. They tested their tool against their own
> server and told us that it takes 2 seconds to insert each record. I know
> this is already bad. But today, I tested it against our own test server.
> They also gave us instruction on what to do first before running the
> archiving tool. Well anyways, after running the archiving tool, it took 20
> seconds to insert 1 record. That's totally bad!
> I would like to know if you will be able to help me guys identify the
> problem by just looking at this links.
> http://restricted.dyndns.org/executionplan.txt
> http://restricted.dyndns.org/execplan1.gif
> http://restricted.dyndns.org/execplan2.gif
> http://restricted.dyndns.org/execplan3.gif
> The INSERT statement that you will see there consumed 10 seconds of CPU and
> 30 seconds of Duration.
> Are there any other statements that I can execute against my captured
> profiler table that can us troubleshoot?
> Any help will be greatly appreciated.
> Thanks.
> V1rt
>
>|||It looks like you are filtering the source table, Enclosure. Nothing wrong
with that. However, then you are joining this to the destination table.
Why?
An archive table usually doesn't contain the data in it yet. Are you
joining to make sure that you don't attempt to archive data that's already
been copied?
If so, there are better ways to write this. If you are INSERTing into a
table, not an update, there is rarely a need to join the destination to the
source. This will only slow you down because an INSERT will usually involve
updating the joined columns.
Try something more like this:
BEGIN TRANSACTION
INSERT INTO TableDestination
(Column1, Column2, ..., ColumnN)
SELECT Column1, Column2, ..., ColumnN
FROM TableSource
WHERE TableSource.ColumnX = ExpressionX
IF @.@.ERROR <> 0 BEGIN
ROLLBACK TRANSACTION
RETURN
END
DELETE FROM s
FROM TableDestination AS d
INNER JOIN TableSource AS s
ON d.Key1 = s.Key1
AND d.Key2 = s.Key2
AND...
AND d.KeyN = s.KeyN
IF @.@.ERROR <> 0 BEGIN
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION
Sincerely,
Anthony Thomas
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:B085D060-8557-4C9E-B632-0C320BE1CE98@.microsoft.com...
> >> told us that it takes 2 seconds to insert each record
> well....
> Go back to the vendor (I assume you haven't paid them yet) and tell them
> this isn't acceptable.
> How many records do you have to deal with - even at 2 secs per record?
>
> "Neil" wrote:
> > Hey guys,
> >
> > The vendor gave us an archiving tool for our huge tables. We gave them a
> > production copy of our tables. They tested their tool against their own
> > server and told us that it takes 2 seconds to insert each record. I know
> > this is already bad. But today, I tested it against our own test server.
> > They also gave us instruction on what to do first before running the
> > archiving tool. Well anyways, after running the archiving tool, it took
20
> > seconds to insert 1 record. That's totally bad!
> >
> > I would like to know if you will be able to help me guys identify the
> > problem by just looking at this links.
> >
> > http://restricted.dyndns.org/executionplan.txt
> > http://restricted.dyndns.org/execplan1.gif
> > http://restricted.dyndns.org/execplan2.gif
> > http://restricted.dyndns.org/execplan3.gif
> >
> > The INSERT statement that you will see there consumed 10 seconds of CPU
and
> > 30 seconds of Duration.
> >
> > Are there any other statements that I can execute against my captured
> > profiler table that can us troubleshoot?
> >
> > Any help will be greatly appreciated.
> >
> > Thanks.
> >
> > V1rt
> >
> >
> >|||Hi Anthony,
Thanks for the awesome reply. Am I correct that the destination table that
is being joint is the tablefieldaudit? That's what I saw in the INSERT
statement.
Below is what I captured using Profiler. I saw tons of this running for 20+
seconds each. :(
INSERT INTO
TABLEFIELDAUDIT(TABLENAME,FIELDNAME,FIELDVALUE,CHANGEDATE,KEYVALUE,USERID,SUBKEY1,
SUBKEY2)
SELECT TABLENAME,FIELDNAME,FIELDVALUE,CHANGEDATE,E.RECORDID AS
KEYVALUE,USERID,SUBKEY1, SUBKEY2
FROM TABLEFIELDAUDIT
INNER JOIN ENCLOSURE E
ON TABLEFIELDAUDIT.SUBKEY1=E.BARCODE AND
TABLEFIELDAUDIT.SUBKEY2=E.ENCLOSURENUMBER
WHERE TABLENAME='ENCLOSURE' AND SUBKEY1='00010690'
Thanks again,
Neil
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:OLmPhvtyEHA.2568@.TK2MSFTNGP11.phx.gbl...
> It looks like you are filtering the source table, Enclosure. Nothing
> wrong
> with that. However, then you are joining this to the destination table.
> Why?
> An archive table usually doesn't contain the data in it yet. Are you
> joining to make sure that you don't attempt to archive data that's already
> been copied?
> If so, there are better ways to write this. If you are INSERTing into a
> table, not an update, there is rarely a need to join the destination to
> the
> source. This will only slow you down because an INSERT will usually
> involve
> updating the joined columns.
> Try something more like this:
> BEGIN TRANSACTION
> INSERT INTO TableDestination
> (Column1, Column2, ..., ColumnN)
> SELECT Column1, Column2, ..., ColumnN
> FROM TableSource
> WHERE TableSource.ColumnX = ExpressionX
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK TRANSACTION
> RETURN
> END
> DELETE FROM s
> FROM TableDestination AS d
> INNER JOIN TableSource AS s
> ON d.Key1 = s.Key1
> AND d.Key2 = s.Key2
> AND...
> AND d.KeyN = s.KeyN
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK TRANSACTION
> RETURN
> END
> COMMIT TRANSACTION
> Sincerely,
>
> Anthony Thomas
>
> --
> "Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
> news:B085D060-8557-4C9E-B632-0C320BE1CE98@.microsoft.com...
>> >> told us that it takes 2 seconds to insert each record
>> well....
>> Go back to the vendor (I assume you haven't paid them yet) and tell them
>> this isn't acceptable.
>> How many records do you have to deal with - even at 2 secs per record?
>>
>> "Neil" wrote:
>> > Hey guys,
>> >
>> > The vendor gave us an archiving tool for our huge tables. We gave them
>> > a
>> > production copy of our tables. They tested their tool against their own
>> > server and told us that it takes 2 seconds to insert each record. I
>> > know
>> > this is already bad. But today, I tested it against our own test
>> > server.
>> > They also gave us instruction on what to do first before running the
>> > archiving tool. Well anyways, after running the archiving tool, it took
> 20
>> > seconds to insert 1 record. That's totally bad!
>> >
>> > I would like to know if you will be able to help me guys identify the
>> > problem by just looking at this links.
>> >
>> > http://restricted.dyndns.org/executionplan.txt
>> > http://restricted.dyndns.org/execplan1.gif
>> > http://restricted.dyndns.org/execplan2.gif
>> > http://restricted.dyndns.org/execplan3.gif
>> >
>> > The INSERT statement that you will see there consumed 10 seconds of CPU
> and
>> > 30 seconds of Duration.
>> >
>> > Are there any other statements that I can execute against my captured
>> > profiler table that can us troubleshoot?
>> >
>> > Any help will be greatly appreciated.
>> >
>> > Thanks.
>> >
>> > V1rt
>> >
>> >
>> >
>

Archiving very slow, help needed please

Hey guys,
The vendor gave us an archiving tool for our huge tables. We gave them a
production copy of our tables. They tested their tool against their own
server and told us that it takes 2 seconds to insert each record. I know
this is already bad. But today, I tested it against our own test server.
They also gave us instruction on what to do first before running the
archiving tool. Well anyways, after running the archiving tool, it took 20
seconds to insert 1 record. That's totally bad!
I would like to know if you will be able to help me guys identify the
problem by just looking at this links.
http://restricted.dyndns.org/executionplan.txt
http://restricted.dyndns.org/execplan1.gif
http://restricted.dyndns.org/execplan2.gif
http://restricted.dyndns.org/execplan3.gif
The INSERT statement that you will see there consumed 10 seconds of CPU and
30 seconds of Duration.
Are there any other statements that I can execute against my captured
profiler table that can us troubleshoot?
Any help will be greatly appreciated.
Thanks.
V1rt
>> told us that it takes 2 seconds to insert each record
well....
Go back to the vendor (I assume you haven't paid them yet) and tell them
this isn't acceptable.
How many records do you have to deal with - even at 2 secs per record?
"Neil" wrote:

> Hey guys,
> The vendor gave us an archiving tool for our huge tables. We gave them a
> production copy of our tables. They tested their tool against their own
> server and told us that it takes 2 seconds to insert each record. I know
> this is already bad. But today, I tested it against our own test server.
> They also gave us instruction on what to do first before running the
> archiving tool. Well anyways, after running the archiving tool, it took 20
> seconds to insert 1 record. That's totally bad!
> I would like to know if you will be able to help me guys identify the
> problem by just looking at this links.
> http://restricted.dyndns.org/executionplan.txt
> http://restricted.dyndns.org/execplan1.gif
> http://restricted.dyndns.org/execplan2.gif
> http://restricted.dyndns.org/execplan3.gif
> The INSERT statement that you will see there consumed 10 seconds of CPU and
> 30 seconds of Duration.
> Are there any other statements that I can execute against my captured
> profiler table that can us troubleshoot?
> Any help will be greatly appreciated.
> Thanks.
> V1rt
>
>
|||It looks like you are filtering the source table, Enclosure. Nothing wrong
with that. However, then you are joining this to the destination table.
Why?
An archive table usually doesn't contain the data in it yet. Are you
joining to make sure that you don't attempt to archive data that's already
been copied?
If so, there are better ways to write this. If you are INSERTing into a
table, not an update, there is rarely a need to join the destination to the
source. This will only slow you down because an INSERT will usually involve
updating the joined columns.
Try something more like this:
BEGIN TRANSACTION
INSERT INTO TableDestination
(Column1, Column2, ..., ColumnN)
SELECT Column1, Column2, ..., ColumnN
FROM TableSource
WHERE TableSource.ColumnX = ExpressionX
IF @.@.ERROR <> 0 BEGIN
ROLLBACK TRANSACTION
RETURN
END
DELETE FROM s
FROM TableDestination AS d
INNER JOIN TableSource AS s
ON d.Key1 = s.Key1
AND d.Key2 = s.Key2
AND...
AND d.KeyN = s.KeyN
IF @.@.ERROR <> 0 BEGIN
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION
Sincerely,
Anthony Thomas

"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:B085D060-8557-4C9E-B632-0C320BE1CE98@.microsoft.com...[vbcol=seagreen]
> well....
> Go back to the vendor (I assume you haven't paid them yet) and tell them
> this isn't acceptable.
> How many records do you have to deal with - even at 2 secs per record?
>
> "Neil" wrote:
20[vbcol=seagreen]
and[vbcol=seagreen]
|||Hi Anthony,
Thanks for the awesome reply. Am I correct that the destination table that
is being joint is the tablefieldaudit? That's what I saw in the INSERT
statement.
Below is what I captured using Profiler. I saw tons of this running for 20+
seconds each.
INSERT INTO
TABLEFIELDAUDIT(TABLENAME,FIELDNAME,FIELDVALUE,CHA NGEDATE,KEYVALUE,USERID,SUBKEY1,
SUBKEY2)
SELECT TABLENAME,FIELDNAME,FIELDVALUE,CHANGEDATE,E.RECORD ID AS
KEYVALUE,USERID,SUBKEY1, SUBKEY2
FROM TABLEFIELDAUDIT
INNER JOIN ENCLOSURE E
ON TABLEFIELDAUDIT.SUBKEY1=E.BARCODE AND
TABLEFIELDAUDIT.SUBKEY2=E.ENCLOSURENUMBER
WHERE TABLENAME='ENCLOSURE' AND SUBKEY1='00010690'
Thanks again,
Neil
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:OLmPhvtyEHA.2568@.TK2MSFTNGP11.phx.gbl...
> It looks like you are filtering the source table, Enclosure. Nothing
> wrong
> with that. However, then you are joining this to the destination table.
> Why?
> An archive table usually doesn't contain the data in it yet. Are you
> joining to make sure that you don't attempt to archive data that's already
> been copied?
> If so, there are better ways to write this. If you are INSERTing into a
> table, not an update, there is rarely a need to join the destination to
> the
> source. This will only slow you down because an INSERT will usually
> involve
> updating the joined columns.
> Try something more like this:
> BEGIN TRANSACTION
> INSERT INTO TableDestination
> (Column1, Column2, ..., ColumnN)
> SELECT Column1, Column2, ..., ColumnN
> FROM TableSource
> WHERE TableSource.ColumnX = ExpressionX
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK TRANSACTION
> RETURN
> END
> DELETE FROM s
> FROM TableDestination AS d
> INNER JOIN TableSource AS s
> ON d.Key1 = s.Key1
> AND d.Key2 = s.Key2
> AND...
> AND d.KeyN = s.KeyN
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK TRANSACTION
> RETURN
> END
> COMMIT TRANSACTION
> Sincerely,
>
> Anthony Thomas
>
> --
> "Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
> news:B085D060-8557-4C9E-B632-0C320BE1CE98@.microsoft.com...
> 20
> and
>

Archiving very slow, help needed please

Hey guys,
The vendor gave us an archiving tool for our huge tables. We gave them a
production copy of our tables. They tested their tool against their own
server and told us that it takes 2 seconds to insert each record. I know
this is already bad. But today, I tested it against our own test server.
They also gave us instruction on what to do first before running the
archiving tool. Well anyways, after running the archiving tool, it took 20
seconds to insert 1 record. That's totally bad!
I would like to know if you will be able to help me guys identify the
problem by just looking at this links.
http://restricted.dyndns.org/executionplan.txt
http://restricted.dyndns.org/execplan1.gif
http://restricted.dyndns.org/execplan2.gif
http://restricted.dyndns.org/execplan3.gif
The INSERT statement that you will see there consumed 10 seconds of CPU and
30 seconds of Duration.
Are there any other statements that I can execute against my captured
profiler table that can us troubleshoot?
Any help will be greatly appreciated.
Thanks.
V1rt>> told us that it takes 2 seconds to insert each record
well....
Go back to the vendor (I assume you haven't paid them yet) and tell them
this isn't acceptable.
How many records do you have to deal with - even at 2 secs per record?
"Neil" wrote:

> Hey guys,
> The vendor gave us an archiving tool for our huge tables. We gave them a
> production copy of our tables. They tested their tool against their own
> server and told us that it takes 2 seconds to insert each record. I know
> this is already bad. But today, I tested it against our own test server.
> They also gave us instruction on what to do first before running the
> archiving tool. Well anyways, after running the archiving tool, it took 20
> seconds to insert 1 record. That's totally bad!
> I would like to know if you will be able to help me guys identify the
> problem by just looking at this links.
> http://restricted.dyndns.org/executionplan.txt
> http://restricted.dyndns.org/execplan1.gif
> http://restricted.dyndns.org/execplan2.gif
> http://restricted.dyndns.org/execplan3.gif
> The INSERT statement that you will see there consumed 10 seconds of CPU an
d
> 30 seconds of Duration.
> Are there any other statements that I can execute against my captured
> profiler table that can us troubleshoot?
> Any help will be greatly appreciated.
> Thanks.
> V1rt
>
>|||It looks like you are filtering the source table, Enclosure. Nothing wrong
with that. However, then you are joining this to the destination table.
Why?
An archive table usually doesn't contain the data in it yet. Are you
joining to make sure that you don't attempt to archive data that's already
been copied?
If so, there are better ways to write this. If you are INSERTing into a
table, not an update, there is rarely a need to join the destination to the
source. This will only slow you down because an INSERT will usually involve
updating the joined columns.
Try something more like this:
BEGIN TRANSACTION
INSERT INTO TableDestination
(Column1, Column2, ..., ColumnN)
SELECT Column1, Column2, ..., ColumnN
FROM TableSource
WHERE TableSource.ColumnX = ExpressionX
IF @.@.ERROR <> 0 BEGIN
ROLLBACK TRANSACTION
RETURN
END
DELETE FROM s
FROM TableDestination AS d
INNER JOIN TableSource AS s
ON d.Key1 = s.Key1
AND d.Key2 = s.Key2
AND...
AND d.KeyN = s.KeyN
IF @.@.ERROR <> 0 BEGIN
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION
Sincerely,
Anthony Thomas
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:B085D060-8557-4C9E-B632-0C320BE1CE98@.microsoft.com...[vbcol=seagreen]
> well....
> Go back to the vendor (I assume you haven't paid them yet) and tell them
> this isn't acceptable.
> How many records do you have to deal with - even at 2 secs per record?
>
> "Neil" wrote:
>
20[vbcol=seagreen]
and[vbcol=seagreen]|||Hi Anthony,
Thanks for the awesome reply. Am I correct that the destination table that
is being joint is the tablefieldaudit? That's what I saw in the INSERT
statement.
Below is what I captured using Profiler. I saw tons of this running for 20+
seconds each.
INSERT INTO
TABLEFIELDAUDIT(TABLENAME,FIELDNAME,FIEL
DVALUE,CHANGEDATE,KEYVALUE,USERID,SU
BKEY1,
SUBKEY2)
SELECT TABLENAME,FIELDNAME,FIELDVALUE,CHANGEDAT
E,E.RECORDID AS
KEYVALUE,USERID,SUBKEY1, SUBKEY2
FROM TABLEFIELDAUDIT
INNER JOIN ENCLOSURE E
ON TABLEFIELDAUDIT.SUBKEY1=E.BARCODE AND
TABLEFIELDAUDIT.SUBKEY2=E.ENCLOSURENUMBER
WHERE TABLENAME='ENCLOSURE' AND SUBKEY1='00010690'
Thanks again,
Neil
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:OLmPhvtyEHA.2568@.TK2MSFTNGP11.phx.gbl...
> It looks like you are filtering the source table, Enclosure. Nothing
> wrong
> with that. However, then you are joining this to the destination table.
> Why?
> An archive table usually doesn't contain the data in it yet. Are you
> joining to make sure that you don't attempt to archive data that's already
> been copied?
> If so, there are better ways to write this. If you are INSERTing into a
> table, not an update, there is rarely a need to join the destination to
> the
> source. This will only slow you down because an INSERT will usually
> involve
> updating the joined columns.
> Try something more like this:
> BEGIN TRANSACTION
> INSERT INTO TableDestination
> (Column1, Column2, ..., ColumnN)
> SELECT Column1, Column2, ..., ColumnN
> FROM TableSource
> WHERE TableSource.ColumnX = ExpressionX
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK TRANSACTION
> RETURN
> END
> DELETE FROM s
> FROM TableDestination AS d
> INNER JOIN TableSource AS s
> ON d.Key1 = s.Key1
> AND d.Key2 = s.Key2
> AND...
> AND d.KeyN = s.KeyN
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK TRANSACTION
> RETURN
> END
> COMMIT TRANSACTION
> Sincerely,
>
> Anthony Thomas
>
> --
> "Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
> news:B085D060-8557-4C9E-B632-0C320BE1CE98@.microsoft.com...
> 20
> and
>

archiving data

Hi all hope this is in the right forum.
Ive got a SQL database which accumulates about 4 million records each year.
This amount causes my PC to slow down when searching, and my hard drive is
getting fuller.
To overcome this problem I have to delete half the records each year (2
million) which takes quite some time.
My question is - is it possible to archive some of the SQL database each
year onto another hard drive or DVD. The archived data should them be removed
from the original SQL data base.
I Would also need to be able to view, sort and search this archived data on
another machine in excell or something.
Hope someone can help.
Thanks
Jon
"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> Hi all hope this is in the right forum.
> Ive got a SQL database which accumulates about 4 million records each
> year.
> This amount causes my PC to slow down when searching, and my hard drive is
> getting fuller.
>
4 million a YEAR slows you down? Do you have indexes?
Anyway...

> To overcome this problem I have to delete half the records each year (2
> million) which takes quite some time.
>
Yeah, deletions can take time.

> My question is - is it possible to archive some of the SQL database each
> year onto another hard drive or DVD. The archived data should them be
> removed
> from the original SQL data base.
I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
in a format you can use.
Then delete it.
That should solve your problems.
> I Would also need to be able to view, sort and search this archived data
> on
> another machine in excell or something.
> Hope someone can help.
> Thanks
> Jon
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||Hi,
Thanks for the reply.
Yes we have clustered indexes on the primary keys.
Sorry but what is DTS.
Can the process be automated on a particular date?
Thanks
"Greg D. Moore (Strider)" wrote:

> "jsw" <jsw@.discussions.microsoft.com> wrote in message
> news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> 4 million a YEAR slows you down? Do you have indexes?
> Anyway...
>
> Yeah, deletions can take time.
>
> I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
> in a format you can use.
> Then delete it.
> That should solve your problems.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>
|||"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:AF4D2F53-9995-4745-973D-5E3DC04B1EC3@.microsoft.com...
> Hi,
> Thanks for the reply.
> Yes we have clustered indexes on the primary keys.
> Sorry but what is DTS.
Data Transformation Services.
Look for it under Enterprise Manager.

> Can the process be automated on a particular date?
Yes.
Create a DTS package, and then you can schedule it.

> Thanks
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

archiving data

Hi all hope this is in the right forum.
Ive got a SQL database which accumulates about 4 million records each year.
This amount causes my PC to slow down when searching, and my hard drive is
getting fuller.
To overcome this problem I have to delete half the records each year (2
million) which takes quite some time.
My question is - is it possible to archive some of the SQL database each
year onto another hard drive or DVD. The archived data should them be remove
d
from the original SQL data base.
I Would also need to be able to view, sort and search this archived data on
another machine in excell or something.
Hope someone can help.
Thanks
Jon"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> Hi all hope this is in the right forum.
> Ive got a SQL database which accumulates about 4 million records each
> year.
> This amount causes my PC to slow down when searching, and my hard drive is
> getting fuller.
>
4 million a YEAR slows you down? Do you have indexes?
Anyway...

> To overcome this problem I have to delete half the records each year (2
> million) which takes quite some time.
>
Yeah, deletions can take time.

> My question is - is it possible to archive some of the SQL database each
> year onto another hard drive or DVD. The archived data should them be
> removed
> from the original SQL data base.
I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
in a format you can use.
Then delete it.
That should solve your problems.
> I Would also need to be able to view, sort and search this archived data
> on
> another machine in excell or something.
> Hope someone can help.
> Thanks
> Jon
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi,
Thanks for the reply.
Yes we have clustered indexes on the primary keys.
Sorry but what is DTS.
Can the process be automated on a particular date?
Thanks
"Greg D. Moore (Strider)" wrote:

> "jsw" <jsw@.discussions.microsoft.com> wrote in message
> news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> 4 million a YEAR slows you down? Do you have indexes?
> Anyway...
>
> Yeah, deletions can take time.
>
> I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the da
ta
> in a format you can use.
> Then delete it.
> That should solve your problems.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>|||"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:AF4D2F53-9995-4745-973D-5E3DC04B1EC3@.microsoft.com...
> Hi,
> Thanks for the reply.
> Yes we have clustered indexes on the primary keys.
> Sorry but what is DTS.
Data Transformation Services.
Look for it under Enterprise Manager.

> Can the process be automated on a particular date?
Yes.
Create a DTS package, and then you can schedule it.

> Thanks
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

archiving data

Hi all hope this is in the right forum.
Ive got a SQL database which accumulates about 4 million records each year.
This amount causes my PC to slow down when searching, and my hard drive is
getting fuller.
To overcome this problem I have to delete half the records each year (2
million) which takes quite some time.
My question is - is it possible to archive some of the SQL database each
year onto another hard drive or DVD. The archived data should them be removed
from the original SQL data base.
I Would also need to be able to view, sort and search this archived data on
another machine in excell or something.
Hope someone can help.
Thanks
Jon"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> Hi all hope this is in the right forum.
> Ive got a SQL database which accumulates about 4 million records each
> year.
> This amount causes my PC to slow down when searching, and my hard drive is
> getting fuller.
>
4 million a YEAR slows you down? Do you have indexes?
Anyway...
> To overcome this problem I have to delete half the records each year (2
> million) which takes quite some time.
>
Yeah, deletions can take time.
> My question is - is it possible to archive some of the SQL database each
> year onto another hard drive or DVD. The archived data should them be
> removed
> from the original SQL data base.
I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
in a format you can use.
Then delete it.
That should solve your problems.
> I Would also need to be able to view, sort and search this archived data
> on
> another machine in excell or something.
> Hope someone can help.
> Thanks
> Jon
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi,
Thanks for the reply.
Yes we have clustered indexes on the primary keys.
Sorry but what is DTS.
Can the process be automated on a particular date?
Thanks
"Greg D. Moore (Strider)" wrote:
> "jsw" <jsw@.discussions.microsoft.com> wrote in message
> news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> >
> > Hi all hope this is in the right forum.
> >
> > Ive got a SQL database which accumulates about 4 million records each
> > year.
> > This amount causes my PC to slow down when searching, and my hard drive is
> > getting fuller.
> >
> 4 million a YEAR slows you down? Do you have indexes?
> Anyway...
>
> > To overcome this problem I have to delete half the records each year (2
> > million) which takes quite some time.
> >
> Yeah, deletions can take time.
>
> > My question is - is it possible to archive some of the SQL database each
> > year onto another hard drive or DVD. The archived data should them be
> > removed
> > from the original SQL data base.
> I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
> in a format you can use.
> Then delete it.
> That should solve your problems.
> >
> > I Would also need to be able to view, sort and search this archived data
> > on
> > another machine in excell or something.
> >
> > Hope someone can help.
> >
> > Thanks
> >
> > Jon
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>|||"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:AF4D2F53-9995-4745-973D-5E3DC04B1EC3@.microsoft.com...
> Hi,
> Thanks for the reply.
> Yes we have clustered indexes on the primary keys.
> Sorry but what is DTS.
Data Transformation Services.
Look for it under Enterprise Manager.
> Can the process be automated on a particular date?
Yes.
Create a DTS package, and then you can schedule it.
> Thanks
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.comsql

application retrieve same records, sometimes fast, sometimes extreme slow

Hi,
Sometimes, in my application, when i browse a
record, it takes very fast 1/2 seconds but sometimes it
seems like hang there and take a very long time around
10 minutes to finish,could it be deadlock ? Any other
suggestion?
How to check and prevent from deadlock?
thanks.
regards,
florence
Could it be possible you are blocked due to your choice of isolation levels?
You could run sp_who and see if anyone is blocking you the next time your
app slows down. You could set to READ UNCOMMITTED if its acceptable by your
app. Then again, a commit by another app should not take that long, so you
might want to look at fixing the other app instead.
Another possibility is that the data was in the buffer cache when the app
was fast.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
> Hi,
> Sometimes, in my application, when i browse a
> record, it takes very fast 1/2 seconds but sometimes it
> seems like hang there and take a very long time around
> 10 minutes to finish,could it be deadlock ? Any other
> suggestion?
> How to check and prevent from deadlock?
> thanks.
> regards,
> florence
>
|||Hi,
Thanks for your reply. I didn't set any isolation
level in my application, so i think by default it should
be read uncommitted right? How to detect whether the data
is in the buffer cache but the application run too fast?
Thanks

>--Original Message--
>Could it be possible you are blocked due to your choice
of isolation levels?
>You could run sp_who and see if anyone is blocking you
the next time your
>app slows down. You could set to READ UNCOMMITTED if
its acceptable by your
>app. Then again, a commit by another app should not
take that long, so you
>might want to look at fixing the other app instead.
>Another possibility is that the data was in the buffer
cache when the app
>was fast.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup
Lite, free!
>
>"florencelee" <florencelee@.visualsolutions.com.my> wrote
in message[vbcol=seagreen]
>news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
it
>
>.
>
|||DBCC MEMUSAGE(names, 50) to show the top 50 objects in the buffer cache by
number of pages (each page is 8k).
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:3db501c4ac5a$f93f5d10$a501280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> Thanks for your reply. I didn't set any isolation
> level in my application, so i think by default it should
> be read uncommitted right? How to detect whether the data
> is in the buffer cache but the application run too fast?
> Thanks
>
> of isolation levels?
> the next time your
> its acceptable by your
> take that long, so you
> cache when the app
> Lite, free!
> in message
> it

application retrieve same records, sometimes fast, sometimes extreme slow

Hi,
Sometimes, in my application, when i browse a
record, it takes very fast 1/2 seconds but sometimes it
seems like hang there and take a very long time around
10 minutes to finish,could it be deadlock ? Any other
suggestion?
How to check and prevent from deadlock?
thanks.
regards,
florenceCould it be possible you are blocked due to your choice of isolation levels?
You could run sp_who and see if anyone is blocking you the next time your
app slows down. You could set to READ UNCOMMITTED if its acceptable by your
app. Then again, a commit by another app should not take that long, so you
might want to look at fixing the other app instead.
Another possibility is that the data was in the buffer cache when the app
was fast.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
> Hi,
> Sometimes, in my application, when i browse a
> record, it takes very fast 1/2 seconds but sometimes it
> seems like hang there and take a very long time around
> 10 minutes to finish,could it be deadlock ? Any other
> suggestion?
> How to check and prevent from deadlock?
> thanks.
> regards,
> florence
>|||Hi,
Thanks for your reply. I didn't set any isolation
level in my application, so i think by default it should
be read uncommitted right? How to detect whether the data
is in the buffer cache but the application run too fast?
Thanks
>--Original Message--
>Could it be possible you are blocked due to your choice
of isolation levels?
>You could run sp_who and see if anyone is blocking you
the next time your
>app slows down. You could set to READ UNCOMMITTED if
its acceptable by your
>app. Then again, a commit by another app should not
take that long, so you
>might want to look at fixing the other app instead.
>Another possibility is that the data was in the buffer
cache when the app
>was fast.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup
Lite, free!
>
>"florencelee" <florencelee@.visualsolutions.com.my> wrote
in message
>news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
>> Hi,
>> Sometimes, in my application, when i browse a
>> record, it takes very fast 1/2 seconds but sometimes
it
>> seems like hang there and take a very long time around
>> 10 minutes to finish,could it be deadlock ? Any other
>> suggestion?
>> How to check and prevent from deadlock?
>> thanks.
>> regards,
>> florence
>
>.
>|||DBCC MEMUSAGE(names, 50) to show the top 50 objects in the buffer cache by
number of pages (each page is 8k).
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:3db501c4ac5a$f93f5d10$a501280a@.phx.gbl...
> Hi,
> Thanks for your reply. I didn't set any isolation
> level in my application, so i think by default it should
> be read uncommitted right? How to detect whether the data
> is in the buffer cache but the application run too fast?
> Thanks
>
> >--Original Message--
> >Could it be possible you are blocked due to your choice
> of isolation levels?
> >You could run sp_who and see if anyone is blocking you
> the next time your
> >app slows down. You could set to READ UNCOMMITTED if
> its acceptable by your
> >app. Then again, a commit by another app should not
> take that long, so you
> >might want to look at fixing the other app instead.
> >
> >Another possibility is that the data was in the buffer
> cache when the app
> >was fast.
> >
> >--
> >Peter Yeoh
> >http://www.yohz.com
> >Need smaller SQL2K backup files? Use MiniSQLBackup
> Lite, free!
> >
> >
> >"florencelee" <florencelee@.visualsolutions.com.my> wrote
> in message
> >news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
> >> Hi,
> >> Sometimes, in my application, when i browse a
> >> record, it takes very fast 1/2 seconds but sometimes
> it
> >> seems like hang there and take a very long time around
> >> 10 minutes to finish,could it be deadlock ? Any other
> >> suggestion?
> >>
> >> How to check and prevent from deadlock?
> >>
> >> thanks.
> >>
> >> regards,
> >>
> >> florence
> >>
> >
> >
> >.
> >

Application is very Slow

Dear All,

Finally I completed my project. Thanks all you helped me to do it.

Now I have the biggest problem.

In my application the Data grid is filled with Data from SQL server table which has large number of records. When I run my queries in SS Management Studio it runs very fast. To fill data to datagrid it takes lots of time. How can I reduce this time. How can I increase the performance of my Application.

Thanks,

Janaka

Try to disable debugging and trace in your web.config after deployment.

Also for large amount of data you can use a custom paging solution to paging through data to prevent getting all the data which for sure affect the performance.

|||

Thanks I did custom paging its amazing. Now ok.

Again another problem.

Users edit some records in Gridview and click update button. Data will be updated in SQL table. But I want to track what are the records actually edited.

Do you have any idea?

thanks,

Janaka

|||

You may do this in many ways:

- You can handle the GridView RowUpdated event and log the information to say Database table ( Data may be : current login user id , Update time stamp,record id)

|||

Hi,

You understood what I have to do clearly. But in multi editing grid view, row updating event is not firing. Because when page loading the grid is in editable mood and there is no edit button or update button inside the grid for each and every row.

Thanks,

Janaka

Application is slow after upgrade to windows server 2003

My SQL-Server application is slow after upgrade of the operating system from
windows server 2000 to 2003. Any suggestions? Thanks.Run performance monitor first
http://sqlservercode.blogspot.com/
"Heini" wrote:

> My SQL-Server application is slow after upgrade of the operating system fr
om
> windows server 2000 to 2003. Any suggestions? Thanks.|||Heini,
How do you define slow? What part of the application is slow? Task
Manager, System Monitor and Profiler can all be used to see what is
happening. Detailed analysis of individual queries can be performed by
using execution plans in Query Analyzer.
HTH
Jerry
"Heini" <Heini@.discussions.microsoft.com> wrote in message
news:F86D1153-55B1-4431-8B25-19211B553E36@.microsoft.com...
> My SQL-Server application is slow after upgrade of the operating system
> from
> windows server 2000 to 2003. Any suggestions? Thanks.

Application is slow after upgrade to windows server 2003

My SQL-Server application is slow after upgrade of the operating system from
windows server 2000 to 2003. Any suggestions? Thanks.
Run performance monitor first
http://sqlservercode.blogspot.com/
"Heini" wrote:

> My SQL-Server application is slow after upgrade of the operating system from
> windows server 2000 to 2003. Any suggestions? Thanks.
|||Heini,
How do you define slow? What part of the application is slow? Task
Manager, System Monitor and Profiler can all be used to see what is
happening. Detailed analysis of individual queries can be performed by
using execution plans in Query Analyzer.
HTH
Jerry
"Heini" <Heini@.discussions.microsoft.com> wrote in message
news:F86D1153-55B1-4431-8B25-19211B553E36@.microsoft.com...
> My SQL-Server application is slow after upgrade of the operating system
> from
> windows server 2000 to 2003. Any suggestions? Thanks.

Application is slow after upgrade to windows server 2003

My SQL-Server application is slow after upgrade of the operating system from
windows server 2000 to 2003. Any suggestions? Thanks.Run performance monitor first
http://sqlservercode.blogspot.com/
"Heini" wrote:
> My SQL-Server application is slow after upgrade of the operating system from
> windows server 2000 to 2003. Any suggestions? Thanks.|||Heini,
How do you define slow? What part of the application is slow? Task
Manager, System Monitor and Profiler can all be used to see what is
happening. Detailed analysis of individual queries can be performed by
using execution plans in Query Analyzer.
HTH
Jerry
"Heini" <Heini@.discussions.microsoft.com> wrote in message
news:F86D1153-55B1-4431-8B25-19211B553E36@.microsoft.com...
> My SQL-Server application is slow after upgrade of the operating system
> from
> windows server 2000 to 2003. Any suggestions? Thanks.

Application goes slow or not responding after few transaction

Dear all viewer ,

Thanks to everyone.

We are using -
MSSQL Server 2000 on windows 2000 advanced server.
PB 7.0 for client server front end tools.

Prior to few days our application works fine, rightnow it get slow or not responding after few (4/5) transaction. We don't know wht it is, If anyone has same experience so that is helpfull for us.

Thanks

R.MallMonitor your activity with SQL Profiler.|||Thanks for reply

How I can do it? Can you give me step by step methods?

R.Mall|||SELECT Profiler
FROM BooksOnline

or

SELECT Consultant
FROM YellowPages|||Thanks for right suggession, I will do it.

Thanks|||I wonder if he actually did that? :)

You can also find information about running Profiler from:

www.sql-server-performance.com
www.sqlteam.com
SQL Server 2000 Performance Tuning Technical Manual (Microsoft Press)

Anything wrong with the connection?

Dear all,
I have a program (written in VB6) running in a Win2000 server with SQL
server 2000 in it. However, the program runs very slow or even "Time Out
Expired" many times. My connection string is:
strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
Id=mySQLUser;Password=myPassword;"
On the other hand I tried to run the same program in my own PC (Win2000
professional) with SQL desktop version. The program runs very fast. Can
anyone advise what is the problem when the program is running in SQL server
2000?
Thanks a million!!
Ivan
On Mon, 7 Nov 2005 01:26:50 -0800, Ivan wrote:

> Dear all,
> I have a program (written in VB6) running in a Win2000 server with SQL
> server 2000 in it. However, the program runs very slow or even "Time Out
> Expired" many times. My connection string is:
> strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
> Id=mySQLUser;Password=myPassword;"
> On the other hand I tried to run the same program in my own PC (Win2000
> professional) with SQL desktop version. The program runs very fast. Can
> anyone advise what is the problem when the program is running in SQL server
> 2000?
> Thanks a million!!
> Ivan
Hello,
I'm afraid that you need to investigate more in depth. Probably nothing to
do with your connection. Try to use the SQL Server profiler to trace
queries sent to the server, and watch things like reads, CPU, duration on
this trace. Then isolate the queires that could cause problems and watch
their query plan. Search for indexes that could lack.
Look in Enterprise Manager (or doing a sp_who2, or sp_lock) the number of
connections, and if there are blocking locks. Have also a look at some
performance counters.
You'll need to search for hints. Info you gave is not sufficient to give a
clue of what could be the problem.
Good luck
Rudi Bruchez
MCDBA

Anything wrong with the connection?

Dear all,
I have a program (written in VB6) running in a Win2000 server with SQL
server 2000 in it. However, the program runs very slow or even "Time Out
Expired" many times. My connection string is:
strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
Id=mySQLUser;Password=myPassword;"
On the other hand I tried to run the same program in my own PC (Win2000
professional) with SQL desktop version. The program runs very fast. Can
anyone advise what is the problem when the program is running in SQL server
2000?
Thanks a million!!
IvanOn Mon, 7 Nov 2005 01:26:50 -0800, Ivan wrote:

> Dear all,
> I have a program (written in VB6) running in a Win2000 server with SQL
> server 2000 in it. However, the program runs very slow or even "Time Out
> Expired" many times. My connection string is:
> strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;Use
r
> Id=mySQLUser;Password=myPassword;"
> On the other hand I tried to run the same program in my own PC (Win2000
> professional) with SQL desktop version. The program runs very fast. Can
> anyone advise what is the problem when the program is running in SQL serve
r
> 2000?
> Thanks a million!!
> Ivan
Hello,
I'm afraid that you need to investigate more in depth. Probably nothing to
do with your connection. Try to use the SQL Server profiler to trace
queries sent to the server, and watch things like reads, CPU, duration on
this trace. Then isolate the queires that could cause problems and watch
their query plan. Search for indexes that could lack.
Look in Enterprise Manager (or doing a sp_who2, or sp_lock) the number of
connections, and if there are blocking locks. Have also a look at some
performance counters.
You'll need to search for hints. Info you gave is not sufficient to give a
clue of what could be the problem.
Good luck
Rudi Bruchez
MCDBA

Anything wrong with the connection?

Dear all,
I have a program (written in VB6) running in a Win2000 server with SQL
server 2000 in it. However, the program runs very slow or even "Time Out
Expired" many times. My connection string is:
strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
Id=mySQLUser;Password=myPassword;"
On the other hand I tried to run the same program in my own PC (Win2000
professional) with SQL desktop version. The program runs very fast. Can
anyone advise what is the problem when the program is running in SQL server
2000?
Thanks a million!!
IvanOn Mon, 7 Nov 2005 01:26:50 -0800, Ivan wrote:
> Dear all,
> I have a program (written in VB6) running in a Win2000 server with SQL
> server 2000 in it. However, the program runs very slow or even "Time Out
> Expired" many times. My connection string is:
> strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
> Id=mySQLUser;Password=myPassword;"
> On the other hand I tried to run the same program in my own PC (Win2000
> professional) with SQL desktop version. The program runs very fast. Can
> anyone advise what is the problem when the program is running in SQL server
> 2000?
> Thanks a million!!
> Ivan
Hello,
I'm afraid that you need to investigate more in depth. Probably nothing to
do with your connection. Try to use the SQL Server profiler to trace
queries sent to the server, and watch things like reads, CPU, duration on
this trace. Then isolate the queires that could cause problems and watch
their query plan. Search for indexes that could lack.
Look in Enterprise Manager (or doing a sp_who2, or sp_lock) the number of
connections, and if there are blocking locks. Have also a look at some
performance counters.
You'll need to search for hints. Info you gave is not sufficient to give a
clue of what could be the problem.
Good luck
--
Rudi Bruchez
MCDBA