Block size issues causing performance or transactions to slow down

Hello friends this time,

today I want to attach a case that I've ever read in my current company, has to do with the importance of the disk block size environments data base.

They are attached and read they recommend. All should take into account the block size for the disks that hold your data files.

Details:

Detailed description can be found at Autotrac Record Link: <provide link> NA

Client complains about performance and transactions are not at the optimal range according to the client.

The SQL DBA should check the disk IO based on input from the client as there could be an issue with the block size previously used to format the disks prior to MS SQL being installed on the drives.

A test case scenario with a 900 swipe a second credit card simulator:

The disk response time in milli seconds was unacceptable (started increasing past .5\.7 and 1+ when reached an average of 4,000-4,500 transactions per second on 2 drives ). SQL DBA viewed the block sizes used to format the 2 drives with bad performance and found that Partition starting offset sizes of 32kb. Although disruptive to make the change, the data was removed, the drives reformatted with 64kb blocks and the test case scenario achieved an average of 7,500 \ max of 13,000 transactions per second while staying under the .5 ms second disk response time.

Client complaints regarding SQL performance, transactions not at the optimal range

Ed, Why after checking (what typically does the SQL dba check for when client complains of performance or transactions going to slow?

On the OS Server, local disks or storage devices(mount points):

To check for block size used, you go to Start --> run --. and type: msinfo32 and hit enter. Navigate as seen below and view the partition starting offset and as viewed below this would be a 32kb block size. This is normally not optimal for MS SQL Server performance. This is also the default for Windows Server 2003 systems when not 64kb specified during disk format. Newer OS's such as Windows 2008 use a larger block size and partition alignment works out of the box usually.

This would be a disruptive change if block size were found at 32 KB. The SA would need to be contacted, the data would need to be removed before the change from 32 KB to 64 KB is made and the device would need to be reformatted and then data loaded back and tested.