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