Maintenance, indexes, fragmentation & file groups

I am trying to get my head around many of the issues related to
maintaining some of our larger databases.
Our largest database is roughly 10 GB. Our largest table in the
database has 15,783,725 rows with a size of 9.8 GB and an index size of
2.8 GB according to the taskpad view. It has one clustered index and
13 indexes. The clustered index has a prefix of PK_*** Four of the
indexes have a prefix of IX_*** and the remaining nine indexes have a
prefix of _WA_Sys_***.
I have been reading a lot about reindexing, checkdb, shrinkdb, etc.
First question, my current rough idea for our new maintenance plan
looks like this:
Daily:
1) checkdb
2) backup db
3) backup tlog (tlog backups will run throughout the day as well)
Weekly:
1) checkdb
2) backupdb
3) shrinkfile db
4) backup tlog
5) shrinkfile tlog
6) file system defrag
7) reindex (not an indexdefrag)
I keep reading that shrinking the database files is wasted I/O but, we
are renting SAN space, so we need to be as efficient as possible with
disk space. We plan to shrink the db before the tlog backup so that we
can reclaim the tlog space created during shrinking the db.
I have read that putting your indexes into separate filegroups can
reduce fragmentation and thus improve performance. Given the size of
our indexes, this seems to make sense to me. Should I move all
indexes, clustered and nonclustered to the separate filegroup?
Also, someone previous to me had created a second db file for this
database, but they are both in the same file group. Should I leave
that as is, or do I need to do something about the second file?
Is there anything I am missing? I think I have caught all the main
points for performing regular maintenance. I have already collected,
created and modified a set of stored procs to do most of these tasks,
so it will just be a matter of scripting a few jobs.
Thank you,
KevinSee in-Line:
--
Andrew J. Kelly SQL MVP
"kghammond" <kghammond@.nrscorp.com> wrote in message
news:1140623640.392540.197050@.g43g2000cwa.googlegroups.com...
>I am trying to get my head around many of the issues related to
> maintaining some of our larger databases.
> Our largest database is roughly 10 GB. Our largest table in the
> database has 15,783,725 rows with a size of 9.8 GB and an index size of
> 2.8 GB according to the taskpad view. It has one clustered index and
> 13 indexes. The clustered index has a prefix of PK_*** Four of the
> indexes have a prefix of IX_*** and the remaining nine indexes have a
> prefix of _WA_Sys_***.
_WA... means that these are statistics and not actual indexes. You can
ignore them for all practical purposes.
> I have been reading a lot about reindexing, checkdb, shrinkdb, etc.
> First question, my current rough idea for our new maintenance plan
> looks like this:
> Daily:
> 1) checkdb
> 2) backup db
> 3) backup tlog (tlog backups will run throughout the day as well)
> Weekly:
> 1) checkdb
> 2) backupdb
> 3) shrinkfile db
> 4) backup tlog
> 5) shrinkfile tlog
> 6) file system defrag
> 7) reindex (not an indexdefrag)
No need for shrink as I will explain below. You should not have to defrag
the file system if you don't continuously grow and shrink.
> I keep reading that shrinking the database files is wasted I/O but, we
> are renting SAN space, so we need to be as efficient as possible with
> disk space. We plan to shrink the db before the tlog backup so that we
> can reclaim the tlog space created during shrinking the db.
If the file grows again after it is shrunk that is a PURE indication that it
needs the space at one time or another. This is especially true when you
reindex. You must have free space in the files in order for the db to
operate properly. Shrinking just prolongs the inevitable and will hurt
overall performance. Find the size in which it no longer grows and leave it
there because that is what it requires. There is no getting around this so
accept it and move on.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> I have read that putting your indexes into separate filegroups can
> reduce fragmentation and thus improve performance. Given the size of
> our indexes, this seems to make sense to me. Should I move all
> indexes, clustered and nonclustered to the separate filegroup?
It's probably not worth the trouble. Make sure you have a proper fill
factor to reduce fragmentation and page splits.
> Also, someone previous to me had created a second db file for this
> database, but they are both in the same file group. Should I leave
> that as is, or do I need to do something about the second file?
Having more than one file is often a good thing just make sure they are both
the same size when you create them.
> Is there anything I am missing? I think I have caught all the main
> points for performing regular maintenance. I have already collected,
> created and modified a set of stored procs to do most of these tasks,
> so it will just be a matter of scripting a few jobs.
There is usually no need to rebuild indexes every night. Look in
BooksOnLine under DBCC SHOWCONTIG for a script to reindex only the indexes
that actually are fragmented beyond a certain amount.
> Thank you,
> Kevin
>|||OK, I have been sold on the no shrinking. We were originally concerned
because both our db and tlog have been as much as 50% larger than what
is currently in our db and tlog. For example, our two files are broken
down like this:
Size In Use Free
--
2.7GB 1.5GB 1.2GB
10.4GB 5.5GB 4.9GB
If I am understanding reindexing properly and we have a 2.9 GB index on
one table, then it will use much of that free space during reindexing.
Our updated maintenance plan looks like this:
Daily:
1) checkdb
2) backup db
3) backup tlog (tlog backups will run throughout the day as well)
Weekly:
1) checkdb
2) backup db
3) backup tlog
4) file system defrag
5) reindex (not an indexdefrag, using a stored proc >20% or so)
One note about our database usage. Our database has relatively few
writes and new rows being added on a daily basis. This database is
primarily used for many SELECT's and our primary table is usually being
joined to other tables in these SELECT's.
I don't know if it is possible to physically order out data so that
common rows are contiguous within the files. Our data is generally
time dependant and tied to different organizations. So a SELECT
statement will generally read all rows that are within the last four
months and belong to organization x.
So I think the goal of fragmentation in our case is to keep the index
contiguous to make reads of the index faster and more efficient. I
think I am understanding this correctly.
As noted above, our two files are quite a bit different in size. Do I
need to do something to correct this?
I have to read up more on fill factors. I don't understand them well
enough yet to understand their impact on fragmentation. Any tips while
I research this more thouroughly?
Thank you,
Kevin|||If you use DBCC DBREINDEX and just specify the table name it will rebuild
ALL indexes on that table in one big transaction. You will need at least 1.2
times the total size of all the indexes including the clustered index. I
assume you have a clustered index and if not you should. The differences in
the size of the file is due to the fact the second file was not made the
same as the first. SQL Server splits the data evenly across multiple files
in the same file group if they are of the same size and have the same amount
of free space. In reality it is the amount of free space in each file that
determines how much data is placed in each file. If you have twice as much
free space in one file then the other it will put twice as much data in the
one that has more free space and so on. The goal is to create all the files
the same size to begin with and then data gets evenly distributed across
them from the beginning. In your case if you make them both say 7GB and run
DBCC DBREINDEX a few times they should even out. Then you can see how much
free space is left and shrink slightly if needed. Back to the clustered
index. In general every table should have one. They are the only things
that help to control fragmentation in the table itself. But choosing the
right column(s) for the clustered index is not always so straight forward.
They are great for times when you always search in ranges or on values that
have a lot of repeating rows. In your case the datetime column or the
organization may be good choices. These should help.
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
"kghammond" <kghammond@.nrscorp.com> wrote in message
news:1140637582.856303.277700@.g14g2000cwa.googlegroups.com...
> OK, I have been sold on the no shrinking. We were originally concerned
> because both our db and tlog have been as much as 50% larger than what
> is currently in our db and tlog. For example, our two files are broken
> down like this:
> Size In Use Free
> --
> 2.7GB 1.5GB 1.2GB
> 10.4GB 5.5GB 4.9GB
> If I am understanding reindexing properly and we have a 2.9 GB index on
> one table, then it will use much of that free space during reindexing.
> Our updated maintenance plan looks like this:
> Daily:
> 1) checkdb
> 2) backup db
> 3) backup tlog (tlog backups will run throughout the day as well)
> Weekly:
> 1) checkdb
> 2) backup db
> 3) backup tlog
> 4) file system defrag
> 5) reindex (not an indexdefrag, using a stored proc >20% or so)
> One note about our database usage. Our database has relatively few
> writes and new rows being added on a daily basis. This database is
> primarily used for many SELECT's and our primary table is usually being
> joined to other tables in these SELECT's.
> I don't know if it is possible to physically order out data so that
> common rows are contiguous within the files. Our data is generally
> time dependant and tied to different organizations. So a SELECT
> statement will generally read all rows that are within the last four
> months and belong to organization x.
> So I think the goal of fragmentation in our case is to keep the index
> contiguous to make reads of the index faster and more efficient. I
> think I am understanding this correctly.
> As noted above, our two files are quite a bit different in size. Do I
> need to do something to correct this?
> I have to read up more on fill factors. I don't understand them well
> enough yet to understand their impact on fragmentation. Any tips while
> I research this more thouroughly?
> Thank you,
> Kevin
>|||kghammond a écrit :
[...]
> 6) file system defrag
Never do that !
In fact impossible on data and log files, while the database is hot.
But the OS defrag does not did a optimized defrag, just a logical one.
If you created your db with large fixed files, the SQL Server OS try to
find the tracks on the differents plates of the disk wich must reduce
the moving of the head on the disk surface. So the cluster wont be
logically continuous.
Il you do a OS defrag you will broke this optimization.
A +
A +
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||I don't agree with that at all. First off you can in fact do on-line defrag
at the OS level with tools like DiskKeeper. While I never recommend that
when it can be avoided it is in fact possible. As far as where sql server
looks for the data on disk that too is not true. SQL Server has absolutely
no control over the actual fetching of the data in terms of moving the heads
etc. It simply makes a call to the OS and requests a certain piece of data
but it is the responsibility of the I/O driver and the storage foundation to
do the work as it sees fit. SQL Server knows of coarse that a particular
page lives in a particular file in a particular slot. But that is as far as
it goes.
--
Andrew J. Kelly SQL MVP
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:uf%23mgMFOGHA.1360@.TK2MSFTNGP10.phx.gbl...
> kghammond a écrit :
> [...]
>> 6) file system defrag
> Never do that !
> In fact impossible on data and log files, while the database is hot.
> But the OS defrag does not did a optimized defrag, just a logical one.
> If you created your db with large fixed files, the SQL Server OS try to
> find the tracks on the differents plates of the disk wich must reduce the
> moving of the head on the disk surface. So the cluster wont be logically
> continuous.
> Il you do a OS defrag you will broke this optimization.
> A +
> A +
>
> --
> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************