I personally have used Raxco PerfectDisk (not associated with the company or any of their employees in any way) to do online defrags of SQL Server LUNs. Works perfectly, if it does slow the server down a bit. I would recommend doing it during periods of lighter activity. When I say "works perfectly" I am referring to it not corrupting the volume or the SQL data files.
The built-in defragmenter does a very poor job if certain structures are fragmented on the drive. PerfectDisk shows you details about all the items that are fragmented including the NTFS allocation tables, directories, alternate file streams, etc. etc.
Does PerfectDisk defragment SQL databases? http://support.raxco.com/KB/a106/does-perfectdisk-defragment-sql-databases.aspx
See this archive copy of the Technet News Magazine regarding SQL Server and fragmentation: http://web.archive.org/web/20100803204458/http://www.microsoft.com/technet/abouttn/flash/tips/tips_083104.mspx
From the error message I can quickly tell the following:
- The database is elite_prod
ffcount
,bldr
,frtfwd
, and histry
are all MyISAM tables
Because you are using the MyISAM Storage Engine, you have two major handicaps:
HANDICAP #1
Changes to MyISAM are cached differently (regardless of the OS)
- The index pages for MyISAM tables are cached in the MyISAM Key Buffer (sized by key_buffer_size)
- Index changes are flushed to the
.MYI
by the MyISAM Storage Engine
- Data are never cached as there is no engine-centric buffering for data
- Data changes are flushed to the
.MYD
by the OS (OUCH !!!!)
- I discussed this before
HANDICAP #2
Microsoft Windows is terrible at caching disk changes. Even running FLUSH TABLES;
in MySQL in a Windows environment is not a panacea. IMHO anyone using PostgreSQL or Oracle should be able to voice this same complaint about Windows. I will leave it to the SQL Server gurus to answer how they find Windows caching in terms of SQL Server.
ANALYSIS
MyISAM maintains a count of open file handles against the tables.
If the mysqld process or the Windows Server crashes, every MyISAM that had open file handles will retain the open file handle count internally.
When you access a MyISAM table for the first time since mysqld was started, it should have a zero file handle. Otherwise, you get that error message marked as crashed and should be repaired
. This explains the tables periodically coming up as crashed crashed.
See my post MyISAM table keeps crashing. What are my options?
SUGGESTIONS
You could switch those tables to InnoDB and let the InnoDB Buffer cache everything, or at least better caching. I would still worry about Windows in this respect because the option innodb_flush_method is disabled in the Windows version of MySQL. I say disabled because the MySQL Documentation says:
Controls the system calls used to flush data to the InnoDB data files and log files, which can influence I/O throughput. This variable is relevant only for Unix and Linux systems. On Windows systems, the flush method is always async_unbuffered and cannot be changed.
If you want leave the tables as MyISAM, please go back to Ubuntu because it is a bit more diligent when it comes to flushing disk changes, especially when it has enough RAM. Even a terabyte of RAM will not do a thing for MyISAM tables in Windows.
Best Answer
Provided you recon 100% that physical disk fragmentation is significantly affecting sql server performance and you can afford downtime, go for disk defrag.
I have never had to defrag a windows disk hosting sql server databases. You should choose a sensible autogrowth value for your databases and have Instant file initialization enabled.