I am sorry to put so many issues at one go.
1. I have just upgraded one of our production dbs(sql 2000) with
latest service packs( change from 3 to 4) and others hot fixes.Also
upgraded the 2003 WIN2k box with all the hot fixes. Since after,all my
processing is taking almost double time to complete.We have processings
which runs thrice a day , which pulls/massages data from our central
db.I did identify few store procs which are running very slowly.My
question.
a)Did any one face this issue before , and if yes, could anyone list
where all it could have got affected?
b) What all non-db related things i should be looking at?
2.I have a sqlserver(2000) box( win 2k3) with 8GB of RAM. I abe
configured it to use 6GB of RAM through dynamic memory management.I
have also enabled the AWE option. My question is do i stil have to
include /pae switch in the boot.ini file.3GB /PAE switch comes into
picture when i have 4 GB of RAM(certain versions) , and it gives me
that extra 1 GB. BUt when i have 8GB , it is AWE Enabled , then what
extra benefit do i get by adding this switch? Does AWE depends on PAE
switch to fucntion normally. AWE infact takes all the memory space
(except 128 MB for OS to work).SO why do i add 3GB /PAE switch to tell
OS to give me 1 extra GB more when AWE( it takes the entire memory and
not just only 1 GB more does that for me.
3. How do i know when was the last time my index was rebuilt/updated.(
may be recreate or may be using dbccreindex).i tried DBCC show update
statiscs, it does show something called last updated. What i know this
shows, when the statistics were last updated( mine is auto update).So
if i run dbcc reindex will the last updated value changeFor future posts, you might want to post each Q independently, to keep each topic in a separate
thread. :-)
2) AWE depends on OS AWE. It is a no-op to sp_configure AWE without setting PAE in BOOT.INI. You can
still decide whether to have 2 or 3 GB "normally" addressable memory using the /3GB switch. The
recommendation is not to have /3GB if you have > some 12-16GB memory in the machine (as overhead of
managing that amount of memory comes from what is left from 4GB minus 2 or 3 GB).
3) That information is not stored anywhere. Why is it important to you?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<shuvam_roy@.yahoo.com> wrote in message news:1127221805.482833.46720@.g49g2000cwa.googlegroups.com...
>I am sorry to put so many issues at one go.
> 1. I have just upgraded one of our production dbs(sql 2000) with
> latest service packs( change from 3 to 4) and others hot fixes.Also
> upgraded the 2003 WIN2k box with all the hot fixes. Since after,all my
> processing is taking almost double time to complete.We have processings
> which runs thrice a day , which pulls/massages data from our central
> db.I did identify few store procs which are running very slowly.My
> question.
> a)Did any one face this issue before , and if yes, could anyone list
> where all it could have got affected?
> b) What all non-db related things i should be looking at?
> 2.I have a sqlserver(2000) box( win 2k3) with 8GB of RAM. I abe
> configured it to use 6GB of RAM through dynamic memory management.I
> have also enabled the AWE option. My question is do i stil have to
> include /pae switch in the boot.ini file.3GB /PAE switch comes into
> picture when i have 4 GB of RAM(certain versions) , and it gives me
> that extra 1 GB. BUt when i have 8GB , it is AWE Enabled , then what
> extra benefit do i get by adding this switch? Does AWE depends on PAE
> switch to fucntion normally. AWE infact takes all the memory space
> (except 128 MB for OS to work).SO why do i add 3GB /PAE switch to tell
> OS to give me 1 extra GB more when AWE( it takes the entire memory and
> not just only 1 GB more does that for me.
>
> 3. How do i know when was the last time my index was rebuilt/updated.(
> may be recreate or may be using dbccreindex).i tried DBCC show update
> statiscs, it does show something called last updated. What i know this
> shows, when the statistics were last updated( mine is auto update).So
> if i run dbcc reindex will the last updated value change
>|||Thanks a lot TIBOR...next tiem will post different questions on
different threads.
BTW any idea baout the first one.?|||You're welcome... :-)
As for the first one, I can't say. My first guess would be to check the execution plans for the
queries involved...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<shuvam_roy@.yahoo.com> wrote in message
news:1127229172.480279.256450@.g49g2000cwa.googlegroups.com...
> Thanks a lot TIBOR...next tiem will post different questions on
> different threads.
> BTW any idea baout the first one.?
>
Showing posts with label production. Show all posts
Showing posts with label production. Show all posts
Tuesday, March 27, 2012
Few Issues
I am sorry to put so many issues at one go.
1. I have just upgraded one of our production dbs(sql 2000) with
latest service packs( change from 3 to 4) and others hot fixes.Also
upgraded the 2003 WIN2k box with all the hot fixes. Since after,all my
processing is taking almost double time to complete.We have processings
which runs thrice a day , which pulls/massages data from our central
db.I did identify few store procs which are running very slowly.My
question.
a)Did any one face this issue before , and if yes, could anyone list
where all it could have got affected?
b) What all non-db related things i should be looking at?
2.I have a sqlserver(2000) box( win 2k3) with 8GB of RAM. I abe
configured it to use 6GB of RAM through dynamic memory management.I
have also enabled the AWE option. My question is do i stil have to
include /pae switch in the boot.ini file.3GB /PAE switch comes into
picture when i have 4 GB of RAM(certain versions) , and it gives me
that extra 1 GB. BUt when i have 8GB , it is AWE Enabled , then what
extra benefit do i get by adding this switch? Does AWE depends on PAE
switch to fucntion normally. AWE infact takes all the memory space
(except 128 MB for OS to work).SO why do i add 3GB /PAE switch to tell
OS to give me 1 extra GB more when AWE( it takes the entire memory and
not just only 1 GB more does that for me.
3. How do i know when was the last time my index was rebuilt/updated.(
may be recreate or may be using dbccreindex).i tried DBCC show update
statiscs, it does show something called last updated. What i know this
shows, when the statistics were last updated( mine is auto update).So
if i run dbcc reindex will the last updated value change
For future posts, you might want to post each Q independently, to keep each topic in a separate
thread. :-)
2) AWE depends on OS AWE. It is a no-op to sp_configure AWE without setting PAE in BOOT.INI. You can
still decide whether to have 2 or 3 GB "normally" addressable memory using the /3GB switch. The
recommendation is not to have /3GB if you have > some 12-16GB memory in the machine (as overhead of
managing that amount of memory comes from what is left from 4GB minus 2 or 3 GB).
3) That information is not stored anywhere. Why is it important to you?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<shuvam_roy@.yahoo.com> wrote in message news:1127221805.482833.46720@.g49g2000cwa.googlegro ups.com...
>I am sorry to put so many issues at one go.
> 1. I have just upgraded one of our production dbs(sql 2000) with
> latest service packs( change from 3 to 4) and others hot fixes.Also
> upgraded the 2003 WIN2k box with all the hot fixes. Since after,all my
> processing is taking almost double time to complete.We have processings
> which runs thrice a day , which pulls/massages data from our central
> db.I did identify few store procs which are running very slowly.My
> question.
> a)Did any one face this issue before , and if yes, could anyone list
> where all it could have got affected?
> b) What all non-db related things i should be looking at?
> 2.I have a sqlserver(2000) box( win 2k3) with 8GB of RAM. I abe
> configured it to use 6GB of RAM through dynamic memory management.I
> have also enabled the AWE option. My question is do i stil have to
> include /pae switch in the boot.ini file.3GB /PAE switch comes into
> picture when i have 4 GB of RAM(certain versions) , and it gives me
> that extra 1 GB. BUt when i have 8GB , it is AWE Enabled , then what
> extra benefit do i get by adding this switch? Does AWE depends on PAE
> switch to fucntion normally. AWE infact takes all the memory space
> (except 128 MB for OS to work).SO why do i add 3GB /PAE switch to tell
> OS to give me 1 extra GB more when AWE( it takes the entire memory and
> not just only 1 GB more does that for me.
>
> 3. How do i know when was the last time my index was rebuilt/updated.(
> may be recreate or may be using dbccreindex).i tried DBCC show update
> statiscs, it does show something called last updated. What i know this
> shows, when the statistics were last updated( mine is auto update).So
> if i run dbcc reindex will the last updated value change
>
|||Thanks a lot TIBOR...next tiem will post different questions on
different threads.
BTW any idea baout the first one.?
|||You're welcome... :-)
As for the first one, I can't say. My first guess would be to check the execution plans for the
queries involved...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<shuvam_roy@.yahoo.com> wrote in message
news:1127229172.480279.256450@.g49g2000cwa.googlegr oups.com...
> Thanks a lot TIBOR...next tiem will post different questions on
> different threads.
> BTW any idea baout the first one.?
>
1. I have just upgraded one of our production dbs(sql 2000) with
latest service packs( change from 3 to 4) and others hot fixes.Also
upgraded the 2003 WIN2k box with all the hot fixes. Since after,all my
processing is taking almost double time to complete.We have processings
which runs thrice a day , which pulls/massages data from our central
db.I did identify few store procs which are running very slowly.My
question.
a)Did any one face this issue before , and if yes, could anyone list
where all it could have got affected?
b) What all non-db related things i should be looking at?
2.I have a sqlserver(2000) box( win 2k3) with 8GB of RAM. I abe
configured it to use 6GB of RAM through dynamic memory management.I
have also enabled the AWE option. My question is do i stil have to
include /pae switch in the boot.ini file.3GB /PAE switch comes into
picture when i have 4 GB of RAM(certain versions) , and it gives me
that extra 1 GB. BUt when i have 8GB , it is AWE Enabled , then what
extra benefit do i get by adding this switch? Does AWE depends on PAE
switch to fucntion normally. AWE infact takes all the memory space
(except 128 MB for OS to work).SO why do i add 3GB /PAE switch to tell
OS to give me 1 extra GB more when AWE( it takes the entire memory and
not just only 1 GB more does that for me.
3. How do i know when was the last time my index was rebuilt/updated.(
may be recreate or may be using dbccreindex).i tried DBCC show update
statiscs, it does show something called last updated. What i know this
shows, when the statistics were last updated( mine is auto update).So
if i run dbcc reindex will the last updated value change
For future posts, you might want to post each Q independently, to keep each topic in a separate
thread. :-)
2) AWE depends on OS AWE. It is a no-op to sp_configure AWE without setting PAE in BOOT.INI. You can
still decide whether to have 2 or 3 GB "normally" addressable memory using the /3GB switch. The
recommendation is not to have /3GB if you have > some 12-16GB memory in the machine (as overhead of
managing that amount of memory comes from what is left from 4GB minus 2 or 3 GB).
3) That information is not stored anywhere. Why is it important to you?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<shuvam_roy@.yahoo.com> wrote in message news:1127221805.482833.46720@.g49g2000cwa.googlegro ups.com...
>I am sorry to put so many issues at one go.
> 1. I have just upgraded one of our production dbs(sql 2000) with
> latest service packs( change from 3 to 4) and others hot fixes.Also
> upgraded the 2003 WIN2k box with all the hot fixes. Since after,all my
> processing is taking almost double time to complete.We have processings
> which runs thrice a day , which pulls/massages data from our central
> db.I did identify few store procs which are running very slowly.My
> question.
> a)Did any one face this issue before , and if yes, could anyone list
> where all it could have got affected?
> b) What all non-db related things i should be looking at?
> 2.I have a sqlserver(2000) box( win 2k3) with 8GB of RAM. I abe
> configured it to use 6GB of RAM through dynamic memory management.I
> have also enabled the AWE option. My question is do i stil have to
> include /pae switch in the boot.ini file.3GB /PAE switch comes into
> picture when i have 4 GB of RAM(certain versions) , and it gives me
> that extra 1 GB. BUt when i have 8GB , it is AWE Enabled , then what
> extra benefit do i get by adding this switch? Does AWE depends on PAE
> switch to fucntion normally. AWE infact takes all the memory space
> (except 128 MB for OS to work).SO why do i add 3GB /PAE switch to tell
> OS to give me 1 extra GB more when AWE( it takes the entire memory and
> not just only 1 GB more does that for me.
>
> 3. How do i know when was the last time my index was rebuilt/updated.(
> may be recreate or may be using dbccreindex).i tried DBCC show update
> statiscs, it does show something called last updated. What i know this
> shows, when the statistics were last updated( mine is auto update).So
> if i run dbcc reindex will the last updated value change
>
|||Thanks a lot TIBOR...next tiem will post different questions on
different threads.
BTW any idea baout the first one.?
|||You're welcome... :-)
As for the first one, I can't say. My first guess would be to check the execution plans for the
queries involved...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<shuvam_roy@.yahoo.com> wrote in message
news:1127229172.480279.256450@.g49g2000cwa.googlegr oups.com...
> Thanks a lot TIBOR...next tiem will post different questions on
> different threads.
> BTW any idea baout the first one.?
>
Few Issues
I am sorry to put so many issues at one go.
1. I have just upgraded one of our production dbs(sql 2000) with
latest service packs( change from 3 to 4) and others hot fixes.Also
upgraded the 2003 WIN2k box with all the hot fixes. Since after,all my
processing is taking almost double time to complete.We have processings
which runs thrice a day , which pulls/massages data from our central
db.I did identify few store procs which are running very slowly.My
question.
a)Did any one face this issue before , and if yes, could anyone list
where all it could have got affected?
b) What all non-db related things i should be looking at?
2.I have a sqlserver(2000) box( win 2k3) with 8GB of RAM. I abe
configured it to use 6GB of RAM through dynamic memory management.I
have also enabled the AWE option. My question is do i stil have to
include /pae switch in the boot.ini file.3GB /PAE switch comes into
picture when i have 4 GB of RAM(certain versions) , and it gives me
that extra 1 GB. BUt when i have 8GB , it is AWE Enabled , then what
extra benefit do i get by adding this switch? Does AWE depends on PAE
switch to fucntion normally. AWE infact takes all the memory space
(except 128 MB for OS to work).SO why do i add 3GB /PAE switch to tell
OS to give me 1 extra GB more when AWE( it takes the entire memory and
not just only 1 GB more does that for me.
3. How do i know when was the last time my index was rebuilt/updated.(
may be recreate or may be using dbccreindex).i tried DBCC show update
statiscs, it does show something called last updated. What i know this
shows, when the statistics were last updated( mine is auto update).So
if i run dbcc reindex will the last updated value changeFor future posts, you might want to post each Q independently, to keep each
topic in a separate
thread. :-)
2) AWE depends on OS AWE. It is a no-op to sp_configure AWE without setting
PAE in BOOT.INI. You can
still decide whether to have 2 or 3 GB "normally" addressable memory using t
he /3GB switch. The
recommendation is not to have /3GB if you have > some 12-16GB memory in the
machine (as overhead of
managing that amount of memory comes from what is left from 4GB minus 2 or 3
GB).
3) That information is not stored anywhere. Why is it important to you?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<shuvam_roy@.yahoo.com> wrote in message news:1127221805.482833.46720@.g49g2000cwa.googlegroup
s.com...
>I am sorry to put so many issues at one go.
> 1. I have just upgraded one of our production dbs(sql 2000) with
> latest service packs( change from 3 to 4) and others hot fixes.Also
> upgraded the 2003 WIN2k box with all the hot fixes. Since after,all my
> processing is taking almost double time to complete.We have processings
> which runs thrice a day , which pulls/massages data from our central
> db.I did identify few store procs which are running very slowly.My
> question.
> a)Did any one face this issue before , and if yes, could anyone list
> where all it could have got affected?
> b) What all non-db related things i should be looking at?
> 2.I have a sqlserver(2000) box( win 2k3) with 8GB of RAM. I abe
> configured it to use 6GB of RAM through dynamic memory management.I
> have also enabled the AWE option. My question is do i stil have to
> include /pae switch in the boot.ini file.3GB /PAE switch comes into
> picture when i have 4 GB of RAM(certain versions) , and it gives me
> that extra 1 GB. BUt when i have 8GB , it is AWE Enabled , then what
> extra benefit do i get by adding this switch? Does AWE depends on PAE
> switch to fucntion normally. AWE infact takes all the memory space
> (except 128 MB for OS to work).SO why do i add 3GB /PAE switch to tell
> OS to give me 1 extra GB more when AWE( it takes the entire memory and
> not just only 1 GB more does that for me.
>
> 3. How do i know when was the last time my index was rebuilt/updated.(
> may be recreate or may be using dbccreindex).i tried DBCC show update
> statiscs, it does show something called last updated. What i know this
> shows, when the statistics were last updated( mine is auto update).So
> if i run dbcc reindex will the last updated value change
>|||Thanks a lot TIBOR...next tiem will post different questions on
different threads.
BTW any idea baout the first one.?|||You're welcome... :-)
As for the first one, I can't say. My first guess would be to check the exec
ution plans for the
queries involved...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<shuvam_roy@.yahoo.com> wrote in message
news:1127229172.480279.256450@.g49g2000cwa.googlegroups.com...
> Thanks a lot TIBOR...next tiem will post different questions on
> different threads.
> BTW any idea baout the first one.?
>sql
1. I have just upgraded one of our production dbs(sql 2000) with
latest service packs( change from 3 to 4) and others hot fixes.Also
upgraded the 2003 WIN2k box with all the hot fixes. Since after,all my
processing is taking almost double time to complete.We have processings
which runs thrice a day , which pulls/massages data from our central
db.I did identify few store procs which are running very slowly.My
question.
a)Did any one face this issue before , and if yes, could anyone list
where all it could have got affected?
b) What all non-db related things i should be looking at?
2.I have a sqlserver(2000) box( win 2k3) with 8GB of RAM. I abe
configured it to use 6GB of RAM through dynamic memory management.I
have also enabled the AWE option. My question is do i stil have to
include /pae switch in the boot.ini file.3GB /PAE switch comes into
picture when i have 4 GB of RAM(certain versions) , and it gives me
that extra 1 GB. BUt when i have 8GB , it is AWE Enabled , then what
extra benefit do i get by adding this switch? Does AWE depends on PAE
switch to fucntion normally. AWE infact takes all the memory space
(except 128 MB for OS to work).SO why do i add 3GB /PAE switch to tell
OS to give me 1 extra GB more when AWE( it takes the entire memory and
not just only 1 GB more does that for me.
3. How do i know when was the last time my index was rebuilt/updated.(
may be recreate or may be using dbccreindex).i tried DBCC show update
statiscs, it does show something called last updated. What i know this
shows, when the statistics were last updated( mine is auto update).So
if i run dbcc reindex will the last updated value changeFor future posts, you might want to post each Q independently, to keep each
topic in a separate
thread. :-)
2) AWE depends on OS AWE. It is a no-op to sp_configure AWE without setting
PAE in BOOT.INI. You can
still decide whether to have 2 or 3 GB "normally" addressable memory using t
he /3GB switch. The
recommendation is not to have /3GB if you have > some 12-16GB memory in the
machine (as overhead of
managing that amount of memory comes from what is left from 4GB minus 2 or 3
GB).
3) That information is not stored anywhere. Why is it important to you?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<shuvam_roy@.yahoo.com> wrote in message news:1127221805.482833.46720@.g49g2000cwa.googlegroup
s.com...
>I am sorry to put so many issues at one go.
> 1. I have just upgraded one of our production dbs(sql 2000) with
> latest service packs( change from 3 to 4) and others hot fixes.Also
> upgraded the 2003 WIN2k box with all the hot fixes. Since after,all my
> processing is taking almost double time to complete.We have processings
> which runs thrice a day , which pulls/massages data from our central
> db.I did identify few store procs which are running very slowly.My
> question.
> a)Did any one face this issue before , and if yes, could anyone list
> where all it could have got affected?
> b) What all non-db related things i should be looking at?
> 2.I have a sqlserver(2000) box( win 2k3) with 8GB of RAM. I abe
> configured it to use 6GB of RAM through dynamic memory management.I
> have also enabled the AWE option. My question is do i stil have to
> include /pae switch in the boot.ini file.3GB /PAE switch comes into
> picture when i have 4 GB of RAM(certain versions) , and it gives me
> that extra 1 GB. BUt when i have 8GB , it is AWE Enabled , then what
> extra benefit do i get by adding this switch? Does AWE depends on PAE
> switch to fucntion normally. AWE infact takes all the memory space
> (except 128 MB for OS to work).SO why do i add 3GB /PAE switch to tell
> OS to give me 1 extra GB more when AWE( it takes the entire memory and
> not just only 1 GB more does that for me.
>
> 3. How do i know when was the last time my index was rebuilt/updated.(
> may be recreate or may be using dbccreindex).i tried DBCC show update
> statiscs, it does show something called last updated. What i know this
> shows, when the statistics were last updated( mine is auto update).So
> if i run dbcc reindex will the last updated value change
>|||Thanks a lot TIBOR...next tiem will post different questions on
different threads.
BTW any idea baout the first one.?|||You're welcome... :-)
As for the first one, I can't say. My first guess would be to check the exec
ution plans for the
queries involved...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<shuvam_roy@.yahoo.com> wrote in message
news:1127229172.480279.256450@.g49g2000cwa.googlegroups.com...
> Thanks a lot TIBOR...next tiem will post different questions on
> different threads.
> BTW any idea baout the first one.?
>sql
Friday, March 23, 2012
Feedback after one week of using
Hi all,
Just installed the software on Monday and I already have 2 reports in the
production. This software is so amazing, I did a little demo today at work
and everyone was shocked to see that it took me only 15 minutes to author
one simple report with no line of code! It's very easy to use, I think
coming up with the query is hard. I still think it needs some improvements,
i.e. calendar control (this is a must in any report, I don't believe they
didn't think of it when they released the software!)
I still have some questions though:
1. Since we have SQL and Web servers in the DMZ (they belong to DMZ domain),
we don't want to have users (on separate domain) log on (using DMZ domain
account - no trust allowed) to view and subscribe reports. Is there a way to
set anonymous access to default reports, except for few reports and certain
features that we want to lock out from anonymous users? This helps us a lot
with maintenance and support.
2. Can I connect to 2 or more datasources in the same report?
Thanks in advance for the help.
SQL Server Reporting Services ROCKS!Thanks for using our product.
1. Allowing both anonymous and authenticated access would require to
configure IIS to accept both anonymous and windows integrated
authentication. We found this configuration to be very confusing and asking
customers to avoid it. If you allow both, internet explorer will not know
which one to use. This will cause unstable behavior, sometimes it will try
to go as anonymous and sometimes will ask for user name and password.
One way to solve this problem is to install another report server and point
it to the same database. Then allow only anonymous access on one RS and only
integrated authentication on another. After that you can give rights to
account under which IIS runs anonymous user for some reports. Note that if
you use different boxes for this, you'll have to make this used a domain
user known to both boxes and SQL.
2. Yes, this is possible.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"VNN" <VNN@.hotmail.com> wrote in message
news:enXXJ7lCFHA.1268@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> Just installed the software on Monday and I already have 2 reports in the
> production. This software is so amazing, I did a little demo today at work
> and everyone was shocked to see that it took me only 15 minutes to author
> one simple report with no line of code! It's very easy to use, I think
> coming up with the query is hard. I still think it needs some
improvements,
> i.e. calendar control (this is a must in any report, I don't believe they
> didn't think of it when they released the software!)
> I still have some questions though:
> 1. Since we have SQL and Web servers in the DMZ (they belong to DMZ
domain),
> we don't want to have users (on separate domain) log on (using DMZ domain
> account - no trust allowed) to view and subscribe reports. Is there a way
to
> set anonymous access to default reports, except for few reports and
certain
> features that we want to lock out from anonymous users? This helps us a
lot
> with maintenance and support.
> 2. Can I connect to 2 or more datasources in the same report?
> Thanks in advance for the help.
> SQL Server Reporting Services ROCKS!
>|||Hello Dmitry,
I can echo the comments of VNN. I am into my second week using RS,
apart from initial hiccups of installation, development has been rapid.
I am eagerly awaiting SP2 to see if they can add some features which i
feel can make my life easier.
As you mentioned multiple installations of RS pointing to same DB, I
would like to know more about this.
I am trying to deploy my reports on a remote server. But i am not sure
to use the existing RS DB on a machine in my domain or
Create a new RS db on the server.
Would like to know how to go about
1. Using the existing DB
2. Creating a new DB on the server.
In case of creating a new DB is there a way I can export all the
reports I created on my local machine to the new report server ?
What are the security/account types/auth/IIS issues I need to take care
of.
I am gonna do the installation tommorow and trying to get as much info
as possible.
Thx
Bofo|||When multiple servers are sharing the same SQL DB that is called a web farm
configuration. It is more complicated to setup and it is more expensive (you
have to have an enterprise server license). For what you are doing you
definitely do not need to do this and I would recommend against it (even if
you are already using enterprise). In your case it is best to have
development and production separate. It is very easy to deploy all reports
again. RS has multiple configurations you can deploy to (I have three). You
can deploy all your reports or you can deploy just the ones you need (select
the ones to deploy, right mouse click deploy).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RemoteDeploy" <bofobofo@.yahoo.com> wrote in message
news:1107497967.753916.202930@.l41g2000cwc.googlegroups.com...
> Hello Dmitry,
> I can echo the comments of VNN. I am into my second week using RS,
> apart from initial hiccups of installation, development has been rapid.
> I am eagerly awaiting SP2 to see if they can add some features which i
> feel can make my life easier.
> As you mentioned multiple installations of RS pointing to same DB, I
> would like to know more about this.
> I am trying to deploy my reports on a remote server. But i am not sure
> to use the existing RS DB on a machine in my domain or
> Create a new RS db on the server.
> Would like to know how to go about
> 1. Using the existing DB
> 2. Creating a new DB on the server.
> In case of creating a new DB is there a way I can export all the
> reports I created on my local machine to the new report server ?
> What are the security/account types/auth/IIS issues I need to take care
> of.
> I am gonna do the installation tommorow and trying to get as much info
> as possible.
>
> Thx
> Bofo
>
Just installed the software on Monday and I already have 2 reports in the
production. This software is so amazing, I did a little demo today at work
and everyone was shocked to see that it took me only 15 minutes to author
one simple report with no line of code! It's very easy to use, I think
coming up with the query is hard. I still think it needs some improvements,
i.e. calendar control (this is a must in any report, I don't believe they
didn't think of it when they released the software!)
I still have some questions though:
1. Since we have SQL and Web servers in the DMZ (they belong to DMZ domain),
we don't want to have users (on separate domain) log on (using DMZ domain
account - no trust allowed) to view and subscribe reports. Is there a way to
set anonymous access to default reports, except for few reports and certain
features that we want to lock out from anonymous users? This helps us a lot
with maintenance and support.
2. Can I connect to 2 or more datasources in the same report?
Thanks in advance for the help.
SQL Server Reporting Services ROCKS!Thanks for using our product.
1. Allowing both anonymous and authenticated access would require to
configure IIS to accept both anonymous and windows integrated
authentication. We found this configuration to be very confusing and asking
customers to avoid it. If you allow both, internet explorer will not know
which one to use. This will cause unstable behavior, sometimes it will try
to go as anonymous and sometimes will ask for user name and password.
One way to solve this problem is to install another report server and point
it to the same database. Then allow only anonymous access on one RS and only
integrated authentication on another. After that you can give rights to
account under which IIS runs anonymous user for some reports. Note that if
you use different boxes for this, you'll have to make this used a domain
user known to both boxes and SQL.
2. Yes, this is possible.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"VNN" <VNN@.hotmail.com> wrote in message
news:enXXJ7lCFHA.1268@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> Just installed the software on Monday and I already have 2 reports in the
> production. This software is so amazing, I did a little demo today at work
> and everyone was shocked to see that it took me only 15 minutes to author
> one simple report with no line of code! It's very easy to use, I think
> coming up with the query is hard. I still think it needs some
improvements,
> i.e. calendar control (this is a must in any report, I don't believe they
> didn't think of it when they released the software!)
> I still have some questions though:
> 1. Since we have SQL and Web servers in the DMZ (they belong to DMZ
domain),
> we don't want to have users (on separate domain) log on (using DMZ domain
> account - no trust allowed) to view and subscribe reports. Is there a way
to
> set anonymous access to default reports, except for few reports and
certain
> features that we want to lock out from anonymous users? This helps us a
lot
> with maintenance and support.
> 2. Can I connect to 2 or more datasources in the same report?
> Thanks in advance for the help.
> SQL Server Reporting Services ROCKS!
>|||Hello Dmitry,
I can echo the comments of VNN. I am into my second week using RS,
apart from initial hiccups of installation, development has been rapid.
I am eagerly awaiting SP2 to see if they can add some features which i
feel can make my life easier.
As you mentioned multiple installations of RS pointing to same DB, I
would like to know more about this.
I am trying to deploy my reports on a remote server. But i am not sure
to use the existing RS DB on a machine in my domain or
Create a new RS db on the server.
Would like to know how to go about
1. Using the existing DB
2. Creating a new DB on the server.
In case of creating a new DB is there a way I can export all the
reports I created on my local machine to the new report server ?
What are the security/account types/auth/IIS issues I need to take care
of.
I am gonna do the installation tommorow and trying to get as much info
as possible.
Thx
Bofo|||When multiple servers are sharing the same SQL DB that is called a web farm
configuration. It is more complicated to setup and it is more expensive (you
have to have an enterprise server license). For what you are doing you
definitely do not need to do this and I would recommend against it (even if
you are already using enterprise). In your case it is best to have
development and production separate. It is very easy to deploy all reports
again. RS has multiple configurations you can deploy to (I have three). You
can deploy all your reports or you can deploy just the ones you need (select
the ones to deploy, right mouse click deploy).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RemoteDeploy" <bofobofo@.yahoo.com> wrote in message
news:1107497967.753916.202930@.l41g2000cwc.googlegroups.com...
> Hello Dmitry,
> I can echo the comments of VNN. I am into my second week using RS,
> apart from initial hiccups of installation, development has been rapid.
> I am eagerly awaiting SP2 to see if they can add some features which i
> feel can make my life easier.
> As you mentioned multiple installations of RS pointing to same DB, I
> would like to know more about this.
> I am trying to deploy my reports on a remote server. But i am not sure
> to use the existing RS DB on a machine in my domain or
> Create a new RS db on the server.
> Would like to know how to go about
> 1. Using the existing DB
> 2. Creating a new DB on the server.
> In case of creating a new DB is there a way I can export all the
> reports I created on my local machine to the new report server ?
> What are the security/account types/auth/IIS issues I need to take care
> of.
> I am gonna do the installation tommorow and trying to get as much info
> as possible.
>
> Thx
> Bofo
>
federated databases
I am wondering if federated databases is reliable enough
for a large production server. I am seeking opinion of
those who are using federated databases in in large
production environment only.
Thanks.Reliable enough is a relative. If it wasn't reliable it wouldn't be in the
product but you might want to consider scaling up instead of out. It's a
much simpler technology and with less servers involved there is less chance
of something breaking.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:2d7be01c469d4$9d71a330$a301280a@.phx
.gbl...
> I am wondering if federated databases is reliable enough
> for a large production server. I am seeking opinion of
> those who are using federated databases in in large
> production environment only.
> Thanks.
>
for a large production server. I am seeking opinion of
those who are using federated databases in in large
production environment only.
Thanks.Reliable enough is a relative. If it wasn't reliable it wouldn't be in the
product but you might want to consider scaling up instead of out. It's a
much simpler technology and with less servers involved there is less chance
of something breaking.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:2d7be01c469d4$9d71a330$a301280a@.phx
.gbl...
> I am wondering if federated databases is reliable enough
> for a large production server. I am seeking opinion of
> those who are using federated databases in in large
> production environment only.
> Thanks.
>
federated databases
I am wondering if federated databases is reliable enough
for a large production server. I am seeking opinion of
those who are using federated databases in in large
production environment only.
Thanks.
Reliable enough is a relative. If it wasn't reliable it wouldn't be in the
product but you might want to consider scaling up instead of out. It's a
much simpler technology and with less servers involved there is less chance
of something breaking.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:2d7be01c469d4$9d71a330$a301280a@.phx.gbl...
> I am wondering if federated databases is reliable enough
> for a large production server. I am seeking opinion of
> those who are using federated databases in in large
> production environment only.
> Thanks.
>
for a large production server. I am seeking opinion of
those who are using federated databases in in large
production environment only.
Thanks.
Reliable enough is a relative. If it wasn't reliable it wouldn't be in the
product but you might want to consider scaling up instead of out. It's a
much simpler technology and with less servers involved there is less chance
of something breaking.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:2d7be01c469d4$9d71a330$a301280a@.phx.gbl...
> I am wondering if federated databases is reliable enough
> for a large production server. I am seeking opinion of
> those who are using federated databases in in large
> production environment only.
> Thanks.
>
federated databases
I am wondering if federated databases is reliable enough
for a large production server. I am seeking opinion of
those who are using federated databases in in large
production environment only.
Thanks.Reliable enough is a relative. If it wasn't reliable it wouldn't be in the
product but you might want to consider scaling up instead of out. It's a
much simpler technology and with less servers involved there is less chance
of something breaking.
--
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:2d7be01c469d4$9d71a330$a301280a@.phx.gbl...
> I am wondering if federated databases is reliable enough
> for a large production server. I am seeking opinion of
> those who are using federated databases in in large
> production environment only.
> Thanks.
>
for a large production server. I am seeking opinion of
those who are using federated databases in in large
production environment only.
Thanks.Reliable enough is a relative. If it wasn't reliable it wouldn't be in the
product but you might want to consider scaling up instead of out. It's a
much simpler technology and with less servers involved there is less chance
of something breaking.
--
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:2d7be01c469d4$9d71a330$a301280a@.phx.gbl...
> I am wondering if federated databases is reliable enough
> for a large production server. I am seeking opinion of
> those who are using federated databases in in large
> production environment only.
> Thanks.
>
Monday, March 12, 2012
Fastest Way To Make Test Copy of Database
Some of our developers want to automate some system for bringing over a copy
of the production database to their workstations to use for test purposes.
I want to break this down into two separate questions:
1) What is the most efficient way to bring the current database state over
to test machines? What I would like to find is a way to apply just the
change logs to the database currently on the developer's machine. There
just aren't that many changes being made to the database, and the log files
are small.
Assuming the log file is backed up using the native Windows SQL backup
utility on the production database, what is the procedure to apply those
changes to the database on another machine?
2) How can this whole process be automated, so that the programmer could
just run a script and expect that 10 minutes later his database is up to the
current level
Are there applications that would help to automate this whole process?
Will
Internet: westes AT earthbroadcast.com
If the database is small you can write custom script to drop the developer
database and recreate it thru script given to developer.
step 1: Share the network location of backup on both machines
step 2: create linked server on the developer machine
step 3: write custom script on developer machine that go thru cursor like this
a) restore database with norecovery
b) declare c1 INSENSITIVE cursor for
select bmf.physical_device_name file_name
from sql2.msdb.dbo.backupset bs
inner join sql2.msdb.dbo.backupmediafamily bmf
on bmf.media_set_id = bs.media_set_id
where upper(bs.database_name) = upper(@.orig_db_name)
and backup_start_date > (getdate()-2)
and type ='L'
order by bs.backup_start_date
FOR READ ONLY
c) go thru cursor and apply all the Tlogs
EXEC ('RESTORE LOG ' + @.restore_db_name + ' from DISK =' + '''' + @.id +
'''' + ' with standby=' + '''' + 'c:\mssql\data\MSSQL\BACKUP\UNDO_' +
@.restore_db_name + '.DAT' + '''')
At the end restore database with recovery and you will have a DB as of last
tlog backup.
I do what you asked in question 2 but DB is open only as read only and
gets sync from production by custom scripts listed above.
If you have Enterprise edition you do not need to write the custom script as
it is in built in maintenance wizard
" wrote:
> Some of our developers want to automate some system for bringing over a copy
> of the production database to their workstations to use for test purposes.
> I want to break this down into two separate questions:
> 1) What is the most efficient way to bring the current database state over
> to test machines? What I would like to find is a way to apply just the
> change logs to the database currently on the developer's machine. There
> just aren't that many changes being made to the database, and the log files
> are small.
> Assuming the log file is backed up using the native Windows SQL backup
> utility on the production database, what is the procedure to apply those
> changes to the database on another machine?
> 2) How can this whole process be automated, so that the programmer could
> just run a script and expect that 10 minutes later his database is up to the
> current level
> Are there applications that would help to automate this whole process?
> --
> Will
> Internet: westes AT earthbroadcast.com
>
>
|||Will
1) What is the most efficient way to bring the current database state over
to test machines? What I would like to find is a way to apply just the
change logs to the database currently on the developer's machine. There
just aren't that many changes being made to the database, and the log files
are small.
A. Detach\Attach database or more secure BACKUP /RESTORE Database
Also take a look at this article
http://www.sql-server-performance.co...g_shipping.asp
"Will" <DELETE_westes@.earthbroadcast.com> wrote in message
news:ex%232INmkFHA.576@.TK2MSFTNGP15.phx.gbl...
> Some of our developers want to automate some system for bringing over a
> copy
> of the production database to their workstations to use for test purposes.
> I want to break this down into two separate questions:
> 1) What is the most efficient way to bring the current database state over
> to test machines? What I would like to find is a way to apply just the
> change logs to the database currently on the developer's machine. There
> just aren't that many changes being made to the database, and the log
> files
> are small.
> Assuming the log file is backed up using the native Windows SQL backup
> utility on the production database, what is the procedure to apply those
> changes to the database on another machine?
> 2) How can this whole process be automated, so that the programmer could
> just run a script and expect that 10 minutes later his database is up to
> the
> current level
> Are there applications that would help to automate this whole process?
> --
> Will
> Internet: westes AT earthbroadcast.com
>
>
of the production database to their workstations to use for test purposes.
I want to break this down into two separate questions:
1) What is the most efficient way to bring the current database state over
to test machines? What I would like to find is a way to apply just the
change logs to the database currently on the developer's machine. There
just aren't that many changes being made to the database, and the log files
are small.
Assuming the log file is backed up using the native Windows SQL backup
utility on the production database, what is the procedure to apply those
changes to the database on another machine?
2) How can this whole process be automated, so that the programmer could
just run a script and expect that 10 minutes later his database is up to the
current level
Are there applications that would help to automate this whole process?
Will
Internet: westes AT earthbroadcast.com
If the database is small you can write custom script to drop the developer
database and recreate it thru script given to developer.
step 1: Share the network location of backup on both machines
step 2: create linked server on the developer machine
step 3: write custom script on developer machine that go thru cursor like this
a) restore database with norecovery
b) declare c1 INSENSITIVE cursor for
select bmf.physical_device_name file_name
from sql2.msdb.dbo.backupset bs
inner join sql2.msdb.dbo.backupmediafamily bmf
on bmf.media_set_id = bs.media_set_id
where upper(bs.database_name) = upper(@.orig_db_name)
and backup_start_date > (getdate()-2)
and type ='L'
order by bs.backup_start_date
FOR READ ONLY
c) go thru cursor and apply all the Tlogs
EXEC ('RESTORE LOG ' + @.restore_db_name + ' from DISK =' + '''' + @.id +
'''' + ' with standby=' + '''' + 'c:\mssql\data\MSSQL\BACKUP\UNDO_' +
@.restore_db_name + '.DAT' + '''')
At the end restore database with recovery and you will have a DB as of last
tlog backup.
I do what you asked in question 2 but DB is open only as read only and
gets sync from production by custom scripts listed above.
If you have Enterprise edition you do not need to write the custom script as
it is in built in maintenance wizard
" wrote:
> Some of our developers want to automate some system for bringing over a copy
> of the production database to their workstations to use for test purposes.
> I want to break this down into two separate questions:
> 1) What is the most efficient way to bring the current database state over
> to test machines? What I would like to find is a way to apply just the
> change logs to the database currently on the developer's machine. There
> just aren't that many changes being made to the database, and the log files
> are small.
> Assuming the log file is backed up using the native Windows SQL backup
> utility on the production database, what is the procedure to apply those
> changes to the database on another machine?
> 2) How can this whole process be automated, so that the programmer could
> just run a script and expect that 10 minutes later his database is up to the
> current level
> Are there applications that would help to automate this whole process?
> --
> Will
> Internet: westes AT earthbroadcast.com
>
>
|||Will
1) What is the most efficient way to bring the current database state over
to test machines? What I would like to find is a way to apply just the
change logs to the database currently on the developer's machine. There
just aren't that many changes being made to the database, and the log files
are small.
A. Detach\Attach database or more secure BACKUP /RESTORE Database
Also take a look at this article
http://www.sql-server-performance.co...g_shipping.asp
"Will" <DELETE_westes@.earthbroadcast.com> wrote in message
news:ex%232INmkFHA.576@.TK2MSFTNGP15.phx.gbl...
> Some of our developers want to automate some system for bringing over a
> copy
> of the production database to their workstations to use for test purposes.
> I want to break this down into two separate questions:
> 1) What is the most efficient way to bring the current database state over
> to test machines? What I would like to find is a way to apply just the
> change logs to the database currently on the developer's machine. There
> just aren't that many changes being made to the database, and the log
> files
> are small.
> Assuming the log file is backed up using the native Windows SQL backup
> utility on the production database, what is the procedure to apply those
> changes to the database on another machine?
> 2) How can this whole process be automated, so that the programmer could
> just run a script and expect that 10 minutes later his database is up to
> the
> current level
> Are there applications that would help to automate this whole process?
> --
> Will
> Internet: westes AT earthbroadcast.com
>
>
Fastest Way To Make Test Copy of Database
Some of our developers want to automate some system for bringing over a copy
of the production database to their workstations to use for test purposes.
I want to break this down into two separate questions:
1) What is the most efficient way to bring the current database state over
to test machines? What I would like to find is a way to apply just the
change logs to the database currently on the developer's machine. There
just aren't that many changes being made to the database, and the log files
are small.
Assuming the log file is backed up using the native Windows SQL backup
utility on the production database, what is the procedure to apply those
changes to the database on another machine?
2) How can this whole process be automated, so that the programmer could
just run a script and expect that 10 minutes later his database is up to the
current level
Are there applications that would help to automate this whole process?
--
Will
Internet: westes AT earthbroadcast.comIf the database is small you can write custom script to drop the developer
database and recreate it thru script given to developer.
step 1: Share the network location of backup on both machines
step 2: create linked server on the developer machine
step 3: write custom script on developer machine that go thru cursor like this
a) restore database with norecovery
b) declare c1 INSENSITIVE cursor for
select bmf.physical_device_name file_name
from sql2.msdb.dbo.backupset bs
inner join sql2.msdb.dbo.backupmediafamily bmf
on bmf.media_set_id = bs.media_set_id
where upper(bs.database_name) = upper(@.orig_db_name)
and backup_start_date > (getdate()-2)
and type ='L'
order by bs.backup_start_date
FOR READ ONLY
c) go thru cursor and apply all the Tlogs
EXEC ('RESTORE LOG ' + @.restore_db_name + ' from DISK =' + '''' + @.id +
'''' + ' with standby=' + '''' + 'c:\mssql\data\MSSQL\BACKUP\UNDO_' +
@.restore_db_name + '.DAT' + '''')
At the end restore database with recovery and you will have a DB as of last
tlog backup.
I do what you asked in question 2 but DB is open only as read only and
gets sync from production by custom scripts listed above.
If you have Enterprise edition you do not need to write the custom script as
it is in built in maintenance wizard
" wrote:
> Some of our developers want to automate some system for bringing over a copy
> of the production database to their workstations to use for test purposes.
> I want to break this down into two separate questions:
> 1) What is the most efficient way to bring the current database state over
> to test machines? What I would like to find is a way to apply just the
> change logs to the database currently on the developer's machine. There
> just aren't that many changes being made to the database, and the log files
> are small.
> Assuming the log file is backed up using the native Windows SQL backup
> utility on the production database, what is the procedure to apply those
> changes to the database on another machine?
> 2) How can this whole process be automated, so that the programmer could
> just run a script and expect that 10 minutes later his database is up to the
> current level
> Are there applications that would help to automate this whole process?
> --
> Will
> Internet: westes AT earthbroadcast.com
>
>|||Will
1) What is the most efficient way to bring the current database state over
to test machines? What I would like to find is a way to apply just the
change logs to the database currently on the developer's machine. There
just aren't that many changes being made to the database, and the log files
are small.
A. Detach\Attach database or more secure BACKUP /RESTORE Database
Also take a look at this article
http://www.sql-server-performance.com/sql_server_log_shipping.asp
"Will" <DELETE_westes@.earthbroadcast.com> wrote in message
news:ex%232INmkFHA.576@.TK2MSFTNGP15.phx.gbl...
> Some of our developers want to automate some system for bringing over a
> copy
> of the production database to their workstations to use for test purposes.
> I want to break this down into two separate questions:
> 1) What is the most efficient way to bring the current database state over
> to test machines? What I would like to find is a way to apply just the
> change logs to the database currently on the developer's machine. There
> just aren't that many changes being made to the database, and the log
> files
> are small.
> Assuming the log file is backed up using the native Windows SQL backup
> utility on the production database, what is the procedure to apply those
> changes to the database on another machine?
> 2) How can this whole process be automated, so that the programmer could
> just run a script and expect that 10 minutes later his database is up to
> the
> current level
> Are there applications that would help to automate this whole process?
> --
> Will
> Internet: westes AT earthbroadcast.com
>
>
of the production database to their workstations to use for test purposes.
I want to break this down into two separate questions:
1) What is the most efficient way to bring the current database state over
to test machines? What I would like to find is a way to apply just the
change logs to the database currently on the developer's machine. There
just aren't that many changes being made to the database, and the log files
are small.
Assuming the log file is backed up using the native Windows SQL backup
utility on the production database, what is the procedure to apply those
changes to the database on another machine?
2) How can this whole process be automated, so that the programmer could
just run a script and expect that 10 minutes later his database is up to the
current level
Are there applications that would help to automate this whole process?
--
Will
Internet: westes AT earthbroadcast.comIf the database is small you can write custom script to drop the developer
database and recreate it thru script given to developer.
step 1: Share the network location of backup on both machines
step 2: create linked server on the developer machine
step 3: write custom script on developer machine that go thru cursor like this
a) restore database with norecovery
b) declare c1 INSENSITIVE cursor for
select bmf.physical_device_name file_name
from sql2.msdb.dbo.backupset bs
inner join sql2.msdb.dbo.backupmediafamily bmf
on bmf.media_set_id = bs.media_set_id
where upper(bs.database_name) = upper(@.orig_db_name)
and backup_start_date > (getdate()-2)
and type ='L'
order by bs.backup_start_date
FOR READ ONLY
c) go thru cursor and apply all the Tlogs
EXEC ('RESTORE LOG ' + @.restore_db_name + ' from DISK =' + '''' + @.id +
'''' + ' with standby=' + '''' + 'c:\mssql\data\MSSQL\BACKUP\UNDO_' +
@.restore_db_name + '.DAT' + '''')
At the end restore database with recovery and you will have a DB as of last
tlog backup.
I do what you asked in question 2 but DB is open only as read only and
gets sync from production by custom scripts listed above.
If you have Enterprise edition you do not need to write the custom script as
it is in built in maintenance wizard
" wrote:
> Some of our developers want to automate some system for bringing over a copy
> of the production database to their workstations to use for test purposes.
> I want to break this down into two separate questions:
> 1) What is the most efficient way to bring the current database state over
> to test machines? What I would like to find is a way to apply just the
> change logs to the database currently on the developer's machine. There
> just aren't that many changes being made to the database, and the log files
> are small.
> Assuming the log file is backed up using the native Windows SQL backup
> utility on the production database, what is the procedure to apply those
> changes to the database on another machine?
> 2) How can this whole process be automated, so that the programmer could
> just run a script and expect that 10 minutes later his database is up to the
> current level
> Are there applications that would help to automate this whole process?
> --
> Will
> Internet: westes AT earthbroadcast.com
>
>|||Will
1) What is the most efficient way to bring the current database state over
to test machines? What I would like to find is a way to apply just the
change logs to the database currently on the developer's machine. There
just aren't that many changes being made to the database, and the log files
are small.
A. Detach\Attach database or more secure BACKUP /RESTORE Database
Also take a look at this article
http://www.sql-server-performance.com/sql_server_log_shipping.asp
"Will" <DELETE_westes@.earthbroadcast.com> wrote in message
news:ex%232INmkFHA.576@.TK2MSFTNGP15.phx.gbl...
> Some of our developers want to automate some system for bringing over a
> copy
> of the production database to their workstations to use for test purposes.
> I want to break this down into two separate questions:
> 1) What is the most efficient way to bring the current database state over
> to test machines? What I would like to find is a way to apply just the
> change logs to the database currently on the developer's machine. There
> just aren't that many changes being made to the database, and the log
> files
> are small.
> Assuming the log file is backed up using the native Windows SQL backup
> utility on the production database, what is the procedure to apply those
> changes to the database on another machine?
> 2) How can this whole process be automated, so that the programmer could
> just run a script and expect that 10 minutes later his database is up to
> the
> current level
> Are there applications that would help to automate this whole process?
> --
> Will
> Internet: westes AT earthbroadcast.com
>
>
Fastest Way To Make Test Copy of Database
Some of our developers want to automate some system for bringing over a copy
of the production database to their workstations to use for test purposes.
I want to break this down into two separate questions:
1) What is the most efficient way to bring the current database state over
to test machines? What I would like to find is a way to apply just the
change logs to the database currently on the developer's machine. There
just aren't that many changes being made to the database, and the log files
are small.
Assuming the log file is backed up using the native Windows SQL backup
utility on the production database, what is the procedure to apply those
changes to the database on another machine?
2) How can this whole process be automated, so that the programmer could
just run a script and expect that 10 minutes later his database is up to the
current level
Are there applications that would help to automate this whole process?
Will
Internet: westes AT earthbroadcast.comIf the database is small you can write custom script to drop the developer
database and recreate it thru script given to developer.
step 1: Share the network location of backup on both machines
step 2: create linked server on the developer machine
step 3: write custom script on developer machine that go thru cursor like th
is
a) restore database with norecovery
b) declare c1 INSENSITIVE cursor for
select bmf.physical_device_name file_name
from sql2.msdb.dbo.backupset bs
inner join sql2.msdb.dbo.backupmediafamily bmf
on bmf.media_set_id = bs.media_set_id
where upper(bs.database_name) = upper(@.orig_db_name)
and backup_start_date > (getdate()-2)
and type ='L'
order by bs.backup_start_date
FOR READ ONLY
c) go thru cursor and apply all the Tlogs
EXEC ('RESTORE LOG ' + @.restore_db_name + ' from DISK =' + '''' + @.id +
'''' + ' with standby=' + '''' + 'c:\mssql\data\MSSQL\BACKUP\UNDO_' +
@.restore_db_name + '.DAT' + '''')
At the end restore database with recovery and you will have a DB as of last
tlog backup.
I do what you asked in question 2 but DB is open only as read only and
gets sync from production by custom scripts listed above.
If you have Enterprise edition you do not need to write the custom script as
it is in built in maintenance wizard
" wrote:
> Some of our developers want to automate some system for bringing over a co
py
> of the production database to their workstations to use for test purposes.
> I want to break this down into two separate questions:
> 1) What is the most efficient way to bring the current database state over
> to test machines? What I would like to find is a way to apply just the
> change logs to the database currently on the developer's machine. There
> just aren't that many changes being made to the database, and the log file
s
> are small.
> Assuming the log file is backed up using the native Windows SQL backup
> utility on the production database, what is the procedure to apply those
> changes to the database on another machine?
> 2) How can this whole process be automated, so that the programmer could
> just run a script and expect that 10 minutes later his database is up to t
he
> current level
> Are there applications that would help to automate this whole process?
> --
> Will
> Internet: westes AT earthbroadcast.com
>
>|||Will
1) What is the most efficient way to bring the current database state over
to test machines? What I would like to find is a way to apply just the
change logs to the database currently on the developer's machine. There
just aren't that many changes being made to the database, and the log files
are small.
A. Detach\Attach database or more secure BACKUP /RESTORE Database
Also take a look at this article
http://www.sql-server-performance.c...og_shipping.asp
"Will" <DELETE_westes@.earthbroadcast.com> wrote in message
news:ex%232INmkFHA.576@.TK2MSFTNGP15.phx.gbl...
> Some of our developers want to automate some system for bringing over a
> copy
> of the production database to their workstations to use for test purposes.
> I want to break this down into two separate questions:
> 1) What is the most efficient way to bring the current database state over
> to test machines? What I would like to find is a way to apply just the
> change logs to the database currently on the developer's machine. There
> just aren't that many changes being made to the database, and the log
> files
> are small.
> Assuming the log file is backed up using the native Windows SQL backup
> utility on the production database, what is the procedure to apply those
> changes to the database on another machine?
> 2) How can this whole process be automated, so that the programmer could
> just run a script and expect that 10 minutes later his database is up to
> the
> current level
> Are there applications that would help to automate this whole process?
> --
> Will
> Internet: westes AT earthbroadcast.com
>
>
of the production database to their workstations to use for test purposes.
I want to break this down into two separate questions:
1) What is the most efficient way to bring the current database state over
to test machines? What I would like to find is a way to apply just the
change logs to the database currently on the developer's machine. There
just aren't that many changes being made to the database, and the log files
are small.
Assuming the log file is backed up using the native Windows SQL backup
utility on the production database, what is the procedure to apply those
changes to the database on another machine?
2) How can this whole process be automated, so that the programmer could
just run a script and expect that 10 minutes later his database is up to the
current level
Are there applications that would help to automate this whole process?
Will
Internet: westes AT earthbroadcast.comIf the database is small you can write custom script to drop the developer
database and recreate it thru script given to developer.
step 1: Share the network location of backup on both machines
step 2: create linked server on the developer machine
step 3: write custom script on developer machine that go thru cursor like th
is
a) restore database with norecovery
b) declare c1 INSENSITIVE cursor for
select bmf.physical_device_name file_name
from sql2.msdb.dbo.backupset bs
inner join sql2.msdb.dbo.backupmediafamily bmf
on bmf.media_set_id = bs.media_set_id
where upper(bs.database_name) = upper(@.orig_db_name)
and backup_start_date > (getdate()-2)
and type ='L'
order by bs.backup_start_date
FOR READ ONLY
c) go thru cursor and apply all the Tlogs
EXEC ('RESTORE LOG ' + @.restore_db_name + ' from DISK =' + '''' + @.id +
'''' + ' with standby=' + '''' + 'c:\mssql\data\MSSQL\BACKUP\UNDO_' +
@.restore_db_name + '.DAT' + '''')
At the end restore database with recovery and you will have a DB as of last
tlog backup.
I do what you asked in question 2 but DB is open only as read only and
gets sync from production by custom scripts listed above.
If you have Enterprise edition you do not need to write the custom script as
it is in built in maintenance wizard
" wrote:
> Some of our developers want to automate some system for bringing over a co
py
> of the production database to their workstations to use for test purposes.
> I want to break this down into two separate questions:
> 1) What is the most efficient way to bring the current database state over
> to test machines? What I would like to find is a way to apply just the
> change logs to the database currently on the developer's machine. There
> just aren't that many changes being made to the database, and the log file
s
> are small.
> Assuming the log file is backed up using the native Windows SQL backup
> utility on the production database, what is the procedure to apply those
> changes to the database on another machine?
> 2) How can this whole process be automated, so that the programmer could
> just run a script and expect that 10 minutes later his database is up to t
he
> current level
> Are there applications that would help to automate this whole process?
> --
> Will
> Internet: westes AT earthbroadcast.com
>
>|||Will
1) What is the most efficient way to bring the current database state over
to test machines? What I would like to find is a way to apply just the
change logs to the database currently on the developer's machine. There
just aren't that many changes being made to the database, and the log files
are small.
A. Detach\Attach database or more secure BACKUP /RESTORE Database
Also take a look at this article
http://www.sql-server-performance.c...og_shipping.asp
"Will" <DELETE_westes@.earthbroadcast.com> wrote in message
news:ex%232INmkFHA.576@.TK2MSFTNGP15.phx.gbl...
> Some of our developers want to automate some system for bringing over a
> copy
> of the production database to their workstations to use for test purposes.
> I want to break this down into two separate questions:
> 1) What is the most efficient way to bring the current database state over
> to test machines? What I would like to find is a way to apply just the
> change logs to the database currently on the developer's machine. There
> just aren't that many changes being made to the database, and the log
> files
> are small.
> Assuming the log file is backed up using the native Windows SQL backup
> utility on the production database, what is the procedure to apply those
> changes to the database on another machine?
> 2) How can this whole process be automated, so that the programmer could
> just run a script and expect that 10 minutes later his database is up to
> the
> current level
> Are there applications that would help to automate this whole process?
> --
> Will
> Internet: westes AT earthbroadcast.com
>
>
Fastest Way To Make Test Copy of Database
Some of our developers want to automate some system for bringing over a copy
of the production database to their workstations to use for test purposes.
I want to break this down into two separate questions:
1) What is the most efficient way to bring the current database state over
to test machines? What I would like to find is a way to apply just the
change logs to the database currently on the developer's machine. There
just aren't that many changes being made to the database, and the log files
are small.
Assuming the log file is backed up using the native Windows SQL backup
utility on the production database, what is the procedure to apply those
changes to the database on another machine?
2) How can this whole process be automated, so that the programmer could
just run a script and expect that 10 minutes later his database is up to the
current level
Are there applications that would help to automate this whole process?
Will
Internet: westes AT earthbroadcast.comIf the database is small you can write custom script to drop the developer
database and recreate it thru script given to developer.
step 1: Share the network location of backup on both machines
step 2: create linked server on the developer machine
step 3: write custom script on developer machine that go thru cursor like th
is
a) restore database with norecovery
b) declare c1 INSENSITIVE cursor for
select bmf.physical_device_name file_name
from sql2.msdb.dbo.backupset bs
inner join sql2.msdb.dbo.backupmediafamily bmf
on bmf.media_set_id = bs.media_set_id
where upper(bs.database_name) = upper(@.orig_db_name)
and backup_start_date > (getdate()-2)
and type ='L'
order by bs.backup_start_date
FOR READ ONLY
c) go thru cursor and apply all the Tlogs
EXEC ('RESTORE LOG ' + @.restore_db_name + ' from DISK =' + '''' + @.id +
'''' + ' with standby=' + '''' + 'c:\mssql\data\MSSQL\BACKUP\UNDO_' +
@.restore_db_name + '.DAT' + '''')
At the end restore database with recovery and you will have a DB as of last
tlog backup.
I do what you asked in question 2 but DB is open only as read only and
gets sync from production by custom scripts listed above.
If you have Enterprise edition you do not need to write the custom script as
it is in built in maintenance wizard
" wrote:
> Some of our developers want to automate some system for bringing over a co
py
> of the production database to their workstations to use for test purposes.
> I want to break this down into two separate questions:
> 1) What is the most efficient way to bring the current database state over
> to test machines? What I would like to find is a way to apply just the
> change logs to the database currently on the developer's machine. There
> just aren't that many changes being made to the database, and the log file
s
> are small.
> Assuming the log file is backed up using the native Windows SQL backup
> utility on the production database, what is the procedure to apply those
> changes to the database on another machine?
> 2) How can this whole process be automated, so that the programmer could
> just run a script and expect that 10 minutes later his database is up to t
he
> current level
> Are there applications that would help to automate this whole process?
> --
> Will
> Internet: westes AT earthbroadcast.com
>
>|||Will
1) What is the most efficient way to bring the current database state over
to test machines? What I would like to find is a way to apply just the
change logs to the database currently on the developer's machine. There
just aren't that many changes being made to the database, and the log files
are small.
A. Detach\Attach database or more secure BACKUP /RESTORE Database
Also take a look at this article
http://www.sql-server-performance.c...og_shipping.asp
"Will" <DELETE_westes@.earthbroadcast.com> wrote in message
news:ex%232INmkFHA.576@.TK2MSFTNGP15.phx.gbl...
> Some of our developers want to automate some system for bringing over a
> copy
> of the production database to their workstations to use for test purposes.
> I want to break this down into two separate questions:
> 1) What is the most efficient way to bring the current database state over
> to test machines? What I would like to find is a way to apply just the
> change logs to the database currently on the developer's machine. There
> just aren't that many changes being made to the database, and the log
> files
> are small.
> Assuming the log file is backed up using the native Windows SQL backup
> utility on the production database, what is the procedure to apply those
> changes to the database on another machine?
> 2) How can this whole process be automated, so that the programmer could
> just run a script and expect that 10 minutes later his database is up to
> the
> current level
> Are there applications that would help to automate this whole process?
> --
> Will
> Internet: westes AT earthbroadcast.com
>
>
of the production database to their workstations to use for test purposes.
I want to break this down into two separate questions:
1) What is the most efficient way to bring the current database state over
to test machines? What I would like to find is a way to apply just the
change logs to the database currently on the developer's machine. There
just aren't that many changes being made to the database, and the log files
are small.
Assuming the log file is backed up using the native Windows SQL backup
utility on the production database, what is the procedure to apply those
changes to the database on another machine?
2) How can this whole process be automated, so that the programmer could
just run a script and expect that 10 minutes later his database is up to the
current level
Are there applications that would help to automate this whole process?
Will
Internet: westes AT earthbroadcast.comIf the database is small you can write custom script to drop the developer
database and recreate it thru script given to developer.
step 1: Share the network location of backup on both machines
step 2: create linked server on the developer machine
step 3: write custom script on developer machine that go thru cursor like th
is
a) restore database with norecovery
b) declare c1 INSENSITIVE cursor for
select bmf.physical_device_name file_name
from sql2.msdb.dbo.backupset bs
inner join sql2.msdb.dbo.backupmediafamily bmf
on bmf.media_set_id = bs.media_set_id
where upper(bs.database_name) = upper(@.orig_db_name)
and backup_start_date > (getdate()-2)
and type ='L'
order by bs.backup_start_date
FOR READ ONLY
c) go thru cursor and apply all the Tlogs
EXEC ('RESTORE LOG ' + @.restore_db_name + ' from DISK =' + '''' + @.id +
'''' + ' with standby=' + '''' + 'c:\mssql\data\MSSQL\BACKUP\UNDO_' +
@.restore_db_name + '.DAT' + '''')
At the end restore database with recovery and you will have a DB as of last
tlog backup.
I do what you asked in question 2 but DB is open only as read only and
gets sync from production by custom scripts listed above.
If you have Enterprise edition you do not need to write the custom script as
it is in built in maintenance wizard
" wrote:
> Some of our developers want to automate some system for bringing over a co
py
> of the production database to their workstations to use for test purposes.
> I want to break this down into two separate questions:
> 1) What is the most efficient way to bring the current database state over
> to test machines? What I would like to find is a way to apply just the
> change logs to the database currently on the developer's machine. There
> just aren't that many changes being made to the database, and the log file
s
> are small.
> Assuming the log file is backed up using the native Windows SQL backup
> utility on the production database, what is the procedure to apply those
> changes to the database on another machine?
> 2) How can this whole process be automated, so that the programmer could
> just run a script and expect that 10 minutes later his database is up to t
he
> current level
> Are there applications that would help to automate this whole process?
> --
> Will
> Internet: westes AT earthbroadcast.com
>
>|||Will
1) What is the most efficient way to bring the current database state over
to test machines? What I would like to find is a way to apply just the
change logs to the database currently on the developer's machine. There
just aren't that many changes being made to the database, and the log files
are small.
A. Detach\Attach database or more secure BACKUP /RESTORE Database
Also take a look at this article
http://www.sql-server-performance.c...og_shipping.asp
"Will" <DELETE_westes@.earthbroadcast.com> wrote in message
news:ex%232INmkFHA.576@.TK2MSFTNGP15.phx.gbl...
> Some of our developers want to automate some system for bringing over a
> copy
> of the production database to their workstations to use for test purposes.
> I want to break this down into two separate questions:
> 1) What is the most efficient way to bring the current database state over
> to test machines? What I would like to find is a way to apply just the
> change logs to the database currently on the developer's machine. There
> just aren't that many changes being made to the database, and the log
> files
> are small.
> Assuming the log file is backed up using the native Windows SQL backup
> utility on the production database, what is the procedure to apply those
> changes to the database on another machine?
> 2) How can this whole process be automated, so that the programmer could
> just run a script and expect that 10 minutes later his database is up to
> the
> current level
> Are there applications that would help to automate this whole process?
> --
> Will
> Internet: westes AT earthbroadcast.com
>
>
Friday, March 9, 2012
Faster Deletion from a production database
I need to delete several million transactions from a production database. I
have tried the SP below but it takes 4 1/2 hours to delete 5,000
transactions. Can someone please look over this and let me know if there is
someway to speed up this process? I can't truncate the table because I stil
l
need the most recent (6 months) worth of transactions. The remaining
transactions date back to 10/21/2003 and have been backed up and now need to
be deleted.
Thanks in advance,
Matt
CREATE PROC dbo.ArchiveProduction
(
@.CutOffDate DATETIME = NULL
)
AS
BEGIN
SET NOCOUNT ON
IF @.CutOffDate IS NULL
BEGIN
SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
END
ELSE
BEGIN
IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
BEGIN
RAISERROR ('Cannot delete transactions from last three months', 16, 1)
RETURN -1
END
END
BEGIN TRAN
-- Fill a temp table with the transaction id's to delete
DECLARE @.tmp table(TranID int, BatchID int, OutboundFileID int,
FileContentsID int, FileReceiveID int)
INSERT INTO @.tmp
SELECT t.[ID] as TranID, b.[ID] as BatchID, t.OutboundFileID, f.[ID] AS
FileContentsID,
r.[ID] AS FileReceiveID
FROM FileReceive r LEFT OUTER JOIN FileContents f ON r.[ID] =
f.FileReceiveID
LEFT OUTER JOIN Batches b ON f.[ID] = b.FileContentsID
LEFT OUTER JOIN Transactions t ON b.[ID] = t.BatchID
WHERE r.FileFindDate < @.CutOffDate
-- Acknowledgments
DELETE FROM dbo.Acknowledgments
WHERE TransactionID IN
(
Select TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Acknowledgments',
16, 1)
RETURN -1
END
-- OutboundTranSet
DELETE FROM dbo.OutboundTranSet
FROM dbo.OutboundTranSet o INNER JOIN dbo.OutboundBatch b
ON o.OutboundBatchID = b.[ID] INNER JOIN OutboundFile f
ON f.[ID] = b.FileID INNER JOIN dbo.Transactions t
ON f.[ID] = t.OutboundFileID
WHERE t.OutboundFileID IN
(
SELECT OutboundFileID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OutboundTranSet',
16, 1)
RETURN -1
END
-- OutboundBatch
DELETE FROM dbo.OutboundBatch
FROM dbo.OutboundBatch o INNER JOIN dbo.OutboundFile f
ON o.FileID = f.[ID] INNER JOIN dbo.Transactions t
ON f.[ID] = t.OutboundFileID
WHERE t.OutboundFileID IN
(
SELECT OutboundFileID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OutboundBatch',
16, 1)
RETURN -1
END
-- OutboundFile
DELETE FROM dbo.OutboundFile
WHERE [ID] IN
(
SELECT OutboundFileID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OutboundFile', 16,
1)
RETURN -1
END
-- PayorResponse
DELETE FROM dbo.PayorResponse
WHERE TransactionID IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.PayorResponse',
16, 1)
RETURN -1
END
-- ClaimInfo
DELETE FROM dbo.ClaimInfo
WHERE TransactionID IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.ClaimInfo', 16, 1)
RETURN -1
END
-- Transactions
-- AttachmentImages
DELETE FROM dbo.AttachmentImages
FROM dbo.AttachmentImages a INNER JOIN dbo.AttachmentsToClaims c
ON a.A2CID = c.[ID]
WHERE c.TranID IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.AttachmentImages',
16, 1)
RETURN -1
END
-- AttachmentsToClaims
DELETE FROM dbo.AttachmentsToClaims
WHERE TranID IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from
dbo.AttachmentsToClaims', 16, 1)
RETURN -1
END
-- ClaimsToAttachments
DELETE FROM dbo.ClaimsToAttachments
WHERE TranID IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from
dbo.ClaimsToAttachments', 16, 1)
RETURN -1
END
-- Transactions
DELETE FROM dbo.Transactions
WHERE [ID] IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Transactions', 16,
1)
RETURN -1
END
-- Batches
-- Batches
DELETE FROM dbo.Batches
WHERE [ID] IN
(
SELECT BatchID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Batches', 16, 1)
RETURN -1
END
-- FileContents
DELETE FROM dbo.FileContents
WHERE [ID] IN
(
SELECT FileContentsID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.FileContents', 16,
1)
RETURN -1
END
-- FileReceive
DELETE FROM dbo.FileReceive
WHERE [ID] IN
(
SELECT FileReceiveID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.FileReceive', 16, 1)
RETURN -1
END
IF @.@.TRANCOUNT > 0
BEGIN
COMMIT TRAN
RETURN 0
END
END
GO1) Don't use table variables for large amounts of data (more than say 500
rows), use temporary tables.
2) Use joins or EXISTS, not IN.
3) Create indexes on all columns that you join on, including those in the
temporary tables.
4) By the looks of it you can cut out quite a few joins, specially between
the OutboundFile and Transactions tables.
Jacco Schalkwijk
SQL Server MVP
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:E40D3532-FD70-43BE-A437-0B2168A71C75@.microsoft.com...
>I need to delete several million transactions from a production database.
>I
> have tried the SP below but it takes 4 1/2 hours to delete 5,000
> transactions. Can someone please look over this and let me know if there
> is
> someway to speed up this process? I can't truncate the table because I
> still
> need the most recent (6 months) worth of transactions. The remaining
> transactions date back to 10/21/2003 and have been backed up and now need
> to
> be deleted.
> Thanks in advance,
> Matt
>
> CREATE PROC dbo.ArchiveProduction
> (
> @.CutOffDate DATETIME = NULL
> )
> AS
> BEGIN
> SET NOCOUNT ON
> IF @.CutOffDate IS NULL
> BEGIN
> SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
> END
> ELSE
> BEGIN
> IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
> BEGIN
> RAISERROR ('Cannot delete transactions from last three months', 16, 1)
> RETURN -1
> END
> END
> BEGIN TRAN
> -- Fill a temp table with the transaction id's to delete
> DECLARE @.tmp table(TranID int, BatchID int, OutboundFileID int,
> FileContentsID int, FileReceiveID int)
> INSERT INTO @.tmp
> SELECT t.[ID] as TranID, b.[ID] as BatchID, t.OutboundFileID, f.[ID] AS
> FileContentsID,
> r.[ID] AS FileReceiveID
> FROM FileReceive r LEFT OUTER JOIN FileContents f ON r.[ID] =
> f.FileReceiveID
> LEFT OUTER JOIN Batches b ON f.[ID] = b.FileContentsID
> LEFT OUTER JOIN Transactions t ON b.[ID] = t.BatchID
> WHERE r.FileFindDate < @.CutOffDate
> -- Acknowledgments
> DELETE FROM dbo.Acknowledgments
> WHERE TransactionID IN
> (
> Select TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Acknowledgments',
> 16, 1)
> RETURN -1
> END
> -- OutboundTranSet
> DELETE FROM dbo.OutboundTranSet
> FROM dbo.OutboundTranSet o INNER JOIN dbo.OutboundBatch b
> ON o.OutboundBatchID = b.[ID] INNER JOIN OutboundFile f
> ON f.[ID] = b.FileID INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundTranSet',
> 16, 1)
> RETURN -1
> END
> -- OutboundBatch
> DELETE FROM dbo.OutboundBatch
> FROM dbo.OutboundBatch o INNER JOIN dbo.OutboundFile f
> ON o.FileID = f.[ID] INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundBatch',
> 16, 1)
> RETURN -1
> END
> -- OutboundFile
> DELETE FROM dbo.OutboundFile
> WHERE [ID] IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundFile', 16,
> 1)
> RETURN -1
> END
> -- PayorResponse
> DELETE FROM dbo.PayorResponse
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.PayorResponse',
> 16, 1)
> RETURN -1
> END
> -- ClaimInfo
> DELETE FROM dbo.ClaimInfo
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.ClaimInfo', 16, 1)
> RETURN -1
> END
> -- Transactions
> -- AttachmentImages
> DELETE FROM dbo.AttachmentImages
> FROM dbo.AttachmentImages a INNER JOIN dbo.AttachmentsToClaims c
> ON a.A2CID = c.[ID]
> WHERE c.TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.AttachmentImages',
> 16, 1)
> RETURN -1
> END
> -- AttachmentsToClaims
> DELETE FROM dbo.AttachmentsToClaims
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.AttachmentsToClaims', 16, 1)
> RETURN -1
> END
> -- ClaimsToAttachments
> DELETE FROM dbo.ClaimsToAttachments
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.ClaimsToAttachments', 16, 1)
> RETURN -1
> END
> -- Transactions
> DELETE FROM dbo.Transactions
> WHERE [ID] IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Transactions', 16,
> 1)
> RETURN -1
> END
> -- Batches
> -- Batches
> DELETE FROM dbo.Batches
> WHERE [ID] IN
> (
> SELECT BatchID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Batches', 16, 1)
> RETURN -1
> END
> -- FileContents
> DELETE FROM dbo.FileContents
> WHERE [ID] IN
> (
> SELECT FileContentsID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileContents', 16,
> 1)
> RETURN -1
> END
>
> -- FileReceive
> DELETE FROM dbo.FileReceive
> WHERE [ID] IN
> (
> SELECT FileReceiveID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileReceive', 16,
> 1)
> RETURN -1
> END
> IF @.@.TRANCOUNT > 0
> BEGIN
> COMMIT TRAN
> RETURN 0
> END
> END
> GO
>|||Thanks, Jacco! I'll give it a shot.
"Jacco Schalkwijk" wrote:
> 1) Don't use table variables for large amounts of data (more than say 500
> rows), use temporary tables.
> 2) Use joins or EXISTS, not IN.
> 3) Create indexes on all columns that you join on, including those in the
> temporary tables.
> 4) By the looks of it you can cut out quite a few joins, specially between
> the OutboundFile and Transactions tables.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:E40D3532-FD70-43BE-A437-0B2168A71C75@.microsoft.com...
>
>|||Depending on how many records are in the tmp Table, Your problem *might* be
because there are no indices on that tmp table. WHich means that each delet
e
must do a complete table scan on that table. SO, If there are a chunk of
records in there, I might suggest using four table variables instead, as
follows:
Declare @.TranIDs Table(TId Integer Primary Key Not Null)
Declare @.BatchesIDs Table(BId Integer Primary Key Not Null)
Declare @.FileIDs Table(FId Integer Primary Key Not Null)
Declare @.ContentIDs Table(CId Integer Primary Key Not Null)
Declare @.ReceiveIDs Table(RId Integer Primary Key Not Null)
And then use these tables in all the delete queries instead... This will
allow each delete query to use Indexes on Table variables and may improve
performace substantially..
Also I suggest you put the errhandling at the end, and just "Goto" a named
label... It'll make the SP much cleaner and easier to maintain, as so:
CREATE PROC dbo.ArchiveProduction
@.CutOffDate DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON
Declare @.Msg VarChar(300)
Declare @.Err Integer
Set @.CutOffDate = IsNUll(@.CutOffDate, DATEADD(mm, -6, CURRENT_TIMESTAMP))
Set @.Msg = 'Cannot delete transactions from last three months'
IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP) Goto ErrHandler
-- ----
Declare @.TranIDs Table(TId Integer Primary Key Not Null)
Declare @.BatchIDs Table(BId Integer Primary Key Not Null)
Declare @.FileIDs Table(FId Integer Primary Key Not Null)
Declare @.ContentIDs Table(CId Integer Primary Key Not Null)
Declare @.ReceiveIDs Table(RId Integer Primary Key Not Null)
BEGIN TRAN
-- Fill a temp table with the transaction id's to delete
INSERT @.ReceiveIDs(RId)
Select Distinct ID FROM FileReceive r
WHERE FileFindDate < @.CutOffDate
-- --
Insert @.ContentIDs(CId)
Select Distinct C.ID From FileContents C
Join @.ReceiveIDs R On R.RID = C.FileReceiveID
-- --
Insert @.BatchIDs(BId)
Select Distinct B.ID From Batches B
Join @.ContentIDs C On C.CId = B.FileContentsID
Insert @.TranIDs (TId)
Select Distinct ID From Transactions
Join @.BatchIDs B On B.Bid = T.ID
/*SELECT t.[ID] as TranID,
b.[ID] BatchID,
t.OutboundFileID,
f.[ID] FileContentsID,
r.[ID] FileReceiveID
FROM FileReceive r
LEFT JOIN FileContents f ON f.FileReceiveID = r.[ID]
LEFT JOIN Batches b ON b.FileContentsID = f.[ID]
LEFT JOIN Transactions t ON t.BatchID = b.[ID]
WHERE r.FileFindDate < @.CutOffDate */
Begin Transaction
-- Acknowledgments
'Error occured while deleting data from dbo.Acknowledgments'
DELETE dbo.Acknowledgments
WHERE TransactionID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundTranSet
Set @.Msg = 'Error occured while deleting data from dbo.OutboundTranSet'
DELETE dbo.OutboundTranSet
FROM dbo.OutboundTranSet o
JOIN dbo.OutboundBatch b ON o.OutboundBatchID = b.[ID]
JOIN OutboundFile f ON f.[ID] = b.FileID
JOIN dbo.Transactions t ON f.[ID] = t.OutboundFileID
WHERE t.ID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundBatch
Set @.Msg = 'Error occured while deleting data from dbo.OutboundBatch'
DELETE dbo.OutboundBatch
FROM dbo.OutboundBatch o
JOIN dbo.OutboundFile f ON o.FileID = f.[ID]
JOIN dbo.Transactions t ON f.[ID] = t.OutboundFileID
WHERE t.ID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundFile
Set @.Msg = 'Error occured while deleting data from dbo.OutboundFile'
DELETE dbo.OutboundFile
From OutboundFile F Join Transactions T
On T.OutboundFileID = F.ID
WHERE T.ID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- PayorResponse
Set @.Msg = 'Error occured while deleting data from dbo.PayorResponse'
DELETE dbo.PayorResponse
WHERE TransactionID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- ClaimInfo
Set @.Msg = 'Error occured while deleting data from dbo.ClaimInfo'
DELETE dbo.ClaimInfo
WHERE TransactionID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Transactions
-- AttachmentImages
Set @.Msg = 'Error occured while deleting data from dbo.AttachmentImages'
DELETE dbo.AttachmentImages
FROM dbo.AttachmentImages a
JOIN dbo.AttachmentsToClaims c
ON a.A2CID = c.[ID]
WHERE c.TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- AttachmentsToClaims
Set @.Msg = 'Error occured while deleting data from
dbo.AttachmentsToClaims'
DELETE FROM dbo.AttachmentsToClaims
WHERE TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- ClaimsToAttachments
Set @.Msg = 'Error occured while deleting data from
dbo.ClaimsToAttachments'
DELETE FROM dbo.ClaimsToAttachments
WHERE TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Transactions
Set @.Msg = 'Error occured while deleting data from dbo.Transactions'
DELETE dbo.Transactions
WHERE [ID] IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Batches
-- Batches
Set @.Msg = 'Error occured while deleting data from dbo.Batches'
DELETE dbo.Batches
WHERE [ID] IN (SELECT BatchID FROM @.BatchIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- FileContents
Set @.Msg = 'Error occured while deleting data from dbo.FileContents'
DELETE dbo.FileContents
WHERE [ID] IN (SELECT FID FROM @.ContentIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- FileReceive
Set @.Msg = 'Error occured while deleting data from dbo.FileReceive'
DELETE dbo.FileReceive
WHERE [ID] IN (SELECT RID FROM @.ReceiveIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
COMMIT TRAN
RETURN (0) -- This is the end of normal processing
-- ***********************************
ErrHandler:
If @.@.TranCount > 0 RollBack Transaction
Raiserror(@.Msg, 16, 1)
If @.Err = 0 Set @.Err = -1
Return(@.Err)|||oh oh, some extra stuff in there I neglected to delete.. Here's correctted
version...
CREATE PROC dbo.ArchiveProduction
@.CutOffDate DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON
Declare @.Msg VarChar(300)
Declare @.Err Integer
Set @.CutOffDate = IsNUll(@.CutOffDate, DATEADD(mm, -6, CURRENT_TIMESTAMP))
Set @.Msg = 'Cannot delete transactions from last three months'
IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP) Goto ErrHandler
-- ----
Declare @.TranIDs Table(TId Integer Primary Key Not Null)
Declare @.BatchIDs Table(BId Integer Primary Key Not Null)
Declare @.FileIDs Table(FId Integer Primary Key Not Null)
Declare @.ContentIDs Table(CId Integer Primary Key Not Null)
Declare @.ReceiveIDs Table(RId Integer Primary Key Not Null)
INSERT @.ReceiveIDs(RId)
Select Distinct ID FROM FileReceive r
WHERE FileFindDate < @.CutOffDate
-- --
Insert @.ContentIDs(CId)
Select Distinct C.ID From FileContents C
Join @.ReceiveIDs R On R.RID = C.FileReceiveID
-- --
Insert @.BatchIDs(BId)
Select Distinct B.ID From Batches B
Join @.ContentIDs C On C.CId = B.FileContentsID
Insert @.TranIDs (TId)
Select Distinct ID From Transactions
Join @.BatchIDs B On B.Bid = T.ID
Begin Transaction
-- Acknowledgments
'Error occured while deleting data from dbo.Acknowledgments'
DELETE dbo.Acknowledgments
WHERE TransactionID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundTranSet
Set @.Msg = 'Error occured while deleting data from dbo.OutboundTranSet'
DELETE dbo.OutboundTranSet
FROM dbo.OutboundTranSet o
JOIN dbo.OutboundBatch b ON o.OutboundBatchID = b.[ID]
JOIN OutboundFile f ON f.[ID] = b.FileID
JOIN dbo.Transactions t ON f.[ID] = t.OutboundFileID
WHERE t.ID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundBatch
Set @.Msg = 'Error occured while deleting data from dbo.OutboundBatch'
DELETE dbo.OutboundBatch
FROM dbo.OutboundBatch o
JOIN dbo.OutboundFile f ON o.FileID = f.[ID]
JOIN dbo.Transactions t ON f.[ID] = t.OutboundFileID
WHERE t.ID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundFile
Set @.Msg = 'Error occured while deleting data from dbo.OutboundFile'
DELETE dbo.OutboundFile
From OutboundFile F Join Transactions T
On T.OutboundFileID = F.ID
WHERE T.ID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- PayorResponse
Set @.Msg = 'Error occured while deleting data from dbo.PayorResponse'
DELETE dbo.PayorResponse
WHERE TransactionID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- ClaimInfo
Set @.Msg = 'Error occured while deleting data from dbo.ClaimInfo'
DELETE dbo.ClaimInfo
WHERE TransactionID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Transactions
-- AttachmentImages
Set @.Msg = 'Error occured while deleting data from dbo.AttachmentImages'
DELETE dbo.AttachmentImages
FROM dbo.AttachmentImages a
JOIN dbo.AttachmentsToClaims c
ON a.A2CID = c.[ID]
WHERE c.TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- AttachmentsToClaims
Set @.Msg = 'Error occured while deleting data from
dbo.AttachmentsToClaims'
DELETE FROM dbo.AttachmentsToClaims
WHERE TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- ClaimsToAttachments
Set @.Msg = 'Error occured while deleting data from
dbo.ClaimsToAttachments'
DELETE FROM dbo.ClaimsToAttachments
WHERE TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Transactions
Set @.Msg = 'Error occured while deleting data from dbo.Transactions'
DELETE dbo.Transactions
WHERE [ID] IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Batches
-- Batches
Set @.Msg = 'Error occured while deleting data from dbo.Batches'
DELETE dbo.Batches
WHERE [ID] IN (SELECT BatchID FROM @.BatchIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- FileContents
Set @.Msg = 'Error occured while deleting data from dbo.FileContents'
DELETE dbo.FileContents
WHERE [ID] IN (SELECT FID FROM @.ContentIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- FileReceive
Set @.Msg = 'Error occured while deleting data from dbo.FileReceive'
DELETE dbo.FileReceive
WHERE [ID] IN (SELECT RID FROM @.ReceiveIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
COMMIT TRAN
RETURN (0)
-- ***********************************
ErrHandler:
If @.@.TranCount > 0 RollBack Transaction
Raiserror(@.Msg, 16, 1)
If @.Err = 0 Set @.Err = -1
Return(@.Err)|||What is the recovery model on your database? Try setting to simple recovery.
Also, create a loop where you delete only 100,000 records in each iteration
followed by a checkpoint. You will also want to perform a backup just prior
to this.
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:E40D3532-FD70-43BE-A437-0B2168A71C75@.microsoft.com...
> I need to delete several million transactions from a production database.
I
> have tried the SP below but it takes 4 1/2 hours to delete 5,000
> transactions. Can someone please look over this and let me know if there
is
> someway to speed up this process? I can't truncate the table because I
still
> need the most recent (6 months) worth of transactions. The remaining
> transactions date back to 10/21/2003 and have been backed up and now need
to
> be deleted.
> Thanks in advance,
> Matt
>
> CREATE PROC dbo.ArchiveProduction
> (
> @.CutOffDate DATETIME = NULL
> )
> AS
> BEGIN
> SET NOCOUNT ON
> IF @.CutOffDate IS NULL
> BEGIN
> SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
> END
> ELSE
> BEGIN
> IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
> BEGIN
> RAISERROR ('Cannot delete transactions from last three months', 16, 1)
> RETURN -1
> END
> END
> BEGIN TRAN
> -- Fill a temp table with the transaction id's to delete
> DECLARE @.tmp table(TranID int, BatchID int, OutboundFileID int,
> FileContentsID int, FileReceiveID int)
> INSERT INTO @.tmp
> SELECT t.[ID] as TranID, b.[ID] as BatchID, t.OutboundFileID, f.[ID] AS
> FileContentsID,
> r.[ID] AS FileReceiveID
> FROM FileReceive r LEFT OUTER JOIN FileContents f ON r.[ID] =
> f.FileReceiveID
> LEFT OUTER JOIN Batches b ON f.[ID] = b.FileContentsID
> LEFT OUTER JOIN Transactions t ON b.[ID] = t.BatchID
> WHERE r.FileFindDate < @.CutOffDate
> -- Acknowledgments
> DELETE FROM dbo.Acknowledgments
> WHERE TransactionID IN
> (
> Select TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Acknowledgments',
> 16, 1)
> RETURN -1
> END
> -- OutboundTranSet
> DELETE FROM dbo.OutboundTranSet
> FROM dbo.OutboundTranSet o INNER JOIN dbo.OutboundBatch b
> ON o.OutboundBatchID = b.[ID] INNER JOIN OutboundFile f
> ON f.[ID] = b.FileID INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundTranSet',
> 16, 1)
> RETURN -1
> END
> -- OutboundBatch
> DELETE FROM dbo.OutboundBatch
> FROM dbo.OutboundBatch o INNER JOIN dbo.OutboundFile f
> ON o.FileID = f.[ID] INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundBatch',
> 16, 1)
> RETURN -1
> END
> -- OutboundFile
> DELETE FROM dbo.OutboundFile
> WHERE [ID] IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundFile', 16,
> 1)
> RETURN -1
> END
> -- PayorResponse
> DELETE FROM dbo.PayorResponse
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.PayorResponse',
> 16, 1)
> RETURN -1
> END
> -- ClaimInfo
> DELETE FROM dbo.ClaimInfo
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.ClaimInfo', 16, 1)
> RETURN -1
> END
> -- Transactions
> -- AttachmentImages
> DELETE FROM dbo.AttachmentImages
> FROM dbo.AttachmentImages a INNER JOIN dbo.AttachmentsToClaims c
> ON a.A2CID = c.[ID]
> WHERE c.TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.AttachmentImages',
> 16, 1)
> RETURN -1
> END
> -- AttachmentsToClaims
> DELETE FROM dbo.AttachmentsToClaims
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.AttachmentsToClaims', 16, 1)
> RETURN -1
> END
> -- ClaimsToAttachments
> DELETE FROM dbo.ClaimsToAttachments
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.ClaimsToAttachments', 16, 1)
> RETURN -1
> END
> -- Transactions
> DELETE FROM dbo.Transactions
> WHERE [ID] IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Transactions', 16,
> 1)
> RETURN -1
> END
> -- Batches
> -- Batches
> DELETE FROM dbo.Batches
> WHERE [ID] IN
> (
> SELECT BatchID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Batches', 16, 1)
> RETURN -1
> END
> -- FileContents
> DELETE FROM dbo.FileContents
> WHERE [ID] IN
> (
> SELECT FileContentsID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileContents', 16,
> 1)
> RETURN -1
> END
>
> -- FileReceive
> DELETE FROM dbo.FileReceive
> WHERE [ID] IN
> (
> SELECT FileReceiveID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileReceive', 16,
1)
> RETURN -1
> END
> IF @.@.TRANCOUNT > 0
> BEGIN
> COMMIT TRAN
> RETURN 0
> END
> END
> GO
>|||Thanks to all of you for your help. With your input the delete now only
takes 2 1/2 minutes to delete 5,000 transactions and all associated items.
"Matt" wrote:
> I need to delete several million transactions from a production database.
I
> have tried the SP below but it takes 4 1/2 hours to delete 5,000
> transactions. Can someone please look over this and let me know if there
is
> someway to speed up this process? I can't truncate the table because I st
ill
> need the most recent (6 months) worth of transactions. The remaining
> transactions date back to 10/21/2003 and have been backed up and now need
to
> be deleted.
> Thanks in advance,
> Matt
>
> CREATE PROC dbo.ArchiveProduction
> (
> @.CutOffDate DATETIME = NULL
> )
> AS
> BEGIN
> SET NOCOUNT ON
> IF @.CutOffDate IS NULL
> BEGIN
> SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
> END
> ELSE
> BEGIN
> IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
> BEGIN
> RAISERROR ('Cannot delete transactions from last three months', 16, 1)
> RETURN -1
> END
> END
> BEGIN TRAN
> -- Fill a temp table with the transaction id's to delete
> DECLARE @.tmp table(TranID int, BatchID int, OutboundFileID int,
> FileContentsID int, FileReceiveID int)
> INSERT INTO @.tmp
> SELECT t.[ID] as TranID, b.[ID] as BatchID, t.OutboundFileID, f.[ID] AS
> FileContentsID,
> r.[ID] AS FileReceiveID
> FROM FileReceive r LEFT OUTER JOIN FileContents f ON r.[ID] =
> f.FileReceiveID
> LEFT OUTER JOIN Batches b ON f.[ID] = b.FileContentsID
> LEFT OUTER JOIN Transactions t ON b.[ID] = t.BatchID
> WHERE r.FileFindDate < @.CutOffDate
> -- Acknowledgments
> DELETE FROM dbo.Acknowledgments
> WHERE TransactionID IN
> (
> Select TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Acknowledgments'
,
> 16, 1)
> RETURN -1
> END
> -- OutboundTranSet
> DELETE FROM dbo.OutboundTranSet
> FROM dbo.OutboundTranSet o INNER JOIN dbo.OutboundBatch b
> ON o.OutboundBatchID = b.[ID] INNER JOIN OutboundFile f
> ON f.[ID] = b.FileID INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundTranSet'
,
> 16, 1)
> RETURN -1
> END
> -- OutboundBatch
> DELETE FROM dbo.OutboundBatch
> FROM dbo.OutboundBatch o INNER JOIN dbo.OutboundFile f
> ON o.FileID = f.[ID] INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundBatch',
> 16, 1)
> RETURN -1
> END
> -- OutboundFile
> DELETE FROM dbo.OutboundFile
> WHERE [ID] IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundFile', 1
6,
> 1)
> RETURN -1
> END
> -- PayorResponse
> DELETE FROM dbo.PayorResponse
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.PayorResponse',
> 16, 1)
> RETURN -1
> END
> -- ClaimInfo
> DELETE FROM dbo.ClaimInfo
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.ClaimInfo', 16,
1)
> RETURN -1
> END
> -- Transactions
> -- AttachmentImages
> DELETE FROM dbo.AttachmentImages
> FROM dbo.AttachmentImages a INNER JOIN dbo.AttachmentsToClaims c
> ON a.A2CID = c.[ID]
> WHERE c.TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.AttachmentImages
',
> 16, 1)
> RETURN -1
> END
> -- AttachmentsToClaims
> DELETE FROM dbo.AttachmentsToClaims
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.AttachmentsToClaims', 16, 1)
> RETURN -1
> END
> -- ClaimsToAttachments
> DELETE FROM dbo.ClaimsToAttachments
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.ClaimsToAttachments', 16, 1)
> RETURN -1
> END
> -- Transactions
> DELETE FROM dbo.Transactions
> WHERE [ID] IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Transactions', 1
6,
> 1)
> RETURN -1
> END
> -- Batches
> -- Batches
> DELETE FROM dbo.Batches
> WHERE [ID] IN
> (
> SELECT BatchID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Batches', 16, 1)
> RETURN -1
> END
> -- FileContents
> DELETE FROM dbo.FileContents
> WHERE [ID] IN
> (
> SELECT FileContentsID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileContents', 1
6,
> 1)
> RETURN -1
> END
>
> -- FileReceive
> DELETE FROM dbo.FileReceive
> WHERE [ID] IN
> (
> SELECT FileReceiveID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileReceive', 16
, 1)
> RETURN -1
> END
> IF @.@.TRANCOUNT > 0
> BEGIN
> COMMIT TRAN
> RETURN 0
> END
> END
> GO
>
have tried the SP below but it takes 4 1/2 hours to delete 5,000
transactions. Can someone please look over this and let me know if there is
someway to speed up this process? I can't truncate the table because I stil
l
need the most recent (6 months) worth of transactions. The remaining
transactions date back to 10/21/2003 and have been backed up and now need to
be deleted.
Thanks in advance,
Matt
CREATE PROC dbo.ArchiveProduction
(
@.CutOffDate DATETIME = NULL
)
AS
BEGIN
SET NOCOUNT ON
IF @.CutOffDate IS NULL
BEGIN
SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
END
ELSE
BEGIN
IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
BEGIN
RAISERROR ('Cannot delete transactions from last three months', 16, 1)
RETURN -1
END
END
BEGIN TRAN
-- Fill a temp table with the transaction id's to delete
DECLARE @.tmp table(TranID int, BatchID int, OutboundFileID int,
FileContentsID int, FileReceiveID int)
INSERT INTO @.tmp
SELECT t.[ID] as TranID, b.[ID] as BatchID, t.OutboundFileID, f.[ID] AS
FileContentsID,
r.[ID] AS FileReceiveID
FROM FileReceive r LEFT OUTER JOIN FileContents f ON r.[ID] =
f.FileReceiveID
LEFT OUTER JOIN Batches b ON f.[ID] = b.FileContentsID
LEFT OUTER JOIN Transactions t ON b.[ID] = t.BatchID
WHERE r.FileFindDate < @.CutOffDate
-- Acknowledgments
DELETE FROM dbo.Acknowledgments
WHERE TransactionID IN
(
Select TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Acknowledgments',
16, 1)
RETURN -1
END
-- OutboundTranSet
DELETE FROM dbo.OutboundTranSet
FROM dbo.OutboundTranSet o INNER JOIN dbo.OutboundBatch b
ON o.OutboundBatchID = b.[ID] INNER JOIN OutboundFile f
ON f.[ID] = b.FileID INNER JOIN dbo.Transactions t
ON f.[ID] = t.OutboundFileID
WHERE t.OutboundFileID IN
(
SELECT OutboundFileID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OutboundTranSet',
16, 1)
RETURN -1
END
-- OutboundBatch
DELETE FROM dbo.OutboundBatch
FROM dbo.OutboundBatch o INNER JOIN dbo.OutboundFile f
ON o.FileID = f.[ID] INNER JOIN dbo.Transactions t
ON f.[ID] = t.OutboundFileID
WHERE t.OutboundFileID IN
(
SELECT OutboundFileID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OutboundBatch',
16, 1)
RETURN -1
END
-- OutboundFile
DELETE FROM dbo.OutboundFile
WHERE [ID] IN
(
SELECT OutboundFileID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OutboundFile', 16,
1)
RETURN -1
END
-- PayorResponse
DELETE FROM dbo.PayorResponse
WHERE TransactionID IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.PayorResponse',
16, 1)
RETURN -1
END
-- ClaimInfo
DELETE FROM dbo.ClaimInfo
WHERE TransactionID IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.ClaimInfo', 16, 1)
RETURN -1
END
-- Transactions
-- AttachmentImages
DELETE FROM dbo.AttachmentImages
FROM dbo.AttachmentImages a INNER JOIN dbo.AttachmentsToClaims c
ON a.A2CID = c.[ID]
WHERE c.TranID IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.AttachmentImages',
16, 1)
RETURN -1
END
-- AttachmentsToClaims
DELETE FROM dbo.AttachmentsToClaims
WHERE TranID IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from
dbo.AttachmentsToClaims', 16, 1)
RETURN -1
END
-- ClaimsToAttachments
DELETE FROM dbo.ClaimsToAttachments
WHERE TranID IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from
dbo.ClaimsToAttachments', 16, 1)
RETURN -1
END
-- Transactions
DELETE FROM dbo.Transactions
WHERE [ID] IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Transactions', 16,
1)
RETURN -1
END
-- Batches
-- Batches
DELETE FROM dbo.Batches
WHERE [ID] IN
(
SELECT BatchID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Batches', 16, 1)
RETURN -1
END
-- FileContents
DELETE FROM dbo.FileContents
WHERE [ID] IN
(
SELECT FileContentsID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.FileContents', 16,
1)
RETURN -1
END
-- FileReceive
DELETE FROM dbo.FileReceive
WHERE [ID] IN
(
SELECT FileReceiveID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.FileReceive', 16, 1)
RETURN -1
END
IF @.@.TRANCOUNT > 0
BEGIN
COMMIT TRAN
RETURN 0
END
END
GO1) Don't use table variables for large amounts of data (more than say 500
rows), use temporary tables.
2) Use joins or EXISTS, not IN.
3) Create indexes on all columns that you join on, including those in the
temporary tables.
4) By the looks of it you can cut out quite a few joins, specially between
the OutboundFile and Transactions tables.
Jacco Schalkwijk
SQL Server MVP
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:E40D3532-FD70-43BE-A437-0B2168A71C75@.microsoft.com...
>I need to delete several million transactions from a production database.
>I
> have tried the SP below but it takes 4 1/2 hours to delete 5,000
> transactions. Can someone please look over this and let me know if there
> is
> someway to speed up this process? I can't truncate the table because I
> still
> need the most recent (6 months) worth of transactions. The remaining
> transactions date back to 10/21/2003 and have been backed up and now need
> to
> be deleted.
> Thanks in advance,
> Matt
>
> CREATE PROC dbo.ArchiveProduction
> (
> @.CutOffDate DATETIME = NULL
> )
> AS
> BEGIN
> SET NOCOUNT ON
> IF @.CutOffDate IS NULL
> BEGIN
> SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
> END
> ELSE
> BEGIN
> IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
> BEGIN
> RAISERROR ('Cannot delete transactions from last three months', 16, 1)
> RETURN -1
> END
> END
> BEGIN TRAN
> -- Fill a temp table with the transaction id's to delete
> DECLARE @.tmp table(TranID int, BatchID int, OutboundFileID int,
> FileContentsID int, FileReceiveID int)
> INSERT INTO @.tmp
> SELECT t.[ID] as TranID, b.[ID] as BatchID, t.OutboundFileID, f.[ID] AS
> FileContentsID,
> r.[ID] AS FileReceiveID
> FROM FileReceive r LEFT OUTER JOIN FileContents f ON r.[ID] =
> f.FileReceiveID
> LEFT OUTER JOIN Batches b ON f.[ID] = b.FileContentsID
> LEFT OUTER JOIN Transactions t ON b.[ID] = t.BatchID
> WHERE r.FileFindDate < @.CutOffDate
> -- Acknowledgments
> DELETE FROM dbo.Acknowledgments
> WHERE TransactionID IN
> (
> Select TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Acknowledgments',
> 16, 1)
> RETURN -1
> END
> -- OutboundTranSet
> DELETE FROM dbo.OutboundTranSet
> FROM dbo.OutboundTranSet o INNER JOIN dbo.OutboundBatch b
> ON o.OutboundBatchID = b.[ID] INNER JOIN OutboundFile f
> ON f.[ID] = b.FileID INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundTranSet',
> 16, 1)
> RETURN -1
> END
> -- OutboundBatch
> DELETE FROM dbo.OutboundBatch
> FROM dbo.OutboundBatch o INNER JOIN dbo.OutboundFile f
> ON o.FileID = f.[ID] INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundBatch',
> 16, 1)
> RETURN -1
> END
> -- OutboundFile
> DELETE FROM dbo.OutboundFile
> WHERE [ID] IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundFile', 16,
> 1)
> RETURN -1
> END
> -- PayorResponse
> DELETE FROM dbo.PayorResponse
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.PayorResponse',
> 16, 1)
> RETURN -1
> END
> -- ClaimInfo
> DELETE FROM dbo.ClaimInfo
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.ClaimInfo', 16, 1)
> RETURN -1
> END
> -- Transactions
> -- AttachmentImages
> DELETE FROM dbo.AttachmentImages
> FROM dbo.AttachmentImages a INNER JOIN dbo.AttachmentsToClaims c
> ON a.A2CID = c.[ID]
> WHERE c.TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.AttachmentImages',
> 16, 1)
> RETURN -1
> END
> -- AttachmentsToClaims
> DELETE FROM dbo.AttachmentsToClaims
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.AttachmentsToClaims', 16, 1)
> RETURN -1
> END
> -- ClaimsToAttachments
> DELETE FROM dbo.ClaimsToAttachments
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.ClaimsToAttachments', 16, 1)
> RETURN -1
> END
> -- Transactions
> DELETE FROM dbo.Transactions
> WHERE [ID] IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Transactions', 16,
> 1)
> RETURN -1
> END
> -- Batches
> -- Batches
> DELETE FROM dbo.Batches
> WHERE [ID] IN
> (
> SELECT BatchID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Batches', 16, 1)
> RETURN -1
> END
> -- FileContents
> DELETE FROM dbo.FileContents
> WHERE [ID] IN
> (
> SELECT FileContentsID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileContents', 16,
> 1)
> RETURN -1
> END
>
> -- FileReceive
> DELETE FROM dbo.FileReceive
> WHERE [ID] IN
> (
> SELECT FileReceiveID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileReceive', 16,
> 1)
> RETURN -1
> END
> IF @.@.TRANCOUNT > 0
> BEGIN
> COMMIT TRAN
> RETURN 0
> END
> END
> GO
>|||Thanks, Jacco! I'll give it a shot.
"Jacco Schalkwijk" wrote:
> 1) Don't use table variables for large amounts of data (more than say 500
> rows), use temporary tables.
> 2) Use joins or EXISTS, not IN.
> 3) Create indexes on all columns that you join on, including those in the
> temporary tables.
> 4) By the looks of it you can cut out quite a few joins, specially between
> the OutboundFile and Transactions tables.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:E40D3532-FD70-43BE-A437-0B2168A71C75@.microsoft.com...
>
>|||Depending on how many records are in the tmp Table, Your problem *might* be
because there are no indices on that tmp table. WHich means that each delet
e
must do a complete table scan on that table. SO, If there are a chunk of
records in there, I might suggest using four table variables instead, as
follows:
Declare @.TranIDs Table(TId Integer Primary Key Not Null)
Declare @.BatchesIDs Table(BId Integer Primary Key Not Null)
Declare @.FileIDs Table(FId Integer Primary Key Not Null)
Declare @.ContentIDs Table(CId Integer Primary Key Not Null)
Declare @.ReceiveIDs Table(RId Integer Primary Key Not Null)
And then use these tables in all the delete queries instead... This will
allow each delete query to use Indexes on Table variables and may improve
performace substantially..
Also I suggest you put the errhandling at the end, and just "Goto" a named
label... It'll make the SP much cleaner and easier to maintain, as so:
CREATE PROC dbo.ArchiveProduction
@.CutOffDate DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON
Declare @.Msg VarChar(300)
Declare @.Err Integer
Set @.CutOffDate = IsNUll(@.CutOffDate, DATEADD(mm, -6, CURRENT_TIMESTAMP))
Set @.Msg = 'Cannot delete transactions from last three months'
IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP) Goto ErrHandler
-- ----
Declare @.TranIDs Table(TId Integer Primary Key Not Null)
Declare @.BatchIDs Table(BId Integer Primary Key Not Null)
Declare @.FileIDs Table(FId Integer Primary Key Not Null)
Declare @.ContentIDs Table(CId Integer Primary Key Not Null)
Declare @.ReceiveIDs Table(RId Integer Primary Key Not Null)
BEGIN TRAN
-- Fill a temp table with the transaction id's to delete
INSERT @.ReceiveIDs(RId)
Select Distinct ID FROM FileReceive r
WHERE FileFindDate < @.CutOffDate
-- --
Insert @.ContentIDs(CId)
Select Distinct C.ID From FileContents C
Join @.ReceiveIDs R On R.RID = C.FileReceiveID
-- --
Insert @.BatchIDs(BId)
Select Distinct B.ID From Batches B
Join @.ContentIDs C On C.CId = B.FileContentsID
Insert @.TranIDs (TId)
Select Distinct ID From Transactions
Join @.BatchIDs B On B.Bid = T.ID
/*SELECT t.[ID] as TranID,
b.[ID] BatchID,
t.OutboundFileID,
f.[ID] FileContentsID,
r.[ID] FileReceiveID
FROM FileReceive r
LEFT JOIN FileContents f ON f.FileReceiveID = r.[ID]
LEFT JOIN Batches b ON b.FileContentsID = f.[ID]
LEFT JOIN Transactions t ON t.BatchID = b.[ID]
WHERE r.FileFindDate < @.CutOffDate */
Begin Transaction
-- Acknowledgments
'Error occured while deleting data from dbo.Acknowledgments'
DELETE dbo.Acknowledgments
WHERE TransactionID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundTranSet
Set @.Msg = 'Error occured while deleting data from dbo.OutboundTranSet'
DELETE dbo.OutboundTranSet
FROM dbo.OutboundTranSet o
JOIN dbo.OutboundBatch b ON o.OutboundBatchID = b.[ID]
JOIN OutboundFile f ON f.[ID] = b.FileID
JOIN dbo.Transactions t ON f.[ID] = t.OutboundFileID
WHERE t.ID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundBatch
Set @.Msg = 'Error occured while deleting data from dbo.OutboundBatch'
DELETE dbo.OutboundBatch
FROM dbo.OutboundBatch o
JOIN dbo.OutboundFile f ON o.FileID = f.[ID]
JOIN dbo.Transactions t ON f.[ID] = t.OutboundFileID
WHERE t.ID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundFile
Set @.Msg = 'Error occured while deleting data from dbo.OutboundFile'
DELETE dbo.OutboundFile
From OutboundFile F Join Transactions T
On T.OutboundFileID = F.ID
WHERE T.ID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- PayorResponse
Set @.Msg = 'Error occured while deleting data from dbo.PayorResponse'
DELETE dbo.PayorResponse
WHERE TransactionID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- ClaimInfo
Set @.Msg = 'Error occured while deleting data from dbo.ClaimInfo'
DELETE dbo.ClaimInfo
WHERE TransactionID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Transactions
-- AttachmentImages
Set @.Msg = 'Error occured while deleting data from dbo.AttachmentImages'
DELETE dbo.AttachmentImages
FROM dbo.AttachmentImages a
JOIN dbo.AttachmentsToClaims c
ON a.A2CID = c.[ID]
WHERE c.TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- AttachmentsToClaims
Set @.Msg = 'Error occured while deleting data from
dbo.AttachmentsToClaims'
DELETE FROM dbo.AttachmentsToClaims
WHERE TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- ClaimsToAttachments
Set @.Msg = 'Error occured while deleting data from
dbo.ClaimsToAttachments'
DELETE FROM dbo.ClaimsToAttachments
WHERE TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Transactions
Set @.Msg = 'Error occured while deleting data from dbo.Transactions'
DELETE dbo.Transactions
WHERE [ID] IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Batches
-- Batches
Set @.Msg = 'Error occured while deleting data from dbo.Batches'
DELETE dbo.Batches
WHERE [ID] IN (SELECT BatchID FROM @.BatchIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- FileContents
Set @.Msg = 'Error occured while deleting data from dbo.FileContents'
DELETE dbo.FileContents
WHERE [ID] IN (SELECT FID FROM @.ContentIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- FileReceive
Set @.Msg = 'Error occured while deleting data from dbo.FileReceive'
DELETE dbo.FileReceive
WHERE [ID] IN (SELECT RID FROM @.ReceiveIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
COMMIT TRAN
RETURN (0) -- This is the end of normal processing
-- ***********************************
ErrHandler:
If @.@.TranCount > 0 RollBack Transaction
Raiserror(@.Msg, 16, 1)
If @.Err = 0 Set @.Err = -1
Return(@.Err)|||oh oh, some extra stuff in there I neglected to delete.. Here's correctted
version...
CREATE PROC dbo.ArchiveProduction
@.CutOffDate DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON
Declare @.Msg VarChar(300)
Declare @.Err Integer
Set @.CutOffDate = IsNUll(@.CutOffDate, DATEADD(mm, -6, CURRENT_TIMESTAMP))
Set @.Msg = 'Cannot delete transactions from last three months'
IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP) Goto ErrHandler
-- ----
Declare @.TranIDs Table(TId Integer Primary Key Not Null)
Declare @.BatchIDs Table(BId Integer Primary Key Not Null)
Declare @.FileIDs Table(FId Integer Primary Key Not Null)
Declare @.ContentIDs Table(CId Integer Primary Key Not Null)
Declare @.ReceiveIDs Table(RId Integer Primary Key Not Null)
INSERT @.ReceiveIDs(RId)
Select Distinct ID FROM FileReceive r
WHERE FileFindDate < @.CutOffDate
-- --
Insert @.ContentIDs(CId)
Select Distinct C.ID From FileContents C
Join @.ReceiveIDs R On R.RID = C.FileReceiveID
-- --
Insert @.BatchIDs(BId)
Select Distinct B.ID From Batches B
Join @.ContentIDs C On C.CId = B.FileContentsID
Insert @.TranIDs (TId)
Select Distinct ID From Transactions
Join @.BatchIDs B On B.Bid = T.ID
Begin Transaction
-- Acknowledgments
'Error occured while deleting data from dbo.Acknowledgments'
DELETE dbo.Acknowledgments
WHERE TransactionID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundTranSet
Set @.Msg = 'Error occured while deleting data from dbo.OutboundTranSet'
DELETE dbo.OutboundTranSet
FROM dbo.OutboundTranSet o
JOIN dbo.OutboundBatch b ON o.OutboundBatchID = b.[ID]
JOIN OutboundFile f ON f.[ID] = b.FileID
JOIN dbo.Transactions t ON f.[ID] = t.OutboundFileID
WHERE t.ID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundBatch
Set @.Msg = 'Error occured while deleting data from dbo.OutboundBatch'
DELETE dbo.OutboundBatch
FROM dbo.OutboundBatch o
JOIN dbo.OutboundFile f ON o.FileID = f.[ID]
JOIN dbo.Transactions t ON f.[ID] = t.OutboundFileID
WHERE t.ID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundFile
Set @.Msg = 'Error occured while deleting data from dbo.OutboundFile'
DELETE dbo.OutboundFile
From OutboundFile F Join Transactions T
On T.OutboundFileID = F.ID
WHERE T.ID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- PayorResponse
Set @.Msg = 'Error occured while deleting data from dbo.PayorResponse'
DELETE dbo.PayorResponse
WHERE TransactionID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- ClaimInfo
Set @.Msg = 'Error occured while deleting data from dbo.ClaimInfo'
DELETE dbo.ClaimInfo
WHERE TransactionID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Transactions
-- AttachmentImages
Set @.Msg = 'Error occured while deleting data from dbo.AttachmentImages'
DELETE dbo.AttachmentImages
FROM dbo.AttachmentImages a
JOIN dbo.AttachmentsToClaims c
ON a.A2CID = c.[ID]
WHERE c.TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- AttachmentsToClaims
Set @.Msg = 'Error occured while deleting data from
dbo.AttachmentsToClaims'
DELETE FROM dbo.AttachmentsToClaims
WHERE TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- ClaimsToAttachments
Set @.Msg = 'Error occured while deleting data from
dbo.ClaimsToAttachments'
DELETE FROM dbo.ClaimsToAttachments
WHERE TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Transactions
Set @.Msg = 'Error occured while deleting data from dbo.Transactions'
DELETE dbo.Transactions
WHERE [ID] IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Batches
-- Batches
Set @.Msg = 'Error occured while deleting data from dbo.Batches'
DELETE dbo.Batches
WHERE [ID] IN (SELECT BatchID FROM @.BatchIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- FileContents
Set @.Msg = 'Error occured while deleting data from dbo.FileContents'
DELETE dbo.FileContents
WHERE [ID] IN (SELECT FID FROM @.ContentIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- FileReceive
Set @.Msg = 'Error occured while deleting data from dbo.FileReceive'
DELETE dbo.FileReceive
WHERE [ID] IN (SELECT RID FROM @.ReceiveIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
COMMIT TRAN
RETURN (0)
-- ***********************************
ErrHandler:
If @.@.TranCount > 0 RollBack Transaction
Raiserror(@.Msg, 16, 1)
If @.Err = 0 Set @.Err = -1
Return(@.Err)|||What is the recovery model on your database? Try setting to simple recovery.
Also, create a loop where you delete only 100,000 records in each iteration
followed by a checkpoint. You will also want to perform a backup just prior
to this.
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:E40D3532-FD70-43BE-A437-0B2168A71C75@.microsoft.com...
> I need to delete several million transactions from a production database.
I
> have tried the SP below but it takes 4 1/2 hours to delete 5,000
> transactions. Can someone please look over this and let me know if there
is
> someway to speed up this process? I can't truncate the table because I
still
> need the most recent (6 months) worth of transactions. The remaining
> transactions date back to 10/21/2003 and have been backed up and now need
to
> be deleted.
> Thanks in advance,
> Matt
>
> CREATE PROC dbo.ArchiveProduction
> (
> @.CutOffDate DATETIME = NULL
> )
> AS
> BEGIN
> SET NOCOUNT ON
> IF @.CutOffDate IS NULL
> BEGIN
> SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
> END
> ELSE
> BEGIN
> IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
> BEGIN
> RAISERROR ('Cannot delete transactions from last three months', 16, 1)
> RETURN -1
> END
> END
> BEGIN TRAN
> -- Fill a temp table with the transaction id's to delete
> DECLARE @.tmp table(TranID int, BatchID int, OutboundFileID int,
> FileContentsID int, FileReceiveID int)
> INSERT INTO @.tmp
> SELECT t.[ID] as TranID, b.[ID] as BatchID, t.OutboundFileID, f.[ID] AS
> FileContentsID,
> r.[ID] AS FileReceiveID
> FROM FileReceive r LEFT OUTER JOIN FileContents f ON r.[ID] =
> f.FileReceiveID
> LEFT OUTER JOIN Batches b ON f.[ID] = b.FileContentsID
> LEFT OUTER JOIN Transactions t ON b.[ID] = t.BatchID
> WHERE r.FileFindDate < @.CutOffDate
> -- Acknowledgments
> DELETE FROM dbo.Acknowledgments
> WHERE TransactionID IN
> (
> Select TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Acknowledgments',
> 16, 1)
> RETURN -1
> END
> -- OutboundTranSet
> DELETE FROM dbo.OutboundTranSet
> FROM dbo.OutboundTranSet o INNER JOIN dbo.OutboundBatch b
> ON o.OutboundBatchID = b.[ID] INNER JOIN OutboundFile f
> ON f.[ID] = b.FileID INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundTranSet',
> 16, 1)
> RETURN -1
> END
> -- OutboundBatch
> DELETE FROM dbo.OutboundBatch
> FROM dbo.OutboundBatch o INNER JOIN dbo.OutboundFile f
> ON o.FileID = f.[ID] INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundBatch',
> 16, 1)
> RETURN -1
> END
> -- OutboundFile
> DELETE FROM dbo.OutboundFile
> WHERE [ID] IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundFile', 16,
> 1)
> RETURN -1
> END
> -- PayorResponse
> DELETE FROM dbo.PayorResponse
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.PayorResponse',
> 16, 1)
> RETURN -1
> END
> -- ClaimInfo
> DELETE FROM dbo.ClaimInfo
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.ClaimInfo', 16, 1)
> RETURN -1
> END
> -- Transactions
> -- AttachmentImages
> DELETE FROM dbo.AttachmentImages
> FROM dbo.AttachmentImages a INNER JOIN dbo.AttachmentsToClaims c
> ON a.A2CID = c.[ID]
> WHERE c.TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.AttachmentImages',
> 16, 1)
> RETURN -1
> END
> -- AttachmentsToClaims
> DELETE FROM dbo.AttachmentsToClaims
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.AttachmentsToClaims', 16, 1)
> RETURN -1
> END
> -- ClaimsToAttachments
> DELETE FROM dbo.ClaimsToAttachments
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.ClaimsToAttachments', 16, 1)
> RETURN -1
> END
> -- Transactions
> DELETE FROM dbo.Transactions
> WHERE [ID] IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Transactions', 16,
> 1)
> RETURN -1
> END
> -- Batches
> -- Batches
> DELETE FROM dbo.Batches
> WHERE [ID] IN
> (
> SELECT BatchID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Batches', 16, 1)
> RETURN -1
> END
> -- FileContents
> DELETE FROM dbo.FileContents
> WHERE [ID] IN
> (
> SELECT FileContentsID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileContents', 16,
> 1)
> RETURN -1
> END
>
> -- FileReceive
> DELETE FROM dbo.FileReceive
> WHERE [ID] IN
> (
> SELECT FileReceiveID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileReceive', 16,
1)
> RETURN -1
> END
> IF @.@.TRANCOUNT > 0
> BEGIN
> COMMIT TRAN
> RETURN 0
> END
> END
> GO
>|||Thanks to all of you for your help. With your input the delete now only
takes 2 1/2 minutes to delete 5,000 transactions and all associated items.
"Matt" wrote:
> I need to delete several million transactions from a production database.
I
> have tried the SP below but it takes 4 1/2 hours to delete 5,000
> transactions. Can someone please look over this and let me know if there
is
> someway to speed up this process? I can't truncate the table because I st
ill
> need the most recent (6 months) worth of transactions. The remaining
> transactions date back to 10/21/2003 and have been backed up and now need
to
> be deleted.
> Thanks in advance,
> Matt
>
> CREATE PROC dbo.ArchiveProduction
> (
> @.CutOffDate DATETIME = NULL
> )
> AS
> BEGIN
> SET NOCOUNT ON
> IF @.CutOffDate IS NULL
> BEGIN
> SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
> END
> ELSE
> BEGIN
> IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
> BEGIN
> RAISERROR ('Cannot delete transactions from last three months', 16, 1)
> RETURN -1
> END
> END
> BEGIN TRAN
> -- Fill a temp table with the transaction id's to delete
> DECLARE @.tmp table(TranID int, BatchID int, OutboundFileID int,
> FileContentsID int, FileReceiveID int)
> INSERT INTO @.tmp
> SELECT t.[ID] as TranID, b.[ID] as BatchID, t.OutboundFileID, f.[ID] AS
> FileContentsID,
> r.[ID] AS FileReceiveID
> FROM FileReceive r LEFT OUTER JOIN FileContents f ON r.[ID] =
> f.FileReceiveID
> LEFT OUTER JOIN Batches b ON f.[ID] = b.FileContentsID
> LEFT OUTER JOIN Transactions t ON b.[ID] = t.BatchID
> WHERE r.FileFindDate < @.CutOffDate
> -- Acknowledgments
> DELETE FROM dbo.Acknowledgments
> WHERE TransactionID IN
> (
> Select TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Acknowledgments'
,
> 16, 1)
> RETURN -1
> END
> -- OutboundTranSet
> DELETE FROM dbo.OutboundTranSet
> FROM dbo.OutboundTranSet o INNER JOIN dbo.OutboundBatch b
> ON o.OutboundBatchID = b.[ID] INNER JOIN OutboundFile f
> ON f.[ID] = b.FileID INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundTranSet'
,
> 16, 1)
> RETURN -1
> END
> -- OutboundBatch
> DELETE FROM dbo.OutboundBatch
> FROM dbo.OutboundBatch o INNER JOIN dbo.OutboundFile f
> ON o.FileID = f.[ID] INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundBatch',
> 16, 1)
> RETURN -1
> END
> -- OutboundFile
> DELETE FROM dbo.OutboundFile
> WHERE [ID] IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundFile', 1
6,
> 1)
> RETURN -1
> END
> -- PayorResponse
> DELETE FROM dbo.PayorResponse
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.PayorResponse',
> 16, 1)
> RETURN -1
> END
> -- ClaimInfo
> DELETE FROM dbo.ClaimInfo
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.ClaimInfo', 16,
1)
> RETURN -1
> END
> -- Transactions
> -- AttachmentImages
> DELETE FROM dbo.AttachmentImages
> FROM dbo.AttachmentImages a INNER JOIN dbo.AttachmentsToClaims c
> ON a.A2CID = c.[ID]
> WHERE c.TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.AttachmentImages
',
> 16, 1)
> RETURN -1
> END
> -- AttachmentsToClaims
> DELETE FROM dbo.AttachmentsToClaims
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.AttachmentsToClaims', 16, 1)
> RETURN -1
> END
> -- ClaimsToAttachments
> DELETE FROM dbo.ClaimsToAttachments
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.ClaimsToAttachments', 16, 1)
> RETURN -1
> END
> -- Transactions
> DELETE FROM dbo.Transactions
> WHERE [ID] IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Transactions', 1
6,
> 1)
> RETURN -1
> END
> -- Batches
> -- Batches
> DELETE FROM dbo.Batches
> WHERE [ID] IN
> (
> SELECT BatchID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Batches', 16, 1)
> RETURN -1
> END
> -- FileContents
> DELETE FROM dbo.FileContents
> WHERE [ID] IN
> (
> SELECT FileContentsID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileContents', 1
6,
> 1)
> RETURN -1
> END
>
> -- FileReceive
> DELETE FROM dbo.FileReceive
> WHERE [ID] IN
> (
> SELECT FileReceiveID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileReceive', 16
, 1)
> RETURN -1
> END
> IF @.@.TRANCOUNT > 0
> BEGIN
> COMMIT TRAN
> RETURN 0
> END
> END
> GO
>
Wednesday, March 7, 2012
faster box but slower database
Hi,
Got two SQL servers (production,dev) and I noticed recently that my
production servers is much slower when running one import stored
procedure. The stored proc only uses tables variables a lot and only
performs selects on actual tables. The specs are:
Productions server:
SQL 2000 v. 8002039 (SP4)
2x Dual Core Intel Xeon 3,2GH
1GB Memory
RAID 1
Windows 2003 Server
Development server:
SQL 2000 v. 800194 (no service pack)
1x Dual Core Intel Pentium 4 3GHz
Windows XP Professional
The stored procedures takes 20 sec running on the dev box but 2.4 min
on the production box.
All indexes and tables are the same in both databases. I tried
updating the stats, rebuild indexes, recompile the stored proc, free
the proc cache but without any luck.
Any help would be very much appricated.
KristjanOne thing I forgot to mention. The execution tree found in the
profiler differ for the same query in diffrent databases.|||You didn't mention how much ram is on the dev box.
Also how many rows and what is the DB size? 1GB of ram on a production SQL
box with that kind of CPU seems way low to me.
Mike
"kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
news:1138125661.913476.224860@.g14g2000cwa.googlegroups.com...
> Hi,
> Got two SQL servers (production,dev) and I noticed recently that my
> production servers is much slower when running one import stored
> procedure. The stored proc only uses tables variables a lot and only
> performs selects on actual tables. The specs are:
> Productions server:
> SQL 2000 v. 8002039 (SP4)
> 2x Dual Core Intel Xeon 3,2GH
> 1GB Memory
> RAID 1
> Windows 2003 Server
> Development server:
> SQL 2000 v. 800194 (no service pack)
> 1x Dual Core Intel Pentium 4 3GHz
> Windows XP Professional
> The stored procedures takes 20 sec running on the dev box but 2.4 min
> on the production box.
> All indexes and tables are the same in both databases. I tried
> updating the stats, rebuild indexes, recompile the stored proc, free
> the proc cache but without any luck.
> Any help would be very much appricated.
> Kristjan
>|||The dev box also got 1 GB ram. The databases are almost the same size
because the app is not writing very much on runtime.
The stored proc spends most time on single table containing about
600,000 rows (both dev and production server). By the way, the
profiler tells me the production servers is doing ~10 times more reads
and spending ~10 more CPU than the dev server.
The total databsize is about 0,5 GB but maybe 1/4 of it is accessed
realtime.|||What different you found in Execution plan?
Try to run query on Prod. using option (maxdop 1).
If you have joins check that fields on both sides of the join have same
datatypes.
Regards
Amish Shah|||Tried the maxdop 1 option, no big difference.
The datatypes were ok.
Installed the database on my laptop to reproduce this. With no service
pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
Seems like updated server optimizer is not doing a good job with my
stored proc. Collecting some more data which I will post to this
thread soon.|||There have been a number of changes from GOLD and SP1 to SP4. A number of
these fixes result in different query plans being generated. It is not a far
comparison of performance between GOLD and SP4. Your GOLD version is also a
lot less secure in that it does not protect against SLAMMER.
Chris
"kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
news:1138274581.095820.120850@.o13g2000cwo.googlegroups.com...
> Tried the maxdop 1 option, no big difference.
> The datatypes were ok.
> Installed the database on my laptop to reproduce this. With no service
> pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
> Seems like updated server optimizer is not doing a good job with my
> stored proc. Collecting some more data which I will post to this
> thread soon.
>|||This may sound silly, but have you opened the sproc on the SP4 box and
recompiled it on SP4. I have seen a few odd things from time to time when
sprocs were compiled on a lower SPack DB then restored to a differnt SPack
server. Views too.
At least you would give the SP4 query optimizer a chance to take a look at
the sproc to build a new plan to store with the sproc.
Mike
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:uu3f3HpIGHA.532@.TK2MSFTNGP15.phx.gbl...
> There have been a number of changes from GOLD and SP1 to SP4. A number of
> these fixes result in different query plans being generated. It is not a
> far comparison of performance between GOLD and SP4. Your GOLD version is
> also a lot less secure in that it does not protect against SLAMMER.
> Chris
> "kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
> news:1138274581.095820.120850@.o13g2000cwo.googlegroups.com...
>|||can you post the DDL and query you are running?|||Currently changing the query to "fit" the SP4 optimizer and think I
almost there, meaning the duration of the query is dropping to what I
expected. The changes sofar involved rewriting of several joins. I
get back to the thread in few hours or less - hopfully with result of
success. Thanks for all your replies. Its amazing to find the support
available on this group.
Got two SQL servers (production,dev) and I noticed recently that my
production servers is much slower when running one import stored
procedure. The stored proc only uses tables variables a lot and only
performs selects on actual tables. The specs are:
Productions server:
SQL 2000 v. 8002039 (SP4)
2x Dual Core Intel Xeon 3,2GH
1GB Memory
RAID 1
Windows 2003 Server
Development server:
SQL 2000 v. 800194 (no service pack)
1x Dual Core Intel Pentium 4 3GHz
Windows XP Professional
The stored procedures takes 20 sec running on the dev box but 2.4 min
on the production box.
All indexes and tables are the same in both databases. I tried
updating the stats, rebuild indexes, recompile the stored proc, free
the proc cache but without any luck.
Any help would be very much appricated.
KristjanOne thing I forgot to mention. The execution tree found in the
profiler differ for the same query in diffrent databases.|||You didn't mention how much ram is on the dev box.
Also how many rows and what is the DB size? 1GB of ram on a production SQL
box with that kind of CPU seems way low to me.
Mike
"kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
news:1138125661.913476.224860@.g14g2000cwa.googlegroups.com...
> Hi,
> Got two SQL servers (production,dev) and I noticed recently that my
> production servers is much slower when running one import stored
> procedure. The stored proc only uses tables variables a lot and only
> performs selects on actual tables. The specs are:
> Productions server:
> SQL 2000 v. 8002039 (SP4)
> 2x Dual Core Intel Xeon 3,2GH
> 1GB Memory
> RAID 1
> Windows 2003 Server
> Development server:
> SQL 2000 v. 800194 (no service pack)
> 1x Dual Core Intel Pentium 4 3GHz
> Windows XP Professional
> The stored procedures takes 20 sec running on the dev box but 2.4 min
> on the production box.
> All indexes and tables are the same in both databases. I tried
> updating the stats, rebuild indexes, recompile the stored proc, free
> the proc cache but without any luck.
> Any help would be very much appricated.
> Kristjan
>|||The dev box also got 1 GB ram. The databases are almost the same size
because the app is not writing very much on runtime.
The stored proc spends most time on single table containing about
600,000 rows (both dev and production server). By the way, the
profiler tells me the production servers is doing ~10 times more reads
and spending ~10 more CPU than the dev server.
The total databsize is about 0,5 GB but maybe 1/4 of it is accessed
realtime.|||What different you found in Execution plan?
Try to run query on Prod. using option (maxdop 1).
If you have joins check that fields on both sides of the join have same
datatypes.
Regards
Amish Shah|||Tried the maxdop 1 option, no big difference.
The datatypes were ok.
Installed the database on my laptop to reproduce this. With no service
pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
Seems like updated server optimizer is not doing a good job with my
stored proc. Collecting some more data which I will post to this
thread soon.|||There have been a number of changes from GOLD and SP1 to SP4. A number of
these fixes result in different query plans being generated. It is not a far
comparison of performance between GOLD and SP4. Your GOLD version is also a
lot less secure in that it does not protect against SLAMMER.
Chris
"kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
news:1138274581.095820.120850@.o13g2000cwo.googlegroups.com...
> Tried the maxdop 1 option, no big difference.
> The datatypes were ok.
> Installed the database on my laptop to reproduce this. With no service
> pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
> Seems like updated server optimizer is not doing a good job with my
> stored proc. Collecting some more data which I will post to this
> thread soon.
>|||This may sound silly, but have you opened the sproc on the SP4 box and
recompiled it on SP4. I have seen a few odd things from time to time when
sprocs were compiled on a lower SPack DB then restored to a differnt SPack
server. Views too.
At least you would give the SP4 query optimizer a chance to take a look at
the sproc to build a new plan to store with the sproc.
Mike
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:uu3f3HpIGHA.532@.TK2MSFTNGP15.phx.gbl...
> There have been a number of changes from GOLD and SP1 to SP4. A number of
> these fixes result in different query plans being generated. It is not a
> far comparison of performance between GOLD and SP4. Your GOLD version is
> also a lot less secure in that it does not protect against SLAMMER.
> Chris
> "kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
> news:1138274581.095820.120850@.o13g2000cwo.googlegroups.com...
>|||can you post the DDL and query you are running?|||Currently changing the query to "fit" the SP4 optimizer and think I
almost there, meaning the duration of the query is dropping to what I
expected. The changes sofar involved rewriting of several joins. I
get back to the thread in few hours or less - hopfully with result of
success. Thanks for all your replies. Its amazing to find the support
available on this group.
faster box but slower database
Hi,
Got two SQL servers (production,dev) and I noticed recently that my
production servers is much slower when running one import stored
procedure. The stored proc only uses tables variables a lot and only
performs selects on actual tables. The specs are:
Productions server:
SQL 2000 v. 8002039 (SP4)
2x Dual Core Intel Xeon 3,2GH
1GB Memory
RAID 1
Windows 2003 Server
Development server:
SQL 2000 v. 800194 (no service pack)
1x Dual Core Intel Pentium 4 3GHz
Windows XP Professional
The stored procedures takes 20 sec running on the dev box but 2.4 min
on the production box.
All indexes and tables are the same in both databases. I tried
updating the stats, rebuild indexes, recompile the stored proc, free
the proc cache but without any luck.
Any help would be very much appricated.
Kristjan
One thing I forgot to mention. The execution tree found in the
profiler differ for the same query in diffrent databases.
|||You didn't mention how much ram is on the dev box.
Also how many rows and what is the DB size? 1GB of ram on a production SQL
box with that kind of CPU seems way low to me.
Mike
"kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
news:1138125661.913476.224860@.g14g2000cwa.googlegr oups.com...
> Hi,
> Got two SQL servers (production,dev) and I noticed recently that my
> production servers is much slower when running one import stored
> procedure. The stored proc only uses tables variables a lot and only
> performs selects on actual tables. The specs are:
> Productions server:
> SQL 2000 v. 8002039 (SP4)
> 2x Dual Core Intel Xeon 3,2GH
> 1GB Memory
> RAID 1
> Windows 2003 Server
> Development server:
> SQL 2000 v. 800194 (no service pack)
> 1x Dual Core Intel Pentium 4 3GHz
> Windows XP Professional
> The stored procedures takes 20 sec running on the dev box but 2.4 min
> on the production box.
> All indexes and tables are the same in both databases. I tried
> updating the stats, rebuild indexes, recompile the stored proc, free
> the proc cache but without any luck.
> Any help would be very much appricated.
> Kristjan
>
|||The dev box also got 1 GB ram. The databases are almost the same size
because the app is not writing very much on runtime.
The stored proc spends most time on single table containing about
600,000 rows (both dev and production server). By the way, the
profiler tells me the production servers is doing ~10 times more reads
and spending ~10 more CPU than the dev server.
The total databsize is about 0,5 GB but maybe 1/4 of it is accessed
realtime.
|||What different you found in Execution plan?
Try to run query on Prod. using option (maxdop 1).
If you have joins check that fields on both sides of the join have same
datatypes.
Regards
Amish Shah
|||Tried the maxdop 1 option, no big difference.
The datatypes were ok.
Installed the database on my laptop to reproduce this. With no service
pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
Seems like updated server optimizer is not doing a good job with my
stored proc. Collecting some more data which I will post to this
thread soon.
|||There have been a number of changes from GOLD and SP1 to SP4. A number of
these fixes result in different query plans being generated. It is not a far
comparison of performance between GOLD and SP4. Your GOLD version is also a
lot less secure in that it does not protect against SLAMMER.
Chris
"kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
news:1138274581.095820.120850@.o13g2000cwo.googlegr oups.com...
> Tried the maxdop 1 option, no big difference.
> The datatypes were ok.
> Installed the database on my laptop to reproduce this. With no service
> pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
> Seems like updated server optimizer is not doing a good job with my
> stored proc. Collecting some more data which I will post to this
> thread soon.
>
|||This may sound silly, but have you opened the sproc on the SP4 box and
recompiled it on SP4. I have seen a few odd things from time to time when
sprocs were compiled on a lower SPack DB then restored to a differnt SPack
server. Views too.
At least you would give the SP4 query optimizer a chance to take a look at
the sproc to build a new plan to store with the sproc.
Mike
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:uu3f3HpIGHA.532@.TK2MSFTNGP15.phx.gbl...
> There have been a number of changes from GOLD and SP1 to SP4. A number of
> these fixes result in different query plans being generated. It is not a
> far comparison of performance between GOLD and SP4. Your GOLD version is
> also a lot less secure in that it does not protect against SLAMMER.
> Chris
> "kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
> news:1138274581.095820.120850@.o13g2000cwo.googlegr oups.com...
>
|||can you post the DDL and query you are running?
|||Currently changing the query to "fit" the SP4 optimizer and think I
almost there, meaning the duration of the query is dropping to what I
expected. The changes sofar involved rewriting of several joins. I
get back to the thread in few hours or less - hopfully with result of
success. Thanks for all your replies. Its amazing to find the support
available on this group.
Got two SQL servers (production,dev) and I noticed recently that my
production servers is much slower when running one import stored
procedure. The stored proc only uses tables variables a lot and only
performs selects on actual tables. The specs are:
Productions server:
SQL 2000 v. 8002039 (SP4)
2x Dual Core Intel Xeon 3,2GH
1GB Memory
RAID 1
Windows 2003 Server
Development server:
SQL 2000 v. 800194 (no service pack)
1x Dual Core Intel Pentium 4 3GHz
Windows XP Professional
The stored procedures takes 20 sec running on the dev box but 2.4 min
on the production box.
All indexes and tables are the same in both databases. I tried
updating the stats, rebuild indexes, recompile the stored proc, free
the proc cache but without any luck.
Any help would be very much appricated.
Kristjan
One thing I forgot to mention. The execution tree found in the
profiler differ for the same query in diffrent databases.
|||You didn't mention how much ram is on the dev box.
Also how many rows and what is the DB size? 1GB of ram on a production SQL
box with that kind of CPU seems way low to me.
Mike
"kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
news:1138125661.913476.224860@.g14g2000cwa.googlegr oups.com...
> Hi,
> Got two SQL servers (production,dev) and I noticed recently that my
> production servers is much slower when running one import stored
> procedure. The stored proc only uses tables variables a lot and only
> performs selects on actual tables. The specs are:
> Productions server:
> SQL 2000 v. 8002039 (SP4)
> 2x Dual Core Intel Xeon 3,2GH
> 1GB Memory
> RAID 1
> Windows 2003 Server
> Development server:
> SQL 2000 v. 800194 (no service pack)
> 1x Dual Core Intel Pentium 4 3GHz
> Windows XP Professional
> The stored procedures takes 20 sec running on the dev box but 2.4 min
> on the production box.
> All indexes and tables are the same in both databases. I tried
> updating the stats, rebuild indexes, recompile the stored proc, free
> the proc cache but without any luck.
> Any help would be very much appricated.
> Kristjan
>
|||The dev box also got 1 GB ram. The databases are almost the same size
because the app is not writing very much on runtime.
The stored proc spends most time on single table containing about
600,000 rows (both dev and production server). By the way, the
profiler tells me the production servers is doing ~10 times more reads
and spending ~10 more CPU than the dev server.
The total databsize is about 0,5 GB but maybe 1/4 of it is accessed
realtime.
|||What different you found in Execution plan?
Try to run query on Prod. using option (maxdop 1).
If you have joins check that fields on both sides of the join have same
datatypes.
Regards
Amish Shah
|||Tried the maxdop 1 option, no big difference.
The datatypes were ok.
Installed the database on my laptop to reproduce this. With no service
pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
Seems like updated server optimizer is not doing a good job with my
stored proc. Collecting some more data which I will post to this
thread soon.
|||There have been a number of changes from GOLD and SP1 to SP4. A number of
these fixes result in different query plans being generated. It is not a far
comparison of performance between GOLD and SP4. Your GOLD version is also a
lot less secure in that it does not protect against SLAMMER.
Chris
"kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
news:1138274581.095820.120850@.o13g2000cwo.googlegr oups.com...
> Tried the maxdop 1 option, no big difference.
> The datatypes were ok.
> Installed the database on my laptop to reproduce this. With no service
> pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
> Seems like updated server optimizer is not doing a good job with my
> stored proc. Collecting some more data which I will post to this
> thread soon.
>
|||This may sound silly, but have you opened the sproc on the SP4 box and
recompiled it on SP4. I have seen a few odd things from time to time when
sprocs were compiled on a lower SPack DB then restored to a differnt SPack
server. Views too.
At least you would give the SP4 query optimizer a chance to take a look at
the sproc to build a new plan to store with the sproc.
Mike
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:uu3f3HpIGHA.532@.TK2MSFTNGP15.phx.gbl...
> There have been a number of changes from GOLD and SP1 to SP4. A number of
> these fixes result in different query plans being generated. It is not a
> far comparison of performance between GOLD and SP4. Your GOLD version is
> also a lot less secure in that it does not protect against SLAMMER.
> Chris
> "kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
> news:1138274581.095820.120850@.o13g2000cwo.googlegr oups.com...
>
|||can you post the DDL and query you are running?
|||Currently changing the query to "fit" the SP4 optimizer and think I
almost there, meaning the duration of the query is dropping to what I
expected. The changes sofar involved rewriting of several joins. I
get back to the thread in few hours or less - hopfully with result of
success. Thanks for all your replies. Its amazing to find the support
available on this group.
Subscribe to:
Posts (Atom)