WHITEPAPER

By Juan Rogers

With an Introduction to SQL defrag manager

SQL SERVER
FRAGMENTATION
EXPLAINED
TM

SUMMARY

approach to identifying and resolving index fragmentation in SQL Server

Warning: This white paper will get a bit technical as it is intended for DBAs who want to truly understand the details and key components of fragmentation in SQL Server.

» How to judge the improvements gained by defragmenting your server

»

» The pros and cons of various approaches to managing fragmentation

» The mechanics behind performance robbing data voids

» How fragmentation affects performance

» The difference between disk and SQL Server internal and external fragmentation

The following is a summary of the key topics covered in this paper:

monitoring and managing index fragmentation.

This technical white paper will help you understand SQL Server fragmentation

OVERVIEW

As the data in Microsoft SQL Server tables changes their indexes change. Over time these indexes become fragmented. This fragmentation will adversely affect performance. This technical white paper provides information to help you understand the detailed mechanics behind fragmentation. It will also help you understand the methods and approaches for performing defragmentation so you can improve your SQL Server’s performance.

SUMMARY

WEB
TWITTER
FACEBOOK
LINKEDIN

EMEA
APAC
MEXICO
BRAZIL

US

www.idera.com www.twitter.com/Idera_Software www.facebook.com/IderaSoftware www.linkedin.com/groups?gid=2662613 +1 713 523 4433
877 GO IDERA (464 3372)
+44 (0) 1753 218410
+61 1300 307 211
+52 (55) 8421 6770
+55 (11) 3230 7938

and Melbourne.

PAGE 1

and a more responsive system overall.

Microsoft as the defragmentation tool internal to Windows. In fact, Diskeeper’s latest innovations bring physical defragmentation capabilities to a completely new level.

Physical defragmentation products such as Windows defrag, Power Defrag™, Page
Defrag™ (another Microsoft tool), or the granddaddy of them all, Diskeeper 2011™

segments stored apart from one another. A hard drive’s head relocates to read each individual segment. As it moves to each segment the head ‘seeks’ - often at a cost of 3-4 times the time it takes to read the segment itself. Physical fragmentation primarily affects desktop or laptop PCs containing one hard drive. The single drive must sequentially gather data – so on a fragmented disk it seeks, reads, seeks, reads
- these 4 operations are performed one after another. Defragmented, the operation ends up as seek, read, read. We reduce the total cost of 24ms to 15ms in our simple example. Physical fragmentation slows down your PC because reading data is interrupted

discussed. Physical fragmentation is a side effect of how hard drives and Windows work. It is common knowledge that regular disk defragmentation is required to achieve optimal performance from your PC. Windows even includes a basic defragmentation utility.

A: SQL FRAGMENTATION IS NOT PHYSICAL DISK FRAGMENTATION.
NOT ALL FRAGMENTATION IS EQUAL!

Q: WHAT IS SQL SERVER FRAGMENTATION? IS IT DIFFERENT THAN
PHYSICAL DISK FRAGMENTATION?

becomes quickly evident as your system slows down over time.

two segments. The seek costs us 18ms, while the read costs 6ms. Considering the

PAGE 2

data - how SQL Server has laid out the database itself, how full each page is, and how effectively we’re utilizing available SQL Server resources, we can optimize to

The most important concept to understand is that the controller, physical defragmentation programs, and multi-drive arrays are unaware of what SQL Server

the virtual unison of tandem drives. It’s by design. The goal is to gain the most performance while incurring the least overhead – so don’t run physical defrags if they slow the storage by 50% while running, and ultimately improve read speeds
1-2%.

There are many DBAs who run a traditional physical defragmentation program in tandem with their intelligent drive controller which results in limited improvement.

reading for 3ms with no seek delay impact at all. Data storage drives are generally much faster than workstation drives, so seek times of 4ms and read times of 1.5ms are not unusual.

Working in tandem, however, allows one drive to seek while the others read. With

The fault-tolerance in database storage overcomes the vast majority of physical disk fragmentations’ impact. Best practices universally prescribe multi-drive storage subsystems for production SQL Servers. Most SQL Servers use multi-drive storage such as RAID arrays, SANs, and NAS devices; there are always multiple drives acting in tandem. Hard disk controllers supporting drive arrays are aware of the alternate seek/read dynamic and tailor communications with the array for maximum I/O.

fragmentation is something solved with hardware – not with defragmentation scripts or tools.

However, physical disk fragmentation is not the same as SQL Server defragmentation! SQL Server is different. SQL Servers use advanced storage systems

PAGE 3

Let’s dive into the details of where these voids sit, how they are created, and how they propagate throughout your server:

fragmentation manually.

A Typical, day to day activity causes SQL Servers to fragment over time.
Changes to your data – inserts, updates, deletes, and even changing varchar values contribute to fragmentation. The full list of actions that cause fragmentation is long and the rate of fragmentation varies across different indexes and tables. Sometimes there is a pattern resulting

Q What creates the voids and other adverse effects and how do I get a handle on them?

Fragmentation of your SQL Server’s internal allocations and page structures result in
‘gaps’ or ‘void’ space that is dead weight carried along with valid data. Your backups, storage, I/O channels, buffer memory, cached data, logs, tempdb, CPUs and query plans are impacted by these unnecessary voids. SQL’s fragmentation continually eats away at these resources with nearly every select, update, delete, insert, and table/index change. If ignored, fragmentation can be the proverbial ‘death by a thousand cuts’ to a server’s performance and scalability.

A Fragmentation of SQL Server indexes mainly creates wasted space that can affect your server performance much more than one might expect.

Q How is SQL Server’s fragmentation affecting my Server?

defragmentation by orders of magnitude. In a nutshell, SQL Server’s performance can be most improved by focusing on its internals. In fact, once you start focusing on defragmentation at the SQL Server level – whether with manual defragmentation or with the automated defragmentation provided with SQL defrag manager, you may decide that physical defragmentation is no longer needed!

PAGE 4

Shown here is a detailed diagram of how
SQL Server fragmentation can affect your
SQL Server performance and overview of the affected areas. As you identify how the fragmentation affects your server, you’ll see that fragmentation effects are cumulative and nearly impossible to predict. SQL defrag manager, however, uses sophisticated algorithms to predict and detect SQL Server fragmentation “hot spots” and to defragment indexes on a continuous basis.

PAGE 5

approximation.)

denser a page, the more data vs. void it contains. A page density of 100% would mean the data page is completely full. Even if the pages had no void, Figure 3

population post split. The common practice of using an identity column as your clustered index, forces inserts into new pages at the bottom of the table, preventing recovery of the voided space.

The void/waste space is known as “internal fragmentation.” Internal fragmentation lowers page density and as a result our server resources trickle slowly away now

The net result of drifts is waste – lots of it – waste of your disk, I/O channels, server’s caches and buffers, and CPU utilization. The waste may also skew your query plans.

The more that heavily, spiked, or continuous changes occur on a table, the faster and further it and its indexes drift. Since the indexes are based on variants of data in the

divides the full page evenly, putting half of its data on a newly allocated page, and

Fig. 2

gets a maximum of 8096 bytes per page – the rest of the page contains the page header and row locations.

SQL Server stores all data, objects, and internal structures in 8192 byte data pages shown in Figure 2.
These pages are known only to SQL Server and may

contiguously accessing the pages after the split.

PAGE 6

them, now what?

If, for a moment, we ignore SQL defrag manager, there are two existing methods for managing SQL Server fragmentation. Neither is ideal, or gives you the information you need to stay informed and on top of the fragmentation challenge. Both leave you completely blind — you won’t know if they helped, hurt, stepped on, or blocked your busiest table.

A Besides SQL defrag manager from Idera, there are two approaches most commonly used for fragmentation today and both have serious disadvantages. Q

process of splitting, voids, progressive order, and rates of decay requires non-stop attention to insure the server is running with as much free resource as it can.

While it may seem trivial on a small scale, when your average page density is low, you are wasting disk space, incurring more physical I/O, increased logical reads, wasting precious server memory while computing and comparing data unnecessarily.
Further, if you are fortunate enough to have an intelligent I/O controller, you are

space becomes too much (your page density becomes too low), SQL Server will discard the index due to excessive overhead. At this point, fragmentation becomes very evident as very few systems will tolerate discarding indexes in favor of table scans. fragmentation’.

the pages after the split. Interestingly this parallels physical fragmentation – although it is a completely isolated variant in SQL Server’s management of data vs.

in the defragmented space vs. the fragmented space. By reclaiming the voids, we return capacity to our server.

Fig. 4 The four pages require four logical reads. Defragmentation would condense the data by reorganizing it into two pages and two reads. A 42% reduction in void

PAGE 7

» Should be tailored to each database – but to do this would require near constant “hand-tuning.” A very time-consuming and practically impossible process.

» May require changes which would required you to re-deploy the new script to all of the servers in your enterprise.

» Rarely have internal logic to know when to defragment– instead they just steamroll your servers every day whether they need it or not (perhaps many times a day.)

» Request information that can cause contention or deadlocks.

All-purpose SQL Server defragmentation scripts:

does not track how defragmentation varies each time, and offers no

These scripts are often quite complex with unpredictable results. They usually work, but may often cause after effects such as blocking or locking and can generate considerable overhead. You have no idea

Second method: Run a blind maintenance script.

for the next hotspot or for SQL Server performance to run down again and again. Unfortunately, you will never know when your server is going to act up or how severe the impact will be. Furthermore, there may be cascade effects caused by inadvertent query plan disruption due to fragmentation.

The server performance degrades slowly and is ignored. All of a sudden, a spot in the database reaches critical mass, performance craters, and is eventually addressed. This is how the majority of DBAs

First method: Reactive damage control.

SQL defrag manager not only tracks the improvement achieved on each object, it maintains dozens of statistics on each table and index.

is sent to the DBA.

ability to ascertain the status of system resource metrices prior to executing the defrag policy. The DBA can set thresholds for (Server
CPU %, SQL server CPU %, Memory,TLOG % full) and much more!
The “Proactive Resource Check” makes it possible for the DBA to proactively anticipate unplanned outages or system bottle-necks that may cause application batch cycles to creep into the defragmentation maintenance window, which may prevent it from running. If the

Futhermore, SQL defrag manager brings proactive intelligence to

Consider this: If you are able to eliminate void space, every page of void reclaimed is money back in your corporation’s pocket. Those reclaimed resources are regained server capacity that had been lost unnecessarily. SQL defrag manager will reclaim these resources and track the total improvement on every object in your enterprise daily or over a year. You can even produce an annual report showing how much money has been saved through the use of defragmentation technology – and we guarantee that it will be impressive!

regarding the important task of index fragmentation maintenance.

SQL defrag manager offers a totally new way to identify, optimize, manage and automate SQL Server defragmentation. It is designed

Third method: Idera SQL defrag manager.

» Aren’t able to report when the script was run, what performance enhancements were gained, or how many resources they’ve reclaimed on your server since you started running them.

PAGE 8

The SQL defrag manager console gives you a centralized real-time manageable view into the fragmentation levels across hundreds of servers and thousands of databases.

This information guides SQL defrag manager to determine how often it should check for fragmentation, and if you wish, the method it will use to correct the fragmentation. SQL defrag manager eliminates defragmentation overhead and risk on your servers – there is no agent required on any managed server. There is no job scheduled or script deployed. SQL defrag manager simply runs as a service, quietly in the background with no affect to your production servers.

You be the judge of how we’ve done.

Unlike scripts, SQL defrag manager’s fragmentation detection routines are non-blocking. Defragmentation is also non-blocking, given the DBA has not chosen to rebuild the fragmented object. Rebuilt objects are often not needed.
SQL defrag was developed based on the feedback from experienced DBAs who were frustrated with the scripts and the handholding that their 24x7 , 99.999% available enterprises required. SQL defrag manager will shed light on the fragmentation levels across your entire SQL Server environment — allowing you to quickly detect and manage fragmentation with ease. It will also give you assurance that defragmentation is being handled in exactly the way it should be for that particular database – no more guessing!

PAGE 9

defrag policy.

IS IT SAFE TO DEFRAGMENT?

have it run a one time job at a convenient time for you — such as on the weekend.

Setting the schedule you wish for the object to be checked is straightforward. Start

INTELLIGENT SCHEDULING

becomes a problem again. You can set it to send you a summary and let you know if there is a problem, or it can simply defragment as needed – automatically.

Quickly drill down into the server, database, and locate an offending table/index by

impact fragmentation is having on it, its databases, tables and indexes. You can easily sort to bring the items most in need of your attention to the forefront.

Within moments of installation you’ll see a screen like that to the right.
Fragmentation level detection in SQL defrag is non-blocking. Unlike scripts or manual queries, risk of unpredictable impact is eliminated. You have immediate and complete visibility into your enterprise and how fragmentation is affecting it.

SQL DEFRAG MANAGER™

PAGE 10

»

»

operation even on weekends.

» Override any table you feel should be handled differently – perhaps with a custom range or time.

» If you set only the server level all databases, tables, and indexes will inherit.

CUSTOM TAILOR DEFRAGMENTATION TO YOUR ENTERPRISE –
SCHEDULE, SET THE RANGE, AND SET THE ACTION.

» Have it try non-blocking remediation, and if that doesn’t reach acceptable, try more invasive blocking.

» Have it try non-blocking remediation and if that cannot reach an acceptable level, tell you.

» Have it notify you of approaching thresholds, so you can manually defragment when you wish.

DEFRAGMENT YOUR WAY - WHILE INSURING MAXIMUM BENEFIT.

settings and track the fragmentation changes over time.

If your index defragmentation jobs are running too frequently , it may indicate

FREQUENT INDEX DEFRAGMENTATION? MANAGE INDEX FILL
FACTOR

Automation screen: This instructs SQL defrag manager to take a look at this object regularly. If you wish, you can specify a custom range for scan density and fragmentation. This is generally set at the server and all child objects inherit the setting – but you have the control. Simply override this function on any object in your environment. PAGE 11

» SQL defrag manager brings proactive resource checking intelligence to the defragmentation process

» There is no need for you to watch every potential problem in your enterprise. SQL defrag will let you know.

» The tool is as interactive or automated as you wish it to be.

LET SQL DEFRAG MANAGER DEFRAGMENT FOR YOU.

SQL defrag manager alerts you via email or error logs of the status of your defrag job and additionally lets you set the thresholds for 10 different alerts.

FLEXIBLE DEFRAGMENTATION STATUS NOTIFICATION

PAGE 12

company – every day.

» Model-based reporting allows for easy development of your own custom reports.

» All of the reports are in Reporting Services. Subscribe to your reports and read with your morning coffee.

»

» Detailed breakdown reports show exactly how each object has been managed, and the improvement.

» Comprehensive overview reporting covers every aspect of your enterprise at a glance. 10,000 FT. TO 1 FT. + 360° REPORTING -

» If SQL defrag manager predicts a problem will occur before it is next scheduled, it’ll let you know.

» Fragmentation details are intelligently collected based on a customizable schedule, keeping overhead on your monitored servers low and controlled.

LIGHTWEIGHT COLLECTION

» Agent-less collection mechanism. There are no resident scripts on any of your monitored servers.

» Sit at your desk - view and manage defragmentation activity across all of your servers. » The SQL defrag manager Management Console provides a real-time window into fragmentation levels.

CENTRAL ENTERPRISE MANAGEMENT CONSOLE – RUN THE
CLIENT FROM ANY MACHINE.

PAGE 13

ROI value for defragmentation that you can provide management. Perhaps if you save your company $50,000 they might consider that $10,000 raise?

SQL defrag manager reports on all resources reclaimed during defragmentation and will translate this into memory, disk, I/O, CPU, and backups – and assign hard costs

RESOURCES RECLAIMED REPORTS: PERHAPS THE MOST
IMPORTANT REPORT OF ALL?

Idera provides tools for Microsoft SQL Server,
SharePoint, and PowerShell management and administration. Our products provide solutions for performance monitoring, backup and recovery, security and auditing, and PowerShell scripting. Headquartered in Houston, Texas, Idera is a Microsoft Gold Partner and has over 5,000 customers worldwide. For more information or to download a 14-day trial of any Idera product, please visit www.idera.com.

About Idera