Can SQL server performance improved by defragmenting ?

Asked By sushmita 40 points N/A Posted on - 08/12/2011

The drives on my SQL Server are fragmented & I assume because of this, I have a slow performance on my SQL Server.

I want to defragment my drives. Can anybody let me know, whether defragment will help to boost the SQL Server's performance ?

• Status: Open
• Question Views: 749
• Answer Count: 1
• Vote Up 1 Vote Down

• Answer Accepted: No
• Question Category: File Servers

Answered By Sue Lee 0 points N/A #114065

Can SQL server performance improved by defragmenting ?

Hello Sushmita,

It sounds like you are referring to the OS level defragmentation. Yes, it is of great help. The files used by SQL server when defragmented will become more contiguous on disk. But you might also consider SQL server level of defragmentation. To check if you have fragmented table data, use this Transact -SQL command: sys.dm_db_index_physical_stats if you are using SQL Server 2008. (Older SQL server version was DBCC SHOWCONTIG command). This command will report to you size and fragmentation information of your database data and indexes.

If you think you need to defrag the index use ALTER INDEX command. (Older SQL version was DBCC INDEXDEFRAG). This command will help improve index scanning performance. Doing both OS level defragmentation and SQL Server level defragmentation will surely boost your SQL server's performance.

About Sue Lee

Questions
0

Answers
6

Best Answers
2

• Vote Up 0 Vote Down

• Posted on - 08/13/2011
• Question Category: File Servers