Share via

What is good metrics for Storage on NAS for SQL server on VM

Devendra Yadav 1 Reputation point
2020-10-13T15:45:37.917+00:00

Dear experts,

I have Always on Availability groups configured in synchronous mode with two servers. Primary and Secondray servers are in different DC's. They have 8 core CPU's & 128GB RAM. They are VM servers.

I have seen disk latency for Data drive and Log drive and TempDB drive between 500-1000ms. They are all on separate drives and use NAS storage. These NAS (NetApp) boxes are using datastore and the datastores have disk that are mapped to the VM server.
I did check with Storage team, and they see less that 5ms on the Datastore. Could you guys suggest what should be optimal metrics for this on SQL server or this is normal?

Regards,
Devendra Yadav

SQL Server | Other

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. m 4,276 Reputation points
    2020-10-14T06:14:02.96+00:00

    Hi @Devendra Yadav ,

    ...Could you guys suggest what should be optimal metrics for this on SQL server or this is normal?

    Quote from this doc. : monitoring-disk-usage
    ...
    Less than 10 ms - very good
    Between 10 - 20 ms - okay
    Between 20 - 50 ms - slow, needs attention
    Greater than 50 ms – Serious I/O bottleneck
    ...

    So for your case , 500-1000ms is serious bad.

    Follow steps as this doc. to troubleshoot the issue : how to troubleshoot SQL Server I/O-related issues

    Workaround:
    Quote from this doc.: slow-storage-reads-writes
    ...
    To Fix the Problem
    There’s two ways to fix slow storage: make the storage go faster, or ask the storage do to less work.

    The second option – asking storage to do less work – is usually the easiest way to start. By dropping unused indexes, we can insert/update/delete data faster, run backups faster, do DBCCs faster, and even do index rebuild jobs faster. By using the right indexes, we can avoid storage-intensive table scans and do less data juggling in TempDB.
    ...

    More information: how-to-examine-io-subsystem-latencies-from-within-sql-server ,monitoring-read-write-latency, monitor-sql-server-io

    BR,
    Mia

    If the answer is helpful, please click "Accept Answer" and upvote it.

    2 people found this answer helpful.
  2. m 4,276 Reputation points
    2020-10-15T07:11:02.15+00:00

    Hi @Devendra Yadav ,

    Is the reply helpful?

    BR,
    Mia

    If the answer is helpful, please click "Accept Answer" and upvote it.

  3. Shashank Singh 6,251 Reputation points
    2020-11-03T17:23:55.223+00:00

    Yes we have SSD and SQL server runs on Virtual machines to which sql drives are connected using datastore of NAS.
    I have seen disk latency for Data drive and Log drive and TempDB drive between 500-1000ms.

    Both your comments now point to 3 things

    1. You have some serious problems with your drive. 500-1000ms latency is atrocious and that too when you have SSD.
    2. Something is really putting too much load on tempdb. While chances of this are less as latency is 500 ms but this could also not be rules out.
    3. You have problem with underlying storage disk, SAN or controllers. Get this checked.

    Is there any other data file on drive where tempdb resides or this drive is dedicated to tempdb ?

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.