Questions[fragmentation](server)

I'm using Windows Storage Space with multiple hard drives.

If I create multiple virtual disks, then delete some of them, and resize some of them, will the virtual disk be finally divided into multiple fragments and will these fragments be located everywhere across all these hard drives?

For example: If VirtualDisk1's first 1TB is located at the beginning of PhysicalDrive4, and VirtualDisk1's second 1TB is located at the end of PhysicalDrive4, VirtualDisk1's last 3TB is on PhysicalDrive5, and VirtualDisk2, VirtualDisk3, etc.

Demonstration picture of such fragmentation

I wonder if such fragmentation will cause performance issues and how does Windows deal with such situations and how does Windows optimize storage space for this scenario?

I have a Redis 3.0.5 instance that tend to show growing mem_fragmentation_ratio over time.

The application using that instance is constantly creating and deleting keys.

After one month, I end up with a mem_fragmentation_ratio > 1.30. This impacts the memory footprint of Redis on that server:

~$ redis-cli info memory
# Memory
used_memory:7711297480
used_memory_human:7.18G
used_memory_rss:10695098368
used_memory_peak:11301744128
used_memory_peak_human:10.53G
used_memory_lua:95232
mem_fragmentation_ratio:1.39
mem_allocator:jemalloc-3.6.0

If I restart the Redis service and reload from AOF, mem_fragmentation_ratio goes back to acceptable level (1.06):

~$ redis-cli info memory
# Memory
used_memory:7493466968
used_memory_human:6.98G
used_memory_rss:7924920320
used_memory_peak:8279112992
used_memory_peak_human:7.71G
used_memory_lua:91136
mem_fragmentation_ratio:1.06
mem_allocator:jemalloc-3.6.0

Recycling Redis is impacting for our application (even if we do this with a Sentinel failover after a slave restart).

Is there another way to reduce mem_fragmentation_ratio, like a 'defragmentation' process that I could schedule off-peak ?

I have a mssql database, we'll call it: mssqlDB01. I've been tasked with performing defragmentation on all of the tables. This database has a few hundred tables and each table has a range of 1 to 15 indexes per table.

Google led me to discover a practice to defrag all indexes per table, but I can't figure out how to do it on all tables.

ALTER INDEX ALL ON TABLENAME REBUILD;

what i'm looking for is

ALTER INDEX ALL ON * REBUILD; 

but it complains

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '*'.`

below lets me find all tables in my DB

SELECT * FROM information_schema.tables WHERE TABLE_TYPE='BASE TABLE'

can I somehow push this into the command?

ALTER INDEX ALL ON (SELECT * FROM information_schema.tables WHERE TABLE_TYPE='BASE TABLE'; ) REBUILD;

Is there a way to detect memory fragmentation on Linux? This is because on some long running servers I have noticed performance degradation and only after I restart process I see better performance. I noticed it more when using Linux huge page support -- are huge pages in Linux more prone to fragmentation?

I have looked at /proc/buddyinfo in particular. I want to know whether there are any better ways(not just CLI commands per se, any program or theoretical background would do) to look at it.

I know that there are really three kinds of fragmentation that I need to be concerned about as a DBA:

1. Index Fragmentation in the SQL data files, including clustered index (table) fragmentation. Identify this using DBCC SHOWCONTIG (in SQL 2000) or sys.dm_ db_ index_ physical_ stats (in 2005+).

2. VLF Fragmentation inside of SQL Log files. Run DBCC LOGINFO to see how many VLFs are in each of your SQL log files.

3. Physical file fragmentation of the database files on the hard drive. Diagnose this by using the "Disk Defragmenter" utility in Windows. (inspired by this excellent blog post)

A lot of attention is paid to index fragmentation (see this excellent Serverfault answer from Paul Randall), so that's not the focus of my question.

I know I can prevent physical fragmentation (and VLF fragmentation) when the database is originally created by planning a reasonable expected data file and log size, because this fragmentation occurs most often from frequent grows and shrinks, but I have some questions about how to fix physical fragmentation once it is identified:

• First of all, is physical fragmentation even relevant on an Enterprise SAN? Can I/should I use Windows Defragmenter on a SAN drive, or should the SAN team be using internal defragmenting utilities? Is the fragmentation analysis I get from the Windows tool even accurate when run on a SAN drive?

• How big of a deal is physical fragmentation on SQL performance? (Let's assume an internal drive array, pending the outcome of the prior question.) Is it a BIGGER deal than internal index fragmentation? Or is it really the same kind of problem (the drive having to do random reads instead of sequential reads)

• Is defragmenting (or rebuilding) indexes a waste of time if the drive is physically fragmented? Do I need to fix the one before I address the other?

• What's the best way to fix physical file fragmentation on a production SQL box? I know I can turn off SQL services and run Windows Defrag, but I also heard about a technique where you do a full backup, drop the database, then restore from the backup to an empty drive. Is this latter technique recommended? Does restoring from a backup like this also build indexes from scratch, eliminating internal index fragmentation? Or does it simply return the page order to the same as when the backup was taken? (We're using Quest Lightspeed backups with compression, if that matters.)

UPDATE: Good answers so far on whether to defragment SAN drives (NO) and whether index defragmentation is still worthwhile on physically fragmented drives (YES).

Anyone else care to weigh in on the best methods for actually doing the defragmentation? Or an estimate on the length of time you'd expect it would take to defrag a large fragmented drive, say 500GB or so? Relevant, obviously, because that's the time my SQL server will be down!

Also, if anyone has any anecdotal info on SQL performance improvements you've made by fixing physical fragmentation, that would be great, too. Mike's blog post talks about uncovering the problem, but isn't specific about what kind of improvement it made.