Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Different I/O performance each datafile same LUN Expand / Collapse
Author
Message
Posted Tuesday, January 22, 2008 11:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 31, 2008 10:14 AM
Points: 4, Visits: 10
Hello guys

We have a strange case. We have a SAP , on Windows 2003, using SQL Server 2005, and using a EMC Clarion for Storage.

Globally , response time of SAP is very good.
SQL Server response time is good.
CPUs have an excellent performance
..
But we have noticed I/O performance of Datafiles of SQL server is not excellent. This database have 8 datafiles, and I/O per file, is between 10 and 20 ms. Is not bad, but is not excellent...

The curious thing is: the number of I/O is well balanced between 8 datafiles but ms/IO per each datafile is different, and is growing (!?). I mean:

MTPDATA1 Data I: 13,168392
MTPDATA2 Data I: 13,965697
MTPDATA3 Data I: 15,507416
MTPDATA4 Data I: 16,531073
MTPDATA5 Data I: 17,404987
MTPDATA6 Data I: 18,767620
MTPDATA7 Data I: 20,017096
MTPDATA8 Data I: 20,813471

Any explanation for this???


This Database comes from an Heterogeneous System Copy. Originally was on OS/400 DB2/400. One week ago we are working in new platform Windows 2003 /SQL Server 2005.

First point:This database have been created with an export/import. So this database should be free of fragmentation problems. Should be reorganized.

Second: He details of I/O per file:
Filename Type Partition ms/IO Reads Writes I/O wait ms

MTPDATA1 Data I: 11,071782 8.728.781 371.484 100.756.151
MTPDATA2 Data I: 11,768664 8.734.624 358.552 107.014.530
MTPDATA3 Data I: 12,402772 8.722.614 365.930 112.723.139
MTPDATA4 Data I: 13,181894 8.737.115 372.423 120.080.961
MTPDATA5 Data I: 13,916623 8.749.069 382.267 127.077.363
MTPDATA6 Data I: 15,081958 8.734.311 380.829 137.474.162
MTPDATA7 Data I: 16,147454 8.776.978 380.401 147.868.360
MTPDATA8 Data I: 16,823962 8.731.213 374.198 153.189.086
Data 69.914.705 2.986.084 1.006.183.752
MTPLOG1 Log J: 2,778653 4.022.434 3.234.530 20.164.584
Log 4.022.434 3.234.530 20.164.584
73.937.139 6.220.614 1.026.348.336

As you can see the balancing is right. Each datafile has the same ratio of reads and writes.

The thing is: time I/O access of each datafile is different. And, curiously is scaling...MTPDATA1 < MTPDATA 2 < MTPDATA 3... < MTPDATA8



I 'm just talked to Storage Manager. He told me that cache of Clarion has a low activity.

Is very strange. The most access is sequential...


Any help please???

Thanks and regards
Javier
Post #446040
Posted Thursday, January 24, 2008 12:47 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697
I can only make general observations from experience with SAN's ..
are your luns dedicated spindles - if not then you will have contention.
are all your trays/luns on the same fibre speed
are the number of spindles for each lun identical
I don't know how your san configures it's cache, but I'd say check it's config is the same - in general terms read cache tends to slow performance and write cache improve performance.
I've never found SAN engineers generally very helpful - there seems to be an inbuilt arrogance that performance should not be questioned !


The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Post #447166
Posted Friday, January 25, 2008 10:18 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:59 PM
Points: 4,411, Visits: 6,284
1) were all files created exactly the same size and time?
2) were all files created large enough for all data and data growth or did you let autogrowth manage the size as you loaded data and began processing?
3) are all files now exactly the same size?
4) are all files part of the same filegroup?


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #447656
Posted Friday, January 25, 2008 11:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 31, 2008 10:14 AM
Points: 4, Visits: 10
Hello

First of all, thanks for your answer.

Past day, we had a meeting with Clarion engineers, customer and us.

Clarion engineers admitted to have set up a bad configuration of LUNS.

It seems there is problem of missalignment, in case of Windows. Is necessary to do a format manually to Labels of Windows, using Diskpart

By default whenMicrosoft Disk Manager formats Clarion LUNs it creates a partition starting at 63rd sector,which misaligns the partition with the underlying storage subsystem.This can cause a greater I/O load than is necessary.

If anybody wants more information, ask me.

Thanks and regards
Javier
Post #447691
Posted Friday, January 25, 2008 12:18 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:59 PM
Points: 4,411, Visits: 6,284
That is something that almost every non-experienced (and quite a few of the experienced) SAN configuration personnel miss. However, I am not sure I understand why it has led to different performance for each file.

Note that there are a bunch of other settings to be manipulated to achieve optimal SAN storage for a SQL Server installation. A good consultant is worth his/her weight in gold here. I see it time and again where companies drop 6 or even 7 figures on a storage system and then try to configure it themselves (or with just the assistance of a vendor-provided-generic-installation-consultant. Such a waste.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #447752
Posted Saturday, January 26, 2008 3:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 31, 2008 10:14 AM
Points: 4, Visits: 10
Hello

It seems there is missaligment regarding to initial sector of disk. So, probably file1 has an offset , and file2 has more offset, file3 more, and so on..

Isn't?

Regards
Post #447914
Posted Saturday, January 26, 2008 7:56 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:59 PM
Points: 4,411, Visits: 6,284
I didn't understand it to work that way. I thought the actual files that were created were all made with even block sizes (usually 64 512 byte sectors or 32K) and would all thus be 512 bytes misaligned. I could be incorrect in this.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #447937
Posted Saturday, January 26, 2008 9:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:18 PM
Points: 7,112, Visits: 15,494
Of course - if your block size is 32 (like you mentioned) and your file sizes aren't an even increment of said block size - wouldn't your alignment be all over the place?

For alignment to really work, EVERYTHING needs to be aligned, right? Partition start,File starts, file sizes, etc....? and in the case of SQL files, growth factors?


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #447946
Posted Saturday, January 26, 2008 2:21 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:59 PM
Points: 4,411, Visits: 6,284
See here for description of DISKPART commands: http://support.microsoft.com/kb/300415. Down at the start of the CREATE section "On all MBR disks, the size or offset parameters are rounded up to cylinder alignment. On GPT disks, the size or offset parameters are rounded to sector alignment." This should result in things not getting worse, but again I could be wrong. I think the GUI tools for making volumes do the same.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #447971
Posted Tuesday, January 29, 2008 11:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 31, 2008 10:14 AM
Points: 4, Visits: 10
Hi guys

I can confirm my theory.

EMC Clarion Manager have performed changes on configurarion of SAN: one of them regarding to right alignment.

Now, the disk access time have been improved, and now access time of each datafile is not sequentally but similars.

Regards and thanks for your suggestions

Javier

Post #449030
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse