Post reply

SQL on Hyperv

  • December 27, 2022 at 4:16 pm

    #4130734

    Best practice question.

    We have an sql application from an external partner running on sql 2019.  We have some complaints, about speed or the lack of it.  External company makes remarks that the server is the issue or could be the issue.  We have configured everything as best practice.  We think that maybe the sql instance is not using the full potential of the virtual machine.

    Is there a way we can check if the SQL server is using the full power?

    The hyperV layer is a server 2016. The SQL virtual machine is configured with 8 vprocessors and 250Gb of ram.  If you run the taskmanager in the virtual machine you can see this. sockets:1  virtual processors:8  max.memory: 244Gb

    Any help or suggestions would be fine.

    Thanks.

     

     

  • December 28, 2022 at 8:53 am

    #4130984

    Standard answer for slowness -> what are your waits. Have you already activated query store to find the culprits? Is it patched up

    Hyper-V:

    Is it someway throttled: powersaving , resource limits, low priority ...

     

  • December 28, 2022 at 10:03 am

    #4131013

    Hi,

    and what kind of workload do you use?

    OLTP or OLAP?

    Check your configuration for parallelism and so on.

    There are some nice free tools to check, what kind of problem your server got.

    sp_blitz from Brent Ozar may be a good and easy choise.

    Good luck

    Andreas

  • December 28, 2022 at 3:23 pm

    #4131123

    Hi,

    I'm not a SQL expert, i'm the infrastructure guy :-).  So i'm looking at the more infrastructure-side of things.

    Users complain that 'sometimes' the third party sql-application works slow, slow to realy slow they say. So the guys of the SQL-aplication say (ofcourse) probably this has something to do with the server. So laying the ball in my camp.  While i'm saying everything you asked for is there, and is even oversized.  So you get the point.

    in attachment are some screenshots of the SQL server Perfomance Dashboard.  Maybe the numbers will say something to you guys, because for me its still struggeling to understand. (Altough the last few days i'm learning sql :-))

    Thanks for any feedback.

     

     

  • December 28, 2022 at 3:55 pm

    #4131141

    Screenshots in docx format

    Attachments:
    You must be logged in to view attached files.

  • December 28, 2022 at 4:09 pm

    #4131143

    It's nice to have the dashboard.

    You can use it when it's slow ("user request") to make an inventory of troublesome queries / blocking

    Most expensive queries by cpu / reads / ... give you an insight since last restart of the database

    Your screenshots shows there is very little going on (only one session).

    There are other applications beside sql server, but the cpu impact is minimal ( less than 5%?)

    Your database is patched up to the latest CU

     

  • December 28, 2022 at 4:12 pm

    #4131144

    As Andreas wrote, if you have access to the database you can use sp_Blitz, sp_humanevents, sp_WhoIsActive for a quick triage.

    For administration of SQL DB's you might also look at dbatools

  • December 28, 2022 at 4:16 pm

    #4131147

    Yes, i did all updates and pathches on server OS and SQL server.

    Indeed, the company is in holiday now, so thats why there's not much going on. And thats why i may update, restart, upgrade, test all the servers :-).

    Glad to hear that the numbers are not showing anything out of the ordinary.

    Once the company re-starts, more usage will be generated, and maybe i can post back some real-life usage. If thats okay?

     

     

     

     

     

     

  • December 28, 2022 at 5:48 pm

    #4131184

    Jo wrote:

    Best practice question.

    We have configured everything as best practice.

    What "best practices"?  Can you elaborate on these, and where you learned these?

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • December 28, 2022 at 9:03 pm

    #4131247

    Jo wrote:

    External company makes remarks that the server is the issue or could be the issue.

    This is easy then... have a video meeting where you drive and they tell you what to look for/try/etc.  HAVE THEM PROVE what they say.  They should be happy to do it so that they can "prove" it's not actually their database design/software.

    Almost every 3rd party database/software company I've complained to about performance have always blamed the server/hardware/index maintenance/blah/blah/blah.  They also say, "Well, you're the first customer to complain".  I so much want to tell them that we're also the first customer they've had that can actually tell. 😀

    I've recently had one 3rd party database/software company explain that they need to archive rows from the biggest table to avoid documented major slow downs once it goes over [insert drum roll here] 40,000 rows.  It's got like 15 columns of data in it and none of it has large character based columns.

    There's another company that can't understand why them doing 149,000 reads on the same table for the same rows every time someone tabs to a new filed (close to 200 active sessions) is a bad idea.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 28, 2022 at 10:12 pm

    #4131261

    regardless of other items, there is one thing you do need to confirm - is the HOST set to have cpu at full power all the time (or not) and is the VM also need to have power at full all the time.

    if either has power saving mode on, that can slow down even the best SQL you have with a perfectly good db/tables/indexes.

    and is it NUMA aware and are the cores/memory NUMA aligned.

  • December 29, 2022 at 7:57 am

    #4131393

    Hi Frederico,

    Thanks for the tips. I did the following adjustements.

    Server 2016 OS

    Power settings -> all on max.power -> was OK

    Smart array raid controller

    Power settings -> Max performance -> was OK

    HP proliant BIOS settings

    Power settings -> Dynamic Power Savings Mode -> Adjusted to "Static High Performance Mode"

    HP Static High Performance Mode—Processors run in the maximum power and performance state, regardless of the OS power management policy. This mode is useful in environments where performance is critical and power consumption is less important.

     

  • December 29, 2022 at 8:57 am

    #4131409

    In the meanwhile i had the possibility to run the sp_blitz. (not under real-life-load)

    The result are in attachment.  Curious if that output will show something to you SQL-guys.

    Thanks for the feedback.

     

     

  • December 29, 2022 at 9:04 am

    #4131422

    retry in word format

     

    Attachments:
    You must be logged in to view attached files.

  • December 29, 2022 at 9:28 am

    #4131427

    Haven't opened the attachment yet.

    One of the result columns of sp_Blitz is URL which has more details what the check is about

    Like "Active Tables Without Clustered Indexes" -> https://BrentOzar.com/go/heaps

Viewing 15 posts - 1 through 15 (of 43 total)

You must be logged in to reply to this topic. Login to reply