DB Maintenance Recommendations

I've read BOL on establishing a DB Maintenance Plan using the Wizard but it
really fails to make any real-world recommendations (that I can find) about
timing and options. I want to create a plan for an web application
production database of about 1.3gb in size with no scheduled downtime.
Are there any references/websites that list generally-accepted
recommendations for all of the options?
For example, how often should the system DBs be verified and backed up? How
often should indexes be rebuilt? Once a day? Once a week? Once a month? If
it runs an integrity check and tries to fix minor problems, won't that make
my DB (and my web application) unavailable to online users since it switches
to single-user mode? And so on.
Thanks for any pointers.Don Miller wrote:
> I've read BOL on establishing a DB Maintenance Plan using the Wizard but it
> really fails to make any real-world recommendations (that I can find) about
> timing and options. I want to create a plan for an web application
> production database of about 1.3gb in size with no scheduled downtime.
> Are there any references/websites that list generally-accepted
> recommendations for all of the options?
> For example, how often should the system DBs be verified and backed up? How
> often should indexes be rebuilt? Once a day? Once a week? Once a month? If
> it runs an integrity check and tries to fix minor problems, won't that make
> my DB (and my web application) unavailable to online users since it switches
> to single-user mode? And so on.
> Thanks for any pointers.
>
Finally, somebody sees through the wizards and realizes the complexity
they are hiding! Sorry, I hate those things... :-)
My suggestions:
1. Run weekly full backups, and frequent transaction log backups during
the week. I have a script that will do all of this for you:
http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
2. Rebuild indexes nightly or weekly, based on their level of
fragmentation. I have a script that I run nightly, rebuilding only
those that are fragmented beyond 30%:
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
3. As far as the integrity checks, I maintain a "maintenance" copy of
my databases, which is created by restoring the weekly full backup. Run
your DBCC checks against this database. If errors exist in that
database, they also exist in the live database. You can deal with them
in a planned outage in the live database. Having this maintenance
database also gives you a "test" area that you can use to analyze data
problems, performance problems, etc..
Hope that helps!
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44EDCBB5.5040808@.realsqlguy.com...
> 1. Run weekly full backups, and frequent transaction log backups during
> the week. I have a script that will do all of this for you:
> http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
I really can't afford to lose any data, but could settle for a day's worth
loss, and have little space for hundreds of transaction logs. Was thinking
about complete backups nightly and differential throughout the day.
> 2. Rebuild indexes nightly or weekly, based on their level of
> fragmentation. I have a script that I run nightly, rebuilding only
> those that are fragmented beyond 30%:
>
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
I have not ever rebuilt indexes or even know how to check if they are in
trouble (there must be some way to check). My DB only deals with thousands
of rows and so there has been no noticeable degradation in performance.
> 3. As far as the integrity checks, I maintain a "maintenance" copy of
> my databases, which is created by restoring the weekly full backup. Run
> your DBCC checks against this database. If errors exist in that
> database, they also exist in the live database. You can deal with them
> in a planned outage in the live database. Having this maintenance
> database also gives you a "test" area that you can use to analyze data
> problems, performance problems, etc..
Great idea!|||Don Miller wrote:
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:44EDCBB5.5040808@.realsqlguy.com...
>> 1. Run weekly full backups, and frequent transaction log backups during
>> the week. I have a script that will do all of this for you:
>> http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
> I really can't afford to lose any data, but could settle for a day's worth
> loss, and have little space for hundreds of transaction logs. Was thinking
> about complete backups nightly and differential throughout the day.
>
You can modify my script to make it backup nightly. Find the first IF
statement, remove the entire line, and replace it with:
IF GETDATE() - @.dtLastFullBackup > 1
>> 2. Rebuild indexes nightly or weekly, based on their level of
>> fragmentation. I have a script that I run nightly, rebuilding only
>> those that are fragmented beyond 30%:
> http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
> I have not ever rebuilt indexes or even know how to check if they are in
> trouble (there must be some way to check). My DB only deals with thousands
> of rows and so there has been no noticeable degradation in performance.
>
This script will do all of that for you, you just have to schedule it to
run.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||If you can't afford to loose data then how do you justify not having the
disk space for log backups? You can get a 300GB drive for $100.00 these
days. How much will it cost you if you loose data? With that said keep in
mind that a differential backup works by backing up the extents that have
had a change since the last full backup. The change can be just one bit out
of the entire 64K extent but it will back up the entire extent. If you
reindex all your tables your differential will most likely be the size of
your db. Something to think about.
--
Andrew J. Kelly SQL MVP
"Don Miller" <nospam@.nospam.com> wrote in message
news:u$ogvk5xGHA.4232@.TK2MSFTNGP05.phx.gbl...
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:44EDCBB5.5040808@.realsqlguy.com...
>> 1. Run weekly full backups, and frequent transaction log backups during
>> the week. I have a script that will do all of this for you:
>> http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
> I really can't afford to lose any data, but could settle for a day's worth
> loss, and have little space for hundreds of transaction logs. Was thinking
> about complete backups nightly and differential throughout the day.
>> 2. Rebuild indexes nightly or weekly, based on their level of
>> fragmentation. I have a script that I run nightly, rebuilding only
>> those that are fragmented beyond 30%:
> http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
> I have not ever rebuilt indexes or even know how to check if they are in
> trouble (there must be some way to check). My DB only deals with thousands
> of rows and so there has been no noticeable degradation in performance.
>> 3. As far as the integrity checks, I maintain a "maintenance" copy of
>> my databases, which is created by restoring the weekly full backup. Run
>> your DBCC checks against this database. If errors exist in that
>> database, they also exist in the live database. You can deal with them
>> in a planned outage in the live database. Having this maintenance
>> database also gives you a "test" area that you can use to analyze data
>> problems, performance problems, etc..
> Great idea!
>|||I've been playing with your script (on my production db moved to my dev
machine) since I have been very remiss in maintaining my database. I ran the
'defrag' option on the db (all indexes were about 50% fragmented), then I
did a SHRINKDB, and saw the size of my .mdf (and backup files) go from 1.3GB
to around 200MB!
Then I read about DBREINDEX and saw that your script handled that too. So, I
ran the rebuild script (on a pristine copy of my prod db without doing the
defrag first), shrunk the database again, and again got the same reduction
in file size. However, when I ran your script in the report mode AFTER the
rebuild and shrink, almost ALL of the files are reported as 75-100%
fragmented!!! Also, there are indexes showing up in the report as being 100%
fragmented that I can't even find. They are all named like
NameOfTable.tNameOfTable with a 't' prefix rather than 'IX_' there.
Is there a reason that after a DBREINDEX all the indexes are reported as
almost 100% fragmented?
What are those indexes with a 't' in their name?
Thanks for any explanation.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44EDD97B.5050604@.realsqlguy.com...
> Don Miller wrote:
> > "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> > news:44EDCBB5.5040808@.realsqlguy.com...
> >
> >> 1. Run weekly full backups, and frequent transaction log backups
during
> >> the week. I have a script that will do all of this for you:
> >> http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
> >
> > I really can't afford to lose any data, but could settle for a day's
worth
> > loss, and have little space for hundreds of transaction logs. Was
thinking
> > about complete backups nightly and differential throughout the day.
> >
> You can modify my script to make it backup nightly. Find the first IF
> statement, remove the entire line, and replace it with:
> IF GETDATE() - @.dtLastFullBackup > 1
> >> 2. Rebuild indexes nightly or weekly, based on their level of
> >> fragmentation. I have a script that I run nightly, rebuilding only
> >> those that are fragmented beyond 30%:
> >>
> >
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
> >
> > I have not ever rebuilt indexes or even know how to check if they are in
> > trouble (there must be some way to check). My DB only deals with
thousands
> > of rows and so there has been no noticeable degradation in performance.
> >
> This script will do all of that for you, you just have to schedule it to
> run.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||You're right of course. My server is at a dedicated hosting site with RAID 5
(4 drives in the array) with a total of 30gb for two partitions, so it is
not quite that easy, or inexpensive to add even 50 more gigs to my capacity.
They basically want folks to get a new server instead, or will charge $100 a
MONTH more for the extra capacity.
I appreciate your thoughts.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eqp%23bb%23xGHA.4912@.TK2MSFTNGP03.phx.gbl...
> If you can't afford to loose data then how do you justify not having the
> disk space for log backups? You can get a 300GB drive for $100.00 these
> days. How much will it cost you if you loose data? With that said keep in
> mind that a differential backup works by backing up the extents that have
> had a change since the last full backup. The change can be just one bit
out
> of the entire 64K extent but it will back up the entire extent. If you
> reindex all your tables your differential will most likely be the size of
> your db. Something to think about.
> --
> Andrew J. Kelly SQL MVP
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:u$ogvk5xGHA.4232@.TK2MSFTNGP05.phx.gbl...
> > "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> > news:44EDCBB5.5040808@.realsqlguy.com...
> >
> >> 1. Run weekly full backups, and frequent transaction log backups
during
> >> the week. I have a script that will do all of this for you:
> >> http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
> >
> > I really can't afford to lose any data, but could settle for a day's
worth
> > loss, and have little space for hundreds of transaction logs. Was
thinking
> > about complete backups nightly and differential throughout the day.
> >
> >> 2. Rebuild indexes nightly or weekly, based on their level of
> >> fragmentation. I have a script that I run nightly, rebuilding only
> >> those that are fragmented beyond 30%:
> >>
> >
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
> >
> > I have not ever rebuilt indexes or even know how to check if they are in
> > trouble (there must be some way to check). My DB only deals with
thousands
> > of rows and so there has been no noticeable degradation in performance.
> >
> >> 3. As far as the integrity checks, I maintain a "maintenance" copy of
> >> my databases, which is created by restoring the weekly full backup.
Run
> >> your DBCC checks against this database. If errors exist in that
> >> database, they also exist in the live database. You can deal with them
> >> in a planned outage in the live database. Having this maintenance
> >> database also gives you a "test" area that you can use to analyze data
> >> problems, performance problems, etc..
> >
> > Great idea!
> >
> >
>|||Can you post some of the actual results of the showcontig? You have to be
very careful about shrinking the data files. Shrink happens from the end of
the file inward and has to move any data near the end of the file to an
empty slot near the beginning. That shrinking process usually results in
fragmenting your indexes all over again in the process. A rebuild with DBCC
DBREINDEX requires about 1.2 times the size of your db in free space to do a
proper rebuild. This is due to the fact it makes a copy first and then drops
the original indexes and renames them. So you always need lots of free space
for rebuilds to operate properly. The key is to not have too little so as
the rebuild grows the files each time. IN general there is no penalty for
too much free space except for in your case if you simply don't have the
room. Once you find that medium where you have enough space to do your daily
operations and can rebuild your indexes you will find that the fragmentation
will be better after a rebuild. With that said there are other factors in
play as well. If your db is that small I suspect you have a lot of tables or
indexes using mixed extents. Essentially anything under 8 pages will share
as much as 8 different extents for the 8 pages that normally would go all on
1 extent. If you have less than several hundred to a thousand pages in the
table I would not be too concerned with some fragmentation. Have a look at
these as well:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp
Fill Factors
http://www.sql-server-performance.com/gv_clustered_indexes.asp
Clustered Indexes
Andrew J. Kelly SQL MVP
"Don Miller" <nospam@.nospam.com> wrote in message
news:uUbZgwDyGHA.3512@.TK2MSFTNGP04.phx.gbl...
> I've been playing with your script (on my production db moved to my dev
> machine) since I have been very remiss in maintaining my database. I ran
> the
> 'defrag' option on the db (all indexes were about 50% fragmented), then I
> did a SHRINKDB, and saw the size of my .mdf (and backup files) go from
> 1.3GB
> to around 200MB!
> Then I read about DBREINDEX and saw that your script handled that too. So,
> I
> ran the rebuild script (on a pristine copy of my prod db without doing the
> defrag first), shrunk the database again, and again got the same reduction
> in file size. However, when I ran your script in the report mode AFTER the
> rebuild and shrink, almost ALL of the files are reported as 75-100%
> fragmented!!! Also, there are indexes showing up in the report as being
> 100%
> fragmented that I can't even find. They are all named like
> NameOfTable.tNameOfTable with a 't' prefix rather than 'IX_' there.
> Is there a reason that after a DBREINDEX all the indexes are reported as
> almost 100% fragmented?
> What are those indexes with a 't' in their name?
> Thanks for any explanation.
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:44EDD97B.5050604@.realsqlguy.com...
>> Don Miller wrote:
>> > "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> > news:44EDCBB5.5040808@.realsqlguy.com...
>> >
>> >> 1. Run weekly full backups, and frequent transaction log backups
> during
>> >> the week. I have a script that will do all of this for you:
>> >> http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
>> >
>> > I really can't afford to lose any data, but could settle for a day's
> worth
>> > loss, and have little space for hundreds of transaction logs. Was
> thinking
>> > about complete backups nightly and differential throughout the day.
>> >
>> You can modify my script to make it backup nightly. Find the first IF
>> statement, remove the entire line, and replace it with:
>> IF GETDATE() - @.dtLastFullBackup > 1
>> >> 2. Rebuild indexes nightly or weekly, based on their level of
>> >> fragmentation. I have a script that I run nightly, rebuilding only
>> >> those that are fragmented beyond 30%:
>> >>
>> >
> http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
>> >
>> > I have not ever rebuilt indexes or even know how to check if they are
>> > in
>> > trouble (there must be some way to check). My DB only deals with
> thousands
>> > of rows and so there has been no noticeable degradation in performance.
>> >
>> This script will do all of that for you, you just have to schedule it to
>> run.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OCSStLEyGHA.3844@.TK2MSFTNGP06.phx.gbl...
First of all, I REALLY appreciate your expert help. These remarkable changes
in size could be that I shunned maintenance over a period of YEARS. It could
easily be that my DB is in actuality so small (<300mb) that performance
issues do not appear and one of the problems was that fragmented indexes
were continually growing the size of the database unnecessarily.
Also, are the indexes with the small "t" in front of the table name maybe
have something to do with clustered indexes?
> Can you post some of the actual results of the showcontig?
This morning, I started over again after restoring to my dev machine, the
last full (simple) backup from production. The first thing I did was do the
DBREINDEX for all tables in the DB, then a SHRINKDB (all using the Simple
recovery model). The .mdf went 1.4gb to 280mb, and the .ldf went to 2mb.
Here's the showcontig of one table 'Patients' after all of that happened (is
there an easier to read way to post this?):
IndexName IndexId Level Pages Rows MinimumRecordSize MaximumRecordSize
AverageRecordSize ForwardedRecords Extents ExtentSwitches AverageFreeBytes
AveragePageDenisty ScanDensity BestCount ActualCount LogicalFragmentation
ExtentFragmentation
PK_Patients 1 0 176 NULL NULL NULL NULL NULL 0 23 NULL NULL 92 22 24
99 NULL
IX_Patients_ClinicalEDC 2 0 10 NULL NULL NULL NULL NULL 0 2 NULL NULL
67 2 3 90 NULL
IX_Patients_FirstName 3 0 11 NULL NULL NULL NULL NULL 0 4 NULL NULL 40
2 5 100 NULL
IX_Patients_GroupID 4 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL 33 1
3 100 NULL
IX_Patients_LastName 5 0 11 NULL NULL NULL NULL NULL 0 4 NULL NULL 40
2 5 100 NULL
IX_Patients_ProviderID 6 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL 33
1 3 100 NULL
IX_Patients_PersonID 7 0 8 NULL NULL NULL NULL NULL 0 1 NULL NULL 50 1
2 100 NULL
IX_Patients_LastNameSearch 8 0 11 NULL NULL NULL NULL NULL 0 3 NULL
NULL 50 2 4 100 NULL
IX_Patients_FirstNameSearch 9 0 11 NULL NULL NULL NULL NULL 0 1 NULL
NULL 100 2 2 82 NULL
IX_Patients_UserName 10 0 6 NULL NULL NULL NULL NULL 0 0 NULL NULL 100
1 1 83 NULL
IX_Patients_MaritalStatusID 12 0 8 NULL NULL NULL NULL NULL 0 2 NULL
NULL 33 1 3 88 NULL
IX_Patients_updateAuthorID 13 0 8 NULL NULL NULL NULL NULL 0 2 NULL
NULL 33 1 3 75 NULL
IX_Patients_ReligionID 14 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
33 1 3 75 NULL
IX_Patients_LanguageID 15 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
33 1 3 75 NULL
IX_Patients_PtRaceID 16 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL 33
1 3 75 NULL
IX_Patients_AuthorID 17 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL 33
1 3 75 NULL
IX_Patients_CtRaceID 39 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL 33
1 3 75 NULL
IX_Patients_FacilityID 40 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
33 1 3 75 NULL
IX_Patients_OxFinalAuthorDate 49 0 10 NULL NULL NULL NULL NULL 0 4
NULL NULL 40 2 5 90 NULL
IX_Patients_HospitalID 50 0 8 NULL NULL NULL NULL NULL 0 3 NULL NULL
25 1 4 75 NULL
tPatients 255 0 194 15709 79 493 91 0 42 41 546 93 60 25 42 99 90
> shrinking process usually results in
> fragmenting your indexes all over again in the process. A rebuild with
DBCC
> DBREINDEX requires about 1.2 times the size of your db in free space to do
a
> proper rebuild.
> So you always need lots of free space
> for rebuilds to operate properly.
Do you mean free space on the hard drive (which on my dev machine I have
30gb to spare and on the production I have 4-5x the size of the database) or
'space available' within the database file (like you see when one brings up
properties for the database)?
> If your db is that small I suspect you have a lot of tables or
> indexes using mixed extents.
Yes, I have some 90 tables. I don't know what mixed extents means at this
point.
> Essentially anything under 8 pages will share
> as much as 8 different extents for the 8 pages that normally would go all
on
> 1 extent. If you have less than several hundred to a thousand pages in the
> table I would not be too concerned with some fragmentation.
My table with the most rows has about 1500 pages
Have a look at
> these as well:
>
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
> considerations
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> Index Defrag Best Practices 2000
> http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
> Understanding DBCC SHOWCONTIG
>
http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp
> Fill Factors
> http://www.sql-server-performance.com/gv_clustered_indexes.asp
> Clustered Indexes
>
> --
> Andrew J. Kelly SQL MVP
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:uUbZgwDyGHA.3512@.TK2MSFTNGP04.phx.gbl...
> > I've been playing with your script (on my production db moved to my dev
> > machine) since I have been very remiss in maintaining my database. I ran
> > the
> > 'defrag' option on the db (all indexes were about 50% fragmented), then
I
> > did a SHRINKDB, and saw the size of my .mdf (and backup files) go from
> > 1.3GB
> > to around 200MB!
> >
> > Then I read about DBREINDEX and saw that your script handled that too.
So,
> > I
> > ran the rebuild script (on a pristine copy of my prod db without doing
the
> > defrag first), shrunk the database again, and again got the same
reduction
> > in file size. However, when I ran your script in the report mode AFTER
the
> > rebuild and shrink, almost ALL of the files are reported as 75-100%
> > fragmented!!! Also, there are indexes showing up in the report as being
> > 100%
> > fragmented that I can't even find. They are all named like
> > NameOfTable.tNameOfTable with a 't' prefix rather than 'IX_' there.
> >
> > Is there a reason that after a DBREINDEX all the indexes are reported as
> > almost 100% fragmented?
> >
> > What are those indexes with a 't' in their name?
> >
> > Thanks for any explanation.
> >
> >
> > "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> > news:44EDD97B.5050604@.realsqlguy.com...
> >> Don Miller wrote:
> >> > "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> >> > news:44EDCBB5.5040808@.realsqlguy.com...
> >> >
> >> >> 1. Run weekly full backups, and frequent transaction log backups
> > during
> >> >> the week. I have a script that will do all of this for you:
> >> >> http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
> >> >
> >> > I really can't afford to lose any data, but could settle for a day's
> > worth
> >> > loss, and have little space for hundreds of transaction logs. Was
> > thinking
> >> > about complete backups nightly and differential throughout the day.
> >> >
> >>
> >> You can modify my script to make it backup nightly. Find the first IF
> >> statement, remove the entire line, and replace it with:
> >>
> >> IF GETDATE() - @.dtLastFullBackup > 1
> >>
> >> >> 2. Rebuild indexes nightly or weekly, based on their level of
> >> >> fragmentation. I have a script that I run nightly, rebuilding only
> >> >> those that are fragmented beyond 30%:
> >> >>
> >> >
> >
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
> >> >
> >> > I have not ever rebuilt indexes or even know how to check if they are
> >> > in
> >> > trouble (there must be some way to check). My DB only deals with
> > thousands
> >> > of rows and so there has been no noticeable degradation in
performance.
> >> >
> >>
> >> This script will do all of that for you, you just have to schedule it
to
> >> run.
> >>
> >>
> >>
> >> --
> >> Tracy McKibben
> >> MCDBA
> >> http://www.realsqlguy.com
> >
> >
>|||Maybe this is simpler (after DBREINDEX and SHRINKDB):
DBCC SHOWCONTIG scanning 'PtHxEvents' table...
Table: 'PtHxEvents' (1366295927); index ID: 1, database ID: 15
TABLE level scan performed.
- Pages Scanned........................: 1524
- Extents Scanned.......................: 194
- Extent Switches.......................: 194
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 97.95% [191:195]
- Logical Scan Fragmentation ..............: 99.87%
- Extent Scan Fragmentation ...............: 12.89%
- Avg. Bytes Free per Page................: 33.2
- Avg. Page Density (full)................: 99.59%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
"Don Miller" <nospam@.nospam.com> wrote in message
news:uo0mBoEyGHA.356@.TK2MSFTNGP02.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OCSStLEyGHA.3844@.TK2MSFTNGP06.phx.gbl...
> First of all, I REALLY appreciate your expert help. These remarkable
changes
> in size could be that I shunned maintenance over a period of YEARS. It
could
> easily be that my DB is in actuality so small (<300mb) that performance
> issues do not appear and one of the problems was that fragmented indexes
> were continually growing the size of the database unnecessarily.
> Also, are the indexes with the small "t" in front of the table name maybe
> have something to do with clustered indexes?
> > Can you post some of the actual results of the showcontig?
> This morning, I started over again after restoring to my dev machine, the
> last full (simple) backup from production. The first thing I did was do
the
> DBREINDEX for all tables in the DB, then a SHRINKDB (all using the Simple
> recovery model). The .mdf went 1.4gb to 280mb, and the .ldf went to 2mb.
> Here's the showcontig of one table 'Patients' after all of that happened
(is
> there an easier to read way to post this?):
> IndexName IndexId Level Pages Rows MinimumRecordSize
MaximumRecordSize
> AverageRecordSize ForwardedRecords Extents ExtentSwitches AverageFreeBytes
> AveragePageDenisty ScanDensity BestCount ActualCount LogicalFragmentation
> ExtentFragmentation
> PK_Patients 1 0 176 NULL NULL NULL NULL NULL 0 23 NULL NULL 92 22 24
> 99 NULL
> IX_Patients_ClinicalEDC 2 0 10 NULL NULL NULL NULL NULL 0 2 NULL
NULL
> 67 2 3 90 NULL
> IX_Patients_FirstName 3 0 11 NULL NULL NULL NULL NULL 0 4 NULL NULL
40
> 2 5 100 NULL
> IX_Patients_GroupID 4 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL 33
1
> 3 100 NULL
> IX_Patients_LastName 5 0 11 NULL NULL NULL NULL NULL 0 4 NULL NULL
40
> 2 5 100 NULL
> IX_Patients_ProviderID 6 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
33
> 1 3 100 NULL
> IX_Patients_PersonID 7 0 8 NULL NULL NULL NULL NULL 0 1 NULL NULL 50
1
> 2 100 NULL
> IX_Patients_LastNameSearch 8 0 11 NULL NULL NULL NULL NULL 0 3 NULL
> NULL 50 2 4 100 NULL
> IX_Patients_FirstNameSearch 9 0 11 NULL NULL NULL NULL NULL 0 1 NULL
> NULL 100 2 2 82 NULL
> IX_Patients_UserName 10 0 6 NULL NULL NULL NULL NULL 0 0 NULL NULL
100
> 1 1 83 NULL
> IX_Patients_MaritalStatusID 12 0 8 NULL NULL NULL NULL NULL 0 2 NULL
> NULL 33 1 3 88 NULL
> IX_Patients_updateAuthorID 13 0 8 NULL NULL NULL NULL NULL 0 2 NULL
> NULL 33 1 3 75 NULL
> IX_Patients_ReligionID 14 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
> 33 1 3 75 NULL
> IX_Patients_LanguageID 15 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
> 33 1 3 75 NULL
> IX_Patients_PtRaceID 16 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
33
> 1 3 75 NULL
> IX_Patients_AuthorID 17 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
33
> 1 3 75 NULL
> IX_Patients_CtRaceID 39 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
33
> 1 3 75 NULL
> IX_Patients_FacilityID 40 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
> 33 1 3 75 NULL
> IX_Patients_OxFinalAuthorDate 49 0 10 NULL NULL NULL NULL NULL 0 4
> NULL NULL 40 2 5 90 NULL
> IX_Patients_HospitalID 50 0 8 NULL NULL NULL NULL NULL 0 3 NULL NULL
> 25 1 4 75 NULL
> tPatients 255 0 194 15709 79 493 91 0 42 41 546 93 60 25 42 99 90
>
> > shrinking process usually results in
> > fragmenting your indexes all over again in the process. A rebuild with
> DBCC
> > DBREINDEX requires about 1.2 times the size of your db in free space to
do
> a
> > proper rebuild.
> >
> > So you always need lots of free space
> > for rebuilds to operate properly.
> Do you mean free space on the hard drive (which on my dev machine I have
> 30gb to spare and on the production I have 4-5x the size of the database)
or
> 'space available' within the database file (like you see when one brings
up
> properties for the database)?
> > If your db is that small I suspect you have a lot of tables or
> > indexes using mixed extents.
> Yes, I have some 90 tables. I don't know what mixed extents means at this
> point.
> > Essentially anything under 8 pages will share
> > as much as 8 different extents for the 8 pages that normally would go
all
> on
> > 1 extent. If you have less than several hundred to a thousand pages in
the
> > table I would not be too concerned with some fragmentation.
> My table with the most rows has about 1500 pages
>
>
> Have a look at
> > these as well:
> >
> >
> > http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
> > considerations
> >
> >
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> > Index Defrag Best Practices 2000
> > http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
> > Understanding DBCC SHOWCONTIG
> >
>
http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp
> > Fill Factors
> > http://www.sql-server-performance.com/gv_clustered_indexes.asp
> > Clustered Indexes
> >
> >
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> > "Don Miller" <nospam@.nospam.com> wrote in message
> > news:uUbZgwDyGHA.3512@.TK2MSFTNGP04.phx.gbl...
> > > I've been playing with your script (on my production db moved to my
dev
> > > machine) since I have been very remiss in maintaining my database. I
ran
> > > the
> > > 'defrag' option on the db (all indexes were about 50% fragmented),
then
> I
> > > did a SHRINKDB, and saw the size of my .mdf (and backup files) go from
> > > 1.3GB
> > > to around 200MB!
> > >
> > > Then I read about DBREINDEX and saw that your script handled that too.
> So,
> > > I
> > > ran the rebuild script (on a pristine copy of my prod db without doing
> the
> > > defrag first), shrunk the database again, and again got the same
> reduction
> > > in file size. However, when I ran your script in the report mode AFTER
> the
> > > rebuild and shrink, almost ALL of the files are reported as 75-100%
> > > fragmented!!! Also, there are indexes showing up in the report as
being
> > > 100%
> > > fragmented that I can't even find. They are all named like
> > > NameOfTable.tNameOfTable with a 't' prefix rather than 'IX_' there.
> > >
> > > Is there a reason that after a DBREINDEX all the indexes are reported
as
> > > almost 100% fragmented?
> > >
> > > What are those indexes with a 't' in their name?
> > >
> > > Thanks for any explanation.
> > >
> > >
> > > "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> > > news:44EDD97B.5050604@.realsqlguy.com...
> > >> Don Miller wrote:
> > >> > "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> > >> > news:44EDCBB5.5040808@.realsqlguy.com...
> > >> >
> > >> >> 1. Run weekly full backups, and frequent transaction log backups
> > > during
> > >> >> the week. I have a script that will do all of this for you:
> > >> >> http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
> > >> >
> > >> > I really can't afford to lose any data, but could settle for a
day's
> > > worth
> > >> > loss, and have little space for hundreds of transaction logs. Was
> > > thinking
> > >> > about complete backups nightly and differential throughout the day.
> > >> >
> > >>
> > >> You can modify my script to make it backup nightly. Find the first
IF
> > >> statement, remove the entire line, and replace it with:
> > >>
> > >> IF GETDATE() - @.dtLastFullBackup > 1
> > >>
> > >> >> 2. Rebuild indexes nightly or weekly, based on their level of
> > >> >> fragmentation. I have a script that I run nightly, rebuilding
only
> > >> >> those that are fragmented beyond 30%:
> > >> >>
> > >> >
> > >
>
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
> > >> >
> > >> > I have not ever rebuilt indexes or even know how to check if they
are
> > >> > in
> > >> > trouble (there must be some way to check). My DB only deals with
> > > thousands
> > >> > of rows and so there has been no noticeable degradation in
> performance.
> > >> >
> > >>
> > >> This script will do all of that for you, you just have to schedule it
> to
> > >> run.
> > >>
> > >>
> > >>
> > >> --
> > >> Tracy McKibben
> > >> MCDBA
> > >> http://www.realsqlguy.com
> > >
> > >
> >
> >
>|||Don,
I really can't see where the 99% Logical fragmentation is coming from. I
would try issuing a DBCC DBREINDEX onthe table again and see what it says.
How many files do you have in the Filegroup where this table lives?
--
Andrew J. Kelly SQL MVP
"Don Miller" <nospam@.nospam.com> wrote in message
news:%23Jo$O1EyGHA.2220@.TK2MSFTNGP02.phx.gbl...
> Maybe this is simpler (after DBREINDEX and SHRINKDB):
> DBCC SHOWCONTIG scanning 'PtHxEvents' table...
> Table: 'PtHxEvents' (1366295927); index ID: 1, database ID: 15
> TABLE level scan performed.
> - Pages Scanned........................: 1524
> - Extents Scanned.......................: 194
> - Extent Switches.......................: 194
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 97.95% [191:195]
> - Logical Scan Fragmentation ..............: 99.87%
> - Extent Scan Fragmentation ...............: 12.89%
> - Avg. Bytes Free per Page................: 33.2
> - Avg. Page Density (full)................: 99.59%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:uo0mBoEyGHA.356@.TK2MSFTNGP02.phx.gbl...
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OCSStLEyGHA.3844@.TK2MSFTNGP06.phx.gbl...
>> First of all, I REALLY appreciate your expert help. These remarkable
> changes
>> in size could be that I shunned maintenance over a period of YEARS. It
> could
>> easily be that my DB is in actuality so small (<300mb) that performance
>> issues do not appear and one of the problems was that fragmented indexes
>> were continually growing the size of the database unnecessarily.
>> Also, are the indexes with the small "t" in front of the table name maybe
>> have something to do with clustered indexes?
>> > Can you post some of the actual results of the showcontig?
>> This morning, I started over again after restoring to my dev machine, the
>> last full (simple) backup from production. The first thing I did was do
> the
>> DBREINDEX for all tables in the DB, then a SHRINKDB (all using the Simple
>> recovery model). The .mdf went 1.4gb to 280mb, and the .ldf went to 2mb.
>> Here's the showcontig of one table 'Patients' after all of that happened
> (is
>> there an easier to read way to post this?):
>> IndexName IndexId Level Pages Rows MinimumRecordSize
> MaximumRecordSize
>> AverageRecordSize ForwardedRecords Extents ExtentSwitches
>> AverageFreeBytes
>> AveragePageDenisty ScanDensity BestCount ActualCount LogicalFragmentation
>> ExtentFragmentation
>> PK_Patients 1 0 176 NULL NULL NULL NULL NULL 0 23 NULL NULL 92 22
>> 24
>> 99 NULL
>> IX_Patients_ClinicalEDC 2 0 10 NULL NULL NULL NULL NULL 0 2 NULL
> NULL
>> 67 2 3 90 NULL
>> IX_Patients_FirstName 3 0 11 NULL NULL NULL NULL NULL 0 4 NULL NULL
> 40
>> 2 5 100 NULL
>> IX_Patients_GroupID 4 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL 33
> 1
>> 3 100 NULL
>> IX_Patients_LastName 5 0 11 NULL NULL NULL NULL NULL 0 4 NULL NULL
> 40
>> 2 5 100 NULL
>> IX_Patients_ProviderID 6 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
> 33
>> 1 3 100 NULL
>> IX_Patients_PersonID 7 0 8 NULL NULL NULL NULL NULL 0 1 NULL NULL
>> 50
> 1
>> 2 100 NULL
>> IX_Patients_LastNameSearch 8 0 11 NULL NULL NULL NULL NULL 0 3 NULL
>> NULL 50 2 4 100 NULL
>> IX_Patients_FirstNameSearch 9 0 11 NULL NULL NULL NULL NULL 0 1
>> NULL
>> NULL 100 2 2 82 NULL
>> IX_Patients_UserName 10 0 6 NULL NULL NULL NULL NULL 0 0 NULL NULL
> 100
>> 1 1 83 NULL
>> IX_Patients_MaritalStatusID 12 0 8 NULL NULL NULL NULL NULL 0 2
>> NULL
>> NULL 33 1 3 88 NULL
>> IX_Patients_updateAuthorID 13 0 8 NULL NULL NULL NULL NULL 0 2 NULL
>> NULL 33 1 3 75 NULL
>> IX_Patients_ReligionID 14 0 8 NULL NULL NULL NULL NULL 0 2 NULL
>> NULL
>> 33 1 3 75 NULL
>> IX_Patients_LanguageID 15 0 8 NULL NULL NULL NULL NULL 0 2 NULL
>> NULL
>> 33 1 3 75 NULL
>> IX_Patients_PtRaceID 16 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
> 33
>> 1 3 75 NULL
>> IX_Patients_AuthorID 17 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
> 33
>> 1 3 75 NULL
>> IX_Patients_CtRaceID 39 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
> 33
>> 1 3 75 NULL
>> IX_Patients_FacilityID 40 0 8 NULL NULL NULL NULL NULL 0 2 NULL
>> NULL
>> 33 1 3 75 NULL
>> IX_Patients_OxFinalAuthorDate 49 0 10 NULL NULL NULL NULL NULL 0 4
>> NULL NULL 40 2 5 90 NULL
>> IX_Patients_HospitalID 50 0 8 NULL NULL NULL NULL NULL 0 3 NULL
>> NULL
>> 25 1 4 75 NULL
>> tPatients 255 0 194 15709 79 493 91 0 42 41 546 93 60 25 42 99 90
>>
>> > shrinking process usually results in
>> > fragmenting your indexes all over again in the process. A rebuild with
>> DBCC
>> > DBREINDEX requires about 1.2 times the size of your db in free space to
> do
>> a
>> > proper rebuild.
>> >
>> > So you always need lots of free space
>> > for rebuilds to operate properly.
>> Do you mean free space on the hard drive (which on my dev machine I have
>> 30gb to spare and on the production I have 4-5x the size of the database)
> or
>> 'space available' within the database file (like you see when one brings
> up
>> properties for the database)?
>> > If your db is that small I suspect you have a lot of tables or
>> > indexes using mixed extents.
>> Yes, I have some 90 tables. I don't know what mixed extents means at this
>> point.
>> > Essentially anything under 8 pages will share
>> > as much as 8 different extents for the 8 pages that normally would go
> all
>> on
>> > 1 extent. If you have less than several hundred to a thousand pages in
> the
>> > table I would not be too concerned with some fragmentation.
>> My table with the most rows has about 1500 pages
>>
>>
>> Have a look at
>> > these as well:
>> >
>> >
>> > http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
>> > considerations
>> >
>> >
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> > Index Defrag Best Practices 2000
>> > http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
>> > Understanding DBCC SHOWCONTIG
>> >
> http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp
>> > Fill Factors
>> > http://www.sql-server-performance.com/gv_clustered_indexes.asp
>> > Clustered Indexes
>> >
>> >
>> >
>> > --
>> > Andrew J. Kelly SQL MVP
>> >
>> > "Don Miller" <nospam@.nospam.com> wrote in message
>> > news:uUbZgwDyGHA.3512@.TK2MSFTNGP04.phx.gbl...
>> > > I've been playing with your script (on my production db moved to my
> dev
>> > > machine) since I have been very remiss in maintaining my database. I
> ran
>> > > the
>> > > 'defrag' option on the db (all indexes were about 50% fragmented),
> then
>> I
>> > > did a SHRINKDB, and saw the size of my .mdf (and backup files) go
>> > > from
>> > > 1.3GB
>> > > to around 200MB!
>> > >
>> > > Then I read about DBREINDEX and saw that your script handled that
>> > > too.
>> So,
>> > > I
>> > > ran the rebuild script (on a pristine copy of my prod db without
>> > > doing
>> the
>> > > defrag first), shrunk the database again, and again got the same
>> reduction
>> > > in file size. However, when I ran your script in the report mode
>> > > AFTER
>> the
>> > > rebuild and shrink, almost ALL of the files are reported as 75-100%
>> > > fragmented!!! Also, there are indexes showing up in the report as
> being
>> > > 100%
>> > > fragmented that I can't even find. They are all named like
>> > > NameOfTable.tNameOfTable with a 't' prefix rather than 'IX_' there.
>> > >
>> > > Is there a reason that after a DBREINDEX all the indexes are reported
> as
>> > > almost 100% fragmented?
>> > >
>> > > What are those indexes with a 't' in their name?
>> > >
>> > > Thanks for any explanation.
>> > >
>> > >
>> > > "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> > > news:44EDD97B.5050604@.realsqlguy.com...
>> > >> Don Miller wrote:
>> > >> > "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> > >> > news:44EDCBB5.5040808@.realsqlguy.com...
>> > >> >
>> > >> >> 1. Run weekly full backups, and frequent transaction log backups
>> > > during
>> > >> >> the week. I have a script that will do all of this for you:
>> > >> >> http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
>> > >> >
>> > >> > I really can't afford to lose any data, but could settle for a
> day's
>> > > worth
>> > >> > loss, and have little space for hundreds of transaction logs. Was
>> > > thinking
>> > >> > about complete backups nightly and differential throughout the
>> > >> > day.
>> > >> >
>> > >>
>> > >> You can modify my script to make it backup nightly. Find the first
> IF
>> > >> statement, remove the entire line, and replace it with:
>> > >>
>> > >> IF GETDATE() - @.dtLastFullBackup > 1
>> > >>
>> > >> >> 2. Rebuild indexes nightly or weekly, based on their level of
>> > >> >> fragmentation. I have a script that I run nightly, rebuilding
> only
>> > >> >> those that are fragmented beyond 30%:
>> > >> >>
>> > >> >
>> > >
> http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
>> > >> >
>> > >> > I have not ever rebuilt indexes or even know how to check if they
> are
>> > >> > in
>> > >> > trouble (there must be some way to check). My DB only deals with
>> > > thousands
>> > >> > of rows and so there has been no noticeable degradation in
>> performance.
>> > >> >
>> > >>
>> > >> This script will do all of that for you, you just have to schedule
>> > >> it
>> to
>> > >> run.
>> > >>
>> > >>
>> > >>
>> > >> --
>> > >> Tracy McKibben
>> > >> MCDBA
>> > >> http://www.realsqlguy.com
>> > >
>> > >
>> >
>> >
>>
>|||Don,
I can't tell what those indexes with the t are but most likely they are
statistics and not indexes. But for this Patients table I would get rid of
the unused indexes because boy you sure have a lot of them<g>. If the index
has a very low selectivity (meaning there are a lot of the same values) the
nonclustered index is most likely not even being used. I don't have the
table DDL so this is a guess but columns such as Race, Religion, marital
status and even HospitalID probably have a lot of rows that match the same
value. For instance let take Marital Status. I bet most match 30 or more %
of the rows. A nonclustered index for a simple lookup is usually useless
after just a few %, sometimes as low as 2 or 3% even. What about the first &
last name indexes? There is a first & last name search index as well.
What is the difference between the two? Getting rid of unused indexes may
free up some room as well.
And by free space I mean in the data file not the hard drive. The first link
I sent should clear that up for you.
--
Andrew J. Kelly SQL MVP
"Don Miller" <nospam@.nospam.com> wrote in message
news:uo0mBoEyGHA.356@.TK2MSFTNGP02.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OCSStLEyGHA.3844@.TK2MSFTNGP06.phx.gbl...
> First of all, I REALLY appreciate your expert help. These remarkable
> changes
> in size could be that I shunned maintenance over a period of YEARS. It
> could
> easily be that my DB is in actuality so small (<300mb) that performance
> issues do not appear and one of the problems was that fragmented indexes
> were continually growing the size of the database unnecessarily.
> Also, are the indexes with the small "t" in front of the table name maybe
> have something to do with clustered indexes?
>> Can you post some of the actual results of the showcontig?
> This morning, I started over again after restoring to my dev machine, the
> last full (simple) backup from production. The first thing I did was do
> the
> DBREINDEX for all tables in the DB, then a SHRINKDB (all using the Simple
> recovery model). The .mdf went 1.4gb to 280mb, and the .ldf went to 2mb.
> Here's the showcontig of one table 'Patients' after all of that happened
> (is
> there an easier to read way to post this?):
> IndexName IndexId Level Pages Rows MinimumRecordSize
> MaximumRecordSize
> AverageRecordSize ForwardedRecords Extents ExtentSwitches AverageFreeBytes
> AveragePageDenisty ScanDensity BestCount ActualCount LogicalFragmentation
> ExtentFragmentation
> PK_Patients 1 0 176 NULL NULL NULL NULL NULL 0 23 NULL NULL 92 22 24
> 99 NULL
> IX_Patients_ClinicalEDC 2 0 10 NULL NULL NULL NULL NULL 0 2 NULL NULL
> 67 2 3 90 NULL
> IX_Patients_FirstName 3 0 11 NULL NULL NULL NULL NULL 0 4 NULL NULL
> 40
> 2 5 100 NULL
> IX_Patients_GroupID 4 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL 33 1
> 3 100 NULL
> IX_Patients_LastName 5 0 11 NULL NULL NULL NULL NULL 0 4 NULL NULL 40
> 2 5 100 NULL
> IX_Patients_ProviderID 6 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
> 33
> 1 3 100 NULL
> IX_Patients_PersonID 7 0 8 NULL NULL NULL NULL NULL 0 1 NULL NULL 50
> 1
> 2 100 NULL
> IX_Patients_LastNameSearch 8 0 11 NULL NULL NULL NULL NULL 0 3 NULL
> NULL 50 2 4 100 NULL
> IX_Patients_FirstNameSearch 9 0 11 NULL NULL NULL NULL NULL 0 1 NULL
> NULL 100 2 2 82 NULL
> IX_Patients_UserName 10 0 6 NULL NULL NULL NULL NULL 0 0 NULL NULL
> 100
> 1 1 83 NULL
> IX_Patients_MaritalStatusID 12 0 8 NULL NULL NULL NULL NULL 0 2 NULL
> NULL 33 1 3 88 NULL
> IX_Patients_updateAuthorID 13 0 8 NULL NULL NULL NULL NULL 0 2 NULL
> NULL 33 1 3 75 NULL
> IX_Patients_ReligionID 14 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
> 33 1 3 75 NULL
> IX_Patients_LanguageID 15 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
> 33 1 3 75 NULL
> IX_Patients_PtRaceID 16 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL 33
> 1 3 75 NULL
> IX_Patients_AuthorID 17 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL 33
> 1 3 75 NULL
> IX_Patients_CtRaceID 39 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL 33
> 1 3 75 NULL
> IX_Patients_FacilityID 40 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
> 33 1 3 75 NULL
> IX_Patients_OxFinalAuthorDate 49 0 10 NULL NULL NULL NULL NULL 0 4
> NULL NULL 40 2 5 90 NULL
> IX_Patients_HospitalID 50 0 8 NULL NULL NULL NULL NULL 0 3 NULL NULL
> 25 1 4 75 NULL
> tPatients 255 0 194 15709 79 493 91 0 42 41 546 93 60 25 42 99 90
>
>> shrinking process usually results in
>> fragmenting your indexes all over again in the process. A rebuild with
> DBCC
>> DBREINDEX requires about 1.2 times the size of your db in free space to
>> do
> a
>> proper rebuild.
>> So you always need lots of free space
>> for rebuilds to operate properly.
> Do you mean free space on the hard drive (which on my dev machine I have
> 30gb to spare and on the production I have 4-5x the size of the database)
> or
> 'space available' within the database file (like you see when one brings
> up
> properties for the database)?
>> If your db is that small I suspect you have a lot of tables or
>> indexes using mixed extents.
> Yes, I have some 90 tables. I don't know what mixed extents means at this
> point.
>> Essentially anything under 8 pages will share
>> as much as 8 different extents for the 8 pages that normally would go all
> on
>> 1 extent. If you have less than several hundred to a thousand pages in
>> the
>> table I would not be too concerned with some fragmentation.
> My table with the most rows has about 1500 pages
>
>
> Have a look at
>> these as well:
>>
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
>> considerations
>>
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> Index Defrag Best Practices 2000
>> http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
>> Understanding DBCC SHOWCONTIG
> http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp
>> Fill Factors
>> http://www.sql-server-performance.com/gv_clustered_indexes.asp
>> Clustered Indexes
>>
>> --
>> Andrew J. Kelly SQL MVP
>> "Don Miller" <nospam@.nospam.com> wrote in message
>> news:uUbZgwDyGHA.3512@.TK2MSFTNGP04.phx.gbl...
>> > I've been playing with your script (on my production db moved to my dev
>> > machine) since I have been very remiss in maintaining my database. I
>> > ran
>> > the
>> > 'defrag' option on the db (all indexes were about 50% fragmented), then
> I
>> > did a SHRINKDB, and saw the size of my .mdf (and backup files) go from
>> > 1.3GB
>> > to around 200MB!
>> >
>> > Then I read about DBREINDEX and saw that your script handled that too.
> So,
>> > I
>> > ran the rebuild script (on a pristine copy of my prod db without doing
> the
>> > defrag first), shrunk the database again, and again got the same
> reduction
>> > in file size. However, when I ran your script in the report mode AFTER
> the
>> > rebuild and shrink, almost ALL of the files are reported as 75-100%
>> > fragmented!!! Also, there are indexes showing up in the report as being
>> > 100%
>> > fragmented that I can't even find. They are all named like
>> > NameOfTable.tNameOfTable with a 't' prefix rather than 'IX_' there.
>> >
>> > Is there a reason that after a DBREINDEX all the indexes are reported
>> > as
>> > almost 100% fragmented?
>> >
>> > What are those indexes with a 't' in their name?
>> >
>> > Thanks for any explanation.
>> >
>> >
>> > "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> > news:44EDD97B.5050604@.realsqlguy.com...
>> >> Don Miller wrote:
>> >> > "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> >> > news:44EDCBB5.5040808@.realsqlguy.com...
>> >> >
>> >> >> 1. Run weekly full backups, and frequent transaction log backups
>> > during
>> >> >> the week. I have a script that will do all of this for you:
>> >> >> http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
>> >> >
>> >> > I really can't afford to lose any data, but could settle for a day's
>> > worth
>> >> > loss, and have little space for hundreds of transaction logs. Was
>> > thinking
>> >> > about complete backups nightly and differential throughout the day.
>> >> >
>> >>
>> >> You can modify my script to make it backup nightly. Find the first IF
>> >> statement, remove the entire line, and replace it with:
>> >>
>> >> IF GETDATE() - @.dtLastFullBackup > 1
>> >>
>> >> >> 2. Rebuild indexes nightly or weekly, based on their level of
>> >> >> fragmentation. I have a script that I run nightly, rebuilding only
>> >> >> those that are fragmented beyond 30%:
>> >> >>
>> >> >
>> >
> http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
>> >> >
>> >> > I have not ever rebuilt indexes or even know how to check if they
>> >> > are
>> >> > in
>> >> > trouble (there must be some way to check). My DB only deals with
>> > thousands
>> >> > of rows and so there has been no noticeable degradation in
> performance.
>> >> >
>> >>
>> >> This script will do all of that for you, you just have to schedule it
> to
>> >> run.
>> >>
>> >>
>> >>
>> >> --
>> >> Tracy McKibben
>> >> MCDBA
>> >> http://www.realsqlguy.com
>> >
>> >
>>
>|||Again, what I did was a DBREINDEX on a database that had never had this done
before (using Tracy McKibben's script to rebuild all indexes - recovered a
LOT of pages or potential disk space), then SHRINKDB (recovered the file
space on the hard drive), and then I found the very high Logical Scan
Fragmentation (after the shrink). I then did a defrag of all tables and
indices using INDEXDEFRAG (using the script from BOL). Then for the same
example table that I did a SHOWCONTIG before (below) I got this new report
(and it looks pretty good to me as far as scan density, fragmentation,
etc.). So, I *think* I'm OK with a final db size one-sixth the size of what
I had before which already makes backups/restores about six times faster and
my backup of db backup files to a remote online service faster..
DBCC SHOWCONTIG scanning 'PtHxEvents' table...
Table: 'PtHxEvents' (1366295927); index ID: 1, database ID: 15
TABLE level scan performed.
- Pages Scanned........................: 1524
- Extents Scanned.......................: 194
- Extent Switches.......................: 193
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 98.45% [191:194]
- Logical Scan Fragmentation ..............: 0.52%
- Extent Scan Fragmentation ...............: 12.89%
- Avg. Bytes Free per Page................: 33.2
- Avg. Page Density (full)................: 99.59%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
> How many files do you have in the Filegroup where this table lives?
I wouldn't even know where to look.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ec85Z3FyGHA.3492@.TK2MSFTNGP02.phx.gbl...
> Don,
> I really can't see where the 99% Logical fragmentation is coming from. I
> would try issuing a DBCC DBREINDEX onthe table again and see what it says.
> How many files do you have in the Filegroup where this table lives?
> --
> Andrew J. Kelly SQL MVP
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:%23Jo$O1EyGHA.2220@.TK2MSFTNGP02.phx.gbl...
> > Maybe this is simpler (after DBREINDEX and SHRINKDB):
> >
> > DBCC SHOWCONTIG scanning 'PtHxEvents' table...
> > Table: 'PtHxEvents' (1366295927); index ID: 1, database ID: 15
> > TABLE level scan performed.
> > - Pages Scanned........................: 1524
> > - Extents Scanned.......................: 194
> > - Extent Switches.......................: 194
> > - Avg. Pages per Extent..................: 7.9
> > - Scan Density [Best Count:Actual Count]......: 97.95% [191:195]
> > - Logical Scan Fragmentation ..............: 99.87%
> > - Extent Scan Fragmentation ...............: 12.89%
> > - Avg. Bytes Free per Page................: 33.2
> > - Avg. Page Density (full)................: 99.59%
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >
> > "Don Miller" <nospam@.nospam.com> wrote in message
> > news:uo0mBoEyGHA.356@.TK2MSFTNGP02.phx.gbl...
> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> >> news:OCSStLEyGHA.3844@.TK2MSFTNGP06.phx.gbl...
> >>
> >> First of all, I REALLY appreciate your expert help. These remarkable
> > changes
> >> in size could be that I shunned maintenance over a period of YEARS. It
> > could
> >> easily be that my DB is in actuality so small (<300mb) that performance
> >> issues do not appear and one of the problems was that fragmented
indexes
> >> were continually growing the size of the database unnecessarily.
> >>
> >> Also, are the indexes with the small "t" in front of the table name
maybe
> >> have something to do with clustered indexes?
> >>
> >> > Can you post some of the actual results of the showcontig?
> >>
> >> This morning, I started over again after restoring to my dev machine,
the
> >> last full (simple) backup from production. The first thing I did was do
> > the
> >> DBREINDEX for all tables in the DB, then a SHRINKDB (all using the
Simple
> >> recovery model). The .mdf went 1.4gb to 280mb, and the .ldf went to
2mb.
> >> Here's the showcontig of one table 'Patients' after all of that
happened
> > (is
> >> there an easier to read way to post this?):
> >>
> >> IndexName IndexId Level Pages Rows MinimumRecordSize
> > MaximumRecordSize
> >> AverageRecordSize ForwardedRecords Extents ExtentSwitches
> >> AverageFreeBytes
> >> AveragePageDenisty ScanDensity BestCount ActualCount
LogicalFragmentation
> >> ExtentFragmentation
> >> PK_Patients 1 0 176 NULL NULL NULL NULL NULL 0 23 NULL NULL 92 22
> >> 24
> >> 99 NULL
> >> IX_Patients_ClinicalEDC 2 0 10 NULL NULL NULL NULL NULL 0 2 NULL
> > NULL
> >> 67 2 3 90 NULL
> >> IX_Patients_FirstName 3 0 11 NULL NULL NULL NULL NULL 0 4 NULL
NULL
> > 40
> >> 2 5 100 NULL
> >> IX_Patients_GroupID 4 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
33
> > 1
> >> 3 100 NULL
> >> IX_Patients_LastName 5 0 11 NULL NULL NULL NULL NULL 0 4 NULL
NULL
> > 40
> >> 2 5 100 NULL
> >> IX_Patients_ProviderID 6 0 8 NULL NULL NULL NULL NULL 0 2 NULL
NULL
> > 33
> >> 1 3 100 NULL
> >> IX_Patients_PersonID 7 0 8 NULL NULL NULL NULL NULL 0 1 NULL NULL
> >> 50
> > 1
> >> 2 100 NULL
> >> IX_Patients_LastNameSearch 8 0 11 NULL NULL NULL NULL NULL 0 3
NULL
> >> NULL 50 2 4 100 NULL
> >> IX_Patients_FirstNameSearch 9 0 11 NULL NULL NULL NULL NULL 0 1
> >> NULL
> >> NULL 100 2 2 82 NULL
> >> IX_Patients_UserName 10 0 6 NULL NULL NULL NULL NULL 0 0 NULL
NULL
> > 100
> >> 1 1 83 NULL
> >> IX_Patients_MaritalStatusID 12 0 8 NULL NULL NULL NULL NULL 0 2
> >> NULL
> >> NULL 33 1 3 88 NULL
> >> IX_Patients_updateAuthorID 13 0 8 NULL NULL NULL NULL NULL 0 2
NULL
> >> NULL 33 1 3 75 NULL
> >> IX_Patients_ReligionID 14 0 8 NULL NULL NULL NULL NULL 0 2 NULL
> >> NULL
> >> 33 1 3 75 NULL
> >> IX_Patients_LanguageID 15 0 8 NULL NULL NULL NULL NULL 0 2 NULL
> >> NULL
> >> 33 1 3 75 NULL
> >> IX_Patients_PtRaceID 16 0 8 NULL NULL NULL NULL NULL 0 2 NULL
NULL
> > 33
> >> 1 3 75 NULL
> >> IX_Patients_AuthorID 17 0 8 NULL NULL NULL NULL NULL 0 2 NULL
NULL
> > 33
> >> 1 3 75 NULL
> >> IX_Patients_CtRaceID 39 0 8 NULL NULL NULL NULL NULL 0 2 NULL
NULL
> > 33
> >> 1 3 75 NULL
> >> IX_Patients_FacilityID 40 0 8 NULL NULL NULL NULL NULL 0 2 NULL
> >> NULL
> >> 33 1 3 75 NULL
> >> IX_Patients_OxFinalAuthorDate 49 0 10 NULL NULL NULL NULL NULL 0
4
> >> NULL NULL 40 2 5 90 NULL
> >> IX_Patients_HospitalID 50 0 8 NULL NULL NULL NULL NULL 0 3 NULL
> >> NULL
> >> 25 1 4 75 NULL
> >> tPatients 255 0 194 15709 79 493 91 0 42 41 546 93 60 25 42 99 90
> >>
> >>
> >> > shrinking process usually results in
> >> > fragmenting your indexes all over again in the process. A rebuild
with
> >> DBCC
> >> > DBREINDEX requires about 1.2 times the size of your db in free space
to
> > do
> >> a
> >> > proper rebuild.
> >> >
> >> > So you always need lots of free space
> >> > for rebuilds to operate properly.
> >>
> >> Do you mean free space on the hard drive (which on my dev machine I
have
> >> 30gb to spare and on the production I have 4-5x the size of the
database)
> > or
> >> 'space available' within the database file (like you see when one
brings
> > up
> >> properties for the database)?
> >>
> >> > If your db is that small I suspect you have a lot of tables or
> >> > indexes using mixed extents.
> >>
> >> Yes, I have some 90 tables. I don't know what mixed extents means at
this
> >> point.
> >>
> >> > Essentially anything under 8 pages will share
> >> > as much as 8 different extents for the 8 pages that normally would go
> > all
> >> on
> >> > 1 extent. If you have less than several hundred to a thousand pages
in
> > the
> >> > table I would not be too concerned with some fragmentation.
> >>
> >> My table with the most rows has about 1500 pages
> >>
> >>
> >>
> >>
> >>
> >> Have a look at
> >> > these as well:
> >> >
> >> >
> >> > http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
> >> > considerations
> >> >
> >> >
> >>
> >
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> >> > Index Defrag Best Practices 2000
> >> > http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
> >> > Understanding DBCC SHOWCONTIG
> >> >
> >>
> >
http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp
> >> > Fill Factors
> >> > http://www.sql-server-performance.com/gv_clustered_indexes.asp
> >> > Clustered Indexes
> >> >
> >> >
> >> >
> >> > --
> >> > Andrew J. Kelly SQL MVP
> >> >
> >> > "Don Miller" <nospam@.nospam.com> wrote in message
> >> > news:uUbZgwDyGHA.3512@.TK2MSFTNGP04.phx.gbl...
> >> > > I've been playing with your script (on my production db moved to my
> > dev
> >> > > machine) since I have been very remiss in maintaining my database.
I
> > ran
> >> > > the
> >> > > 'defrag' option on the db (all indexes were about 50% fragmented),
> > then
> >> I
> >> > > did a SHRINKDB, and saw the size of my .mdf (and backup files) go
> >> > > from
> >> > > 1.3GB
> >> > > to around 200MB!
> >> > >
> >> > > Then I read about DBREINDEX and saw that your script handled that
> >> > > too.
> >> So,
> >> > > I
> >> > > ran the rebuild script (on a pristine copy of my prod db without
> >> > > doing
> >> the
> >> > > defrag first), shrunk the database again, and again got the same
> >> reduction
> >> > > in file size. However, when I ran your script in the report mode
> >> > > AFTER
> >> the
> >> > > rebuild and shrink, almost ALL of the files are reported as 75-100%
> >> > > fragmented!!! Also, there are indexes showing up in the report as
> > being
> >> > > 100%
> >> > > fragmented that I can't even find. They are all named like
> >> > > NameOfTable.tNameOfTable with a 't' prefix rather than 'IX_' there.
> >> > >
> >> > > Is there a reason that after a DBREINDEX all the indexes are
reported
> > as
> >> > > almost 100% fragmented?
> >> > >
> >> > > What are those indexes with a 't' in their name?
> >> > >
> >> > > Thanks for any explanation.
> >> > >
> >> > >
> >> > > "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> >> > > news:44EDD97B.5050604@.realsqlguy.com...
> >> > >> Don Miller wrote:
> >> > >> > "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> >> > >> > news:44EDCBB5.5040808@.realsqlguy.com...
> >> > >> >
> >> > >> >> 1. Run weekly full backups, and frequent transaction log
backups
> >> > > during
> >> > >> >> the week. I have a script that will do all of this for you:
> >> > >> >>
http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
> >> > >> >
> >> > >> > I really can't afford to lose any data, but could settle for a
> > day's
> >> > > worth
> >> > >> > loss, and have little space for hundreds of transaction logs.
Was
> >> > > thinking
> >> > >> > about complete backups nightly and differential throughout the
> >> > >> > day.
> >> > >> >
> >> > >>
> >> > >> You can modify my script to make it backup nightly. Find the
first
> > IF
> >> > >> statement, remove the entire line, and replace it with:
> >> > >>
> >> > >> IF GETDATE() - @.dtLastFullBackup > 1
> >> > >>
> >> > >> >> 2. Rebuild indexes nightly or weekly, based on their level of
> >> > >> >> fragmentation. I have a script that I run nightly, rebuilding
> > only
> >> > >> >> those that are fragmented beyond 30%:
> >> > >> >>
> >> > >> >
> >> > >
> >>
> >
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
> >> > >> >
> >> > >> > I have not ever rebuilt indexes or even know how to check if
they
> > are
> >> > >> > in
> >> > >> > trouble (there must be some way to check). My DB only deals with
> >> > > thousands
> >> > >> > of rows and so there has been no noticeable degradation in
> >> performance.
> >> > >> >
> >> > >>
> >> > >> This script will do all of that for you, you just have to schedule
> >> > >> it
> >> to
> >> > >> run.
> >> > >>
> >> > >>
> >> > >>
> >> > >> --
> >> > >> Tracy McKibben
> >> > >> MCDBA
> >> > >> http://www.realsqlguy.com
> >> > >
> >> > >
> >> >
> >> >
> >>
> >>
> >
> >
>|||Again, I appreciate your comments.
> But for this Patients table I would get rid of
> the unused indexes because boy you sure have a lot of them<g>. If the
index
> has a very low selectivity (meaning there are a lot of the same values)
the
> nonclustered index is most likely not even being used.
I *think* the reason I have so many indices is that I read somewhere at some
time by someone I trusted, that if a column is a foreign key EVER used in a
join to the primary key table (or in a WHERE clause), it should be indexed.
If I misread or misunderstood that premise than I do have a LOT of possibly
unnecessary indices.
> I don't have the
> table DDL so this is a guess but columns such as Race, Religion, marital
> status and even HospitalID probably have a lot of rows that match the same
> value.
Yes.
> For instance let take Marital Status. I bet most match 30 or more %
> of the rows. A nonclustered index for a simple lookup is usually useless
> after just a few %, sometimes as low as 2 or 3% even.
If I were looking for all patients who are married (linked to a primary key
table with an ID) and maybe are Caucasian, why wouldn't indexing all
patients by marital status (and race) in the Patient table be faster for
that query?
> What about the first &
> last name indexes? There is a first & last name search index as well.
> What is the difference between the two?
We actually encrypt all patient-identifying information like first and last
name, and since searching by patient name is the most frequently used
function in our application, we essentially denormalized the search strings
rather than do that on the fly with encrypting and decrypting (using
asynchronous calls to the server with Ajax).
> And by free space I mean in the data file not the hard drive. The first
link
> I sent should clear that up for you.
When I look at db properties after all of this reindexing and shrinking it
says available space is like close to 0. I have used the defaults for the
database to grow automatically by 10%.
> --
> Andrew J. Kelly SQL MVP
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:uo0mBoEyGHA.356@.TK2MSFTNGP02.phx.gbl...
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OCSStLEyGHA.3844@.TK2MSFTNGP06.phx.gbl...
> >
> > First of all, I REALLY appreciate your expert help. These remarkable
> > changes
> > in size could be that I shunned maintenance over a period of YEARS. It
> > could
> > easily be that my DB is in actuality so small (<300mb) that performance
> > issues do not appear and one of the problems was that fragmented indexes
> > were continually growing the size of the database unnecessarily.
> >
> > Also, are the indexes with the small "t" in front of the table name
maybe
> > have something to do with clustered indexes?
> >
> >> Can you post some of the actual results of the showcontig?
> >
> > This morning, I started over again after restoring to my dev machine,
the
> > last full (simple) backup from production. The first thing I did was do
> > the
> > DBREINDEX for all tables in the DB, then a SHRINKDB (all using the
Simple
> > recovery model). The .mdf went 1.4gb to 280mb, and the .ldf went to 2mb.
> > Here's the showcontig of one table 'Patients' after all of that happened
> > (is
> > there an easier to read way to post this?):
> >
> > IndexName IndexId Level Pages Rows MinimumRecordSize
> > MaximumRecordSize
> > AverageRecordSize ForwardedRecords Extents ExtentSwitches
AverageFreeBytes
> > AveragePageDenisty ScanDensity BestCount ActualCount
LogicalFragmentation
> > ExtentFragmentation
> > PK_Patients 1 0 176 NULL NULL NULL NULL NULL 0 23 NULL NULL 92 22
24
> > 99 NULL
> > IX_Patients_ClinicalEDC 2 0 10 NULL NULL NULL NULL NULL 0 2 NULL
NULL
> > 67 2 3 90 NULL
> > IX_Patients_FirstName 3 0 11 NULL NULL NULL NULL NULL 0 4 NULL NULL
> > 40
> > 2 5 100 NULL
> > IX_Patients_GroupID 4 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL 33
1
> > 3 100 NULL
> > IX_Patients_LastName 5 0 11 NULL NULL NULL NULL NULL 0 4 NULL NULL
40
> > 2 5 100 NULL
> > IX_Patients_ProviderID 6 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
> > 33
> > 1 3 100 NULL
> > IX_Patients_PersonID 7 0 8 NULL NULL NULL NULL NULL 0 1 NULL NULL
50
> > 1
> > 2 100 NULL
> > IX_Patients_LastNameSearch 8 0 11 NULL NULL NULL NULL NULL 0 3 NULL
> > NULL 50 2 4 100 NULL
> > IX_Patients_FirstNameSearch 9 0 11 NULL NULL NULL NULL NULL 0 1
NULL
> > NULL 100 2 2 82 NULL
> > IX_Patients_UserName 10 0 6 NULL NULL NULL NULL NULL 0 0 NULL NULL
> > 100
> > 1 1 83 NULL
> > IX_Patients_MaritalStatusID 12 0 8 NULL NULL NULL NULL NULL 0 2
NULL
> > NULL 33 1 3 88 NULL
> > IX_Patients_updateAuthorID 13 0 8 NULL NULL NULL NULL NULL 0 2 NULL
> > NULL 33 1 3 75 NULL
> > IX_Patients_ReligionID 14 0 8 NULL NULL NULL NULL NULL 0 2 NULL
NULL
> > 33 1 3 75 NULL
> > IX_Patients_LanguageID 15 0 8 NULL NULL NULL NULL NULL 0 2 NULL
NULL
> > 33 1 3 75 NULL
> > IX_Patients_PtRaceID 16 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
33
> > 1 3 75 NULL
> > IX_Patients_AuthorID 17 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
33
> > 1 3 75 NULL
> > IX_Patients_CtRaceID 39 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
33
> > 1 3 75 NULL
> > IX_Patients_FacilityID 40 0 8 NULL NULL NULL NULL NULL 0 2 NULL
NULL
> > 33 1 3 75 NULL
> > IX_Patients_OxFinalAuthorDate 49 0 10 NULL NULL NULL NULL NULL 0 4
> > NULL NULL 40 2 5 90 NULL
> > IX_Patients_HospitalID 50 0 8 NULL NULL NULL NULL NULL 0 3 NULL
NULL
> > 25 1 4 75 NULL
> > tPatients 255 0 194 15709 79 493 91 0 42 41 546 93 60 25 42 99 90
> >
> >
> >> shrinking process usually results in
> >> fragmenting your indexes all over again in the process. A rebuild with
> > DBCC
> >> DBREINDEX requires about 1.2 times the size of your db in free space to
> >> do
> > a
> >> proper rebuild.
> >>
> >> So you always need lots of free space
> >> for rebuilds to operate properly.
> >
> > Do you mean free space on the hard drive (which on my dev machine I have
> > 30gb to spare and on the production I have 4-5x the size of the
database)
> > or
> > 'space available' within the database file (like you see when one brings
> > up
> > properties for the database)?
> >
> >> If your db is that small I suspect you have a lot of tables or
> >> indexes using mixed extents.
> >
> > Yes, I have some 90 tables. I don't know what mixed extents means at
this
> > point.
> >
> >> Essentially anything under 8 pages will share
> >> as much as 8 different extents for the 8 pages that normally would go
all
> > on
> >> 1 extent. If you have less than several hundred to a thousand pages in
> >> the
> >> table I would not be too concerned with some fragmentation.
> >
> > My table with the most rows has about 1500 pages
> >
> >
> >
> >
> >
> > Have a look at
> >> these as well:
> >>
> >>
> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
> >> considerations
> >>
> >>
> >
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> >> Index Defrag Best Practices 2000
> >> http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
> >> Understanding DBCC SHOWCONTIG
> >>
> >
http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp
> >> Fill Factors
> >> http://www.sql-server-performance.com/gv_clustered_indexes.asp
> >> Clustered Indexes
> >>
> >>
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >> "Don Miller" <nospam@.nospam.com> wrote in message
> >> news:uUbZgwDyGHA.3512@.TK2MSFTNGP04.phx.gbl...
> >> > I've been playing with your script (on my production db moved to my
dev
> >> > machine) since I have been very remiss in maintaining my database. I
> >> > ran
> >> > the
> >> > 'defrag' option on the db (all indexes were about 50% fragmented),
then
> > I
> >> > did a SHRINKDB, and saw the size of my .mdf (and backup files) go
from
> >> > 1.3GB
> >> > to around 200MB!
> >> >
> >> > Then I read about DBREINDEX and saw that your script handled that
too.
> > So,
> >> > I
> >> > ran the rebuild script (on a pristine copy of my prod db without
doing
> > the
> >> > defrag first), shrunk the database again, and again got the same
> > reduction
> >> > in file size. However, when I ran your script in the report mode
AFTER
> > the
> >> > rebuild and shrink, almost ALL of the files are reported as 75-100%
> >> > fragmented!!! Also, there are indexes showing up in the report as
being
> >> > 100%
> >> > fragmented that I can't even find. They are all named like
> >> > NameOfTable.tNameOfTable with a 't' prefix rather than 'IX_' there.
> >> >
> >> > Is there a reason that after a DBREINDEX all the indexes are reported
> >> > as
> >> > almost 100% fragmented?
> >> >
> >> > What are those indexes with a 't' in their name?
> >> >
> >> > Thanks for any explanation.
> >> >
> >> >
> >> > "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> >> > news:44EDD97B.5050604@.realsqlguy.com...
> >> >> Don Miller wrote:
> >> >> > "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> >> >> > news:44EDCBB5.5040808@.realsqlguy.com...
> >> >> >
> >> >> >> 1. Run weekly full backups, and frequent transaction log backups
> >> > during
> >> >> >> the week. I have a script that will do all of this for you:
> >> >> >>
http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
> >> >> >
> >> >> > I really can't afford to lose any data, but could settle for a
day's
> >> > worth
> >> >> > loss, and have little space for hundreds of transaction logs. Was
> >> > thinking
> >> >> > about complete backups nightly and differential throughout the
day.
> >> >> >
> >> >>
> >> >> You can modify my script to make it backup nightly. Find the first
IF
> >> >> statement, remove the entire line, and replace it with:
> >> >>
> >> >> IF GETDATE() - @.dtLastFullBackup > 1
> >> >>
> >> >> >> 2. Rebuild indexes nightly or weekly, based on their level of
> >> >> >> fragmentation. I have a script that I run nightly, rebuilding
only
> >> >> >> those that are fragmented beyond 30%:
> >> >> >>
> >> >> >
> >> >
> >
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
> >> >> >
> >> >> > I have not ever rebuilt indexes or even know how to check if they
> >> >> > are
> >> >> > in
> >> >> > trouble (there must be some way to check). My DB only deals with
> >> > thousands
> >> >> > of rows and so there has been no noticeable degradation in
> > performance.
> >> >> >
> >> >>
> >> >> This script will do all of that for you, you just have to schedule
it
> > to
> >> >> run.
> >> >>
> >> >>
> >> >>
> >> >> --
> >> >> Tracy McKibben
> >> >> MCDBA
> >> >> http://www.realsqlguy.com
> >> >
> >> >
> >>
> >>
> >
> >
>|||That is good that you don't have much fragmentation but be aware there are
differences between INDEXDEFRAG and DBREINDEX. See this for more details:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
I wouldn't worry about the files. If you don't know you probably never
created anything other than the defaults.
--
Andrew J. Kelly SQL MVP
"Don Miller" <nospam@.nospam.com> wrote in message
news:Oqm%23YmGyGHA.2400@.TK2MSFTNGP03.phx.gbl...
> Again, what I did was a DBREINDEX on a database that had never had this
> done
> before (using Tracy McKibben's script to rebuild all indexes - recovered a
> LOT of pages or potential disk space), then SHRINKDB (recovered the file
> space on the hard drive), and then I found the very high Logical Scan
> Fragmentation (after the shrink). I then did a defrag of all tables and
> indices using INDEXDEFRAG (using the script from BOL). Then for the same
> example table that I did a SHOWCONTIG before (below) I got this new report
> (and it looks pretty good to me as far as scan density, fragmentation,
> etc.). So, I *think* I'm OK with a final db size one-sixth the size of
> what
> I had before which already makes backups/restores about six times faster
> and
> my backup of db backup files to a remote online service faster..
> DBCC SHOWCONTIG scanning 'PtHxEvents' table...
> Table: 'PtHxEvents' (1366295927); index ID: 1, database ID: 15
> TABLE level scan performed.
> - Pages Scanned........................: 1524
> - Extents Scanned.......................: 194
> - Extent Switches.......................: 193
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 98.45% [191:194]
> - Logical Scan Fragmentation ..............: 0.52%
> - Extent Scan Fragmentation ...............: 12.89%
> - Avg. Bytes Free per Page................: 33.2
> - Avg. Page Density (full)................: 99.59%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>> How many files do you have in the Filegroup where this table lives?
> I wouldn't even know where to look.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ec85Z3FyGHA.3492@.TK2MSFTNGP02.phx.gbl...
>> Don,
>> I really can't see where the 99% Logical fragmentation is coming from. I
>> would try issuing a DBCC DBREINDEX onthe table again and see what it
>> says.
>> How many files do you have in the Filegroup where this table lives?
>> --
>> Andrew J. Kelly SQL MVP
>> "Don Miller" <nospam@.nospam.com> wrote in message
>> news:%23Jo$O1EyGHA.2220@.TK2MSFTNGP02.phx.gbl...
>> > Maybe this is simpler (after DBREINDEX and SHRINKDB):
>> >
>> > DBCC SHOWCONTIG scanning 'PtHxEvents' table...
>> > Table: 'PtHxEvents' (1366295927); index ID: 1, database ID: 15
>> > TABLE level scan performed.
>> > - Pages Scanned........................: 1524
>> > - Extents Scanned.......................: 194
>> > - Extent Switches.......................: 194
>> > - Avg. Pages per Extent..................: 7.9
>> > - Scan Density [Best Count:Actual Count]......: 97.95% [191:195]
>> > - Logical Scan Fragmentation ..............: 99.87%
>> > - Extent Scan Fragmentation ...............: 12.89%
>> > - Avg. Bytes Free per Page................: 33.2
>> > - Avg. Page Density (full)................: 99.59%
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> >
>> > "Don Miller" <nospam@.nospam.com> wrote in message
>> > news:uo0mBoEyGHA.356@.TK2MSFTNGP02.phx.gbl...
>> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> >> news:OCSStLEyGHA.3844@.TK2MSFTNGP06.phx.gbl...
>> >>
>> >> First of all, I REALLY appreciate your expert help. These remarkable
>> > changes
>> >> in size could be that I shunned maintenance over a period of YEARS. It
>> > could
>> >> easily be that my DB is in actuality so small (<300mb) that
>> >> performance
>> >> issues do not appear and one of the problems was that fragmented
> indexes
>> >> were continually growing the size of the database unnecessarily.
>> >>
>> >> Also, are the indexes with the small "t" in front of the table name
> maybe
>> >> have something to do with clustered indexes?
>> >>
>> >> > Can you post some of the actual results of the showcontig?
>> >>
>> >> This morning, I started over again after restoring to my dev machine,
> the
>> >> last full (simple) backup from production. The first thing I did was
>> >> do
>> > the
>> >> DBREINDEX for all tables in the DB, then a SHRINKDB (all using the
> Simple
>> >> recovery model). The .mdf went 1.4gb to 280mb, and the .ldf went to
> 2mb.
>> >> Here's the showcontig of one table 'Patients' after all of that
> happened
>> > (is
>> >> there an easier to read way to post this?):
>> >>
>> >> IndexName IndexId Level Pages Rows MinimumRecordSize
>> > MaximumRecordSize
>> >> AverageRecordSize ForwardedRecords Extents ExtentSwitches
>> >> AverageFreeBytes
>> >> AveragePageDenisty ScanDensity BestCount ActualCount
> LogicalFragmentation
>> >> ExtentFragmentation
>> >> PK_Patients 1 0 176 NULL NULL NULL NULL NULL 0 23 NULL NULL 92
>> >> 22
>> >> 24
>> >> 99 NULL
>> >> IX_Patients_ClinicalEDC 2 0 10 NULL NULL NULL NULL NULL 0 2 NULL
>> > NULL
>> >> 67 2 3 90 NULL
>> >> IX_Patients_FirstName 3 0 11 NULL NULL NULL NULL NULL 0 4 NULL
> NULL
>> > 40
>> >> 2 5 100 NULL
>> >> IX_Patients_GroupID 4 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
> 33
>> > 1
>> >> 3 100 NULL
>> >> IX_Patients_LastName 5 0 11 NULL NULL NULL NULL NULL 0 4 NULL
> NULL
>> > 40
>> >> 2 5 100 NULL
>> >> IX_Patients_ProviderID 6 0 8 NULL NULL NULL NULL NULL 0 2 NULL
> NULL
>> > 33
>> >> 1 3 100 NULL
>> >> IX_Patients_PersonID 7 0 8 NULL NULL NULL NULL NULL 0 1 NULL
>> >> NULL
>> >> 50
>> > 1
>> >> 2 100 NULL
>> >> IX_Patients_LastNameSearch 8 0 11 NULL NULL NULL NULL NULL 0 3
> NULL
>> >> NULL 50 2 4 100 NULL
>> >> IX_Patients_FirstNameSearch 9 0 11 NULL NULL NULL NULL NULL 0 1
>> >> NULL
>> >> NULL 100 2 2 82 NULL
>> >> IX_Patients_UserName 10 0 6 NULL NULL NULL NULL NULL 0 0 NULL
> NULL
>> > 100
>> >> 1 1 83 NULL
>> >> IX_Patients_MaritalStatusID 12 0 8 NULL NULL NULL NULL NULL 0 2
>> >> NULL
>> >> NULL 33 1 3 88 NULL
>> >> IX_Patients_updateAuthorID 13 0 8 NULL NULL NULL NULL NULL 0 2
> NULL
>> >> NULL 33 1 3 75 NULL
>> >> IX_Patients_ReligionID 14 0 8 NULL NULL NULL NULL NULL 0 2 NULL
>> >> NULL
>> >> 33 1 3 75 NULL
>> >> IX_Patients_LanguageID 15 0 8 NULL NULL NULL NULL NULL 0 2 NULL
>> >> NULL
>> >> 33 1 3 75 NULL
>> >> IX_Patients_PtRaceID 16 0 8 NULL NULL NULL NULL NULL 0 2 NULL
> NULL
>> > 33
>> >> 1 3 75 NULL
>> >> IX_Patients_AuthorID 17 0 8 NULL NULL NULL NULL NULL 0 2 NULL
> NULL
>> > 33
>> >> 1 3 75 NULL
>> >> IX_Patients_CtRaceID 39 0 8 NULL NULL NULL NULL NULL 0 2 NULL
> NULL
>> > 33
>> >> 1 3 75 NULL
>> >> IX_Patients_FacilityID 40 0 8 NULL NULL NULL NULL NULL 0 2 NULL
>> >> NULL
>> >> 33 1 3 75 NULL
>> >> IX_Patients_OxFinalAuthorDate 49 0 10 NULL NULL NULL NULL NULL 0
> 4
>> >> NULL NULL 40 2 5 90 NULL
>> >> IX_Patients_HospitalID 50 0 8 NULL NULL NULL NULL NULL 0 3 NULL
>> >> NULL
>> >> 25 1 4 75 NULL
>> >> tPatients 255 0 194 15709 79 493 91 0 42 41 546 93 60 25 42 99
>> >> 90
>> >>
>> >>
>> >> > shrinking process usually results in
>> >> > fragmenting your indexes all over again in the process. A rebuild
> with
>> >> DBCC
>> >> > DBREINDEX requires about 1.2 times the size of your db in free space
> to
>> > do
>> >> a
>> >> > proper rebuild.
>> >> >
>> >> > So you always need lots of free space
>> >> > for rebuilds to operate properly.
>> >>
>> >> Do you mean free space on the hard drive (which on my dev machine I
> have
>> >> 30gb to spare and on the production I have 4-5x the size of the
> database)
>> > or
>> >> 'space available' within the database file (like you see when one
> brings
>> > up
>> >> properties for the database)?
>> >>
>> >> > If your db is that small I suspect you have a lot of tables or
>> >> > indexes using mixed extents.
>> >>
>> >> Yes, I have some 90 tables. I don't know what mixed extents means at
> this
>> >> point.
>> >>
>> >> > Essentially anything under 8 pages will share
>> >> > as much as 8 different extents for the 8 pages that normally would
>> >> > go
>> > all
>> >> on
>> >> > 1 extent. If you have less than several hundred to a thousand pages
> in
>> > the
>> >> > table I would not be too concerned with some fragmentation.
>> >>
>> >> My table with the most rows has about 1500 pages
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> Have a look at
>> >> > these as well:
>> >> >
>> >> >
>> >> > http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
>> >> > considerations
>> >> >
>> >> >
>> >>
>> >
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> >> > Index Defrag Best Practices 2000
>> >> > http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
>> >> > Understanding DBCC SHOWCONTIG
>> >> >
>> >>
>> >
> http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp
>> >> > Fill Factors
>> >> > http://www.sql-server-performance.com/gv_clustered_indexes.asp
>> >> > Clustered Indexes
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > Andrew J. Kelly SQL MVP
>> >> >
>> >> > "Don Miller" <nospam@.nospam.com> wrote in message
>> >> > news:uUbZgwDyGHA.3512@.TK2MSFTNGP04.phx.gbl...
>> >> > > I've been playing with your script (on my production db moved to
>> >> > > my
>> > dev
>> >> > > machine) since I have been very remiss in maintaining my database.
> I
>> > ran
>> >> > > the
>> >> > > 'defrag' option on the db (all indexes were about 50% fragmented),
>> > then
>> >> I
>> >> > > did a SHRINKDB, and saw the size of my .mdf (and backup files) go
>> >> > > from
>> >> > > 1.3GB
>> >> > > to around 200MB!
>> >> > >
>> >> > > Then I read about DBREINDEX and saw that your script handled that
>> >> > > too.
>> >> So,
>> >> > > I
>> >> > > ran the rebuild script (on a pristine copy of my prod db without
>> >> > > doing
>> >> the
>> >> > > defrag first), shrunk the database again, and again got the same
>> >> reduction
>> >> > > in file size. However, when I ran your script in the report mode
>> >> > > AFTER
>> >> the
>> >> > > rebuild and shrink, almost ALL of the files are reported as
>> >> > > 75-100%
>> >> > > fragmented!!! Also, there are indexes showing up in the report as
>> > being
>> >> > > 100%
>> >> > > fragmented that I can't even find. They are all named like
>> >> > > NameOfTable.tNameOfTable with a 't' prefix rather than 'IX_'
>> >> > > there.
>> >> > >
>> >> > > Is there a reason that after a DBREINDEX all the indexes are
> reported
>> > as
>> >> > > almost 100% fragmented?
>> >> > >
>> >> > > What are those indexes with a 't' in their name?
>> >> > >
>> >> > > Thanks for any explanation.
>> >> > >
>> >> > >
>> >> > > "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> >> > > news:44EDD97B.5050604@.realsqlguy.com...
>> >> > >> Don Miller wrote:
>> >> > >> > "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> >> > >> > news:44EDCBB5.5040808@.realsqlguy.com...
>> >> > >> >
>> >> > >> >> 1. Run weekly full backups, and frequent transaction log
> backups
>> >> > > during
>> >> > >> >> the week. I have a script that will do all of this for you:
>> >> > >> >>
> http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
>> >> > >> >
>> >> > >> > I really can't afford to lose any data, but could settle for a
>> > day's
>> >> > > worth
>> >> > >> > loss, and have little space for hundreds of transaction logs.
> Was
>> >> > > thinking
>> >> > >> > about complete backups nightly and differential throughout the
>> >> > >> > day.
>> >> > >> >
>> >> > >>
>> >> > >> You can modify my script to make it backup nightly. Find the
> first
>> > IF
>> >> > >> statement, remove the entire line, and replace it with:
>> >> > >>
>> >> > >> IF GETDATE() - @.dtLastFullBackup > 1
>> >> > >>
>> >> > >> >> 2. Rebuild indexes nightly or weekly, based on their level of
>> >> > >> >> fragmentation. I have a script that I run nightly, rebuilding
>> > only
>> >> > >> >> those that are fragmented beyond 30%:
>> >> > >> >>
>> >> > >> >
>> >> > >
>> >>
>> >
> http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
>> >> > >> >
>> >> > >> > I have not ever rebuilt indexes or even know how to check if
> they
>> > are
>> >> > >> > in
>> >> > >> > trouble (there must be some way to check). My DB only deals
>> >> > >> > with
>> >> > > thousands
>> >> > >> > of rows and so there has been no noticeable degradation in
>> >> performance.
>> >> > >> >
>> >> > >>
>> >> > >> This script will do all of that for you, you just have to
>> >> > >> schedule
>> >> > >> it
>> >> to
>> >> > >> run.
>> >> > >>
>> >> > >>
>> >> > >>
>> >> > >> --
>> >> > >> Tracy McKibben
>> >> > >> MCDBA
>> >> > >> http://www.realsqlguy.com
>> >> > >
>> >> > >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||The index mainly needs to be on the other table being joined more than the
main one. If you are joining Patient with marital status the WHERE clause
should be against the Patient table (IE: Patient ID) which needs an index.
But for Marital status the index will need to be on the Marital Status table
for the PK. If you have RI between the two you may need both.
> If I were looking for all patients who are married (linked to a primary
> key
> table with an ID) and maybe are Caucasian, why wouldn't indexing all
> patients by marital status (and race) in the Patient table be faster for
> that query?
That would be faster IF the number matching your query were in ther few %
range I mentioned. But if 50% are married and Caucasian SQL Server will scan
the table instead of using the index. You can see what it uses with the
graphical showplan in Query Analyzer. There is no need to guess.
Occasionally you need to run DBCC UPDATEUSAGE onthe db to keep the free
space stats up to date. Try that and then look at the properties. You may
also want to right click on the database node in EM and choose View -
Taskpad to see how much data / free space there is for each file and the log
file.
--
Andrew J. Kelly SQL MVP
"Don Miller" <nospam@.nospam.com> wrote in message
news:ewOY2DHyGHA.3844@.TK2MSFTNGP06.phx.gbl...
> Again, I appreciate your comments.
>> But for this Patients table I would get rid of
>> the unused indexes because boy you sure have a lot of them<g>. If the
> index
>> has a very low selectivity (meaning there are a lot of the same values)
> the
>> nonclustered index is most likely not even being used.
> I *think* the reason I have so many indices is that I read somewhere at
> some
> time by someone I trusted, that if a column is a foreign key EVER used in
> a
> join to the primary key table (or in a WHERE clause), it should be
> indexed.
> If I misread or misunderstood that premise than I do have a LOT of
> possibly
> unnecessary indices.
>> I don't have the
>> table DDL so this is a guess but columns such as Race, Religion, marital
>> status and even HospitalID probably have a lot of rows that match the
>> same
>> value.
> Yes.
>> For instance let take Marital Status. I bet most match 30 or more %
>> of the rows. A nonclustered index for a simple lookup is usually useless
>> after just a few %, sometimes as low as 2 or 3% even.
> If I were looking for all patients who are married (linked to a primary
> key
> table with an ID) and maybe are Caucasian, why wouldn't indexing all
> patients by marital status (and race) in the Patient table be faster for
> that query?
>> What about the first &
>> last name indexes? There is a first & last name search index as well.
>> What is the difference between the two?
> We actually encrypt all patient-identifying information like first and
> last
> name, and since searching by patient name is the most frequently used
> function in our application, we essentially denormalized the search
> strings
> rather than do that on the fly with encrypting and decrypting (using
> asynchronous calls to the server with Ajax).
>> And by free space I mean in the data file not the hard drive. The first
> link
>> I sent should clear that up for you.
> When I look at db properties after all of this reindexing and shrinking it
> says available space is like close to 0. I have used the defaults for the
> database to grow automatically by 10%.
>> --
>> Andrew J. Kelly SQL MVP
>> "Don Miller" <nospam@.nospam.com> wrote in message
>> news:uo0mBoEyGHA.356@.TK2MSFTNGP02.phx.gbl...
>> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> > news:OCSStLEyGHA.3844@.TK2MSFTNGP06.phx.gbl...
>> >
>> > First of all, I REALLY appreciate your expert help. These remarkable
>> > changes
>> > in size could be that I shunned maintenance over a period of YEARS. It
>> > could
>> > easily be that my DB is in actuality so small (<300mb) that performance
>> > issues do not appear and one of the problems was that fragmented
>> > indexes
>> > were continually growing the size of the database unnecessarily.
>> >
>> > Also, are the indexes with the small "t" in front of the table name
> maybe
>> > have something to do with clustered indexes?
>> >
>> >> Can you post some of the actual results of the showcontig?
>> >
>> > This morning, I started over again after restoring to my dev machine,
> the
>> > last full (simple) backup from production. The first thing I did was do
>> > the
>> > DBREINDEX for all tables in the DB, then a SHRINKDB (all using the
> Simple
>> > recovery model). The .mdf went 1.4gb to 280mb, and the .ldf went to
>> > 2mb.
>> > Here's the showcontig of one table 'Patients' after all of that
>> > happened
>> > (is
>> > there an easier to read way to post this?):
>> >
>> > IndexName IndexId Level Pages Rows MinimumRecordSize
>> > MaximumRecordSize
>> > AverageRecordSize ForwardedRecords Extents ExtentSwitches
> AverageFreeBytes
>> > AveragePageDenisty ScanDensity BestCount ActualCount
> LogicalFragmentation
>> > ExtentFragmentation
>> > PK_Patients 1 0 176 NULL NULL NULL NULL NULL 0 23 NULL NULL 92 22
> 24
>> > 99 NULL
>> > IX_Patients_ClinicalEDC 2 0 10 NULL NULL NULL NULL NULL 0 2 NULL
> NULL
>> > 67 2 3 90 NULL
>> > IX_Patients_FirstName 3 0 11 NULL NULL NULL NULL NULL 0 4 NULL
>> > NULL
>> > 40
>> > 2 5 100 NULL
>> > IX_Patients_GroupID 4 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
>> > 33
> 1
>> > 3 100 NULL
>> > IX_Patients_LastName 5 0 11 NULL NULL NULL NULL NULL 0 4 NULL NULL
> 40
>> > 2 5 100 NULL
>> > IX_Patients_ProviderID 6 0 8 NULL NULL NULL NULL NULL 0 2 NULL
>> > NULL
>> > 33
>> > 1 3 100 NULL
>> > IX_Patients_PersonID 7 0 8 NULL NULL NULL NULL NULL 0 1 NULL NULL
> 50
>> > 1
>> > 2 100 NULL
>> > IX_Patients_LastNameSearch 8 0 11 NULL NULL NULL NULL NULL 0 3
>> > NULL
>> > NULL 50 2 4 100 NULL
>> > IX_Patients_FirstNameSearch 9 0 11 NULL NULL NULL NULL NULL 0 1
> NULL
>> > NULL 100 2 2 82 NULL
>> > IX_Patients_UserName 10 0 6 NULL NULL NULL NULL NULL 0 0 NULL NULL
>> > 100
>> > 1 1 83 NULL
>> > IX_Patients_MaritalStatusID 12 0 8 NULL NULL NULL NULL NULL 0 2
> NULL
>> > NULL 33 1 3 88 NULL
>> > IX_Patients_updateAuthorID 13 0 8 NULL NULL NULL NULL NULL 0 2
>> > NULL
>> > NULL 33 1 3 75 NULL
>> > IX_Patients_ReligionID 14 0 8 NULL NULL NULL NULL NULL 0 2 NULL
> NULL
>> > 33 1 3 75 NULL
>> > IX_Patients_LanguageID 15 0 8 NULL NULL NULL NULL NULL 0 2 NULL
> NULL
>> > 33 1 3 75 NULL
>> > IX_Patients_PtRaceID 16 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
> 33
>> > 1 3 75 NULL
>> > IX_Patients_AuthorID 17 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
> 33
>> > 1 3 75 NULL
>> > IX_Patients_CtRaceID 39 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL
> 33
>> > 1 3 75 NULL
>> > IX_Patients_FacilityID 40 0 8 NULL NULL NULL NULL NULL 0 2 NULL
> NULL
>> > 33 1 3 75 NULL
>> > IX_Patients_OxFinalAuthorDate 49 0 10 NULL NULL NULL NULL NULL 0 4
>> > NULL NULL 40 2 5 90 NULL
>> > IX_Patients_HospitalID 50 0 8 NULL NULL NULL NULL NULL 0 3 NULL
> NULL
>> > 25 1 4 75 NULL
>> > tPatients 255 0 194 15709 79 493 91 0 42 41 546 93 60 25 42 99 90
>> >
>> >
>> >> shrinking process usually results in
>> >> fragmenting your indexes all over again in the process. A rebuild with
>> > DBCC
>> >> DBREINDEX requires about 1.2 times the size of your db in free space
>> >> to
>> >> do
>> > a
>> >> proper rebuild.
>> >>
>> >> So you always need lots of free space
>> >> for rebuilds to operate properly.
>> >
>> > Do you mean free space on the hard drive (which on my dev machine I
>> > have
>> > 30gb to spare and on the production I have 4-5x the size of the
> database)
>> > or
>> > 'space available' within the database file (like you see when one
>> > brings
>> > up
>> > properties for the database)?
>> >
>> >> If your db is that small I suspect you have a lot of tables or
>> >> indexes using mixed extents.
>> >
>> > Yes, I have some 90 tables. I don't know what mixed extents means at
> this
>> > point.
>> >
>> >> Essentially anything under 8 pages will share
>> >> as much as 8 different extents for the 8 pages that normally would go
> all
>> > on
>> >> 1 extent. If you have less than several hundred to a thousand pages in
>> >> the
>> >> table I would not be too concerned with some fragmentation.
>> >
>> > My table with the most rows has about 1500 pages
>> >
>> >
>> >
>> >
>> >
>> > Have a look at
>> >> these as well:
>> >>
>> >>
>> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
>> >> considerations
>> >>
>> >>
>> >
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> >> Index Defrag Best Practices 2000
>> >> http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
>> >> Understanding DBCC SHOWCONTIG
>> >>
>> >
> http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp
>> >> Fill Factors
>> >> http://www.sql-server-performance.com/gv_clustered_indexes.asp
>> >> Clustered Indexes
>> >>
>> >>
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >> "Don Miller" <nospam@.nospam.com> wrote in message
>> >> news:uUbZgwDyGHA.3512@.TK2MSFTNGP04.phx.gbl...
>> >> > I've been playing with your script (on my production db moved to my
> dev
>> >> > machine) since I have been very remiss in maintaining my database. I
>> >> > ran
>> >> > the
>> >> > 'defrag' option on the db (all indexes were about 50% fragmented),
> then
>> > I
>> >> > did a SHRINKDB, and saw the size of my .mdf (and backup files) go
> from
>> >> > 1.3GB
>> >> > to around 200MB!
>> >> >
>> >> > Then I read about DBREINDEX and saw that your script handled that
> too.
>> > So,
>> >> > I
>> >> > ran the rebuild script (on a pristine copy of my prod db without
> doing
>> > the
>> >> > defrag first), shrunk the database again, and again got the same
>> > reduction
>> >> > in file size. However, when I ran your script in the report mode
> AFTER
>> > the
>> >> > rebuild and shrink, almost ALL of the files are reported as 75-100%
>> >> > fragmented!!! Also, there are indexes showing up in the report as
> being
>> >> > 100%
>> >> > fragmented that I can't even find. They are all named like
>> >> > NameOfTable.tNameOfTable with a 't' prefix rather than 'IX_' there.
>> >> >
>> >> > Is there a reason that after a DBREINDEX all the indexes are
>> >> > reported
>> >> > as
>> >> > almost 100% fragmented?
>> >> >
>> >> > What are those indexes with a 't' in their name?
>> >> >
>> >> > Thanks for any explanation.
>> >> >
>> >> >
>> >> > "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> >> > news:44EDD97B.5050604@.realsqlguy.com...
>> >> >> Don Miller wrote:
>> >> >> > "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> >> >> > news:44EDCBB5.5040808@.realsqlguy.com...
>> >> >> >
>> >> >> >> 1. Run weekly full backups, and frequent transaction log
>> >> >> >> backups
>> >> > during
>> >> >> >> the week. I have a script that will do all of this for you:
>> >> >> >>
> http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
>> >> >> >
>> >> >> > I really can't afford to lose any data, but could settle for a
> day's
>> >> > worth
>> >> >> > loss, and have little space for hundreds of transaction logs. Was
>> >> > thinking
>> >> >> > about complete backups nightly and differential throughout the
> day.
>> >> >> >
>> >> >>
>> >> >> You can modify my script to make it backup nightly. Find the first
> IF
>> >> >> statement, remove the entire line, and replace it with:
>> >> >>
>> >> >> IF GETDATE() - @.dtLastFullBackup > 1
>> >> >>
>> >> >> >> 2. Rebuild indexes nightly or weekly, based on their level of
>> >> >> >> fragmentation. I have a script that I run nightly, rebuilding
> only
>> >> >> >> those that are fragmented beyond 30%:
>> >> >> >>
>> >> >> >
>> >> >
>> >
> http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
>> >> >> >
>> >> >> > I have not ever rebuilt indexes or even know how to check if they
>> >> >> > are
>> >> >> > in
>> >> >> > trouble (there must be some way to check). My DB only deals with
>> >> > thousands
>> >> >> > of rows and so there has been no noticeable degradation in
>> > performance.
>> >> >> >
>> >> >>
>> >> >> This script will do all of that for you, you just have to schedule
> it
>> > to
>> >> >> run.
>> >> >>
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Tracy McKibben
>> >> >> MCDBA
>> >> >> http://www.realsqlguy.com
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||Don Miller wrote:
> I've been playing with your script (on my production db moved to my dev
> machine) since I have been very remiss in maintaining my database. I ran the
> 'defrag' option on the db (all indexes were about 50% fragmented), then I
> did a SHRINKDB, and saw the size of my .mdf (and backup files) go from 1.3GB
> to around 200MB!
> Then I read about DBREINDEX and saw that your script handled that too. So, I
> ran the rebuild script (on a pristine copy of my prod db without doing the
> defrag first), shrunk the database again, and again got the same reduction
> in file size. However, when I ran your script in the report mode AFTER the
> rebuild and shrink, almost ALL of the files are reported as 75-100%
> fragmented!!! Also, there are indexes showing up in the report as being 100%
> fragmented that I can't even find. They are all named like
> NameOfTable.tNameOfTable with a 't' prefix rather than 'IX_' there.
> Is there a reason that after a DBREINDEX all the indexes are reported as
> almost 100% fragmented?
> What are those indexes with a 't' in their name?
> Thanks for any explanation.
>
Sorry for the delayed response, I took a long weekend... Seems like
Andrew has explained everything pretty well, but I'll toss in a couple
of comments. The massive change in your database size is due to the
fact that your indexes, after being defragged, are now tightly compacted
into clean, contiguous units, instead of being "full of holes", so to
speak. Unfortunately, when you shrank the database, that was all
undone. Shrinking the database will reorganize data pages to make the
files as small as possible. This often results in pages being seperated
from their peers, and being scattered throughout the database.
My suggestion would be to add another 100MB to your database, and then
run the defrag script again. You should see a much different result.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||As a workaround (because it worked), I wrote a script that first rebuilt the
indexes for all tables in my database using DBREINDEX (I think I grabbed it
off BOL), then did a DBCC SHRINKDATABASE (myDB, 10), and then used major
portions of your script to fix all of the now 99% fragmented indexes using
DBCC INDEXDEFRAG. Then, in the end, my DB was a tenth of its size, and the
indexes had high scan densities and low fragmentation.
> My suggestion would be to add another 100MB to your database, and then
> run the defrag script again. You should see a much different result.
I don't know how to add 100mb to my database.
During my experimentation, when I did SHRINKDATABASE without touching the
indexes, nothing happened to physical file sizes. I'm also pretty sure when
I ran the defrag script alone on the bloated DB, there was no reduction in
the physical file sizes either. It does seem that the indexes have to be
"fixed" before the physical files can become shorter. So, I somehow came up
with the script above and it worked. If I could eliminate either the first
DBREINDEX or the second INDEXDEFRAG, that would be wonderful.
Thanks for your help (and next weekend should be even longer).
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44F2EF97.907@.realsqlguy.com...
> Don Miller wrote:
> > I've been playing with your script (on my production db moved to my dev
> > machine) since I have been very remiss in maintaining my database. I ran
the
> > 'defrag' option on the db (all indexes were about 50% fragmented), then
I
> > did a SHRINKDB, and saw the size of my .mdf (and backup files) go from
1.3GB
> > to around 200MB!
> >
> > Then I read about DBREINDEX and saw that your script handled that too.
So, I
> > ran the rebuild script (on a pristine copy of my prod db without doing
the
> > defrag first), shrunk the database again, and again got the same
reduction
> > in file size. However, when I ran your script in the report mode AFTER
the
> > rebuild and shrink, almost ALL of the files are reported as 75-100%
> > fragmented!!! Also, there are indexes showing up in the report as being
100%
> > fragmented that I can't even find. They are all named like
> > NameOfTable.tNameOfTable with a 't' prefix rather than 'IX_' there.
> >
> > Is there a reason that after a DBREINDEX all the indexes are reported as
> > almost 100% fragmented?
> >
> > What are those indexes with a 't' in their name?
> >
> > Thanks for any explanation.
> >
> Sorry for the delayed response, I took a long weekend... Seems like
> Andrew has explained everything pretty well, but I'll toss in a couple
> of comments. The massive change in your database size is due to the
> fact that your indexes, after being defragged, are now tightly compacted
> into clean, contiguous units, instead of being "full of holes", so to
> speak. Unfortunately, when you shrank the database, that was all
> undone. Shrinking the database will reorganize data pages to make the
> files as small as possible. This often results in pages being seperated
> from their peers, and being scattered throughout the database.
> My suggestion would be to add another 100MB to your database, and then
> run the defrag script again. You should see a much different result.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com