Showing posts with label specifically. Show all posts
Showing posts with label specifically. Show all posts

Friday, March 23, 2012

Optimizations Plan Slow

I am looking for some general best practices regarding how often to run an
optimations plan/job, and what options I should choose. Specifically, I am
have very inconsistent results on a nightly job I have set to run
optimizations on my database, ranging anywhere from 5 minutes to 100+
minutes. The daily changes to the database itself do not vary *that*
drastically (it's basically the same activity each day), so I'm trying to
figure out 1) why it takes so long in the first place, and 2) why the
duration varies so much from day to day.
The job is set to run nightly, around 4AM. I have "Reorganize data and
index pages" checked, w/ the "Change free space per page percentage to"
option set to 10%. I also have "Remove unused space from database files"
checked, w/ the "Shrink database when it grows beyond" option set to 50MB.
Currently, the size of the entire database is between 1GB and 2GB, though
this used to be over 4GB until I dropped/readded an index on a table that
has its records archived frequently -- this alone freed up almost 3GB.
The reason I need to minimize the time it takes for this job, is because
while the job is running, my web site is pretty much hosed. At one time,
4AM was off-peak hours, so this was not as much an issue. But now, the site
gets quite a bit of activity from another part of the globe, so 4AM is no
longer "off-peak" -- there really isn't an off-peak now. So this job is
causing problems for the non-regional visitors during this time.
I'm not sure if this is enough info for you to provide valuable feedback,
but if there's anything else I could provide to help you help me, let me
know.
Thanks in advance.
JeradPartial answer:
You want to avoid fragmenting SQL Server database files on disk if at all
possible. Database files that expand, especially in relatively small
increments, will become fragmented. The varying degree of fragmentation MAY
contribute to your varying run times.
I would suggest expanding your database to a reasonable size, (preferably to
it's expected stable size, or at least allowing 12 month's growth). Then
remove the SHRINK database options.
For a database this size do you really need to re-establish the index
fill-factor on a daily basis? That is, do users' experience performance
degradation within hours? If not, then you may be advised to schedule this
reindexing work less frequently, perhaps at weekends which are often
quieter. At the end of the working day run DBCC SHOWCONTIG - if the Scan
Density will fall below, say, 40% byb the nd of week, then index work may be
needed. Even then, consider DBCC INDEXDEFRAG which is an online operation-
less intrusive and can be interrupted. (See BOL for further info.)
Good luck.
Mike P.
"Jerad Rose" <no@.spam.com> wrote in message
news:OwZe2xGuFHA.4080@.TK2MSFTNGP12.phx.gbl...
> I am looking for some general best practices regarding how often to run an
> optimations plan/job, and what options I should choose. Specifically, I
am
> have very inconsistent results on a nightly job I have set to run
> optimizations on my database, ranging anywhere from 5 minutes to 100+
> minutes. The daily changes to the database itself do not vary *that*
> drastically (it's basically the same activity each day), so I'm trying to
> figure out 1) why it takes so long in the first place, and 2) why the
> duration varies so much from day to day.
> The job is set to run nightly, around 4AM. I have "Reorganize data and
> index pages" checked, w/ the "Change free space per page percentage to"
> option set to 10%. I also have "Remove unused space from database files"
> checked, w/ the "Shrink database when it grows beyond" option set to 50MB.
> Currently, the size of the entire database is between 1GB and 2GB, though
> this used to be over 4GB until I dropped/readded an index on a table that
> has its records archived frequently -- this alone freed up almost 3GB.
> The reason I need to minimize the time it takes for this job, is because
> while the job is running, my web site is pretty much hosed. At one time,
> 4AM was off-peak hours, so this was not as much an issue. But now, the
site
> gets quite a bit of activity from another part of the globe, so 4AM is no
> longer "off-peak" -- there really isn't an off-peak now. So this job is
> causing problems for the non-regional visitors during this time.
> I'm not sure if this is enough info for you to provide valuable feedback,
> but if there's anything else I could provide to help you help me, let me
> know.
> Thanks in advance.
> Jerad
>|||Hi Mike.
Thanks for the response. What you said made a lot of sense, and got me to
doing a little more research on index fragments. I found a really good
article that helped me understand why index fragments occur, and what can be
done to prevent/minimze them:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
I have since disabled this job, and have set up a view (report) that I now
monitor to check for excessive index fragments, and then use DBCC
INDEXDEFRAG to reduce the fragments on those tables. The article discusses
an article on BOL that provides a script you can use to automatically defrag
any indexes that has fragments exceeding a given threshold. But for now, I
want to monitor my tables to see which ones are frequently being fragmented,
and maybe redesign the table/indexes to reduce the fragments, instead of
constantly defragging these indexes -- if possible.
Thanks again for your help.
Jerad
"Mike P" <mikep@.aicorporation.com__NO_SPAM> wrote in message
news:uJzcNiIuFHA.2848@.TK2MSFTNGP10.phx.gbl...
> Partial answer:
> You want to avoid fragmenting SQL Server database files on disk if at all
> possible. Database files that expand, especially in relatively small
> increments, will become fragmented. The varying degree of fragmentation
> MAY
> contribute to your varying run times.
> I would suggest expanding your database to a reasonable size, (preferably
> to
> it's expected stable size, or at least allowing 12 month's growth). Then
> remove the SHRINK database options.
> For a database this size do you really need to re-establish the index
> fill-factor on a daily basis? That is, do users' experience performance
> degradation within hours? If not, then you may be advised to schedule
> this
> reindexing work less frequently, perhaps at weekends which are often
> quieter. At the end of the working day run DBCC SHOWCONTIG - if the Scan
> Density will fall below, say, 40% byb the nd of week, then index work may
> be
> needed. Even then, consider DBCC INDEXDEFRAG which is an online
> operation-
> less intrusive and can be interrupted. (See BOL for further info.)
> Good luck.
> Mike P.
> "Jerad Rose" <no@.spam.com> wrote in message
> news:OwZe2xGuFHA.4080@.TK2MSFTNGP12.phx.gbl...
>> I am looking for some general best practices regarding how often to run
>> an
>> optimations plan/job, and what options I should choose. Specifically, I
> am
>> have very inconsistent results on a nightly job I have set to run
>> optimizations on my database, ranging anywhere from 5 minutes to 100+
>> minutes. The daily changes to the database itself do not vary *that*
>> drastically (it's basically the same activity each day), so I'm trying to
>> figure out 1) why it takes so long in the first place, and 2) why the
>> duration varies so much from day to day.
>> The job is set to run nightly, around 4AM. I have "Reorganize data and
>> index pages" checked, w/ the "Change free space per page percentage to"
>> option set to 10%. I also have "Remove unused space from database files"
>> checked, w/ the "Shrink database when it grows beyond" option set to
>> 50MB.
>> Currently, the size of the entire database is between 1GB and 2GB, though
>> this used to be over 4GB until I dropped/readded an index on a table that
>> has its records archived frequently -- this alone freed up almost 3GB.
>> The reason I need to minimize the time it takes for this job, is because
>> while the job is running, my web site is pretty much hosed. At one time,
>> 4AM was off-peak hours, so this was not as much an issue. But now, the
> site
>> gets quite a bit of activity from another part of the globe, so 4AM is no
>> longer "off-peak" -- there really isn't an off-peak now. So this job is
>> causing problems for the non-regional visitors during this time.
>> I'm not sure if this is enough info for you to provide valuable feedback,
>> but if there's anything else I could provide to help you help me, let me
>> know.
>> Thanks in advance.
>> Jerad
>>
>sql

Optimizations Plan Slow

I am looking for some general best practices regarding how often to run an
optimations plan/job, and what options I should choose. Specifically, I am
have very inconsistent results on a nightly job I have set to run
optimizations on my database, ranging anywhere from 5 minutes to 100+
minutes. The daily changes to the database itself do not vary *that*
drastically (it's basically the same activity each day), so I'm trying to
figure out 1) why it takes so long in the first place, and 2) why the
duration varies so much from day to day.
The job is set to run nightly, around 4AM. I have "Reorganize data and
index pages" checked, w/ the "Change free space per page percentage to"
option set to 10%. I also have "Remove unused space from database files"
checked, w/ the "Shrink database when it grows beyond" option set to 50MB.
Currently, the size of the entire database is between 1GB and 2GB, though
this used to be over 4GB until I dropped/readded an index on a table that
has its records archived frequently -- this alone freed up almost 3GB.
The reason I need to minimize the time it takes for this job, is because
while the job is running, my web site is pretty much hosed. At one time,
4AM was off-peak hours, so this was not as much an issue. But now, the site
gets quite a bit of activity from another part of the globe, so 4AM is no
longer "off-peak" -- there really isn't an off-peak now. So this job is
causing problems for the non-regional visitors during this time.
I'm not sure if this is enough info for you to provide valuable feedback,
but if there's anything else I could provide to help you help me, let me
know.
Thanks in advance.
Jerad
Partial answer:
You want to avoid fragmenting SQL Server database files on disk if at all
possible. Database files that expand, especially in relatively small
increments, will become fragmented. The varying degree of fragmentation MAY
contribute to your varying run times.
I would suggest expanding your database to a reasonable size, (preferably to
it's expected stable size, or at least allowing 12 month's growth). Then
remove the SHRINK database options.
For a database this size do you really need to re-establish the index
fill-factor on a daily basis? That is, do users' experience performance
degradation within hours? If not, then you may be advised to schedule this
reindexing work less frequently, perhaps at weekends which are often
quieter. At the end of the working day run DBCC SHOWCONTIG - if the Scan
Density will fall below, say, 40% byb the nd of week, then index work may be
needed. Even then, consider DBCC INDEXDEFRAG which is an online operation-
less intrusive and can be interrupted. (See BOL for further info.)
Good luck.
Mike P.
"Jerad Rose" <no@.spam.com> wrote in message
news:OwZe2xGuFHA.4080@.TK2MSFTNGP12.phx.gbl...
> I am looking for some general best practices regarding how often to run an
> optimations plan/job, and what options I should choose. Specifically, I
am
> have very inconsistent results on a nightly job I have set to run
> optimizations on my database, ranging anywhere from 5 minutes to 100+
> minutes. The daily changes to the database itself do not vary *that*
> drastically (it's basically the same activity each day), so I'm trying to
> figure out 1) why it takes so long in the first place, and 2) why the
> duration varies so much from day to day.
> The job is set to run nightly, around 4AM. I have "Reorganize data and
> index pages" checked, w/ the "Change free space per page percentage to"
> option set to 10%. I also have "Remove unused space from database files"
> checked, w/ the "Shrink database when it grows beyond" option set to 50MB.
> Currently, the size of the entire database is between 1GB and 2GB, though
> this used to be over 4GB until I dropped/readded an index on a table that
> has its records archived frequently -- this alone freed up almost 3GB.
> The reason I need to minimize the time it takes for this job, is because
> while the job is running, my web site is pretty much hosed. At one time,
> 4AM was off-peak hours, so this was not as much an issue. But now, the
site
> gets quite a bit of activity from another part of the globe, so 4AM is no
> longer "off-peak" -- there really isn't an off-peak now. So this job is
> causing problems for the non-regional visitors during this time.
> I'm not sure if this is enough info for you to provide valuable feedback,
> but if there's anything else I could provide to help you help me, let me
> know.
> Thanks in advance.
> Jerad
>
|||Hi Mike.
Thanks for the response. What you said made a lot of sense, and got me to
doing a little more research on index fragments. I found a really good
article that helped me understand why index fragments occur, and what can be
done to prevent/minimze them:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
I have since disabled this job, and have set up a view (report) that I now
monitor to check for excessive index fragments, and then use DBCC
INDEXDEFRAG to reduce the fragments on those tables. The article discusses
an article on BOL that provides a script you can use to automatically defrag
any indexes that has fragments exceeding a given threshold. But for now, I
want to monitor my tables to see which ones are frequently being fragmented,
and maybe redesign the table/indexes to reduce the fragments, instead of
constantly defragging these indexes -- if possible.
Thanks again for your help.
Jerad
"Mike P" <mikep@.aicorporation.com__NO_SPAM> wrote in message
news:uJzcNiIuFHA.2848@.TK2MSFTNGP10.phx.gbl...
> Partial answer:
> You want to avoid fragmenting SQL Server database files on disk if at all
> possible. Database files that expand, especially in relatively small
> increments, will become fragmented. The varying degree of fragmentation
> MAY
> contribute to your varying run times.
> I would suggest expanding your database to a reasonable size, (preferably
> to
> it's expected stable size, or at least allowing 12 month's growth). Then
> remove the SHRINK database options.
> For a database this size do you really need to re-establish the index
> fill-factor on a daily basis? That is, do users' experience performance
> degradation within hours? If not, then you may be advised to schedule
> this
> reindexing work less frequently, perhaps at weekends which are often
> quieter. At the end of the working day run DBCC SHOWCONTIG - if the Scan
> Density will fall below, say, 40% byb the nd of week, then index work may
> be
> needed. Even then, consider DBCC INDEXDEFRAG which is an online
> operation-
> less intrusive and can be interrupted. (See BOL for further info.)
> Good luck.
> Mike P.
> "Jerad Rose" <no@.spam.com> wrote in message
> news:OwZe2xGuFHA.4080@.TK2MSFTNGP12.phx.gbl...
> am
> site
>

Optimizations Plan Slow

I am looking for some general best practices regarding how often to run an
optimations plan/job, and what options I should choose. Specifically, I am
have very inconsistent results on a nightly job I have set to run
optimizations on my database, ranging anywhere from 5 minutes to 100+
minutes. The daily changes to the database itself do not vary *that*
drastically (it's basically the same activity each day), so I'm trying to
figure out 1) why it takes so long in the first place, and 2) why the
duration varies so much from day to day.
The job is set to run nightly, around 4AM. I have "Reorganize data and
index pages" checked, w/ the "Change free space per page percentage to"
option set to 10%. I also have "Remove unused space from database files"
checked, w/ the "Shrink database when it grows beyond" option set to 50MB.
Currently, the size of the entire database is between 1GB and 2GB, though
this used to be over 4GB until I dropped/readded an index on a table that
has its records archived frequently -- this alone freed up almost 3GB.
The reason I need to minimize the time it takes for this job, is because
while the job is running, my web site is pretty much hosed. At one time,
4AM was off-peak hours, so this was not as much an issue. But now, the site
gets quite a bit of activity from another part of the globe, so 4AM is no
longer "off-peak" -- there really isn't an off-peak now. So this job is
causing problems for the non-regional visitors during this time.
I'm not sure if this is enough info for you to provide valuable feedback,
but if there's anything else I could provide to help you help me, let me
know.
Thanks in advance.
JeradPartial answer:
You want to avoid fragmenting SQL Server database files on disk if at all
possible. Database files that expand, especially in relatively small
increments, will become fragmented. The varying degree of fragmentation MAY
contribute to your varying run times.
I would suggest expanding your database to a reasonable size, (preferably to
it's expected stable size, or at least allowing 12 month's growth). Then
remove the SHRINK database options.
For a database this size do you really need to re-establish the index
fill-factor on a daily basis? That is, do users' experience performance
degradation within hours? If not, then you may be advised to schedule this
reindexing work less frequently, perhaps at weekends which are often
quieter. At the end of the working day run DBCC SHOWCONTIG - if the Scan
Density will fall below, say, 40% byb the nd of week, then index work may be
needed. Even then, consider DBCC INDEXDEFRAG which is an online operation-
less intrusive and can be interrupted. (See BOL for further info.)
Good luck.
Mike P.
"Jerad Rose" <no@.spam.com> wrote in message
news:OwZe2xGuFHA.4080@.TK2MSFTNGP12.phx.gbl...
> I am looking for some general best practices regarding how often to run an
> optimations plan/job, and what options I should choose. Specifically, I
am
> have very inconsistent results on a nightly job I have set to run
> optimizations on my database, ranging anywhere from 5 minutes to 100+
> minutes. The daily changes to the database itself do not vary *that*
> drastically (it's basically the same activity each day), so I'm trying to
> figure out 1) why it takes so long in the first place, and 2) why the
> duration varies so much from day to day.
> The job is set to run nightly, around 4AM. I have "Reorganize data and
> index pages" checked, w/ the "Change free space per page percentage to"
> option set to 10%. I also have "Remove unused space from database files"
> checked, w/ the "Shrink database when it grows beyond" option set to 50MB.
> Currently, the size of the entire database is between 1GB and 2GB, though
> this used to be over 4GB until I dropped/readded an index on a table that
> has its records archived frequently -- this alone freed up almost 3GB.
> The reason I need to minimize the time it takes for this job, is because
> while the job is running, my web site is pretty much hosed. At one time,
> 4AM was off-peak hours, so this was not as much an issue. But now, the
site
> gets quite a bit of activity from another part of the globe, so 4AM is no
> longer "off-peak" -- there really isn't an off-peak now. So this job is
> causing problems for the non-regional visitors during this time.
> I'm not sure if this is enough info for you to provide valuable feedback,
> but if there's anything else I could provide to help you help me, let me
> know.
> Thanks in advance.
> Jerad
>|||Hi Mike.
Thanks for the response. What you said made a lot of sense, and got me to
doing a little more research on index fragments. I found a really good
article that helped me understand why index fragments occur, and what can be
done to prevent/minimze them:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
I have since disabled this job, and have set up a view (report) that I now
monitor to check for excessive index fragments, and then use DBCC
INDEXDEFRAG to reduce the fragments on those tables. The article discusses
an article on BOL that provides a script you can use to automatically defrag
any indexes that has fragments exceeding a given threshold. But for now, I
want to monitor my tables to see which ones are frequently being fragmented,
and maybe redesign the table/indexes to reduce the fragments, instead of
constantly defragging these indexes -- if possible.
Thanks again for your help.
Jerad
"Mike P" <mikep@.aicorporation.com__NO_SPAM> wrote in message
news:uJzcNiIuFHA.2848@.TK2MSFTNGP10.phx.gbl...
> Partial answer:
> You want to avoid fragmenting SQL Server database files on disk if at all
> possible. Database files that expand, especially in relatively small
> increments, will become fragmented. The varying degree of fragmentation
> MAY
> contribute to your varying run times.
> I would suggest expanding your database to a reasonable size, (preferably
> to
> it's expected stable size, or at least allowing 12 month's growth). Then
> remove the SHRINK database options.
> For a database this size do you really need to re-establish the index
> fill-factor on a daily basis? That is, do users' experience performance
> degradation within hours? If not, then you may be advised to schedule
> this
> reindexing work less frequently, perhaps at weekends which are often
> quieter. At the end of the working day run DBCC SHOWCONTIG - if the Scan
> Density will fall below, say, 40% byb the nd of week, then index work may
> be
> needed. Even then, consider DBCC INDEXDEFRAG which is an online
> operation-
> less intrusive and can be interrupted. (See BOL for further info.)
> Good luck.
> Mike P.
> "Jerad Rose" <no@.spam.com> wrote in message
> news:OwZe2xGuFHA.4080@.TK2MSFTNGP12.phx.gbl...
> am
> site
>

Tuesday, March 20, 2012

optimization

I cannot make the database optimize properly. I have a
maintenance plan setup specifically to do just that, but
it is never able to run and our database is becoming
extremely slow. Any ideas?What errors are being reported by sqlmaint - should be in one of the txt =filesin the sql server logs directory. Youcan find the filename buy =looking at the details of the scheduled job that runs the optimisatons =job.
If the optimisations job i snever running it will man indexes etc may be =less than optimal but your phrase "extremely slow" makes me suspect =something else is wrong as well.
Mike John
"Kristen" <anonymous@.discussions.microsoft.com> wrote in message =news:025e01c3a7d4$d3af1d00$a501280a@.phx.gbl...
> I cannot make the database optimize properly. I have a > maintenance plan setup specifically to do just that, but > it is never able to run and our database is becoming > extremely slow. Any ideas?|||As far as I can tell, there are no errors, it is just not
doing it. In the History file, it tells me that it is
checking the db, but it is not able to rebuild indexes,
which my boss thinks is the reason the db is so very slow.
It backs up the db without any problem, but it cannot
back up the transaction log...
As you can probably tell, I am fairly new to SQL server...
Anything I can do to fix it?
>--Original Message--
>What errors are being reported by sqlmaint - should be
in one of the txt filesin the sql server logs directory.
Youcan find the filename buy looking at the details of
the scheduled job that runs the optimisatons job.
>If the optimisations job i snever running it will man
indexes etc may be less than optimal but your
phrase "extremely slow" makes me suspect something else
is wrong as well.
>Mike John
>
>"Kristen" <anonymous@.discussions.microsoft.com> wrote in
message news:025e01c3a7d4$d3af1d00$a501280a@.phx.gbl...
>> I cannot make the database optimize properly. I have
a
>> maintenance plan setup specifically to do just that,
but
>> it is never able to run and our database is becoming
>> extremely slow. Any ideas?
>.
>|||> As far as I can tell, there are no errors, it is just not
> doing it. In the History file, it tells me that it is
> checking the db, but it is not able to rebuild indexes,
How do you know it isn't rebuilding indexes if there are no error messages?
We need the error message from the rtp file.
> It backs up the db without any problem, but it cannot
> back up the transaction log...
That is probably not related. My guess is that the database is in simple
recovery mode. read more in Books Online about backup to understand recovery
mode and ability to do log backup.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Kristen" <anonymous@.discussions.microsoft.com> wrote in message
news:0b3d01c3a7df$dc7d7160$a001280a@.phx.gbl...
> As far as I can tell, there are no errors, it is just not
> doing it. In the History file, it tells me that it is
> checking the db, but it is not able to rebuild indexes,
> which my boss thinks is the reason the db is so very slow.
> It backs up the db without any problem, but it cannot
> back up the transaction log...
> As you can probably tell, I am fairly new to SQL server...
> Anything I can do to fix it?
> >--Original Message--
> >What errors are being reported by sqlmaint - should be
> in one of the txt filesin the sql server logs directory.
> Youcan find the filename buy looking at the details of
> the scheduled job that runs the optimisatons job.
> >
> >If the optimisations job i snever running it will man
> indexes etc may be less than optimal but your
> phrase "extremely slow" makes me suspect something else
> is wrong as well.
> >
> >Mike John
> >
> >
> >"Kristen" <anonymous@.discussions.microsoft.com> wrote in
> message news:025e01c3a7d4$d3af1d00$a501280a@.phx.gbl...
> >> I cannot make the database optimize properly. I have
> a
> >> maintenance plan setup specifically to do just that,
> but
> >> it is never able to run and our database is becoming
> >> extremely slow. Any ideas?
> >.
> >|||Kristen
Have you tried using dbcc showcontig, this will show you
if your tables are fragmented. (See BOL for details). If
they are bably fragmented you will know the main plan is
not working.
Another thought is that in your maint plan have you chosen
the option 'attempt to repair minor problems' if you have
remove it. This regularly causes problems with maint
plans. Any open connections and your plan will not run
properly with this option.
Hope this helps
John|||The reason I know it is not rebuilding indexes is because
the history tells me so. There is always a red X next to
rebuild indexes indicating it was not done. I looked
through every error log at the times it reported that
rebuilding indexes was not done and it said nothing about
rebuilding indexes. That is why I say there is no error
message. If there is a different place I should be
looking please tell me because I do not know what an rtp
file is.
>--Original Message--
>> As far as I can tell, there are no errors, it is just
not
>> doing it. In the History file, it tells me that it is
>> checking the db, but it is not able to rebuild indexes,
>How do you know it isn't rebuilding indexes if there are
no error messages?
>We need the error message from the rtp file.
>
>> It backs up the db without any problem, but it cannot
>> back up the transaction log...
>That is probably not related. My guess is that the
database is in simple
>recovery mode. read more in Books Online about backup to
understand recovery
>mode and ability to do log backup.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Kristen" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0b3d01c3a7df$dc7d7160$a001280a@.phx.gbl...
>> As far as I can tell, there are no errors, it is just
not
>> doing it. In the History file, it tells me that it is
>> checking the db, but it is not able to rebuild indexes,
>> which my boss thinks is the reason the db is so very
slow.
>> It backs up the db without any problem, but it cannot
>> back up the transaction log...
>> As you can probably tell, I am fairly new to SQL
server...
>> Anything I can do to fix it?
>> >--Original Message--
>> >What errors are being reported by sqlmaint - should be
>> in one of the txt filesin the sql server logs
directory.
>> Youcan find the filename buy looking at the details of
>> the scheduled job that runs the optimisatons job.
>> >
>> >If the optimisations job i snever running it will man
>> indexes etc may be less than optimal but your
>> phrase "extremely slow" makes me suspect something else
>> is wrong as well.
>> >
>> >Mike John
>> >
>> >
>> >"Kristen" <anonymous@.discussions.microsoft.com> wrote
in
>> message news:025e01c3a7d4$d3af1d00$a501280a@.phx.gbl...
>> >> I cannot make the database optimize properly. I
have
>> a
>> >> maintenance plan setup specifically to do just that,
>> but
>> >> it is never able to run and our database is becoming
>> >> extremely slow. Any ideas?
>> >.
>> >
>
>.
>|||Hi,
Thanks for the suggestion about the showcontig. I will
try that later. I checked the 'attempt to repair minor
problems' in all three of our maint plans and none of
them have this option checked.
Thanks,
Kristen
>--Original Message--
>Kristen
>Have you tried using dbcc showcontig, this will show you
>if your tables are fragmented. (See BOL for details). If
>they are bably fragmented you will know the main plan is
>not working.
>Another thought is that in your maint plan have you
chosen
>the option 'attempt to repair minor problems' if you
have
>remove it. This regularly causes problems with maint
>plans. Any open connections and your plan will not run
>properly with this option.
>Hope this helps
>John
>.
>|||The main wizard can create a report file. Check the config of the maint
plan, make sure that you have specified a report file and check that report
file for error messages.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Kristen" <anonymous@.discussions.microsoft.com> wrote in message
news:024701c3a87a$cdfe2fd0$a301280a@.phx.gbl...
> The reason I know it is not rebuilding indexes is because
> the history tells me so. There is always a red X next to
> rebuild indexes indicating it was not done. I looked
> through every error log at the times it reported that
> rebuilding indexes was not done and it said nothing about
> rebuilding indexes. That is why I say there is no error
> message. If there is a different place I should be
> looking please tell me because I do not know what an rtp
> file is.
>
> >--Original Message--
> >> As far as I can tell, there are no errors, it is just
> not
> >> doing it. In the History file, it tells me that it is
> >> checking the db, but it is not able to rebuild indexes,
> >
> >How do you know it isn't rebuilding indexes if there are
> no error messages?
> >We need the error message from the rtp file.
> >
> >
> >> It backs up the db without any problem, but it cannot
> >> back up the transaction log...
> >
> >That is probably not related. My guess is that the
> database is in simple
> >recovery mode. read more in Books Online about backup to
> understand recovery
> >mode and ability to do log backup.
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Kristen" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0b3d01c3a7df$dc7d7160$a001280a@.phx.gbl...
> >> As far as I can tell, there are no errors, it is just
> not
> >> doing it. In the History file, it tells me that it is
> >> checking the db, but it is not able to rebuild indexes,
> >> which my boss thinks is the reason the db is so very
> slow.
> >>
> >> It backs up the db without any problem, but it cannot
> >> back up the transaction log...
> >>
> >> As you can probably tell, I am fairly new to SQL
> server...
> >>
> >> Anything I can do to fix it?
> >>
> >> >--Original Message--
> >> >What errors are being reported by sqlmaint - should be
> >> in one of the txt filesin the sql server logs
> directory.
> >> Youcan find the filename buy looking at the details of
> >> the scheduled job that runs the optimisatons job.
> >> >
> >> >If the optimisations job i snever running it will man
> >> indexes etc may be less than optimal but your
> >> phrase "extremely slow" makes me suspect something else
> >> is wrong as well.
> >> >
> >> >Mike John
> >> >
> >> >
> >> >"Kristen" <anonymous@.discussions.microsoft.com> wrote
> in
> >> message news:025e01c3a7d4$d3af1d00$a501280a@.phx.gbl...
> >> >> I cannot make the database optimize properly. I
> have
> >> a
> >> >> maintenance plan setup specifically to do just that,
> >> but
> >> >> it is never able to run and our database is becoming
> >> >> extremely slow. Any ideas?
> >> >.
> >> >
> >
> >
> >.
> >|||Thank you!
Here it is:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC
failed because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
What does it mean?
>--Original Message--
>The main wizard can create a report file. Check the
config of the maint
>plan, make sure that you have specified a report file
and check that report
>file for error messages.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Kristen" <anonymous@.discussions.microsoft.com> wrote in
message
>news:024701c3a87a$cdfe2fd0$a301280a@.phx.gbl...
>> The reason I know it is not rebuilding indexes is
because
>> the history tells me so. There is always a red X next
to
>> rebuild indexes indicating it was not done. I looked
>> through every error log at the times it reported that
>> rebuilding indexes was not done and it said nothing
about
>> rebuilding indexes. That is why I say there is no
error
>> message. If there is a different place I should be
>> looking please tell me because I do not know what an
rtp
>> file is.
>>
>> >--Original Message--
>> >> As far as I can tell, there are no errors, it is
just
>> not
>> >> doing it. In the History file, it tells me that it
is
>> >> checking the db, but it is not able to rebuild
indexes,
>> >
>> >How do you know it isn't rebuilding indexes if there
are
>> no error messages?
>> >We need the error message from the rtp file.
>> >
>> >
>> >> It backs up the db without any problem, but it
cannot
>> >> back up the transaction log...
>> >
>> >That is probably not related. My guess is that the
>> database is in simple
>> >recovery mode. read more in Books Online about backup
to
>> understand recovery
>> >mode and ability to do log backup.
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at:
>> >http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"Kristen" <anonymous@.discussions.microsoft.com> wrote
in
>> message
>> >news:0b3d01c3a7df$dc7d7160$a001280a@.phx.gbl...
>> >> As far as I can tell, there are no errors, it is
just
>> not
>> >> doing it. In the History file, it tells me that it
is
>> >> checking the db, but it is not able to rebuild
indexes,
>> >> which my boss thinks is the reason the db is so very
>> slow.
>> >>
>> >> It backs up the db without any problem, but it
cannot
>> >> back up the transaction log...
>> >>
>> >> As you can probably tell, I am fairly new to SQL
>> server...
>> >>
>> >> Anything I can do to fix it?
>> >>
>> >> >--Original Message--
>> >> >What errors are being reported by sqlmaint -
should be
>> >> in one of the txt filesin the sql server logs
>> directory.
>> >> Youcan find the filename buy looking at the details
of
>> >> the scheduled job that runs the optimisatons job.
>> >> >
>> >> >If the optimisations job i snever running it will
man
>> >> indexes etc may be less than optimal but your
>> >> phrase "extremely slow" makes me suspect something
else
>> >> is wrong as well.
>> >> >
>> >> >Mike John
>> >> >
>> >> >
>> >> >"Kristen" <anonymous@.discussions.microsoft.com>
wrote
>> in
>> >> message news:025e01c3a7d4$d3af1d00
$a501280a@.phx.gbl...
>> >> >> I cannot make the database optimize properly. I
>> have
>> >> a
>> >> >> maintenance plan setup specifically to do just
that,
>> >> but
>> >> >> it is never able to run and our database is
becoming
>> >> >> extremely slow. Any ideas?
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||It means that you either has a view with index on or a computed column with
an index on. And for these to be manipulated (index rebuilt in this case),
the session which executes that manipulation command (maintenance wizard
executing DBCC DBREINDEX in this case) need to have the SET commands
mentioned in the error message turned on. Unfortunately, the maint wizard
doesn't do that. Your options it to remove the reorg from the maint wizard
and do it yourself from an Agent TSQL jobstep (having the correct SET
options), or remove those indexes.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Kristen" <anonymous@.discussions.microsoft.com> wrote in message
news:047f01c3a894$427adc00$a301280a@.phx.gbl...
> Thank you!
> Here it is:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
> [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC
> failed because the following SET options have incorrect
> settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> What does it mean?
> >--Original Message--
> >The main wizard can create a report file. Check the
> config of the maint
> >plan, make sure that you have specified a report file
> and check that report
> >file for error messages.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Kristen" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:024701c3a87a$cdfe2fd0$a301280a@.phx.gbl...
> >> The reason I know it is not rebuilding indexes is
> because
> >> the history tells me so. There is always a red X next
> to
> >> rebuild indexes indicating it was not done. I looked
> >> through every error log at the times it reported that
> >> rebuilding indexes was not done and it said nothing
> about
> >> rebuilding indexes. That is why I say there is no
> error
> >> message. If there is a different place I should be
> >> looking please tell me because I do not know what an
> rtp
> >> file is.
> >>
> >>
> >> >--Original Message--
> >> >> As far as I can tell, there are no errors, it is
> just
> >> not
> >> >> doing it. In the History file, it tells me that it
> is
> >> >> checking the db, but it is not able to rebuild
> indexes,
> >> >
> >> >How do you know it isn't rebuilding indexes if there
> are
> >> no error messages?
> >> >We need the error message from the rtp file.
> >> >
> >> >
> >> >> It backs up the db without any problem, but it
> cannot
> >> >> back up the transaction log...
> >> >
> >> >That is probably not related. My guess is that the
> >> database is in simple
> >> >recovery mode. read more in Books Online about backup
> to
> >> understand recovery
> >> >mode and ability to do log backup.
> >> >--
> >> >Tibor Karaszi, SQL Server MVP
> >> >Archive at:
> >> >http://groups.google.com/groups?
> >> oi=djq&as_ugroup=microsoft.public.sqlserver
> >> >
> >> >
> >> >"Kristen" <anonymous@.discussions.microsoft.com> wrote
> in
> >> message
> >> >news:0b3d01c3a7df$dc7d7160$a001280a@.phx.gbl...
> >> >> As far as I can tell, there are no errors, it is
> just
> >> not
> >> >> doing it. In the History file, it tells me that it
> is
> >> >> checking the db, but it is not able to rebuild
> indexes,
> >> >> which my boss thinks is the reason the db is so very
> >> slow.
> >> >>
> >> >> It backs up the db without any problem, but it
> cannot
> >> >> back up the transaction log...
> >> >>
> >> >> As you can probably tell, I am fairly new to SQL
> >> server...
> >> >>
> >> >> Anything I can do to fix it?
> >> >>
> >> >> >--Original Message--
> >> >> >What errors are being reported by sqlmaint -
> should be
> >> >> in one of the txt filesin the sql server logs
> >> directory.
> >> >> Youcan find the filename buy looking at the details
> of
> >> >> the scheduled job that runs the optimisatons job.
> >> >> >
> >> >> >If the optimisations job i snever running it will
> man
> >> >> indexes etc may be less than optimal but your
> >> >> phrase "extremely slow" makes me suspect something
> else
> >> >> is wrong as well.
> >> >> >
> >> >> >Mike John
> >> >> >
> >> >> >
> >> >> >"Kristen" <anonymous@.discussions.microsoft.com>
> wrote
> >> in
> >> >> message news:025e01c3a7d4$d3af1d00
> $a501280a@.phx.gbl...
> >> >> >> I cannot make the database optimize properly. I
> >> have
> >> >> a
> >> >> >> maintenance plan setup specifically to do just
> that,
> >> >> but
> >> >> >> it is never able to run and our database is
> becoming
> >> >> >> extremely slow. Any ideas?
> >> >> >.
> >> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Friday, March 9, 2012

Opinions about Insertion Technique

Looking for some insight from the professionals about how they handle
row inserts. Specifically single row inserts through a stored
procedure versus bulk inserts.

One argument are people who say all inserts (and updates and deletions
I guess) should go through stored procedures. The reasoning is that
the developers that code the client side have no reason to understand
HOW the data is stored, just that it is. Another problem is an insert
that deals with multiple tables. It would be very easy for the
developer to forget a step. That last point also applies to business
logic. In my case, adding a security to our SecurityMaster can touch 1
to 4 tables depending on the type of security. Also, certain fields
are required while others are set to null for depending on the type.

Because a stored procedure cannot be passed datasets but only scalar
values, when you need to deal with multiple (i.e. bulk) rows you are
stuck using cursors. This post is NOT about the pros and cons of
cursors. There are plenty of those on the boards (some of them
probably started by me and showing my understanding (or more
correctly, lack of) of the way to do things). Stored procedures also
give you the ability to abort and/or log inserts that cannot happen
because of contraints and/or business rule failures.

Another approach is to write code (not accessible from outside the
database) that handles bulk inserts. You would need to write in rules
to "extract" or "exclude" rows that do not match constraints or
business rules otherwise ALL the inserts would fail because of one bad
row. I guess you could put the "potential" rows into a temp table.
Apply your rules to the temp table and delete / move rows that would
fail. Any rows left can that be bulk inserted. (You could also use the
rows that were moved to another temp table for logging why they
failed.)

So that leaves use with two possible ways to get data into the system.
A single row based approach for client apps and a bulk based for
internal use. But that leaves use with another problem. You now have
business logic in TWO separate areas. You have to remember to modify
code or fix bugs in multiple locations.

For those that are still reading my post, my question is...

How do you handle this? What is the approach you take?Jay

What we have are a series of core stored procedures for single row
insert/update/deletes. As you point out, this may do extra
validation/processing etc. (Examples include security checking,
auditing, optimistic locking, business rule validation etc). These are
auto-generated.

In addition, we sometimes do bulk inserts from another table or data
source. To ensure we dont duplicate validation/processing AND to
ensure all data goes through the one filter before being committed, we
simply call the exact same stored procedure multiple times, typically
from within a cursor, but can be done from code in our case C#.
Depending on the application, this may be done as a transaction block.
We typically let the stored proc do the validation of the data and log
any rows that failed to insert.

This works like a charm for us and I have used this approach in a
number of successful projects.

However, this bulk insert approach does not scale very well and if you
do a lot of batch style processing, you really need to be looking at
customised scripts/stored procedures to get the best performance.

JayCallas@.hotmail.com (Jason) wrote in message news:<f01a7c89.0402042129.6f9090f6@.posting.google.com>...
> Looking for some insight from the professionals about how they handle
> row inserts. Specifically single row inserts through a stored
> procedure versus bulk inserts.
> One argument are people who say all inserts (and updates and deletions
> I guess) should go through stored procedures. The reasoning is that
> the developers that code the client side have no reason to understand
> HOW the data is stored, just that it is. Another problem is an insert
> that deals with multiple tables. It would be very easy for the
> developer to forget a step. That last point also applies to business
> logic. In my case, adding a security to our SecurityMaster can touch 1
> to 4 tables depending on the type of security. Also, certain fields
> are required while others are set to null for depending on the type.
> Because a stored procedure cannot be passed datasets but only scalar
> values, when you need to deal with multiple (i.e. bulk) rows you are
> stuck using cursors. This post is NOT about the pros and cons of
> cursors. There are plenty of those on the boards (some of them
> probably started by me and showing my understanding (or more
> correctly, lack of) of the way to do things). Stored procedures also
> give you the ability to abort and/or log inserts that cannot happen
> because of contraints and/or business rule failures.
> Another approach is to write code (not accessible from outside the
> database) that handles bulk inserts. You would need to write in rules
> to "extract" or "exclude" rows that do not match constraints or
> business rules otherwise ALL the inserts would fail because of one bad
> row. I guess you could put the "potential" rows into a temp table.
> Apply your rules to the temp table and delete / move rows that would
> fail. Any rows left can that be bulk inserted. (You could also use the
> rows that were moved to another temp table for logging why they
> failed.)
> So that leaves use with two possible ways to get data into the system.
> A single row based approach for client apps and a bulk based for
> internal use. But that leaves use with another problem. You now have
> business logic in TWO separate areas. You have to remember to modify
> code or fix bugs in multiple locations.
> For those that are still reading my post, my question is...
> How do you handle this? What is the approach you take?|||Jason (JayCallas@.hotmail.com) writes:
> Because a stored procedure cannot be passed datasets but only scalar
> values,

Actually, this is not completely true anymore. With SQL 2000 we got
the rowset provider OPENXML. So you can send in a complex dataset with
data for umpteen tables in one single procedure call.

It is probably still not as effecient as bulk insert, but bulk insert has
its limitations in validation etc. You can use a staging table, but
that comes with a cost of course.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||PromisedOyster@.hotmail.com (Mystery Man) wrote in message news:<87c81238.0402050421.69e0e0a@.posting.google.com>...
> Jay
> What we have are a series of core stored procedures for single row
> insert/update/deletes. As you point out, this may do extra
> validation/processing etc. (Examples include security checking,
> auditing, optimistic locking, business rule validation etc). These are
> auto-generated.

I use a combination - depending on what I want to achieve - if I am
loading a routine 'bulk' load then that runs split into adequate
batches (to minimise impact on transactions and logs) - but if I have
a luser wanting to enter data I like the stored proc approach for all
sorts of reasons.|||My personal experience is that business logic gets more and more
complicated as a project develops. The users wants to change this,
tweak that, etc. And stored procedures are the only sure-fire way I
can accomplish all of the user's requests. I do try to "modularize"
common code. For example, a user wants to view a dataset. The web
page calls the viewdataSP. Inside the viewdataSP, it calls a
userAccessSP. Erland has a pretty good article about how to call
stored procedures from a stored procedure.