SQL SERVER – De-fragmentation of Database at Operating System to Improve Performance

This issues was brought to me by our Sr. Network Engineer. While running operating system level de-fragmentation using either windows de-fragmentation or third party tool it always skip all the MDF file and never de-fragment them. He was wondering why this happens all the time.

The reason MDF file are skipped all the time in de-fragmentation because they are in use when SQL Server is running. Windows operating system de-fragmentation skips all the file in are currently in use.

After discovering this the real question was how to de-fragment when files are in use. Steps are Stop the Server, Re-start, keep the SQL Server services off and do de-fragmentation.

What if Server can not be taken off-line? In that case, nothing can be done. In my company SQL Server runs on redundant configuration – they are mirrored real time as well fail-over clustering is configured. We took one server down and system ran on safely on fail over server while we ran de-fragmentation on other server. We repeated the same order for all server. We see significant difference in our operating system performance as well as database response time.

One more thing to note, operating system needs more than 15% empty space to do optimal de-fragmentation.

Just for fun I will type my conversation with our Sr. Network Engineer. He is very smart person.

Sr. Network Engineer : Hey Pinal, MDF files does not de-fragment every night.
Pinal : May be because they are in use. Needs to alternate and de-fragment on cluster, what do you think?
Sr. Network Engineer : Yeah! Thanks. It will be good test for fail-over as well. Will do tonight.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

5 thoughts on “SQL SERVER – De-fragmentation of Database at Operating System to Improve Performance

  1. Pingback: SQL SERVER – Cleaning Up SQL Server Indexes – Defragmentation, Fillfactor – Video « SQL Server Journey with SQL Authority

  2. I am not sure I understood this well. At our fail over cluster mdf files are at clustered resources that is disk on storage and when I fail over to other node all resources fail over too so only c drive is left for defrag and I can do that anyway

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #034 | Journey to SQL Authority with Pinal Dave

  4. Hi Pinal,

    I too don’t know if I understood this correctly(as irena mentioned). In Clustering, when we failover we have the same MDF files moving to other node leaving them to be still in use. How fragmentation can be done in such scenario unless I use mirroring or logshipping where they have different MDF and LDF files.

    Thanks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s