Administration / Performance

Using PowerShell to find Processor issues & more

Daniel Janik8 Comments

One of the top three performance killers for SQL Server is lack of processing power. I’d say that it’s second only to storage latency and more of a concern than memory. Although with anything in SQL we can say it depends.

I’ve recently seen a lot of SQL Server instances that were hosted on servers where the power management configuration was not configured; or, I should say it was not disabled.

Power management can reduce the server performance by more than 20% and it’s a simple issue to resolve that requires no code changes to the database or your application.

I decided to create a simple PowerShell script that would report back only if it detected an issue.

Download the code here: https://gallery.technet.microsoft.com/Simple-hardware-check-e82d95ef

image

If any power management settings are detected, a yellow warning will be displayed. If this happens you’ll want to disable the power management configuration in the BIOS and also configure the power management settings in Windows to “High Performance”.  Once you’re done, you should notice your queries running quicker than before. Just like magic!

Like this post? Be sure to check out some of my other blog posts on SQL Server Central or my my blog site SQL Tech Blog.

8 thoughts on “Using PowerShell to find Processor issues & more”

    • On a virtual you also want to watch out for vNUMA problems. On VMWare if hot plug cpu is disabled the vNUMA is also disabled and this can lead to huge performance concerns.

      Like

      Reply

  2. Just want to say one big THANKYOU!
    Using your script identified we had %C2 states on our servers.
    Took down some samples of time test on returning 20mill+ records on one of our SQL servers – was typically getting around 10 minutes.
    Tried changing the power settings in OS, but made no difference. Script still reported %C2 states.
    All our servers are on VMWare, so talked to my network guy, and we offloaded all our servers from one host to another, then made the necessary tweaks in VMware Host, and Virtual Machine BIOS to place in max performance mode. Moved the machines back from one host to another and ran the script again, but it still reported %C2 states! (although it was impossible for it to have these now). Procedeed to do some time tests anyway, and was amazed at the results. The same SQL query now took on average 5m30s! Approx 40% improvement. Our standby APC time has reduced from 2h58m to about 2h40m, so I think thats an acceptable trade off for a near 40% improvement in SQL queries!
    We have since tested this on other VMWare hosts to ensure it wasnt a one off, and am happy to report that all results are the same – approx 40% improvement overall! Its just the script still reports that we have %C2 states, even when we dont. So I would suggest anyone to do this change regardless.
    Regards
    Jason

    Liked by 1 person

    Reply

    • Looks like CTRL-ENTER sends the reply. haha. I meant to type this as well:

      It’s not uncommon to see upwards of 20 to 30% improvement. I’m really glad you found the script useful and that I was able to help. In regards to the BIOS, You’ll want to completely disable c-states. I’m not sure you’ll get much more performance but if c-states are not disabled then that would be why they are still reporting.

      Please check back for more fun scripts and SQL tips. I’m an independent consultant. If you need any help tuning queries or help with a project I’m always available; so, feel free to reach out.

      Also be sure to follow me on LinkedIn and Twitter (@SQLSME).

      Thanks again!

      Like

      Reply

Leave a comment