Different I/O performance each datafile same LUN

[former_member192334]

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???

Thanks a lot for advance

Javier

[former_member192334]

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.

In fact. there is a SAP Note very clarifier...886337

Regards and thanks for your suggestions

Javier

[Former Member]

Hi,

Check disk fragmentation of the data files.

regards,

Wojtek

[former_member192334]

Hi

What do you mean with fragmentation of datafiles?

Keep in mind that this database comes of an export/import from another platform.

thanks

Regards

Javier

[Former Member]

Javier,

I don't think this is related to the performance on the SAN. I believe this is more related to which tables are stored in which table spaces and how often those tables are accessed (others on the list feel free to correct me).

Hope that helps.

J. Haynes

Denver, CO

[former_member192334]

Hello again

Let's see. I 'll explain all details.

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

Are you sure that is caused by Tables SQL server????

Thanks and regards

Javier

[Former Member]

I'm talking about disk fragmentation - not database.....

Please open Windows Disk Defragmeter and analyze disk fragmentation with data files. Such fragmentation often takes place, when you import data and have small initial size of data files. I such case the file does'nt occupy continuous space on disk but is spreated on whole disk in severel fragments - it always decrease performance of data access from such data file.

[former_member192334]

Analyzed:

"Yo do not need defragment this volume DBFiles (I:)"

Any other suggestion?

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...

Thanks and regards

Javier