SQL Server Database Performance
All SQL Server databases, over time, experience "internal" file fragmentation.
This occurs when records are removed from database pages, but the space it
occupied is still there after deletion. Eventually this space is reused, but as
it is reused, the data pages become fragmented, which can lead to unnecessary I/O,
especially in case of table scans where many data pages are read, one after another.
Even if the original
database and log files are not fragmented when they are first created, they will
almost certainly become fragmented as the database grows over time. For example,
if you set the original database size to 100MB and the log to 10MB, and you have
them set to grow automatically, and if eventually the database grows to 5GB in size
and the log grows to 100MB in size, external fragmentation could become great. Every
time the database or log files grow automatically, there is the potential for external
Read more on SQL Server performance »
How do I get the most performance from my SQL Server?
One of the biggest hardware bottlenecks for any SQL Server is disk I/O. Anything
a database administrator can do to reduce the amount of I/O necessary to complete
a task will increase that server’s performance.
Tuning queries to minimize the amount of data returned.
Some of the most common things DBAs often resort to are:
Using fast disks and arrays.
Using lots of RAM so more data is cached.
Frequent DBCC REINDEXing of data to remove logical database deleted or inactive
But none of these actions will prevent hardware bottlenecks that occur due to fragmentation.
In order to defrag external fragmentation and get the most performance from your
SQL Server, you need an operating system utility, not a SQL Server utility.
There are two types of external fragmentation a utility like Diskeeper handles:
- File Fragmentation
- Free Space Fragmentation
File fragmentation concerns computer disk files that are not whole, but rather are
broken into scattered parts; while free space fragmentation means that the empty
space on a disk is broken into scattered parts rather than being collected all in
one big empty space. File fragmentation causes problems with accessing data stored
in computer disk files, while free space fragmentation may cause problems creating
new data files or extending (adding to) old ones.
Fragmentation never stops. Without the real-time support of
Diskeeper® data performance software, fragmentation accumulates and heavily
relied upon servers become slower and eventually must be taken off line for maintenance.
NTFS does try to minimize file fragmentation but can’t begin to keep up.
When Diskeeper runs, it acts to defrag database and log files so that instead of
being made up of many pieces, the file is one continuous segment. In addition, Diskeeper
defrags free space so that when database or log files expand, that they can expand
with little or no fragmentation.
V-locity® virtual platform optimizer
does the same with VMs.
All file movement in a Diskeeper defragmentation job is handled by the operating
system itself. In fact, the code in the operating system prioritizes safety in determining
what can be defragmented and what cannot. SQL Servers databases (e.g. .LDF, .MDF)
are perfectly safe to defragment. As Diskeeper sends requests to the operating system
(through an API) to move files, if it comes across files that cannot be safely moved,
they are simply skipped over without any error or concern.
"We offer a global Shared SQL Server hosting service within an oil major. The service
hosts up to 150+ databases per host in some cases so very heavily shared. We experienced
a production outage where SQL 2005 stalled as it could not write in a timely manner
to the database file. It came as a great surprise that this was found to be due
to the database files being so heavily fragmented.
— British Petroleum
"A manual Windows defrag of the 2TB volume achieved only 12% in 6 hours and was
an unacceptably long outage of service. We chose Diskeeper to address this problem
and have rolled it out on all production environments and it is fantastic, defragmenting
pretty much 4TB of data within two weeks at most."
Download a free trial of Diskeeper Server or V-locity to test on your SQL Server today »