Defrag SQL Server database

Posted on 2009-02-20
Last Modified: 2012-05-06
I have an sql server 2005 database which is reindexed every night. Queries are running slowly. Do i need to defrag the database in order to improve performance. If so, why?
Question by:fred2k3
    LVL 60

    Expert Comment

    No, there are likely other reasons the queries are running slow...they may be written poorly.  You can run profiler and look for the queries w/ the highest number of reads for a good starting point for the queries that need tuning.
    LVL 11

    Assisted Solution

    How many recordsets are you querying? Maybe the indexes are not optimal. Also you maybe could tweak the long running queries.

    According to Microsoft only large indexes with over 1000 pages can benefit of a defragmentation of the index. I you are asking about defragmentation of your physical hard drives it could help if there is heavy fragmentation but a defragmentation can not do wonders.

    Better check if you can tweak your queries and indexes.

    Remember to not use single column index but composite (multi-column) index on all columns in WHERE, JOIN, GROUP BY, ORDER BY, in this order.
    LVL 57

    Accepted Solution

    If queries are not running properly,

    1. Include appropriate indexes.
    2. Use Profiler to catch out the query execution time.
    3. Use showplan or explain plan to see why those queries take much time and if it not using your indexes created prior then go for either Reindexing or Defragmenting.

    You can have Defragmentation periodically as part of your maintenance plan too.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now