SAN Maintenance

staggerlee

Hi all,

What maintenance do you do on SANs?

We have some disk that hold SQL data (*.mdf and *.ldf) someone ran a report and it resulted in us finding out the disks had 98% fragmentation.

Do you SAN guys run defrags? Ive only ever used the Windows defrag and rarely seen any kind of improvement, would defragging a SAN improve anything that much?

Anyone know the kind of effect a 98% fragmented disk would have on SQL?

what maintenance do you guys do on SANs?

Does anyone know any decent beginners books that i can look into for info on the SAN world?


Any input gratefully received

Cheers

s

RobertKaucher

staggerlee wrote: »

Anyone know the kind of effect a 98% fragmented disk would have on SQL?

s

Exactly what you are imaging.

You should ideally find a tool to perform online, external defragmentation,
Keeping Your SQL Server Databases Defragmented with Diskeeper

Defrag will work but you should not really run it while the MDFs and LDFs are doing their thing. What are you doing about internal frag on the DBs?

I am not qualified to give an opinion regarding the SAN portion of the question. I thought most SANs had tools for that though.

staggerlee

Hi Robert,

Thanks for the link

Our SQL man has started (yep started) doing index rebuilds (for some dbs)

Its all come about due to a slow query, apparently a index rebuild on the table it uses fixs the problem but half a day later its slow and needs rebuilding. The DB is for an external app, and there tech suggested looking at the SAN hence we found out about the 98%

None of our team do maint on the SAN as that was installed by a 3rd party, if we have any issues they come in.

Would this really course a problem messing up 1 index but not the other xxx indexes we have? I dont see how it could. Yes i think we should deal with it. But this is now the sole reason for the problem as far as the people who made the app and our SQL guy /sigh

Cheers

s

Claymoore

Start with this book:
Storage Networking Fundamentals: An Introduction to Storage Devices, Subsystems, Applications, Management, and File Systems

I used it as a supplemental resource when studying for my EMC ISA certification, and it's a good primer on storage.

First off, I am not a SQL expert. I do know a few things about SANs and Exchange storage design, so I'll approach the problem from that perspective. If I'm wrong on something that applies to SQL, I'm sure someone like RK will step in and fill in the gaps.

From reading your post, it looks like you have LDF and MDF files on the same disk. This is almost always a very bad thing. The classic reason why you separate database and transaction log files is that if you lose the DB disk, you can go back to tape and replay the logs from the still functioning log disk. The modern reason why you separate the log and DB files is because they have different I/O requirements. Logs are linear and DBs are random. You want to minimize the penalties of rotation latency (disk spinning) and seek time (head movement) on your log disks. If you only have linear log data on a disk, then the write operations occur in one nice long sting like a needle on an LP. If the R/W head has to skip all over the disk - which happens when you mix data and/or your disk is highly fragmented - your performance will suffer.

About the only times you can get away with mixing data types is with a smaller test/dev environment without a lot of I/O or your SAN has a large amount of write cache. By having the SAN cache the writes and then commit them in large chunks, you hide the mechanical penalties of the disk.

it_consultant

I am almost certain that the disk does not actually have 98% fragmentation. Almost every SAN I can think of does its own maintenance to prevent this behavior. Did you run this report in Windows or through the SAN's interface? What kind of SAN are we talking about?

Windows can have trouble understanding disk information if its mounted over iSCSI or Fiber, consider that the underpinnings of most SANs is some form of linux.

staggerlee

Hi it_consultant

the defrag was run on a trial software of diskeeper (version im not sure on).

The SAN is a LSI which is apparently a re badged .hill (None of this makes sense to me so do correct me if im wrong) We use SANtricity software.

Claymoore, yes the combined ldf and mdf on the same disks is a sad sad thing.. We are upgrading to 2008 server shortly and im getting them put on different RAIDs now.. Im also going to try and set the file sizes of the ldfs so they arent constantly growing and being shrunk again by our dba as i read that helps with it all.

Another problem that has turned up apart from the query is the external company have wrote a app that apparently re-creates all the tables and adds in a new column in some tables to help with indexing etc. This takes about 6 hours to run on test machines. But crashs every time its run on the cluster, getting Timed out connections. (Again the SAN is being blamed!)

thanks for the input. I shall check out the amazon link shortly