locked
Check server hardware performance RRS feed

  • Question

  • Hello.

    I would like to check if the hardware on which the SQL server is installed is efficient enough.

    I have installed Red Gate Monitoring.

    This is how the logs for two days look like (large images on: RegGate Logs ):















    Do I understand correctly that the hardware is sufficient?

    Regards,

    Tomasz


    Friday, August 14, 2020 9:03 AM

All replies

  • Your server is page swapping, which is bad. 

    How much physical RAM does the server have?  What is the "Max Server memory" setting in SQL Server?  Is there anything else running on this server, SSRS, SSIS, SSAS or any other non SQL related apps?

    Did you set lock pages in memory?

    https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver15

    https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-the-lock-pages-in-memory-option-windows?view=sql-server-ver15

    • Proposed as answer by MIAOYUXI Monday, August 17, 2020 7:49 AM
    Friday, August 14, 2020 12:35 PM
  • I don't to bad mouth a good sponsor of very many SQL Server events, but it is obvious the tool was build by a developer with a desktop - single processor/core mentality, not understanding that (in the old days) SQL Server frequently runs on multi-processor (+multi-core) systems. Of course, all desktops have been multi-core for 14+ years. Specifically, the first chart processor time does not give any indication as to how many physical cores / logical processors (hyper-threading state) or physical NUMA nodes. Just guess from memory of 320GB that this is a somewhat large system. But why is target memory 459GB?

    The memory pages /sec might indicate swapping, but I have noticed SQL Server backups also generates memory pages without negative affect.

    I am guess CPU + memory is significant to handle many loads, and  that you currently have almost no disk activity. I am guessing you have a weak SSD storage, that shows reasonable latency times at low load,  but would strain under a heavy load. Depends what you want, what your infrastructure team/cloud will give you


    jchang

    • Proposed as answer by MIAOYUXI Monday, August 17, 2020 7:49 AM
    Friday, August 14, 2020 2:11 PM
  • You cannot totally eliminate page swapping in Windows, because Windows apparently uses the page file for temp space, even when you have plenty of free RAM available.  I have never been able to find out from MS why it does that.

    Friday, August 14, 2020 3:03 PM
  • Hi  @TomaszFilipek,

    > Do I understand correctly that the hardware is sufficient?

    You can compare it by referencing : sql-server-performance-checklist

    And quote from this blog:  dba-guide-sql-server-performance-troubleshooting

    To start troubleshooting the most common CPU performance issues, monitor % Processor Time. This counter is available in Performance Monitor. If its value is constantly higher than 80%, the processor is under pressure.

    BR,

    Mia

    If the reply helped, please do “Accept Answer ” and upvote it.--Mia


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Tuesday, August 18, 2020 8:15 AM