Handle Disk Fragmentation issues with effective methods within SQL Server enviornment

When you talk about Performance problems then fragmentation & database integrity issues are dearest friends of DBA!

As data is modified in the databases, the tables and indexes will become fragmented, which means the data is scattered everywhere in the database. So talking in DBA sense the moral is as more fragmented a clustered or non-clustered index becomes, it is nothing but database engine requires to add more pages to fulfil the same query request. So it is a required practice to defragment these indexes on a regular basis using your own methods or industry best practices. So to check the allocation of all database pages and internal structures based on the regular activity on the database, delete/insert/modify processes and instant statement pops up is DBCC CHECKALLOC. When it is executed an internal database snapshots are created to maintain the transactional consistency during the operation of checkout. To get consistent results I have seen that some of  the adhoc scripts specifies TABLOCK then creation of database snapshot may not occur causing exclusive database lock during the execution of this command which will potentially affect the database concurrency.

Ok, enough talk on index fragmentation and now I'm more concerned on disk fragmentation as a DBA you are more responsiblefor server hardware as well. You must understand the value that professional grade disk defragmentation software brings to their servers. Storage servers can experience high levels of disk thrashing (the constant writing and rewriting of small amounts of data) caused from excessive file fragmentation for database MDF & LDF files. When we refer the operating system based fragmentation then you will see network and application issues have a much more visible impact on the performance of network-based services, especially when problems with those functions are encountered.

Optimal disk performance translates into better ROI, but how can you assure that present hardware can bring it all?

Testing the current hardware disks or involve the hardware vendor to get certfied on the testing pattern it is followed. As you can easily obtain results for impact of server disk defragmentation by looking at common tasks that network servers, both physical and virtual, encounter, ranging from maintenance tasks such as server backup and anti-virus scans.Many out there think that involving the tests of a basic user tasksscha s opening files stored on the host server and virtual machines, and manipulating email are not included, but they must be included too. They will help to add on more fuel to the rigorous database testing such as database queries, index creation, and bulk updates.

So doing such a tests requirement I always depend upon the fantastic tool from Diskeeper which can produce fantastic reports about the file fragmentation on to the root level on the servers. Using this tool you need to perform a benchmark tests involving the hardware and I have performed such benchmarking tests using HP's DL380 G5 equipped with dual quad-core 2.83 GHz Xeon processors, each with a 2x8MB L2 cache, 32 GB of RAM and eleven 72 GB 10,000 RPM SCSI drives attached to an HP Smart Array P400 controller that has a 256 MB cache and that supports both serial attached SCSI and SATA drives. The disk volums varies from 30GB to 170 GB due to the nature of the application data I have to go on such range that keeps the database sizes to a minimum of  500 GB within 7200 RPM locally attached SATA drives and 4 x 500GB for backup purpose only. So this Diskeeper tool helped to achieve such a results by performing 3 levels of fragmentations low, medium and high with  Diskcrusher fragmentation utility to createfragmented files and directories. So the results that are produced are phenominal on the level of fragmentation you’ll encounter in usual production environments and pretty much dependent upon the level of use and types of applications the server deals with.

So to refer about complete testing on SQL Server side, we used 2008 version  with a bulk insert of 500,000 rows of data. The bulk insert is often the fastest
method of getting data into a SQL Server database. Obviously captured the resource usage of the server by using PERFMON (SYSMON) for CPU, Memory and disk related counters. Also the testing of table key creation times are directly related to how much data SQL Server had to touch, and the level of fragmentation that had to be dealt with. SQL Server 2008 does a very good job of managing its databases, but defragmentation shows appreciable
improvement in the performance of tasks such as this with a performance improvement of over 11 percent in the most fragmented environments. Coming to the query execution that the test involved differ primarily due to the amount of data returned by SQL, in response to the query. The tests depict the effects of
manipulating the data on a fragmented drive with peak performance improvements of approximately 21%.

SQL Servers benefit from defragmentation; reading and writing data with either application simply works better when the files are not fragmented. Which means whenever a DBA follows the regular practice of defragmenting the indexes then underlying file defrag methods are easy to manage and you will instantly see the result as is improved performance during the busy times at the server. I see that many such solutions are simply de-tour'd by suggesting to throw more storage resources (hardware) at a problem and it should be the last resort, because it only masks the potential problems that intelligent disk
defragmentation addresses. The reason I mention on Diskeeper is which it reported that server storage levels are consistently exceeding 75 percent or so, due to the affect of transactional and usage of users about the  data off of the servers causing the fragmentation which isn’t a direct result of reduced capacity, the chances for fragmentation increase as free storage space decreases and the operating system is forced to write data into an everincreasing
number of non-contiguous spaces.  The tool comes with the option of manual or auto defragmentation method, by using the automated the same disk volume sees absolutely minimal fragmentation even though it is in continual use by applications and users.

To wrap up the discussion it is better to defragment the operating system files too on regular basis or whenever there is a low server activity on the SQL Server, specifically.