SQL Server stores the data and indexes on data files in a physical disk, this is called external fragmentation. As all files on the windows system, fragmentation degrades I/O performance. SQL Server has also a internal fragmentation that occurs when records are removed from the database pages, but the space is not freed. If data files have external fragmentation, index rebuilding will take longer since there is a I/O bottleneck.
Defragmentation is the process to fix fragmentation. For each type of fragmentation, there is a solution: 1) Internal fragmentation: Use the following script from SQL Server 2005 BOL to defragment or DBCC DBREINDEX .
2) External fragmentation: Use a defragmentation tool like diskeeper to defragment the file system.
Developing a maintenance plan for SQL Server is a best practice.
Paging the SQL Server data to virtual memory on disk reduces overall performance. The Enable the Lock Pages in Memory Option is a windows policy that prevents Windows from paging the SQL Server data to virtual memory on disk. The option is disabled by default. This privilege must be enabled to configure Address Windowing Extensions (AWE), that must be activated on sql server.
To enable the lock pages in memory option: 1) Execute gpedit.msc on Start->Run 2) Expand Computer Configuration->Windows Settings 3) Expand Security Settings->Local Policies 4) Select the User Rights Assignment folder 5) Double-click Lock pages in memory and add the account that runs sqlservr.exe