San Performace with SQl Server

San Performace with SQl Server

Post by U2FyYWRoa » Sun, 28 Aug 2005 00:56:09


hi everyone,

Am presenlty Testing the I/o performance on the new CX-500 San From Emc


And Hba From Emulex,The LUn Size is 1 TB and when doing the Testing
with SQlIO Tool with a I/o block size-8 i get 39.3
MB/sec for Random write requestes but when doing the same with Random
Read
requests i get 6.04 MB/sec as the output,the read/Write cache is
enabled with read cache set to 288 and write to 1198,We have a
sqlserver Oltp Enviroment with mostlty Read Intensive Operations , iam
wonering how can i increase the Read i/o ,The Lun design looks bad we
should have made smaller Luns and Shared the Data and Log Files among
them ,will i get any performance if i set read cache to low value or
turn off the read cache or should i recomend to rebuild the san design
to smaller chunks , i am not so well versed in san
design , what i want is best perfromance for my sql serverr IO ,Pls
Suggest

We Have Raid 10 On our San with 4D+4D Raid Group sets ,In this Situtation
what is the best Setting For the Read Cache for our Hba Emulex(2GB)


Thanks&Regards
--
Saradhi
 
 
 

San Performace with SQl Server

Post by SQL M » Sun, 28 Aug 2005 07:10:12

Hi

Have you tried formatting the drives at OS level with cluster sizes of 64kb?

We found it made a big difference on IO performance.

--
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: XXXX@XXXXX.COM

MVP Program: http://www.yqcomputer.com/

Blog: http://www.yqcomputer.com/

 
 
 

San Performace with SQl Server

Post by U2FyYWRoa » Sun, 28 Aug 2005 15:37:04

i am not aware how to do it,can u provide me any link on it pls

Thanks®ards
Saradhi
 
 
 

San Performace with SQl Server

Post by SQL M » Sun, 28 Aug 2005 18:03:36

Windows format.

From the command line

format <drive> /A:64K

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: XXXX@XXXXX.COM

MVP Program: http://www.yqcomputer.com/

Blog: http://www.yqcomputer.com/
 
 
 

San Performace with SQl Server

Post by U2FyYWRoa » Wed, 31 Aug 2005 16:21:02

If we format The Default cluster size to 64 kb at the Os Level ,will it not
need to
Defragmentation of the Disk ,and should we go for a regular defrag of the
Dirve chaning it ,if we increase the Read cache will we get any performance
boost out of it , and make the cache to 50- 50 read and write

Thanks,
Saradhi
 
 
 

San Performace with SQl Server

Post by U2FyYWRoa » Sat, 03 Sep 2005 01:44:02

hi mike ,
i have asked my admin about this of formatting our drive with 64 k as us
said, he says we have set element size of 128 k in the cluster and we need
not formst the disk again in that situation is it true ,can u pls clarify my
doubt

Thanks®ards
Saradhi
 
 
 

San Performace with SQl Server

Post by Anthony Th » Sat, 03 Sep 2005 08:32:22

ou should format the drive using 64 KB allocation units. Yes, the SAN
element size, which is just the equivalent of the RAID stripe size, is 128
KB by default, but for a different purpose. The reason you want 64 KB AUs
at the Logical OS level is because it reduces the number of IOPS to get the
same amount of data plus SQL Server will execute asynchronous disk requests
by Extents, which are 8 x 8 KB data pages = 64 KB. It can also request up
to 2 Extents whenever it is attempting to execute Read-Ahead requests, which
is 128 KB, and would be 1 request but 2 IOPS if formatted as 64 KB AUs
versus 32 IOPS is formatted with the default 4 KB AUs. If the turnaround
time is about 10 to 20 milliseconds per IO request, a 16 fold increase in
transmission could be quite a windfall for performance.

However, and this has been debated, you may wish to format the separate
transaction log disks as 8 KB AUs as the transaction log is a serial process
that is not tied to Extents nor Read-Ahead operations. In this case,
aligning with the data page size would be preferable.

That being said, even though we have separated our Data files from TLog
file, and TempDB data from User DB data, we do dedicate and format SQL
Server database file drives all with 64 KB AUs and have increased our IO
throughput.

If you need to support a high levels of Read IOPS, buy more, smaller disks
for the SAN, increasing the spindle count. If you need to support high
levels of Write IOPS, then by more HBAs and a Multipath solution for your
server. EMC makes PowerPath; Veritas has Volume Manager. And there are
many other products on the market which will do the same task.

Finally, along with properly formatting your disks, you also need to
consider properly aligning your NTFS volumes with the SAN track sectors.
This can be done either on the SAN or on the OS. On the SAN, it is called a
LUN Offset. On the Win2K OS, you can use the Resource Kit DISKPAR tool to
"sector" align the partition before you format it. For Win2K3, if you are
pre-SP1, use the DISKPAR utility; if post-SP1, use the OS DISKPART tool.
The difference is that DISKPAR will ask you for the number of sectors to
offset for alignment, DISKPART will ask you for the number of bytes. What
you want is 64 sectors = 32,768 bytes.

Here's the reason, for many modern disks, tracks contain more than 63
sectors/track, but NTFS was created when they were just 63 sectors/track,
and reserves the first track as the MBR header; so, it is unusable by the
OS. When you partition, you will end up starting at sector number 64,
assuming each track only contains 63 sectors. However, consider what
happens if you really have 64 or more sectors/track. If you request 4 KB
chunks, you will request the first seven just fine, but on the eighth, you
will ask for 4 KB, but there will only be 3,584 bytes left on the track; so,
to fulfill your request for 4,096 bytes, the first 3,584 bytes will be read
from the current track, but the final 512 bytes (one sector) will be read
from the adjoining track. That means you will have to incur the additional
latency of the seek time to move the disk actuator from one track to the
next, the slowest operation of any but the latest disks. If you use 64 KB
AUs, you will incur this additional latency with each and every IO
operation.

Now, the proper alignment depends on the number of sectors/track the
particular disks used in your SAN; so, get thi