Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 503
  • Last Modified:

Defrag SQL Server database

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?
2 Solutions
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.
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.
Raja Jegan RSQL Server DBA & ArchitectCommented:
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.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now