SQL Server Performance

Disk-Speed - local vs. NETAPP

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by dropout, Dec 24, 2008.

  1. dropout New Member

    Hi all
    Our IT recently switched the storage system for the hole company. Now we have a NETAPP storage system. During this migration they moved (without asking!) our SQL-database from the local virtual (vmware esx3) harddrives to the NETAPP.
    My perfmon-logs show the following:
    DatumStartEndePhys. Disk: % Disk timePhys. Disk: Avg. Disk Q. L. writePhys. Disk: Avg. Disk Q. L. readProcessor: % Processor time
    Av.Min.Max.Av.Min.Max.Av.Min.Max.Av.Min.Max.
    10.07.200808:0011:000.4890.00311.8770.0020.0000.1060.0180.0000.4735.3730.23428.659
    28.10.200808:0011:001.7450.01261.9710.0510.0002.3660.0190.0000.4639.7980.32636.823
    11.12.200813:0516:052.1230.00455.2150.0460.0002.2030.0390.0001.0678.8650.26049.388
    12.12.200808:0011:002.4940.00456.5750.0300.0002.2330.0700.0000.7699.5260.27337.904
    NETAPP Phase 2
    15.12.200808:0011:007.9570.01365.5760.0350.0011.2770.2840.0002.61211.4230.26053.177
    15.12.200813:0016:004.6750.035127.8120.0540.0011.5130.1330.0004.2978.4030.41736.328
    16.12.200808:0011:008.4750.01283.2610.0430.0001.8780.2960.0003.32210.4900.23452.695
    16.12.200813:0016:008.8270.042103.6530.0640.0011.6020.2890.0003.30214.4950.83345.182
    19.12.200808:0011:008.1880.01666.1190.0240.0010.8710.3030.0002.62510.0980.28650.703
    19.12.200813:0016:007.8500.015105.0430.0350.0011.0720.2790.0004.16510.7770.23438.646Write speed:
    • NETAPP is faster
    • We can verify this in our application
    • This might be because of the bigger cache?
    Read speed:
    • NETAPP is way slower
    • All read-intensive tasks in our application are noticeable slower
    My questions:
    • Do those numbers in the table verify my statements above? There are only small changes of the numbers, but you can feel it clearly in our application.
    • Is ist justifiable to ask the IT do undo the migration for our SQL-database? What's your opinion?
    Sorry that I can't deliver more details. But thanks for any advise.
    Reto E.
    dropout, Dec 24, 2008
    #2

  2. satya Moderator

    In general SQL Server runs at around 75% - 80% of native performance when you virtualise it under ESX Server. If you observe the READ performance of the disks is really low, the virtual memory setting will also degrade the performance.
    Did you align the virtual machine with the NetApp LUN?
    satya, Dec 24, 2008
    #3

  3. moh_hassan20 New Member

    Avg. Disk Read Queue Length and Avg. Disk Write Queue Length are not accurate counters for SAN storage
    it is best to use latency counters whic are: Avg. Disk Sec/Read and Avg. Disk sec/Write
    can you give us the values of these counters.?
    Try to rebuild all indexs to removve any internal fragmentation in the database
    Be sure that location of data and log are located in different LUNS.


    moh_hassan20, Dec 25, 2008
    #4

  4. satya Moderator

    ... to add up I would say atleast REINDEX required indexes (tables that are having huge inserts/deletes/updates and so on) [;)]
    satya, Dec 27, 2008
    #5

  5. dropout New Member

    Hello and thanks for your help.
    Counters:
    Unfortunately I have only those Disk-counters. I could make new readings but I have no history data.
    DB:
    DB-Indexes are rebuild regularly. This should be optimized.
    Fragmentation:
    The harddisks show heavy defragmentation (in the windows-server defrag-tool), but this shouldn't matter since the files are on the NETAPP?!?
    I guess I have to go "in fight" with our IT to improve the location of our files on the NETAPP.
    I'll keep you posted.
    dropout, Dec 28, 2008
    #6

  6. satya Moderator

    After your clarification I do agree with you that the STORAGE team (within your Org.) and further you should collect some more stats to show that NETAPP is causing issues.
    Few installations at our side that used NETAPP, we found that iSCSI initiator has some performance issues on the entry-level FAS boxes that is due to latency around 25ms! Sometimes this will get worse if the transacation log is not sized up to the extent and any other log files are generated on the server, adding more fuel to the fire.
    No doubt that A well-configured local disc array often performs better, but doesn't have the advantage of shared resources and you may also enquire about NetApp configuration to find whether it is using Fibre Channel connectivity. Using IOMETER tool you could run few performance tests to see how the NetApp is performing, and here I would like to present few blogs and information for your reading:
    http://sqlserver-qa.net/blogs/tools...t-size-to-64k-any-benefit-or-performance.aspx
    http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1262122,00.html#trace
    This is getting interesting please do post your findings.
    satya, Jan 2, 2009
    #7

  7. dropout New Member

    Hi all
    Thank you very much for your help so far.
    I will try to make some further performance tests of the NetApp. Afterwards I would like to switch back to the local storage we had before the NetApp and do the same test. This should give me a good overview.
    I'll keep you posted.
    Reto E.
    dropout, Jan 4, 2009
    #8

  8. dropout New Member

    Yesterday I made the first readings on the NetApp.
    C: D: E: F:
    Datumvon - bis system.mdf / tempdb.ldfbackups
    05.01.200908:00 - 11:00avg. sec./writeavg.0.001 0.004 0.001 0.001
    min.0.001 0.000 0.000 0.000
    max.0.007 0.083 0.006 0.023
    avg. sec./readavg.0.000 0.011 0.007 0.000
    min.0.000 0.000 0.000 0.000
    max.0.031 0.055 0.182 0.014
    • d: is for the .mdf an the tempdb (due to available space on this drive)
    • e: every 30 minutes there is a read-spike when the log-file is backed up to f:
    TempDB:
    • 4 x 100MB Data (only 10MB is used)
    • 1 x 2500MB Log (1250MB is used)
    • recovery model: simple
    • no maintenance plan
    • has to be on d: because the other drives haven't enough free space
    Those readings doesn't seem to be too bad (to me). I will make some further readings on the NetApp and then switch back to local storage and do the same tests.
    I will also try to use IOMeter, but for this I need the permission of our IT.
    dropout, Jan 5, 2009
    #9

  9. dropout New Member

    Hi all
    I got our IT to switch back to local disks. Here the results for D:
    • NETAPP
      avg. write between 4 and 41 ms
      avg. reed between 11 and 37 ms
    • Local Disks
      avg. write between 1 and 2 ms
      avg. reed between 3 and 4 ms
    The problem is, that the NETAPP is one big storage cloud for the whole company. So if the NETAPP is strong used by other applications or users, the performance is suffering. This is of caurse bad for a SQL-Server ...
    Unfortunately discspace is already again a problem. So I can't get the IT to reconfigure our NETAPP.
    Greetings
    Reto E.
    dropout, Feb 20, 2009
    #10

  10. moh_hassan20 New Member

    <p>review that document for sql server and NETAPP:</p><img src="file:///G:/DOCUME%7E1/m/LOCALS%7E1/Temp/moz-screenshot.jpg" alt="">http://download.microsoft.com/download/a/4/7/a47b7b0e-976d-4f49-b15d-f02ade638ebe/FibreChannelAndiSCSIPerf.pdf
    moh_hassan20, Feb 24, 2009
    #11

Share This Page