Best practices SQL on Netapp luns

Discussion in 'microsoft.public.sqlserver.programming' started by xperre van wilrijk, Apr 20, 2012.

  1. Hi,

    I'm not a storage expert. I just maintain a set of virtualized sql
    servers using Netapp luns (iscsi/snapdrive/snapmanager;...).

    I noticed that data updates on Netapp are written to free blocks,
    meaning the original block is not updated, but kept, since referenced
    by snapshots earlier made.

    So, may I conclude fragmentation is inherent to Netapp? May I
    conclude windows defrag might cause volumes running out of space? May
    I conclude that (in case we would have enough free space in the
    volume) the chance that less physical IO is initiated after windows
    defrag is negligible or even that in some cases the number of physical
    IO's might increase? May I conclude Windows will initiate less IO's
    since it thinks data is sequentialized, but the consequential number
    of IO's on Netapp is unpredicatable?

    May I conclude that the sql command "set statistics io on" does not
    tell me the truth about the number of physical reads executed on
    Netapp (or any other disk virtualisation/SAN system), only the number
    of physical IO windows or SQL initiated (thinks that have to be
    done)? Thus it's possible that Windows launches 5 physical IO's, but
    data is in Netapp cache, meaning no real physical disk access is
    executed.

    Anyway I wonder what defrag means in RAID setup. Isn't it so that 1
    byte might be spread over 8 physical disks ... or that 8 bytes might
    be spread over 8 physical disk?

    When I read https://communities.netapp.com/thread/8226 I start to
    wonder whether sql server index rebuilds might no longer be best
    practice, since this will have the same effect on snapshots as windows
    defrag? May I conclude our metroclustered NetApp offers HA, DR and
    fast restore, but that we should review best practices regarding IO
    optimisation?

    Thanks
    Peter
     
    xperre van wilrijk, Apr 20, 2012
    #1
    1. Advertisements

  2. I think those are question you will need to ask in forums devoted
    to Netapp. I have no knowledge about product.
    This assumption is correct. However, that does not mean that SET STATISTICS
    IO ON is irrelevant. And even a standard SATA disk have some amount of
    cache.
    Or maybe Netapp does not play well with SQL Server? After all, there
    are more things than just index rebuilds that causes lots of IO
    on SQL Server. ETL loads for instance.

    No, neither am I a storage expert.


    --
    Erland Sommarskog, SQL Server MVP,

    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
     
    Erland Sommarskog, Apr 20, 2012
    #2
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.