locked
Performance Issue (Insert Query) RRS feed

  • Question

  • Hi All, 

    Not sure whether is a right category to post this. I have two SQL servers which running under SQL Server 2012 Standard Edition. One is served as primary named SQL6 and another is secondary named SQL5, both running database mirroring for DR purposes and SQL6 already serve as primary server for 4 years+. Recently I found out there's performance issue, so I use SQLIO to perform the IO test in both machines and the results seems big different. 

    Here the sample tests that I perform and the results :- 

    sqlio -kR -s60 -frandom -o32 -b64 -Fparam.txt > results_all.txt 
    sqlio -kR -s60 -fsequential -o32 -b64 -Fparam.txt >> results_all.txt 
    sqlio -kW -s60 -frandom -o32 -b64 -Fparam.txt >> results_all.txt 
    sqlio -kW -s60 -fsequential -o32 -b64 -Fparam.txt >> results_all.txt 


    Results: Drive SQL6 Read (random IOs) Read (sequential IOs) Write (random IOs) Write (sequential IOs)
    C IOs/sec : 15990.36 15839.71 6084.54 6105.5
    MBs/sec : 999.39 989.98 380.28 381.59
    IOs/sec : 8479.86 8481.69 6260.7 6212.85
    MBs/sec : 529.99 530.1 391.29 388.3
    IOs/sec : 4525.48 13924.25 1046.41 12660.16
    MBs/sec : 282.84 870.26 65.4 791.26
    IOs/sec : 5657.04 7573.56 437.6 4919.53
    MBs/sec : 353.56 473.34 27.35 307.47
    Drive SQL5 Read (random IOs) Read (sequential IOs) Write (random IOs) Write (sequential IOs)
    C IOs/sec : 37521.6 26141.68 10430.16 6311.01
    MBs/sec : 2345.1 1633.85 651.88 394.43
    D IOs/sec : 44659.23 37266.78 15943.14 6184.28
    MBs/sec : 2791.2 2329.17 996.44 386.51
    E IOs/sec : 42147.96 30678.01 11434.35 12137.04
    MBs/sec : 2634.24 1917.37 714.64 758.56
    F IOs/sec : 7300.41 14181.1 5811.54 4952.59
    MBs/sec : 456.27 886.31 363.22 309.53

    Note: D stores log files, E stores data flies and F stores DB backup files

    I already fail-over all the databases to the secondary server named SQL5 (act as passive) temporarily. And here are my concern :- 

    1) Is this about the disk issue? What other methods that I can diagnostics the disk IO?  

    2) or is this about database physical file fragmentation issue? (https://www.mssqltips.com/sqlservertip/3008/solving-sql-server-database-physical-file-fragmentation/) But SQL5 should be same with SQL6, right?  And I did have a defragmentation job which run over the weekend. 

    Hope can get some advice here. Thanks and highly appreciated. 

    Best Regards, 

                  Han 

    Wednesday, September 19, 2018 2:31 AM

All replies

  • SQLIO to perform the IO test in both machines

    Hello Han,

    SQLIO is a plain hardware Benchmark test tool, it don't test SQL Server performance, even if the suggests so. So when you see different in performance reported by the tool, then it is a Hardware/Windows issue, not a SQL Server one.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, September 19, 2018 6:22 AM
  • Hi Harn,

    >>Is this about the disk issue? What other methods that I can diagnostics the disk IO? 

    This problem can be caused by a disk problem or Windows OS scheduling, I would suggest you using a tool named CrystalDiskMark to check the speed of disk, by the way, please also check if your disk has been achieved 4K Alignment.

    Based on the above information, the latency is mainly caused by the random IOs, this will affect Lazy Writer and by Checkpoints. This problem can be caused by the difference between disk, the achieved 4K Alignment. This can also caused by many other hardware and OS problem.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 19, 2018 7:17 AM
  • Let me start by asking why do think there is disk issue, how did you reached to conclusion that disk was culprit for low performance

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, September 19, 2018 7:23 AM
  • How much data do you insert? Do you set up asynchronous mirroring ? If you insert 1000000 rows into a table, the principle server need to wait for the mirrored server finishing this query and then commit the transaction, which may lead to the principal server’s transaction latency.

    Reference:
    Database Mirroring Best Practices and Performance Considerations 
    http://technet.microsoft.com/en-us/library/cc917681.aspx


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, September 26, 2018 6:56 AM