Defragmentation on SQL Server 2008

[dbaus]

     I have run across multiple questions and comments in regards to this while searching on internet.

Currently we are provisioning physical servers to boot off of our Netapp SAN.  We are connected via FCP HBA cards and are setting up clustered servers.  We have them attached to the SAN to thick provisioned LUNS.  These will be production SQL server boxes and I finally was able to talk some of my team members to utilize Snapdrive to create the new drive space and attach it per netapp best practices.  Currently we do not have enough space on our SAN to enable snapshots of our sequel servers.  I have recommended that we install SMSQL (which we are licensed for already) but since we will not be using snapshots to begin with I am running into some difficult choices that are being made saying that we will install it later.  Also our DBA is very concerned in regards to File Defragmentation from the windows side of things. So this leads to my first two questions:

1. Is installing SMSQL at a later point in time an available option or will we need to install it now so it knows how the files are laid out originally?

2. Obviously from a Windows perspective a 3rd party windows defragmentation tool (Diskeeper) will be useful, but from a SAN standpoint will it cause too much I\O and be detrimental?

Thanks for any advice, as i am the one responsible for monitoring the SAN, but other individuals have access and make the configuration changes\ initial setup on the system.

[radek_kubka]

Hi & welcome to the forums!

1. Is installing SMSQL at a later point in time an available option or will we need to install it now so it knows how the files are laid out originally?

Installing SMSQL later will be perfectly fine - no issues with that.

2. Obviously from a Windows perspective a 3rd party windows defragmentation tool (Diskeeper) will be useful, but from a SAN standpoint will it cause too much I\O and be detrimental?

I recommend you don't use any host-side defragmentation tools - or use them very sporadically, being conscious of likely outcome. Host-side defrag in most cases will balloon your existing snapshots, as from filer point of view all moved blocks have been changed, so it's not a very desired result.

Running reallocate command on the filer side may be advised though. There are some thoughts around this:

http://communities.netapp.com/message/20969#20969

http://communities.netapp.com/message/12493#12493

Regards,
Radek

[parkerj]

Just a couple of things about database fragmentation. Fragmentation with in the database itself comes from page splits due to inserts, updates and deletes. SQL Server at the time of file allocation will go ahead and extend a contiguous file for the entire size specified.  Then the fragmentation that occurs inside the database happens when you have changes with in the database.  This fragmentation can not be fixed by running a defrag utility because the file system sees the database file as on lage  continuous unit.  The way to remove database fragmentation is to rebuild the indexes and table structures.  The best way is to rebuild a clustered index on a table.  SQL Server 2005 and above enterprise allows the operations to take place online.  Doing online operations within the database requires you to ensure that you have the free space within the database and tempdb to accomplish the tasks. So the issue that you will have is that after rebuilding tables and indexes within the database you now will have very large snapshots since you just changed every page in the table(s).  This is not a show stopper but something that needs to be managed.

Best of luck

John

[radek_kubka]

Hi,

The number of page splits can be lowered down by decreasing FILLFACTOR for indexes.

Having said that, database fragmentation & reorgs are inevitable in a long run.

There is a very interesting blog post over here:

http://blogs.technet.com/josebda/archive/2009/03/20/sql-server-2008-fragmentation.aspx

Regards,

Radek