Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Diskeeper & SQL, good or bad? 1
- Thread starter mjhessler
- Start date
Diskkeeper may or may not affect SQL Server; I don't know. What I do know it that it will not fix the defragmetation problems of a database. You need to reindex or index defrag within the database to fix that.
Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief
Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief
mrdenny
Programmer
- May 27, 2002
- 11,595
If diskkeeper is run while SQL is running, then it should have no effect on the SQL mdf and ldf files, as SQL locks the files open while it's running. Thus preventing any software from touching the files. If you run diskkeeper while the SQL Service is stopped then it will line the database files back up with them selves.
However this shouldn't be much of a problem is the drives for SQL Server are dedicated to SQL Server. If there isn't ever anything else of the drives the files should be contiguous.
I'm not sure how much use disk-keeper will be when run against a SAN drive. The physical drives may be in use on more than one virtual disk, so what disk keeper thinks is contigous space on the drive may not be. The SAN vendor should know of the desk defrag until for use on the san drives. Or the SAN may have something built in to take care of this.
Denny
--Anything is possible. All it takes is a little research. (Me)
![[noevil]](/data/assets/smilies/noevil.gif "[noevil] [noevil]")
(My very old site)
However this shouldn't be much of a problem is the drives for SQL Server are dedicated to SQL Server. If there isn't ever anything else of the drives the files should be contiguous.
I'm not sure how much use disk-keeper will be when run against a SAN drive. The physical drives may be in use on more than one virtual disk, so what disk keeper thinks is contigous space on the drive may not be. The SAN vendor should know of the desk defrag until for use on the san drives. Or the SAN may have something built in to take care of this.
Denny
--Anything is possible. All it takes is a little research. (Me)
(My very old site)
-
1
- #4
Hello Everyone,
As the Senior Technical Engineer for Executive Software I know a great deal about Diskeeper and the NTFS file systems. I admit that I’m not an SQL dba, but have a fair amount of practical experience in both production and test labs (in house and at customer sites) with regards to Diskeeper running on SQL, NAS, SAN, RAID, MS Cluster Servers, etc…
“SQLSister” is absolutely correct that a file system defragmenter such as Diskeeper does not alter or change the internal structure of the datastore files. That operation is better left to the manufactures of the database.
In test labs as well as in the real world, I have not found the *.mdf and *.ldf files locked for exclusive access EXCEPT in Windows NT 4 environments. With that being said, these files can be moved or defragmented by Diskeeper on Windows 2000 and above operating systems WITHOUT the need to stop or shutdown SQL.
As “mrdenny” states, if these files are the only files on the partition, they are not likely to be too badly fragmented, unless there are multiple databases and they are allowed to expand automatically as needed. If that is true, you may find that you have a great deal of file/free space fragmentation at the file system level. This will also be compounded as you re-index from time to time.
Furthermore, most hardware manufactures will not admit to their hardware being affected by fragmentation. After all they can quote lab tests which proves their hardware is superior to another vendors. The problem here is that such tests are done with physical disk read/writes. No file system is involved. Unfortunately, this isn’t a real world case as your partitions will be formatted as an NTFS volume and your SQL transactions are passed from the application to the file system and ultimately to the device driver and disk controller. Remember, Diskeeper is reading the $MFT (Master File Table) and the $BITMAP (Volume Bitmap File – Identifies which clusters are allocated and which are free) to determine whether a file is fragmented or moving it would help consolidate the free space. It does not read the individual physical disks, nor does it know the number of physical units that are part of the set.
Large data caches can help to mitigate some of the performance problems associated with file fragmentation but you cannot cache ALL data and at some point the cache is read/refreshed from disk anyway. NAS, SAN, and RAID arrays can also reduce the effects of fragmentation but if the file is fragmented into more pieces than the physical number of disks performance suffers as a disk I/O Queue develops. Take for example a file that is in 2 fragments. It takes twice as long to access on a single spindled disk, but in a multiple disk array the data will usually be scattered onto multiple disks and as a result multiple I/O requests can be satisfied at the same time. Now let’s say you have a file that is in 200 fragments. The file system will issue an I/O request for each of the fragments, but since you don’t have 200 physical disks, the first group of I/O requests are in progress, the rest must wait until the disk is no longer busy to process the remaining I/O’s. Granted, it is faster than this same operation being done on a single disk, but not as fast as it could be if the file were contiguous. The bottom-line is that if file fragmentation if not handled on a regular basis, you are not getting your full value for all that expensive hardware and performance/production suffers.
I hope this information helps and I would be happy to continue the thread or take it off-line for further private discussion.
Best Regards,
Howard Butler
Senior Technical Engineer
Executive Software
“Diskeeper – The Number One Automatic Defragmenter”
As the Senior Technical Engineer for Executive Software I know a great deal about Diskeeper and the NTFS file systems. I admit that I’m not an SQL dba, but have a fair amount of practical experience in both production and test labs (in house and at customer sites) with regards to Diskeeper running on SQL, NAS, SAN, RAID, MS Cluster Servers, etc…
“SQLSister” is absolutely correct that a file system defragmenter such as Diskeeper does not alter or change the internal structure of the datastore files. That operation is better left to the manufactures of the database.
In test labs as well as in the real world, I have not found the *.mdf and *.ldf files locked for exclusive access EXCEPT in Windows NT 4 environments. With that being said, these files can be moved or defragmented by Diskeeper on Windows 2000 and above operating systems WITHOUT the need to stop or shutdown SQL.
As “mrdenny” states, if these files are the only files on the partition, they are not likely to be too badly fragmented, unless there are multiple databases and they are allowed to expand automatically as needed. If that is true, you may find that you have a great deal of file/free space fragmentation at the file system level. This will also be compounded as you re-index from time to time.
Furthermore, most hardware manufactures will not admit to their hardware being affected by fragmentation. After all they can quote lab tests which proves their hardware is superior to another vendors. The problem here is that such tests are done with physical disk read/writes. No file system is involved. Unfortunately, this isn’t a real world case as your partitions will be formatted as an NTFS volume and your SQL transactions are passed from the application to the file system and ultimately to the device driver and disk controller. Remember, Diskeeper is reading the $MFT (Master File Table) and the $BITMAP (Volume Bitmap File – Identifies which clusters are allocated and which are free) to determine whether a file is fragmented or moving it would help consolidate the free space. It does not read the individual physical disks, nor does it know the number of physical units that are part of the set.
Large data caches can help to mitigate some of the performance problems associated with file fragmentation but you cannot cache ALL data and at some point the cache is read/refreshed from disk anyway. NAS, SAN, and RAID arrays can also reduce the effects of fragmentation but if the file is fragmented into more pieces than the physical number of disks performance suffers as a disk I/O Queue develops. Take for example a file that is in 2 fragments. It takes twice as long to access on a single spindled disk, but in a multiple disk array the data will usually be scattered onto multiple disks and as a result multiple I/O requests can be satisfied at the same time. Now let’s say you have a file that is in 200 fragments. The file system will issue an I/O request for each of the fragments, but since you don’t have 200 physical disks, the first group of I/O requests are in progress, the rest must wait until the disk is no longer busy to process the remaining I/O’s. Granted, it is faster than this same operation being done on a single disk, but not as fast as it could be if the file were contiguous. The bottom-line is that if file fragmentation if not handled on a regular basis, you are not getting your full value for all that expensive hardware and performance/production suffers.
I hope this information helps and I would be happy to continue the thread or take it off-line for further private discussion.
Best Regards,
Howard Butler
Senior Technical Engineer
Executive Software
“Diskeeper – The Number One Automatic Defragmenter”
mrdenny
Programmer
- May 27, 2002
- 11,595
Howard,
Thanks for posting to this thread. It's good to know that the mdf and ldf files aren't locked from disk keeper in 2000/2003 server. I'll have to remember that.
Denny
--Anything is possible. All it takes is a little research. (Me)
(My very old site)
Thanks for posting to this thread. It's good to know that the mdf and ldf files aren't locked from disk keeper in 2000/2003 server. I'll have to remember that.
Denny
--Anything is possible. All it takes is a little research. (Me)
(My very old site)
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
