| Author |
Database Optimization frequency
|
|
| Barry Woitena 2004-02-12, 12:25 pm |
|
We have a few 300 GB databases in our SQL Server 2000. We modify 1% of
the data each week. Our DBA has scheduled a maintenance optimization
each Wednesday evening. As a result, we spend Thursdays looking at
hourglasses. Is there a rule of thumb for the frequency of running the
optimization job? like after 20% of the data has changed?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
| |
| Ray Higdon 2004-02-12, 7:24 pm |
| >As a result, we spend Thursdays looking at
> hourglasses.
What do you mean? Is the optimization carrying over to Thursday?
--
Ray Higdon MCSE, MCDBA, CCNA
---
"Barry Woitena" <barry.woitena@brooks.af.mil> wrote in message
news:%23oJy6KY8DHA.2832@tk2msftngp13.phx.gbl...
>
> We have a few 300 GB databases in our SQL Server 2000. We modify 1% of
> the data each week. Our DBA has scheduled a maintenance optimization
> each Wednesday evening. As a result, we spend Thursdays looking at
> hourglasses. Is there a rule of thumb for the frequency of running the
> optimization job? like after 20% of the data has changed?
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
| |
| Barry Woitena 2004-02-13, 10:27 am |
|
I mean that the job runs from 5:00 pm Wednesday thru noon Thursday - 19
hours. I've since showed the staff DBCC Showcontig which details
fragmentation, or lack thereof. I included the output from one of our
VLDBs. I looked like:
- Scan Density [Best Count:Actual Count].......: 99.28% [11443:11526]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 13.41%
- Avg. Bytes Free per Page.....................: 607.2
I believe that when Scan Density drops to 80%, and/or Avg. Bytes Free
per Page drops to 400, then an optimization is necessary to sustain
performance.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
| |
| Ray Higdon 2004-02-14, 9:24 am |
| Your DBA is not very bright if he allows optimization to run during business
hours, just my opinion. Are you a 24-hour shop? Or just M-F?
--
Ray Higdon MCSE, MCDBA, CCNA
---
"Barry Woitena" <barry.woitena@brooks.af.mil> wrote in message
news:%23cK0d6j8DHA.1596@TK2MSFTNGP10.phx.gbl...
>
> I mean that the job runs from 5:00 pm Wednesday thru noon Thursday - 19
> hours. I've since showed the staff DBCC Showcontig which details
> fragmentation, or lack thereof. I included the output from one of our
> VLDBs. I looked like:
> - Scan Density [Best Count:Actual Count].......: 99.28% [11443:11526]
> - Logical Scan Fragmentation ..................: 0.00%
> - Extent Scan Fragmentation ...................: 13.41%
> - Avg. Bytes Free per Page.....................: 607.2
> I believe that when Scan Density drops to 80%, and/or Avg. Bytes Free
> per Page drops to 400, then an optimization is necessary to sustain
> performance.
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
| |
| Barry Woitena 2004-02-16, 10:24 am |
| I think we got it figured out. We can exclude the indexes from the
optimization step. We will keep running dbcc showcontig until the output
shows fragmentation sufficient to optimize.
I better cool it with this site. So far I've received about 20 virus
containing emails that our firewall caught.
- see ya
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!
| |
| Ray Higdon 2004-02-16, 11:24 am |
| When you post your email use something extra like mine is
sqlhigdon@NOSPAM.yahoo.com.
I'm not sure what you mean when you say you will keep running "dbcc
showcontig" until it is sufficient. Showcontig is a reading facility it
doesn't optimize anything.
--
Ray Higdon MCSE, MCDBA, CCNA
---
"Barry Woitena" <barry.woitena@brooks.af.mil> wrote in message
news:%23eKUwyJ9DHA.2472@TK2MSFTNGP10.phx.gbl...
> I think we got it figured out. We can exclude the indexes from the
> optimization step. We will keep running dbcc showcontig until the output
> shows fragmentation sufficient to optimize.
> I better cool it with this site. So far I've received about 20 virus
> containing emails that our firewall caught.
>
> - see ya
>
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
|
|
|
|