SQL Server 7 - high disk msec/Read

[Former Member]

Dear SDN Experts,

I'm not familiar with SQL Server.

Could someone advise me what could be tuned on the SQL database if disk queue length & disk msec/Read are too high? These statistics are based on OS performance monitor.

Fyi, the CPU and Memory are low on usage.

How to tune the SQL database to improve the disk queue length and time it takes per Read from disk? Any buffering? Indexing? Kindly give some advice on the required How-To's.

Many Thanks.

Alfonso.

[Former Member]

Hi

Low memory utilization and high disk queue length and disk reads indicate that the SQL server process is not allocated sufficient memory and hence it is using the virtual memory and thereby causing high disk reads and disk queue lengths. Please check the 'BufferCacheHitRatio' and if it is less than 80%, the server is not assigned enough memory or the machine does not have enough memory.

In this case, you allocate sufficient memory to SQL server by using the sp_configure stored procedure that sets the values of MIN SERVER MEMORY and MAX SERVER MEMORY to dedicated values. Make sure that these values are set such that other memory bound processes running ont the same machine do not get affected.

If you observe high disk I/O even after allocating sufficient memory to SQL server process, consider revising the disk configurations (such as RAID configuration etc.)

In general, SQL server automatically configures the buffers and thier sizes provided sufficient memory is allcoated to the server process.

Hope this is helpful.

Thanks and Regards

Madhu

[Former Member]

Thanks to Madhu for the very useful advise.

For my case, the Buffercachehitratio is 99%. Does this mean that allocating more memory to database buffer will not help?

I'm also surprised to find that the msec/Write is slightly lower compared to msec/Read based on Windows Perfmon.

If this IO bottleneck is due to slow disk, why is it that the Write is faster? Does this indicate something else? Can you pls provide additional insight.

Appreciate your reply, Madhu!

Regards,

alfonso s.

[Former Member]

Hi

The 99% BufferCacheHitRatio indicates that most of the data is accessed from the Cache which is good. Anyway, could you please let me know what percentage of physical memory is being assigned to SQL server process..

I suggest you to look out for the paging activity which indicates if memory is the problem for the disk bottleneck. since the writes are faster than reads, please check if proper indexes are created for the tables.

Make use of the SQL tools and trace the calls and get the explain plan for the queries to identify if full table scans or index scans are performed frequently.

Hope this will be helpful

Regards

Madhu

[Former Member]

Excellent points raised by Madhu babu.

You can look in ST03 and see what transactions are taking too long, or being run too often. Also check your SAP buffers in ST02. Check for missing indexes in DB02. The SAP system should be correctly sized so that around 60% RAM is given to SAP, 30 - 40% to MSSQL server. If you have multiple SAP systems on the same box, or other appications such as J2EE engine, TREX etc. , you need to set aside RAM for those too.

Most of the times if you tune the SAP transactions / reports, the load on the database automatically eases up.

Regards,

Sanket