SQL Server Performance

Data fragmentation?

Discussion in 'Performance Tuning for DBAs' started by georgej, Apr 5, 2007.

  1. georgej New Member

    I use DBCC SHRINKFILE with EMPTYFILE option on SQL 2000 server
    to empty one file and move its data to other 5 files in the database. So I can delete this empty file. Can this action cause data fragmentation on disks?

    Thanks for advise,
  2. bradmcgehee New Member

    Yes, this will contribute to overall physical disk file fragmentation. I generally recommend that you use some sort of defrag software periodically. The OS defragger requires you to turn off SQL Server to defrag the files. A product like Diskkeeper can defrag while SQL Server is online. If your data is stored on a SAN, then file defragmentation is generally not as big an issue.

    --------------------------------
    Brad M. McGehee, SQL Server MVP
    http://www.sqlbrad.com
  3. Luis Martin Moderator

    Yes. What kind of disk do you have?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  4. georgej New Member

    Thank for all the replies!
    Yes. I use SAN storage for my SQL server 2000.




    quote:Originally posted by LuisMartin

    Yes. What kind of disk do you have?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.




  5. Luis Martin Moderator

    So, is not a big problem.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  6. georgej New Member

    Why data on SAN, the file defragmentation is not big issue?

    Thanks,



    quote:Originally posted by LuisMartin

    So, is not a big problem.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.




  7. Luis Martin Moderator

    Because the majority of SANs take care of this for you.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  8. MohammedU New Member

    quote:Originally posted by georgej

    Why data on SAN, the file defragmentation is not big issue?

    Thanks,
    Most of the SAN systems has backgroud defrag process running for optimum performance.


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  9. satya Moderator

    One suggestion is if you are performing this on a regular basis, then better to stop it and assign relevant sizes to the data & log files in order to maintain consistent growth on the database. On the other side the negative performace will be added when database optimization task complets successfully and data file is shrinked regularly.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  10. georgej New Member

    Thanks to all of your suggestions!
    The recommendations help me a lot.

    George


    quote:Originally posted by satya

    One suggestion is if you are performing this on a regular basis, then better to stop it and assign relevant sizes to the data & log files in order to maintain consistent growth on the database. On the other side the negative performace will be added when database optimization task complets successfully and data file is shrinked regularly.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  11. satya Moderator

    I will appreciate your feedback if you have implemented them and getting 'real' results [<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.</i></font id="size1"></font id="teal"></center>

Share This Page